<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
;
```
Google Drive
Gist
Clipboard
Sign in with Wallet
