T-SQL Class Examples

I am truly sorry that I am just now getting to posting these examples.

USE AdventureWorks2008R2
--Using variables wiht control flow in t-sql
DECLARE @var INT
SET @var = (SELECT COUNT (*) FROM Person.Person)
WHILE @var > 0
  IF (SELECT @var-1) >= 1000
BEGIN
SELECT @var - 1
SELECT FirstName,
      MiddleName,
      LastName
FROM Person.Person
WHERE BusinessEntityID = @var
SET @var = @var - 1
END;
GO
SELECT OrderQty + 10,
      OrderQty
FROM Sales.SalesOrderDetail;
GO
--Variables have a limimted scope to the batch
--GO ends the scope
DECLARE @var INT = 10
SELECT @var;
SELECT @var;
GO
--@var is out of scope and an error is raiased
SELECT @var;
GO
--Using a common table expression
WITH CTE
AS
(
SELECT *
FROM Person.Person
)
SELECT FirstName
FROM CTE;
GO
--Structured error handling in t-sql
BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
   PRINT 'Cant do that'
   SELECT ERROR_NUMBER(),
         ERROR_MESSAGE()
END CATCH;

--Unhandled error that occurs during binding
BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT NotHere
   FROM NoSuchThing
END TRY
BEGIN CATCH
   PRINT 'Cant do that'
   SELECT ERROR_NUMBER(),
         ERROR_MESSAGE()
END CATCH;

--Order by clause ordering by the LastName then FirstName
SELECT FirstName,
      LastName
FROM Person.Person
ORDER BY LastName,
        FirstName;
GO

--WHERE using =
SELECT FirstName,
      MiddleName,
      LastName
FROM Person.Person
WHERE MiddleName = ''

--LIKE operator last name beginning with Ab and then any other letters
SELECT *
FROM Person.Person
WHERE LastName LIKE 'Ab%';
GO

--LIKE Operator LastName beginning with Ab and the next letter CAN NOT BE e
SELECT FirstName,
      LastName,
      MiddleName
FROM Person.Person
WHERE LastName LIKE 'Ab[^e]%';

--Using OR and AND in the WHERE clause
--FirstName Like and MiddleName IS NULL are evaluated together 4 rows
SELECT FirstName,
      LastName,
      MiddleName
FROM Person.Person
WHERE LastName LIKE 'Ab%'
AND (FirstName LIKE 'K%'
OR MiddleName IS NULL);

--Using OR and AND in the WHERE clause
--FirstName Like and MiddleName IS NULL are evaluated separately 8,500 rows
SELECT FirstName,
      LastName,
      MiddleName
FROM Person.Person
WHERE LastName LIKE 'Ab%'
AND FirstName LIKE 'K%'
OR MiddleName IS NULL

--USING BETWEEN and the >= and <= equivalent
SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1 AND 10;

SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice >= 1
AND UnitPrice <= 10;

--Using BETWEEN with character data types
SELECT FirstName,
      LastName
FROM Person.Person
WHERE LastName BETWEEN 'A' AND 'N';

--Using IN with a sub-query
SELECT *
FROM Person.Address
WHERE City IN (SELECT City FROM Person.Address WHERE City LIKE '%a%');

--NULL values used in expression results in NULL
SELECT 8 + 1 + NULL

--Using ISNULL to replace a NULL value with a specified value
SELECT FirstName,
      ISNULL(MiddleName, 'N/A'),
      LastName
FROM Person.Person

--Using ISNULL in an expression
SELECT 8 + 1 + ISNULL(NULL, 2);

--Using COALESCE in an expression to handle NULL values
SELECT 8 + 1 + COALESCE(NULL, NULL, NULL, 4);

--DISTINCT is applied to the ENTIRE row, ot just the column name immediately following DISTINCT
SELECT DISTINCT LastName,
      FirstName,
      BusinessEntityID
FROM Person.Person;

--Using String literals
SELECT 'Hello my name is ' + FirstName + ' '+ LastName lName,
      'This will NEVER CHANGE' AS never
FROM Person.Person;

--Using ISNULL with string literals
SELECT FirstName + ' '+ ISNULL(SUBSTRING(MiddleName, 1,1) + '.',  ' ') + ' ' + LastName lName,
      'This will NEVER CHANGE' AS never
FROM Person.Person;

--Using expressions
SELECT OrderQty * UnitPrice - UnitPriceDiscount AS SalesPrice
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
ORDER BY OrderQty

--COUNT aggregation showing the total count of rows
SELECT COUNT(*)
FROM HumanResourc