---
# System prepended metadata

title: ASDXDDL8M2 2024-10-14

---

<p style="font-size: 40px; text-align: center;">Fundamentals of SQL</p>
<p style="text-align: center;">ASDXDDL8M2</p>
<p style="text-align: center;">2024-10-14</p>

---


# Testing, Testing

```sql
SELECT 'Hello World';
```

Can you see this text?
Send a 😬!


# Dropbox and Google Drive

Some of the versions of SQL scripts on Dropbox and Google Drive are slightly newer than the versions copied below. The differences are minimal.

Dropbox
http://tiny.cc/db20241014

Google Drive
http://tiny.cc/gd20241014


# Code Comments

Here are some use cases for adding **comments** (non-executable text notes) to our SQL code:
* Add metadata to the top of a script, such as the purpose of the script, the author, and the date of last update.
* Explain what a query or expression is doing.
* Temporarily comment out a block of code while debugging.
* Create headings and section dividers.
* Assign a unique name to each expression.
* Add cautionary notes.
* Explain decisions and business rules.
* Mention linked _JIRA_ tickets.
* Note benchmarking results.


## Inline Comments
To create an inline comment, use `--`.

```sql=
-- Inline comment at the start of a line.
```

```sql=
SELECT * FROM MyTable; -- Inline comment at the end of a line.
```


## Multi-line Comments
To create a multi-line comment, use `/*` and `*/`.

```sql=
/*	Multi-
	line
	comment
*/
```


# SELECT

See **"DG_02 Retrieving Data 1—SELECT v0.1.2.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_02 Retrieving Data 1 – SELECT                                                                                           │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • Set the database context                                                                                               │
  │    • SELECT                                                                                                                 │
  │    • SELECT Lists                                                                                                           │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Set the database context                                                                                                   │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- We can set the database context with the `USE` command. This is equivalent to selecting the database name from the drop-down
-- list at the top-left of the screen.
USE Northwind;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  SELECT                                                                                                                     │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

/*	To execute a query, select the entire query and then perform one of the following actions:
	  • Press F5.
	  • Press Ctrl + E.
	  • Press Alt + X.
	  • Query > Execute.
	  • Right-click > Execute.
	  • SQL Editor bar > Execute.
*/


-- ⊱  TASK  ⊰
-- Execute the query below to retrieve all data from the Customers table.
-- The output appears in the Results panel at the bottom of the screen.
SELECT * FROM dbo.Customers;

-- Q: How many rows are there in the Customers table?
-- A: 91.


-- ⊱  TASK  ⊰
-- In Object Explorer, expand Databases > Northwind > Tables.
-- Write and execute several more `SELECT *` queries, using a different table for each.
SELECT * FROM dbo.[Order Details];
SELECT * FROM dbo.Products;
SELECT * FROM dbo.Territories;

-- Q: What happens if we select multiple queries and execute them all at the same time?
-- A: The Results panel contains the output of all the queries.



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  SELECT Lists                                                                                                               │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/
 
-- We have seen that `SELECT *` retrieves all columns. If we want to retrieve only specific colummns, we need a SELECT list.


-- `SELECT *` means "select all columns".

-- ⊱  TASK  ⊰
-- Execute the query below to retrieve all data from the Products table.
SELECT * FROM dbo.Products;

-- Q: How many columns are returned?
-- A: 10.


-- To retrieve data from specific columns only, we specify those columns in a list, as below.

-- ⊱  TASK  ⊰
-- Execute the query.
SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM dbo.Products;

-- Q: How many columns are returned?
-- A: 4.


-- ⊱  TASK  ⊰
-- We can use line breaks and tab indents to make the code more readable.
-- Verify that the following query returns the same data as the previous query.
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,UnitsInStock
FROM
	dbo.Products
;


-- We can retrieve columns in any order.

-- ⊱  TASK  ⊰
-- Verify that the following query returns the same data as the previous query, but with the columns rearranged.
SELECT
	UnitPrice
	,UnitsInStock
	,ProductName
	,ProductID
FROM
	dbo.Products
;


-- ⊱  TASK  ⊰
-- Write and execute several more `SELECT <list>` queries.
-- You may wish to execute `SELECT *` first to view the column names.
-- Format your queries for readability.

-- Example solutions:
SELECT
	LastName
	,FirstName
	,BirthDate
FROM
	dbo.Employees
;

SELECT
	CategoryID
	,CategoryName
	,[Description]
FROM
	dbo.Categories
