<p style="font-size: 40px; text-align: center;">Fundamentals of SQL</p>
<p style="font-size: 24px; text-align: center;">Exercise Guide 1</p>
<p style="font-size: 16px; text-align: center;">ASDXDDL8M2</p>
<p style="font-size: 16px; text-align: center;">2025-01-27</p>
---
# Testing, Testing
```sql
SELECT 'Hello World';
```
Can you see this text?
Send a 😬!
# Dropbox and Google Drive
Dropbox
http://tiny.cc/db20250127
Google Drive
http://tiny.cc/gd20250127
HackMD (this document)
http://tiny.cc/hmd120250127
# Clipboard
Miscellaneous SQL snippets will be shared here.
```sql=
-- Select random 100 rows.
SELECT TOP 100 * FROM <MyTable> ORDER BY NEWID();
```
```sql=
-- Practice with joins.
--————————————————————————————————————————————————————
-- The following command might fail. If so, try the fix below.
DROP DATABASE IF EXISTS JoinPractice;
-- If the above command fails with 'Cannot drop database "JoinPractice" because it is currently in use', try these commands:
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = 'JoinPractice'
GO
USE master;
GO
ALTER DATABASE JoinPractice SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master
GO
DROP DATABASE IF EXISTS JoinPractice;
GO
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
CREATE DATABASE JoinPractice;
USE JoinPractice;
GO
--DROP TABLE LeftTable;
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)
;
SELECT * FROM LeftTable;
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
DROP TABLE IF EXISTS RightTable;
GO
CREATE TABLE RightTable (
Letter char(1)
,Colour varchar(100)
);
GO
-- Populate the table.
INSERT INTO
RightTable
(
Letter
,Colour
)
VALUES
('A', 'Red')
,('A', 'Green')
,('B', 'Blue')
,('B', 'Black')
,('C', NULL)
,('D', 'Purple')
;
GO
SELECT * FROM LeftTable;
SELECT * FROM RightTable;
```
## Handy query from Jim
Returns the row count of all tables in the database instantly!
```sql=
SELECT
SCHEMA_NAME(schema_id) AS [SchemaName],
t.name AS [TableName],
p.rows AS [RowCount]
FROM
sys.tables AS t
INNER JOIN
sys.partitions AS p ON t.object_id = p.object_id
WHERE
p.index_id IN (0, 1) -- 0: Heap, 1: Clustered
GROUP BY
SCHEMA_NAME(schema_id), t.name, p.rows
ORDER BY
[SchemaName], [TableName]
```
# Code Comments
Here are some use cases for adding **comments** (non-executable text notes) to our SQL code:
* Adding context for fields/columns.
* Headings.
* Documentation/explaining how you've approached a problem.
* To prevent execution of queries.
* To clarify/explain to others.
* Step-by-step instructions/explanations.
* Goals.
* What server to run on.
* Reminders.
* TODOs and fixes.
* Courtesy to others--so they can e.g. pick up your work and understand it.
* Purpose--put this near the top.
* Sections/headers.
* Hightlight issues--efficiency, bugs, inaccuracies.
* When to run (if on a schedule).
* In larger scripts, consider:
- Ctrl + F codes, e.g. #INTRO, #PRODUCTINFO.
- TOC.
## 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:
-- ⊱ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
-- 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:
-- ⊱ 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.75`.
SELECT
UnitPrice
,UnitPrice * 0.9
,UnitPrice * 0.75
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:
-- 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: [Discount50Pcnt] and [Discount85Pcnt].
-- A:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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.
-- Tip: first, examine the Products table.
SELECT
ProductID
,ProductName
,UnitPrice
,UnitsInStock
,UnitsOnOrder
,... AS CurrentStockValue
,... AS FutureStockValue
FROM
dbo.Products
;
-- A:
-- 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ ⊱ 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:
```
# 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:
-- ⊱ 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:
-- ⊱ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
-- ⊱ 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:
/* 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.
*/
-- A: a) b)
-- Q2: What command sets the database context to the NORTHWIND database?
-- A:
-- Q3: Write a query that retrieves all data from Employees. Use a
-- two-part name.
-- A:
-- Q4: Write another version of the same query, this time using
-- a three-part name.
-- A:
-- Q5: Write another version of (Q3), this time filter to rows where
-- city is London.
-- A:
-- Q6: Write another version of (Q3), this time filter to rows where
-- the person's first name is later than "Pigeon" in the alphabet.
-- A:
-- Q7: Create a copy of (Q6) and modify to only retrieve the following columns:
-- Employee ID, first name, last name, title.
-- A:
-- Q8: Write a query to select all data from the table that contains
-- order details.
-- A:
-- Q9: Select all columns except OrderID from [Order Details], filtering
-- to rows where UnitPrice is between 10 and 20, inclusive.
-- A:
-- Q10: Create another version of (Q9), this time changing the WHERE
-- clause so that it filters to rows where:
-- (Quantity is greater than or equal to 35)
-- OR
-- (Quantity < 20 AND Discount is not zero)
-- A:
```
# 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
-- ⊱ 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:
```
# 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:
-- ⊱ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
-- ⊱ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
-- 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:
```
# 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:
-- ⊱ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
-- ⊱ 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:
-- ⊱ 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:
```
# 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:
-- 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:
-- ⊱ 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:
-- ⊱ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ `_` 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:
-- ⊱ 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:
-- ⊱ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
/*╒═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ 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:
-- ⊱ 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:
-- ⊱ 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:
-- 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:
```
# Joins
```sql=
USE Northwind;
--USE <your_database_name_here>; -- Any database will do.
-- 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')
;
-- Q: Inspect both tables.
SELECT * FROM LeftTable;
SELECT * FROM RightTable;
-- 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
-- Join predicate.
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;
```
## Customers and Values
```sql=
-- Create a table called "LeftTable" in the current database.
DROP TABLE IF EXISTS Customer;
--DROP TABLE Northwind.dbo.LeftTable;
GO
CREATE TABLE Customer (
OrderID int
,Customer varchar(100)
);
GO
-- Populate the table.
INSERT INTO
Customer
(
OrderID
,Customer
)
VALUES
(1000, 'Alice' )
,(2000, 'Bob' )
,(2000, 'Cathy' )
,(NULL, 'Diego')
,(3000, 'Emily' )
;
GO
SELECT * FROM Customer;
DROP TABLE IF EXISTS [Value];
--DROP TABLE Northwind.dbo.LeftTable;
GO
CREATE TABLE [Value] (
OrderNum int
,TotalValue decimal(9, 2) -- Or money.
);
GO
-- Populate the table.
INSERT INTO
[Value]
(
OrderNum
,TotalValue
)
VALUES
(1000 , 31.14 )
,(1000 , 15.92 )
,(2000 , 6.53 )
,(4000 , 58.97 )
,(NULL , 9.32 )
;
GO
SELECT * FROM [Value];
```
# CASE
```sql=
SELECT
ProductID
,UnitPrice
,Discount
,CASE
--WHEN Discount > 1 THEN 'Too big discount'
WHEN Discount >= 0.25 THEN 'Big discount'
WHEN Discount >= 0.10 THEN 'Small discount'
WHEN Discount > 0 THEN 'Tiny discount'
WHEN Discount = 0 THEN 'None'
ELSE 'ERROR'
END
FROM
dbo.[Order Details]
;
```
## Using CASE to test code snippets
```sql=
SELECT (CASE WHEN 'abc' LIKE '__c' THEN 'Correct' ELSE 'Wrong' END) AS LikeTest;
```
# ISNULL() and COALESCE()
```sql=
SELECT
--FirstName + ' ' + LastName AS FullName
CONCAT(FirstName, LastName) AS FullName
,City
,Region AS [State]
,Country
,(CASE WHEN Region IS NULL THEN Country ELSE Region END) AS StateOrCountry1
,ISNULL(Region, Country) AS StateOrCountry2
,COALESCE(Region, Country) AS StateOrCountry3
FROM
dbo.Employees
;
-- Like IFERROR() in Excel.
SELECT ISNULL('abc', 'def'); -- "abc".
SELECT ISNULL(NULL, 'def'); -- "def".
-- Returns the first non-NULL parameter.
SELECT COALESCE('abc', 'def', 'ghi'); -- "abc".
SELECT COALESCE(NULL, 'def', 'ghi'); -- "def".
SELECT COALESCE('abc', NULL, NULL); -- "abc".
SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, 1); -- 1.
```
Google Drive
Gist
Clipboard
Sign in with Wallet
