SQL Server Documentation - Example Code Snippets

Explore a curated collection of practical examples demonstrating various SQL Server features and functionalities. These snippets are designed to help you understand and implement common scenarios effectively.

Basic Querying and Data Retrieval

Selecting Specific Columns

Retrieve data from a table, specifying the columns you need.

SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE Country = 'USA';

Filtering with WHERE Clause

Use the WHERE clause to filter records based on specified conditions.

SELECT OrderID, OrderDate, ShipVia
FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'
ORDER BY OrderDate DESC;

Joining Tables

Combine rows from two or more tables based on a related column between them.

SELECT o.OrderID, c.CompanyName, o.OrderDate
FROM Orders AS o
JOIN Customers AS c ON o.CustomerID = c.CustomerID
WHERE c.City = 'London';

Data Manipulation Language (DML)

Inserting New Records

Add new rows of data to a table.

INSERT INTO Products (ProductName, SupplierID, CategoryID, Unit, Price)
VALUES ('Chai Tea', 1, 1, '10 boxes x 20 bags', 18.00);

Updating Existing Records

Modify existing data within a table.

UPDATE Customers
SET Country = 'Canada'
WHERE CustomerID = 5;

Deleting Records

Remove rows from a table that meet specific criteria.

DELETE FROM OrderDetails
WHERE Quantity < 5;

Advanced SQL Server Features

Using Aggregate Functions (COUNT, SUM, AVG)

Perform calculations on sets of rows.

SELECT COUNT(CustomerID) AS NumberOfCustomers,
       AVG(CAST(Freight AS DECIMAL(10,2))) AS AverageFreight
FROM Orders
WHERE ShipCountry = 'Germany';

Grouping Data with GROUP BY

Group rows that have the same values in specified columns into summary rows.

SELECT City, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY City
ORDER BY NumberOfCustomers DESC;

Creating a Stored Procedure

A pre-compiled SQL query that can be executed repeatedly.

CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT OrderID, OrderDate, ShipCity
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
GO

-- To execute:
-- EXEC GetCustomerOrders @CustomerID = 10;

Using a Common Table Expression (CTE)

A temporary named result set that you can reference within a single SQL statement.

WITH RecentOrders AS (
    SELECT OrderID, CustomerID, OrderDate
    FROM Orders
    WHERE OrderDate > DATEADD(year, -1, GETDATE())
)
SELECT c.CompanyName, ro.OrderID, ro.OrderDate
FROM Customers AS c
JOIN RecentOrders AS ro ON c.CustomerID = ro.CustomerID;

Back to SQL Overview Advanced Topics