SQL Cheat Sheet
This SQL cheat sheet—part of our Complete Guide to SQL—provides a quick reference for common SQL operations and functions, adapted to work with the Classic Models database.
The SQL query examples provided in the cheat sheet use the table names: productlines
, products
, orderdetails
, employees
, customers
, orders
, offices
, and payments
as shown in the database diagram at the bottom of this page. This structure represents a model car business, so the examples have been tailored to fit this context.
Table of Contents
Select, Order By, Distinct
Sum, Avg, ROUND, Min, Max, Group By, HAVING, COUNT
Upper, Lower, Length, SUBSTR, Concatenation
Case, COALESCE, Cast
Union, Union All, Except, Intersect
Partition BY, Order By, PARTITION BY AND ORDER BY
DENSE_RANK, RANK, Row Number, NTile
Inner Join, Left Join, Right Join, Cross Join, JOIN MULTIPLE, JOIN SELF
SELECT, FROM, WHERE, IN, EXISTS, Correlated Subquery, =, CTE
SQLite-specific commands, PostgreSQL-specific commands
Selection Queries
Clause
How to use
Explained
Select
SELECT *
FROM products;
Display all columns from products
table.
SELECT productName,
buyPrice
FROM products;
Display only productName
and buyPrice
columns from products
table.
Order By
SELECT productName,
buyPrice
FROM products
ORDER BY buyPrice DESC;
Sort the selected columns by buyPrice
in descending order.
SELECT productName,
buyPrice
FROM products
ORDER BY productName ASC;
Sort the selected columns by productName
in ascending order.
SELECT orderNumber,
customerNumber,
orderDate
FROM orders
ORDER BY customerNumber ASC, orderDate DESC;
Sorts the data by customerNumber
and then by orderDate
within each customerNumber
.
Distinct
SELECT DISTINCT productLine
FROM products;
Retrieve unique values from productLine
in products
table.
SELECT country,
DISTINCT city
FROM customers
ORDER BY country, city;
Retrieve unique combinations of city
and country
where customers are located, sorted by country
and then city
.
Aggregate Functions
Clause
How to use
Explained
SUM
SELECT SUM(quantityOrdered * priceEach) AS total_sales
FROM orderdetails;
Calculates the total sales from the orderdetails
table.
AVG
SELECT AVG(buyPrice) AS average_price
FROM products;
Averages the buyPrice
values in products
.
ROUND
SELECT ROUND(AVG(buyPrice), 2) AS average_price
FROM products;
Rounds the average of buyPrice
to two decimal places.
MIN
SELECT MIN(buyPrice) AS lowest_price
FROM products;
Finds the minimum value in the buyPrice
column of products
.
MAX
SELECT MAX(buyPrice) AS highest_price
FROM products;
Rounds the average of buyPrice
to two decimal places.
COUNT
SELECT COUNT(*) AS total_orders
FROM orders;
Counts the total number of rows in orders
.
GROUP BY
SELECT productLine,
AVG(buyPrice) AS avg_price
FROM products
GROUP BY productLine;
Groups rows by productLine
and calculates the average price for each product line.
SELECT productLine,
AVG(buyPrice) AS avg_price
FROM products
WHERE buyPrice > 100
GROUP BY productLine;
Groups rows by productLine
for products with price over 100 and calculates the average price for each product line.
SELECT customerNumber,
COUNT(orderNumber) AS order_count
FROM orders
WHERE orderDate >= '2023-01-01'
GROUP BY customerNumber
ORDER BY order_count DESC;
Groups orders by customerNumber
, counts the number of orders for each customer in 2023 and beyond, and sorts the results by the order count in descending order. This shows which customers placed the most orders in 2023.
HAVING
SELECT productLine,
AVG(buyPrice) AS avg_price
FROM products
GROUP BY productLine
HAVING AVG(buyPrice) > 50;
Filters product lines to only include those with average price greater than 50.
COUNT
SELECT COUNT(*) AS total_products
FROM products;
Counts the total number of rows in the products
table, returning the total number of products. This includes all rows, regardless of NULL
values in any columns.
SELECT COUNT(reportsTo) AS employees_with_manager
FROM employees;
Counts the number of non-null values in the reportsTo
column of the 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.
COUNT(*)
includes all rows, while COUNT(column_name)
excludes NULL
values in the specified column.
String Functions
Clause
How to use
Explained
UPPER
SELECT UPPER(productName) AS uppercase_name
FROM products;
Converts the productName
column values to uppercase.
LOWER
SELECT LOWER(productName) AS lowercase_name
FROM products;
Converts the productName
column values to lowercase.
LENGTH
SELECT productName,
LENGTH(productName) AS name_length
FROM products;
Calculates the length of each value in the productName
column.
SUBSTR
SELECT productLine,
SUBSTR(productLine, 1, 3) AS product_category
FROM products;
Extracts the first three characters from the productLine
column. SUBSTR
extracts a substring from a given string. It can be used to extract characters from the beginning, end, or any position within the string.
SELECT productCode,
SUBSTR(productCode, -4) AS product_id
FROM products;
Extracts the last four characters from the productCode
column.
Concat (using ||)
SELECT firstName || ' ' || lastName AS full_name
FROM employees;
Concatenates firstName
and lastName
with a space in between.
SELECT firstName || '.' || lastName || '@modelcars.com'
AS email_address
FROM employees;
Creates an email address by concatenating first name, last name, and a domain.
Conditional Queries
Clause
How to use
Explained
CASE
SELECT productName,
buyPrice,
CASE
WHEN buyPrice < 50 THEN 'Budget'
WHEN buyPrice BETWEEN 50 AND 100 THEN 'Mid'
ELSE 'Premium'
END AS price_category
FROM products;
Categorizes the buyPrice
values into 'Budget', 'Mid', and 'Premium' categories.
SELECT orderNumber,
orderDate,
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;
Categorizes orders into different sale seasons based on the order date.
Coalesce
SELECT productName,
COALESCE(productDescription,
'No description available'
) AS product_description
FROM products;
Returns 'No description available' if productDescription
is NULL
.
SELECT employeeNumber,
firstName,
lastName,
COALESCE(extension,
email,
'No contact information'
) AS contact_info
FROM employees;
Returns the first non-null value among extension
, email
, or 'No contact information'.
Cast
SELECT orderNumber,
CAST(orderDate AS DATE) AS order_day
FROM orders;
Converts the orderDate
to DATE type.
Combine Data
Clause
How to use
Explained
Union
SELECT productName
FROM products
WHERE productLine = 'Classic Cars'
UNION
SELECT productName
FROM products
WHERE productLine = 'Vintage Cars';
Combines the product names from Classic Cars
and Vintage Cars
product lines, removing duplicates.
Union All
SELECT productName
FROM products
WHERE productLine = 'Classic Cars'
UNION ALL
SELECT productName
FROM products
WHERE productLine = 'Vintage Cars';
Combines the product names from Classic Cars
and Vintage Cars
product lines without removing duplicates.
Except
SELECT productCode, productName
FROM products
EXCEPT
SELECT productCode, productName
FROM products
WHERE productLine = 'Classic Cars';
Returns products EXCEPT the Classic Cars
product line, demonstrating how EXCEPT
removes rows from the first result that appear in the second result.
Intersect
SELECT customerNumber, customerName
FROM customers
WHERE country = 'USA'
INTERSECT
SELECT customerNumber, customerName
FROM customers
WHERE creditLimit > 100000;
Returns customers who are both located in the USA AND have a credit limit over 100,000. This query demonstrates how INTERSECT
finds common rows between two result sets.
EXCEPT
and INTERSECT
are not supported in all SQL databases. These examples use PostgreSQL syntax.
Window Functions
Clause
How to use
Explained
PARTITION BY
SELECT employeeNumber,
officeCode,
extension,
AVG(LENGTH(extension)) OVER (
PARTITION BY officeCode
) AS avg_extension_length
FROM employees;
Calculates the average extension
length within each office. The PARTITION BY
clause divides the data into partitions based on the officeCode
column.
Order By
SELECT employeeNumber,
officeCode,
extension,
SUM(LENGTH(extension)) OVER (
ORDER BY LENGTH(extension) DESC
) AS running_total_length
FROM employees;
Calculates a running total of extension
lengths ordered by length in descending order.
PARTITION BY AND ORDER BY
SELECT employeeNumber,
officeCode,
extension,
SUM(LENGTH(extension)) OVER (
PARTITION BY officeCode
ORDER BY LENGTH(extension) DESC
) AS running_total_length
FROM employees;
Calculates a running total of extension
lengths within each office, ordered by length.
Ranking Functions
Clause
How to use
Explained
Dense Rank
SELECT productCode,
productName,
buyPrice,
DENSE_RANK() OVER (
ORDER BY buyPrice DESC
) AS price_rank
FROM products;
Ranks products based on buyPrice
in descending order. Differs from RANK
by handling ties differently (no gaps in ranking).
RANK
SELECT employeeNumber,
officeCode,
extension,
RANK() OVER (
PARTITION BY officeCode
ORDER BY LENGTH(extension) DESC
) AS extension_rank_in_office
FROM employees;
Ranks employees within each office based on their extension
length. Differs from DENSE_RANK
by leaving gaps in ranking when there are ties.
ROW NUMBER
SELECT orderNumber,
orderDate,
customerNumber,
ROW_NUMBER() OVER (
ORDER BY orderDate, customerNumber
) AS order_number
FROM orders;
Assigns a unique row number to each order based on orderDate
and customerNumber
.
Joins
Clause
How to use
Explained
INNER JOIN
SELECT o.orderNumber,
o.orderDate,
c.customerName
FROM orders AS o
INNER JOIN customers AS c
ON o.customerNumber = c.customerNumber;
Joins orders
and customers tables
, returning only matching rows. This is the default join type when JOIN
is used without specifying LEFT
, RIGHT
, or FULL
.
LEFT JOIN
SELECT p.productCode,
p.productName,
od.orderNumber
FROM products AS p
LEFT JOIN orderdetails AS od
ON p.productCode = od.productCode;
Joins products
and orderdetails
tables, returning all products and their orders (if any).
RIGHT JOIN
SELECT e.employeeNumber,
e.lastName,
o.officeCode
FROM offices AS o
RIGHT JOIN employees AS e
ON o.officeCode = e.officeCode;
Joins offices
and employees
tables, returning all employees and their offices (if any).
CROSS JOIN
SELECT p.productName,
pl.textDescription
FROM products AS p
CROSS JOIN productlines AS pl;
Returns all possible combinations of products and product line descriptions.
join multiple
SELECT o.orderNumber,
c.customerName,
p.productName
FROM orders AS o
JOIN customers AS c
ON o.customerNumber = c.customerNumber
JOIN orderdetails AS od
ON o.orderNumber = od.orderNumber
JOIN products p
ON od.productCode = p.productCode;
Joins four tables: orders
, customers
, orderdetails
, and products
.
SELF JOIN
SELECT e1.firstName || ' ' || e1.lastName AS employee,
e2.firstName || ' ' || e2.lastName AS manager
FROM employees AS e1
LEFT JOIN employees AS e2
ON e1.reportsTo = e2.employeeNumber;
Self-join example listing employees and their respective managers.
Subqueries
Clause
How to use
Explained
Subquery in SELECT
SELECT productName,
buyPrice,
(SELECT AVG(buyPrice)
FROM products) AS avg_price
FROM products;
Includes a subquery that calculates the average price for all products.
Subquery in FROM
SELECT productLine,
avg_price
FROM (
SELECT productLine,
AVG(buyPrice) AS avg_price
FROM products
GROUP BY productLine
) AS line_averages
WHERE avg_price < 100;
Finds product lines with an average price greater than 100 using a subquery.
Subquery in WHERE
SELECT productName,
buyPrice
FROM products p1
WHERE p1.buyPrice < (
SELECT AVG(p2.buyPrice)
FROM products p2
WHERE p1.productLine = p2.productLine
)
ORDER BY productLine,
buyPrice DESC;
This query selects products that are more expensive than the average price in their respective product line, ordered by product line and price in descending order.
Subquery with EXISTS
SELECT customerName
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customerNumber = c.customerNumber
AND o.orderDate >= '2023-01-01'
);
Finds products that are more expensive than the average price in their product line.
=
SELECT orderNumber,
orderDate,
totalAmount
FROM orders
WHERE customerNumber = (
SELECT customerNumber
FROM customers
WHERE customerName = 'Petit Auto'
)
ORDER BY orderDate DESC;
This query selects all orders for a specific customer named ‘Petit Auto’, ordered by date from most recent to oldest.
CTE
WITH order_totals AS (
SELECT orderNumber,
SUM(quantityOrdered * priceEach
) AS total_amount
FROM orderdetails
GROUP BY orderNumber
)
SELECT o.orderNumber,
o.orderDate,
ot.total_amount
FROM orders AS o
JOIN order_totals AS ot
ON o.orderNumber = ot.orderNumber
ORDER BY ot.total_amount DESC;
This query calculates the total amount for each order using a common table expression CTE and then joins the orders
table with the CTE to display order details with total amounts, ordered by total amount in descending order.
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
.headers on
Sets output to column mode with headers 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.