MSDN Tutorials

Querying Data in SQL Server

This tutorial will guide you through the fundamental concepts and practical techniques for querying data effectively in Microsoft SQL Server. Mastering data querying is essential for retrieving, analyzing, and reporting on the information stored in your databases.

Introduction to Data Querying

A query is a request for information from a database. SQL (Structured Query Language) is the standard language used to communicate with relational databases like SQL Server. The primary statement for retrieving data is the SELECT statement.

Basic SELECT Statements

The simplest query retrieves all columns and all rows from a table. To retrieve specific columns, list them after the SELECT keyword, separated by commas.

-- Retrieve all columns from the 'Customers' table
SELECT *
FROM Customers;

-- Retrieve 'FirstName' and 'LastName' from the 'Employees' table
SELECT FirstName, LastName
FROM Employees;

Filtering Data with WHERE

The WHERE clause is used to specify criteria for selecting rows. You can use comparison operators (=, !=, >, <, >=, <=), logical operators (AND, OR, NOT), and other operators like LIKE, IN, and BETWEEN.

-- Select customers from 'London'
SELECT CustomerID, CompanyName, City
FROM Customers
WHERE City = 'London';

-- Select employees hired after January 1, 2023
SELECT EmployeeID, FirstName, HireDate
FROM Employees
WHERE HireDate > '2023-01-01';

-- Select products with a price between 50 and 100
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 50 AND 100;

Sorting Data with ORDER BY

The ORDER BY clause sorts the result set based on one or more columns. Use ASC for ascending order (default) and DESC for descending order.

-- Select all products and sort them by UnitPrice in descending order
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

-- Select employees and sort by LastName then FirstName
SELECT LastName, FirstName
FROM Employees
ORDER BY LastName ASC, FirstName ASC;

Joining Tables

Databases often store related data in separate tables. JOIN clauses combine rows from two or more tables based on a related column between them.

• INNER JOIN: Returns rows when there is a match in both tables.
• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
• RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
• FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table.

-- Get customer names and their corresponding order dates
SELECT c.CompanyName, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- Get all products and their category names (if available)
SELECT p.ProductName, pc.CategoryName
FROM Products p
LEFT JOIN ProductCategories pc ON p.CategoryID = pc.CategoryID;

Aggregate Functions

Aggregate functions perform calculations on a set of rows and return a single value. Common functions include:

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of a numeric column.
• AVG(): Calculates the average of a numeric column.
• MIN(): Finds the minimum value in a column.
• MAX(): Finds the maximum value in a column.

-- Count the total number of customers
SELECT COUNT(*) AS TotalCustomers
FROM Customers;

-- Calculate the average unit price of products
SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;

Grouping Data with GROUP BY

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions.

-- Count the number of orders per customer
SELECT CustomerID, COUNT(*) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID
ORDER BY NumberOfOrders DESC;

-- Calculate the total sales amount per city
SELECT City, SUM(Orders.TotalAmount) AS TotalSales
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY City
ORDER BY TotalSales DESC;

Subqueries

A subquery (or inner query) is a query nested inside another SQL query. It can be used in the WHERE, FROM, or SELECT clause.

-- Find employees who have placed orders
SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID IN (SELECT DISTINCT EmployeeID FROM Orders);

-- Find products with a price higher than the average price
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

Conclusion

You have learned the essentials of querying data in SQL Server, including basic SELECT statements, filtering with WHERE, sorting with ORDER BY, joining tables, using aggregate functions, grouping data, and employing subqueries. Practice these concepts to become proficient in data retrieval.

Continue exploring more advanced topics such as window functions, common table expressions (CTEs), and stored procedures for more complex data manipulation and retrieval needs.