Module 1 - Table Expressions.sql

-- VIEWS 
USE Northwind
GO
CREATE VIEW dbo.ReportsTo AS (
		SELECT
		m.FirstName + ' ' + m.LastName AS ManagerName, e.FirstName + ' ' + e.LastName AS EmployeeName
	FROM
		dbo.Employees AS e
		JOIN
		dbo.Employees AS m
		ON
	e.ReportsTo = m.EmployeeID
)
GO
SELECT * FROM dbo.ReportsTo ORDER BY EmployeeName DESC
GO

CREATE VIEW dbo.MostValuableCustomers AS (
	SELECT TOP 10
		o.CustomerID,
		SUM(UnitPrice * Quantity) AS TotalPrice
	FROM
		dbo.Orders AS o
	INNER JOIN
		dbo.[Order Details] AS od
	ON
		o.OrderID = od.OrderID
	GROUP BY
		o.CustomerID
	ORDER BY
		TotalPrice DESC
)
GO

SELECT * FROM dbo.MostValuableCustomers

GO
-- TVF
-- Which customers have spent OVER a particular amount?
CREATE FUNCTION dbo.CustomerValue(@Price MONEY) RETURNS TABLE AS RETURN (
	SELECT
		o.CustomerID,
		SUM(UnitPrice * Quantity) AS TotalPrice
	FROM
		dbo.Orders AS o
	INNER JOIN
		dbo.[Order Details] AS od
	ON
		o.OrderID = od.OrderID
	GROUP BY
		o.CustomerID
	HAVING
		SUM(UnitPrice * Quantity) > @Price
);
GO

SELECT * FROM dbo.CustomerValue(40000) ORDER BY TotalPrice DESC
SELECT * FROM dbo.CustomerValue(10000) ORDER BY TotalPrice DESC
SELECT * FROM dbo.CustomerValue(4000) ORDER BY TotalPrice ASC

-- Multi-Line TVF's
GO
CREATE FUNCTION dbo.RevenuePerYearBI(@StartYear INT) RETURNS @Revenue TABLE (FinancialYear INT, Revenue MONEY)
BEGIN
	INSERT INTO @Revenue
		SELECT 
			YEAR(o.OrderDate) AS FinancialYear,
			SUM(od.UnitPrice * od.Quantity) AS TotalRevenue
		FROM
			dbo.Orders AS o
		JOIN
			dbo.[Order Details] AS od
		ON
			o.OrderID = od.OrderID
		WHERE
			YEAR(o.OrderDate) >= @StartYear
		GROUP BY
			YEAR(o.OrderDate)
		--INSERT INTO @Revenue ...
	RETURN
END
GO

SELECT * FROM dbo.RevenuePerYearBI(1997)


-- Derived tables

SELECT *
FROM (SELECT TOP 3
		CustomerId
	FROM Orders
	GROUP BY CustomerID
	ORDER BY COUNT(CustomerID)) AS Top3Customers

SELECT p.ProductName, p.CategoryID, UnitPrice, AveragePrice
FROM Products AS p
	INNER JOIN
	(
	SELECT CategoryID, AVG(UnitPrice) AS AveragePrice
	FROM Products
	GROUP BY CategoryID
) AS AveragePricePerCategory
	ON
p.CategoryID = AveragePricePerCategory.CategoryID
WHERE p.UnitPrice >= AveragePrice

-- Derived Tables
-- 3 customers that have placed the most orders
SELECT * FROM 
(
	SELECT TOP 3 CustomerID, COUNT(OrderID) AS NumOrders FROM dbo.Orders GROUP BY CustomerID ORDER BY NumOrders DESC
) AS Top3Customers

--SELECT * FROM Top3Customers

-- Each product that is priced above average FOR ITS CATEGORY
SELECT ProductName, CategoryID, UnitPrice FROM dbo.Products
SELECT CategoryID, AVG(UnitPrice) AS AvgPrice FROM dbo.Products GROUP BY CategoryID

SELECT ProductName, p.CategoryID, UnitPrice, AvgPrice FROM dbo.Products AS p
INNER JOIN
(SELECT CategoryID, AVG(UnitPrice) AS AvgPrice FROM dbo.Products GROUP BY CategoryID) AS DerivedAverages
ON
p.CategoryID = DerivedAverages.CategoryID
WHERE
	UnitPrice > AvgPrice

Module 3 - Window Funcs.sql

SELECT 
	ProductName,
	CategoryID,
	UnitPrice,
	AVG(UnitPrice) OVER () AS AvgPrice