;
```


# Expressions in SELECT Lists

See **"DG_02 Retrieving Data 2—Expressions in SELECT lists v0.1.1.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_02 Retrieving Data 2 – Expressions in SELECT Lists                                                                      │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • Column Aliases                                                                                                         │
  │    • Expressions in SELECT Lists                                                                                            │
  │    • Expressions and Column Aliases Together                                                                                │
  │    • CHALLENGE TASK                                                                                                         │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


USE Northwind;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Column Aliases                                                                                                             │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- In a query, we have the option to apply a custom name--called an alias--to each column retrieved.
-- Note: the custom column name only appears in the results--the source table is unmodified.


-- ⊱  TASK  ⊰
-- Compare the results of the following two queries. Note the column names.
SELECT
	UnitPrice
	,UnitsInStock
FROM
	dbo.Products
;

SELECT
	UnitPrice		AS Price
	,UnitsInStock	AS Stock
FROM
	dbo.Products
;


-- ⊱  TASK  ⊰
-- Write several queries retrieve data from specific columns of the Suppliers table.
-- Your queries should apply custom aliases to one or more of these columns.

-- Example solutions:
SELECT
	CompanyName
	,Address		AS StreetAddress
	,PostalCode		AS Postcode
FROM
	dbo.Suppliers
;

SELECT
	ContactName		AS [Name]
	,ContactTitle	AS Title
FROM
	dbo.Suppliers
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Expressions in SELECT Lists                                                                                                │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- We can create derived columns by applying mathematical operators to existing columns.


-- ⊱  TASK  ⊰
-- Execute the query below. Note the two unnamed columns that result from the expressions `UnitPrice * 0.9` and `UnitPrice * 0.5`.
SELECT
	UnitPrice
	,UnitPrice * 0.9
	,UnitPrice * 0.5
FROM
	dbo.Products
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Expressions and Column Aliases Together                                                                                    │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


-- ⊱  TASK  ⊰
-- Modify the query below so that the `UnitPrice * 0.9` column is named "Discount10Pcnt" and the `UnitPrice * 0.75` column is
-- named "Discount25Pcnt".
SELECT
	UnitPrice
	,UnitPrice * 0.9
	,UnitPrice * 0.75
FROM
	dbo.Products
;

-- A:
SELECT
	UnitPrice
	,UnitPrice * 0.9	AS Discount10Pcnt
	,UnitPrice * 0.75	AS Discount25Pcnt
FROM
	dbo.Products
;

-- We notice that the derived columns seem to have more decimal digits than the UnitPrice column. This is a topic for a later time.


-- ⊱  TASK  ⊰
-- Create a copy of your previous query. Add two more columns: [Discount25Pcnt] and [Discount75Pcnt].

-- A:
SELECT
	UnitPrice
	,UnitPrice * 0.9	AS Discount10Pcnt
	,UnitPrice * 0.75	AS Discount25Pcnt
	,UnitPrice * 0.5	AS Discount50Pcnt
	,UnitPrice * 0.25	AS Discount75Pcnt
FROM
	dbo.Products
;


/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Arithmetic Operators                                                                                                       │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

/*	The arithmetic operators supported in T-SQL are:
	  +		Addition
	  -		Subtraction
	  *		Multiplication
	  /		Division
	  %		Modulo (the integer remainder of a division)
	  ()	Parentheses
*/


-- ⊱  TASK  ⊰
-- Complete the query below according to your best judgement. Interpret [FutureStockValue] as the total monetary value of units
-- in stock plus units on order. You will need to reference one or more columns from the Products table, and you will need to use
-- one or more arithmetic operators.
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,UnitsInStock
	,UnitsOnOrder
	,... AS CurrentStockValue
	,... AS FutureStockValue
FROM
	dbo.Products
;


-- A: First, let's examine the Products table.
SELECT * FROM dbo.Products;

-- Now we can figure out what is required.
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,UnitsInStock
	,UnitsOnOrder
	,UnitPrice * UnitsInStock					AS CurrentStockValue
	,(UnitsInStock + UnitsOnOrder) * UnitPrice	AS FutureStockValue
FROM
	dbo.Products
;


-- Create a copy of your previous query. Add the [Reorder] column to the SELECT list. Also add a derived column:
-- [UnitsInStock] - [ReorderLevel]. Give this column the alias "StockSurplus".
-- A:
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,UnitsInStock
	,UnitsOnOrder
	,ReorderLevel
	,UnitPrice * UnitsInStock					AS CurrentStockValue
	,(UnitsInStock + UnitsOnOrder) * UnitPrice	AS FutureStockValue
	,UnitsInStock - ReorderLevel				AS StockSurplus
FROM
	dbo.Products
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  ⊱  CHALLENGE TASK  ⊰                                                                                                       │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

/*	Suppose that Northwind stores all its stock in boxes, with only one type of product in each box. Each box can hold a maximum
	of 12 units. The minimum number of boxes is always used. Northwind would like to calculate how much spare box space it has
	for each product.

	Examples:
		ProductID	ProductName		UnitsInStock	BoxesRequired	SpareSpace	Explanation
		1			Chai			39				4				9			3 full boxes; 1 box with 9 spaces
		2			Chang			17				2				7			1 full box; 1 box with 7 spaces
		3			Aniseed Syrup	13				2				11			1 full box; 1 box with 11 spaces

	Create a copy of your previous query. Add a new derived column, "SpareSpace". Use the modulo operator, `%`.
	IMPORTANT: SpareSpace can be any number between 0 and 11, but never 12.
*/

-- A:
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,UnitsInStock
	,UnitsOnOrder
	,ReorderLevel
	,UnitPrice * UnitsInStock					AS CurrentStockValue
	,(UnitsInStock + UnitsOnOrder) * UnitPrice	AS FutureStockValue
	,UnitsInStock - ReorderLevel				AS StockSurplus
	,(12 - UnitsInStock % 12) % 12				AS SpareSpace
FROM
	dbo.Products
;


```


