IT

Inner join examples:

Example 1;

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID

 

Date Samples:

SELECT EmployeeID, LastName, FirstName, Title,
HireDate, ReportsTo, Country
FROM Employees
WHERE DATEPART(m, HireDate) = @Month

DATEPART is a T-SQL function that returns a particular date portion of a datetime type; in this case we're using DATEPART to return the month of the HireDate column.

Random funtions:

Example 1 [3]:

SELECT TOP 1 CategoryID, CategoryName 
FROM Categories 
ORDER BY NEWID()

Notes:  

• ORDER BY NEWID() returns the records sorted in random order (see Using NEWID() to Randomly Sort Records);
• SELECT TOP 1 returns the first record from the result set;

Put together, this query returns the CategoryID and CategoryName column values from a single, randomly selected category.

Ranking Functions (Transact-SQL) samples:

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
Transact-SQL provides the following ranking functions:

• RANK
• NTILE
• DENSE_RANK
• ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. 

ROW NUMBER example [1]:

The ROW_NUMBER() keyword can be used to associated a ranking with each record returned over a particular ordering using the following syntax:

SELECT columnList,
       ROW_NUMBER() OVER(orderByClause)
FROM TableName

ROW_NUMBER() returns a numerical value that specifies the rank for each record with regards to the indicated ordering. For example, to see the rank for each product, ordered from the most expensive to the least, we could use the following query:

SELECT ProductName, UnitPrice,
       ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
FROM Products

Retrieving only a limited number of rows based on the ROW_NUMBER():

ALTER PROCEDURE [dbo].[GetProductsPaged]
(
    @startRowIndex int,
    @maximumRows int
)
AS 
    SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
    CategoryName, SupplierName
    FROM
        (
            SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
            UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
            (SELECT CategoryName
            FROM Categories 
            WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName, 
            (SELECT CompanyName
            FROM Suppliers
            WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName,
            ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
        FROM Products
        ) AS ProductsWithRowNumbers 
    WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

UPDATE examples:

• (Northwind) Set all products from a specific supplier to "discontinued":

UPDATE [Products]
SET Discontinued = 1 
WHERE SupplierID = @SupplierID

Stored procedures examples :

Example 1 [3] :

CREATE PROCEDURE dbo.GetProductsByCategory 
    @CategoryID int 
AS
    SELECT * 
    FROM Products 
    WHERE CategoryID = @CategoryID

Related articles:

[1] - Efficiently Paging Through Large Amounts of Data (see Step 3: Returning the Precise Subset of Records)
[2]
[3]