T-SQL Script List

For the complete list of TSQL 2012 test database creation, please download from here.

The reason for this list is to keep a record of what I explored, and sometimes for me to quickly recall what I did. The script can be copied for execution statement by statement.

create database

/*
-- 1. Connect to master database

-- 2. Run the following code to create an empty database called TSQL2012
USE master;

-- Drop database
IF DB_ID('TSQL2012') IS NOT NULL DROP DATABASE TSQL2012;

-- If database could not be created due to open connections, abort
IF @@ERROR = 3702
RAISERROR('Database cannot be dropped because there are still open connections.', 127, 127) WITH NOWAIT, LOG;

-- Create database
CREATE DATABASE TSQL2012;
GO

USE TSQL2012;
GO

create schema in database

---------------------------------------------------------------------
-- Create Schemas
---------------------------------------------------------------------

CREATE SCHEMA HR AUTHORIZATION dbo;
GO
CREATE SCHEMA Production AUTHORIZATION dbo;
GO
CREATE SCHEMA Sales AUTHORIZATION dbo;
GO
CREATE SCHEMA Stats AUTHORIZATION dbo;
GO

create table / index in schemas

---------------------------------------------------------------------
-- Create Tables
---------------------------------------------------------------------

-- Create table HR.Employees
CREATE TABLE HR.Employees
(
empid INT NOT NULL IDENTITY,
lastname NVARCHAR(20) NOT NULL,
firstname NVARCHAR(10) NOT NULL,
title NVARCHAR(30) NOT NULL,
titleofcourtesy NVARCHAR(25) NOT NULL,
birthdate DATETIME NOT NULL,
hiredate DATETIME NOT NULL,
address NVARCHAR(60) NOT NULL,
city NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
postalcode NVARCHAR(10) NULL,
country NVARCHAR(15) NOT NULL,
phone NVARCHAR(24) NOT NULL,
mgrid INT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid)
REFERENCES HR.Employees(empid),
CONSTRAINT CHK_birthdate CHECK(birthdate <= CURRENT_TIMESTAMP) ); CREATE NONCLUSTERED INDEX idx_nc_lastname ON HR.Employees(lastname); CREATE NONCLUSTERED INDEX idx_nc_postalcode ON HR.Employees(postalcode); -- Create table Production.Suppliers CREATE TABLE Production.Suppliers ( supplierid INT NOT NULL IDENTITY, companyname NVARCHAR(40) NOT NULL, contactname NVARCHAR(30) NOT NULL, contacttitle NVARCHAR(30) NOT NULL, address NVARCHAR(60) NOT NULL, city NVARCHAR(15) NOT NULL, region NVARCHAR(15) NULL, postalcode NVARCHAR(10) NULL, country NVARCHAR(15) NOT NULL, phone NVARCHAR(24) NOT NULL, fax NVARCHAR(24) NULL, CONSTRAINT PK_Suppliers PRIMARY KEY(supplierid) ); CREATE NONCLUSTERED INDEX idx_nc_companyname ON Production.Suppliers(companyname); CREATE NONCLUSTERED INDEX idx_nc_postalcode ON Production.Suppliers(postalcode); -- Create table Production.Categories CREATE TABLE Production.Categories ( categoryid INT NOT NULL IDENTITY, categoryname NVARCHAR(15) NOT NULL, description NVARCHAR(200) NOT NULL, CONSTRAINT PK_Categories PRIMARY KEY(categoryid) ); CREATE INDEX categoryname ON Production.Categories(categoryname); -- Create table Production.Products CREATE TABLE Production.Products ( productid INT NOT NULL IDENTITY, productname NVARCHAR(40) NOT NULL, supplierid INT NOT NULL, categoryid INT NOT NULL, unitprice MONEY NOT NULL CONSTRAINT DFT_Products_unitprice DEFAULT(0), discontinued BIT NOT NULL CONSTRAINT DFT_Products_discontinued DEFAULT(0), CONSTRAINT PK_Products PRIMARY KEY(productid), CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories(categoryid), CONSTRAINT FK_Products_Suppliers FOREIGN KEY(supplierid) REFERENCES Production.Suppliers(supplierid), CONSTRAINT CHK_Products_unitprice CHECK(unitprice >= 0)
);

CREATE NONCLUSTERED INDEX idx_nc_categoryid ON Production.Products(categoryid);
CREATE NONCLUSTERED INDEX idx_nc_productname ON Production.Products(productname);
CREATE NONCLUSTERED INDEX idx_nc_supplierid ON Production.Products(supplierid);

-- Create table Sales.Customers
CREATE TABLE Sales.Customers
(
custid INT NOT NULL IDENTITY,
companyname NVARCHAR(40) NOT NULL,
contactname NVARCHAR(30) NOT NULL,
contacttitle NVARCHAR(30) NOT NULL,
address NVARCHAR(60) NOT NULL,
city NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
postalcode NVARCHAR(10) NULL,
country NVARCHAR(15) NOT NULL,
phone NVARCHAR(24) NOT NULL,
fax NVARCHAR(24) NULL,
CONSTRAINT PK_Customers PRIMARY KEY(custid)
);

CREATE NONCLUSTERED INDEX idx_nc_city ON Sales.Customers(city);
CREATE NONCLUSTERED INDEX idx_nc_companyname ON Sales.Customers(companyname);
CREATE NONCLUSTERED INDEX idx_nc_postalcode ON Sales.Customers(postalcode);
CREATE NONCLUSTERED INDEX idx_nc_region ON Sales.Customers(region);

-- Create table Sales.Shippers
CREATE TABLE Sales.Shippers
(
shipperid INT NOT NULL IDENTITY,
companyname NVARCHAR(40) NOT NULL,
phone NVARCHAR(24) NOT NULL,
CONSTRAINT PK_Shippers PRIMARY KEY(shipperid)
);

-- Create table Sales.Orders
CREATE TABLE Sales.Orders
(
orderid INT NOT NULL IDENTITY,
custid INT NULL,
empid INT NOT NULL,
orderdate DATETIME NOT NULL,
requireddate DATETIME NOT NULL,
shippeddate DATETIME NULL,
shipperid INT NOT NULL,
freight MONEY NOT NULL
CONSTRAINT DFT_Orders_freight DEFAULT(0),
shipname NVARCHAR(40) NOT NULL,
shipaddress NVARCHAR(60) NOT NULL,
shipcity NVARCHAR(15) NOT NULL,
shipregion NVARCHAR(15) NULL,
shippostalcode NVARCHAR(10) NULL,
shipcountry NVARCHAR(15) NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
REFERENCES Sales.Customers(custid),
CONSTRAINT FK_Orders_Employees FOREIGN KEY(empid)
REFERENCES HR.Employees(empid),
CONSTRAINT FK_Orders_Shippers FOREIGN KEY(shipperid)
REFERENCES Sales.Shippers(shipperid)
);

CREATE NONCLUSTERED INDEX idx_nc_custid ON Sales.Orders(custid);
CREATE NONCLUSTERED INDEX idx_nc_empid ON Sales.Orders(empid);
CREATE NONCLUSTERED INDEX idx_nc_shipperid ON Sales.Orders(shipperid);
CREATE NONCLUSTERED INDEX idx_nc_orderdate ON Sales.Orders(orderdate);
CREATE NONCLUSTERED INDEX idx_nc_shippeddate ON Sales.Orders(shippeddate);
CREATE NONCLUSTERED INDEX idx_nc_shippostalcode ON Sales.Orders(shippostalcode);

-- Create table Sales.OrderDetails
CREATE TABLE Sales.OrderDetails
(
orderid INT NOT NULL,
productid INT NOT NULL,
unitprice MONEY NOT NULL
CONSTRAINT DFT_OrderDetails_unitprice DEFAULT(0),
qty SMALLINT NOT NULL
CONSTRAINT DFT_OrderDetails_qty DEFAULT(1),
discount NUMERIC(4, 3) NOT NULL
CONSTRAINT DFT_OrderDetails_discount DEFAULT(0),
CONSTRAINT PK_OrderDetails PRIMARY KEY(orderid, productid),
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY(orderid)
REFERENCES Sales.Orders(orderid),
CONSTRAINT FK_OrderDetails_Products FOREIGN KEY(productid)
REFERENCES Production.Products(productid),
CONSTRAINT CHK_discount CHECK (discount BETWEEN 0 AND 1),
CONSTRAINT CHK_qty CHECK (qty > 0),
CONSTRAINT CHK_unitprice CHECK (unitprice >= 0)
);

CREATE NONCLUSTERED INDEX idx_nc_orderid ON Sales.OrderDetails(orderid);
CREATE NONCLUSTERED INDEX idx_nc_productid ON Sales.OrderDetails(productid);

-- Create table Stats.Tests
CREATE TABLE Stats.Tests
(
testid VARCHAR(10) NOT NULL,
CONSTRAINT PK_Tests PRIMARY KEY(testid)
);

-- Create table Stats.Scores
CREATE TABLE Stats.Scores
(
testid VARCHAR(10) NOT NULL,
studentid VARCHAR(10) NOT NULL,
score TINYINT NOT NULL
CONSTRAINT CHK_Scores_score CHECK (score BETWEEN 0 AND 100),
CONSTRAINT PK_Scores PRIMARY KEY(testid, studentid),
CONSTRAINT FK_Scores_Tests FOREIGN KEY(testid)
REFERENCES Stats.Tests(testid)
);

CREATE NONCLUSTERED INDEX idx_nc_testid_score ON Stats.Scores(testid, score);

Populate Data Into Tables

We usually use DML (INSERT / DELETE / UPDATE) to populate the data into tables. Here is my T-SQL script to populate the above tables.

Extra bit of script sample:

---------------------------------------------------------------------
-- Populate Tables
---------------------------------------------------------------------

SET NOCOUNT ON;

-- Create and Populate table dbo.Nums
CREATE TABLE dbo.Nums(n INT NOT NULL CONSTRAINT PK_Nums PRIMARY KEY);

DECLARE @max AS INT, @rc AS INT;
SET @max = 100000;
SET @rc = 1;

INSERT INTO dbo.Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

SET NOCOUNT OFF;
GO

Create other objects (View, Function etc)

---------------------------------------------------------------------
-- Create Views and Functions
---------------------------------------------------------------------

CREATE VIEW Sales.OrderValues
WITH SCHEMABINDING
AS

SELECT O.orderid, O.custid, O.empid, O.shipperid, O.orderdate, O.requireddate, O.shippeddate,
SUM(OD.qty) AS qty,
CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
AS NUMERIC(12, 2)) AS val
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
GROUP BY O.orderid, O.custid, O.empid, O.shipperid, O.orderdate, O.requireddate, O.shippeddate;
GO

CREATE VIEW Sales.OrderTotalsByYear
WITH SCHEMABINDING
AS

SELECT
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
GO

CREATE VIEW Sales.CustOrders
WITH SCHEMABINDING
AS

SELECT
O.custid,
DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0) AS ordermonth,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY custid, DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0);
GO

CREATE VIEW Sales.EmpOrders
WITH SCHEMABINDING
AS

SELECT
O.empid,
DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0) AS ordermonth,
SUM(OD.qty) AS qty,
CAST(SUM(OD.qty * OD.unitprice * (1 - discount))
AS NUMERIC(12, 2)) AS val,
COUNT(*) AS numorders
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY empid, DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0);
GO

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum;
GO

Data Analysis using SELECT

-- Step 1: Change database context to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Querying a table
-- Select and run the completed query to show results
-- Point out that there are 9 rows returned
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) as numorders
FROM Sales.Orders
WHERE custid =71
GROUP BY empid,YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;

-- Step 3: Querying a table
-- Select and run the partial query to show results
-- Point out the use of the * as a placeholder since FROM can't be run by itself.
-- Point out that there are 830 rows returned

SELECT *
FROM Sales.Orders;

-- Step 4: Querying a table
-- Select and run the partial query to show results
-- Point out that there are 31 rows returned

SELECT *
FROM Sales.Orders
WHERE custid =71;

-- Step 5: Querying a table with an invalid SELECT statement
-- Select and run the partial query to show results
-- THIS WILL CAUSE AN ERROR DUE TO THE SELECT LIST

SELECT *
FROM Sales.Orders
WHERE custid =71
GROUP BY empid, YEAR(orderdate);

-- Step 6: Querying a table
-- Select and run the partial query to show results
-- Point out that the * in the SELECT list has been
-- replaced with columns that are either in the GROUP BY expression
-- or are aggregate functions (this will be explained further in Module 9
-- Point out that there are 16 rows returned

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) as numorders
FROM Sales.Orders
WHERE custid =71
GROUP BY empid,YEAR(orderdate);

-- Step 7: Querying a table
-- Select and run the partial query to show results
-- Point out that a HAVING clause further filters the results
-- based on the groups
-- Point out that there are 9 rows returned and there is no apparently sort order

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) as numorders
FROM Sales.Orders
WHERE custid =71
GROUP BY empid,YEAR(orderdate)
HAVING COUNT(*) > 1;

-- Step 8: Querying a table
-- Select and run the completed query to show results
-- Point out that the ORDER BY clause further has sorted the results
-- Point out that there are 9 rows returned

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) as numorders
FROM Sales.Orders
WHERE custid =71
GROUP BY empid,YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;

CASE statement

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Simple CASE Expression
--Select and execute the following query to show the use of a simple CASE expression
--to convert a flag-type column to a label.
SELECT productid, productname, unitprice,
CASE discontinued
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Discontinued'
END AS status
FROM Production.Products;

-- Step 3: Simple CASE Expression
--Select and execute the following query to show the use of a simple CASE expression
--to replace an id with a name. Point out that a JOIN to a reference table will be
--a better solution.

SELECT orderid, custid, orderdate,
CASE empid
WHEN 1 THEN 'Buck'
WHEN 2 THEN 'Cameron'
WHEN 3 THEN 'Davis'
WHEN 4 THEN 'Dolgopyatova'
WHEN 5 THEN 'Funk'
WHEN 6 THEN 'King'
WHEN 7 THEN 'Lew'
WHEN 8 THEN 'Peled'
WHEN 9 THEN 'Suurs'
ELSE 'Unknown Sales Rep'
END AS salesrep
FROM Sales.Orders;

What difference is between ANSI SQL-89 and ANSI SQL-93 join?

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Join 2 tables
-- Select and execute the following query
-- to illustrate ANSI SQL-89 syntax
-- to join 2 tables
-- Point out that 830 rows are returned.
SELECT c.companyname, o.orderdate
FROM Sales.Customers AS c, Sales.Orders AS o
WHERE c.custid = o.custid;

-- Step 3: Join 2 tables
-- Select and execute the following query
-- to illustrate ANSI SQL-89 syntax
-- omitting the WHERE clause and causing an inadvertent Cartesian join.
-- Point out that 75530 rows are returned.
SELECT c.companyname, o.orderdate
FROM Sales.Customers AS c, Sales.Orders AS o;

-- Step 4: Join 2 tables
-- Select and execute the following query
-- to illustrate ANSI SQL-92 syntax
-- to join 2 tables
-- Point out that 830 rows are returned.
SELECT c.companyname, o.orderdate
FROM Sales.Customers AS c JOIN Sales.Orders AS o
ON c.custid = o.custid;

-- Step 5: Join 2 tables
-- Select and execute the following query
-- to illustrate ANSI SQL-92 syntax.
-- Note that the ON clause is deliberately omitted
-- to cause an error, showing the protection
-- against accidental Cartesian products
--THIS WILL INTENTIONALLY CAUSE AN ERROR

SELECT c.companyname, o.orderdate
FROM Sales.Customers AS c JOIN Sales.Orders AS o;
-- ON c.custid = o.custid

INNER JOIN

--  Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Join 2 tables
-- Select and execute the following query
-- to demonstrate a two-table inner join.
-- Point out that there are 77 rows output
SELECT c.categoryid, c.categoryname, p.productid, p.productname
FROM Production.Categories AS c
JOIN Production.Products AS p
ON c.categoryid = p.categoryid;

-- Step 3: Join 2 tables
-- Select and execute the following query
-- to demonstrate a two-table inner composite join.
-- Point out that there are 27 rows output without a distinct filter
SELECT e.city, e.country
FROM Sales.Customers AS c
JOIN HR.Employees AS e
ON c.city = e.city AND c.country = e.country;

-- Step 4: Join 2 tables
-- Select and execute the following query
-- to demonstrate a two-table inner composite join.
-- Point out that there are 3 rows output with a distinct filter
SELECT DISTINCT  e.city, e.country
FROM Sales.Customers AS c
JOIN HR.Employees AS e
ON c.city = e.city AND c.country = e.country;

-- Step 5: Join multiples tables
-- Select and execute the following query
-- to demonstrate a two-table inner join.
-- Point out that the elements needed to add and display data
-- from a third table have been commented out to join
-- the first two tables only
-- 830 rows will be returned
SELECT c.custid, c.companyname, o.orderid, o.orderdate-- , od.productid, od.qty
FROM Sales.Customers AS c
JOIN Sales.Orders AS o
ON c.custid = o.custid;
-- JOIN Sales.OrderDetails od
-- ON o.orderid = od.orderid;

-- Step 6: Join 3 tables
-- Select and execute the following query
-- to demonstrate a three-table inner join.
-- 2155 rows will be returned. Why?
SELECT c.custid, c.companyname, o.orderid, o.orderdate, od.productid, od.qty
FROM Sales.Customers AS c
JOIN Sales.Orders AS o
ON c.custid = o.custid
JOIN Sales.OrderDetails od
ON o.orderid = od.orderid;

OUTER JOIN

--  Step 1: Open a new query window to the AdventureWorks database
USE AdventureWorks2008R2;
GO

-- Step 2: Join 2 tables
-- Select and execute the following query
-- to show only matching customers and orders
SELECT c.CustomerID, soh.SalesOrderID
FROM Sales.Customer c JOIN Sales.SalesOrderHeader soh
ON c.CustomerID = soh.CustomerID;
-- (31465 row(s) affected)

-- Step 3: Join 2 tables
-- Select and execute the following query
-- to show all customers and any matching orders
SELECT *
FROM Sales.Customer c LEFT OUTER JOIN Sales.SalesOrderHeader soh
ON c.CustomerID = soh.CustomerID;
-- (32166 row(s) affected)

-- Step 4: Join 2 tables
-- Select and execute the following query to show
-- a left outer join
USE TSQL2012;
GO
SELECT c.custid, c.companyname, o.orderid, o.orderdate
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o
ON c.custid =o.custid;

-- Step 5: Join 2 tables
-- Select and execute the following query to
-- show customers without orders
USE TSQL2012;
GO
SELECT c.custid, c.companyname, o.orderid, o.orderdate
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o
ON c.custid =o.custid
WHERE o.orderid IS NULL;

-- Step 6: Join 2 tables
-- Select and execute the following query to
-- show a right outer join
USE TSQL2012;
GO
SELECT c.custid, c.companyname, o.orderid, o.orderdate
FROM Sales.Customers AS c
RIGHT OUTER JOIN Sales.Orders AS o
ON c.custid =o.custid;

-- Step 7: Join 2 tables
-- Select and execute the following query
-- to show orders without customers
USE TSQL2012;
GO
SELECT c.custid, c.companyname, o.orderid, o.orderdate
FROM Sales.Customers AS c
RIGHT OUTER JOIN Sales.Orders AS o
ON c.custid =o.custid
WHERE c.custid IS NULL;

CROSS JOIN

--  Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Join 2 tables
-- Select and execute the following query
-- to display all employees with managers
-- and the manager's ID and name.
 SELECT e.empid ,e.lastname as empname,e.title,e.mgrid, m.lastname as mgrname
  FROM HR.Employees AS e
  JOIN HR.Employees AS m
  ON e.mgrid=m.empid;

-- Step 3: Join 2 tables
-- Select and execute the following query
-- to display all employees
-- and the manager's ID and name.
  SELECT e.empid ,e.lastname as empname,e.title,e.mgrid, m.lastname as mgrname
  FROM HR.Employees AS e
  LEFT OUTER JOIN HR.Employees AS m
  ON e.mgrid=m.empid;

-- Step 4: Cross Join 2 tables
-- Select and execute the following query
-- to generate all combinations of first and last
-- names from the HR.Employees table
SELECT e1.firstname, e2.lastname
FROM HR.Employees AS e1 CROSS JOIN HR.Employees AS e2;

Sampling: TOP, OFFSET/FETCH

--  Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Use TOP to filter results
-- Most recent 5 orders
-- This will ignore rows with duplicate order dates
-- which would otherwise qualify
-- compare this to next example
SELECT TOP (5) orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

-- Step 3: Use TOP to filter results
-- Most recent 5 orders
-- This will include rows with duplicate order dates
-- which qualify as most recent
SELECT TOP (5) WITH TIES orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

-- Step 4: Use TOP to filter results
-- Top ten percent of rowcount by orderdate
SELECT TOP (10) PERCENT orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

-- Step 5: Use TOP to filter results
-- Point out that without an ORDER BY clause
-- SQL Server may return any 5 rows
SELECT TOP (5) orderid, custid, orderdate
FROM Sales.Orders;

-- Step 6: Use OFFSET/FETCH to filter results
-- OFFSET/FETCH examples
-- First 50 rows only as
-- alternative to SELECT TOP (50)
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY;

-- Step 7: Use OFFSET/FETCH to filter results
-- Skips first 50 rows, returns rows 51-100
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
ORDER BY orderdate, orderid DESC
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;

Handling NULL

--  Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

--  Step 2: Returning results including NULL
-- NULL handling examples
-- Show the presence of NULL in the region column
-- and ORDER BY sorting NULL together and first
SELECT custid, city, region, country
FROM Sales.Customers
ORDER BY region;

--  Step 3: Handling NULLs
-- This query eliminates NULLs in region
SELECT custid, city, region, country
FROM Sales.Customers
WHERE region <> N'SP';

--  Step 4:  Handling NULLs
-- This query also eliminates NULLs in region
SELECT custid, city, region, country
FROM Sales.Customers
WHERE region = N'SP';

--  Step 5:  Handling NULLs
-- Show how testing for NULL with an equality will
-- return an empty result set
-- might be misinterpreted as an absence of NULLs
SELECT custid, city, region, country
FROM Sales.Customers
WHERE region = NULL;

--  Step 6:  Handling NULLs
-- This query explicitly includes only NULLs
SELECT custid, city, region, country
FROM Sales.Customers
WHERE region IS NULL;

--  Step 7:  Handling NULLs
-- This query explicitly excludes NULLs
SELECT custid, city, region, country
FROM Sales.Customers
WHERE region IS NOT NULL;

Implicit / Explicit Type Conversion

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

--Step 2: Use implicit conversion in a query
--Demonstrate implicit conversion from the lower type (varchar)
-- to the higher (int)
SELECT 1 + '2' AS result;

--Step 3: Use implicit conversion in a query
--Demonstrate implicit conversion from the lower type (varchar)
-- to the higher (int)
--NOTE: THIS WILL FAIL

SELECT 1 + 'abc' AS result;

--Step 4: Use explicit conversion in a query

SELECT CAST(1 AS VARCHAR(10)) + 'abc' AS result;

Collation

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 2: Use collation in a query
SELECT empid, lastname
FROM HR.employees
WHERE lastname COLLATE Latin1_General_CS_AS = N'Funk';

-- Step 3: Use concatenation in a query
SELECT empid, lastname, firstname, firstname + N' ' + lastname AS fullname
FROM HR.Employees;

-- Step 4: Use string functions in a query
SELECT SUBSTRING('Microsoft SQL Server',11,3);
SELECT LEFT('Microsoft SQL Server',9);
SELECT RIGHT('Microsoft SQL Server',6);
SELECT LEN('Microsoft SQL Server     ');
SELECT DATALENGTH('Microsoft SQL Server     ');
SELECT CHARINDEX('SQL','Microsoft SQL Server');
SELECT REPLACE('Microsoft SQL Server Denali','Denali','2012');
SELECT UPPER('Microsoft SQL Server');
SELECT LOWER('Microsoft SQL Server');

-- Step 5: Use the LIKE predicate in a query
SELECT categoryid, categoryname, description
FROM Production.Categories
WHERE description LIKE 'Sweet%';

DATE and TIME

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Display various current date and time functions

SELECT
	GETDATE()			AS [GetDate],
	CURRENT_TIMESTAMP	AS [Current_Timestamp],
	GETUTCDATE()		AS [GetUTCDate],
	SYSDATETIME()		AS [SYSDateTime],
	SYSUTCDATETIME()	AS [SYSUTCDateTime],
	SYSDATETIMEOFFSET()	AS [SYSDateTimeOffset];

-- Step 3: Display various functions which return a portion of a date or time
SELECT DATENAME(year,'20120212');
SELECT DAY('20120212') AS [Day], MONTH('20120212') AS [Month],YEAR('20120212') AS [Year];

-- Step 4: Display various functions which return a date or time from parts
SELECT DATETIMEFROMPARTS(2012,2,12,8,30,0,0) AS Result; --7 arguments
SELECT DATETIME2FROMPARTS(2012,2,12,8,30,00,0,0) AS Result; -- 8 arguments
SELECT DATEFROMPARTS(2012,2,12) AS Result; -- 3args
SELECT DATETIMEOFFSETFROMPARTS(2012,2,12,8,30,0,0,-7,0,0) AS Result;

-- Step 5: Demonstrate DATEDIFF with  this to show difference in precision:
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

-- Step 6: Use ISDATE to check validity of inputs:
SELECT ISDATE('20120212'); --is valid
SELECT ISDATE('20120230'); --February doesn't have 30 days

Functions: Scalar, Aggregate, RANK()

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 2: Select and execute the following queries to illustrate
-- scalar functions

SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders;

SELECT ABS(-1.0), ABS(0.0), ABS(1.0);

SELECT CAST(SYSDATETIME() AS DATE) AS [current_date];

SELECT DB_NAME() AS [Current Database];

-- Step 3: Select and execute the following query to illustrate
-- a simple Aggregate function demo without GROUP BY
-- (GROUP BY will be covered in a later module)
SELECT COUNT(*) AS numorders, SUM(unitprice) AS totalsales
FROM	Sales.OrderDetails;

-- Step 4: Select and execute the following query to illustrate
-- a simple ranking function
SELECT TOP(5) productid, productname, unitprice,
	RANK() OVER(ORDER BY unitprice DESC) AS rankbyprice
FROM Production.Products
ORDER BY rankbyprice;

functions: CONVERT, PARSE, TRY_PARSE

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 2: Select and execute the following query to illustrate
-- the CAST function
-- This will succeed
SELECT CAST(SYSDATETIME() AS DATE);

-- Step 3: Select and execute the following query to illustrate
-- the CAST function
-- THIS WILL FAIL
SELECT CAST(SYSDATETIME() AS INT);

-- Step 4: Select and execute the following query to illustrate
-- the CONVERT function
-- This will succeed at converting datetime2 to date
SELECT CONVERT(DATE, SYSDATETIME());

-- THIS WILL FAIL at converting datetime2 to int
SELECT CONVERT(INT, SYSDATETIME());

-- Step 5: Select and execute the following query to illustrate
-- CONVERT with datetime data and a style option
SELECT  CONVERT(datetime, '20120212', 102) AS ANSI_style ;
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP,112) AS ISO_style;

-- Step 6: Select and execute the following query to illustrate
-- PARSE converting a string date to a US-style date
SELECT PARSE('01/02/2012' AS datetime2 USING 'en-US') AS parse_result;

-- Step 7: Select and execute the following query to illustrate
-- PARSE converting a string date to a UK-style date
SELECT PARSE('01/02/2012' AS datetime2 USING 'en-GB') AS parse_result;

-- Step 8: Select and execute the following query to illustrate
-- TRY_PARSE compared to PARSE
-- THIS WILL FAIL
SELECT PARSE('SQLServer' AS datetime2 USING 'en-US') AS parse_result;
-- This will succeed
SELECT TRY_PARSE('SQLServer' AS datetime2 USING 'en-US') AS try_parse_result;

Functions: ISNUMERIC, IIF, CHOOSE

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Select and execute the following query to illustrate
--the ISNUMERIC function with a character input
SELECT ISNUMERIC('SQL') AS isnmumeric_result;

-- Step 3: Select and execute the following query to illustrate
--the ISNUMERIC function with a float input
SELECT ISNUMERIC('1E3') AS isnumeric_result;

-- Step 4: Select and execute the following query to illustrate
--the IIF Function
SELECT 	productid, unitprice, IIF(unitprice > 50, 'high','low') AS pricepoint
FROM Production.Products;

-- Step 5: Select and execute the following query to illustrate
--the CHOOSE function
SELECT CHOOSE (3, 'Beverages', 'Condiments', 'Confections') AS choose_result;

Functions: ISNULL, COALESCE, NULLIF

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Select and execute the following query to illustrate
-- The ISNULL function
SELECT custid, city, ISNULL(region, 'N/A') AS region, country
FROM Sales.Customers;

-- Step 3: Select and execute the following query to illustrate the
-- COALESCE function
SELECT	custid, country, region, city,
			country + ',' + COALESCE(region, ' ') + ', ' + city as location
FROM Sales.Customers;

--  Step 4: Select and execute the following queries to illustrate the
-- NULLIF function

-- First, set up sample data
CREATE TABLE dbo.employee_goals(emp_id INT , goal int, actual int);

GO
-- Populate the sample data
INSERT INTO dbo.employee_goals
VALUES(1,100, 110), (2,90, 90), (3,100, 90), (4,100, 80);

-- Show the sample data
SELECT emp_id, goal, actual
FROM dbo.employee_goals;

-- Use NULLIF to show which employees have actual
-- values different from their goals
SELECT emp_id, NULLIF(actual,goal) AS actual_if_different
FROM dbo.employee_goals;

--  Step 5: Clean up demo table
DROP TABLE dbo.employee_goals;

GROUP BY… HAVING…

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Using the HAVING clause
-- Select and execute the following query to show
-- The use of a HAVING clause. This query has no HAVING clause:
SELECT custid, COUNT(*) AS count_orders
FROM Sales.Orders
GROUP BY custid;

-- This query uses a HAVING clause to filter out customers with fewer than 10 orders
SELECT custid, COUNT(*) AS count_orders
FROM Sales.Orders
GROUP BY custid
HAVING COUNT(*) >= 10

-- Review the logical order of operations
-- the column alias for COUNT(*) hasn't been processed yet
-- when HAVING refers to it
-- THIS WILL FAIL
SELECT custid, COUNT(*) AS count_orders
FROM Sales.Orders
GROUP BY custid
HAVING count_orders >= 10

-- Select and execute the following queries to show
-- difference between WHERE filter and HAVING filter:
-- The following query uses a WHERE clause to filter
-- orders

SELECT COUNT(*) AS cnt, AVG(qty) AS [avg_qty]
FROM Production.Products AS p
JOIN Sales.OrderDetails AS od
	ON p.productid = od.productid
WHERE od.qty > 20
GROUP BY p.categoryid;

-- This query uses a HAVING clause to filter groups
-- with an average quantity > 20
SELECT COUNT(*) AS cnt, AVG(qty) AS [avg_qty]
FROM Production.Products AS p
JOIN Sales.OrderDetails AS od
	ON p.productid = od.productid
GROUP BY p.categoryid
HAVING AVG(qty) > 20;

-- Select and execute the following query to show
-- All customers and how many orders they have placed
-- 89 rows - note custid 13
SELECT c.custid, COUNT(*) AS cnt
FROM Sales.Customers AS c
JOIN Sales.Orders AS o
ON c.custid = o.custid
GROUP BY c.custid
ORDER BY cnt DESC;

-- Use HAVING to filter only customers who have placed more than one order
SELECT c.custid, COUNT(*) AS cnt
FROM Sales.Customers AS c
JOIN Sales.Orders AS o
ON c.custid = o.custid
GROUP BY c.custid
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

-- Select and execute the following query to show
-- All products and in how many orders they appear
-- 77 rows, note bottom of list
SELECT p.productid, COUNT(*) AS cnt
FROM Production.Products AS p
JOIN Sales.OrderDetails AS od
ON p.productid = od.productid
GROUP BY p.productid
ORDER BY cnt DESC;

-- Use HAVING to filter only products which have been ordered 10 or more times:
-- 71 rows returned
SELECT p.productid, COUNT(*) AS cnt
FROM Production.Products AS p
JOIN Sales.OrderDetails AS od
ON p.productid = od.productid
GROUP BY p.productid
HAVING COUNT(*) >= 10
ORDER BY cnt DESC;

sub-query

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 1: Scalar subqueres:
-- Select this query and execute it to
-- obtain most recent order
SELECT MAX(orderid) AS lastorder
FROM Sales.Orders;

-- Select this query and execute it to
-- find details in Sales.OrderDetails
-- for most recent order
SELECT orderid, productid, unitprice, qty
FROM Sales.OrderDetails
WHERE orderid =
 (SELECT MAX(orderid) AS lastorder
 FROM Sales.Orders);

-- THIS WILL FAIL, since
-- subquery returns more than
-- 1 value
SELECT orderid, productid, unitprice, qty
FROM Sales.OrderDetails
WHERE orderid =
 (SELECT orderid AS O
 FROM Sales.Orders
 WHERE empid =2);

-- Step 3: Multi-valued subqueries
-- Select this query and execute it to
-- return order info for customers in Mexico
SELECT custid, orderid
FROM Sales.orders
WHERE custid IN (
 SELECT custid
 FROM Sales.Customers
 WHERE country = N'Mexico');

-- Same result expressed as a join:
SELECT c.custid, o.orderid
FROM Sales.Customers AS c JOIN Sales.Orders AS o
ON c.custid = o.custid
WHERE c.country = N'Mexico';

EXISTS or NOT EXISTS

--  Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Using EXISTS
-- Select this query and execute it to show
-- any customer who placed an order
SELECT custid, companyname
FROM Sales.Customers AS c
WHERE EXISTS (
	SELECT *
	FROM Sales.Orders AS o
	WHERE c.custid=o.custid);

-- Step 3: Using NOT EXISTS
-- Return any customer who has not placed an order
SELECT custid, companyname
FROM Sales.Customers AS c
WHERE NOT EXISTS (
	SELECT *
	FROM Sales.Orders AS o
	WHERE c.custid=o.custid);

-- Step 4: Compare COUNT(*)>0 to EXISTS:
-- Use COUNT(*) > 0
SELECT empid, lastname
FROM HR.Employees AS e
WHERE (SELECT COUNT(*)
		FROM Sales.Orders AS O
		WHERE O.empid = e.empid)>0;
-- Use EXISTS
SELECT empid, lastname
FROM HR.Employees AS e
WHERE EXISTS(	SELECT *
		FROM Sales.Orders AS O
		WHERE O.empid = e.empid);

User Defined Function (UDF)

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Using functions
-- Select and execute the following to
-- demonstrate using the sample function
-- Note: dbo.GetNums() takes as parameters: @low (bigint) and (@high) bigint
SELECT * FROM dbo.GetNums(10,20);
GO

-- Step 3: Creating simple functions
-- Select and execute the following to
-- Create a function to calculate line extension for orders
CREATE FUNCTION Sales.fn_LineTotal ( @orderid INT )
RETURNS TABLE
AS
RETURN
    SELECT  orderid, productid, unitprice, qty, discount,
            CAST(( qty * unitprice * ( 1 - discount ) ) AS DECIMAL(8, 2)) AS line_total
    FROM    Sales.OrderDetails
    WHERE   orderid = @orderid ;
GO
-- Use the function
SELECT orderid, productid, unitprice, qty, discount, line_total
FROM Sales.fn_LineTotal(10252) AS LT;
GO

-- Step 4: Cleanup
DROP FUNCTION Sales.fn_LineTotal;
GO

Derived Tables, Column Aliases

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Column aliases in derived tables
-- Select this query and execute it to show internal column aliases
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS derived_year
GROUP BY orderyear;

-- Select this query and execute it to show external column aliases
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate), custid
FROM Sales.Orders) AS derived_year(orderyear, custid)
GROUP BY orderyear;

-- Step 3: Use a local variable as a parameter for a derived table
DECLARE @emp_id INT = 9;
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (
	SELECT YEAR(orderdate) AS orderyear, custid
	FROM Sales.Orders
	WHERE empid=@emp_id
) AS derived_year
GROUP BY orderyear;

-- Step 4: Nesting derived tables
SELECT orderyear, cust_count
FROM  (
	SELECT  orderyear, COUNT(DISTINCT custid) AS cust_count
	FROM (
		SELECT YEAR(orderdate) AS orderyear ,custid
        FROM Sales.Orders) AS derived_table_1
	GROUP BY orderyear) AS derived_table_2
WHERE cust_count > 80;

-- An alternative to nesting, at the cost of restating the COUNT expression
-- in a having clause
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
	FROM (
		SELECT YEAR(orderdate) AS orderyear ,custid
        FROM Sales.Orders) AS derived_table_1
	GROUP BY orderyear
HAVING COUNT(DISTINCT custid) > 80;

CTE: Common Table Extension

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 2: Common Table Expressions
-- -- Select this query and execute it to show CTE Examples
WITH CTE_year AS
	(
	SELECT YEAR(orderdate) AS orderyear, custid
	FROM Sales.Orders
	)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;

-- Step 3 Recursive CTE
WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
	FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees

UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
	FROM EmpOrg_CTE AS parent
	JOIN HR.Employees AS child
	ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;

UNION ALL vs UNION


-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Using UNION ALL
-- Select this query and execute it to show the use of
-- UNION ALL to return all rows from both tables
-- including duplicates
SELECT country, region, city FROM HR.Employees
UNION ALL -- 100 rows
SELECT country, region, city FROM Sales.Customers;

-- Step 3: Using UNION
-- Select this query and execute it to show the use of
-- UNION to return all rows from both tables
-- excluding duplicates
SELECT country, region, city FROM HR.Employees
UNION
SELECT country, region, city FROM Sales.Customers;

INTERSECT, EXCEPT

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Using INTERSECT
-- Select this query and execute it to show the use of
-- INTERSECT to return only rows found in both tables
SELECT country, region, city FROM HR.Employees
INTERSECT -- 3 distinct rows
SELECT country, region, city FROM Sales.Customers;

-- Step 3: Using EXCEPT
-- Return only rows from left table (Hr.Employees)
SELECT country, region, city FROM HR.Employees
EXCEPT
SELECT country, region, city FROM Sales.Customers;

--Reverse position of tables, return only rows from Sales.Customers
SELECT country, region, city FROM Sales.Customers
EXCEPT
SELECT country, region, city FROM HR.Employees;

Inline Table-valued Function, CROSS APPLY, OUTER APPLY

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Create inline Table-valued Function
-- Create a TVF to accept a supplier id and return
-- the top 3 most expensive products for that shipper
CREATE FUNCTION dbo.fn_TopProductsByShipper
(@supplierid int)
RETURNS TABLE
AS
RETURN
	SELECT TOP (3) productid, productname, unitprice
	FROM Production.Products
	WHERE supplierid = @supplierid
	ORDER BY unitprice DESC;
GO

-- Step 3: Test the function
SELECT * FROM dbo.fn_TopProductsByShipper(2);

-- Test with CROSS APPLY
SELECT S.supplierid, S.companyname,
	P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
CROSS APPLY dbo.fn_TopProductsByShipper(S.supplierid) AS P
ORDER BY S.supplierid ASC, P.unitprice DESC;

-- Step 4: Using OUTER APPLY
-- Find customers with no orders
SELECT C.custid, C.companyname
FROM Sales.Customers AS C --91 customers
LEFT OUTER JOIN Sales.Orders AS O -- 830 orders
ON C.custid = O.custid --832 results with NULL cust

-- Step 5: Using CROSS APPLY
-- Return 3 most recent orders per customer
SELECT C.custid, TopOrders.orderid, TopOrders.orderdate
FROM Sales.Customers AS C
CROSS APPLY
(SELECT TOP (3) orderid, orderdate
	FROM Sales.Orders AS O
	WHERE O.custid = C.custid
	ORDER BY orderdate DESC, orderid DESC) AS TopOrders;

-- Use OUTER APPLY to include customers with no orders
SELECT C.custid, TopOrders.orderid, TopOrders.orderdate
FROM Sales.Customers AS C
OUTER APPLY
(SELECT TOP (3) orderid, orderdate
	FROM Sales.Orders AS O
	WHERE O.custid = C.custid
	ORDER BY orderdate DESC, orderid DESC) AS TopOrders;

OVER clause

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Creating Views for OVER clause

-- Setup views for demo
IF OBJECT_ID('Production.CategorizedProducts','V') IS NOT NULL DROP VIEW Production.CategorizedProducts
GO
CREATE VIEW Production.CategorizedProducts
AS
    SELECT  Production.Categories.categoryid AS CatID,
			Production.Categories.categoryname AS CatName,
            Production.Products.productname AS ProdName,
            Production.Products.unitprice AS UnitPrice
    FROM    Production.Categories
            INNER JOIN Production.Products ON Production.Categories.categoryid=Production.Products.categoryid;
GO
IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
GO
CREATE VIEW Sales.CategoryQtyYear
AS
SELECT  c.categoryname AS Category,
        SUM(od.qty) AS Qty,
        YEAR(o.orderdate) AS Orderyear
FROM    Production.Categories AS c
        INNER JOIN Production.Products AS p ON c.categoryid=p.categoryid
        INNER JOIN Sales.OrderDetails AS od ON p.productid=od.productid
        INNER JOIN Sales.Orders AS o ON od.orderid=o.orderid
GROUP BY c.categoryname, YEAR(o.orderdate);
GO

-- Step 3: Using OVER with ordering
-- Rank products by price from high to low
SELECT CatID, CatName, ProdName, UnitPrice,
	RANK() OVER(ORDER BY UnitPrice DESC) AS PriceRank
FROM Production.CategorizedProducts
ORDER BY PriceRank;

-- Rank products by price in descending order in each category.
-- Note the ties.
SELECT CatID, CatName, ProdName, UnitPrice,
	RANK() OVER(PARTITION BY CatID ORDER BY UnitPrice DESC) AS PriceRank
FROM Production.CategorizedProducts
ORDER BY CatID;

-- Step 4: Use framing to create running total
-- Display a running total of quantity per product category.
-- This uses framing to set boundaries at the start
-- of the set and the current row, for each partition
SELECT Category, Qty, Orderyear,
	SUM(Qty) OVER (
		PARTITION BY category
		ORDER BY orderyear
		ROWS BETWEEN UNBOUNDED PRECEDING
		AND CURRENT ROW) AS RunningQty
FROM Sales.CategoryQtyYear;

-- Display a running total of quantity per year.
SELECT Category, Qty, Orderyear,
	SUM(Qty) OVER (
		PARTITION BY orderyear
		ORDER BY Category
		ROWS BETWEEN UNBOUNDED PRECEDING
		AND CURRENT ROW) AS RunningQty
FROM Sales.CategoryQtyYear;

-- Show both side-by-side per category and per-year

SELECT Category, Qty, Orderyear,
	SUM(Qty) OVER (PARTITION BY orderyear ORDER BY Category	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalByYear,
	SUM(Qty) OVER (PARTITION BY Category ORDER BY OrderYear	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalByCategory
FROM Sales.CategoryQtyYear
ORDER BY Orderyear, Category;

-- Step 5: Clean up
IF OBJECT_ID('Production.CategorizedProducts','V') IS NOT NULL DROP VIEW Production.CategorizedProducts
IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
GO

Inner Derived Table, PIVOT, UNPIVOT

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
GO
-- Step 2: Create view for inner derived table (for screen space/convenience)
CREATE VIEW Sales.CategoryQtyYear
AS
SELECT  c.categoryname AS Category,
        od.qty AS Qty,
        YEAR(o.orderdate) AS Orderyear
FROM    Production.Categories AS c
        INNER JOIN Production.Products AS p ON c.categoryid=p.categoryid
        INNER JOIN Sales.OrderDetails AS od ON p.productid=od.productid
        INNER JOIN Sales.Orders AS o ON od.orderid=o.orderid;
GO
-- Step 3: Test view, review data
SELECT  Category, Qty,Orderyear
FROM Sales.CategoryQtyYear;

-- Step 4: PIVOT and UNPIVOT

-- PIVOT categories on orderyear
SELECT  Category, [2006],[2007],[2008]
FROM    ( SELECT  Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D
    PIVOT(SUM(QTY) FOR orderyear IN ([2006],[2007],[2008])) AS pvt
ORDER BY Category;

-- Step 5: Setup for UNPIVOT demo
-- Pivot categories on orderyear, save to temp table
-- Create staging table to hold pivoted data
CREATE TABLE [Sales].[PivotedCategorySales](
	[Category] [nvarchar](15) NOT NULL,
	[2006] [int] NULL,
	[2007] [int] NULL,
	[2008] [int] NULL);
GO
-- Populate it by pivoting from view
INSERT INTO Sales.PivotedCategorySales (Category, [2006],[2007],[2008])
SELECT Category, [2006],[2007],[2008]
FROM (SELECT  Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D
    PIVOT(SUM(QTY) FOR orderyear IN ([2006],[2007],[2008]))AS p

-- Test staging table
SELECT Category, [2006],[2007],[2008]
FROM Sales.PivotedCategorySales;

-- Step 6: UNPIVOT
SELECT category, qty, orderyear
FROM Sales.PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2006],[2007],[2008])) AS unpvt;

-- Step 7: Clean up
IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
IF OBJECT_ID('Sales.PivotedCategorySales') IS NOT NULL DROP TABLE Sales.PivotedCategorySales
GO

CUBE, ROLLUP, GROUPING_ID

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 2: Setup objects for test
IF OBJECT_ID('Sales.CategorySales','V') IS NOT NULL DROP VIEW Sales.CategorySales
GO
CREATE VIEW Sales.CategorySales
AS
SELECT c.categoryname AS Category,
 o.empid AS Emp,
 o.custid AS Cust,
 od.qty AS Qty,
 YEAR(o.orderdate) AS Orderyear
FROM Production.Categories AS c
 INNER JOIN Production.Products AS p ON c.categoryid=p.categoryid
 INNER JOIN Sales.OrderDetails AS od ON p.productid=od.productid
 INNER JOIN Sales.Orders AS o ON od.orderid=o.orderid
WHERE c.categoryid IN (1,2,3) AND o.custid BETWEEN 1 AND 5; --limits results for slides
GO

-- Step 3: Show query without use of grouping sets
SELECT Category, NULL AS Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY category
UNION ALL
SELECT NULL, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY cust
UNION ALL
SELECT NULL, NULL, SUM(Qty) AS TotalQty
FROM Sales.CategorySales;

-- Step 4: Query with grouping sets
SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY
GROUPING SETS((Category),(Cust),())
ORDER BY Category, Cust;

-- Step 5: Query with CUBE
SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY CUBE(Category,Cust)
ORDER BY Category, Cust;

-- Step 6: With ROLLUP
SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY ROLLUP(Category,Cust)
ORDER BY Category, Cust;

-- Step 7: Using GROUPING_ID
SELECT GROUPING_ID(Category)AS grpCat, GROUPING_ID(Cust) AS grpCust,
 Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY CUBE(Category,Cust)
ORDER BY Category, Cust;

-- Step 8: Clean up
IF OBJECT_ID('Sales.CategorySales','V') IS NOT NULL DROP VIEW Sales.CategorySales
GO

PROCEDURE

-- Step 1: Open a new query window to the TSQL2012 database
USE [TSQL2012]
GO

-- Step 2: create basic procedure with single input parameter
CREATE PROCEDURE Production.ProductsbySuppliers
(@supplierid AS INT)
AS
SELECT  productid,
        productname,
        categoryid,
        unitprice,
        discontinued
FROM Production.Products
WHERE   supplierid = @supplierid
ORDER BY productid;
GO

-- Step 3: Test procedure
EXEC Production.ProductsbySuppliers @supplierid = 1;
GO

-- Step 4: Modify it to take a parameter for rows returned.
-- Note that a maximum default value for @numrows is supplied
-- to avoid breaking existing applications that 
-- don't pass the @numrows parameter
ALTER PROCEDURE Production.ProductsbySuppliers
(@supplierid AS INT, @numrows AS BIGINT = 9223372036854775807) --largest possible value for a bigint (9,223,372,036,854,775,807)
AS
SELECT  TOP (@numrows) productid,
        productname,
        categoryid,
        unitprice,
        discontinued
FROM Production.Products
WHERE   supplierid = @supplierid
ORDER BY productid;
GO

-- Step 4: Test procedure
EXEC Production.ProductsbySuppliers @supplierid = 1, @numrows = 2;

-- Step 5: Clean up
IF OBJECT_ID('Production.ProductsbySuppliers','P') IS NOT NULL DROP PROCEDURE Production.ProductsbySuppliers;

EXEC: dynamic SQL

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

--Step 2: Using EXEC to execute dynamic SQL
DECLARE @sqlstring AS VARCHAR(1000);
SET @sqlstring='SELECT empid, lastname FROM HR.employees;'
EXEC(@sqlstring);
GO
-- Step 3: Using sys.sp_executesql to execute dynamic SQL
-- Simple example with no parameters
DECLARE @sqlcode AS NVARCHAR(256) = N'SELECT GETDATE() AS dt';
EXEC sys.sp_executesql @statement = @sqlcode;
GO
-- Step 4: Example with a single input parameter
DECLARE @sqlstring AS NVARCHAR(1000);
DECLARE @empid AS INT;
SET @sqlstring=N'
	SELECT empid, lastname 
	FROM HR.employees
	WHERE empid=@empid;'
EXEC sys.sp_executesql 
	@statement = @sqlstring,
	@params=N'@empid AS INT',
	@empid = 5;
GO

Basic Control Flow: IF…THEN…, WHILE…

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Control of Flow
--IF..ELSE

IF OBJECT_ID('Production.ProdsByCategory','P') IS NULL
	PRINT 'Object does not exist';
ELSE
	DROP PROC Production.ProdsByCategory;
GO
-- Step 3: Examples from workbook
USE TSQL2012;
GO
IF OBJECT_ID('HR.Employees') IS NULL
BEGIN
	PRINT 'The specified object does not exist';
END;

IF OBJECT_ID('HR.Employees') IS NULL
BEGIN
	PRINT 'The specified object does not exist';
END
ELSE
BEGIN
	PRINT 'The specified object exists';
END;

-- Step 4: IF EXIST
IF EXISTS (SELECT * FROM Sales.EmpOrders WHERE empid =5)
	BEGIN
		PRINT 'Employee has associated orders';
	END;
GO


-- Step 5: WHILE
DECLARE @empid AS INT, @lname AS NVARCHAR(20);
SET @empid = 1
WHILE @empid <=5
	BEGIN
		SELECT @lname = lastname FROM HR.Employees
			WHERE empid = @empid;
		PRINT @lname;
		SET @empid += 1;
	END;

Erro Handling: TRY…CATCH…

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

--Step 2: Demonstrate basic TRY/CATCH structure
BEGIN TRY
	--do something wrong
	SELECT 100/0 AS 'Problem';
END TRY
BEGIN CATCH
	PRINT 'Code inside CATCH is executing';
END CATCH

--Step 3: Break it down into steps
PRINT 'Code before TRY block is executing';
BEGIN TRY
	PRINT '  The TRY Block is starting'
	--do something wrong
	SELECT 100/0 AS 'Problem';
	PRINT '  If we get here, there was no runtime error'
END TRY
BEGIN CATCH
	PRINT '  Code inside CATCH is starting';
	PRINT '  Code inside CATCH is ending';
END CATCH
PRINT 'Now outside the end of the CATCH block';

Error Handling 2: THROW

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 2: --Returning Error Information
BEGIN TRY
	SELECT 1/0; --generate error
END TRY
BEGIN CATCH
	SELECT 
		ERROR_NUMBER() AS errnum,
		ERROR_MESSAGE() AS errmsg,
		ERROR_SEVERITY() AS errsev,
		ERROR_PROCEDURE() AS errproc,
		ERROR_LINE() AS errline;
END CATCH;
-- Step 3: Use THROW to raise custom error
-- Important: Any previous line in the batch before THROW MUST be terminated
-- with a semicolon.
THROW 55000, 'The object does not exist.', 1;

-- Step 4: Show an error without using THROW
BEGIN TRY
	--do something wrong
	SELECT 100/0 AS 'Problem';
END TRY
BEGIN CATCH
	PRINT 'Code inside CATCH is beginning'
	PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(255));
END CATCH
-- Step 5: Use THROW to re-raise the error.
BEGIN TRY
	--do something wrong
	SELECT 100/0 AS 'Problem';
END TRY
BEGIN CATCH
	PRINT 'Code inside CATCH is beginning'
	PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(255));
	THROW;
END CATCH

Why you need TRANSACTION?

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Create a table to support the demonstrations
-- Clean up if the tables already exists
IF OBJECT_ID('dbo.SimpleOrderDetails','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrderDetails;
IF OBJECT_ID('dbo.SimpleOrders','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrders;
GO
CREATE TABLE dbo.SimpleOrders(
	orderid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	custid int NOT NULL FOREIGN KEY REFERENCES Sales.Customers(custid),
	empid int NOT NULL FOREIGN KEY REFERENCES HR.Employees(empid),
	orderdate datetime NOT NULL
);
GO
CREATE TABLE dbo.SimpleOrderDetails(
	orderid int NOT NULL FOREIGN KEY REFERENCES dbo.SimpleOrders(orderid),
	productid int NOT NULL FOREIGN KEY REFERENCES Production.Products(productid),
	unitprice money NOT NULL,
	qty smallint NOT NULL,
 CONSTRAINT PK_OrderDetails PRIMARY KEY (orderid, productid)
);
GO

-- Step 3: Execute a multi-statement batch with error 
-- NOTE: THIS STEP WILL CAUSE AN ERROR

BEGIN TRY
	INSERT INTO dbo.SimpleOrders(custid, empid, orderdate) VALUES (68,9,'2006-07-12');
	INSERT INTO dbo.SimpleOrders(custid, empid, orderdate) VALUES (88,3,'2006-07-15');
	INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty) VALUES (1, 2,15.20,20);
	INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty) VALUES (999,77,26.20,15);
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
END CATCH;

-- Step 4: Show that even with exception handling, 
-- partial success occurred and some rows were inserted 
SELECT  orderid, custid, empid, orderdate
FROM dbo.SimpleOrders;
SELECT  orderid, productid, unitprice, qty
FROM dbo.SimpleOrderDetails;


-- Step N: Clean up demonstration tables
IF OBJECT_ID('dbo.SimpleOrderDetails','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrderDetails;
IF OBJECT_ID('dbo.SimpleOrders','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrders;

The beauty of RDBMS: transaction

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Create a table to support the demonstrations
-- Clean up if the tables already exists
IF OBJECT_ID('dbo.SimpleOrderDetails','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrderDetails;
IF OBJECT_ID('dbo.SimpleOrders','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrders;
GO
CREATE TABLE dbo.SimpleOrders(
	orderid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	custid int NOT NULL FOREIGN KEY REFERENCES Sales.Customers(custid),
	empid int NOT NULL FOREIGN KEY REFERENCES HR.Employees(empid),
	orderdate datetime NOT NULL
);
GO
CREATE TABLE dbo.SimpleOrderDetails(
	orderid int NOT NULL FOREIGN KEY REFERENCES dbo.SimpleOrders(orderid),
	productid int NOT NULL FOREIGN KEY REFERENCES Production.Products(productid),
	unitprice money NOT NULL,
	qty smallint NOT NULL,
 CONSTRAINT PK_OrderDetails PRIMARY KEY (orderid, productid)
);
GO

-- Step 3: Create a transaction to wrap around insertion statements
-- to create a single unit of work
BEGIN TRY
	BEGIN TRANSACTION
		INSERT INTO dbo.SimpleOrders(custid, empid, orderdate) VALUES (68,9,'2006-07-12');
		INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty) VALUES (1, 2,15.20,20);
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
	ROLLBACK TRANSACTION
END CATCH;

-- Step 4: Verify success
SELECT  orderid, custid, empid, orderdate
FROM dbo.SimpleOrders;
SELECT  orderid, productid, unitprice, qty
FROM dbo.SimpleOrderDetails;

-- Step 5: Clear out rows from previous tests
DELETE FROM dbo.SimpleOrderDetails;
GO
DELETE FROM dbo.SimpleOrders;
GO

--Step 6: Execute with errors in data to test transaction handling
BEGIN TRY
	BEGIN TRANSACTION
		INSERT INTO dbo.SimpleOrders(custid, empid, orderdate) VALUES (68,9,'2006-07-15');
		INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty) VALUES (99, 2,15.20,20);
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
	ROLLBACK TRANSACTION
END CATCH;

-- Step 7: Verify that no partial results remain
SELECT  orderid, custid, empid, orderdate
FROM dbo.SimpleOrders;
SELECT  orderid, productid, unitprice, qty
FROM dbo.SimpleOrderDetails;


-- Step N: Clean up demonstration tables
IF OBJECT_ID('dbo.SimpleOrderDetails','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrderDetails;
IF OBJECT_ID('dbo.SimpleOrders','U') IS NOT NULL
	DROP TABLE dbo.SimpleOrders;

CURSOR

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: use a cursor to calculate maximum order date per employee

-- declare local variables for current and previous record elements
DECLARE
  @empid         AS INT      = NULL,
  @orderdate     AS DATETIME = NULL,
  @prevempid     AS INT      = NULL,
  @prevorderdate AS DATETIME = NULL;

-- declare table variables [BG: a table variable] to hold max order date per each employee
DECLARE @T AS TABLE
(
  empid        INT,
  maxorderdate DATETIME
);

-- declare cursor based on query that orders rows by empid and orderdate
DECLARE OrdersCursor CURSOR FAST_FORWARD FOR
  SELECT empid, orderdate
  FROM Sales.Orders
  ORDER BY empid, orderdate;

-- open cursor
OPEN OrdersCursor;

-- fetch first cursor record
FETCH NEXT FROM OrdersCursor INTO @empid, @orderdate;

-- as long as previous fetch found a record
WHILE @@fetch_status = 0
BEGIN
  -- if previous empid is different than current, previous was with the maximum date, then insert a row to @T
  IF @prevempid <> @empid
    INSERT INTO @T(empid, maxorderdate) VALUES(@prevempid, @prevorderdate);
  SELECT @prevempid = @empid, @prevorderdate = @orderdate;
  FETCH NEXT FROM OrdersCursor INTO @empid, @orderdate;
END

-- unless input table was empty (in which case empid is NULL), last record had maximum order date for last employee
IF @empid IS NOT NULL
  INSERT INTO @T(empid, maxorderdate) VALUES(@prevempid, @prevorderdate);

-- close and deallocate cursor
CLOSE OrdersCursor;
DEALLOCATE OrdersCursor;

-- show results
SELECT empid, maxorderdate
FROM @T;
GO

-- Step 3: Use a set-based operation calculate maximum order date per employee
SELECT empid, MAX(orderdate) AS maxorderdate
FROM Sales.Orders
GROUP BY empid
ORDER BY empid;

Simple Way: How query got executed?

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

--Display an estimated plan for a simple query
-- Step 2: Select the following query and click the Display Estimated Execution Plan toolbar button

SELECT orderid, custid, empid, orderdate 
FROM Sales.Orders;

-- Step 3: Elements of the plan, including the Clustered Index Scan and SELECT operators, the arrow and the costs

-- Display the actual execution plan for a simple query
-- Step 4: Select the following query and click the Include Actual Execution Plan toolbar button
SELECT orderid, custid, empid, orderdate 
FROM Sales.Orders;
-- Step 5: Execute the query (press F5 or click the Execute Query toolbar button) and click the Execution Plan tab in the Results pane.
-- Step 6: Point out the elements of the plan, including the Clustered Index Scan and SELECT operators, the arrow and the costs
-- Step 7: Turn off the Actual Execution Plan toggle by clicking the Include Actual Execution Plan toolbar button

--Display Query Performance data
-- Step 8: Enable STATISTICS TIME display by selecting and executing the following batch:
SET STATISTICS TIME ON;
GO
-- Step 9: Execute a simple SELECT query
SELECT orderid, custid, empid, orderdate 
FROM Sales.Orders;
-- Step 10: Click the Messages tab in the Results pane and review the results.
-- Step 11: Disable STATISTICS TIME display by selecting and executing the following batch:
SET STATISTICS TIME OFF;
GO 
-- Step 12: Enable STATISTICS IO display by selecting and executing the following batch:
SET STATISTICS IO ON;
GO
-- Step 13: Execute a simple SELECT query
SELECT orderid, custid, empid, orderdate 
FROM Sales.Orders;
-- Step 14: Click the Messages tab in the Results pane and review the results.
-- Step 15: Disable STATISTICS IO display by selecting and executing the following batch:
SET STATISTICS IO OFF;
GO

FULLTEXT search

-- Step 1: Open a new query window to the AdventureWorks2008R2 database
USE AdventureWorks2008R2;
GO

-- Step 2: 
-- Run queries in this section if needed to create FT catalog and indexes before testing queries
/*
-- Create FT Catalog if necessary
CREATE FULLTEXT CATALOG [AW2008FullTextCatalog]WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION [dbo]
GO
-- Create index on Production.Product
CREATE FULLTEXT INDEX ON [Production].[Product] KEY INDEX [PK_Product_ProductID] ON ([AW2008FullTextCatalog]) WITH (CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Name])
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ENABLE
GO

-- Create index on Production.ProductDescription
CREATE FULLTEXT INDEX ON Production.ProductDescription 
KEY INDEX PK_ProductDescription_ProductDescriptionID 
ON (AW2008FullTextCatalog) 
WITH (CHANGE_TRACKING AUTO);
GO

ALTER FULLTEXT INDEX ON Production.ProductDescription ADD (Description);
GO

ALTER FULLTEXT INDEX ON Production.ProductDescription ENABLE;
GO
*/

