SQL Cheat Sheet – Dataquest

This cheat sheet provides a quick reference for common SQL operations and functions, adapted
to work with the Classic Models database structure. The examples use tables such as products,
orders, customers, employees, offices, orderdetails, productlines, and payments as shown in the
database diagram. This structure represents a model car business, so the examples have been
tailored to fit this context.

Table of Contents
Selection Queries Aggregate Functions
Select, Order By, Distinct Sum, Avg, ROUND, Min, Max,
Group By, HAVING, COUNT

String Functions Conditional Queries

Upper, Lower, Length, SUBSTR, Case, COALESCE, Cast
Concatenation

Combine Data Window Functions

Union, Union All, Except, Partition BY, Order By,
Intersect PARTITION BY AND ORDER BY

Ranking Functions Joins

DENSE_RANK, RANK, Row Number, Inner Join, Left Join, Right
NTile Join, Cross Join, JOIN
MULTIPLE, JOIN SELF

Subqueries SQLite and PostgreSQL

SELECT, FROM, WHERE, IN, SQLite-specific commands, 

EXISTS, Correlated Subquery, PostgreSQL-specific commands
=, CTE

Find more free resources at https://www.dataquest.io/guide/sql

 SQL Cheat Sheet – Dataquest
Selection Queries

Clause How to use Explained

Select SELECT * FROM products; Display all columns from `products` table.

SELECT productName, buyPrice

Display only `productName` and
FROM products; `buyPrice` columns from `products`
table.

Order By SELECT productName, buyPrice

Sort the selected columns by `buyPrice`
FROM products
in descending order.
ORDER BY buyPrice DESC;

SELECT productName, buyPrice
 Sort the selected columns by

FROM products
 `productName` in ascending order.
ORDER BY productName ASC;

SELECT orderNumber, customerNumber,

Sorts the data by `customerNumber` and
orderDate, status
 then by `orderDate` within each
FROM orders
 `customerNumber`.
ORDER BY customerNumber ASC, orderDate DESC;

Distinct SELECT DISTINCT productLine

Retrieve unique values from
FROM products; `productLine` in `products` table.

SELECT DISTINCT city, country

Retrieve unique combinations of `city`
FROM customers
and `country` where customers are
ORDER BY country, city; located, sorted by `country` and then
`city`.
 SQL Cheat Sheet – Dataquest
Aggregate Functions
Clause How to use Explained

SUM SELECT SUM(quantityOrdered * priceEach) 
 Calculates the total sales from the
AS total_sales
`orderdetails` table.
FROM orderdetails;

AVG SELECT AVG(buyPrice) AS average_price

Averages the `buyPrice` values in
FROM products; `products`.

ROUND SELECT ROUND(AVG(buyPrice), 2)

Rounds the average of `buyPrice` to two
AS average_price
decimal places.
FROM products;

MIN SELECT MIN(buyPrice) AS lowest_price
 Finds the minimum value in the

FROM products; `buyPrice` column of `products`.

MAX SELECT MAX(buyPrice) AS highest_price

Finds the maximum value in the
FROM products; `buyPrice` column of `products`.

COUNT SELECT COUNT(*) AS total_orders

Counts the total number of rows in orders.

FROM orders;

Note COUNT(*) includes all rows, while COUNT(column_name) excludes NULL values in the specified column.

GROUP BY SELECT productLine, AVG(buyPrice) 
 Groups rows by `productLine` and

AS avg_price
calculates the average price for each
FROM products
product line.
GROUP BY productLine;

SELECT productLine, AVG(buyPrice)

Groups rows by `productLine` for
AS avg_price
products with price over 100 and
FROM products
calculates the average price for each
WHERE buyPrice > 100
product line.
GROUP BY productLine;

SELECT customerNumber, COUNT(orderNumber)

Groups orders by `customerNumber`,
AS order_count
counts the number of orders for each
FROM orders
customer in 2023, and sorts the results by
WHERE orderDate >= '2023-01-01'
the order count in descending order. This
GROUP BY customerNumber
shows which customers placed the most
ORDER BY order_count DESC;
orders in 2023.

HAVING SELECT productLine, AVG(buyPrice)

Filters product lines to only include those
AS avg_price
with average price greater than 50.
FROM products

