USE WideWorldImporters;
/* Query data by using subqueries and APPLY */
-- 001
-- simple subquery
SELECT o.*
FROM (SELECT * FROM Sales.Orders) o
-- 002
SELECT *
FROM [Application].[People]
WHERE PreferredName IN ( SELECT CustomerName
FROM sales.Customers )
-- 003
USE TestDB;
SELECT *
,(SELECT AVG(salary) FROM dbo.Employees) AS average_salary
FROM dbo.Employees
-- 004
SELECT *
FROM dbo.Employees
WHERE Salary > (SELECT AVG(salary) FROM dbo.Employees)
-- 005
-- correlated subquery
SELECT *
FROM dbo.Employees e1
WHERE Salary > ( SELECT AVG(salary)
FROM Employees e2
WHERE e1.department = e2.department)
-- 006
SELECT *
FROM dbo.Employees e1
INNER JOIN (SELECT AVG(salary) AS avg_salary, department
FROM dbo.employees
GROUP BY department) e2 ON e1.Department = e2.Department
WHERE salary > e2.avg_salary
-- 007
SELECT *
, (SELECT AVG(salary) FROM Employees e2 WHERE e1.department = e2.Department)
FROM Employees e1
WHERE Salary > ( SELECT AVG(salary)
FROM Employees e2
WHERE e1.Department = e2.department)
-- 008
SELECT *
FROM Departments d
WHERE ( SELECT count (*)
FROM Employees e
WHERE e.Department = d.Department) > 2
-- 009
ALTER TABLE Departments ADD Location varchar(100) DEFAULT 'Houston' WITH VALUES;
GO
UPDATE Departments
SET Location = 'New York'
WHERE Department = 'Management'
/* Evaluate performance differences between table joins and correlated subqueries based on provided data and query plans */
-- 010
SELECT *
INTO Customers_copy
FROM WideWorldImporters.sales.Customers
SELECT *
INTO orders_copy
FROM WideWorldImporters.sales.orders
-- 011
-- join faster
SELECT c.CustomerName, o.lastorderdate, o.NumberOfOrders
FROM (SELECT customerID, max(orderdate) AS LastOrderDate, count(*) AS NumberOfOrders
FROM orders_copy
GROUP BY CustomerID) o
INNER JOIN Customers_copy c on c.CustomerID = o.customerId
SELECT CustomerName
,( SELECT MAX(orderdate)
FROM orders_copy o
WHERE c.customerId = o.customerID ) AS LastOrderDate
,( SELECT COUNT(*)
FROM orders_copy o
WHERE c.customerId = o.customerID )AS NumberOfOrders
FROM Customers_copy c
-- 012
-- correlated subquery faster
SELECT *
FROM Customers_copy c
LEFT OUTER JOIN orders_copy o on c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL
SELECT *
FROM Customers_copy c
WHERE NOT EXISTS ( SELECT *
FROM orders_copy o
WHERE c.CustomerID = o.CustomerID)
/* Distinguish between the use of CROSS APPLY and OUTER APPLY */
-- 013
SELECT *
FROM Departments d
CROSS APPLY
( SELECT *
FROM Employees e
WHERE e.Department = d.Department) table_alias
SELECT *
FROM Departments d
INNER JOIN Employees e ON d.Department = e.Department
-- 014
INSERT Departments VALUES ('Research', 'Eindhoven')
SELECT *
FROM Departments d
OUTER APPLY
( SELECT *
FROM Employees e
WHERE e.Department = d.Department) table_alias
SELECT *
FROM Departments d
LEFT OUTER JOIN Employees e ON d.Department = e.Department
-- 015
CREATE FUNCTION dbo.fn_GetListOfEmployees(@Department AS varchar(100))
RETURNS TABLE
AS
RETURN
(
SELECT FirstName + ' ' + LastName AS FullName, Salary
FROM Employees e
WHERE e.Department = @Department
)
-- 016
SELECT *
FROM dbo.fn_GetListOfEmployees('Engineering')
-- 017
SELECT *
FROM Departments d
CROSS APPLY dbo.fn_GetListOfEmployees(d.Department)
-- 018
SELECT session_id, DB_NAME(database_id) AS [database], start_time
, open_transaction_count, [text] AS [Query]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) s
WHERE session_id > 50 -- Exclude system spids
ORDER BY session_id;
/* Query data by using table expressions */
--Identify basic components of table expressions
SELECT o.*
FROM (SELECT * FROM Sales.Orders) o
-- common table expression
WITH o
AS ( SELECT *
FROM Sales.Orders)
SELECT *
FROM o
SELECT *
FROM dbo.Employees e1
WHERE Salary > ( SELECT AVG(salary)
FROM Employees e2
WHERE e1.Department = e2.department);
-- readability example
SELECT c.CustomerName, o.lastorderdate, o.NumberOfOrders
FROM (SELECT customerID, max(orderdate) AS LastOrderDate, count(*) AS NumberOfOrders
FROM orders_copy
GROUP BY CustomerID) o
INNER JOIN Customers_copy c on c.CustomerID = o.customerId;
WITH o AS (
SELECT customerID, max(orderdate) AS LastOrderDate, count(*) AS NumberOfOrders
FROM orders_copy
GROUP BY CustomerID)
SELECT c.CustomerName, o.lastorderdate, o.NumberOfOrders
FROM o
INNER JOIN Customers_copy c on c.CustomerID = o.customerId
--Construct recursive table expressions to meet business requirements
-- Recursive cte, top to bottom:
ALTER TABLE Employees ADD ManagerID tinyint NULL;
GO
INSERT Employees (FirstName,LastName, Salary, Department, ManagerID)
VALUES ('Harriet', 'Hughes', 1800.00,'Sales',7);
INSERT Employees (FirstName,LastName, Salary, Department, ManagerID)
VALUES ('Ronnie', 'Robertson', 1800.00,'Engineering',7);
UPDATE Employees
SET ManagerID = (SELECT EmployeeID FROM Employees WHERE FirstName = 'Harriet')
WHERE Department = 'Sales' AND FirstName <> 'Harriet'
UPDATE Employees
SET ManagerID = (SELECT EmployeeID FROM Employees WHERE FirstName = 'Ronnie')
WHERE Department = 'Engineering' AND FirstName <> 'Ronnie'
ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID)
ALTER TABLE Employees ADD CONSTRAINT FK_EmployeeID FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
GO
WITH Employee_CTE AS (
SELECT EmployeeID, ManagerID, Firstname + ' ' + LastName AS Name, 0 as Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e1.EmployeeID, e1.ManagerID, Firstname + ' ' + LastName, e2.level + 1
FROM Employees e1
INNER JOIN Employee_CTE e2 ON e2.EmployeeID = e1.ManagerID
)
SELECT Name, Level
FROM Employee_CTE
ORDER BY Level, Name
-- Recursive cte, bottom to top:
WITH Employee_CTE AS (
SELECT EmployeeID, ManagerID, Firstname + ' ' + LastName AS Name, 0 as Level
FROM Employees
WHERE EmployeeID NOT IN (SELECT ManagerID FROM Employees WHERE ManagerID IS NOT NULL)
UNION ALL
SELECT e1.EmployeeID, e1.ManagerID, Firstname + ' ' + LastName, e2.level + 1
FROM Employees e1
INNER JOIN Employee_CTE e2 ON e1.EmployeeID = e2.ManagerID
)
SELECT DISTINCT Name, Level
FROM Employee_CTE
ORDER BY Level, Name
-- Recursive CTE, final example
WITH Employee_CTE AS (
SELECT EmployeeID, ManagerID, 0 as Level
FROM Employees
WHERE EmployeeID NOT IN (SELECT ManagerID FROM Employees WHERE ManagerID IS NOT NULL)
UNION ALL
SELECT e1.EmployeeID, e1.ManagerID, e2.level + 1
FROM Employees e1
INNER JOIN Employee_CTE e2 ON e1.EmployeeID = e2.ManagerID
)
,
Distinct_CTE AS (
SELECT DISTINCT EmployeeID, ManagerID, Level
FROM Employee_CTE)
SELECT e2.FirstName + ' ' + e2.LastName AS Name, e3.FirstName + ' ' + e3.LastName AS Manager, Level
FROM Distinct_CTE e1
INNER JOIN Employees e2 ON e1.employeeID = e2.EmployeeID
LEFT OUTER JOIN Employees e3 ON e1.ManagerID = e3.EmployeeID
ORDER BY Level, Name
--multiple CTEs
WITH Employee_CTE AS (
SELECT EmployeeID, ManagerID, 0 as Level
FROM Employees
WHERE EmployeeID NOT IN ( SELECT ManagerID
FROM Employees
WHERE ManagerID IS NOT NULL)
UNION ALL
SELECT e1.EmployeeID, e1.ManagerID, e2.level + 1
FROM Employees e1
INNER JOIN Employee_CTE e2 ON e1.EmployeeID = e2.ManagerID
)
,
Distinct_CTE AS (
SELECT DISTINCT EmployeeID, ManagerID, Level
FROM Employee_CTE)
SELECT e2.FirstName + ' ' + e2.LastName AS Name
, e3.FirstName + ' ' + e3.LastName AS Manager, Level
FROM Distinct_CTE e1
INNER JOIN Employees e2 ON e1.employeeID = e2.EmployeeID
LEFT OUTER JOIN Employees e3 ON e1.ManagerID = e3.EmployeeID
ORDER BY Level, Name
--Temporary table
SELECT *
INTO #tmp_Employees
FROM Employees
SELECT *
FROM #tmp_Employees
ALTER TABLE #tmp_Employees ADD Description varchar(100) NULL
DROP TABLE #tmp_Employees
SELECT *
FROM tempdb.sys.objects
WHERE name LIKE '#tmp_Employees%'
--Table variables
DECLARE @Employees TABLE (
[EmployeeID] [tinyint] NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](50) NULL,
[Address] [varchar](100) NULL,
[Salary] [decimal](18, 2) NULL,
[Department] [varchar](100) NULL,
[Employee_Number] [int] NULL,
[ManagerID] [tinyint] NULL)
INSERT INTO @Employees
SELECT *
FROM Employees
--Group and pivot data by using queries
--Construct complex GROUP BY clauses using GROUPING SETS, and CUBE
DROP TABLE IF EXISTS UFO_Sightings;
GO
CREATE TABLE UFO_Sightings (
CountryID tinyint
, Country varchar(50)
, State varchar(50)
, City varchar(50)
, UFO_Sightings int );
INSERT INTO UFO_Sightings VALUES (1, 'Germany', NULL, 'Berlin', 1)
INSERT INTO UFO_Sightings VALUES (1, 'Germany', NULL, 'Frankfurt', 2)
INSERT INTO UFO_Sightings VALUES (1, 'Germany', NULL, 'Frankfurt', 3)
INSERT INTO UFO_Sightings VALUES (2, 'United States', 'Texas', 'Houston', 1)
INSERT INTO UFO_Sightings VALUES (2, 'United States', 'Texas', 'Paris', 2)
INSERT INTO UFO_Sightings VALUES (2, 'United States', 'Nevada', NULL, 300)
INSERT INTO UFO_Sightings VALUES (3, 'France', NULL, 'Paris', 1);
SELECT *
FROM UFO_Sightings
SELECT Country, SUM(UFO_Sightings)
FROM UFO_Sightings
GROUP BY Country
SELECT SUM(UFO_Sightings)
FROM UFO_Sightings
SELECT SUM(UFO_Sightings)
FROM UFO_Sightings
GROUP BY ()
SELECT Country, City, SUM(UFO_Sightings)
FROM UFO_Sightings
GROUP BY Country, City
ORDER BY Country, City;
SELECT Country, SUM(UFO_Sightings)
FROM UFO_Sightings
WHERE City <> 'Paris'
GROUP BY Country
SELECT Country, SUM(UFO_Sightings)
FROM UFO_Sightings
WHERE ISNULL(City,'') <> 'Paris'
GROUP BY Country
SELECT CountryID, Country, SUM(UFO_Sightings)
FROM UFO_Sightings
GROUP BY Country
SELECT Country, SUM(UFO_Sightings) AS TotalNumberOfSightings
FROM UFO_Sightings
GROUP BY Country
HAVING SUM(UFO_Sightings) > 100;
WITH ufo AS (
SELECT Country, SUM(UFO_Sightings) AS TotalNumberOfSightings
FROM UFO_Sightings
GROUP BY Country)
SELECT *
FROM ufo
WHERE TotalNumberOfSightings > 100
INSERT INTO UFO_Sightings VALUES (4, 'Belgium', NULL, 'Brussels', NULL);
SELECT Country, COUNT(UFO_Sightings) AS Count_1, COUNT(*) AS Count_2
FROM UFO_Sightings
GROUP BY Country
SELECT Country, State, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY Country, State
--complex GROUP BY logic
SELECT Country, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY Country, City
ORDER BY Country, City
SELECT Country, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY ROLLUP (Country, City);
SELECT Country, NULL AS City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY Country
UNION ALL
SELECT Country, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY Country, City
UNION ALL
SELECT NULL, NULL, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY ()
GROUP BY Country, City WITH ROLLUP;
SELECT Country, GROUPING(Country) AS Is_result_of_grouping_countries
, State, GROUPING(State) AS Is_result_of_grouping_cities
FROM UFO_Sightings
GROUP BY Country, State WITH ROLLUP
SELECT CASE
WHEN GROUPING(Country) = 1 THEN 'Grand Total'
WHEN GROUPING(State) = 1 THEN 'Total for ' + Country
ELSE ''
END AS Totals
, Country
, State
,SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY Country, State WITH ROLLUP
SELECT Country, State, GROUPING_ID(Country, State )
FROM UFO_Sightings
GROUP BY Country, State WITH ROLLUP
ALTER TABLE UFO_Sightings ADD Continent varchar(100) NULL
GO --required batch separator, otherwise query will fail on missing column
UPDATE UFO_Sightings
SET Continent = 'Europe'
WHERE Country IN ('Germany', 'France', 'Belgium')
UPDATE UFO_Sightings
SET Continent = 'North America'
WHERE Country IN ('United States')
SELECT Continent
, Country
, State
, GROUPING_ID(Continent, Country, State )
, GROUPING(Continent) * 4 + GROUPING(Country) * 2 + GROUPING(State)
FROM UFO_Sightings
GROUP BY Continent, Country, State WITH ROLLUP
SELECT Country, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY CUBE (Country, City)
ORDER BY 1, 2;
-- CUBE dissected: A & NULL, NULL & B, A & B, NULL & NULL
SELECT Country, NULL AS City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY (Country)
UNION ALL
SELECT NULL, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY ( City)
UNION ALL
SELECT Country, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY Country, City
UNION ALL
SELECT NULL, NULL, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
ORDER BY 1, 2
SELECT Continent
, Country
, City
, SUM(UFO_Sightings) AS TotalUFO_Sightings
, GROUPING_ID(Continent,Country, City)
FROM UFO_Sightings
GROUP BY CUBE (Continent, Country, City)
ORDER BY 5;
SELECT Country, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY GROUPING SETS ( Country, City)
SELECT Country, NULL, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY Country
UNION ALL
SELECT NULL, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY City
SELECT Country, City, SUM(UFO_Sightings) AS TotalUFO_Sightings
FROM UFO_Sightings
GROUP BY GROUPING SETS ( Country, City, ())
--Use windowing functions to group and rank the results of a query
DROP TABLE IF EXISTS Runner_info
CREATE TABLE Runner_info (
Runner varchar(50)
,Nickname varchar(50)
,Race varchar(50)
,Race_date date
,Distance decimal(8,3)
,Finish_time time)
GO
INSERT Runner_info VALUES ('Jack', 'The Flash', 'New York marathon', '2016-11-06', 42.195, '2:53:00')
INSERT Runner_info VALUES ('Jack', 'The Flash', 'New York marathon', '2017-11-05', 42.195, '2:55:00')
INSERT Runner_info VALUES ('Jack', 'The Flash', 'Berlin marathon', '2016-09-25', 42.195, '2:45:00')
INSERT Runner_info VALUES ('Juan Gonzalez', 'Speedy', 'New York marathon', '2016-11-06', 42.195, '3:22:00')
INSERT Runner_info VALUES ('Juan Gonzalez', 'Speedy', 'New York marathon', '2017-11-05', 42.195, '3:18:30')
INSERT Runner_info VALUES ('Rudy', 'Roadrunner', 'New York marathon', '2016-11-06', 42.195, '4:01:00')
INSERT Runner_info VALUES ('Rudy', 'Roadrunner', 'New York marathon', '2017-11-05', 42.195, '4:05:30')
INSERT Runner_info VALUES ('Rudy', 'Roadrunner', 'Berlin marathon', '2016-09-25', 42.195, '3:59:59')
INSERT Runner_info VALUES ('Rudy', 'Roadrunner', 'Berlin marathon', '2017-09-24', 42.195, '4:13:00')
INSERT Runner_info VALUES ('Rudy', 'Roadrunner', 'Rotterdam half marathon', '2017-04-07', 21.1, '1:52')
SELECT *
FROM Runner_info
SELECT *
,COUNT(*) OVER () AS Nr_of_races_total
FROM Runner_info
SELECT *
,COUNT(*) OVER () AS Nr_of_races_total
,COUNT(*) OVER (PARTITION BY Runner) AS Nr_of_races_per_runner
FROM Runner_info
SELECT *
,COUNT(*) OVER (PARTITION BY Runner) AS Nr_of_races_per_runner
FROM Runner_info
WHERE Distance = 42.195
SELECT Runner
,Race
,Race_date
,Finish_time
,MIN(Finish_time) OVER (PARTITION BY Runner, Race) AS Race_PR
,DATEDIFF(mi,MIN(Finish_time) OVER (PARTITION BY Runner, Race), Finish_time) AS Slower_than_PR
FROM Runner_info
OVER ( PARTITION BY column
ORDER BY column
ROWS BETWEEN … AND …)
SELECT Runner, Race, Race_date, distance
,SUM(distance) OVER (PARTITION BY Runner
ORDER BY Race_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS Cumulative_distance
FROM Runner_info
WHERE Runner = 'Rudy'
ORDER BY Runner, Race_date
SELECT Runner, Race, Race_date, distance
,AVG(distance) OVER (PARTITION BY Runner
ORDER BY Race_date
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
AS Running_average
FROM Runner_info
WHERE Runner = 'Rudy'
ORDER BY Runner, Race_date
INSERT Runner_info VALUES ('Rudy', 'Roadrunner', 'Ran home', '2017-11-05', 10, '1:10')
SELECT Runner, Race, Race_date, distance
,SUM(distance) OVER (PARTITION BY Runner
ORDER BY Race_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS Cumulative_distance
FROM Runner_info
WHERE Runner = 'Rudy'
ORDER BY Runner, Race_date
SELECT Runner, Race, Race_date, distance
,SUM(distance) OVER (PARTITION BY Runner
ORDER BY Race_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS Cumulative_distance
FROM Runner_info
WHERE Runner = 'Rudy'
ORDER BY Runner, Race_date
--Ranking window functions
SELECT Runner, Race, Race_date, Finish_time
,ROW_NUMBER() OVER (ORDER BY Finish_time) AS rownumber
,RANK() OVER (ORDER BY Finish_time) AS rank
,DENSE_RANK() OVER (ORDER BY Finish_time) AS denserank
,NTILE(5) OVER (ORDER BY Finish_time) AS ntile
FROM Runner_info
WHERE Distance = 42.195
ORDER BY rownumber
INSERT Runner_info VALUES ('Fred', 'The Shadow', 'New York marathon', '2016-11-06', 42.195, '2:53:00')
--Analytic window functions
SELECT FirstName, LastName, Department, Salary
FROM dbo.Employees
SELECT FirstName, LastName, Department, Salary
,LAG(Salary) OVER (ORDER BY Salary) as lag
,LEAD(Salary) OVER (ORDER BY Salary) as lead
FROM Employees
SELECT FirstName, LastName, Department, Salary
,LAG(Salary) OVER (ORDER BY Salary) as lag
,LEAD(Salary) OVER (ORDER BY Salary) as lead
FROM Employees
ORDER BY LastName
SELECT FirstName, LastName, Department, Salary
,LAG(Salary, 2) OVER (ORDER BY Salary) as lag
,LEAD(Salary, 2) OVER (ORDER BY Salary) as lead
FROM Employees
SELECT FirstName, LastName, Department, Salary
,LAG(Salary, 2, 'not applicable') OVER (ORDER BY Salary) as lag
,LEAD(Salary, 2, 'not applicable') OVER (ORDER BY Salary) as lead
FROM Employees
SELECT FirstName, LastName, Department, Salary
,LAG(Salary) OVER (PARTITION BY Department ORDER BY Salary) as lag
,LEAD(Salary) OVER (PARTITION BY Department ORDER BY Salary) as lead
FROM Employees
ORDER BY Department
SELECT FirstName, LastName, Department, Salary
,FIRST_VALUE(FirstName + ' ' + LastName)
OVER ( PARTITION BY Department
ORDER BY Salary)
as Least_earning_employee
,LAST_VALUE(FirstName + ' ' + LastName)
OVER ( PARTITION BY Department
ORDER BY Salary
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
as Most_earning_employee
FROM Employees
ORDER BY Department, Salary
SELECT FirstName, LastName, Department, Salary
,CUME_DIST() OVER (ORDER BY Salary) as cumulative_distance
,CUME_DIST() OVER (ORDER BY Salary) * COUNT(*) OVER () as equal_or_less
FROM Employees
ORDER BY Salary
INSERT dbo.Employees (FirstName, LastName, Salary, Department)
VALUES ('James', 'Peterson', 900, 'Engineering')
SELECT FirstName, LastName, Department, Salary
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER () as median_salary
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER () as median_salary
FROM Employees
ORDER BY Salary
SELECT FirstName, LastName, Department, Salary
,PERCENT_RANK() OVER (ORDER BY Salary) as fraction_lower_than_this
,CAST(PERCENT_RANK() OVER (ORDER BY Salary) * 100 AS dec(5,1))
as percentage_lower_than_this
FROM Employees
ORDER BY Salary
--Construct PIVOT and UNPIVOT statements to return desired results based on supplied data
SELECT CustomerName, si.StockItemName, sum(quantity) as quantity
FROM sales.Customers c
INNER JOIN sales.Orders o on c.CustomerID = o.CustomerID
INNER JOIN sales.OrderLines ol on o.OrderID = ol.OrderID
INNER JOIN Warehouse.StockItems si on ol.StockItemID = si.StockItemID
WHERE c.CustomerID IN ( SELECT TOP 5 CustomerID
FROM Sales.Orders
GROUP BY CustomerID
ORDER BY COUNT(*))
AND si.StockItemID IN ( SELECT TOP 5 StockItemID
FROM Sales.Orderlines
GROUP BY StockItemID
ORDER BY COUNT(*))
GROUP BY CustomerName, si.StockItemName
ORDER BY CustomerName, si.StockItemName
--PIVOT and UNPIVOT
WITH cte AS(
SELECT CustomerName, si.StockItemName, quantity
FROM sales.Customers c
INNER JOIN sales.Orders o on c.CustomerID = o.CustomerID
INNER JOIN sales.OrderLines ol on o.OrderID = ol.OrderID
INNER JOIN Warehouse.StockItems si on ol.StockItemID = si.StockItemID
WHERE si.StockItemID IN ( SELECT TOP 5 StockItemID
FROM Sales.Orderlines
GROUP BY StockItemID
ORDER BY COUNT(*))
)
SELECT *
FROM cte
PIVOT (SUM(quantity)
FOR CustomerName IN ([Anand Mudaliyar],[Jibek Juniskyzy]
,[Agrita Abele],[Kalyani Benjaree],[Jaroslav Fisar]))
AS SalesQuantity
USE TestDB
GO
DROP TABLE IF EXISTS Sales;
GO
CREATE TABLE Sales(
Customer varchar(50)
, Product varchar(50)
, Quantity int)
INSERT INTO Sales(Customer, Product, Quantity)
VALUES('John','Apples',4)
,('John','Bananas',7)
,('John','Cantaloupe',1)
,('John','Apples',5)
,('John','Beer',6)
,('Bill','Cantaloupe',3)
,('Bill','Beer',24)
,('Diana','Apples',6)
,('Diana','Bananas',5)
,('Diana','Cantaloupe',2)
SELECT *
FROM Sales
-- Pivot customers
SELECT Product, Bill, John, Diana
FROM Sales
PIVOT (SUM(Quantity)
FOR Customer IN (Bill, John, Diana)) AS some_alias
ORDER BY Product
-- Pivot product
SELECT Customer, Apples, Bananas, Cantaloupe , Beer, [Magic mushrooms]
FROM Sales
PIVOT (SUM(Quantity)
FOR Product IN (Apples, Bananas, Cantaloupe , Beer, [Magic mushrooms])) AS some_alias
ORDER BY Customer
-- adding a column
ALTER TABLE Sales ADD CustomerID int NULL
GO
UPDATE Sales SET CustomerID = 1 WHERE Customer = 'Bill'
UPDATE Sales SET CustomerID = 2 WHERE Customer = 'John'
UPDATE Sales SET CustomerID = 3 WHERE Customer = 'Diana'
SELECT Product, Bill, John, Diana
FROM Sales
PIVOT (SUM(Quantity)
FOR Customer IN (Bill, John, Diana)) AS some_alias
ORDER BY Product
ALTER TABLE Sales DROP CustomerID
SELECT Customer, Apples, Bananas, Cantaloupe , Beer, [Magic mushrooms]
INTO Pivoted_sales_table
FROM Sales
PIVOT (SUM(Quantity)
FOR Product IN (Apples, Bananas, Cantaloupe , Beer, [Magic mushrooms])) AS some_alias
ORDER BY Customer
SELECT *
FROM Pivoted_sales_table
SELECT Customer, Product, Quantity
FROM Pivoted_sales_table
UNPIVOT( Quantity FOR
Product IN ( Apples, Bananas, Cantaloupe
, Beer, [Magic mushrooms])
) AS some_alias
--Determine the impact of NULL values in PIVOT and UNPIVOT queries
--Count(*) not allowed
SELECT Product, Bill, John, Diana
FROM Sales
PIVOT (COUNT(Quantity)
FOR Customer IN (Bill, John, Diana)) AS some_alias
ORDER BY Product
SELECT Customer, Apples, Bananas, Cantaloupe , Beer, [Magic mushrooms]
FROM Sales
PIVOT (MIN(Quantity), MAX(Quantity)
FOR Product IN (Apples, Bananas, Cantaloupe , Beer, [Magic mushrooms])) AS some_alias
ORDER BY Customer
USE TestDB
GO
DROP TABLE IF EXISTS Sales;
DROP TABLE IF EXISTS Pivoted_sales_table;
--Query temporal data and non-relational data
--Query historic data by using temporal tables
USE WideWorldImporters
GO
SELECT CountryName, LatestRecordedPopulation
FROM Application.Countries
SELECT CountryName, LatestRecordedPopulation
FROM Application.Countries
FOR SYSTEM_TIME AS OF '2013-01-01 00:00:00.0000000'
WHERE CountryName = 'Australia'
SELECT CountryName, LatestRecordedPopulation, ValidFrom, ValidTo
FROM Application.Countries
FOR SYSTEM_TIME ALL
WHERE CountryName = 'Australia'
SELECT CountryName, LatestRecordedPopulation, ValidFrom, ValidTo
FROM Application.Countries
FOR SYSTEM_TIME FROM '2013-07-01 16:00:00.0000000'
TO '2015-07-01 16:00:00.0000000'
WHERE CountryName = 'Australia'
SELECT CountryName, LatestRecordedPopulation, ValidFrom, ValidTo
FROM Application.Countries
FOR SYSTEM_TIME BETWEEN '2013-07-01 16:00:00.0000000'
AND '2015-07-01 16:00:00.0000000'
WHERE CountryName = 'Australia'
DECLARE @now datetime2 = SYSDATETIME()
SELECT CountryName, LatestRecordedPopulation, ValidFrom, ValidTo
FROM Application.Countries
FOR SYSTEM_TIME CONTAINED IN ('2013-07-01 15:00:00.0000000', @now)
WHERE CountryName = 'Australia'
SELECT CountryName, LatestRecordedPopulation, ValidFrom, ValidTo
FROM Application.Countries_archive
WHERE CountryName = 'Australia'
UNION ALL
SELECT CountryName, LatestRecordedPopulation, ValidFrom, ValidTo
FROM Application.Countries
WHERE CountryName = 'Australia'
USE TestDB
GO
IF EXISTS ( SELECT *
FROM sys.tables
WHERE name = 'Countries'
AND temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE')
BEGIN
ALTER TABLE Countries SET ( SYSTEM_VERSIONING = OFF)
END
GO
DROP TABLE IF EXISTS Countries_archive
DROP TABLE IF EXISTS Countries
GO
CREATE TABLE Countries (
CountryID int IDENTITY PRIMARY KEY
,CountryName varchar(50)
,Population int
,ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN
,ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN
,PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo))
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Countries_archive))
GO
INSERT Countries (CountryName, Population) VALUES ('Neverland', 100)
UPDATE Countries SET Population = 101 WHERE CountryName = 'Neverland'
SELECT *, ValidFrom, ValidTo
FROM Countries
FOR SYSTEM_TIME ALL
ORDER BY ValidFrom
BEGIN TRAN
UPDATE Countries SET Population = 102 WHERE CountryName = 'Neverland'
WAITFOR DELAY '00:00:10'
UPDATE Countries SET Population = 103 WHERE CountryName = 'Neverland'
COMMIT TRAN
SELECT *, ValidFrom, ValidTo
FROM Countries
FOR SYSTEM_TIME ALL
ORDER BY ValidFrom
SELECT *
FROM countries_archive
DECLARE @local_time datetime2
,@utc_time datetime2
SET @local_time = '2018-07-04 13:40:00'
SET @utc_time = DATEADD(second, DATEDIFF(second, SYSDATETIME(), SYSUTCDATETIME()), @local_time)
SELECT *
FROM Countries
FOR SYSTEM_TIME AS OF @utc_time
DELETE FROM Countries_archive WHERE ValidTo <DATEADD(yy, -7, SYSUTCDATETIME())
ALTER TABLE Countries ADD Description varchar(100) NULL
GO
UPDATE Countries SET Description = 'Not an actual country' WHERE CountryName = 'Neverland'
GO
ALTER TABLE Countries DROP COLUMN Description
USE TestDB
GO
IF EXISTS ( SELECT *
FROM sys.tables
WHERE name = 'Countries'
AND temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE')
BEGIN
ALTER TABLE Countries SET ( SYSTEM_VERSIONING = OFF)
END
GO
DROP TABLE IF EXISTS Countries_archive
DROP TABLE IF EXISTS Countries
GO
CREATE TABLE Countries (
CountryID int IDENTITY PRIMARY KEY NOT NULL
,CountryName varchar(50)
,Population int)
GO
INSERT Countries (CountryName, Population) VALUES ('Neverland', 103)
CREATE TABLE Countries_archive (
CountryID int NOT NULL --no identity or primary key
,CountryName varchar(50)
,Population int)
GO
ALTER TABLE Countries_archive ADD ValidFrom datetime2 NOT NULL
ALTER TABLE Countries_archive ADD ValidTo datetime2 NOT NULL
GO
INSERT Countries_archive (CountryID, CountryName, Population, ValidFrom,ValidTo)
VALUES (1, 'Neverland', 100, '2011-01-01', '2012-01-01')
ALTER TABLE Countries ADD ValidFrom datetime2 NULL
ALTER TABLE Countries ADD ValidTo datetime2 NULL
GO
UPDATE Countries SET ValidFrom = '2012-01-01'
UPDATE Countries SET ValidTo = '9999-12-31 23:59:59.9999999'
GO
ALTER TABLE Countries ALTER COLUMN ValidFrom datetime2 NOT NULL
ALTER TABLE Countries ALTER COLUMN ValidTo datetime2 NOT NULL
GO
ALTER TABLE Countries ADD PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo)
ALTER TABLE Countries SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Countries_archive))
SELECT [2012].population - [2011].population AS 'population growth'
FROM Countries FOR SYSTEM_TIME AS OF '2011-01-01' AS [2011]
INNER JOIN Countries FOR SYSTEM_TIME AS OF '2012-01-01' AS [2012]
ON [2011].CountryID = [2012].CountryID
--Query and output JSON data
CREATE TABLE [dbo].[Employees](
[EmployeeID] [tinyint] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](50) NULL,
[Address] [varchar](100) NULL,
[Employee_Number] [int] NULL
)
INSERT dbo.Employees (FirstName, LastName, Address)
VALUES ('Bob', 'Jackson', 'Under the bridge')
,('Bo', 'Didley', 'Home of mr. Bo Didley')
DECLARE @json NVARCHAR(MAX)
SET @json = '{"FirstName":"Bob"}'
SELECT ISJSON(@json) AS 'IsValidJSON?'
SELECT FirstName, lastname, address
FROM employees
WHERE EmployeeID = 1
FOR JSON AUTO
--Format query output as JSON
SELECT FirstName, lastname, address
FROM employees
WHERE EmployeeID in (1,2)
FOR JSON AUTO
SELECT FirstName, lastname, address
FROM employees
WHERE EmployeeID in (1,2)
FOR JSON PATH
SELECT FirstName, lastname, address
FROM employees
WHERE EmployeeID in (1,2)
FOR JSON PATH, ROOT ('Employees')
SELECT FirstName, lastname, address
FROM employees
WHERE EmployeeID in (1,2)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
UPDATE Employees
SET Employee_Number = employeeID
WHERE EmployeeID = 1
SELECT FirstName, lastname, address, Employee_Number
FROM employees
WHERE EmployeeID in (1,2)
FOR JSON PATH, INCLUDE_NULL_VALUES
SELECT FirstName AS 'Name.FirstName'
, lastname AS 'Name.LastName'
, address
FROM employees
WHERE EmployeeID in (1,2)
FOR JSON PATH
--Extract data from JSON
DECLARE @json NVARCHAR(MAX)
SET @json = '{
"A null value":null
,"A text string":"string"
,"A number":2
,"A boolean (true or false)":true
,"A boolean (true or false)":false
,"An array":[1,2,3]
,"An object": {"key1":"value1", "key2":"value2"}
}'
SELECT *
FROM OPENJSON(@json)
SELECT [key], value
FROM OPENJSON(@json)
WHERE type <> 0
SELECT [key], value
FROM OPENJSON(@json,'$."An object"')
SELECT [key], value
FROM OPENJSON(@json,'$."An array"')
SELECT [key], value
FROM OPENJSON(@json,'lax $."n array"')
SELECT [key], value
FROM OPENJSON(@json,'strict $."n array"')
SELECT *
FROM OPENJSON(@json)
WITH (
NameOfTheColumn bigint '$."A number"'
,NameOfTheOtherColumn varchar(100) '$."A text string"'
)
SELECT *
FROM OPENJSON(@json)
WITH (
NameOfTheColumn bigint '$."A number"'
,NameOfTheOtherColumn varchar(100) '$."A text string"'
,JSONColumn nvarchar(max) '$."An object"' AS JSON
,ObjectValue varchar(100) '$."An object".key1'
)
SELECT JSON_VALUE(@json, '$."A number"')
SELECT JSON_QUERY(@json, '$."An object"')
SELECT JSON_QUERY(@json, '$."A number"')
SELECT JSON_VALUE(@json, '$."An object"')
SELECT JSON_MODIFY(@json,'$."A number"', 17)
SELECT JSON_VALUE(@json, '$.Employees[1].address')
--Query and output XML data
DECLARE @xml XML
SET @XML = '<FirstName>Bob</Firstname>'
SELECT @xml
SELECT TRY_CAST('<FirstName>Bob</Firstname>' as XML)
SELECT EmployeeID as '@Id'
,FirstName
,LastName
FROM Employees
WHERE EmployeeID in (1,2)
FOR XML PATH ('Employee')
--Format query output as XML
SELECT EmployeeID
,FirstName
,LastName
FROM Employees
WHERE EmployeeID in (1,2)
FOR XML RAW
SELECT EmployeeID
,FirstName
,LastName
FROM Employees
WHERE EmployeeID in (1,2)
FOR XML RAW ('GiveThisElementAnotherName')
SELECT EmployeeID
,FirstName
,LastName
FROM Employees
WHERE EmployeeID in (1,2)
FOR XML RAW , ELEMENTS
SELECT EmployeeID
,FirstName
,LastName
,Employee_Number
FROM Employees
WHERE EmployeeID in (1,2)
FOR XML RAW
SELECT EmployeeID
,FirstName
,LastName
,Employee_Number
FROM Employees
WHERE EmployeeID in (1,2)
FOR XML RAW, ELEMENTS XSINIL
SELECT EmployeeID
,FirstName
,LastName
FROM Employees
WHERE EmployeeID in (1,2)
FOR XML RAW, ROOT ('Employees')
SELECT c.Customername, o.
