Querying Data in SQL Server

This tutorial guides you through the fundamental concepts and practical application of querying data within a SQL Server database. Understanding how to retrieve specific information from your tables is a cornerstone of database management and development.

The SELECT Statement

The primary statement used for retrieving data is SELECT. It allows you to specify which columns you want to retrieve and from which table(s).

Basic SELECT Statement

To select all columns from a table named Customers:

SELECT *
FROM Customers;

To select specific columns, such as CustomerID and CompanyName, from the same table:

SELECT CustomerID, CompanyName
FROM Customers;

Filtering Data with the WHERE Clause

Often, you need to retrieve only a subset of rows that meet certain criteria. The WHERE clause is used to filter records.

Filtering Customers by City

Retrieve all columns for customers located in 'London':

SELECT *
FROM Customers
WHERE City = 'London';

You can use various comparison operators:

= (Equal to)
<> or != (Not equal to)
> (Greater than)
< (Less than)
>= (Greater than or equal to)
<= (Less than or equal to)
BETWEEN (Between an inclusive range)
LIKE (Pattern matching)
IN (Matches any value in a list)

Example using LIKE for pattern matching (customers whose names start with 'A'):

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE 'A%';

Sorting Data with the ORDER BY Clause

The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.

Sorting Products by Price

Retrieve product names and prices, sorted by price in ascending order:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice ASC;

To sort in descending order:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

You can also sort by multiple columns:

SELECT CustomerID, CompanyName, City
FROM Customers
ORDER BY Country ASC, City ASC;

Aggregating Data with GROUP BY and Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX. The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows.

Counting Customers by Country

Count the number of customers in each country:

SELECT Country, COUNT(*) AS NumberOfCustomers
FROM Customers
GROUP BY Country
ORDER BY NumberOfCustomers DESC;

Calculate the total sales for each product:

SELECT ProductID, SUM(Quantity * UnitPrice) AS TotalSales
FROM OrderDetails
GROUP BY ProductID
ORDER BY TotalSales DESC;

Tip: When using GROUP BY, any column in the SELECT list that is not an aggregate function must be included in the GROUP BY clause.

Filtering Groups with the HAVING Clause

The HAVING clause is used to filter groups based on a specified condition, similar to how WHERE filters individual rows. It's typically used with GROUP BY.

Finding Countries with More Than 10 Customers

SELECT Country, COUNT(*) AS NumberOfCustomers
FROM Customers
GROUP BY Country
HAVING COUNT(*) > 10
ORDER BY NumberOfCustomers DESC;

Joining Tables (Brief Introduction)

While complex joins are covered in a separate tutorial, it's worth noting that to query data from multiple related tables, you'll use JOIN clauses. This allows you to combine rows from two or more tables based on a related column between them.

By mastering these fundamental querying techniques, you'll be well-equipped to extract meaningful insights and manage data effectively within SQL Server.