# Four-Part Names

See **"DG_02 Retrieving Data 4—Four-Part Names v0.1.1.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_02 Retrieving Data 4 – Four-Part Names                                                                                  │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • "Invalid Object Name"                                                                                                  │
  │    •  All Valid One-, Two-, Three-, and Four-Part Names                                                                     │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


USE [master];



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  "Invalid Object Name"                                                                                                      │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- ⊱  TASK  ⊰
-- Verify that the following query fails with "Invalid object name 'dbo.Products'."
SELECT
	UnitPrice
	,UnitsInStock
FROM
	dbo.Products
;


-- The error occurs because the database context is set to master, whereas dbo.Products is in the Northwind database.
-- We could fix the problem by setting the database context to Northwind by executing `USE Northwind`. Alternatively, we can
-- specify the Northwind database by using a three-part name: <database_name>.<schema_name>.<object_name>.

-- ⊱  TASK  ⊰
-- Verify that the following query executes successfully.
SELECT
	UnitPrice
	,UnitsInStock
FROM
	Northwind.dbo.Products
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  All Valid One-, Two-, Three-, and Four-Part Names                                                                          │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

/* The complete list of valid two-, three-, and four-part names is as follows:
	Object reference format										Description
	—————————————————————————————————————————————————————————————————————————————————————————————————————————
	<server_name>.<database_name>.<schema_name>.<object_name>	Four-part name.
	<server_name>.<database_name>..<object_name>				Schema name is omitted.
	<server_name>..<schema_name>.<object_name>					Database name is omitted.
	<server_name>...<object_name>								Database and schema name are omitted.
	<database_name>.<schema_name>.<object_name>					Server name is omitted.
	<database_name>..<object_name>								Server and schema name are omitted.
	<schema_name>.<object_name>									Server and database name are omitted.
	<object_name>												Server, database, and schema name are omitted.
	
	Source: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver16&tabs=code
*/
```


# WHERE

See **"DG_03 Filtering Rows 1—WHERE v0.1.1.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_03 Filtering Rows 1 – WHERE                                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • WHERE                                                                                                                  │
  │    • Comparison Operators                                                                                                   │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


USE Northwind;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  WHERE                                                                                                                      │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- We can filter rows by adding a `WHERE` clause to a query. `WHERE` must come after `SELECT`.


-- ⊱  TASK  ⊰
-- Compare the outputs of the following two queries.
SELECT
	ProductID
	,ProductName
	,Discontinued
FROM
	dbo.Products
;

SELECT
	ProductID
	,ProductName
	,Discontinued
FROM
	dbo.Products
WHERE
	Discontinued = 0
;

-- Q: How many rows are returned for each query?
-- A: 77, 69.


-- ⊱  TASK  ⊰
-- Create a copy of the previous query. Change `Discontinued = 0` to `Discontinued = 1`.
-- Before you execute the query, predict how many rows will be returned. Execute the query and see if your prediction was correct.

-- A: 8 rows.
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,Discontinued
FROM
	dbo.Products
WHERE
	Discontinued = 1
;


-- ⊱  TASK  ⊰
-- Complete the query below to return only rows where [UnitPrice] = 18.
-- Execute the query and verify that 4 rows are returned.
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,Discontinued
FROM
	dbo.Products
WHERE
	...
;

-- A:
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,Discontinued
FROM
	dbo.Products
WHERE
	UnitPrice = 18
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Comparison Operators                                                                                                       │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- ⊱  TASK  ⊰
-- Complete the query below to return only rows where [UnitPrice] is greater than 35.
-- The `>` operator achieves a "greater than" comparison.
-- Verify that your query returns 17 rows.
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,Discontinued
FROM
	dbo.Products
WHERE
	...
;

-- A:
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,Discontinued
FROM
	dbo.Products
WHERE
	UnitPrice > 35
;


-- ⊱  TASK  ⊰
-- Complete the query below to return only rows where [UnitPrice] is less than or equal to 35.
-- The `<=` operator achieves a "less than or equal to" comparison.
-- Before you execute your query, predict the number of rows that will be returned. (Note: the Products table contains 77 rows
-- in total.)
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,Discontinued
FROM
	dbo.Products
WHERE
	...
;

-- A: 60 rows: 77 - 17 = 60.
SELECT
	ProductID
	,ProductName
	,UnitPrice
	,Discontinued
FROM
	dbo.Products
WHERE
	UnitPrice <= 35
;


/*	Here is a complete list of equality and inequality operators supported in T-SQL:
	=		Equal to
	>		Greater than
	<		Less than
	>=		Greater than or equal to
	<=		Less than or equal to and
	<>		Not equal to
	
	!=		Not equal to (same as <>)
	!<		Not less than (same as >=)
	!>		Not greater than (same as <=)
*/


-- ⊱  TASK  ⊰
-- Write and execute several more queries that include a comparison operator in the WHERE clause.

-- Example solutions:
SELECT
	*
FROM
	dbo.Products
WHERE
	UnitsOnOrder <> 0
;

SELECT
	*
FROM
	dbo.Products
WHERE
	UnitsInStock >= 100
;
```