GROUP BY productLine

HAVING AVG(buyPrice) > 50;

COUNT SELECT COUNT(*) AS total_products

Counts the total number of rows in the
FROM products; `products` table, returning the total
number of products. This includes all rows,
regardless of NULL values in any columns.

SELECT COUNT(reportsTo)
Counts the number of non-null values in
AS employees_with_manager
 the `reportsTo` column of the
FROM employees; `employees` table, showing how many
employees have a manager assigned.
COUNT ignores NULL values, so employees
without a manager (e.g., the president) are
not included in this count.
 SQL Cheat Sheet – Dataquest

String Functions

Clause How to use Explained

upper SELECT UPPER(productName)

Converts the `productName` column
AS uppercase_name
values to uppercase.
FROM products;

lower SELECT LOWER(productName)

Converts the `productName` column
AS lowercase_name
values to lowercase.
FROM products;

length SELECT productName, LENGTH(productName)

Calculates the length of each value in the
AS name_length
`productName` column.
FROM products;

SUBSTR SELECT SUBSTR(productLine, 1, 3)

Extracts the first three characters from the
AS product_category, productLine
`productLine` column. `SUBSTR` extracts
FROM products; a substring from a given string. It can be
used to extract characters from the
beginning, end, or any position within the
string.

SELECT SUBSTR(productCode, -4)

Extracts the last four characters from the
AS product_id, productCode
`productCode` column.
FROM products;

Concat
SELECT firstName || ' ' || lastName
Concatenates `firstName` and
(using ||) AS full_name
`lastName` with a space in between.
FROM employees;

SELECT firstName || '.' || lastName || Creates an email address by concatenating

'@classicmodelcars.com' AS email_address
 first name, last name, and domain.
FROM employees;
 SQL Cheat Sheet – Dataquest
Conditional Queries
Clause How to use Explained

Case SELECT productName,

Categorizes the `buyPrice` values into
buyPrice,
'Budget', 'Mid-range', and 'Premium'
CASE
categories.
WHEN buyPrice < 50 THEN

'Budget'

WHEN buyPrice BETWEEN 50

AND 100 THEN 'Mid-range'

ELSE 'Premium'

END AS price_category

FROM products;

SELECT orderNumber,
Categorizes orders into different sale
orderDate,
seasons based on the order date.
CASE

WHEN CAST(strftime('%m',

orderDate) AS INTEGER)

BETWEEN 3 AND 5 THEN

'Spring Sale'



WHEN CAST(strftime('%m',

orderDate) AS INTEGER)

BETWEEN 6 AND 8 THEN

'Summer Sale'

WHEN CAST(strftime('%m',

orderDate) AS INTEGER)

BETWEEN 9 AND 11 THEN

'Fall Sale'

ELSE 'Winter Sale'

END AS sale_season

FROM orders;

Coalesce SELECT productName,

Returns 'No description available' if
COALESCE(productDescription,
productDescription is null.
'No description available')

AS product_description

FROM products;