--  Step 3: Using fulltext queries

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription;
GO
-- Select and execute the following query to illustrate the use of
-- LIKE to find titles with 'bike' in them
-- Starting with 'bike' only
-- Should return 0 rows
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE Description LIKE 'bike%';
GO

-- With 'bike' appearing anywhere
-- Should return 16 rows
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE Description LIKE '%bike%';

GO
-- Select and execute the following query to illustrate the use of
-- CONTAINS to find all descriptions with 'bike'
-- Should return 14 rows
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,'bike');
GO

-- Show that CONTAINS matches words, not characters
-- This will return 4 rows
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,'bikes');
GO

-- Show the use of FORMSOF to match
-- forms of a search term
-- Should return 8 rows
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,'FORMSOF(INFLECTIONAL,race)');
GO

-- Show the use of NEAR to add functionality 
-- beyond what LIKE could achieve
SELECT ProductDescriptionID
      ,Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,'bike NEAR frame');

-- If time and interest permit, show this example of
-- a word filtered by the stop list
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,'you');
GO

-- execute the following query to show the presence of 
-- the word "you" in the noise list
SELECT * FROM sys.dm_fts_parser (' "you" ', 1033, 0, 0)

-- FREETEXT Example
-- Show how FREETEXT returns rows including any
-- of the input terms, no OR operator required.