# Quiz 1
```sql=
-- Q1: What is the SQL command for each of the following operations?
/*
	a) Retrieve data.
	b) Filter data.
*/

-- Q2: Set the database context to the NORTHWIND database.
USE Northwind;

-- Q3: Write a query that retrieves all data from Employees.
SELECT * FROM dbo.Employees;

-- Q4: Write another version of the same query, this time using
-- a three-part name.
SELECT * FROM Northwind.dbo.Employees;

-- Q5: Write another version of (Q3), this time filter to rows where
-- city is London.
SELECT
	*
FROM
	dbo.Employees
WHERE
	City = 'London'
;

-- Q6: Write another version of (Q3), this time filter to rows where
-- the person's name is later than "Pigeon" in the alphabet.
SELECT
	*
FROM
	dbo.Employees
WHERE
	FirstName > 'Pigeon'
;

-- Q7: Create a copy of (Q6) and modify to only retrieve the following columns:
-- Employee ID, first name, last name, title.
SELECT
	EmployeeID
	,FirstName
	,LastName
	,TitleOfCourtesy
FROM
	dbo.Employees
WHERE
	FirstName > 'Pigeon'
;


-- Q8: Select all data from [Order Details].
SELECT * FROM [Order Details];

-- Q9: Select all columns except OrderID from [Order Details], filtering to rows where
-- UnitPrice is between 10 and 20, inclusive.
SELECT
	*
FROM
	[Order Details]
WHERE
	UnitPrice >= 10
	AND UnitPrice <= 20
;


-- Q10: Create a version of (Q9), this time changing the WHERE clause so that it filters
-- to rows where EITHER Quantity >= 35 OR (Quantity < 20 AND Discount is not zero).
SELECT
	*
FROM
	[Order Details]
WHERE
	Quantity >= 35
	OR (Quantity < 20 AND Discount != 0)
;
```


# Filtering Dates

See **"DG_03 Filtering Rows 2—Filtering Dates v0.1.1.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_03 Filtering Rows 2 – Filtering Dates                                                                                   │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • Filtering Dates                                                                                                        │
  │    • Inequalities with Date Strings                                                                                         │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


USE Northwind;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Filtering Dates                                                                                                            │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- In T-SQL, the function DATEFROMPARTS(year, month, day) can be used to create a DATE variable. For example,
-- `DATEFROMPARTS(2099, 1, 31)`.
-- In comparison operations, a DATE variable can be compared to a datelike string literal such as '2099-01-31'.

/*	The most commonly used date formats for string literals are as follows:
		Format			Example			Notes
		'yyyy-mm-dd'	2099-01-31		ISO 8601 Format.					Unambiguous. Recommended for general use.
		'yyyymmdd'		20990131		ISO 8601 Basic Format.				Unambiguous.
		'mm/dd/yyyy'	01/31/2099		US format. Locale-dependent.		*** Ambiguous. Not recommended. ***
		'dd/mm/yyyy'	31/01/2099		European format. Locale-dependent.	*** Ambiguous. Not recommended. ***
*/


-- ⊱  TASK  ⊰
-- Execute the query below and verify that 3 rows are returned.
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	OrderDate = DATEFROMPARTS(1998, 4, 29)
;


-- ⊱  TASK  ⊰
-- Complete the query below using a string literal equivalent to `DATEFROMPARTS(1998, 4, 29)`. Choose an unambiguous date format.
-- Verify that your query returns the same 3 rows.
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	OrderDate = ...
;

-- A:
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	OrderDate = '1998-04-29'
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Inequalities with Date Strings                                                                                             │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- ⊱  TASK  ⊰
-- Complete the query below to returns rows only where [OrderDate] is equal to or later than 1 May 1998.
-- Note: all equality and inequality operators work with DATE variables: = > < >= <= != !> !<.
-- Verify that your query returns 14 rows.
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	...
;

-- A:
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	OrderDate >= '1998-05-01'
;


-- ⊱  TASK  ⊰
-- Complete the query below to returns rows only where [OrderDate] is between 1 May 1998 and 4 May 1998, inclusive.
-- Verify that your query returns 6 rows.
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	...
;