SELECT employeeNumber,
Returns the first non-null value among
firstName,
extension, email, or 'No contact
lastName,
information'.
COALESCE(extension, email, 'No
contact information') AS contact_info

FROM employees;

Cast SELECT orderNumber, CAST(orderDate AS DATE)

Converts the `orderDate` to DATE type.
AS order_day

FROM orders;
 SQL Cheat Sheet – Dataquest
Combine Data
Clause How to Explained
use
union SELECT productName
Combines the product names from ‘Classic
FROM products
Cars’ and ‘Vintage Cars’ product lines,
WHERE productLine = 'Classic Cars'
removing duplicates.
UNION

SELECT productName

FROM products

WHERE productLine = 'Vintage Cars';

union all SELECT productName

Combines the product names from ‘Classic
FROM products
Cars’ and ‘Vintage Cars’ product lines
WHERE productLine = 'Classic Cars'
without removing duplicates.
UNION ALL

SELECT productName

FROM products

WHERE productLine = 'Vintage Cars';

except SELECT productCode, productName

Returns products EXCEPT the ‘Classic Cars’
FROM products
product line, demonstrating how EXCEPT
EXCEPT
removes rows from the first result that
SELECT productCode, productName
appear in the second result.
FROM products

WHERE productLine = 'Classic Cars';

intersect SELECT customerNumber, customerName

Returns customers who are both located in
FROM customers
the USA AND have a credit limit over
WHERE country = 'USA'
100,000. This query demonstrates how
INTERSECT
INTERSECT finds common rows between
SELECT customerNumber, customerName

two result sets.

FROM customers

WHERE creditLimit > 100000;

Note EXCEPT and INTERSECT are not supported in all SQL databases. These examples use PostgreSQL syntax.
 SQL Cheat Sheet – Dataquest
Window Functions
Note SQLite does not support window functions natively. The following examples use PostgreSQL syntax and require PostgreSQL or
a SQLite extension.

Clause How to use Explained

PARTITION
SELECT employeeNumber,
Calculates the average extension length
BY officeCode,
within each office. The PARTITION BY
extension,
clause divides the data into partitions
AVG(LENGTH(extension)) OVER (
based on the officeCode column.
PARTITION BY officeCode

) AS avg_extension_length

FROM employees;

ORDER BY SELECT employeeNumber, 
 Calculates a running total of extension

officeCode,
 lengths ordered by length in descending
extension,
 order.
SUM(LENGTH(extension)) OVER (

ORDER BY LENGTH(extension) DESC

) AS running_total_length

FROM employees;

PARTITION
SELECT employeeNumber, 
 Calculates a running total of extension
BY
officeCode,
 lengths within each office, ordered by
AND
extension,
 length.
ORDER BY SUM(LENGTH(extension)) OVER (

PARTITION BY officeCode

ORDER BY LENGTH(extension) DESC

) AS running_total_length

FROM employees;
 SQL Cheat Sheet – Dataquest
Ranking Functions
Note SQLite does not support ranking functions natively. The following examples use PostgreSQL syntax and require PostgreSQL or a
SQLite extension.

Clause How to use Explained

Dense
SELECT productCode,
select emp_id, sal_amount,

sal_date,
 Ranks products based on buyPrice in
Rank dense_rank()
productName,
over (

descending order. Differs from RANK by
orderbuyPrice,

by month_salary
 handling ties differently (no gaps in

) as rank

DENSE_RANK() OVER (
ranking).
from salaryORDER BY buyPrice DESC

) AS price_rank

FROM products;

RANK SELECT employeeNumber,

select emp_id, sal_amount,
sal_date,
 Ranks employees within each office based
dense_rank()
officeCode,
over (

on their extension length. Differs from
orderexbytension,

month_salary
 DENSE_RANK by leaving gaps in ranking

) as rank

RANK() OVER (
when there are ties.
from salaryPARTITION BY officeCode

ORDER BY LENGTH(extension) DESC

) AS extension_rank_in_office

FROM employees;

ROW
SELECT orderNumber,
select emp_id, sal_amount,

sal_date,
 Assigns a unique row number to each
NUMBER dense_rank()
orderDate,

over (
 order based on orderDate and

ordercustomerNumber,

by month_salary
 customerNumber.
) as rank

ROW_NUMBER() OVER (

from salaryORDER BY orderDate,

customerNumber

) AS order_number

FROM orders;

ROW
SELECT orderNumber,
select emp_id, sal_amount,

sal_date,
 Assigns a unique row number to each
NUMBER dense_rank()
orderDate,

over (
 order based on orderDate and

ordercustomerNumber,

by month_salary
 customerNumber.
) as rank

ROW_NUMBER() OVER (

from salaryORDER BY orderDate,

customerNumber

) AS order_number

FROM orders;
 SQL Cheat Sheet – Dataquest
Joins
Clause How to use Explained

INNER
SELECT o.orderNumber, 
 Joins orders and customers tables,
JOIN o.orderDate, 
 returning only matching rows. This is the
c.customerName
 default join type when JOIN is used
FROM orders o
 without specifying LEFT, RIGHT, or FULL.
INNER JOIN customers c ON
o.customerNumber = c.customerNumber;

LEFT
SELECT p.productCode,
Joins products and orderdetails tables,
JOIN p.productName,
returning all products and their orders (if
od.orderNumber
any).
FROM products p

LEFT JOIN orderdetails od ON

p.productCode = od.productCode;

RIGHT
SELECT e.employeeNumber,
Joins offices and employees tables,
JOIN e.lastName,
returning all employees and their offices (if
o.officeCode
any).
FROM offices o

RIGHT JOIN employees e ON

o.officeCode = e.officeCode;

CROSS
SELECT p.productName,
Returns all possible combinations of
JOIN pl.textDescription
products and product line descriptions.
FROM products p

CROSS JOIN productlines pl;

join
SELECT o.orderNumber,
Joins four tables: orders, customers,
multiple c.customerName,
orderdetails, and products.
p.productName

FROM orders o

JOIN customers c ON o.customerNumber =

c.customerNumber

JOIN orderdetails od ON o.orderNumber =

od.orderNumber

JOIN products p ON od.productCode =

p.productCode;

JOIN SELF SELECT e1.firstName || ' ' || e1.lastName

Self-join example listing employees and
AS employee, e2.firstName || ' ' ||
their respective managers.
e2.lastName AS manager

FROM employees e1

LEFT JOIN employees e2

ON e1.reportsTo =

e2.employeeNumber;
 SQL Cheat Sheet – Dataquest
Subqueries
Clause How to use Explained

Subquery
SELECT productName,
Includes a subquery that calculates the
in SELECT buyPrice,
average price for all products.
(SELECT AVG(buyPrice) FROM

products) AS avg_price

FROM products;

Subquery
SELECT productLine,
Finds product lines with an average price
in FROM avg_price
greater than 100 using a subquery.
FROM (SELECT productLine,

AVG(buyPrice) AS avg_price

FROM products

GROUP BY productLine)

AS line_averages

WHERE avg_price > 100;

Subquery
SELECT productName,
This query selects products that are more
in WHERE buyPrice
expensive than the average price in their
FROM products p1
respective product line, ordered by
WHERE p1.buyPrice > (
product line and price in descending order.
SELECT AVG(p2.buyPrice)

FROM products p2

WHERE p1.productLine =

p2.productLine)

ORDER BY productLine,

buyPrice DESC;

Subquery
SELECT productName,
Finds products that were ordered in order
with IN buyPrice
10100.
FROM products

WHERE productCode IN (

SELECT productCode

FROM orderdetails

WHERE orderNumber = 10100

);

Subquery
SELECT customerName
 Finds products that are more expensive
with
FROM customers c
 than the average price in their product
EXISTS WHERE EXISTS (
 line.
SELECT 1

FROM orders o

WHERE o.customerNumber

= c.customerNumber

AND o.orderDate >= '2023-01-01'

);

= SELECT orderNumber, 
 This query selects all orders for a specific

orderDate, 
 customer named ‘Mini Gifts Distributors
totalAmount
 Ltd.’, ordered by date from most recent to
FROM orders
 oldest.
WHERE customerNumber = (

SELECT customerNumber

FROM customers

WHERE customerName = 'Mini Gifts

Distributors Ltd.'

)

ORDER BY orderDate DESC;

CTE WITH order_totals AS (

This query calculates the total amount for
SELECT orderNumber,
each order using a CTE and then joins the
SUM(quantityOrdered * priceEach)
orders table with the CTE to display order
AS total_amount
details with total amounts, ordered by
FROM orderdetails

total amount in descending order.

GROUP BY orderNumber

SELECT o.orderNumber,

o.orderDate,

ot.total_amount

FROM orders o

JOIN order_totals ot

ON o.orderNumber = ot.orderNumber

ORDER BY ot.total_amount DESC;

 SQL Cheat Sheet – Dataquest
SQLite and PostgreSQL
SQLite Commands

.tables Lists all tables in the current database.

.schema table_name Shows the schema for the specified table.

.mode column
Sets output to column mode with headers
.headers on for better readability.

.open filename Opens a new or existing database file.

.save filename Saves the current database to a file.

.quit Exits the SQLite prompt.

PostgreSQL Commands

\l Lists all databases.

\c database_name Connects to a specific database.

\dt Lists all tables in the current database.

\d table_name Describes the specified table.

\du Lists all roles/users.

\timing Toggles display of query execution time.

\e Opens the last command in an editor.

\i filename Executes commands from a file.

\q Exits the PostgreSQL interactive terminal.

Note SQLite doesn’t have a built-in user management system like PostgreSQL, so commands related to user management are not
applicable.