FROM
	dbo.Products

SELECT 
	ProductName,
	CategoryID,
	UnitPrice,
	AVG(UnitPrice) OVER (PARTITION BY CategoryID) AS AvgPrice
FROM
	dbo.Products

SELECT
	OrderYear,
	OrderMonth,
	TotalRevenue,
	AVG(TotalRevenue) OVER (ORDER BY OrderYear, OrderMonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeMonthAverage,
	SUM(TotalRevenue) OVER(ORDER BY OrderYear, OrderMonth) AS RunningTotal
FROM (
	SELECT
		YEAR(o.orderdate) AS OrderYear,
		MONTH(o.orderdate) AS OrderMonth,
		SUM (od.unitprice * od. quantity) AS TotalRevenue
	FROM 
		dbo.[Order Details] od 
	JOIN 
		dbo.Orders o 
	ON 
		od.OrderID = o.OrderID
	GROUP BY 
		YEAR(o.orderdate), MONTH(o.orderdate)
) AS DerivedRevenue


-- Ranking
GO
CREATE VIEW dbo.CustomersRankedByCountry AS (
SELECT 
	c.Country, c.CompanyName, COUNT(od.OrderID) AS TotalOrders, 
	RANK() OVER (PARTITION BY Country ORDER BY COUNT(od.OrderID) DESC) AS Ranking
FROM 
	[Order Details] od
JOIN 
	Orders o 
ON 
	od.OrderID = o.OrderID 
JOIN 
	Customers c 
ON 
	o.CustomerID = c.CustomerID
GROUP BY 
	c.Country, c.CompanyName
)

SELECT * FROM dbo.CustomersRankedByCountry WHERE Ranking = 1

SELECT 
	c.Country, c.CompanyName, COUNT(od.OrderID) AS TotalOrders, 
	CUME_DIST() OVER (PARTITION BY Country ORDER BY COUNT(od.OrderID) DESC) AS Ranking
FROM 
	[Order Details] od
JOIN 
	Orders o 
ON 
	od.OrderID = o.OrderID 
JOIN 
	Customers c 
ON 
	o.CustomerID = c.CustomerID
GROUP BY 
	c.Country, c.CompanyName
ORDER BY
	Country ASC, Ranking ASC

-- Positonal

SELECT 
	YEAR(OrderDate) AS OrderYear, 
	COUNT(*) AS TotalOrders, 
	LAG(COUNT(*), 1, NULL) OVER (ORDER BY YEAR(OrderDate)) AS PreviousYear,
	COUNT(*) - LAG(COUNT(*), 1, NULL) OVER (ORDER BY YEAR(OrderDate)) AS YearlyChange
FROM dbo.Orders
GROUP BY Year(OrderDate)
ORDER BY OrderYear ASC

Module 4 - Pivots and Advanced Grouping.sql

SELECT 'TotalRevenue' AS RevenuePerYear, * FROM
(
	SELECT YEAR(OrderDate) AS OrderYear, UnitPrice * Quantity AS OrderPrice
	FROM
		ORDERS o
		JOIN
		[Order Details] od
		ON
	o.OrderID = od.OrderID
) AS OriginalData
PIVOT
(
	SUM(OrderPrice) FOR OrderYear IN ([1996], [1997], [1998])
) AS PivotedData

SELECT * FROM
(
	SELECT YEAR(OrderDate) AS OrderYear, CustomerID, SUM(UnitPrice * Quantity) AS TotalOrderPrice
	FROM
		ORDERS o
		JOIN
		[Order Details] od
		ON
	o.OrderID = od.OrderID
	GROUP BY YEAR(OrderDate), o.CustomerID
) AS OriginalData
PIVOT
(
	SUM(TotalOrderPrice) FOR OrderYear IN ([1996], [1997], [1998])
) AS PivotedDate
GO
WITH Pivotted AS
(
	SELECT CustomerID, [1996], [1997], [1998]
	FROM
		(
	SELECT YEAR(OrderDate) AS OrderYear, CustomerID, SUM(UnitPrice * Quantity) AS TotalOrderPrice
		FROM
			ORDERS o
			JOIN
			[Order Details] od
			ON
	o.OrderID = od.OrderID
		GROUP BY YEAR(OrderDate), o.CustomerID
)AS OrdersByYear
PIVOT
(
	SUM(TotalOrderPrice)
	FOR
	OrderYear IN ([1996], [1997], [1998])
) AS PivotedData
)










SELECT OrderYear, CustomerId, TotalRevenue FROM 
(
	SELECT * FROM Pivotted
) as pvt
UNPIVOT
(
	TotalRevenue FOR OrderYear IN ([1996], [1997], [1998])	
) AS unpvt
ORDER BY OrderYear, CustomerID
GO

-- Advanced Grouping
-- Grouped on Year AND Month
-- What if I ALSO want to find out the total orders per year and prer month?
-- e.g. 1996 -> 200 orders JAN -> 88
SELECT 
	YEAR(OrderDate) AS OrderYear, 
	MONTH(OrderDate) AS OrderMonth,
	COUNT(*) AS TotalOrders
FROM dbo.Orders
GROUP BY Year(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear ASC, OrderMonth ASC

SELECT 
	YEAR(OrderDate) AS OrderYear, 
	COUNT(*) AS TotalOrders
FROM dbo.Orders
GROUP BY Year(OrderDate)
ORDER BY OrderYear ASC

SELECT 
	MONTH(OrderDate) AS OrderMonth,
	COUNT(*) AS TotalOrders
FROM dbo.Orders
GROUP BY MONTH(OrderDate)
ORDER BY OrderMonth ASC

SELECT 
	YEAR(OrderDate) AS OrderYear, 
	MONTH(OrderDate) AS OrderMonth,
	COUNT(*) AS TotalOrders,
	GROUPING(YEAR(OrderDate)) AS GroupingYear,
	GROUPING(MONTH(OrderDate)) AS GroupingMonth,
	GROUPING_ID(YEAR(OrderDate), MONTH(OrderDate)) AS GroupingLevel
FROM dbo.Orders
GROUP BY GROUPING SETS 
(
	(YEAR(OrderDate), MONTH(OrderDate)), (YEAR(OrderDate)), (MONTH(OrderDate)), ()
)
ORDER BY GroupingLevel DESC, OrderYear ASC, OrderMonth ASC

-- CUBE


SELECT 
	YEAR(OrderDate) AS OrderYear, 
	MONTH(OrderDate) AS OrderMonth,
	COUNT(*) AS TotalOrders,
	GROUPING(YEAR(OrderDate)) AS GroupingYear,
	GROUPING(MONTH(OrderDate)) AS GroupingMonth,
	GROUPING_ID(YEAR(OrderDate), MONTH(OrderDate)) AS GroupingLevel
FROM dbo.Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate) WITH CUBE -- EVERY POSSIBLE COMBINATION OF GROUPS
ORDER BY GroupingLevel DESC, OrderYear ASC, OrderMonth ASC

-- ROLLUP

SELECT 
	YEAR(OrderDate) AS OrderYear, 
	MONTH(OrderDate) AS OrderMonth,
	COUNT(*) AS TotalOrders,
	GROUPING(YEAR(OrderDate)) AS GroupingYear,
	GROUPING(MONTH(OrderDate)) AS GroupingMonth,
	GROUPING_ID(YEAR(OrderDate), MONTH(OrderDate)) AS GroupingLevel
FROM dbo.Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate) WITH ROLLUP -- generates total for previous month
ORDER BY GroupingLevel DESC, OrderYear ASC