-- A:
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	(OrderDate >= '1998-05-01') AND (OrderDate <= '1998-05-04')
;
```


# Logical Operators

See **"DG_03 Filtering Rows 3—Logical Operators v0.1.1.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_03 Filtering Rows 3 – Logical Operators                                                                                 │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • Introduction: Logical Operators                                                                                        │
  │    • AND                                                                                                                    │
  │    • OR                                                                                                                     │
  │    • NOT                                                                                                                    │
  │    • Combining Logical Operators                                                                                            │
  │    • Formatting a Complex WHERE Predicate                                                                                   │
  │    • CHALLENGE TASK                                                                                                         │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


USE Northwind;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Introduction: Logical Operators                                                                                            │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- The logical operators AND, OR, and NOT can be used in various SQL expressions.
-- We will begin by using these operators to modify the WHERE clause.



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  AND                                                                                                                        │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- The AND operator returns TRUE if both of its operands (inputs) are TRUE, and FALSE otherwise.
/*	Input 1		Input 2		AND Output
	TRUE		TRUE		TRUE
	TRUE		FALSE		FALSE
	FALSE		TRUE		FALSE
	FALSE		FALSE		FALSE
*/


-- ⊱  TASK  ⊰
-- Execute the query below. Verify that the query returns all 9 rows of the Employees table.
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
;


-- ⊱  TASK  ⊰
-- Create a copy of the previous query. Add a WHERE clause to filter to rows where Country = "USA".
-- You will need to use single-quotes around "USA", like this: Country = 'USA'. (Double-quotes will not work.)
-- Verify that your query returns 5 rows.

-- A:
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	Country = 'USA'
;


-- ⊱  TASK  ⊰
-- Create a copy of the previous query and modify it so that the WHERE clause now filters to rows where (Country = "USA")
-- AND (TitleOfCourtesy = "Ms."). Before you execute your query, predict how many rows will be returned.

-- A: 3 rows.
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	Country = 'USA'
	AND TitleOfCourtesy = 'Ms.'
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  OR                                                                                                                         │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- The OR operator returns TRUE if one or both of its operands (inputs) are TRUE, and FALSE otherwise.
/*	Input 1		Input 2		OR Output
	TRUE		TRUE		TRUE
	TRUE		FALSE		TRUE
	FALSE		TRUE		TRUE
	FALSE		FALSE		FALSE
*/


-- ⊱  TASK  ⊰
-- Create a copy of the previous query and modify it so that the WHERE clause now filters to rows where (Country = "USA")
-- OR (TitleOfCourtesy = "Ms."). Before you execute your query, predict how many rows will be returned. Be careful!

-- A: 6 rows.
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	Country = 'USA'
	OR TitleOfCourtesy = 'Ms.'
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  NOT                                                                                                                        │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- The NOT operator converts TRUE to FALSE and FALSE to TRUE. That is, NOT flips the parity of a logical value.
/*	Input	NOT Output
	TRUE	FALSE
	FALSE	TRUE
*/


-- ⊱  TASK  ⊰
-- Execute the query below and verify that it returns 5 rows.
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	NOT (City = 'London')
	--City != 'London' -- Equivalent.
	--City <> 'London' -- Equivalent.
;


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [City] is neither "London" nor "Seattle". Use NOT.
-- Verify that your query returns 3 rows.
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	...
;

-- A:
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	NOT (City = 'London' OR City = 'Seattle')
	--City != 'London' AND City != 'Seattle' -- An equivalent that avoids using NOT.
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Combining Logical Operators                                                                                                │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

/*	When logical operators are combined in a single expression, a specific order of precedence is obeyed:
		1. NOT
		2. AND
		3. OR
	This order of precedence mirrors the order of precedence of mathematical operators: () ^ / * + - ("BODMAS"/"PEMDAS").
		NOT is analogous to mathematical negation, which is a multiplication operation: BOD_M_AS.
		AND is also analogous to multiplication: BOD_M_AS.
		OR is analogous to addition: BODM_A_S.
*/


-- ⊱  TASK  ⊰
-- Consider the query below. Do not execute the query yet.
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	Country = 'USA'
	AND TitleOfCourtesy = 'Dr.'
	OR TitleOfCourtesy = 'Ms.'
;

/*	Q: Which of the following WHERE predicates do you think is being applied?
	  1. Country = 'USA' AND (TitleOfCourtesy = 'Dr.' OR TitleOfCourtesy = 'Ms.')
	  2. (Country = 'USA' AND TitleOfCourtesy = 'Dr.') OR TitleOfCourtesy = 'Ms.'
*/


-- ⊱  TASK  ⊰
-- Execute the query and inspect the output carefully to see if you were right.

-- A: (2). AND is applied before OR.



-- ⊱  TASK  ⊰
-- We can use parentheses to enforce a custom order of evaluation.
-- Predict how many rows will be returned by the query below. Execute the query and see if you were right.
SELECT
	FirstName
	,LastName
	,TitleOfCourtesy
	,City
	,Country
FROM
	dbo.Employees
WHERE
	Country = 'USA'
	AND (TitleOfCourtesy = 'Dr.' OR TitleOfCourtesy = 'Ms.')
;

-- A: 4 rows.




/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Formatting a Complex WHERE Predicate                                                                                       │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- When we have a complex WHERE predicate, we should consider using new lines and indents to improve the readability.

-- Examine the WHERE predicate below, which consists of a single, long line.
WHERE
	 (Condition1 AND (((NOT Condition2) AND ((Condition3 OR Condition4) AND Condition5)) OR Condition6)) AND Condition7 AND Condition8
;

-- Q: If Condition2 and Condition5 are both FALSE, is it possible for the entire predicate to be TRUE? Why?
-- A: Yes: Conditions 1, 6, 7, and 8 must be TRUE.


-- Examine this reformatted version of the same WHERE predicate.
SELECT
	...
FROM
	...
WHERE
	(
		Condition1
		AND (
			(
				(NOT Condition2)
				AND (
					(Condition3 OR Condition4)
					AND Condition5
				)
			)
			OR Condition6
		)
	)
	AND Condition7
	AND Condition8
;

-- Q: Same question: if Condition2 and Condition5 are both FALSE, is it possible for the entire predicate to be TRUE? Why?
-- Q: Did reformatting make the logic easier or harder to interpret?



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  ⊱  CHALLENGE TASK  ⊰                                                                                                       │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/
  
/*	Write a query that returns data from the Products table subject to the following criteria:
	  • The following columns are returned: {ProductID, ProductName, CategoryID, Discontinued, UnitPrice}.
	  • The product is not discontinued.
	  • [CategoryID] is either 1 (Beverages) or 3 (Confections).
	  • [UnitPrice] is less than or equal to 15.
	Verify that your query returns 10 rows.
*/

-- A:
SELECT
	ProductID
	,ProductName
	,CategoryID
	,Discontinued
	,UnitPrice
FROM
	dbo.Products
WHERE
	Discontinued = 0
	AND (CategoryID = 1 OR CategoryID = 3)
	AND UnitPrice <= 15
;
```