SELECT ProductID, Name
FROM Production.Product
WHERE FREETEXT(Name, 'flat nut');

-- Returns instance of "entry", "level" and 
-- "entry-level"
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,'entry level');

FOR XML

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Test FOR XML RAW clause
-- Select and execute the following queries to illustrate the use of
-- FOR XML RAW
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML RAW;

-- Select and execute the following query to illustrate the use of
-- adding a named element to row
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML RAW('Order');

-- Select and execute the following query to illustrate the use of
-- FOR XML RAW with ELEMENTS instead of ATTRIBUTES
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML RAW, ELEMENTS;

-- Select and execute the following query to illustrate the use of
-- FOR XML RAW with ELEMENTS and named row
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML RAW('Order'), ELEMENTS;

-- Select and execute the following query to illustrate the use of
-- FOR XML RAW with a named root element and named row
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML RAW('Order'), ROOT('Orders');

-- Step 3: Demonstrate the use of the FOR XML AUTO clause
-- Select and execute the following query to illustrate the use of
-- FOR XML AUTO
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML AUTO;

-- Cannot add named element to row
-- NOTE: THIS WILL FAIL.
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML AUTO('Order');

-- Step 4: Demonstrate the use of FOR XML AUTO with ELEMENTS instead of ATTRIBUTES
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML AUTO, ELEMENTS;

