<p style="font-size: 40px; text-align: center;">Fundamentals of SQL</p>
<p style="font-size: 24px; text-align: center;">Exercise Guide 2</p>
<p style="font-size: 16px; text-align: center;">ASDXDDL8M2</p>
<p style="font-size: 16px; text-align: center;">2025-01-27</p>

---

# Dropbox and Google Drive

Dropbox
http://tiny.cc/db20250127

Google Drive
http://tiny.cc/gd20250127

HackMD Exercise Guide 1
http://tiny.cc/hmd120250127

HackMD Exercise Guide 2 (this document)
http://tiny.cc/hmd220250127

# (AD-HOC NOTES BEYOND THIS POINT)

# ORDER BY

SELECT
	ProductID
	,ProductName
	,CategoryID
	,UnitPrice
FROM
	dbo.Products
ORDER BY
	...
;

# TOP

SELECT
	ProductName
	,UnitPrice
FROM
	dbo.Products
ORDER BY
	UnitPrice DESC
;

# Joins

USE Northwind;
--USE <your_database_name_here>; -- Alternative.


-- Create a table called "LeftTable" in the current database.
DROP TABLE IF EXISTS LeftTable;
CREATE TABLE LeftTable (
	Letter			char(1)
	,Number			int
);


-- Populate the table.
INSERT INTO
	LeftTable
	(
		Letter
		,Number
	)
VALUES
	('A',		1)
	,('B',		1)
	,('B',		NULL)
	,('C',		1)
	,('C',		2)
	,('C',		3)
	,('E',		4)
	,(NULL,		NULL)
;


-- Create a table called "RightTable" in the current database.
DROP TABLE IF EXISTS RightTable;
CREATE TABLE RightTable (
	Letter			char(1)
	,Colour			varchar(20)
);


-- Populate the table.
INSERT INTO
	RightTable
	(
		Letter
		,Colour
	)
VALUES
	('A',		'Red')
	,('A',		'Green')
	,('B',		'Blue')
	,('B',		'Black')
	,('C',		NULL)
	,('D',		'Purple')
;



-- Inner join.
SELECT
	L.Letter		-- From LeftTable.
	,L.Number		-- From LeftTable.
	,R.Letter		-- From RightTable.
	,R.Colour		-- From RightTable.
FROM
	LeftTable	AS L
INNER JOIN
	RightTable	AS R
ON
	R.Letter = L.Letter
;


-- Left/right/full join.
SELECT
	L.Letter		-- From LeftTable.
	,L.Number		-- From LeftTable.
	,R.Letter		-- From RightTable.
	,R.Colour		-- From RightTable.
FROM
	LeftTable	AS L
LEFT JOIN
--RIGHT JOIN
--FULL JOIN
	RightTable	AS R
ON
	R.Letter = L.Letter
;


-- Clean up.
DROP TABLE IF EXISTS LeftTable;
DROP TABLE IF EXISTS RightTable;

# Table interrogation

Searching for pairs of tables to join.

SELECT TOP 3 * FROM dbo.Categories;
SELECT TOP 3 * FROM dbo.Customers;
SELECT TOP 3 * FROM dbo.Employees;
SELECT TOP 3 * FROM dbo.[Order Details];
SELECT TOP 3 * FROM dbo.Orders;
SELECT TOP 3 * FROM dbo.Products;
SELECT TOP 3 * FROM dbo.Region;
SELECT TOP 3 * FROM dbo.Shippers;
SELECT TOP 3 * FROM dbo.Suppliers;
SELECT TOP 3 * FROM dbo.Territories;

## INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%Customer%ID%';

SELECT * FROM INFORMATION_SCHEMA.TABLES;


SELECT
	C.*
	,T.*
FROM
	INFORMATION_SCHEMA.COLUMNS	C
INNER JOIN
	INFORMATION_SCHEMA.TABLES	T
ON
	T.TABLE_NAME = C.TABLE_NAME
	AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE
	T.TABLE_TYPE = 'BASE TABLE'
;

# Exercise Guide 1.5 p34

-- Write a query that uses the Northwind database and selects the 
-- CompanyName, ContactName and Phone columns from the 
-- dbo.Customers table.
SELECT
	CompanyName
	,ContactName
	,Phone
FROM
	dbo.Customers
; -- 91 rows.


-- Task 2: Write a query that retrieves supplier contact details
-- 1. In the same script file, add another query that selects the same three columns from the Suppliers table.
SELECT
	Phone
	,ContactName
	,CompanyName
FROM
	dbo.Suppliers
; -- 29 rows.


--Task 3: write a query that retrieves employee contact details
--1. In the same script file, add another query that selects rows from the 
--Employees table.

--2. In the select list for the query, add a column that concatenates the 
--FirstName column to a space character and the LastName column 
--(building up a string containing the employee’s full name).

--3. Add the Extension column as well.

--4. Execute the query and verify that you retrieve nine rows of two columns –
--one containing the full name and one containing the extension.
SELECT
	FirstName + ' ' + LastName AS FullName
	,Extension
FROM
	dbo.Employees
; -- 9 rows.

## UNION ALL

SELECT
	CompanyName
	,ContactName
	,Phone
FROM
	dbo.Customers

UNION ALL -- 120 rows.

SELECT
	CompanyName
	,ContactName
	,Phone
FROM
	dbo.Suppliers
;

## UNION

SELECT
	CompanyName
	,ContactName
	,Phone
FROM
	dbo.Customers

UNION 

SELECT
	CompanyName
	,ContactName
	,Phone
FROM
	dbo.Suppliers

UNION -- 120 rows so far (same as UNION ALL because no duplicates)

SELECT
	'Northwind Traders'			AS CompanyName
	,FirstName + ' ' + LastName	AS FullName
	,Extension
FROM
	dbo.Employees
; -- 129 rows.

# CASE statement

Example

SELECT
	(CASE
		WHEN SizeID = 1 THEN 'Small'
		WHEN SizeID = 2 THEN 'Medium'
		WHEN SizeID = 3 THEN 'Large'
		WHEN SizeID = 4 THEN 'Extra large'
	ELSE
		'UNKNOWN'
	END) AS SizeDesc
;

# Subqueries

SELECT
	OrderID
	,SUM(Quantity * UnitPrice) AS OrderTotalUndiscounted
	,SUM(Quantity * UnitPrice * (1 - Discount)) AS OrderTotal -- After discount.
FROM
	[Order Details]
GROUP BY
	OrderID
;


-- Convert to subquery.
SELECT
	Sub.*
FROM
	(
		SELECT
			OrderID
			,SUM(Quantity * UnitPrice) AS OrderTotalUndiscounted
			,SUM(Quantity * UnitPrice * (1 - Discount)) AS OrderTotal -- After discount.
		FROM
			[Order Details]
		GROUP BY
			OrderID
	) Sub
;

# String functions

-- Extract Wally.
-- Use SUBSTRING on: 'abcdefgwallyhijklmnopqrstuvwxyz'

-- Goal: Extract the SKU number.
'Green T-Shirt SKU12345# Blah Blah Blah'

SELECT PATINDEX('%SKU[0-9][0-9][0-9][0-9][0-9]%', 'Green T-Shirt SKU1234567890# Blah Blah Blah');

# Views

CREATE VIEW dbo.[Products by Category AA] AS
SELECT
	C.CategoryName
	,P.ProductName
	,P.QuantityPerUnit
	,P.UnitsInStock
	,P.Discontinued
FROM
	Categories	C
INNER JOIN
	Products	P
ON
	C.CategoryID = P.CategoryID
WHERE
	P.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName
;