Module 5 - Programming.sql

DECLARE @Age TINYINT = 28

PRINT @Age

SET @Age = 14

PRINT @Age

SET @Age = 123456789

PRINT @Age

--SET @Age = 22

DECLARE @Price MONEY

-- make sure kid grows up

WHILE @Age < 18 	
BEGIN
	SET @Age = @Age + 1
	IF @Age % 2 = 0 CONTINUE
	PRINT 'Age: ' + CAST(@Age AS VARCHAR)

END
IF @Age >= 18	
BEGIN
	PRINT 'Is adult'
	SET @Price = 44.94
END
ELSE 
BEGIN
	PRINT 'Is child'
	SET @Price = 12.34
END
PRINT @Price

GO

DECLARE @i INT = 0

WHILE @i <= 100
BEGIN
	SET @i = @i + 1
	IF @i = 50 RETURN
	IF @i % 2= 0 PRINT 'EVEN: ' + CAST(@i AS VARCHAR)
	ELSE PRINT 'ODD: ' + CAST(@i AS VARCHAR)
END

PRINT 'loop over'
GO
PRINT 'New Batch'
GO
-- CURSORS
DECLARE @OrderYear INT, @YearlyRevenue MONEY

DECLARE YearlyRevenueCursor CURSOR
FOR SELECT
    YEAR(o.OrderDate), SUM(od.UnitPrice * Quantity)