-- Select and execute the following query to illustrate the use of
-- FOR XML AUTO with ELEMENTS and a named root element
SELECT orderid, custid, orderdate, shipcountry
FROM Sales.Orders
FOR XML AUTO, ELEMENTS, ROOT('Orders');

-- Select and execute the following query to illustrate the use of
-- Using XPath to control formatting
SELECT orderid "@OrderID", custid "CustID", orderdate "OrdDate"
FROM Sales.Orders
FOR XML PATH('Order'), ELEMENTS, ROOT('Orders');

-- Compare a JOIN query using RAW versus AUTO
SELECT c.custid, c.companyname, c.country, o.orderid, o.orderdate
FROM Sales.Customers AS c
	JOIN Sales.Orders AS o
	ON c.custid = o.custid
FOR XML RAW, ELEMENTS, ROOT('CustomerOrders')

-- Select and execute the following query to illustrate the use of
-- FOR XML AUTO on a JOIN query
SELECT c.custid, c.companyname, c.country, o.orderid, o.orderdate
FROM Sales.Customers AS c
	JOIN Sales.Orders AS o
	ON c.custid = o.custid
FOR XML AUTO, ELEMENTS, ROOT('CustomerOrders')

-- Select and execute the following query to illustrate the use of
-- Including element placeholders for NULL
SELECT custid, region, country
FROM Sales.Customers
WHERE country IN('Mexico', 'Brazil')
FOR XML AUTO, ELEMENTS XSINIL, ROOT('Customers');

XQuery

-- Step 1: Open a new query window to the AdventureWorks2008R2 database
USE AdventureWorks2008R2;
GO
--Select and execute the following queries to illustrate 
--simple XQuery expressions


--Show JobCandidate table
--Click through into a Resume to show the XML
SELECT JobCandidateID, Resume
FROM HumanResources.JobCandidate;

--Extract email from each candidate
SELECT JobCandidateID, Resume.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
	(/ns:Resume/ns:EMail)[1]','nvarchar(20)') AS [email]
FROM HumanResources.JobCandidate;

--Use XQuery Query method to return
--Exmployment location data in XML format
--for candidates with experience in WA.
select JobCandidateID, Resume.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/ns:Resume/ns:Employment/ns:Emp.Location/ns:Location[ns:Loc.State="WA "]') AS WA_Emps
FROM HumanResources.JobCandidate;

Leave a comment

Design a site like this with WordPress.com
Get started