# IN and BETWEEN

See **"DG_03 Filtering Rows 4—IN and BETWEEN v0.1.1.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_03 Filtering Rows 4 – IN and BETWEEN                                                                                    │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • Introduction: IN and BETWEEN                                                                                           │
  │    • IN                                                                                                                     │
  │    • BETWEEN                                                                                                                │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


USE Northwind;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Introduction: IN and BETWEEN                                                                                               │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- IN returns TRUE if the input value matches any value in a specified list.
-- BETWEEN returns TRUE if the input value is between the specified lower and upper bounds, inclusive.



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  IN                                                                                                                         │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- Execute the following query and verify that the reslts are filtered to [RegionID] = {1, 2, 3}.
SELECT
	*
FROM
	dbo.Territories
WHERE
	RegionID = 1
	OR RegionID = 2
	OR RegionID = 3
;


-- We can substitute a predicate consisting of multiple OR's with a single IN.
-- Execute the query below and verify that it returns the same results as the previous query.
SELECT
	*
FROM
	dbo.Territories
WHERE
	RegionID IN (1, 2, 3)
;


-- ⊱  TASK  ⊰
-- Complete the query below to return rows from the Suppliers table where the Country is 'Canada' or 'USA'. Use the IN operator.
-- Verify that your query returns 6 rows.
SELECT
	*
FROM
	dbo.Suppliers
WHERE
	...
;

-- A:
SELECT
	*
FROM
	dbo.Suppliers
WHERE
	Country IN ('Canada', 'USA')
;


-- ⊱  TASK  ⊰
-- Write a query that returns rows from the Suppliers table where the Country is "Germany", "Italy", "France", "Austria", "UK",
-- or "Denmark". Verify that your query returns 11 rows.
-- Note that it makes no difference if there are redundant elements in the IN list. For example, there is no "Austria" in the
-- [Country] column.

-- A:
SELECT
	*
FROM
	dbo.Suppliers
WHERE
	Country IN ('Germany', 'Italy', 'France', 'Austria', 'UK', 'Denmark')
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  BETWEEN                                                                                                                    │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- Recall the query that we wrote earlier to filter to a date range.
-- Execute this query and verify that it returns 6 rows.
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	(OrderDate >= '1998-05-01') AND (OrderDate <= '1998-05-04')
;


-- ⊱  TASK  ⊰
-- Here is a query that achieves the same date range filter using BETWEEN.
-- Execute this query and verify that it returns the same 6 rows.
-- Note the syntax: BETWEEN must always be used with AND: <value> BETWEEN <lower_bound> AND <upper_bound>.
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	OrderDate BETWEEN '1998-05-01' AND '1998-05-04'
;


-- ⊱  TASK  ⊰
-- Write a modified version of the previous query that filters to [OrderDate] between 1 Dec 1996 and 31 Dec 1996.
-- Verify that your query returns 31 rows.

-- A:
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	OrderDate BETWEEN '1996-12-01' AND '1996-12-31'
;


-- ⊱  TASK  ⊰
-- Write another version of the query that filters as follows:
--		([OrderDate] is between 1 Dec 1996 and 31 Dec 1996, inclusive)
--		AND (CustomerID is "HUNGC", "FRANK", "QUEEN", or "SAVEA")
-- Use the BETWEEN, AND, and IN operators.
-- Verify that your query returns 6 rows, consisting of the following [OrderID]s: {10372, 10375, 10393, 10394, 10396, 10398}.

-- A:
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	(OrderDate BETWEEN '1996-12-01' AND '1996-12-31')
	AND (CustomerID IN ('HUNGC', 'FRANK', 'QUEEN', 'SAVEA'))
