---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 1 - Background to T-SQL Querying and Programming
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Creating Tables
---------------------------------------------------------------------
-- Create a database called testdb
IF DB_ID('testdb') IS NULL
CREATE DATABASE testdb;
GO
-- Create table Employees
USE testdb;
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
hiredate DATE NOT NULL,
mgrid INT NULL,
ssn VARCHAR(20) NOT NULL,
salary MONEY NOT NULL
);
---------------------------------------------------------------------
-- Data Integrity
---------------------------------------------------------------------
-- Primary key
ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees
PRIMARY KEY(empid);
-- Unique
ALTER TABLE dbo.Employees
ADD CONSTRAINT UNQ_Employees_ssn
UNIQUE(ssn);
-- Foreign key
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL,
orderts DATETIME NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders
PRIMARY KEY(OrderID)
);
ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Employees
FOREIGN KEY(empid)
REFERENCES dbo.Employees(empid);
ALTER TABLE dbo.Employees
ADD CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid)
REFERENCES Employees(empid);
-- Check
ALTER TABLE dbo.Employees
ADD CONSTRAINT CHK_Employees_salary
CHECK(salary > 0);
-- Default
ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_orderts
DEFAULT(CURRENT_TIMESTAMP) FOR orderts;
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 1 - Background to T-SQL Querying and Programming
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Creating Tables
---------------------------------------------------------------------
-- Create a database called testdb
IF DB_ID('testdb') IS NULL
CREATE DATABASE testdb;
GO
-- Create table Employees
USE testdb;
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
hiredate DATE NOT NULL,
mgrid INT NULL,
ssn VARCHAR(20) NOT NULL,
salary MONEY NOT NULL
);
---------------------------------------------------------------------
-- Data Integrity
---------------------------------------------------------------------
-- Primary key
ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees
PRIMARY KEY(empid);
-- Unique
ALTER TABLE dbo.Employees
ADD CONSTRAINT UNQ_Employees_ssn
UNIQUE(ssn);
-- Foreign key
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL,
orderts DATETIME NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders
PRIMARY KEY(OrderID)
);
ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Employees
FOREIGN KEY(empid)
REFERENCES dbo.Employees(empid);
ALTER TABLE dbo.Employees
ADD CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid)
REFERENCES Employees(empid);
-- Check
ALTER TABLE dbo.Employees
ADD CONSTRAINT CHK_Employees_salary
CHECK(salary > 0);
-- Default
ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_orderts
DEFAULT(CURRENT_TIMESTAMP) FOR orderts;
--------------------------------------------------------
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 2 - Single-Table Queries
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- Return orders placed on June 2007
-- Tables involved: TSQLFundamentals2008 database, Sales.Orders table
-- Desired output:
orderid orderdate custid empid
----------- ----------------------- ----------- -----------
10555 2007-06-02 00:00:00.000 71 6
10556 2007-06-03 00:00:00.000 73 2
10557 2007-06-03 00:00:00.000 44 9
10558 2007-06-04 00:00:00.000 4 1
10559 2007-06-05 00:00:00.000 7 6
10560 2007-06-06 00:00:00.000 25 8
10561 2007-06-06 00:00:00.000 24 2
10562 2007-06-09 00:00:00.000 66 1
10563 2007-06-10 00:00:00.000 67 2
10564 2007-06-10 00:00:00.000 65 4
10565 2007-06-11 00:00:00.000 51 8
10566 2007-06-12 00:00:00.000 7 9
10567 2007-06-12 00:00:00.000 37 1
10568 2007-06-13 00:00:00.000 29 3
10569 2007-06-16 00:00:00.000 65 5
10570 2007-06-17 00:00:00.000 51 3
10571 2007-06-17 00:00:00.000 20 8
10572 2007-06-18 00:00:00.000 5 3
10573 2007-06-19 00:00:00.000 3 7
10574 2007-06-19 00:00:00.000 82 4
10575 2007-06-20 00:00:00.000 52 5
10576 2007-06-23 00:00:00.000 80 3
10577 2007-06-23 00:00:00.000 82 9
10578 2007-06-24 00:00:00.000 11 4
10579 2007-06-25 00:00:00.000 45 1
10580 2007-06-26 00:00:00.000 56 4
10581 2007-06-26 00:00:00.000 21 3
10582 2007-06-27 00:00:00.000 6 3
10583 2007-06-30 00:00:00.000 87 2
10584 2007-06-30 00:00:00.000 7 4
(30 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070601'
AND orderdate < '20070701';
-- 2 (Optional, Advanced)
-- Return orders placed on the last day of the month
-- Tables involved: Sales.Orders table
-- Desired output:
orderid orderdate custid empid
----------- ----------------------- ----------- -----------
10269 2006-07-31 00:00:00.000 89 5
10317 2006-09-30 00:00:00.000 48 6
10343 2006-10-31 00:00:00.000 44 4
10399 2006-12-31 00:00:00.000 83 8
10432 2007-01-31 00:00:00.000 75 3
10460 2007-02-28 00:00:00.000 24 8
10461 2007-02-28 00:00:00.000 46 1
10490 2007-03-31 00:00:00.000 35 7
10491 2007-03-31 00:00:00.000 28 8
10522 2007-04-30 00:00:00.000 44 4
10583 2007-06-30 00:00:00.000 87 2
10584 2007-06-30 00:00:00.000 7 4
10616 2007-07-31 00:00:00.000 32 1
10617 2007-07-31 00:00:00.000 32 4
10686 2007-09-30 00:00:00.000 59 2
10687 2007-09-30 00:00:00.000 37 9
10725 2007-10-31 00:00:00.000 21 4
10806 2007-12-31 00:00:00.000 84 3
10807 2007-12-31 00:00:00.000 27 4
10987 2008-03-31 00:00:00.000 19 8
10988 2008-03-31 00:00:00.000 65 3
10989 2008-03-31 00:00:00.000 61 2
11060 2008-04-30 00:00:00.000 27 2
11061 2008-04-30 00:00:00.000 32 4
11062 2008-04-30 00:00:00.000 66 4
11063 2008-04-30 00:00:00.000 37 3
(26 row(s) affected)
-- Solution
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');
-- 3
-- Return employees with last name containing the letter 'a' twice or more
-- Tables involved: HR.Employees table
-- Desired output:
empid firstname lastname
----------- ---------- --------------------
9 Zoya Dolgopyatova
(1 row(s) affected)
-- Solution
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE '%a%a%';
-- 4
-- Return orders with total value(qty*unitprice) greater than 10000
-- sorted by total value
-- Tables involved: Sales.OrderDetails table
-- Desired output:
orderid totalvalue
----------- ---------------------
10865 17250.00
11030 16321.90
10981 15810.00
10372 12281.20
10424 11493.20
10817 11490.70
10889 11380.00
10417 11283.20
10897 10835.24
10353 10741.60
10515 10588.50
10479 10495.60
10540 10191.70
10691 10164.80
(14 row(s) affected)
-- Solution
SELECT orderid, SUM(qty*unitprice) AS totalvalue
FROM Sales.OrderDetails
GROUP BY orderid
HAVING SUM(qty*unitprice) > 10000
ORDER BY totalvalue DESC;
-- 5
-- Return the three ship countries with the highest average freight in 2007
-- Tables involved: Sales.Orders table
-- Desired output:
shipcountry avgfreight
--------------- ---------------------
Austria 178.3642
Switzerland 117.1775
Sweden 105.16
(3 row(s) affected)
-- Solution
SELECT TOP(3) shipcountry, AVG(freight) AS avgfreight
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY shipcountry
ORDER BY avgfreight DESC;
-- 6
-- Calculate row numbers for orders
-- based on order date ordering (using order id as tiebreaker)
-- for each customer separately
-- Tables involved: Sales.Orders table
-- Desired output:
custid orderdate orderid rownum
----------- ----------------------- ----------- --------------------
1 2007-08-25 00:00:00.000 10643 1
1 2007-10-03 00:00:00.000 10692 2
1 2007-10-13 00:00:00.000 10702 3
1 2008-01-15 00:00:00.000 10835 4
1 2008-03-16 00:00:00.000 10952 5
1 2008-04-09 00:00:00.000 11011 6
2 2006-09-18 00:00:00.000 10308 1
2 2007-08-08 00:00:00.000 10625 2
2 2007-11-28 00:00:00.000 10759 3
2 2008-03-04 00:00:00.000 10926 4
...
(830 row(s) affected)
-- Solution
SELECT custid, orderdate, orderid,
ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid, rownum;
-- 7
-- Figure out and return for each employee the gender based on the title of courtesy
-- Ms., Mrs. - Female, Mr. - Male, Dr. - Unknown
-- Tables involved: HR.Employees table
-- Desired output:
empid firstname lastname titleofcourtesy gender
----------- ---------- -------------------- ------------------------- -------
1 Sara Davis Ms. Female
2 Don Funk Dr. Unknown
3 Judy Lew Ms. Female
4 Yael Peled Mrs. Female
5 Sven Buck Mr. Male
6 Paul Suurs Mr. Male
7 Russell King Mr. Male
8 Maria Cameron Ms. Female
9 Zoya Dolgopyatova Ms. Female
(9 row(s) affected)
-- Solutions
SELECT empid, firstname, lastname, titleofcourtesy,
CASE titleofcourtesy
WHEN 'Ms.' THEN 'Female'
WHEN 'Mrs.' THEN 'Female'
WHEN 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
-- 8
-- Return for each customer the customer ID and region
-- sort the rows in the output by region
-- having NULLs sort last (after non-NULL values)
-- Note that the default in T-SQL is that NULL sort first
-- Tables involved: Sales.Customers table
-- Desired output:
custid region
----------- ---------------
55 AK
10 BC
42 BC
45 CA
37 Co. Cork
33 DF
71 ID
38 Isle of Wight
46 Lara
78 MT
...
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
9 NULL
11 NULL
...
(91 row(s) affected)
-- Solution
SELECT custid, region
FROM Sales.Customers
ORDER BY
CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;
-----------------------------------------------
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 2 - Single-Table Queries
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Elements of SELECT Statement
---------------------------------------------------------------------
-- Listing 2-1: Sample Query
USE TSQLFundamentals2008;
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;
---------------------------------------------------------------------
-- The FROM Clause
---------------------------------------------------------------------
SELECT orderid, custid, empid, orderdate, freight
FROM Sales.Orders;
---------------------------------------------------------------------
-- The WHERE Clause
---------------------------------------------------------------------
SELECT orderid, empid, orderdate, freight
FROM Sales.Orders
WHERE custid = 71;
---------------------------------------------------------------------
-- The GROUP BY Clause
---------------------------------------------------------------------
SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
SELECT
empid,
YEAR(orderdate) AS orderyear,
SUM(freight) AS totalfreight,
COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
/*
SELECT empid, YEAR(orderdate) AS orderyear, freight
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
*/
SELECT
empid,
YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY empid, YEAR(orderdate);
---------------------------------------------------------------------
-- The HAVING Clause
---------------------------------------------------------------------
SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;
---------------------------------------------------------------------
-- The SELECT Clause
---------------------------------------------------------------------
SELECT orderid orderdate
FROM Sales.Orders;
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;
/*
SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE orderyear > 2006;
*/
SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE YEAR(orderdate) > 2006;
/*
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING numorders > 1;
*/
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;
-- Listing 2-2: Query Returning Duplicate Rows
SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71;
-- Listing 2-3: Query With a DISTINCT Clause
SELECT DISTINCT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71;
SELECT *
FROM Sales.Shippers;
/*
SELECT orderid,
YEAR(orderdate) AS orderyear,
orderyear + 1 AS nextyear
FROM Sales.Orders;
*/
SELECT orderid,
YEAR(orderdate) AS orderyear,
YEAR(orderdate) + 1 AS nextyear
FROM Sales.Orders;
---------------------------------------------------------------------
-- The ORDER BY Clause
---------------------------------------------------------------------
-- Listing 2-4: Query Demonstrating the ORDER BY Clause
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;
SELECT empid, firstname, lastname, country
FROM HR.Employees
ORDER BY hiredate;
/*
SELECT DISTINCT country
FROM HR.Employees
ORDER BY empid;
*/
---------------------------------------------------------------------
-- TOP
---------------------------------------------------------------------
-- Listing 2-5: Query Demonstrating the TOP Option
SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
-- Listing 2-6: Query Demonstrating TOP with Unique ORDER BY List
SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC;
SELECT TOP (5) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
---------------------------------------------------------------------
-- Ranking
---------------------------------------------------------------------
SELECT orderid, custid, val,
SUM(val) OVER() AS totalvalue,
SUM(val) OVER(PARTITION BY custid) AS custtotalvalue
FROM Sales.OrderValues;
SELECT orderid, custid, val,
100. * val / SUM(val) OVER() AS pctall,
100. * val / SUM(val) OVER(PARTITION BY custid) AS pctcust
FROM Sales.OrderValues;
SELECT orderid, custid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
NTILE(10) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;
SELECT orderid, custid, val,
ROW_NUMBER() OVER(PARTITION BY custid
ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;
SELECT DISTINCT val, ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM Sales.OrderValues;
SELECT val, ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM Sales.OrderValues
GROUP BY val;
---------------------------------------------------------------------
-- Predicates and Operators
---------------------------------------------------------------------
-- Predicates: IN, BETWEEN, LIKE
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid IN(10248, 10249, 10250);
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid BETWEEN 10300 AND 10310;
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';
-- Comparison operators: =, >, <, >=, <=, <>, !=, !>, !<
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20080101';
-- Logical operators: AND, OR, NOT
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20080101'
AND empid IN(1, 3, 5);
-- Arithmetic operators: +, -, *, /, %
SELECT orderid, productid, qty, unitprice, discount,
qty * unitprice * (1 - discount) AS val
FROM Sales.OrderDetails;
-- Operators Precedence
-- AND precedes OR
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE
custid = 1
AND empid IN(1, 3, 5)
OR custid = 85
AND empid IN(2, 4, 6);
-- Equivalent to
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE
( custid = 1
AND empid IN(1, 3, 5) )
OR
( custid = 85
AND empid IN(2, 4, 6) );
-- *, / precedes +, -
SELECT 10 + 2 * 3 -- 16
SELECT (10 + 2) * 3 -- 36
---------------------------------------------------------------------
-- CASE Expression
---------------------------------------------------------------------
-- Simple
SELECT productid, productname, categoryid,
CASE categoryid
WHEN 1 THEN 'Beverages'
WHEN 2 THEN 'Condiments'
WHEN 3 THEN 'Confections'
WHEN 4 THEN 'Dairy Products'
WHEN 5 THEN 'Grains/Cereals'
WHEN 6 THEN 'Meat/Poultry'
WHEN 7 THEN 'Produce'
WHEN 8 THEN 'Seafood'
ELSE 'Unknown Category'
END AS categoryname
FROM Production.Products;
SELECT orderid, custid, val,
CASE NTILE(3) OVER(ORDER BY val)
WHEN 1 THEN 'Low'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'High'
ELSE 'Unknown'
END AS titledesc
FROM Sales.OrderValues
ORDER BY val;
-- Searched
SELECT orderid, custid, val,
CASE
WHEN val < 1000.00 THEN 'Less then 1000'
WHEN val BETWEEN 1000.00 AND 3000.00 THEN 'Between 1000 and 3000'
WHEN val > 3000.00 THEN 'More than 3000'
ELSE 'Unknown'
END AS valuecategory
FROM Sales.OrderValues;
---------------------------------------------------------------------
-- NULLs
---------------------------------------------------------------------
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region = N'WA';
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region <> N'WA';
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region = NULL;
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region IS NULL;
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region <> N'WA'
OR region IS NULL;
---------------------------------------------------------------------
-- All-At-Once Operations
---------------------------------------------------------------------
/*
SELECT
orderid,
YEAR(orderdate) AS orderyear,
orderyear + 1 AS nextyear
FROM Sales.Orders;
*/
/*
SELECT col1, col2
FROM dbo.T1
WHERE col1 <> 0 AND col2/col1 > 2;
*/
/*
SELECT col1, col2
FROM dbo.T1
WHERE
CASE
WHEN col1 = 0 THEN 'no' -- or 'yes' if row should be returned
WHEN col2/col1 > 2 THEN 'yes'
ELSE 'no'
END = 'yes';
*/
/*
SELECT col1, col2
FROM dbo.T1
WHERE col1 <> 0 and col2 > 2*col1;
*/
---------------------------------------------------------------------
-- Working with Character Data
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Collation
---------------------------------------------------------------------
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = N'davis';
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_CS_AS = N'davis';
---------------------------------------------------------------------
-- Operators and Functions
---------------------------------------------------------------------
-- Concatenation
SELECT empid, firstname + N' ' + lastname AS fullname
FROM HR.Employees;
-- Listing 2-7: Query Demonstrating String Concatenation
SELECT custid, country, region, city,
country + N',' + region + N',' + city AS location
FROM Sales.Customers;
SET CONCAT_NULL_YIELDS_NULL OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT custid, country, region, city,
country + N',' + COALESCE(region, N'') + N',' + city AS location
FROM Sales.Customers;
-- Functions
SELECT SUBSTRING('abcde', 1, 3); -- 'abc'
SELECT RIGHT('abcde', 3); -- 'cde'
SELECT LEN(N'abcde'); -- 5
SELECT DATALENGTH(N'abcde'); -- 10
SELECT CHARINDEX(' ','Itzik Ben-Gan'); -- 6
SELECT PATINDEX('%[0-9]%', 'abcd123efgh'); -- 5
SELECT REPLACE('1-a 2-b', '-', ':'); -- '1:a 2:b'
SELECT empid, lastname,
LEN(lastname) - LEN(REPLACE(lastname, 'e', '')) AS numoccur
FROM HR.Employees;
SELECT REPLICATE('abc', 3); -- 'abcabcabc'
SELECT supplierid,
RIGHT(REPLICATE('0', 9) + CAST(supplierid AS VARCHAR(10)),
10) AS strsupplierid
FROM Production.Suppliers;
SELECT STUFF('xyz', 2, 1, 'abc'); -- 'xabcz'
SELECT UPPER('Itzik Ben-Gan'); -- 'ITZIK BEN-GAN'
SELECT LOWER('Itzik Ben-Gan'); -- 'itzik ben-gan'
SELECT RTRIM(LTRIM(' abc ')); -- 'abc'
---------------------------------------------------------------------
-- LIKE Predicate
---------------------------------------------------------------------
-- Last name starts with D
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';
-- Second character in last name is e
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'_e%';
-- First character in last name is A, B or C
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[ABC]%';
-- First character in last name is A through E
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[A-E]%';
-- First character in last name is not A through E
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[^A-E]%';
---------------------------------------------------------------------
-- Working with Date and Time Data
---------------------------------------------------------------------
-- Literals
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = '20070212';
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = CAST('20070212' AS DATETIME);
SET LANGUAGE British;
SELECT CAST('02/12/2007' AS DATETIME);
SET LANGUAGE us_english;
SELECT CAST('02/12/2007' AS DATETIME);
SET LANGUAGE British;
SELECT CAST('20070212' AS DATETIME);
SET LANGUAGE us_english;
SELECT CAST('20070212' AS DATETIME);
SELECT CONVERT(DATETIME, '02/12/2007', 101);
SELECT CONVERT(DATETIME, '02/12/2007', 103);
-- Working with Date and Time Separately
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = '20070212';
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070212'
AND orderdate < '20070213';
SELECT CAST('12:30:15.123' AS DATETIME);
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007;
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';
-- Functions
-- Current Date and Time
SELECT
GETDATE() AS [GETDATE],
CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],
GETUTCDATE() AS [GETUTCDATE],
SYSDATETIME() AS [SYSDATETIME],
SYSUTCDATETIME() AS [SYSUTCDATETIME],
SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];
SELECT
CAST(SYSDATETIME() AS DATE) AS [current_date],
CAST(SYSDATETIME() AS TIME) AS [current_time];
-- CAST and CONVERT
SELECT CAST('20090212' AS DATE);
SELECT CAST(SYSDATETIME() AS DATE);
SELECT CAST(SYSDATETIME() AS TIME);
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112);
SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS DATETIME);
SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114);
SELECT CAST(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) AS DATETIME);
-- SWITCHOFFSET
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00');
-- TODATETIMEOFFSET
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');
SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00');
-- DATEADD
SELECT DATEADD(year, 1, '20090212');
-- DATEDIFF
SELECT DATEDIFF(day, '20080212', '20090212');
SELECT
DATEADD(
day,
DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101');
SELECT
DATEADD(
month,
DATEDIFF(month, '20010101', CURRENT_TIMESTAMP), '20010101');
SELECT
DATEADD(
month,
DATEDIFF(month, '20091231', CURRENT_TIMESTAMP), '20091231');
-- DATEPART
SELECT DATEPART(month, '20090212');
-- DAY, MONTH, YEAR
SELECT
DAY('20090212') AS theday,
MONTH('20090212') AS themonth,
YEAR('20090212') AS theyear;
-- DATENAME
SELECT DATENAME(month, '20090212');
SELECT DATENAME(year, '20090212');
-- ISDATE
SELECT ISDATE('20090212');
SELECT ISDATE('20090230');
---------------------------------------------------------------------
-- Querying Metadata
---------------------------------------------------------------------
-- Catalog Views
USE TSQLFundamentals2008;
SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_name
FROM sys.tables;
SELECT
name AS column_name,
TYPE_NAME(system_type_id) AS column_type,
max_length,
collation_name,
is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(N'Sales.Orders');
-- Information Schema Views
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = N'BASE TABLE';
SELECT
COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
COLLATION_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'Sales'
AND TABLE_NAME = N'Orders';
-- System Stored Procedures and Functions
EXEC sys.sp_tables;
EXEC sys.sp_help
@objname = N'Sales.Orders';
EXEC sys.sp_columns
@table_name = N'Orders',
@table_owner = N'Sales';
EXEC sys.sp_helpconstraint
@objname = N'Sales.Orders';
SELECT
SERVERPROPERTY('ProductLevel');
SELECT
DATABASEPROPERTYEX(N'TSQLFundamentals2008', 'Collation')
SELECT
OBJECTPROPERTY(OBJECT_ID(N'Sales.Orders'), 'TableHasPrimaryKey');
SELECT
COLUMNPROPERTY(OBJECT_ID(N'Sales.Orders'), N'shipcountry', 'AllowsNull');
---------------------------------------------------
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 3 - Joins
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- 1-1
-- Run the following code to create the Nums auxiliary table
-- in the TSQLFundamentals2008 database:
-- Auxiliary table of nums
SET NOCOUNT ON;
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @i AS INT = 1;
BEGIN TRAN
WHILE @i <= 100000
BEGIN
INSERT INTO dbo.Nums VALUES(@i);
SET @i = @i + 1;
END
COMMIT TRAN
SET NOCOUNT OFF;
GO
-- 1-2
-- Write a query that generates 5 copies out of each employee row
-- Tables involved: TSQLFundamentals2008 database, Employees and Nums tables
--Desired output
empid firstname lastname n
----------- ---------- -------------------- -----------
1 Sara Davis 1
2 Don Funk 1
3 Judy Lew 1
4 Yael Peled 1
5 Sven Buck 1
6 Paul Suurs 1
7 Russell King 1
8 Maria Cameron 1
9 Zoya Dolgopyatova 1
1 Sara Davis 2
2 Don Funk 2
3 Judy Lew 2
4 Yael Peled 2
5 Sven Buck 2
6 Paul Suurs 2
7 Russell King 2
8 Maria Cameron 2
9 Zoya Dolgopyatova 2
1 Sara Davis 3
2 Don Funk 3
3 Judy Lew 3
4 Yael Peled 3
5 Sven Buck 3
6 Paul Suurs 3
7 Russell King 3
8 Maria Cameron 3
9 Zoya Dolgopyatova 3
1 Sara Davis 4
2 Don Funk 4
3 Judy Lew 4
4 Yael Peled 4
5 Sven Buck 4
6 Paul Suurs 4
7 Russell King 4
8 Maria Cameron 4
9 Zoya Dolgopyatova 4
1 Sara Davis 5
2 Don Funk 5
3 Judy Lew 5
4 Yael Peled 5
5 Sven Buck 5
6 Paul Suurs 5
7 Russell King 5
8 Maria Cameron 5
9 Zoya Dolgopyatova 5
(45 row(s) affected)
-- Solution
SELECT E.empid, E.firstname, E.lastname, Nums.n
FROM HR.Employees AS E
CROSS JOIN dbo.Nums
WHERE Nums.n <= 5
ORDER BY n, empid;
-- 1-3
-- c. (Advanced, Optional)
-- Write a query that returns a row for each employee and day
-- in the range June 12, 2009 – June 16 2009.
-- Tables involved: TSQLFundamentals2008 database, Employees and Nums tables
select * from HR.Employees
--Desired output
empid dt
----------- -----------------------
1 2009-06-12 00:00:00.000
1 2009-06-13 00:00:00.000
1 2009-06-14 00:00:00.000
1 2009-06-15 00:00:00.000
1 2009-06-16 00:00:00.000
2 2009-06-12 00:00:00.000
2 2009-06-13 00:00:00.000
2 2009-06-14 00:00:00.000
2 2009-06-15 00:00:00.000
2 2009-06-16 00:00:00.000
3 2009-06-12 00:00:00.000
3 2009-06-13 00:00:00.000
3 2009-06-14 00:00:00.000
3 2009-06-15 00:00:00.000
3 2009-06-16 00:00:00.000
4 2009-06-12 00:00:00.000
4 2009-06-13 00:00:00.000
4 2009-06-14 00:00:00.000
4 2009-06-15 00:00:00.000
4 2009-06-16 00:00:00.000
5 2009-06-12 00:00:00.000
5 2009-06-13 00:00:00.000
5 2009-06-14 00:00:00.000
5 2009-06-15 00:00:00.000
5 2009-06-16 00:00:00.000
6 2009-06-12 00:00:00.000
6 2009-06-13 00:00:00.000
6 2009-06-14 00:00:00.000
6 2009-06-15 00:00:00.000
6 2009-06-16 00:00:00.000
7 2009-06-12 00:00:00.000
7 2009-06-13 00:00:00.000
7 2009-06-14 00:00:00.000
7 2009-06-15 00:00:00.000
7 2009-06-16 00:00:00.000
8 2009-06-12 00:00:00.000
8 2009-06-13 00:00:00.000
8 2009-06-14 00:00:00.000
8 2009-06-15 00:00:00.000
8 2009-06-16 00:00:00.000
9 2009-06-12 00:00:00.000
9 2009-06-13 00:00:00.000
9 2009-06-14 00:00:00.000
9 2009-06-15 00:00:00.000
9 2009-06-16 00:00:00.000
(45 row(s) affected)
dateadd(-- Solution
SELECT E.empid,
DATEADD(day, D.n - 1, '20090612') AS dt
FROM HR.Employees AS E
CROSS JOIN Nums AS D
WHERE D.n <= DATEDIFF(day, '20090612', '20090616') + 1
ORDER BY empid, dt;
select * from hr.Employees
select * from nums
-- 2
-- Return US customers, and for each customer the total number of orders
-- and total quantities.
-- Tables involved: TSQLFundamentals2008 database, Customers, Orders and OrderDetails tables
--Desired output
custid numorders totalqty
----------- ----------- -----------
32 11 345
36 5 122
43 2 20
45 4 181
48 8 134
55 10 603
65 18 1383
71 31 4958
75 9 327
77 4 46
78 3 59
82 3 89
89 14 1063
(13 row(s) affected)
-- Solution
SELECT C.custid, COUNT(DISTINCT O.orderid) AS numorders, SUM(OD.qty) AS totalqty
FROM Sales.Customers AS C
JOIN Sales.Orders AS O
ON O.custid = C.custid
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
WHERE C.country = N'USA'
GROUP BY C.custid;
-- 3
-- Return customers and their orders including customers who placed no orders
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- ------------------------
85 Customer ENQZT 10248 2006-07-04 00:00:00.000
79 Customer FAPSM 10249 2006-07-05 00:00:00.000
34 Customer IBVRG 10250 2006-07-08 00:00:00.000
84 Customer NRCSK 10251 2006-07-08 00:00:00.000
...
73 Customer JMIKW 11074 2008-05-06 00:00:00.000
68 Customer CCKOT 11075 2008-05-06 00:00:00.000
9 Customer RTXGC 11076 2008-05-06 00:00:00.000
65 Customer NYUHS 11077 2008-05-06 00:00:00.000
22 Customer DTDMN NULL NULL
57 Customer WVAXS NULL NULL
(832 row(s) affected)
-- Solution
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid;
-- 4
-- Return customers who placed no orders
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname
----------- ---------------
22 Customer DTDMN
57 Customer WVAXS
(2 row(s) affected)
-- Solution
SELECT C.custid, C.companyname
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid
WHERE O.orderid IS NULL;
-- 5
-- Return customers with orders placed on Feb 12, 2007 along with their orders
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- -----------------------
66 Customer LHANT 10443 2007-02-12 00:00:00.000
5 Customer HGVLZ 10444 2007-02-12 00:00:00.000
(2 row(s) affected)
-- Solution
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
JOIN Sales.Orders AS O
ON O.custid = C.custid
WHERE O.orderdate = '20070212';
-- 6 (Optional, Advanced)
-- Return customers with orders placed on Feb 12, 2007 along with their orders
-- Also return customers who didn't place orders on Feb 12, 2007
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- -----------------------
72 Customer AHPOP NULL NULL
58 Customer AHXHT NULL NULL
25 Customer AZJED NULL NULL
18 Customer BSVAR NULL NULL
91 Customer CCFIZ NULL NULL
...
33 Customer FVXPQ NULL NULL
53 Customer GCJSG NULL NULL
39 Customer GLLAG NULL NULL
16 Customer GYBBY NULL NULL
4 Customer HFBZG NULL NULL
5 Customer HGVLZ 10444 2007-02-12 00:00:00.000
42 Customer IAIJK NULL NULL
34 Customer IBVRG NULL NULL
63 Customer IRRVL NULL NULL
73 Customer JMIKW NULL NULL
15 Customer JUWXK NULL NULL
...
21 Customer KIDPX NULL NULL
30 Customer KSLQF NULL NULL
55 Customer KZQZT NULL NULL
71 Customer LCOUJ NULL NULL
77 Customer LCYBZ NULL NULL
66 Customer LHANT 10443 2007-02-12 00:00:00.000
38 Customer LJUCA NULL NULL
59 Customer LOLJO NULL NULL
36 Customer LVJSO NULL NULL
64 Customer LWGMD NULL NULL
29 Customer MDLWA NULL NULL
...
(91 row(s) affected)
-- Solution
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid
AND O.orderdate = '20070212';
-- 7 (Optional, Advanced)
-- Return all customers, and for each return a Yes/No value
-- depending on whether the customer placed an order on Feb 12, 2007
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname HasOrderOn20070212
----------- --------------- ------------------
1 Customer NRZBB No
2 Customer MLTDN No
3 Customer KBUDE No
4 Customer HFBZG No
5 Customer HGVLZ Yes
6 Customer XHXJV No
7 Customer QXVLA No
8 Customer QUHWH No
9 Customer RTXGC No
10 Customer EEALV No
...
(91 row(s) affected)
-- Solution
SELECT DISTINCT C.custid, C.companyname,
CASE WHEN O.orderid IS NOT NULL THEN 'Yes' ELSE 'No' END AS [HasOrderOn20070212]
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid
AND O.orderdate = '20070212';
---------------------------------------------------------
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 3 - Joins
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- CROSS Joins
---------------------------------------------------------------------
-- ANSI SQL-92
USE TSQLFundamentals2008;
SELECT C.custid, E.empid
FROM Sales.Customers AS C
CROSS JOIN HR.Employees AS E;
-- ANSI SQL-89
SELECT C.custid, E.empid
FROM Sales.Customers AS C, HR.Employees AS E;
-- Self Cross-Join
SELECT
E1.empid, E1.firstname, E1.lastname,
E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1
CROSS JOIN HR.Employees AS E2;
GO
-- All numbers from 1 - 1000
-- Auxiliary table of digits
USE tempdb;
IF OBJECT_ID('dbo.Digits', 'U') IS NOT NULL DROP TABLE dbo.Digits;
CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.Digits(digit)
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
/*
Note:
Above INSERT syntax is new in Microsoft SQL Server 2008.
In earlier versions use:
INSERT INTO dbo.Digits(digit) VALUES(0);
INSERT INTO dbo.Digits(digit) VALUES(1);
INSERT INTO dbo.Digits(digit) VALUES(2);
INSERT INTO dbo.Digits(digit) VALUES(3);
INSERT INTO dbo.Digits(digit) VALUES(4);
INSERT INTO dbo.Digits(digit) VALUES(5);
INSERT INTO dbo.Digits(digit) VALUES(6);
INSERT INTO dbo.Digits(digit) VALUES(7);
INSERT INTO dbo.Digits(digit) VALUES(8);
INSERT INTO dbo.Digits(digit) VALUES(9);
*/
SELECT digit FROM dbo.Digits;
GO
-- All numbers from 1 - 1000
SELECT D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS n
FROM dbo.Digits AS D1
CROSS JOIN dbo.Digits AS D2
CROSS JOIN dbo.Digits AS D3
ORDER BY n;
---------------------------------------------------------------------
-- INNER Joins
---------------------------------------------------------------------
-- ANSI SQL-92
USE TSQLFundamentals2008;
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O
ON E.empid = O.empid;
-- ANSI SQL-89
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E, Sales.Orders AS O
WHERE E.empid = O.empid;
GO
-- Inner Join Safety
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O;
GO
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E, Sales.Orders AS O;
GO
---------------------------------------------------------------------
-- Further Join Examples
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Composite Joins
---------------------------------------------------------------------
-- Audit table for updates against OrderDetails
USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.OrderDetailsAudit', 'U') IS NOT NULL
DROP TABLE Sales.OrderDetailsAudit;
CREATE TABLE Sales.OrderDetailsAudit
(
lsn INT NOT NULL IDENTITY,
orderid INT NOT NULL,
productid INT NOT NULL,
dt DATETIME NOT NULL,
loginname sysname NOT NULL,
columnname sysname NOT NULL,
oldval SQL_VARIANT,
newval SQL_VARIANT,
CONSTRAINT PK_OrderDetailsAudit PRIMARY KEY(lsn),
CONSTRAINT FK_OrderDetailsAudit_OrderDetails
FOREIGN KEY(orderid, productid)
REFERENCES Sales.OrderDetails(orderid, productid)
);
SELECT OD.orderid, OD.productid, OD.qty,
ODA.dt, ODA.loginname, ODA.oldval, ODA.newval
FROM Sales.OrderDetails AS OD
JOIN Sales.OrderDetailsAudit AS ODA
ON OD.orderid = ODA.orderid
AND OD.productid = ODA.productid
WHERE ODA.columnname = N'qty';
---------------------------------------------------------------------
-- Non-Equi Joins
---------------------------------------------------------------------
-- Unique pairs of employees
SELECT
E1.empid, E1.firstname, E1.lastname,
E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1
JOIN HR.Employees AS E2
ON E1.empid < E2.empid;
---------------------------------------------------------------------
-- Multi-Table Joins
---------------------------------------------------------------------
SELECT
C.custid, C.companyname, O.orderid,
OD.productid, OD.qty
FROM Sales.Customers AS C
JOIN Sales.Orders AS O
ON C.custid = O.custid
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid;
---------------------------------------------------------------------
-- Fundamentals of Outer Joins
---------------------------------------------------------------------
-- Customers and their orders, including customers with no orders
SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid;
-- Customers with no orders
SELECT C.custid, C.companyname
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.orderid IS NULL;
GO
---------------------------------------------------------------------
-- Beyond the Fundamentals of Outer Joins
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Including Missing Values
---------------------------------------------------------------------
-- Listing 3-1: Code to Create and Populate the Auxiliary Table Nums
SET NOCOUNT ON;
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @i AS INT = 1;
/*
Note:
The ability to declare and initialize variables in one statement
is new in Microsoft SQL Server 2008.
In earlier versions use separate DECLARE and SET statements:
DECLARE @i AS INT;
SET @i = 1;
*/
BEGIN TRAN
WHILE @i <= 100000
BEGIN
INSERT INTO dbo.Nums VALUES(@i);
SET @i = @i + 1;
END
COMMIT TRAN
SET NOCOUNT OFF;
GO
SELECT DATEADD(day, n-1, '20060101') AS orderdate
FROM dbo.Nums
WHERE n <= DATEDIFF(day, '20060101', '20081231') + 1
ORDER BY orderdate;
SELECT DATEADD(day, Nums.n - 1, '20060101') AS orderdate,
O.orderid, O.custid, O.empid
FROM dbo.Nums
LEFT OUTER JOIN Sales.Orders AS O
ON DATEADD(day, Nums.n - 1, '20060101') = O.orderdate
WHERE Nums.n <= DATEDIFF(day, '20060101', '20081231') + 1
ORDER BY orderdate;
---------------------------------------------------------------------
-- Filtering Attributes from Non-Preserved Side of Outer Join
---------------------------------------------------------------------
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.orderdate >= '20070101';
---------------------------------------------------------------------
-- Using Outer Joins in a Multi-Table Join
---------------------------------------------------------------------
SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid;
-- Option 1: use outer join all along
SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
LEFT OUTER JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid;
-- Option 2: change join order
SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
RIGHT OUTER JOIN Sales.Customers AS C
ON O.custid = C.custid;
-- Option 3: use parentheses
SELECT C.custid, O.orderid, OD.productid, OD.qty
FROM Sales.Customers AS C
LEFT OUTER JOIN
(Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid)
ON C.custid = O.custid;
---------------------------------------------------------------------
-- Using the COUNT Aggregate with Outer Joins
---------------------------------------------------------------------
SELECT C.custid, COUNT(*) AS numorders
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
GROUP BY C.custid;
SELECT C.custid, COUNT(O.orderid) AS numorders
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
GROUP BY C.custid;
====================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 4 - Subqueries
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- Write a query that returns all orders placed on the last day of
-- activity that can be found in the Orders table
-- Tables involved: TSQLFundamentals2008 database, Orders table
--Desired output
orderid orderdate custid empid
----------- ----------------------- ----------- -----------
11077 2008-05-06 00:00:00.000 65 1
11076 2008-05-06 00:00:00.000 9 4
11075 2008-05-06 00:00:00.000 68 8
11074 2008-05-06 00:00:00.000 73 7
(4 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate =
(SELECT MAX(O.orderdate) FROM Sales.Orders AS O);
-- 2 (Optional, Advanced)
-- Write a query that returns all orders placed
-- by the customer(s) who placed the highest number of orders
-- * Note: there may be more than one customer
-- with the same number of orders
-- Tables involved: TSQLFundamentals2008 database, Orders table
-- Desired output:
custid orderid orderdate empid
----------- ----------- ----------------------- -----------
71 10324 2006-10-08 00:00:00.000 9
71 10393 2006-12-25 00:00:00.000 1
71 10398 2006-12-30 00:00:00.000 2
71 10440 2007-02-10 00:00:00.000 4
71 10452 2007-02-20 00:00:00.000 8
71 10510 2007-04-18 00:00:00.000 6
71 10555 2007-06-02 00:00:00.000 6
71 10603 2007-07-18 00:00:00.000 8
71 10607 2007-07-22 00:00:00.000 5
71 10612 2007-07-28 00:00:00.000 1
71 10627 2007-08-11 00:00:00.000 8
71 10657 2007-09-04 00:00:00.000 2
71 10678 2007-09-23 00:00:00.000 7
71 10700 2007-10-10 00:00:00.000 3
71 10711 2007-10-21 00:00:00.000 5
71 10713 2007-10-22 00:00:00.000 1
71 10714 2007-10-22 00:00:00.000 5
71 10722 2007-10-29 00:00:00.000 8
71 10748 2007-11-20 00:00:00.000 3
71 10757 2007-11-27 00:00:00.000 6
71 10815 2008-01-05 00:00:00.000 2
71 10847 2008-01-22 00:00:00.000 4
71 10882 2008-02-11 00:00:00.000 4
71 10894 2008-02-18 00:00:00.000 1
71 10941 2008-03-11 00:00:00.000 7
71 10983 2008-03-27 00:00:00.000 2
71 10984 2008-03-30 00:00:00.000 1
71 11002 2008-04-06 00:00:00.000 4
71 11030 2008-04-17 00:00:00.000 7
71 11031 2008-04-17 00:00:00.000 6
71 11064 2008-05-01 00:00:00.000 1
(31 row(s) affected)
-- Solution
SELECT custid, orderid, orderdate, empid
FROM Sales.Orders
WHERE custid IN
(SELECT TOP (1) WITH TIES O.custid
FROM Sales.Orders AS O
GROUP BY O.custid
ORDER BY COUNT(*) DESC);
-- 3
-- Write a query that returns employees
-- who did not place orders on or after May 1st, 2008
-- Tables involved: TSQLFundamentals2008 database, Employees and Orders tables
-- Desired output:
empid FirstName lastname
----------- ---------- --------------------
3 Judy Lew
5 Sven Buck
6 Paul Suurs
9 Zoya Dolgopyatova
(4 row(s) affected)
-- Solution
SELECT empid, FirstName, lastname
FROM HR.Employees
WHERE empid NOT IN
(SELECT O.empid
FROM Sales.Orders AS O
WHERE O.orderdate >= '20080501');
-- 4
-- Write a query that returns
-- countries where there are customers but not employees
-- Tables involved: TSQLFundamentals2008 database, Customers and Employees tables
-- Desired output:
country
---------------
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland
Italy
Mexico
Norway
Poland
Portugal
Spain
Sweden
Switzerland
Venezuela
(19 row(s) affected)
-- Solution
SELECT DISTINCT country
FROM Sales.Customers
WHERE country NOT IN
(SELECT E.country FROM HR.Employees AS E);
-- 5
-- Write a query that returns for each customer
-- all orders placed on the customer's last day of activity
-- Tables involved: TSQLFundamentals2008 database, Orders table
-- Desired output:
custid orderid orderdate empid
----------- ----------- ----------------------- -----------
1 11011 2008-04-09 00:00:00.000 3
2 10926 2008-03-04 00:00:00.000 4
3 10856 2008-01-28 00:00:00.000 3
4 11016 2008-04-10 00:00:00.000 9
5 10924 2008-03-04 00:00:00.000 3
...
87 11025 2008-04-15 00:00:00.000 6
88 10935 2008-03-09 00:00:00.000 4
89 11066 2008-05-01 00:00:00.000 7
90 11005 2008-04-07 00:00:00.000 2
91 11044 2008-04-23 00:00:00.000 4
(90 row(s) affected)
-- Solution
SELECT custid, orderid, orderdate, empid
FROM Sales.Orders AS O1
WHERE orderdate =
(SELECT MAX(O2.orderdate)
FROM Sales.Orders AS O2
WHERE O2.custid = O1.custid)
ORDER BY custid;
-- 6
-- Write a query that returns customers
-- who placed orders in 2007 but not in 2008
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output:
custid companyname
----------- ----------------------------------------
21 Customer KIDPX
23 Customer WVFAF
33 Customer FVXPQ
36 Customer LVJSO
43 Customer UISOJ
51 Customer PVDZC
85 Customer ENQZT
(7 row(s) affected)
-- Solution
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.orderdate >= '20070101'
AND O.orderdate < '20080101')
AND NOT EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.orderdate >= '20080101'
AND O.orderdate < '20090101');
-- 7 (Optional, Advanced)
-- Write a query that returns customers
-- who ordered product 12
-- Tables involved: TSQLFundamentals2008 database,
-- Customers, Orders and OrderDetails tables
-- Desired output:
custid companyname
----------- ----------------------------------------
48 Customer DVFMB
39 Customer GLLAG
71 Customer LCOUJ
65 Customer NYUHS
44 Customer OXFRU
51 Customer PVDZC
86 Customer SNXOJ
20 Customer THHDP
90 Customer XBBVR
46 Customer XPNIK
31 Customer YJCBX
87 Customer ZHYOS
(12 row(s) affected)
-- Solution
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND EXISTS
(SELECT *
FROM Sales.OrderDetails AS OD
WHERE OD.orderid = O.orderid
AND OD.ProductID = 12));
-- 8 (Optional, Advanced)
-- Write a query that calculates a running total qty
-- for each customer and month
-- Tables involved: TSQLFundamentals2008 database, Sales.CustOrders view
-- Desired output:
custid ordermonth qty runqty
----------- ----------------------- ----------- -----------
1 2007-08-01 00:00:00.000 38 38
1 2007-10-01 00:00:00.000 41 79
1 2008-01-01 00:00:00.000 17 96
1 2008-03-01 00:00:00.000 18 114
1 2008-04-01 00:00:00.000 60 174
2 2006-09-01 00:00:00.000 6 6
2 2007-08-01 00:00:00.000 18 24
2 2007-11-01 00:00:00.000 10 34
2 2008-03-01 00:00:00.000 29 63
3 2006-11-01 00:00:00.000 24 24
3 2007-04-01 00:00:00.000 30 54
3 2007-05-01 00:00:00.000 80 134
3 2007-06-01 00:00:00.000 83 217
3 2007-09-01 00:00:00.000 102 319
3 2008-01-01 00:00:00.000 40 359
...
(636 row(s) affected)
-- Solution
SELECT custid, ordermonth, qty,
(SELECT SUM(O2.qty)
FROM Sales.CustOrders AS O2
WHERE O2.custid = O1.custid
AND O2.ordermonth <= O1.ordermonth) AS runqty
FROM Sales.CustOrders AS O1
ORDER BY custid, ordermonth;
=========================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 4 - Subqueries
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Self-Contained Subqueries
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Scalar Subqueries
---------------------------------------------------------------------
-- Order with the maximum order ID
USE TSQLFundamentals2008;
DECLARE @maxid AS INT = (SELECT MAX(orderid)
FROM Sales.Orders);
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderid = @maxid;
GO
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderid = (SELECT MAX(O.orderid)
FROM Sales.Orders AS O);
-- Scalar subquery expected to return one value
SELECT orderid
FROM Sales.Orders
WHERE empid =
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'B%');
GO
SELECT orderid
FROM Sales.Orders
WHERE empid =
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'D%');
GO
SELECT orderid
FROM Sales.Orders
WHERE empid =
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'A%');
---------------------------------------------------------------------
-- Multi-Valued Subqueries
---------------------------------------------------------------------
SELECT orderid
FROM Sales.Orders
WHERE empid IN
(SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'D%');
SELECT O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O
ON E.empid = O.empid
WHERE E.lastname LIKE N'D%';
-- Orders placed by US customers
SELECT custid, orderid, orderdate, empid
FROM Sales.Orders
WHERE custid IN
(SELECT C.custid
FROM Sales.Customers AS C
WHERE C.country = N'USA');
-- Customers who placed no orders
SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN
(SELECT O.custid
FROM Sales.Orders AS O);
GO
-- Missing order IDs
USE tempdb;
IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;
GO
SELECT *
INTO dbo.Orders
FROM TSQLFundamentals2008.Sales.Orders
WHERE orderid % 2 = 0;
SELECT n
FROM dbo.Nums
WHERE n BETWEEN (SELECT MIN(O.orderid) FROM dbo.Orders AS O)
AND (SELECT MAX(O.orderid) FROM dbo.Orders AS O)
AND n NOT IN (SELECT O.orderid FROM dbo.Orders AS O);
-- CLeanup
DROP TABLE tempdb.dbo.Orders;
---------------------------------------------------------------------
-- Correlated Subqueries
---------------------------------------------------------------------
-- Orders with maximum order ID for each customer
USE TSQLFundamentals2008;
SELECT custid, orderid, orderdate, empid
FROM Sales.Orders AS O1
WHERE orderid =
(SELECT MAX(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.custid = O1.custid);
SELECT MAX(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.custid = 85;
-- Percentage of store total
SELECT orderid, custid, val,
CAST(100. * val / (SELECT SUM(O2.val)
FROM Sales.OrderValues AS O2
WHERE O2.custid = O1.custid)
AS NUMERIC(5,2)) AS pct
FROM Sales.OrderValues AS O1
ORDER BY custid, orderid;
---------------------------------------------------------------------
-- EXISTS
---------------------------------------------------------------------
-- Customers from Spain who placed orders
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE country = N'Spain'
AND EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid);
-- Customers from Spain who didn't place Orders
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE country = N'Spain'
AND NOT EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid);
---------------------------------------------------------------------
-- Beyond the Fundamentals of Subqueries
-- (Optional, Advanced)
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Returning "Previous" or "Next" Value
---------------------------------------------------------------------
SELECT orderid, orderdate, empid, custid,
(SELECT MAX(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.orderid < O1.orderid) AS prevorderid
FROM Sales.Orders AS O1;
SELECT orderid, orderdate, empid, custid,
(SELECT MIN(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.orderid > O1.orderid) AS nextorderid
FROM Sales.Orders AS O1;
---------------------------------------------------------------------
-- Running Aggregates
---------------------------------------------------------------------
SELECT orderyear, qty
FROM Sales.OrderTotalsByYear;
SELECT orderyear, qty,
(SELECT SUM(O2.qty)
FROM Sales.OrderTotalsByYear AS O2
WHERE O2.orderyear <= O1.orderyear) AS runqty
FROM Sales.OrderTotalsByYear AS O1
ORDER BY orderyear;
---------------------------------------------------------------------
-- Misbehaving Subqueries
---------------------------------------------------------------------
---------------------------------------------------------------------
-- NULL Trouble
---------------------------------------------------------------------
-- Customers who didn't place orders
-- Using NOT IN
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE custid NOT IN(SELECT O.custid
FROM Sales.Orders AS O);
-- Add a row to the Orders table with a NULL custid
INSERT INTO Sales.Orders
(custid, empid, orderdate, requireddate, shippeddate, shipperid,
freight, shipname, shipaddress, shipcity, shipregion,
shippostalcode, shipcountry)
VALUES(NULL, 1, '20090212', '20090212',
'20090212', 1, 123.00, N'abc', N'abc', N'abc',
N'abc', N'abc', N'abc');
-- Following returns an empty set
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE custid NOT IN(SELECT O.custid
FROM Sales.Orders AS O);
-- Exclude NULLs
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE custid NOT IN(SELECT O.custid
FROM Sales.Orders AS O
WHERE O.custid IS NOT NULL);
-- Using NOT EXISTS
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid);
-- Cleanup
DELETE FROM Sales.Orders WHERE custid IS NULL;
DBCC CHECKIDENT('Sales.Orders', RESEED, 11077);
---------------------------------------------------------------------
-- Substitution Error in a Subquery Column Name
---------------------------------------------------------------------
-- Create and populate table Sales.MyShippers
IF OBJECT_ID('Sales.MyShippers', 'U') IS NOT NULL
DROP TABLE Sales.MyShippers;
CREATE TABLE Sales.MyShippers
(
shipper_id INT NOT NULL,
companyname NVARCHAR(40) NOT NULL,
phone NVARCHAR(24) NOT NULL,
CONSTRAINT PK_MyShippers PRIMARY KEY(shipper_id)
);
INSERT INTO Sales.MyShippers(shipper_id, companyname, phone)
VALUES(1, N'Shipper GVSUA', N'(503) 555-0137');
INSERT INTO Sales.MyShippers(shipper_id, companyname, phone)
VALUES(2, N'Shipper ETYNR', N'(425) 555-0136');
INSERT INTO Sales.MyShippers(shipper_id, companyname, phone)
VALUES(3, N'Shipper ZHISN', N'(415) 555-0138');
-- Shippers who shipped orders to customer 43
-- Bug
SELECT shipper_id, companyname
FROM Sales.MyShippers
WHERE shipper_id IN
(SELECT shipper_id
FROM Sales.Orders
WHERE custid = 43);
GO
-- The safe way using aliases, bug identified
SELECT shipper_id, companyname
FROM Sales.MyShippers
WHERE shipper_id IN
(SELECT O.shipper_id
FROM Sales.Orders AS O
WHERE O.custid = 43);
GO
-- Bug corrected
SELECT shipper_id, companyname
FROM Sales.MyShippers
WHERE shipper_id IN
(SELECT O.shipperid
FROM Sales.Orders AS O
WHERE O.custid = 43);
-- Cleanup
IF OBJECT_ID('Sales.MyShippers', 'U') IS NOT NULL
DROP TABLE Sales.MyShippers;
====================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 5 - Table Expressions
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1-1
-- Write a query that returns the maximum order date for each employee
-- Tables involved: TSQLFundamentals2008 database, Sales.Orders table
--Desired output
empid maxorderdate
----------- -----------------------
3 2008-04-30 00:00:00.000
6 2008-04-23 00:00:00.000
9 2008-04-29 00:00:00.000
7 2008-05-06 00:00:00.000
1 2008-05-06 00:00:00.000
4 2008-05-06 00:00:00.000
2 2008-05-05 00:00:00.000
5 2008-04-22 00:00:00.000
8 2008-05-06 00:00:00.000
(9 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
SELECT empid, MAX(orderdate) AS maxorderdate
FROM Sales.Orders
GROUP BY empid;
-- 1-2
-- Encapsulate the query from exercise 1-1 in a derived table
-- Write a join query between the derived table and the Sales.Orders
-- table to return the Sales.Orders with the maximum order date for
-- each employee
-- Tables involved: Sales.Orders
-- Desired output:
empid orderdate orderid custid
----------- ----------------------- ----------- -----------
9 2008-04-29 00:00:00.000 11058 6
8 2008-05-06 00:00:00.000 11075 68
7 2008-05-06 00:00:00.000 11074 73
6 2008-04-23 00:00:00.000 11045 10
5 2008-04-22 00:00:00.000 11043 74
4 2008-05-06 00:00:00.000 11076 9
3 2008-04-30 00:00:00.000 11063 37
2 2008-05-05 00:00:00.000 11073 58
2 2008-05-05 00:00:00.000 11070 44
1 2008-05-06 00:00:00.000 11077 65
(10 row(s) affected)
-- Solution
SELECT O.empid, O.orderdate, O.orderid, O.custid
FROM Sales.Orders AS O
JOIN (SELECT empid, MAX(orderdate) AS maxorderdate
FROM Sales.Orders
GROUP BY empid) AS D
ON O.empid = D.empid
AND O.orderdate = D.maxorderdate;
-- 2-1
-- Write a query that calculates a row number for each order
-- based on orderdate, orderid ordering
-- Tables involved: Sales.Orders
-- Desired output:
orderid orderdate custid empid rownum
----------- ----------------------- ----------- ----------- -------
10248 2006-07-04 00:00:00.000 85 5 1
10249 2006-07-05 00:00:00.000 79 6 2
10250 2006-07-08 00:00:00.000 34 4 3
10251 2006-07-08 00:00:00.000 84 3 4
10252 2006-07-09 00:00:00.000 76 4 5
10253 2006-07-10 00:00:00.000 34 3 6
10254 2006-07-11 00:00:00.000 14 5 7
10255 2006-07-12 00:00:00.000 68 9 8
10256 2006-07-15 00:00:00.000 88 3 9
10257 2006-07-16 00:00:00.000 35 4 10
...
(830 row(s) affected)
-- Solution
SELECT orderid, orderdate, custid, empid,
ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders;
-- 2-2
-- Write a query that returns rows with row numbers 11 through 20
-- based on the row number definition in exercise 2-1
-- Use a CTE to encapsulate the code from exercise 2-1
-- Tables involved: Sales.Orders
-- Desired output:
orderid orderdate custid empid rownum
----------- ----------------------- ----------- ----------- -------
10258 2006-07-17 00:00:00.000 20 1 11
10259 2006-07-18 00:00:00.000 13 4 12
10260 2006-07-19 00:00:00.000 56 4 13
10261 2006-07-19 00:00:00.000 61 4 14
10262 2006-07-22 00:00:00.000 65 8 15
10263 2006-07-23 00:00:00.000 20 9 16
10264 2006-07-24 00:00:00.000 24 6 17
10265 2006-07-25 00:00:00.000 7 2 18
10266 2006-07-26 00:00:00.000 87 3 19
10267 2006-07-29 00:00:00.000 25 4 20
(10 row(s) affected)
-- Solution
WITH OrdersRN AS
(
SELECT orderid, orderdate, custid, empid,
ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
)
SELECT * FROM OrdersRN WHERE rownum BETWEEN 11 AND 20;
-- 3
-- Write a solution using a recursive CTE that returns the
-- management chain leading to Zoya Dolgopyatova (employee ID 9)
-- Tables involved: HR.Employees
-- Desired output:
empid mgrid firstname lastname
----------- ----------- ---------- --------------------
9 5 Zoya Dolgopyatova
5 2 Sven Buck
2 1 Don Funk
1 NULL Sara Davis
(4 row(s) affected)
-- Solution
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 9
UNION ALL
SELECT P.empid, P.mgrid, P.firstname, P.lastname
FROM EmpsCTE AS C
JOIN HR.Employees AS P
ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;
-- 4-1
-- Create a view that returns the total qty
-- for each employee and year
-- Tables involved: Sales.Orders and Sales.OrderDetails
-- Desired output when running:
-- SELECT * FROM Sales.VEmpOrders ORDER BY empid, orderyear
empid orderyear qty
----------- ----------- -----------
1 2006 1620
1 2007 3877
1 2008 2315
2 2006 1085
2 2007 2604
2 2008 2366
3 2006 940
3 2007 4436
3 2008 2476
4 2006 2212
4 2007 5273
4 2008 2313
5 2006 778
5 2007 1471
5 2008 787
6 2006 963
6 2007 1738
6 2008 826
7 2006 485
7 2007 2292
7 2008 1877
8 2006 923
8 2007 2843
8 2008 2147
9 2006 575
9 2007 955
9 2008 1140
(27 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.VEmpOrders') IS NOT NULL
DROP VIEW Sales.VEmpOrders;
GO
CREATE VIEW Sales.VEmpOrders
AS
SELECT
empid,
YEAR(orderdate) AS orderyear,
SUM(qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
GROUP BY
empid,
YEAR(orderdate);
GO
-- 4-2 (Optional, Advanced)
-- Write a query against Sales.VEmpOrders
-- that returns the running qty for each employee and year
-- Tables involved: TSQLFundamentals2008 database, Sales.VEmpOrders view
-- Desired output:
empid orderyear qty runqty
----------- ----------- ----------- -----------
1 2006 1620 1620
1 2007 3877 5497
1 2008 2315 7812
2 2006 1085 1085
2 2007 2604 3689
2 2008 2366 6055
3 2006 940 940
3 2007 4436 5376
3 2008 2476 7852
4 2006 2212 2212
4 2007 5273 7485
4 2008 2313 9798
5 2006 778 778
5 2007 1471 2249
5 2008 787 3036
6 2006 963 963
6 2007 1738 2701
6 2008 826 3527
7 2006 485 485
7 2007 2292 2777
7 2008 1877 4654
8 2006 923 923
8 2007 2843 3766
8 2008 2147 5913
9 2006 575 575
9 2007 955 1530
9 2008 1140 2670
(27 row(s) affected)
-- Solution
SELECT empid, orderyear, qty,
(SELECT SUM(qty)
FROM Sales.VEmpOrders AS V2
WHERE V2.empid = V1.empid
AND V2.orderyear <= V1.orderyear) AS runqty
FROM Sales.VEmpOrders AS V1
ORDER BY empid, orderyear;
-- 5-1
-- Create an inline function that accepts as inputs
-- a supplier id (@supid AS INT),
-- and a requested number of products (@n AS INT)
-- The function should return @n products with the highest unit prices
-- that are supplied by the given supplier id
-- Tables involved: Production.Products
-- Desired output when issuing the following query:
-- SELECT * FROM Production.fn_TopProducts(5, 2)
productid productname unitprice
----------- ---------------------------------------- ---------------------
12 Product OSFNS 38.00
11 Product QMVUN 21.00
(2 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
IF OBJECT_ID('Production.fn_TopProducts') IS NOT NULL
DROP FUNCTION Production.fn_TopProducts;
GO
CREATE FUNCTION Production.fn_TopProducts
(@supid AS INT, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) productid, productname, unitprice
FROM Production.Products
WHERE supplierid = @supid
ORDER BY unitprice DESC;
GO
-- 5-2
-- Using the CROSS APPLY operator
-- and the function you created in exercise 5-1,
-- return, for each supplier, the two most expensive products
-- Desired output
supplierid companyname productid productname unitprice
----------- --------------- ----------- --------------- ----------
8 Supplier BWGYE 20 Product QHFFP 81.00
8 Supplier BWGYE 68 Product TBTBL 12.50
20 Supplier CIYNM 43 Product ZZZHR 46.00
20 Supplier CIYNM 44 Product VJIEO 19.45
23 Supplier ELCRN 49 Product FPYPN 20.00
23 Supplier ELCRN 76 Product JYGFE 18.00
5 Supplier EQPNC 12 Product OSFNS 38.00
5 Supplier EQPNC 11 Product QMVUN 21.00
...
(55 row(s) affected)
-- Solution
SELECT S.supplierid, S.companyname, P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
CROSS APPLY Production.fn_TopProducts(S.supplierid, 2) AS P;
=========================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 5 - Table Expressions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Derived Tables
---------------------------------------------------------------------
USE TSQLFundamentals2008;
SELECT *
FROM (SELECT custid, companyname
FROM Sales.Customers
WHERE country = N'USA') AS USACusts;
GO
---------------------------------------------------------------------
-- Assigning Column Aliases
---------------------------------------------------------------------
-- Following fails
SELECT
YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY orderyear;
GO
-- Listing 5-1 Query with a Derived Table using Inline Aliasing Form
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS D
GROUP BY orderyear;
SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate);
-- External column aliasing
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate), custid
FROM Sales.Orders) AS D(orderyear, custid)
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Using Arguments
---------------------------------------------------------------------
-- Yearly Count of Customers handled by Employee 3
DECLARE @empid AS INT = 3;
/*
-- Prior to SQL Server 2008 use separate DECLARE and SET statements:
DECLARE @empid AS INT;
SET @empid = 3;
*/
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid = @empid) AS D
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Nesting
---------------------------------------------------------------------
-- Listing 5-2 Query with Nested Derived Tables
SELECT orderyear, numcusts
FROM (SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS D1
GROUP BY orderyear) AS D2
WHERE numcusts > 70;
SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)
HAVING COUNT(DISTINCT custid) > 70;
---------------------------------------------------------------------
-- Multiple References
---------------------------------------------------------------------
-- Listing 5-3 Multiple Derived Tables Based on the Same Query
SELECT Cur.orderyear,
Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
Cur.numcusts - Prv.numcusts AS growth
FROM (SELECT YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)) AS Cur
LEFT OUTER JOIN
(SELECT YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)) AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
---------------------------------------------------------------------
-- Common Table Expressions
---------------------------------------------------------------------
WITH USACusts AS
(
SELECT custid, companyname
FROM Sales.Customers
WHERE country = N'USA'
)
SELECT * FROM USACusts;
---------------------------------------------------------------------
-- Assigning Column Aliases
---------------------------------------------------------------------
-- Inline column aliasing
WITH C AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
-- External column aliasing
WITH C(orderyear, custid) AS
(
SELECT YEAR(orderdate), custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Using Arguments
---------------------------------------------------------------------
DECLARE @empid AS INT = 3;
/*
-- Prior to SQL Server 2008 use separate DECLARE and SET statements:
DECLARE @empid AS INT;
SET @empid = 3;
*/
WITH C AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Defining Multiple CTEs
---------------------------------------------------------------------
WITH C1 AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
),
C2 AS
(
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C1
GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;
---------------------------------------------------------------------
-- Multiple References
---------------------------------------------------------------------
WITH YearlyCount AS
(
SELECT YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear,
Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
LEFT OUTER JOIN YearlyCount AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
---------------------------------------------------------------------
-- Recursive CTEs (Advanced, Optional)
---------------------------------------------------------------------
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 2
UNION ALL
SELECT C.empid, C.mgrid, C.firstname, C.lastname
FROM EmpsCTE AS P
JOIN HR.Employees AS C
ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;
---------------------------------------------------------------------
-- Views
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Views Described
---------------------------------------------------------------------
-- Creating VUSACusts View
USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
SELECT custid, companyname
FROM Sales.USACusts;
GO
---------------------------------------------------------------------
-- Views and ORDER BY
---------------------------------------------------------------------
-- ORDER BY in a View is not Allowed
ALTER VIEW Sales.USACusts
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO
-- Instead, use ORDER BY in Outer Query
SELECT custid, companyname, region
FROM Sales.USACusts
ORDER BY region;
GO
-- Do not Rely on TOP
ALTER VIEW Sales.USACusts
AS
SELECT TOP (100) PERCENT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO
-- Query USACusts
SELECT custid, companyname, region
FROM Sales.USACusts;
GO
---------------------------------------------------------------------
-- View Options
---------------------------------------------------------------------
---------------------------------------------------------------------
-- ENCRYPTION
---------------------------------------------------------------------
ALTER VIEW Sales.USACusts
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));
GO
ALTER VIEW Sales.USACusts WITH ENCRYPTION
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));
EXEC sp_helptext 'Sales.USACusts';
GO
---------------------------------------------------------------------
-- SCHEMABINDING
---------------------------------------------------------------------
ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
-- Try a schema change
ALTER TABLE Sales.Customers DROP COLUMN address;
GO
---------------------------------------------------------------------
-- CHECK OPTION
---------------------------------------------------------------------
-- Notice that you can insert a row through the view
INSERT INTO Sales.USACusts(
companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax)
VALUES(
N'Customer ABCDE', N'Contact ABCDE', N'Title ABCDE', N'Address ABCDE',
N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789');
-- But when you query the view, you won't see it
SELECT custid, companyname, country
FROM Sales.USACusts
WHERE companyname = N'Customer ABCDE';
-- You can see it in the table, though
SELECT custid, companyname, country
FROM Sales.Customers
WHERE companyname = N'Customer ABCDE';
GO
-- Add CHECK OPTION to the View
ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO
-- Notice that you can't insert a row through the view
INSERT INTO Sales.USACusts(
companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax)
VALUES(
N'Customer FGHIJ', N'Contact FGHIJ', N'Title FGHIJ', N'Address FGHIJ',
N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789');
GO
-- Cleanup
DELETE FROM Sales.Customers
WHERE custid > 91;
DBCC CHECKIDENT('Sales.Customers', RESEED, 91);
IF OBJECT_ID('Sales.USACusts') IS NOT NULL DROP VIEW Sales.USACusts;
GO
---------------------------------------------------------------------
-- Inline User Defined Functions
---------------------------------------------------------------------
-- Creating fn_GetCustOrders function
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
(@cid AS INT) RETURNS TABLE
AS
RETURN
SELECT orderid, custid, empid, orderdate, requireddate,
shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
shipregion, shippostalcode, shipcountry
FROM Sales.Orders
WHERE custid = @cid;
GO
-- Test Function
SELECT orderid, custid
FROM dbo.fn_GetCustOrders(1) AS CO;
SELECT CO.orderid, CO.custid, OD.productid, OD.qty
FROM dbo.fn_GetCustOrders(1) AS CO
JOIN Sales.OrderDetails AS OD
ON CO.orderid = OD.orderid;
GO
-- Cleanup
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
DROP FUNCTION dbo.fn_GetCustOrders;
GO
---------------------------------------------------------------------
-- APPLY
---------------------------------------------------------------------
SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
CROSS JOIN HR.Employees AS E;
SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
CROSS APPLY HR.Employees AS E;
-- 3 most recent orders for each customer
SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
CROSS APPLY
(SELECT TOP(3) orderid, empid, orderdate, requireddate
FROM Sales.Orders AS O
WHERE O.custid = C.custid
ORDER BY orderdate DESC, orderid DESC) AS A;
-- 3 most recent orders for each customer,
-- Include customers without orders
SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
OUTER APPLY
(SELECT TOP(3) orderid, empid, orderdate, requireddate
FROM Sales.Orders AS O
WHERE O.custid = C.custid
ORDER BY orderdate DESC, orderid DESC) AS A;
-- Creation Script for the Function fn_TopOrders
IF OBJECT_ID('dbo.fn_TopOrders') IS NOT NULL
DROP FUNCTION dbo.fn_TopOrders;
GO
CREATE FUNCTION dbo.fn_TopOrders
(@custid AS INT, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) orderid, empid, orderdate, requireddate
FROM Sales.Orders
WHERE custid = @custid
ORDER BY orderdate DESC, orderid DESC;
GO
SELECT
C.custid, C.companyname,
A.orderid, A.empid, A.orderdate, A.requireddate
FROM Sales.Customers AS C
CROSS APPLY dbo.fn_TopOrders(C.custid, 3) AS A;
======================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 6 - Set Operations
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- Write a query that generates a virtual auxiliary table of 10 numbers
-- in the range 1 through 10
-- Tables involved: no table
--Desired output
n
-----------
1
2
3
4
5
6
7
8
9
10
(10 row(s) affected)
-- Solutions
SELECT 1 AS n
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10;
-- SQL Server 2008
SELECT n
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS Nums(n);
-- 2
-- Write a query that returns customer and employee pairs
-- that had order activity in January 2008 but not in February 2008
-- Tables involved: TSQLFundamentals2008 database, Orders table
--Desired output
custid empid
----------- -----------
1 1
3 3
5 8
5 9
6 9
7 6
9 1
12 2
16 7
17 1
20 7
24 8
25 1
26 3
32 4
38 9
39 3
40 2
41 2
42 2
44 8
47 3
47 4
47 8
49 7
55 2
55 3
56 6
59 8
63 8
64 9
65 3
65 8
66 5
67 5
70 3
71 2
75 1
76 2
76 5
80 1
81 1
81 3
81 4
82 6
84 1
84 3
84 4
88 7
89 4
(50 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
EXCEPT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301';
-- 3
-- Write a query that returns customer and employee pairs
-- that had order activity in both January 2008 and February 2008
-- Tables involved: TSQLFundamentals2008 database, Orders table
--Desired output
custid empid
----------- -----------
20 3
39 9
46 5
67 1
71 4
(5 row(s) affected)
-- Solution
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
INTERSECT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301';
-- 4
-- Write a query that returns customer and employee pairs
-- that had order activity in both January 2008 and February 2008
-- but not in 2007
-- Tables involved: TSQLFundamentals2008 database, Orders table
--Desired output
custid empid
----------- -----------
67 1
46 5
(2 row(s) affected)
-- Solution
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
INTERSECT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301'
EXCEPT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';
-- With parentheses
(SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
INTERSECT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301')
EXCEPT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';
-- 5
-- You are given the following query:
SELECT country, region, city
FROM HR.Employees
UNION ALL
SELECT country, region, city
FROM Production.Suppliers;
-- You are asked to add logic to the query
-- such that it would guarantee that the rows from Employees
-- would be returned in the output before the rows from Customers,
-- and within each segment, the rows should be sorted
-- by country, region, city
-- Tables involved: TSQLFundamentals2008 database, Employees and Suppliers tables
--Desired output
country region city
--------------- --------------- ---------------
UK NULL London
UK NULL London
UK NULL London
UK NULL London
USA WA Kirkland
USA WA Redmond
USA WA Seattle
USA WA Seattle
USA WA Tacoma
Australia NSW Sydney
Australia Victoria Melbourne
Brazil NULL Sao Paulo
Canada Québec Montréal
Canada Québec Ste-Hyacinthe
Denmark NULL Lyngby
Finland NULL Lappeenranta
France NULL Annecy
France NULL Montceau
France NULL Paris
Germany NULL Berlin
Germany NULL Cuxhaven
Germany NULL Frankfurt
Italy NULL Ravenna
Italy NULL Salerno
Japan NULL Osaka
Japan NULL Tokyo
Netherlands NULL Zaandam
Norway NULL Sandvika
Singapore NULL Singapore
Spain Asturias Oviedo
Sweden NULL Göteborg
Sweden NULL Stockholm
UK NULL London
UK NULL Manchester
USA LA New Orleans
USA MA Boston
USA MI Ann Arbor
USA OR Bend
(38 row(s) affected)
-- Solution
SELECT country, region, city
FROM (SELECT 1 AS sortcol, country, region, city
FROM HR.Employees
UNION ALL
SELECT 2, country, region, city
FROM Production.Suppliers) AS D
ORDER BY sortcol, country, region, city;
==============================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 6 - Set Operations
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- The UNION Set Operation
---------------------------------------------------------------------
-- The UNION ALL Set Operation
USE TSQLFundamentals2008;
SELECT country, region, city FROM HR.Employees
UNION ALL
SELECT country, region, city FROM Sales.Customers;
-- The UNION DISTINCT Set Operation
SELECT country, region, city FROM HR.Employees
UNION
SELECT country, region, city FROM Sales.Customers;
---------------------------------------------------------------------
-- The INTERSECT
---------------------------------------------------------------------
-- The INTERSECT DISTINCT Set Operation
SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;
-- The INTERSECT ALL Set Operation (Optional, Advanced)
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
INTERSECT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers;
WITH INTERSECT_ALL
AS
(
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
INTERSECT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers
)
SELECT country, region, city
FROM INTERSECT_ALL;
---------------------------------------------------------------------
-- The EXCEPT Set Operation
---------------------------------------------------------------------
-- The EXCEPT DISTINCT Set Operation
-- Employees EXCEPT Customers
SELECT country, region, city FROM HR.Employees
EXCEPT
SELECT country, region, city FROM Sales.Customers;
-- Customers EXCEPT Employees
SELECT country, region, city FROM Sales.Customers
EXCEPT
SELECT country, region, city FROM HR.Employees;
-- The EXCEPT ALL Set Operation (Optional, Advanced)
WITH EXCEPT_ALL
AS
(
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
EXCEPT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers
)
SELECT country, region, city
FROM EXCEPT_ALL;
---------------------------------------------------------------------
-- Precedence
---------------------------------------------------------------------
-- INTERSECT Precedes EXCEPT
SELECT country, region, city FROM Production.Suppliers
EXCEPT
SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;
-- Using Parenthesis
(SELECT country, region, city FROM Production.Suppliers
EXCEPT
SELECT country, region, city FROM HR.Employees)
INTERSECT
SELECT country, region, city FROM Sales.Customers;
---------------------------------------------------------------------
-- Circumventing Unsupported Logical Phases
-- (Optional, Advanced)
---------------------------------------------------------------------
-- Number of Cities per country Covered by Both Customers
-- and Employees
SELECT country, COUNT(*) AS numlocations
FROM (SELECT country, region, city FROM HR.Employees
UNION
SELECT country, region, city FROM Sales.Customers) AS U
GROUP BY country;
-- Two most recent orders for employees 3 and 5
SELECT empid, orderid, orderdate
FROM (SELECT TOP (2) empid, orderid, orderdate
FROM Sales.Orders
WHERE empid = 3
ORDER BY orderdate DESC, orderid DESC) AS D1
UNION ALL
SELECT empid, orderid, orderdate
FROM (SELECT TOP (2) empid, orderid, orderdate
FROM Sales.Orders
WHERE empid = 5
ORDER BY orderdate DESC, orderid DESC) AS D2;
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 5 - Table Expressions
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1-1
-- Write a query that returns the maximum order date for each employee
-- Tables involved: TSQLFundamentals2008 database, Sales.Orders table
--Desired output
empid maxorderdate
----------- -----------------------
3 2008-04-30 00:00:00.000
6 2008-04-23 00:00:00.000
9 2008-04-29 00:00:00.000
7 2008-05-06 00:00:00.000
1 2008-05-06 00:00:00.000
4 2008-05-06 00:00:00.000
2 2008-05-05 00:00:00.000
5 2008-04-22 00:00:00.000
8 2008-05-06 00:00:00.000
(9 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
SELECT empid, MAX(orderdate) AS maxorderdate
FROM Sales.Orders
GROUP BY empid;
-- 1-2
-- Encapsulate the query from exercise 1-1 in a derived table
-- Write a join query between the derived table and the Sales.Orders
-- table to return the Sales.Orders with the maximum order date for
-- each employee
-- Tables involved: Sales.Orders
-- Desired output:
empid orderdate orderid custid
----------- ----------------------- ----------- -----------
9 2008-04-29 00:00:00.000 11058 6
8 2008-05-06 00:00:00.000 11075 68
7 2008-05-06 00:00:00.000 11074 73
6 2008-04-23 00:00:00.000 11045 10
5 2008-04-22 00:00:00.000 11043 74
4 2008-05-06 00:00:00.000 11076 9
3 2008-04-30 00:00:00.000 11063 37
2 2008-05-05 00:00:00.000 11073 58
2 2008-05-05 00:00:00.000 11070 44
1 2008-05-06 00:00:00.000 11077 65
(10 row(s) affected)
-- Solution
SELECT O.empid, O.orderdate, O.orderid, O.custid
FROM Sales.Orders AS O
JOIN (SELECT empid, MAX(orderdate) AS maxorderdate
FROM Sales.Orders
GROUP BY empid) AS D
ON O.empid = D.empid
AND O.orderdate = D.maxorderdate;
-- 2-1
-- Write a query that calculates a row number for each order
-- based on orderdate, orderid ordering
-- Tables involved: Sales.Orders
-- Desired output:
orderid orderdate custid empid rownum
----------- ----------------------- ----------- ----------- -------
10248 2006-07-04 00:00:00.000 85 5 1
10249 2006-07-05 00:00:00.000 79 6 2
10250 2006-07-08 00:00:00.000 34 4 3
10251 2006-07-08 00:00:00.000 84 3 4
10252 2006-07-09 00:00:00.000 76 4 5
10253 2006-07-10 00:00:00.000 34 3 6
10254 2006-07-11 00:00:00.000 14 5 7
10255 2006-07-12 00:00:00.000 68 9 8
10256 2006-07-15 00:00:00.000 88 3 9
10257 2006-07-16 00:00:00.000 35 4 10
...
(830 row(s) affected)
-- Solution
SELECT orderid, orderdate, custid, empid,
ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders;
-- 2-2
-- Write a query that returns rows with row numbers 11 through 20
-- based on the row number definition in exercise 2-1
-- Use a CTE to encapsulate the code from exercise 2-1
-- Tables involved: Sales.Orders
-- Desired output:
orderid orderdate custid empid rownum
----------- ----------------------- ----------- ----------- -------
10258 2006-07-17 00:00:00.000 20 1 11
10259 2006-07-18 00:00:00.000 13 4 12
10260 2006-07-19 00:00:00.000 56 4 13
10261 2006-07-19 00:00:00.000 61 4 14
10262 2006-07-22 00:00:00.000 65 8 15
10263 2006-07-23 00:00:00.000 20 9 16
10264 2006-07-24 00:00:00.000 24 6 17
10265 2006-07-25 00:00:00.000 7 2 18
10266 2006-07-26 00:00:00.000 87 3 19
10267 2006-07-29 00:00:00.000 25 4 20
(10 row(s) affected)
-- Solution
WITH OrdersRN AS
(
SELECT orderid, orderdate, custid, empid,
ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
)
SELECT * FROM OrdersRN WHERE rownum BETWEEN 11 AND 20;
-- 3
-- Write a solution using a recursive CTE that returns the
-- management chain leading to Zoya Dolgopyatova (employee ID 9)
-- Tables involved: HR.Employees
-- Desired output:
empid mgrid firstname lastname
----------- ----------- ---------- --------------------
9 5 Zoya Dolgopyatova
5 2 Sven Buck
2 1 Don Funk
1 NULL Sara Davis
(4 row(s) affected)
-- Solution
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 9
UNION ALL
SELECT P.empid, P.mgrid, P.firstname, P.lastname
FROM EmpsCTE AS C
JOIN HR.Employees AS P
ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;
-- 4-1
-- Create a view that returns the total qty
-- for each employee and year
-- Tables involved: Sales.Orders and Sales.OrderDetails
-- Desired output when running:
-- SELECT * FROM Sales.VEmpOrders ORDER BY empid, orderyear
empid orderyear qty
----------- ----------- -----------
1 2006 1620
1 2007 3877
1 2008 2315
2 2006 1085
2 2007 2604
2 2008 2366
3 2006 940
3 2007 4436
3 2008 2476
4 2006 2212
4 2007 5273
4 2008 2313
5 2006 778
5 2007 1471
5 2008 787
6 2006 963
6 2007 1738
6 2008 826
7 2006 485
7 2007 2292
7 2008 1877
8 2006 923
8 2007 2843
8 2008 2147
9 2006 575
9 2007 955
9 2008 1140
(27 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.VEmpOrders') IS NOT NULL
DROP VIEW Sales.VEmpOrders;
GO
CREATE VIEW Sales.VEmpOrders
AS
SELECT
empid,
YEAR(orderdate) AS orderyear,
SUM(qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
GROUP BY
empid,
YEAR(orderdate);
GO
-- 4-2 (Optional, Advanced)
-- Write a query against Sales.VEmpOrders
-- that returns the running qty for each employee and year
-- Tables involved: TSQLFundamentals2008 database, Sales.VEmpOrders view
-- Desired output:
empid orderyear qty runqty
----------- ----------- ----------- -----------
1 2006 1620 1620
1 2007 3877 5497
1 2008 2315 7812
2 2006 1085 1085
2 2007 2604 3689
2 2008 2366 6055
3 2006 940 940
3 2007 4436 5376
3 2008 2476 7852
4 2006 2212 2212
4 2007 5273 7485
4 2008 2313 9798
5 2006 778 778
5 2007 1471 2249
5 2008 787 3036
6 2006 963 963
6 2007 1738 2701
6 2008 826 3527
7 2006 485 485
7 2007 2292 2777
7 2008 1877 4654
8 2006 923 923
8 2007 2843 3766
8 2008 2147 5913
9 2006 575 575
9 2007 955 1530
9 2008 1140 2670
(27 row(s) affected)
-- Solution
SELECT empid, orderyear, qty,
(SELECT SUM(qty)
FROM Sales.VEmpOrders AS V2
WHERE V2.empid = V1.empid
AND V2.orderyear <= V1.orderyear) AS runqty
FROM Sales.VEmpOrders AS V1
ORDER BY empid, orderyear;
-- 5-1
-- Create an inline function that accepts as inputs
-- a supplier id (@supid AS INT),
-- and a requested number of products (@n AS INT)
-- The function should return @n products with the highest unit prices
-- that are supplied by the given supplier id
-- Tables involved: Production.Products
-- Desired output when issuing the following query:
-- SELECT * FROM Production.fn_TopProducts(5, 2)
productid productname unitprice
----------- ---------------------------------------- ---------------------
12 Product OSFNS 38.00
11 Product QMVUN 21.00
(2 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
IF OBJECT_ID('Production.fn_TopProducts') IS NOT NULL
DROP FUNCTION Production.fn_TopProducts;
GO
CREATE FUNCTION Production.fn_TopProducts
(@supid AS INT, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) productid, productname, unitprice
FROM Production.Products
WHERE supplierid = @supid
ORDER BY unitprice DESC;
GO
-- 5-2
-- Using the CROSS APPLY operator
-- and the function you created in exercise 5-1,
-- return, for each supplier, the two most expensive products
-- Desired output
supplierid companyname productid productname unitprice
----------- --------------- ----------- --------------- ----------
8 Supplier BWGYE 20 Product QHFFP 81.00
8 Supplier BWGYE 68 Product TBTBL 12.50
20 Supplier CIYNM 43 Product ZZZHR 46.00
20 Supplier CIYNM 44 Product VJIEO 19.45
23 Supplier ELCRN 49 Product FPYPN 20.00
23 Supplier ELCRN 76 Product JYGFE 18.00
5 Supplier EQPNC 12 Product OSFNS 38.00
5 Supplier EQPNC 11 Product QMVUN 21.00
...
(55 row(s) affected)
-- Solution
SELECT S.supplierid, S.companyname, P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
CROSS APPLY Production.fn_TopProducts(S.supplierid, 2) AS P;
=========================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 5 - Table Expressions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Derived Tables
---------------------------------------------------------------------
USE TSQLFundamentals2008;
SELECT *
FROM (SELECT custid, companyname
FROM Sales.Customers
WHERE country = N'USA') AS USACusts;
GO
---------------------------------------------------------------------
-- Assigning Column Aliases
---------------------------------------------------------------------
-- Following fails
SELECT
YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY orderyear;
GO
-- Listing 5-1 Query with a Derived Table using Inline Aliasing Form
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS D
GROUP BY orderyear;
SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate);
-- External column aliasing
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate), custid
FROM Sales.Orders) AS D(orderyear, custid)
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Using Arguments
---------------------------------------------------------------------
-- Yearly Count of Customers handled by Employee 3
DECLARE @empid AS INT = 3;
/*
-- Prior to SQL Server 2008 use separate DECLARE and SET statements:
DECLARE @empid AS INT;
SET @empid = 3;
*/
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid = @empid) AS D
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Nesting
---------------------------------------------------------------------
-- Listing 5-2 Query with Nested Derived Tables
SELECT orderyear, numcusts
FROM (SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS D1
GROUP BY orderyear) AS D2
WHERE numcusts > 70;
SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)
HAVING COUNT(DISTINCT custid) > 70;
---------------------------------------------------------------------
-- Multiple References
---------------------------------------------------------------------
-- Listing 5-3 Multiple Derived Tables Based on the Same Query
SELECT Cur.orderyear,
Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
Cur.numcusts - Prv.numcusts AS growth
FROM (SELECT YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)) AS Cur
LEFT OUTER JOIN
(SELECT YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)) AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
---------------------------------------------------------------------
-- Common Table Expressions
---------------------------------------------------------------------
WITH USACusts AS
(
SELECT custid, companyname
FROM Sales.Customers
WHERE country = N'USA'
)
SELECT * FROM USACusts;
---------------------------------------------------------------------
-- Assigning Column Aliases
---------------------------------------------------------------------
-- Inline column aliasing
WITH C AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
-- External column aliasing
WITH C(orderyear, custid) AS
(
SELECT YEAR(orderdate), custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Using Arguments
---------------------------------------------------------------------
DECLARE @empid AS INT = 3;
/*
-- Prior to SQL Server 2008 use separate DECLARE and SET statements:
DECLARE @empid AS INT;
SET @empid = 3;
*/
WITH C AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO
---------------------------------------------------------------------
-- Defining Multiple CTEs
---------------------------------------------------------------------
WITH C1 AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
),
C2 AS
(
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C1
GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;
---------------------------------------------------------------------
-- Multiple References
---------------------------------------------------------------------
WITH YearlyCount AS
(
SELECT YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear,
Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
LEFT OUTER JOIN YearlyCount AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
---------------------------------------------------------------------
-- Recursive CTEs (Advanced, Optional)
---------------------------------------------------------------------
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 2
UNION ALL
SELECT C.empid, C.mgrid, C.firstname, C.lastname
FROM EmpsCTE AS P
JOIN HR.Employees AS C
ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;
---------------------------------------------------------------------
-- Views
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Views Described
---------------------------------------------------------------------
-- Creating VUSACusts View
USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
SELECT custid, companyname
FROM Sales.USACusts;
GO
---------------------------------------------------------------------
-- Views and ORDER BY
---------------------------------------------------------------------
-- ORDER BY in a View is not Allowed
ALTER VIEW Sales.USACusts
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO
-- Instead, use ORDER BY in Outer Query
SELECT custid, companyname, region
FROM Sales.USACusts
ORDER BY region;
GO
-- Do not Rely on TOP
ALTER VIEW Sales.USACusts
AS
SELECT TOP (100) PERCENT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO
-- Query USACusts
SELECT custid, companyname, region
FROM Sales.USACusts;
GO
---------------------------------------------------------------------
-- View Options
---------------------------------------------------------------------
---------------------------------------------------------------------
-- ENCRYPTION
---------------------------------------------------------------------
ALTER VIEW Sales.USACusts
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));
GO
ALTER VIEW Sales.USACusts WITH ENCRYPTION
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));
EXEC sp_helptext 'Sales.USACusts';
GO
---------------------------------------------------------------------
-- SCHEMABINDING
---------------------------------------------------------------------
ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO
-- Try a schema change
ALTER TABLE Sales.Customers DROP COLUMN address;
GO
---------------------------------------------------------------------
-- CHECK OPTION
---------------------------------------------------------------------
-- Notice that you can insert a row through the view
INSERT INTO Sales.USACusts(
companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax)
VALUES(
N'Customer ABCDE', N'Contact ABCDE', N'Title ABCDE', N'Address ABCDE',
N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789');
-- But when you query the view, you won't see it
SELECT custid, companyname, country
FROM Sales.USACusts
WHERE companyname = N'Customer ABCDE';
-- You can see it in the table, though
SELECT custid, companyname, country
FROM Sales.Customers
WHERE companyname = N'Customer ABCDE';
GO
-- Add CHECK OPTION to the View
ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS
SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO
-- Notice that you can't insert a row through the view
INSERT INTO Sales.USACusts(
companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax)
VALUES(
N'Customer FGHIJ', N'Contact FGHIJ', N'Title FGHIJ', N'Address FGHIJ',
N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789');
GO
-- Cleanup
DELETE FROM Sales.Customers
WHERE custid > 91;
DBCC CHECKIDENT('Sales.Customers', RESEED, 91);
IF OBJECT_ID('Sales.USACusts') IS NOT NULL DROP VIEW Sales.USACusts;
GO
---------------------------------------------------------------------
-- Inline User Defined Functions
---------------------------------------------------------------------
-- Creating fn_GetCustOrders function
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
(@cid AS INT) RETURNS TABLE
AS
RETURN
SELECT orderid, custid, empid, orderdate, requireddate,
shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
shipregion, shippostalcode, shipcountry
FROM Sales.Orders
WHERE custid = @cid;
GO
-- Test Function
SELECT orderid, custid
FROM dbo.fn_GetCustOrders(1) AS CO;
SELECT CO.orderid, CO.custid, OD.productid, OD.qty
FROM dbo.fn_GetCustOrders(1) AS CO
JOIN Sales.OrderDetails AS OD
ON CO.orderid = OD.orderid;
GO
-- Cleanup
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
DROP FUNCTION dbo.fn_GetCustOrders;
GO
---------------------------------------------------------------------
-- APPLY
---------------------------------------------------------------------
SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
CROSS JOIN HR.Employees AS E;
SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
CROSS APPLY HR.Employees AS E;
-- 3 most recent orders for each customer
SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
CROSS APPLY
(SELECT TOP(3) orderid, empid, orderdate, requireddate
FROM Sales.Orders AS O
WHERE O.custid = C.custid
ORDER BY orderdate DESC, orderid DESC) AS A;
-- 3 most recent orders for each customer,
-- Include customers without orders
SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
OUTER APPLY
(SELECT TOP(3) orderid, empid, orderdate, requireddate
FROM Sales.Orders AS O
WHERE O.custid = C.custid
ORDER BY orderdate DESC, orderid DESC) AS A;
-- Creation Script for the Function fn_TopOrders
IF OBJECT_ID('dbo.fn_TopOrders') IS NOT NULL
DROP FUNCTION dbo.fn_TopOrders;
GO
CREATE FUNCTION dbo.fn_TopOrders
(@custid AS INT, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) orderid, empid, orderdate, requireddate
FROM Sales.Orders
WHERE custid = @custid
ORDER BY orderdate DESC, orderid DESC;
GO
SELECT
C.custid, C.companyname,
A.orderid, A.empid, A.orderdate, A.requireddate
FROM Sales.Customers AS C
CROSS APPLY dbo.fn_TopOrders(C.custid, 3) AS A;
======================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 6 - Set Operations
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- Write a query that generates a virtual auxiliary table of 10 numbers
-- in the range 1 through 10
-- Tables involved: no table
--Desired output
n
-----------
1
2
3
4
5
6
7
8
9
10
(10 row(s) affected)
-- Solutions
SELECT 1 AS n
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10;
-- SQL Server 2008
SELECT n
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS Nums(n);
-- 2
-- Write a query that returns customer and employee pairs
-- that had order activity in January 2008 but not in February 2008
-- Tables involved: TSQLFundamentals2008 database, Orders table
--Desired output
custid empid
----------- -----------
1 1
3 3
5 8
5 9
6 9
7 6
9 1
12 2
16 7
17 1
20 7
24 8
25 1
26 3
32 4
38 9
39 3
40 2
41 2
42 2
44 8
47 3
47 4
47 8
49 7
55 2
55 3
56 6
59 8
63 8
64 9
65 3
65 8
66 5
67 5
70 3
71 2
75 1
76 2
76 5
80 1
81 1
81 3
81 4
82 6
84 1
84 3
84 4
88 7
89 4
(50 row(s) affected)
-- Solution
USE TSQLFundamentals2008;
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
EXCEPT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301';
-- 3
-- Write a query that returns customer and employee pairs
-- that had order activity in both January 2008 and February 2008
-- Tables involved: TSQLFundamentals2008 database, Orders table
--Desired output
custid empid
----------- -----------
20 3
39 9
46 5
67 1
71 4
(5 row(s) affected)
-- Solution
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
INTERSECT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301';
-- 4
-- Write a query that returns customer and employee pairs
-- that had order activity in both January 2008 and February 2008
-- but not in 2007
-- Tables involved: TSQLFundamentals2008 database, Orders table
--Desired output
custid empid
----------- -----------
67 1
46 5
(2 row(s) affected)
-- Solution
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
INTERSECT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301'
EXCEPT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';
-- With parentheses
(SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080101' AND orderdate < '20080201'
INTERSECT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20080201' AND orderdate < '20080301')
EXCEPT
SELECT custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';
-- 5
-- You are given the following query:
SELECT country, region, city
FROM HR.Employees
UNION ALL
SELECT country, region, city
FROM Production.Suppliers;
-- You are asked to add logic to the query
-- such that it would guarantee that the rows from Employees
-- would be returned in the output before the rows from Customers,
-- and within each segment, the rows should be sorted
-- by country, region, city
-- Tables involved: TSQLFundamentals2008 database, Employees and Suppliers tables
--Desired output
country region city
--------------- --------------- ---------------
UK NULL London
UK NULL London
UK NULL London
UK NULL London
USA WA Kirkland
USA WA Redmond
USA WA Seattle
USA WA Seattle
USA WA Tacoma
Australia NSW Sydney
Australia Victoria Melbourne
Brazil NULL Sao Paulo
Canada Québec Montréal
Canada Québec Ste-Hyacinthe
Denmark NULL Lyngby
Finland NULL Lappeenranta
France NULL Annecy
France NULL Montceau
France NULL Paris
Germany NULL Berlin
Germany NULL Cuxhaven
Germany NULL Frankfurt
Italy NULL Ravenna
Italy NULL Salerno
Japan NULL Osaka
Japan NULL Tokyo
Netherlands NULL Zaandam
Norway NULL Sandvika
Singapore NULL Singapore
Spain Asturias Oviedo
Sweden NULL Göteborg
Sweden NULL Stockholm
UK NULL London
UK NULL Manchester
USA LA New Orleans
USA MA Boston
USA MI Ann Arbor
USA OR Bend
(38 row(s) affected)
-- Solution
SELECT country, region, city
FROM (SELECT 1 AS sortcol, country, region, city
FROM HR.Employees
UNION ALL
SELECT 2, country, region, city
FROM Production.Suppliers) AS D
ORDER BY sortcol, country, region, city;
==============================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 6 - Set Operations
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- The UNION Set Operation
---------------------------------------------------------------------
-- The UNION ALL Set Operation
USE TSQLFundamentals2008;
SELECT country, region, city FROM HR.Employees
UNION ALL
SELECT country, region, city FROM Sales.Customers;
-- The UNION DISTINCT Set Operation
SELECT country, region, city FROM HR.Employees
UNION
SELECT country, region, city FROM Sales.Customers;
---------------------------------------------------------------------
-- The INTERSECT
---------------------------------------------------------------------
-- The INTERSECT DISTINCT Set Operation
SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;
-- The INTERSECT ALL Set Operation (Optional, Advanced)
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
INTERSECT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers;
WITH INTERSECT_ALL
AS
(
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
INTERSECT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers
)
SELECT country, region, city
FROM INTERSECT_ALL;
---------------------------------------------------------------------
-- The EXCEPT Set Operation
---------------------------------------------------------------------
-- The EXCEPT DISTINCT Set Operation
-- Employees EXCEPT Customers
SELECT country, region, city FROM HR.Employees
EXCEPT
SELECT country, region, city FROM Sales.Customers;
-- Customers EXCEPT Employees
SELECT country, region, city FROM Sales.Customers
EXCEPT
SELECT country, region, city FROM HR.Employees;
-- The EXCEPT ALL Set Operation (Optional, Advanced)
WITH EXCEPT_ALL
AS
(
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)) AS rownum,
country, region, city
FROM HR.Employees
EXCEPT
SELECT
ROW_NUMBER()
OVER(PARTITION BY country, region, city
ORDER BY (SELECT 0)),
country, region, city
FROM Sales.Customers
)
SELECT country, region, city
FROM EXCEPT_ALL;
---------------------------------------------------------------------
-- Precedence
---------------------------------------------------------------------
-- INTERSECT Precedes EXCEPT
SELECT country, region, city FROM Production.Suppliers
EXCEPT
SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;
-- Using Parenthesis
(SELECT country, region, city FROM Production.Suppliers
EXCEPT
SELECT country, region, city FROM HR.Employees)
INTERSECT
SELECT country, region, city FROM Sales.Customers;
---------------------------------------------------------------------
-- Circumventing Unsupported Logical Phases
-- (Optional, Advanced)
---------------------------------------------------------------------
-- Number of Cities per country Covered by Both Customers
-- and Employees
SELECT country, COUNT(*) AS numlocations
FROM (SELECT country, region, city FROM HR.Employees
UNION
SELECT country, region, city FROM Sales.Customers) AS U
GROUP BY country;
-- Two most recent orders for employees 3 and 5
SELECT empid, orderid, orderdate
FROM (SELECT TOP (2) empid, orderid, orderdate
FROM Sales.Orders
WHERE empid = 3
ORDER BY orderdate DESC, orderid DESC) AS D1
UNION ALL
SELECT empid, orderid, orderdate
FROM (SELECT TOP (2) empid, orderid, orderdate
FROM Sales.Orders
WHERE empid = 5
ORDER BY orderdate DESC, orderid DESC) AS D2;
=============================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 7 - Pivot, Unpivot and Grouping Sets
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- All exercises for this chapter will involve querying the Orders
-- table in the tempdb database that you created and populated
-- earlier by running the code in Listing 7-1
-- 1
-- Write a query against the Orders table that returns a row for each
-- employee, a column for each order year, and the count of orders
-- for each employee and order year
-- Desired output:
empid cnt2007 cnt2008 cnt2009
----------- ----------- ----------- -----------
1 1 1 1
2 1 2 1
3 2 0 2
-- Solutions
-- Using standard solution
USE tempdb;
SELECT empid,
COUNT(CASE WHEN orderyear = 2007 THEN orderyear END) AS cnt2007,
COUNT(CASE WHEN orderyear = 2008 THEN orderyear END) AS cnt2008,
COUNT(CASE WHEN orderyear = 2009 THEN orderyear END) AS cnt2009
FROM (SELECT empid, YEAR(orderdate) AS orderyear
FROM dbo.Orders) AS D
GROUP BY empid;
-- Using the native PIVOT operator
SELECT empid, [2007] AS cnt2007, [2008] AS cnt2008, [2009] AS cnt2009
FROM (SELECT empid, YEAR(orderdate) AS orderyear
FROM dbo.Orders) AS D
PIVOT(COUNT(orderyear)
FOR orderyear IN([2007], [2008], [2009])) AS P;
-- 2
-- Run the following code to create and populate the EmpYearOrders table:
USE tempdb;
IF OBJECT_ID('dbo.EmpYearOrders', 'U') IS NOT NULL DROP TABLE dbo.EmpYearOrders;
SELECT empid, [2007] AS cnt2007, [2008] AS cnt2008, [2009] AS cnt2009
INTO dbo.EmpYearOrders
FROM (SELECT empid, YEAR(orderdate) AS orderyear
FROM dbo.Orders) AS D
PIVOT(COUNT(orderyear)
FOR orderyear IN([2007], [2008], [2009])) AS P;
SELECT * FROM dbo.EmpYearOrders;
-- Output:
empid cnt2007 cnt2008 cnt2009
----------- ----------- ----------- -----------
1 1 1 1
2 1 2 1
3 2 0 2
-- Write a query against the EmpYearOrders table that unpivots
-- the data, returning a row for each employee and order year
-- with the number of orders
-- Exclude rows where the number of orders is 0
-- (in our example, employee 3 in year 2008)
-- Desired output:
empid orderyear numorders
----------- ----------- -----------
1 2007 1
1 2008 1
1 2009 1
2 2007 1
2 2008 2
2 2009 1
3 2007 2
3 2009 2
-- Solutions
-- Using standard solution
SELECT *
FROM (SELECT empid, orderyear,
CASE orderyear
WHEN 2007 THEN cnt2007
WHEN 2008 THEN cnt2008
WHEN 2009 THEN cnt2009
END AS numorders
FROM dbo.EmpYearOrders
CROSS JOIN (VALUES(2007),(2008),(2009)) AS Years (orderyear)) AS D
WHERE numorders <> 0;
SELECT *
FROM (SELECT empid, orderyear,
CASE orderyear
WHEN 2007 THEN cnt2007
WHEN 2008 THEN cnt2008
WHEN 2009 THEN cnt2009
END AS numorders
FROM dbo.EmpYearOrders
CROSS JOIN (SELECT 2007 AS orderyear
UNION ALL SELECT 2008
UNION ALL SELECT 2009) AS Years) AS D
WHERE numorders <> 0;
-- Using the native UNPIVOT operator
SELECT empid, CAST(RIGHT(orderyear, 4) AS INT) AS orderyear, numorders
FROM dbo.EmpYearOrders
UNPIVOT(numorders FOR orderyear IN(cnt2007, cnt2008, cnt2009)) AS U
WHERE numorders <> 0;
-- 3
-- Write a query against the Orders table that returns the
-- total quantities for each:
-- employee, customer, and order year
-- employee and order year
-- customer and order year
-- Include a result column in the output that uniquely identifies
-- the grouping set with which the current row is associated
-- Desired output:
groupingset empid custid orderyear sumqty
----------- ----------- ------ ----------- -----------
0 2 A 2007 12
0 3 A 2007 10
4 NULL A 2007 22
0 2 A 2008 40
4 NULL A 2008 40
0 3 A 2009 10
4 NULL A 2009 10
0 1 B 2007 20
4 NULL B 2007 20
0 2 B 2008 12
4 NULL B 2008 12
0 2 B 2009 15
4 NULL B 2009 15
0 3 C 2007 22
4 NULL C 2007 22
0 1 C 2008 14
4 NULL C 2008 14
0 1 C 2009 20
4 NULL C 2009 20
0 3 D 2009 30
4 NULL D 2009 30
2 1 NULL 2007 20
2 2 NULL 2007 12
2 3 NULL 2007 32
2 1 NULL 2008 14
2 2 NULL 2008 52
2 1 NULL 2009 20
2 2 NULL 2009 15
2 3 NULL 2009 40
(29 row(s) affected)
-- Solution
SELECT
GROUPING_ID(empid, custid, YEAR(Orderdate)) AS groupingset,
empid, custid, YEAR(Orderdate) AS orderyear, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY
GROUPING SETS
(
(empid, custid, YEAR(orderdate)),
(empid, YEAR(orderdate)),
(custid, YEAR(orderdate))
);
======================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 7 - Pivot, Unpivot and Grouping Sets
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Pivoting Data
---------------------------------------------------------------------
-- Listing 1: Code to Create and Populate the Orders Table
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATE NOT NULL, -- prior to SQL Server 2008 use DATETIME
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
(30001, '20070802', 3, 'A', 10),
(10001, '20071224', 2, 'A', 12),
(10005, '20071224', 1, 'B', 20),
(40001, '20080109', 2, 'A', 40),
(10006, '20080118', 1, 'C', 14),
(20001, '20080212', 2, 'B', 12),
(40005, '20090212', 3, 'A', 10),
(20002, '20090216', 1, 'C', 20),
(30003, '20090418', 2, 'B', 15),
(30004, '20070418', 3, 'C', 22),
(30007, '20090907', 3, 'D', 30);
/*
-- Note The ability to use a single VALUES clause to insert
-- multiple rows to a table is new in Microsoft SQL Server 2008.
-- If you’re working with an earlier version of SQL Server,
-- substitute the single INSERT statement in Listing 7-1 with an
-- INSERT statement per each row:
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30001, '20070802', 3, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10001, '20071224', 2, 'A', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10005, '20071224', 1, 'B', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40001, '20080109', 2, 'A', 40);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10006, '20080118', 1, 'C', 14);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20001, '20080212', 2, 'B', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40005, '20090212', 3, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20002, '20090216', 1, 'C', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30003, '20090418', 2, 'B', 15);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30004, '20070418', 3, 'C', 22);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30007, '20090907', 3, 'D', 30);
*/
SELECT * FROM dbo.Orders;
-- Query against Orders, grouping by employee and customer
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;
---------------------------------------------------------------------
-- Pivoting with Standard SQL
---------------------------------------------------------------------
-- Query against Orders, grouping by employee, pivoting customers,
-- aggregating sum of quantity
SELECT empid,
SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
SUM(CASE WHEN custid = 'D' THEN qty END) AS D
FROM dbo.Orders
GROUP BY empid;
---------------------------------------------------------------------
-- Pivoting with the Native T-SQL PIVOT Operator
---------------------------------------------------------------------
-- Logical equivalent of previous query using the native PIVOT operator
SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
-- Query demonstrating the problem with implicit grouping
SELECT empid, A, B, C, D
FROM dbo.Orders
PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
-- Logical equivalent of previous query
SELECT empid,
SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
SUM(CASE WHEN custid = 'D' THEN qty END) AS D
FROM dbo.Orders
GROUP BY orderid, orderdate, empid;
-- Query against Orders, grouping by customer, pivoting employees,
-- aggregating sum of quantity
SELECT custid, [1], [2], [3]
FROM (SELECT empid, custid, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR empid IN([1], [2], [3])) AS P;
---------------------------------------------------------------------
-- Unpivoting Data
---------------------------------------------------------------------
-- Code to create and populate the EmpCustOrders table
USE tempdb;
IF OBJECT_ID('dbo.EmpCustOrders', 'U') IS NOT NULL DROP TABLE dbo.EmpCustOrders;
SELECT empid, A, B, C, D
INTO dbo.EmpCustOrders
FROM (SELECT empid, custid, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
SELECT * FROM dbo.EmpCustOrders;
---------------------------------------------------------------------
-- Unpivoting with Standard SQL
---------------------------------------------------------------------
-- Unpivot Step 1: generate copies
SELECT *
FROM dbo.EmpCustOrders
CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);
SELECT *
FROM dbo.EmpCustOrders
CROSS JOIN (SELECT 'A' AS custid
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D') AS Custs;
-- Unpivot Step 2: extract elements
SELECT empid, custid,
CASE custid
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END AS qty
FROM dbo.EmpCustOrders
CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);
-- Unpivot Step 3: eliminate NULLs
SELECT *
FROM (SELECT empid, custid,
CASE custid
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END AS qty
FROM dbo.EmpCustOrders
CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)) AS D
WHERE qty IS NOT NULL;
---------------------------------------------------------------------
-- Unpivoting with the Native T-SQL UNPIVOT Operator
---------------------------------------------------------------------
-- Query using the native UNPIVOT operator
SELECT empid, custid, qty
FROM dbo.EmpCustOrders
UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;
---------------------------------------------------------------------
-- Grouping Sets
---------------------------------------------------------------------
-- Four queries, each with a different grouping set
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;
SELECT empid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid;
SELECT custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY custid;
SELECT SUM(qty) AS sumqty
FROM dbo.Orders;
-- Unifying result sets of four queries
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid
UNION ALL
SELECT empid, NULL, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid
UNION ALL
SELECT NULL, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY custid
UNION ALL
SELECT NULL, NULL, SUM(qty) AS sumqty
FROM dbo.Orders;
---------------------------------------------------------------------
-- GROUPING SETS Subclause
---------------------------------------------------------------------
-- Using the GROUPING SETS subclause
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY
GROUPING SETS
(
(empid, custid),
(empid),
(custid),
()
);
---------------------------------------------------------------------
-- CUBE Subclause
---------------------------------------------------------------------
-- Using the CUBE subclause
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY CUBE(empid, custid);
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid
WITH CUBE;
---------------------------------------------------------------------
-- ROLLUP Subclause
---------------------------------------------------------------------
-- Using the ROLLUP subclause
SELECT
YEAR(orderdate) AS orderyear,
MONTH(orderdate) AS ordermonth,
DAY(orderdate) AS orderday,
SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate));
SELECT
YEAR(orderdate) AS orderyear,
MONTH(orderdate) AS ordermonth,
DAY(orderdate) AS orderday,
SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY YEAR(orderdate), MONTH(orderdate), DAY(orderdate)
WITH ROLLUP;
---------------------------------------------------------------------
-- GROUPING and GROUPING_ID Function
---------------------------------------------------------------------
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY CUBE(empid, custid);
SELECT
GROUPING(empid) AS grpemp,
GROUPING(custid) AS grpcust,
empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY CUBE(empid, custid);
SELECT
GROUPING_ID(empid, custid) AS groupingset,
empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY CUBE(empid, custid);
=======================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 8 - Data Modification
-- Solutions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- Run the following code to create the Customers table
-- in the tempdb database
USE tempdb;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
CREATE TABLE dbo.Customers
(
custid INT NOT NULL PRIMARY KEY,
companyname NVARCHAR(40) NOT NULL,
country NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
city NVARCHAR(15) NOT NULL
);
GO
-- 1-1
-- Insert into the Customers table a row with:
-- custid: 100
-- companyname: Company ABCDE
-- country: USA
-- region: WA
-- city: Redmond
-- Solution:
USE tempdb;
INSERT INTO dbo.Customers(custid, companyname, country, region, city)
VALUES(100, N'Company ABCDE', N'USA', N'WA', N'Redmond');
-- 1-2
-- Insert into the Customers table
-- all customers from TSQLFundamentals2008.Sales.Customers
-- who placed orders
-- Solution:
USE tempdb;
INSERT INTO dbo.Customers(custid, companyname, country, region, city)
SELECT custid, companyname, country, region, city
FROM TSQLFundamentals2008.Sales.Customers AS C
WHERE EXISTS
(SELECT * FROM TSQLFundamentals2008.Sales.Orders AS O
WHERE O.custid = C.custid);
-- 1-3
-- Use a SELECT INTO statement to create and populate an Orders table
-- in tempdb, with orders from the Orders table in TSQLFundamentals2008
-- that were placed in the years 2006 through 2008
-- Solution:
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
SELECT *
INTO dbo.Orders
FROM TSQLFundamentals2008.Sales.Orders
WHERE orderdate >= '20060101'
AND orderdate < '20090101';
-- 2
-- Delete from the Orders table in tempdb
-- orders that were placed before August 2006
-- Use the OUTPUT clause to return the orderid and orderdate
-- of the deleted orders
-- Desired output:
orderid orderdate
----------- -----------------------
10248 2006-07-04 00:00:00.000
10249 2006-07-05 00:00:00.000
10250 2006-07-08 00:00:00.000
10251 2006-07-08 00:00:00.000
10252 2006-07-09 00:00:00.000
10253 2006-07-10 00:00:00.000
10254 2006-07-11 00:00:00.000
10255 2006-07-12 00:00:00.000
10256 2006-07-15 00:00:00.000
10257 2006-07-16 00:00:00.000
10258 2006-07-17 00:00:00.000
10259 2006-07-18 00:00:00.000
10260 2006-07-19 00:00:00.000
10261 2006-07-19 00:00:00.000
10262 2006-07-22 00:00:00.000
10263 2006-07-23 00:00:00.000
10264 2006-07-24 00:00:00.000
10265 2006-07-25 00:00:00.000
10266 2006-07-26 00:00:00.000
10267 2006-07-29 00:00:00.000
10268 2006-07-30 00:00:00.000
10269 2006-07-31 00:00:00.000
(22 row(s) affected)
-- Solution:
USE tempdb;
DELETE FROM dbo.Orders
OUTPUT deleted.orderid, deleted.orderdate
WHERE orderdate < '20060801';
-- 3
-- Delete orders placed by customers from Brazil
-- Solution:
USE tempdb;
DELETE FROM dbo.Orders
WHERE EXISTS
(SELECT *
FROM dbo.Customers AS C
WHERE Orders.custid = C.custid
AND C.country = N'Brazil');
DELETE FROM O
FROM dbo.Orders AS O
JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE country = N'Brazil';
MERGE INTO dbo.Orders AS O
USING dbo.Customers AS C
ON O.custid = C.custid
AND country = N'Brazil'
WHEN MATCHED THEN DELETE;
-- 4-1
-- Run the following query against Customers,
-- and notice that some rows have a NULL in the region column
SELECT * FROM dbo.Customers;
-- Output:
custid companyname country region city
----------- -------------- --------------- ---------- ---------------
1 Customer NRZBB Germany NULL Berlin
2 Customer MLTDN Mexico NULL México D.F.
3 Customer KBUDE Mexico NULL México D.F.
4 Customer HFBZG UK NULL London
5 Customer HGVLZ Sweden NULL Luleå
6 Customer XHXJV Germany NULL Mannheim
7 Customer QXVLA France NULL Strasbourg
8 Customer QUHWH Spain NULL Madrid
9 Customer RTXGC France NULL Marseille
10 Customer EEALV Canada BC Tsawassen
...
(90 row(s) affected)
-- 4-2
-- Update the Customers table and change all NULL region values to '<None>'
-- Use the OUTPUT clause to show the custid, old region and new region
-- Desired output:
custid oldregion newregion
----------- --------------- ---------------
1 NULL <None>
2 NULL <None>
3 NULL <None>
4 NULL <None>
5 NULL <None>
6 NULL <None>
7 NULL <None>
8 NULL <None>
9 NULL <None>
11 NULL <None>
12 NULL <None>
13 NULL <None>
14 NULL <None>
16 NULL <None>
17 NULL <None>
18 NULL <None>
19 NULL <None>
20 NULL <None>
23 NULL <None>
24 NULL <None>
25 NULL <None>
26 NULL <None>
27 NULL <None>
28 NULL <None>
29 NULL <None>
30 NULL <None>
39 NULL <None>
40 NULL <None>
41 NULL <None>
44 NULL <None>
49 NULL <None>
50 NULL <None>
52 NULL <None>
53 NULL <None>
54 NULL <None>
56 NULL <None>
58 NULL <None>
59 NULL <None>
60 NULL <None>
63 NULL <None>
64 NULL <None>
66 NULL <None>
68 NULL <None>
69 NULL <None>
70 NULL <None>
72 NULL <None>
73 NULL <None>
74 NULL <None>
76 NULL <None>
79 NULL <None>
80 NULL <None>
83 NULL <None>
84 NULL <None>
85 NULL <None>
86 NULL <None>
87 NULL <None>
90 NULL <None>
91 NULL <None>
(58 row(s) affected)
-- Solution:
USE tempdb;
UPDATE dbo.Customers
SET region = '<None>'
OUTPUT
deleted.custid,
deleted.region AS oldregion,
inserted.region AS newregion
WHERE region IS NULL;
-- 5
-- Update all orders placed by UK customers
-- and set their shipcountry, shipregion, shipcity values
-- to the country, region, city values of the corresponding customers
-- Solutions:
USE tempdb;
UPDATE O
SET shipcountry = C.country,
shipregion = C.region,
shipcity = C.city
FROM dbo.Orders AS O
JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE C.country = 'UK';
WITH CTE_UPD AS
(
SELECT
O.shipcountry AS ocountry, C.country AS ccountry,
O.shipregion AS oregion, C.region AS cregion,
O.shipcity AS ocity, C.city AS ccity
FROM dbo.Orders AS O
JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE C.country = 'UK'
)
UPDATE CTE_UPD
SET ocountry = ccountry, oregion = cregion, ocity = ccity;
MERGE INTO dbo.Orders AS O
USING dbo.Customers AS C
ON O.custid = C.custid
AND C.country = 'UK'
WHEN MATCHED THEN
UPDATE SET shipcountry = C.country,
shipregion = C.region,
shipcity = C.city;
====================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 8 - Data Modification
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Inserting Data
---------------------------------------------------------------------
---------------------------------------------------------------------
-- INSERT VALUES
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL
CONSTRAINT PK_Orders PRIMARY KEY,
orderdate DATE NOT NULL
CONSTRAINT DFT_orderdate DEFAULT(CURRENT_TIMESTAMP),
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL
)
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
VALUES(10001, '20090212', 3, 'A');
INSERT INTO dbo.Orders(orderid, empid, custid)
VALUES(10002, 5, 'B');
INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid)
VALUES
(10003, '20090213', 4, 'B'),
(10004, '20090214', 1, 'A'),
(10005, '20090213', 1, 'C'),
(10006, '20090215', 3, 'C');
SELECT *
FROM ( VALUES
(10003, '20090213', 4, 'B'),
(10004, '20090214', 1, 'A'),
(10005, '20090213', 1, 'C'),
(10006, '20090215', 3, 'C') )
AS O(orderid, orderdate, empid, custid);
---------------------------------------------------------------------
-- INSERT SELECT
---------------------------------------------------------------------
USE tempdb;
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
SELECT orderid, orderdate, empid, custid
FROM TSQLFundamentals2008.Sales.Orders
WHERE shipcountry = 'UK';
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
SELECT 10007, '20090215', 2, 'B' UNION ALL
SELECT 10008, '20090215', 1, 'C' UNION ALL
SELECT 10009, '20090216', 2, 'C' UNION ALL
SELECT 10010, '20090216', 3, 'A';
---------------------------------------------------------------------
-- INSERT EXEC
---------------------------------------------------------------------
USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.usp_getorders', 'P') IS NOT NULL
DROP PROC Sales.usp_getorders;
GO
CREATE PROC Sales.usp_getorders
@country AS NVARCHAR(40)
AS
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE shipcountry = @country;
GO
EXEC Sales.usp_getorders @country = 'France';
USE tempdb;
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
EXEC TSQLFundamentals2008.Sales.usp_getorders @country = 'France';
---------------------------------------------------------------------
-- SELECT INTO
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
SELECT orderid, orderdate, empid, custid
INTO dbo.Orders
FROM TSQLFundamentals2008.Sales.Orders;
-- SELECT INTO with Set Operations
USE tempdb;
IF OBJECT_ID('dbo.Locations', 'U') IS NOT NULL DROP TABLE dbo.Locations;
SELECT country, region, city
INTO dbo.Locations
FROM TSQLFundamentals2008.Sales.Customers
EXCEPT
SELECT country, region, city
FROM TSQLFundamentals2008.HR.Employees;
---------------------------------------------------------------------
-- BULK INSERT
---------------------------------------------------------------------
USE tempdb;
BULK INSERT dbo.Orders FROM 'c:\temp\orders.txt'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
GO
---------------------------------------------------------------------
-- IDENTITY
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_T1 PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
CONSTRAINT CHK_T1_datacol CHECK(datacol LIKE '[A-Z]%')
);
GO
INSERT INTO dbo.T1(datacol) VALUES('AAAAA');
INSERT INTO dbo.T1(datacol) VALUES('CCCCC');
INSERT INTO dbo.T1(datacol) VALUES('BBBBB');
SELECT * FROM dbo.T1;
SELECT $identity FROM dbo.T1;
-- Using SCOPE_IDENTITY
DECLARE @new_key AS INT;
INSERT INTO dbo.T1(datacol) VALUES('AAAAA');
SET @new_key = SCOPE_IDENTITY();
SELECT @new_key AS new_key
-- Run from another connection
SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
@@identity AS [@@identity],
IDENT_CURRENT('dbo.T1') AS [IDENT_CURRENT];
GO
-- Run insert statements
INSERT INTO dbo.T1(datacol) VALUES('12345');
GO
INSERT INTO dbo.T1(datacol) VALUES('EEEEE');
GO
SELECT * FROM dbo.T1;
-- Using IDENTITY_INSERT
SET IDENTITY_INSERT dbo.T1 ON;
INSERT INTO dbo.T1(keycol, datacol) VALUES(5, 'FFFFF');
SET IDENTITY_INSERT dbo.T1 OFF;
INSERT INTO dbo.T1(datacol) VALUES('GGGGG');
SELECT * FROM dbo.T1;
---------------------------------------------------------------------
-- Deleting Data
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
SELECT * INTO dbo.Customers FROM TSQLFundamentals2008.Sales.Customers;
SELECT * INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders;
ALTER TABLE dbo.Customers ADD
CONSTRAINT PK_Customers PRIMARY KEY(custid);
ALTER TABLE dbo.Orders ADD
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
REFERENCES dbo.Customers(custid);
GO
---------------------------------------------------------------------
-- DELETE Statement
---------------------------------------------------------------------
USE tempdb;
DELETE FROM dbo.Orders
WHERE orderdate < '20070101';
---------------------------------------------------------------------
-- TRUNCATE
---------------------------------------------------------------------
TRUNCATE TABLE dbo.T1;
---------------------------------------------------------------------
-- DELETE Based on a Join
---------------------------------------------------------------------
USE tempdb;
DELETE FROM O
FROM dbo.Orders AS O
JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE C.country = N'USA';
-- Using a subquery
DELETE FROM dbo.Orders
WHERE EXISTS
(SELECT *
FROM dbo.Customers AS C
WHERE Orders.custid = C.custid
AND C.country = N'USA');
---------------------------------------------------------------------
-- Updating Data
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
SELECT * INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders;
SELECT * INTO dbo.OrderDetails FROM TSQLFundamentals2008.Sales.OrderDetails;
ALTER TABLE dbo.Orders ADD
CONSTRAINT PK_Orders PRIMARY KEY(orderid);
ALTER TABLE dbo.OrderDetails ADD
CONSTRAINT PK_OrderDetails PRIMARY KEY(orderid, productid),
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY(orderid)
REFERENCES dbo.Orders(orderid);
---------------------------------------------------------------------
-- UPDATE Statement
---------------------------------------------------------------------
USE tempdb;
UPDATE dbo.OrderDetails
SET discount = discount + 0.05
WHERE productid = 51;
-- In SQL Server 2008
UPDATE dbo.OrderDetails
SET discount += 0.05
WHERE productid = 51;
GO
UPDATE dbo.T1
SET col1 = col1 + 10, col2 = col1 + 10;
UPDATE dbo.T1
SET col1 = col2, col2 = col1;
GO
---------------------------------------------------------------------
-- UPDATE Based on a Join
---------------------------------------------------------------------
-- Listing 8-1 Update Statement Based on a Join
UPDATE OD
SET discount = discount + 0.05
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE custid = 1;
UPDATE dbo.OrderDetails
SET discount = discount + 0.05
WHERE EXISTS
(SELECT * FROM dbo.Orders AS O
WHERE O.orderid = OrderDetails.orderid
AND custid = 1);
GO
UPDATE T1
SET col1 = T2.col1,
col2 = T2.col2,
col3 = T2.col3
FROM dbo.T1 JOIN dbo.T2
ON T2.keycol = T1.keycol
WHERE T2.col4 = 'ABC';
UPDATE dbo.T1
SET col1 = (SELECT col1
FROM dbo.T2
WHERE T2.keycol = T1.keycol),
col2 = (SELECT col2
FROM dbo.T2
WHERE T2.keycol = T1.keycol),
col3 = (SELECT col3
FROM dbo.T2
WHERE T2.keycol = T1.keycol)
WHERE EXISTS
(SELECT *
FROM dbo.T2
WHERE T2.keycol = T1.keycol
AND T2.col4 = 'ABC');
GO
/*
UPDATE dbo.T1
SET (col1, col2, col3) =
(SELECT col1, col2, col3
FROM dbo.T2
WHERE T2.keycol = T1.keycol)
WHERE EXISTS
(SELECT *
FROM dbo.T2
WHERE T2.keycol = T1.keycol
AND T2.col4 = 'ABC');
*/
GO
---------------------------------------------------------------------
-- Assignment UPDATE
---------------------------------------------------------------------
USE tempdb;
-- Custom Sequence
IF OBJECT_ID('dbo.Sequence', 'U') IS NOT NULL DROP TABLE dbo.Sequence;
CREATE TABLE dbo.Sequence(val INT NOT NULL);
INSERT INTO dbo.Sequence VALUES(0);
DECLARE @nextval AS INT;
UPDATE Sequence SET @nextval = val = val + 1;
SELECT @nextval;
---------------------------------------------------------------------
-- Merging Data
---------------------------------------------------------------------
-- Listing 8-2 Code that Creates and Populates Customers and CustomersStage
USE tempdb;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
GO
CREATE TABLE dbo.Customers
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
CONSTRAINT PK_Customers PRIMARY KEY(custid)
);
INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');
IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL DROP TABLE dbo.CustomersStage;
GO
CREATE TABLE dbo.CustomersStage
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)
);
INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)
VALUES
(2, 'AAAAA', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(5, 'BBBBB', 'CCCCC', 'DDDDD'),
(6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
(7, 'cust 7 (new)', '(777) 777-7777', 'address 7');
-- Query tables
SELECT * FROM dbo.Customers;
SELECT * FROM dbo.CustomersStage;
-- MERGE Example 1: Update existing, add missing
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);
-- MERGE Example 2: Update existing, add missing, delete missing in source
MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
-- MERGE Example 3: Update existing that changed, add missing
MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED AND
( TGT.companyname <> SRC.companyname
OR TGT.phone <> SRC.phone
OR TGT.address <> SRC.address) THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);
---------------------------------------------------------------------
-- Modifying Data through Table Expressions
---------------------------------------------------------------------
USE tempdb;
UPDATE OD
SET discount = discount + 0.05
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE custid = 1;
WITH C AS
(
SELECT custid, OD.orderid,
productid, discount, discount + 0.05 AS newdiscount
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE custid = 1
)
UPDATE C
SET discount = newdiscount;
UPDATE D
SET discount = newdiscount
FROM ( SELECT custid, OD.orderid,
productid, discount, discount + 0.05 AS newdiscount
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE custid = 1 ) AS D;
-- Update with row numbers
USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT, col2 INT);
GO
INSERT INTO dbo.T1(col1) VALUES(10);
INSERT INTO dbo.T1(col1) VALUES(20);
INSERT INTO dbo.T1(col1) VALUES(30);
SELECT * FROM dbo.T1;
GO
UPDATE dbo.T1
SET col2 = ROW_NUMBER() OVER(ORDER BY col1);
/*
Msg 4108, Level 15, State 1, Line 2
Windowed functions can only appear in the SELECT or ORDER BY clauses.
*/
GO
WITH C AS
(
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1) AS rownum
FROM dbo.T1
)
UPDATE C
SET col2 = rownum;
SELECT * FROM dbo.T1;
---------------------------------------------------------------------
-- Modifications with the TOP Option
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
SELECT * INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders;
DELETE TOP(50) FROM dbo.Orders;
UPDATE TOP(50) dbo.Orders
SET freight = freight + 10.00;
WITH C AS
(
SELECT TOP(50) *
FROM dbo.Orders
ORDER BY orderid
)
DELETE FROM C;
WITH C AS
(
SELECT TOP(50) *
FROM dbo.Orders
ORDER BY orderid DESC
)
UPDATE C
SET freight = freight + 10.00;
---------------------------------------------------------------------
-- The OUTPUT Clause
---------------------------------------------------------------------
---------------------------------------------------------------------
-- INSERT with OUTPUT
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
keycol INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_T1 PRIMARY KEY,
datacol NVARCHAR(40) NOT NULL
);
INSERT INTO dbo.T1(datacol)
OUTPUT inserted.keycol, inserted.datacol
SELECT lastname
FROM TSQLFundamentals2008.HR.Employees
WHERE country = N'USA';
DECLARE @NewRows TABLE(keycol INT, datacol NVARCHAR(40));
INSERT INTO dbo.T1(datacol)
OUTPUT inserted.keycol, inserted.datacol
INTO @NewRows
SELECT lastname
FROM TSQLFundamentals2008.HR.Employees
WHERE country = N'UK';
SELECT * FROM @NewRows;
---------------------------------------------------------------------
-- DELETE with OUTPUT
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
SELECT * INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders;
DELETE FROM dbo.Orders
OUTPUT
deleted.orderid,
deleted.orderdate,
deleted.empid,
deleted.custid
WHERE orderdate < '20080101';
---------------------------------------------------------------------
-- UPDATE with OUTPUT
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails;
SELECT * INTO dbo.OrderDetails FROM TSQLFundamentals2008.Sales.OrderDetails;
UPDATE dbo.OrderDetails
SET discount = discount + 0.05
OUTPUT
inserted.productid,
deleted.discount AS olddiscount,
inserted.discount AS newdiscount
WHERE productid = 51;
---------------------------------------------------------------------
-- MERGE with OUTPUT
---------------------------------------------------------------------
-- First, run Listing 8-2 to recreate Customers and CustomersStage
USE tempdb;
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
OUTPUT $action, inserted.custid,
deleted.companyname AS oldcompanyname,
inserted.companyname AS newcompanyname,
deleted.phone AS oldphone,
inserted.phone AS newphone,
deleted.address AS oldaddress,
inserted.address AS newaddress;
---------------------------------------------------------------------
-- Composable DML
---------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('dbo.ProductsAudit', 'U') IS NOT NULL DROP TABLE dbo.ProductsAudit;
IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL DROP TABLE dbo.Products;
SELECT * INTO dbo.Products FROM TSQLFundamentals2008.Production.Products;
CREATE TABLE dbo.ProductsAudit
(
LSN INT NOT NULL IDENTITY PRIMARY KEY,
TS DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
productid INT NOT NULL,
colname SYSNAME NOT NULL,
oldval SQL_VARIANT NOT NULL,
newval SQL_VARIANT NOT NULL
);
INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
SELECT productid, N'unitprice', oldval, newval
FROM (UPDATE dbo.Products
SET unitprice *= 1.15
OUTPUT
inserted.productid,
deleted.unitprice AS oldval,
inserted.unitprice AS newval
WHERE SupplierID = 1) AS D
WHERE oldval < 20.0 AND newval >= 20.0;
SELECT * FROM dbo.ProductsAudit;
========================Transactions=======================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 9 - Transactions
-- Exercises
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- In all exercises in this chapter, make sure you are connected
-- to the TSQLFundamentals2008 sample database
-- by running the following code:
USE TSQLFundamentals2008;
---------------------------------------------------------------------
-- Blocking
---------------------------------------------------------------------
-- 1-1
-- Open three connections
-- (call them Connection 1, Connection 2 and Connection 3)
-- Run the following code in Connection 1
-- to update rows in Sales.OrderDetails:
BEGIN TRAN;
UPDATE Sales.OrderDetails
SET discount = 0.05
WHERE orderid = 10249;
-- 1-2
-- Run the following code in Connection 2 to query Sales.OrderDetails
-- Connection 2 will be blocked:
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 1-3
-- Run the following code in Connection 3
-- and identify the locks and process ids
-- involved in the blocking chain:
SELECT -- use * to explore
request_session_id AS spid,
resource_type AS restype,
resource_database_id AS dbid,
resource_description AS res,
resource_associated_entity_id AS resid,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks;
-- 1-4
-- In the following exercises replace the session ids 52, 53
-- with the ones you found to be involved in the blocking chain
-- in the previous exercise
-- Run the following code to obtain connection/session/blocking
-- information about the processes involved in the blocking chain
-- Connection info
SELECT -- use * to explore
session_id AS spid,
connect_time,
last_read,
last_write,
most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id IN(52, 53);
-- Session info
SELECT -- use * to explore
session_id AS spid,
login_time,
host_name,
program_name,
login_name,
nt_user_name,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id IN(52, 53);
-- Blocking
SELECT -- use * to explore
session_id AS spid,
blocking_session_id,
command,
sql_handle,
database_id,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
-- 1-5
-- Run the following code to obtain the SQL text of the connections
-- involved in the blocking chain:
-- SQL text
SELECT session_id, text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE session_id IN(52, 53);
-- 1-6
-- Run the following code in Connection 1 to rollback the transaction:
ROLLBACK TRAN;
-- Observe in Connection 2 that the SELECT query returned the
-- two order detail rows, and that those rows were not modified
-- Close all connections
---------------------------------------------------------------------
-- Isolation Levels
---------------------------------------------------------------------
---------------------------------------------------------------------
-- The READ UNCOMMITTED Isolation Level
---------------------------------------------------------------------
-- 2-1a
-- Open two new connections (call them Connection 1 and Connection 2)
-- 2-1b
-- Run the following code in Connection 1 to update
-- rows in Sales.OrderDetails and query it:
BEGIN TRAN;
UPDATE Sales.OrderDetails
SET discount = discount + 0.05
WHERE orderid = 10249;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-1c
-- Run the following code in Connection 2
-- to set the isolation level to READ UNCOMMITTED
-- and query Sales.OrderDetails:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- Notice that you get the modified, uncommitted version of the rows
-- 2-1d
-- Run the following code in Connection 1 to rollback the transaction
ROLLBACK TRAN;
---------------------------------------------------------------------
-- The READ COMMITTED Isolation Level
---------------------------------------------------------------------
-- 2-2a
-- Run the following code in Connection 1 to update
-- rows in Sales.OrderDetails and query it:
BEGIN TRAN;
UPDATE Sales.OrderDetails
SET discount = discount + 0.05
WHERE orderid = 10249;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-2b
-- Run the following code in Connection 2
-- to set the isolation level to READ COMMITTED
-- and query Sales.OrderDetails:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- Notice that you are now blocked
-- 2-2c
-- Run the following code in Connection 1 to commit the transaction
COMMIT TRAN;
-- 2-2d
-- Go to connection 2 and noticed that you got
-- the modified, committed version of the rows
-- 2-2e
-- Run the following code for cleanup
UPDATE Sales.OrderDetails
SET discount = 0.00
WHERE orderid = 10249;
---------------------------------------------------------------------
-- The REPEATABLE READ Isolation Level
---------------------------------------------------------------------
-- 2-3a
-- Run the following code in Connection 1 to set the isolation
-- to REPEATABLE READ, open a transaction and read data from Sales.OrderDetails:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- You get two rows with discount values 0.00
-- 2-3b
-- Run the following code in Connection 2 and notice you are blocked:
UPDATE Sales.OrderDetails
SET discount = discount + 0.05
WHERE orderid = 10249;
-- 2-3c
-- Run the following code in Connection 1 to read the data again
-- and commit the transaction:
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
COMMIT TRAN;
-- You will get the two rows with discount values 0.00 again,
-- giving you repeatable reads. Note that if your code was running
-- under a lower isolation level (READ UNCOMMITTED or READ COMMITTED),
-- the UPDATE statement wouldn’t have been blocked,
-- and you would have gotten non repeatable reads.
-- 2-3d
-- Go to Connection 2 and notice that the update finished
-- 2-3e
-- Run the following code for cleanup
UPDATE Sales.OrderDetails
SET discount = 0.00
WHERE orderid = 10249;
---------------------------------------------------------------------
-- The SERIALIZABLE Isolation Level
---------------------------------------------------------------------
-- 2-4a
-- Run the following code in Connection 1
-- to set the isolation to SERIALIZABLE and query Sales.OrderDetails:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-4b
-- Run the following code in Connection 2 to attempt
-- to insert a row to Sales.OrderDetails with the same
-- order ID that is filtered by the previous query
-- and notice that you are blocked:
INSERT INTO Sales.OrderDetails
(orderid, productid, unitprice, qty, discount)
VALUES(10249, 2, 19.00, 10, 0.00);
-- Note that in lower isolations (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ),
-- this INSERT statement wouldn’t have been blocked.
-- 2-4c
-- Run the following code in Connection 1 to query Sales.OrderDetails
-- again, and commit the transaction:
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
COMMIT TRAN;
-- You get the same result set as you got from the previous query
-- in the same transaction, and since the INSERT statement was blocked,
-- you got no phantom reads.
-- 2-4d
-- Go back to Connection 2 and notice that the INSERT statement finished
-- 2-4e
-- Run the following code for cleanup:
DELETE FROM Sales.OrderDetails
WHERE orderid = 10249
AND productid = 2;
-- 2-4f
-- Run the following code in both Connection 1 and Connection 2
-- to set the isolation level to the default READ COMMITTED:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
---------------------------------------------------------------------
-- The SNAPSHOT Isolation Level
---------------------------------------------------------------------
-- 2-5a
-- Run the following code to allow SNAPSHOT isolation
-- in the TSQLFundamentals2008 database:
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION ON;
-- 2-5b
-- Run the following code in Connection 1 to open a transaction,
-- update rows in Sales.OrderDetails and query it:
BEGIN TRAN;
UPDATE Sales.OrderDetails
SET discount = discount + 0.05
WHERE orderid = 10249;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-5c
-- Run the following code in Connection 2 to set the isolation
-- to SNAPSHOT and query Sales.OrderDetails
-- Notice that you're not blocked, rather you get an earlier
-- consistent version of the data that was available when the
-- transaction started (discount values 0.00):
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-5d
-- Go to Connection 1 and commit the transaction:
COMMIT TRAN;
-- 2-5e
-- Go to Connection 2 and query the data again; notice that
-- you still get discount values 0.00:
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-5f
-- In Connection 2 commit the transaction and query the data again;
-- notice that now you get discount values 0.05:
COMMIT TRAN;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-5g
-- Run the following code for cleanup:
UPDATE Sales.OrderDetails
SET discount = 0.00
WHERE orderid = 10249;
-- Close all connections
---------------------------------------------------------------------
-- The READ COMMITTED SNAPSHOT Isolation Level
---------------------------------------------------------------------
-- 2-6a
-- Turn on READ_COMMITTED_SNAPSHOT in the TSQLFundamentals2008 database:
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT ON;
-- 2-6b
-- Open two new connections (call them Connection 1 and Connection 2)
-- 2-6c
-- Run the following code in Connection 1 to open a transaction
-- and update rows in Sales.OrderDetails:
BEGIN TRAN;
UPDATE Sales.OrderDetails
SET discount = discount + 0.05
WHERE orderid = 10249;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-6d
-- Run the following code in Connection 2 which is now
-- running under the isolation READ COMMITTED SNAPSHOT
-- since the database flag READ_COMMITTED_SNAPSHOT is turned on
-- Notice that you're not blocked, rather you get an earlier
-- consistent version of the data that was available when the
-- statement started (discount values 0.00):
BEGIN TRAN;
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
-- 2-6e
-- Go to Connection 1 and commit the transaction:
COMMIT TRAN;
-- 2-6f
-- Go to Connection 2 and query the data again and commit
-- the transaction; notice that you get the new discount values 0.05:
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
WHERE orderid = 10249;
COMMIT TRAN;
-- 2-6g
-- Run the following code for cleanup:
UPDATE Sales.OrderDetails
SET discount = 0.00
WHERE orderid = 10249;
-- Close all connections
-- 2-6h
-- Change the database flags back to the defaults,
-- disabling snapshot isolations:
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT OFF;
---------------------------------------------------------------------
-- Deadlocks
---------------------------------------------------------------------
-- 3-1
-- Open two new connections (call them Connection 1 and Connection 2)
-- 3-2
-- Run the following code in Connection 1 to open a transaction
-- and update the row for product 2 in Production.Products:
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
-- 3-3
-- Run the following code in Connection 2 to open a transaction
-- and update the row for product 3 in Production.Products:
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 3;
-- 3-4
-- Run the following code in Connection 1 to query product 3;
-- you will be blocked:
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 3;
COMMIT TRAN;
-- 3-5
-- Run the following code in Connection 2 to query product 2;
-- you will be blocked, and a deadlock error will be generated
-- either in Connection 1 or Connection 2:
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
-- 3-6
-- Can you suggest a way to prevent this deadlock?
-- Answer: swap the order in which you access the objects
-- in one of the transactions.
-- 3-7
-- Run the following code for cleanup:
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
UPDATE Production.Products
SET unitprice = 10.00
WHERE productid = 3;
=================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 9 - Transactions
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Transactions
---------------------------------------------------------------------
-- Transaction Example
USE TSQLFundamentals2008;
-- Start a new transaction
BEGIN TRAN;
-- Declare a variable
DECLARE @neworderid AS INT;
-- Insert a new order into the Sales.Orders table
INSERT INTO Sales.Orders
(custid, empid, orderdate, requireddate, shippeddate,
shipperid, freight, shipname, shipaddress, shipcity,
shippostalcode, shipcountry)
VALUES
(85, 5, '20090212', '20090301', '20090216',
3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
N'10345', N'France');
-- Save the new order ID in a variable
SET @neworderid = SCOPE_IDENTITY();
-- Return the new order ID
SELECT @neworderid AS neworderid;
-- Insert order lines for new order into Sales.OrderDetails
INSERT INTO Sales.OrderDetails
(orderid, productid, unitprice, qty, discount)
VALUES(@neworderid, 11, 14.00, 12, 0.000);
INSERT INTO Sales.OrderDetails
(orderid, productid, unitprice, qty, discount)
VALUES(@neworderid, 42, 9.80, 10, 0.000);
INSERT INTO Sales.OrderDetails
(orderid, productid, unitprice, qty, discount)
VALUES(@neworderid, 72, 34.80, 5, 0.000);
-- Commit the transaction
COMMIT TRAN;
-- Cleanup
DELETE FROM Sales.OrderDetails
WHERE orderid > 11077;
DELETE FROM Sales.Orders
WHERE orderid > 11077;
DBCC CHECKIDENT ('Sales.Orders', RESEED, 11077);
---------------------------------------------------------------------
-- Locks and Blocking
---------------------------------------------------------------------
-- Make sure in all new connections
-- that you are connected to TSQLFundamentals2008
USE TSQLFundamentals2008;
-- Connection 1
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
-- Connection 2
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 3
-- Lock info
SELECT -- use * to explore
request_session_id AS spid,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) AS dbname,
resource_description AS res,
resource_associated_entity_id AS resid,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks;
-- Connection info
SELECT -- use * to explore
session_id AS spid,
connect_time,
last_read,
last_write,
most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id IN(52, 53);
-- SQL text
SELECT session_id, text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE session_id IN(52, 53);
-- Session info
SELECT -- use * to explore
session_id AS spid,
login_time,
host_name,
program_name,
login_name,
nt_user_name,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id IN(52, 53);
-- Blocking
SELECT -- use * to explore
session_id AS spid,
blocking_session_id,
command,
sql_handle,
database_id,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
-- Connection 2
-- Stop, then set the LOCK_TIMEOUT, then retry
SET LOCK_TIMEOUT 5000;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Remove timeout
SET LOCK_TIMEOUT -1;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 3
KILL 52;
---------------------------------------------------------------------
-- Isolation Levels
---------------------------------------------------------------------
---------------------------------------------------------------------
-- The READ UNCOMMITTED Isolation Level
---------------------------------------------------------------------
-- Connection 1
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 1
ROLLBACK TRAN;
---------------------------------------------------------------------
-- The READ COMMITTED Isolation Level
---------------------------------------------------------------------
-- Connection 1
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 1
COMMIT TRAN;
-- Cleanup
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
---------------------------------------------------------------------
-- The REPEATABLE READ Isolation Level
---------------------------------------------------------------------
-- Connection 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 2
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
-- Connection 1
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
-- Cleanup
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
---------------------------------------------------------------------
-- The SERIALIZABLE Isolation Level
---------------------------------------------------------------------
-- Connection 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT productid, productname, categoryid, unitprice
FROM Production.Products
WHERE categoryid = 1;
-- Connection 2
INSERT INTO Production.Products
(productname, supplierid, categoryid,
unitprice, discontinued)
VALUES('Product ABCDE', 1, 1, 20.00, 0);
-- Connection 1
SELECT productid, productname, categoryid, unitprice
FROM Production.Products
WHERE categoryid = 1;
COMMIT TRAN;
-- Cleanup
DELETE FROM Production.Products
WHERE productid > 77;
DBCC CHECKIDENT ('Production.Products', RESEED, 77);
-- In all connections issue:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
---------------------------------------------------------------------
-- Snapshot Isolation Levels
---------------------------------------------------------------------
---------------------------------------------------------------------
-- The SNAPSHOT Isolation Level
---------------------------------------------------------------------
-- Allow SNAPSHOT isolation in the database
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Connection 1
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 1
COMMIT TRAN;
-- Connection 2
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
-- Connection 2
BEGIN TRAN
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
-- Cleanup
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
---------------------------------------------------------------------
-- Conflict Detection
---------------------------------------------------------------------
-- Connection 1, Step 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 1, Step 2
UPDATE Production.Products
SET unitprice = 20.00
WHERE productid = 2;
COMMIT TRAN;
-- Cleanup
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
-- Connection 1, Step 1
BEGIN TRAN;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 2, Step 1
UPDATE Production.Products
SET unitprice = 25.00
WHERE productid = 2;
-- Connection 1, Step 2
UPDATE Production.Products
SET unitprice = 20.00
WHERE productid = 2;
-- Cleanup
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
-- Close all connections
---------------------------------------------------------------------
-- The READ COMMITTED SNAPSHOT Isolation Level
---------------------------------------------------------------------
-- Turn on READ_COMMITTED_SNAPSHOT
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT ON;
-- Connection 1
USE TSQLFundamentals2008;
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 2
BEGIN TRAN;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
-- Connection 1
COMMIT TRAN;
-- Connection 2
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
-- Cleanup
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
-- Close all connections
-- Make sure you're back in default mode
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Change database options to default
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT OFF;
---------------------------------------------------------------------
-- Deadlocks
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Simple Deadlock Example
---------------------------------------------------------------------
-- Connection 1
USE TSQLFundamentals2008;
BEGIN TRAN;
UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
-- Connection 2
BEGIN TRAN;
UPDATE Sales.OrderDetails
SET unitprice = unitprice + 1.00
WHERE productid = 2;
-- Connection 1
SELECT orderid, productid, unitprice
FROM Sales.OrderDetails
WHERE productid = 2;
COMMIT TRAN;
-- Connection 2
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
-- Cleanup
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;
UPDATE Sales.OrderDetails
SET unitprice = 19.00
WHERE productid = 2
AND orderid >= 10500;
UPDATE Sales.OrderDetails
SET unitprice = 15.20
WHERE productid = 2
AND orderid < 10500;
=================================
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 10 - Programmable Objects
-- © 2008 Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Variables
---------------------------------------------------------------------
-- Declare a variable and initialize it with a value
DECLARE @i AS INT;
SET @i = 10;
GO
-- Declare and initialize a variable in the same statement
DECLARE @i AS INT = 10;
GO
-- Store the result of a subquery in a variable
USE TSQLFundamentals2008;
DECLARE @empname AS NVARCHAR(61);
SET @empname = (SELECT firstname + N' ' + lastname
FROM HR.Employees
WHERE empid = 3);
SELECT @empname AS empname;
GO
-- Using the SET command to assign one variable at a time
DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);
SET @firstname = (SELECT firstname
FROM HR.Employees
WHERE empid = 3);
SET @lastname = (SELECT lastname
FROM HR.Employees
WHERE empid = 3);
SELECT @firstname AS firstname, @lastname AS lastname;
GO
-- Using the SELECT command to assign multiple variables in the same statement
DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);
SELECT
@firstname = firstname,
@lastname = lastname
FROM HR.Employees
WHERE empid = 3;
SELECT @firstname AS firstname, @lastname AS lastname;
GO
-- SELECT doesn't fail when multiple rows qualify
DECLARE @empname AS NVARCHAR(61);
SELECT @empname = firstname + N' ' + lastname
FROM HR.Employees
WHERE mgrid = 2;
SELECT @empname AS empname;
GO
-- SET fails when multiple rows qualify
DECLARE @empname AS NVARCHAR(61);
SET @empname = (SELECT firstname + N' ' + lastname
FROM HR.Employees
WHERE mgrid = 2);
SELECT @empname AS empname;
GO
---------------------------------------------------------------------
-- Batches
---------------------------------------------------------------------
-- A Batch as a Unit of Parsing
-- Valid batch
PRINT 'First batch';
USE TSQLFundamentals2008;
GO
-- Invalid batch
PRINT 'Second batch';
SELECT custid FROM Sales.Customers;
SELECT orderid FOM Sales.Orders;
GO
-- Valid batch
PRINT 'Third batch';
SELECT empid FROM HR.Employees;
GO
-- Batches and Variables
DECLARE @i AS INT;
SET @i = 10;
-- Succeeds
PRINT @i;
GO
-- Fails
PRINT @i;
GO
-- Statements That Cannot Be Combined in the same Batch
IF OBJECT_ID('Sales.MyView', 'V') IS NOT NULL DROP VIEW Sales.MyView;
CREATE VIEW Sales.MyView
AS
SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY YEAR(orderdate);
GO
-- A Batch as a Unit of Resolution
-- Create T1 with one column
USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT);
GO
-- Following fails
ALTER TABLE dbo.T1 ADD col2 INT;
SELECT col1, col2 FROM dbo.T1;
GO
-- Following succeeds
ALTER TABLE dbo.T1 ADD col2 INT;
GO
SELECT col1, col2 FROM dbo.T1;
GO
-- The GO n Option
-- Create T1 with identity column
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT IDENTITY);
GO
-- Suppress insert messages
SET NOCOUNT ON;
GO
-- Execute batch 100 times
INSERT INTO dbo.T1 DEFAULT VALUES;
GO 100
---------------------------------------------------------------------
-- Flow Elements
---------------------------------------------------------------------
-- The IF ... ELSE Flow Element
IF YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day, 1, CURRENT_TIMESTAMP))
PRINT 'Today is the last day of the year.'
ELSE
PRINT 'Today is not the last day of the year.'
GO
-- IF ELSE IF
IF YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day, 1, CURRENT_TIMESTAMP))
PRINT 'Today is the last day of the year.'
ELSE
IF MONTH(CURRENT_TIMESTAMP) <> MONTH(DATEADD(day, 1, CURRENT_TIMESTAMP))
PRINT 'Today is the last day of the month but not the last day of the year.'
ELSE
PRINT 'Today is not the last day of the month.';
GO
-- Statement Block
IF DAY(CURRENT_TIMESTAMP) = 1
BEGIN
PRINT 'Today is the first day of the month.';
PRINT 'Starting a full database backup.';
BACKUP DATABASE TSQLFundamentals2008
TO DISK = 'C:\Temp\TSQLFundamentals2008_Full.BAK' WITH INIT;
PRINT 'Finished full database backup.';
END
ELSE
BEGIN
PRINT 'Today is not the first day of the month.'
PRINT 'Starting a differential database backup.';
BACKUP DATABASE TSQLFundamentals2008
TO DISK = 'C:\Temp\TSQLFundamentals2008_Diff.BAK' WITH INIT;
PRINT 'Finished differential database backup.';
END
GO
-- The WHILE Flow Element
DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 10
BEGIN
PRINT @i;
SET @i = @i + 1;
END;
GO
-- BREAK
DECLARE @i AS INT;
SET @i = 1
WHILE @i <= 10
BEGIN
IF @i = 6 BREAK;
PRINT @i;
SET @i = @i + 1;
END;
GO
-- CONTINUE
DECLARE @i AS INT;
SET @i = 0
WHILE @i < 10
BEGIN
SET @i = @i + 1;
IF @i = 6 CONTINUE;
PRINT @i;
END;
GO
-- An Example of Using IF and WHILE
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
GO
DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.Nums(n) VALUES(@i);
SET @i = @i + 1;
END
GO
---------------------------------------------------------------------
-- Cursors
---------------------------------------------------------------------
-- Example: Running Aggregations
SET NOCOUNT ON;
USE TSQLFundamentals2008;
DECLARE @Result TABLE
(
custid INT,
ordermonth DATETIME,
qty INT,
runqty INT,
PRIMARY KEY(custid, ordermonth)
);
DECLARE
@custid AS INT,
@prvcustid AS INT,
@ordermonth DATETIME,
@qty AS INT,
@runqty AS INT;
DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR
SELECT custid, ordermonth, qty
FROM Sales.CustOrders
ORDER BY custid, ordermonth;
OPEN C
FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
SELECT @prvcustid = @custid, @runqty = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @custid <> @prvcustid
SELECT @prvcustid = @custid, @runqty = 0;
SET @runqty = @runqty + @qty;
INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty);
FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
END
CLOSE C;
DEALLOCATE C;
SELECT
custid,
CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth,
qty,
runqty
FROM @Result
ORDER BY custid, ordermonth;
GO
---------------------------------------------------------------------
-- Temporary Tables
---------------------------------------------------------------------
-- Local Temporary Tables
USE TSQLFundamentals2008;
IF OBJECT_ID('tempdb.dbo.#MyOrderTotalsByYear') IS NOT NULL
DROP TABLE dbo.#MyOrderTotalsByYear;
GO
SELECT
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty
INTO dbo.#MyOrderTotalsByYear
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM dbo.#MyOrderTotalsByYear AS Cur
LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
GO
-- Try accessing the table from another session
SELECT orderyear, qty FROM dbo.#MyOrderTotalsByYear;
-- Global Temporary Tables
CREATE TABLE dbo.##Globals
(
id sysname NOT NULL PRIMARY KEY,
val SQL_VARIANT NOT NULL
);
-- Run from any session
INSERT INTO dbo.##Globals(id, val) VALUES(N'i', CAST(10 AS INT));
-- Run from any session
SELECT val FROM dbo.##Globals WHERE id = N'i';
-- Run from any session
DROP TABLE dbo.##Globals;
GO
-- Table Variables
DECLARE @MyOrderTotalsByYear TABLE
(
orderyear INT NOT NULL PRIMARY KEY,
qty INT NOT NULL
);
INSERT INTO @MyOrderTotalsByYear(orderyear, qty)
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);
SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM @MyOrderTotalsByYear AS Cur
LEFT OUTER JOIN @MyOrderTotalsByYear AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
GO
-- Table Types
USE TSQLFundamentals2008;
IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL
DROP TYPE dbo.OrderTotalsByYear;
CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
orderyear INT NOT NULL PRIMARY KEY,
qty INT NOT NULL
);
GO
-- Use table type
DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;
INSERT INTO @MyOrderTotalsByYear(orderyear, qty)
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);
SELECT orderyear, qty FROM @MyOrderTotalsByYear;
GO
---------------------------------------------------------------------
-- Dynamic SQL
---------------------------------------------------------------------
-- The EXEC Command
-- Simple example of EXEC
DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);
GO
-- Using EXEC to returned space used of tables in database
USE TSQLFundamentals2008;
DECLARE
@sql AS NVARCHAR(300),
@schemaname AS sysname,
@tablename AS sysname;
DECLARE C CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
OPEN C
FETCH NEXT FROM C INTO @schemaname, @tablename;
WHILE @@fetch_status = 0
BEGIN
SET @sql =
N'EXEC sp_spaceused N'''
+ QUOTENAME(@schemaname) + N'.'
+ QUOTENAME(@tablename) + N''';';
EXEC(@sql);
FETCH NEXT FROM C INTO @schemaname, @tablename;
END
CLOSE C;
DEALLOCATE C;
GO
-- The sp_executesql Stored Procedure
-- Simple example using sp_executesql
DECLARE @sql AS NVARCHAR(100);
SET @sql = N'SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;';
EXEC sp_executesql
@stmt = @sql,
@params = N'@orderid AS INT',
@orderid = 10248;
GO
-- Using sp_executesql to returned count of rows of tables in database
DECLARE @Counts TABLE
(
schemaname sysname NOT NULL,
tablename sysname NOT NULL,
numrows INT NOT NULL,
PRIMARY KEY(schemaname, tablename)
);
DECLARE
@sql AS NVARCHAR(350),
@schemaname AS sysname,
@tablename AS sysname,
@numrows AS INT;
DECLARE C CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES;
OPEN C
FETCH NEXT FROM C INTO @schemaname, @tablename;
WHILE @@fetch_status = 0
BEGIN
SET @sql =
N'SET @n = (SELECT COUNT(*) FROM '
+ QUOTENAME(@schemaname) + N'.'
+ QUOTENAME(@tablename) + N');';
EXEC sp_executesql
@stmt = @sql,
@params = N'@n AS INT OUTPUT',
@n = @numrows OUTPUT;
INSERT INTO @Counts(schemaname, tablename, numrows)
VALUES(@schemaname, @tablename, @numrows);
FETCH NEXT FROM C INTO @schemaname, @tablename;
END
CLOSE C;
DEALLOCATE C;
SELECT schemaname, tablename, numrows
FROM @Counts;
GO
-- Using PIVOT with Dynamic SQL (Advanced, Optional)
-- Static PIVOT
SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
FROM Sales.Orders) AS D
PIVOT(SUM(freight) FOR orderyear IN([2006],[2007],[2008])) AS P;
-- Dynamic PIVOT
DECLARE
@sql AS NVARCHAR(1000),
@orderyear AS INT,
@first AS INT;
DECLARE C CURSOR FAST_FORWARD FOR
SELECT DISTINCT(YEAR(orderdate)) AS orderyear
FROM Sales.Orders
ORDER BY orderyear;
SET @first = 1;
SET @sql = N'SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
FROM Sales.Orders) AS D
PIVOT(SUM(freight) FOR orderyear IN(';
OPEN C
FETCH NEXT FROM C INTO @orderyear;
WHILE @@fetch_status = 0
BEGIN
IF @first = 0
SET @sql = @sql + N','
ELSE
SET @first = 0;
SET @sql = @sql + QUOTENAME(@orderyear);
FETCH NEXT FROM C INTO @orderyear;
END
CLOSE C;
DEALLOCATE C;
SET @sql = @sql + N')) AS P;';
EXEC sp_executesql @stmt = @sql;
GO
---------------------------------------------------------------------
-- Routines
---------------------------------------------------------------------
---------------------------------------------------------------------
-- User Defined Functions
---------------------------------------------------------------------
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.fn_age') IS NOT NULL DROP FUNCTION dbo.fn_age;
GO
CREATE FUNCTION dbo.fn_age
(
@birthdate AS DATETIME,
@eventdate AS DATETIME
)
RETURNS INT
AS
BEGIN
RETURN
DATEDIFF(year, @birthdate, @eventdate)
- CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)
< 100 * MONTH(@birthdate) + DAY(@birthdate)
THEN 1 ELSE 0
END
END
GO
-- Test function
SELECT
empid, firstname, lastname, birthdate,
dbo.fn_age(birthdate, CURRENT_TIMESTAMP) AS age
FROM HR.Employees;
---------------------------------------------------------------------
-- Stored Procedures
---------------------------------------------------------------------
-- Using a Stored Procedure
USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.usp_GetCustomerOrders', 'P') IS NOT NULL
DROP PROC Sales.usp_GetCustomerOrders;
GO
CREATE PROC Sales.usp_GetCustomerOrders
@custid AS INT,
@fromdate AS DATETIME = '19000101',
@todate AS DATETIME = '99991231',
@numrows AS INT OUTPUT
AS
SET NOCOUNT ON;
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = @custid
AND orderdate >= @fromdate
AND orderdate < @todate;
SET @numrows = @@rowcount;
GO
DECLARE @rc AS INT;
EXEC Sales.usp_GetCustomerOrders
@custid = 1, -- Also try with 100
@fromdate = '20070101',
@todate = '20080101',
@numrows = @rc OUTPUT;
SELECT @rc AS numrows;
GO
---------------------------------------------------------------------
-- Triggers
---------------------------------------------------------------------
-- Example for a DML audit trigger
USE tempdb;
IF OBJECT_ID('dbo.T1_Audit', 'U') IS NOT NULL DROP TABLE dbo.T1_Audit;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.T1_Audit
(
audit_lsn INT NOT NULL IDENTITY PRIMARY KEY,
dt DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
login_name sysname NOT NULL DEFAULT(SUSER_SNAME()),
keycol INT NOT NULL,
datacol VARCHAR(10) NOT NULL
);
GO
CREATE TRIGGER trg_T1_insert_audit ON dbo.T1 AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.T1_Audit(keycol, datacol)
SELECT keycol, datacol FROM inserted;
GO
INSERT INTO dbo.T1(keycol, datacol) VALUES(10, 'a');
INSERT INTO dbo.T1(keycol, datacol) VALUES(30, 'x');
INSERT INTO dbo.T1(keycol, datacol) VALUES(20, 'g');
SELECT audit_lsn, dt, login_name, keycol, datacol
FROM dbo.T1_Audit;
GO
-- Example for a DDL audit trigger
USE master;
IF DB_ID('testdb') IS NOT NULL DROP DATABASE testdb;
CREATE DATABASE testdb;
GO
USE testdb;
GO
-- Creation Script for AuditDDLEvents Table and trg_audit_ddl_events Trigger
IF OBJECT_ID('dbo.AuditDDLEvents', 'U') IS NOT NULL
DROP TABLE dbo.AuditDDLEvents;
CREATE TABLE dbo.AuditDDLEvents
(
audit_lsn INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype sysname NOT NULL,
loginname sysname NOT NULL,
schemaname sysname NOT NULL,
objectname sysname NOT NULL,
targetobjectname sysname NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(audit_lsn)
);
GO
CREATE TRIGGER trg_audit_ddl_events
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON;
DECLARE @eventdata AS XML;
SET @eventdata = eventdata();
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'VARCHAR(23)'),
@eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),
@eventdata);
GO
-- Test trigger trg_audit_ddl_events
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
ALTER TABLE dbo.T1 ADD col2 INT NULL;
ALTER TABLE dbo.T1 ALTER COLUMN col2 INT NOT NULL;
CREATE NONCLUSTERED INDEX idx1 ON dbo.T1(col2);
GO
SELECT * FROM dbo.AuditDDLEvents;
GO
-- Cleanup
USE master;
IF DB_ID('testdb') IS NOT NULL DROP DATABASE testdb;
GO
---------------------------------------------------------------------
-- Error Handling
---------------------------------------------------------------------
-- Simple example
BEGIN TRY
PRINT 10/2;
PRINT 'No error';
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH
GO
BEGIN TRY
PRINT 10/0;
PRINT 'No error';
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH
GO
-- Script to create Employees table in tempdb
USE tempdb;
IF OBJECT_ID('dbo.Employees') IS NOT NULL DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT CHK_Employees_empid CHECK(empid > 0),
CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid)
);
GO
-- Detailed Example
BEGIN TRY
INSERT INTO dbo.Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL);
-- Also try with empid = 0, 'A', NULL
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
BEGIN
PRINT 'Handling PK violation...';
END
ELSE IF ERROR_NUMBER() = 547
BEGIN
PRINT 'Handling CHECK/FK constraint violation...';
END
ELSE IF ERROR_NUMBER() = 515
BEGIN
PRINT 'Handling NULL violation...';
END
ELSE IF ERROR_NUMBER() = 245
BEGIN
PRINT 'Handling conversion error...';
END
ELSE
BEGIN
PRINT 'Handling unknown error...';
END
PRINT 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT 'Error Message : ' + ERROR_MESSAGE();
PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10));
PRINT 'Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10));
PRINT 'Error Proc : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc');
END CATCH
GO
-- Encapsulating Reusable Code
IF OBJECT_ID('dbo.usp_err_messages', 'P') IS NOT NULL
DROP PROC dbo.usp_err_messages;
GO
CREATE PROC dbo.usp_err_messages
AS
SET NOCOUNT ON;
IF ERROR_NUMBER() = 2627
BEGIN
PRINT 'Handling PK violation...';
END
ELSE IF ERROR_NUMBER() = 547
BEGIN
PRINT 'Handling CHECK/FK constraint violation...';
END
ELSE IF ERROR_NUMBER() = 515
BEGIN
PRINT 'Handling NULL violation...';
END
ELSE IF ERROR_NUMBER() = 245
BEGIN
PRINT 'Handling conversion error...';
END
ELSE
BEGIN
PRINT 'Handling unknown error...';
END
PRINT 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT 'Error Message : ' + ERROR_MESSAGE();
PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10));
PRINT 'Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10));
PRINT 'Error Proc : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc');
GO
-- Calling Proc in CATCH Block
BEGIN TRY
INSERT INTO dbo.Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL);
END TRY
BEGIN CATCH
EXEC dbo.usp_err_messages;
END CATCH
=====================================
No comments:
Post a Comment