FROM
    dbo.Orders AS o
    JOIN
    dbo.[Order Details] AS od
    ON
	o.OrderID = od.OrderID
GROUP BY YEAR(o.OrderDate)
OPEN YearlyRevenueCursor
FETCH NEXT FROM YearlyRevenueCursor INTO @OrderYear, @YearlyRevenue
WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT CAST(@OrderYear AS VARCHAR) + ': £' + CAST(@YearlyRevenue AS varchar);
    FETCH NEXT FROM YearlyRevenueCursor INTO 
            @OrderYear, 
            @YearlyRevenue;
END;

CLOSE YearlyRevenueCursor;

DEALLOCATE YearlyRevenueCursor;

-- BIG FUNC
-- any particular sentence -> Any Particular Sentence
ALTER FUNCTION dbo.Capitalise2(@String VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    DECLARE @String2 VARCHAR(255) = LOWER(TRIM(@String))
    DECLARE @Index int = 1
    DECLARE @HasRun int = 0
    WHILE @Index > 0
    BEGIN
        IF @Index = 1
            SET @Index = 0
        SET @String2 = STUFF(@String2, @Index + 1, 1, UPPER(SUBSTRING(@String2, @Index + 1, 1)))
        SET @Index = CHARINDEX(' ', @String, @Index + 1)
    END
    RETURN @String2
END
GO
SELECT dbo.Capitalise2(LOWER(ContactName)) AS ProperCase
FROM dbo.Customers

Module 6 - Error Handling.sql

SELECT FROM db.o.Employees

SELECT AVG(UnitPrice) FROM [Order Details]

-- %s -> string placeholder
EXEC sp_addmessage 70000, 15, 'Oops, you seem to have %s'
-- -1 -> use default
RAISERROR(70000, -1, 1, 'Bloop')

RAISERROR(70000, -1, 2, 'Bloop 2: The bloopening');

BEGIN TRY
	DECLARE @msg VARCHAR(100) = FORMATMESSAGE('Oops, you seem to have: %s', 'accidentally used a new version of sql server');
	THROW 70000, @msg, 1
END TRY
BEGIN CATCH
	PRINT 'ERROR AHOY!'
END CATCH
PRINT 'hello'

SELECT * FROM Products

CREATE TABLE #Trainers (Name VARCHAR(100) NOT NULL UNIQUE)
INSERT INTO #Trainers (Name) VALUES ('Jordan')

DECLARE @Trainer VARCHAR(50) = 'Jordan'

BEGIN TRY
	INSERT INTO #Trainers (Name) VALUES (@Trainer)
END TRY
BEGIN CATCH
	PRINT 'Trainer ' + @Trainer + 'already exists in the table'
END CATCH

SELECT * FROM #Trainers

-- Ex 2 Task 2 STARTING POINT
DECLARE @Vendor VARCHAR(100)
SET @Vendor = 'QA'

IF @Vendor IS NULL
	BEGIN
		PRINT 'Vendor must not be NULL'
		RETURN
	END

IF NOT EXISTS (SELECT * FROM dbo.Vendor WHERE VendorName = @Vendor)
	BEGIN
		PRINT 'Vendor ' + @Vendor + ' does not exist'
		RETURN
	END

SELECT *
	FROM dbo.Course AS C
		INNER JOIN dbo.Vendor AS V
			ON C.VendorID = V.VendorID
	WHERE VendorName = @Vendor	
GO

-- Ex 3 Task 2	STARTING POINT

UPDATE dbo.Vendor
SET VendorName = NULL
WHERE VendorID = 1

Module 7 - Transactions.sql

DROP TABLE #Trainers;
CREATE TABLE #Trainers
(
    TrainerID INT PRIMARY KEY IDENTITY,
    [Name] VARCHAR(100) NOT NULL UNIQUE,
    Cash MONEY CHECK (Cash > 0 AND CASH IS NOT NULL)
)


INSERT INTO #Trainers
    ([Name], Cash)
VALUES
    ('Jordan', 4494),
    ('Cameron', 5)

-- GET CAMERON TO LEND ME A TENNER
SELECT * FROM #Trainers
BEGIN TRY
	BEGIN TRAN -- creates a restore point
		UPDATE #Trainers SET Cash = Cash - 10 WHERE Name = 'Jordan'
		UPDATE #Trainers SET Cash = Cash + 10 WHERE Name = 'Cameron'
	COMMIT TRAN -- save changes to the db
END TRY
BEGIN CATCH
	PRINT 'Transaction cancelled!'
	ROLLBACK TRAN
END CATCH

SELECT * FROM #Trainers