;


-- ⊱  TASK  ⊰
-- BETWEEN also works on strings.
-- Verify that the query below returns 17 rows and contains only rows where [CustomerID] is alphabetically between "AA" and "AR".
SELECT
	OrderID
	,CustomerID
	,OrderDate
FROM
	dbo.Orders
WHERE
	CustomerID BETWEEN 'AA' AND 'AR'
;


-- Q: Try changing the lower and upper bounds to lowercase. Is BETWEEN case-sensitive?
-- A: No. (However, it is possible to add a COLLATE command to force case-sensitivity.)
```


# LIKE

See **"DG_03 Filtering Rows 5—LIKE v0.1.1.sql"**.

```sql=
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  DG_03 Filtering Rows 5 – LIKE                                                                                              │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Last updated: 18 Oct 2024            © QA Ltd.                                                                             │
  ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
  │  Content:                                                                                                                   │
  │    • Introduction: LIKE                                                                                                     │
  │    • `%` Wildcard                                                                                                           │
  │    • `_` Wildcard                                                                                                           │
  │    • Positive character sets: `[...]`                                                                                       │
  │    • Negative character sets: `[^...]`                                                                                      │
  │    • Character ranges `[...-...]`                                                                                           │
  │    • CHALLENGE TASK                                                                                                         │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/


USE Northwind;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Introduction: LIKE                                                                                                         │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- LIKE is a string comparison operator that allows for wildcard matching.
-- For example, the predicate `Title LIKE 'm%'` returns TRUE if [Title] begins with the letter "m".
-- LIKE is not case-sensitive by default.

/*	The LIKE operator supports the following wildcard characters:
	%		Matches any string of zero or more characters.
	_		Matches any single character.
	[]		Matches any single character listed within the square brackets.
	[^]		Matches any single character not listed with the square brackets.

	We will experiment with each of these wildcards.
*/


/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  `%` Wildcard                                                                                                               │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- `%` matches any string of zero or more characters.
-- For example, the predicate `ProductName LIKE '%iphone%'` returns TRUE if [ProductName] contains the string "iphone".


-- ⊱  TASK  ⊰
-- Execute the query below and verify that it returns 43 rows.
-- Notice that the query filters to rows where the string "sales" appears anywhere in [ContactTitle].
-- Also note that the wildcard match is not case-sensitive.
SELECT
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Phone
FROM
	dbo.Customers
WHERE
	ContactTitle LIKE '%sales%'
;


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [ContactTitle] *starts with* "sales".
-- Execute your query and verify that it returns 40 rows.
SELECT
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Phone
FROM
	dbo.Customers
WHERE
	ContactTitle LIKE 'sales%'
;


-- Recall that a predicate is an expression that returns TRUE or FALSE.
-- Q: What SQL logical operator converts TRUE to FALSE and FALSE to TRUE?
-- A: NOT.


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [ContactTitle] *does not start with* "sales".
-- Execute your query and verify that it returns 51 rows.
SELECT
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Phone
FROM
	dbo.Customers
WHERE
	ContactTitle NOT LIKE 'sales%'
	--NOT ContactTitle LIKE 'sales%' -- Equivalent.
;


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [ContactTitle] contains "sales" but does not start with "sales".
-- Execute your query and verify that it returns 3 rows.
SELECT
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Phone
FROM
	dbo.Customers
WHERE
	(ContactTitle LIKE '%sales%')
	AND (ContactTitle NOT LIKE 'sales%')
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  `_` Wildcard                                                                                                               │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- `_` matches any single character.
-- For example, the predicate `SupplierCode LIKE 'usa__'` ("usa" followed by 2 underscores) returns TRUE if [SupplierCode]
-- starts with "usa" and is exactly 5 characters in length.


-- ⊱  TASK  ⊰
-- Execute the query below and verify that it returns 3 rows.
-- The predicate `PostalCode LIKE '_____'` returns TRUE if [PostalCode] has exactly 6 characters.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	PostalCode LIKE '______' -- 6 underscores.
;


-- ⊱  TASK  ⊰
-- Complete the query below to filter to rows where [PostalCode] has exactly 5 characters and starts with "1".
-- Verify that your query returns 4 rows.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	PostalCode LIKE '1____' -- "1" followed by 5 underscores.
;


-- ⊱  TASK  ⊰
-- Complete the query below to filter to rows where the second letter of [ContactName] is "e".
-- [ContactName] can be any number of characters in length.
-- Verify that your query returns 12 rows.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	ContactName LIKE '_e%'
;


-- ⊱  TASK  ⊰
-- Create a modified version of the previous query that filters to rows that satisfy the following criteria:
--   • The second letter of [ContactName] is "e".
--   • [ContactName] does not contain the letter "a".
-- Verify that your query returns 5 rows.

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	(ContactName LIKE '_e%')
	AND (ContactName NOT LIKE '%a%')
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Positive character sets: `[...]`                                                                                           │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- `[...]` Matches any single character listed within the square brackets.
-- For example, the predicate `PaperSize LIKE 'A[123]'` returns TRUE if [Papersize] is equal to "A1", "A2", or "A3".
-- A positive character set can be combined with the wildcard characters `%` and `_`.


-- ⊱  TASK  ⊰
-- Execute the query below and verify that it returns 19 rows.
-- Notice that the query filters to rows where [PostalCode] starts with "2", "3", "5", or "7".
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	PostalCode LIKE '[2357]%'
;


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [ContactName] starts with a vowel.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	ContactName LIKE '[aeiou]%'
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Negative character sets: `[^...]`                                                                                           │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- `[^...]` Matches any single character not listed within the square brackets.
-- For example, the predicate `PaperSize LIKE 'A[^123]'` returns TRUE if [Papersize] is equal to "A" followed by any single
-- character except "1", "2", or "3".
-- Note that both positive and negative characters sets match exactly one character. Thus, `PaperSize LIKE 'A[^123]'` would *not*
-- match "A", since the pattern demands a letter "A" followed by exactly one character.


-- ⊱  TASK  ⊰
-- Execute the query below and verify that it returns 16 rows.
-- Notice that the query filters to rows where [Phone] does not start with "0" or "(".
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	Phone LIKE '[^0(]%'
;


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [Phone] does not start with a numeric digit 0-9.
-- Verify that your query returns 56 rows.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	Phone LIKE '[^0123456789]%'
;



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  Character ranges `[...-...]`                                                                                               │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/

-- A LIKE pattern can also accept one or more ranges of characters.
-- For example, the predicate `PostalCode LIKE '[0-9]%'` returns TRUE if [PostalCode] starts with any numeric digit 0-9.
-- In addition to numeric digits, a character range can include a wide variety of characters and symbols. For example, `'[a-z]'`.


-- ⊱  TASK  ⊰
-- Execute the query below and verify that it returns 76 rows.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	Phone LIKE '[0-9]%'
;


-- ⊱  TASK  ⊰
-- Execute the query below and verify that it returns 46 rows.
-- The LIKE pattern '[0-18-9]' part means "any single character between 0 and 1, or between 8 and 9, or 'a' or 'b' or 'c'".
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	PostalCode LIKE '[0-18-9abc]%'
;


-- ⊱  TASK  ⊰
-- LIKE ranges can also be negative.
-- Execute the query below and verify that it returns 46 rows.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	ContactName LIKE '[^a-ty]%'
;

-- Q: Describe what the LIKE predicate is doing in the query above.
-- A: Filters to rows where the first letter of [ContactName] is not between "a" and "t" inclusive and is not "y". (Case-
-- insensitive).


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [Phone] does not start with a numeric digit 0-9.
-- Verify that your query returns 56 rows.
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	Phone LIKE '[^0-9]%'
;


-- ⊱  TASK  ⊰
-- Complete the query below so that it filters to rows where [Phone] consists of numeric digits and spaces only.
-- This is a tricky question!
-- Verify that your query returns 3 rows, consisting of the following [ContactName]: "Patricia McKenna", "Jytte Petersen", and
-- "Palle Ibsen".
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	Phone NOT LIKE '%[^0-9 ]%'
	--Phone LIKE '%[0-9 ]%' AND Phone NOT LIKE '%[^0-9 ]%' -- Variant that also filters out empty [Phone] values.
;


-- The LIKE pattern we have just figured out is very useful for detecting invalid characters in a column.



/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
  │  ⊱  CHALLENGE TASK  ⊰                                                                                                       │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘*/
 
/*	Complete the query below so that it filters to rows where the following criteria are satisfied:
	  • [ContactName] does not end with a vowel.
	  • [Postalcode] does not start with "0" or "1".
	  • The third character of [Phone] is not a numeric digit 0-9.
	Verify that your query returns 13 rows.
*/
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	...
;

-- A:
SELECT
	CustomerID
	,ContactName
	,PostalCode
	,Phone
FROM
	dbo.Customers
WHERE
	ContactName LIKE '%[^aeiou]'
	AND Postalcode LIKE '[^01]%'
	AND Phone LIKE '__[^0-9]%'
;


```


# (AD-HOC NOTES BEYOND THIS POINT)

---



# ORDER BY
```sql=
SELECT
	ProductID
	,ProductName
	,CategoryID
	,UnitPrice
FROM
	dbo.Products
ORDER BY
	...
;
```


# TOP
```sql=
SELECT
	ProductName
	,UnitPrice
FROM
	dbo.Products
ORDER BY
	UnitPrice DESC
;
```


# Joins
```sql
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 "LeftTable" 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.
```sql=
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

```sql=
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
```sql=
--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
```sql=
SELECT
	CompanyName
	,ContactName
	,Phone
FROM
	dbo.Customers

UNION ALL -- 120 rows.

SELECT
	CompanyName
	,ContactName
	,Phone
FROM
	dbo.Suppliers
;
```

## UNION
```sql=
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
```sql=
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

```sql=
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
```sql=
-- Extract Wally.
-- Use SUBSTRING on: 'abcdefgwallyhijklmnopqrstuvwxyz'

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

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

# Views
```sql=
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
;
```