SQL Interview Cheat Sheet

The cheat sheet provides customers and orders sample tables, including IDs, names, ages, cities, countries, subscription status, order dates, costs, discounts and statuses.

Fetch all columns:

SELECT *
FROM customers;

Fetch selected columns:

SELECT name, age
FROM customers;

Ascending:

SELECT *
FROM customers
ORDER BY age ASC;

Descending:

SELECT *
FROM customers
ORDER BY age DESC;

Rename a column:

SELECT cost * 0.04 AS sales_tax
FROM orders;

Rename a table:

SELECT cus.name, cus.age
FROM customers AS cus;

Filter customers over 35:

SELECT *
FROM customers
WHERE age > 35;

Combine conditions:

SELECT *
FROM customers
WHERE (country = 'USA' OR country = 'Canada')
AND has_subscription = TRUE;

Starts with New:

SELECT *
FROM customers
WHERE city LIKE 'New%';

Multiple patterns:

SELECT *
FROM customers
WHERE city LIKE '%e'
   OR city LIKE '_r%'
   OR city LIKE '%x%';

SELECT status
FROM orders
WHERE cost BETWEEN 100 AND 200;

SELECT name
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');

SELECT name
FROM customers
WHERE age IS NOT NULL;

The source also demonstrates NOT with compound conditions.

INNER JOIN returns rows having matching values in both tables.

SELECT orders.order_id, orders.cus_id,
       customers.id, customers.name
FROM orders
INNER JOIN customers
ON orders.cus_id = customers.id;

LEFT JOIN returns all rows from the left table and matching rows from the right table; unmatched right-side values are NULL.

SELECT customers.name, orders.date, orders.cost
FROM customers
LEFT JOIN orders
ON customers.id = orders.cus_id;

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

SELECT orders.order_id, customers.name
FROM orders
RIGHT JOIN customers
ON orders.cus_id = customers.id;

FULL OUTER JOIN returns all rows from both tables; unmatched values are NULL.

SELECT orders.order_id, orders.cost,
       customers.id, customers.name
FROM orders
FULL OUTER JOIN customers
ON orders.cus_id = customers.id;

SELF JOIN joins a table with itself to compare rows. Aliases distinguish the table instances.

SELECT A.cus_id, A.order_id AS ord_1, A.date AS date_1,
       B.order_id AS ord_2, B.date AS date_2
FROM orders A
JOIN orders B ON A.cus_id = B.cus_id
AND A.order_id != B.order_id;

CROSS JOIN returns all possible row combinations and has no join condition.

SELECT orders.order_id, customers.name
FROM orders
CROSS JOIN customers;

GROUP BY groups rows with the same specified values and calculates aggregates for each group.

SELECT cus_id,
       SUM(cost) AS sum_cost,
       COUNT(order_id) AS count_id,
       MAX(cost) AS max_cost,
       ROUND(AVG(cost), 2) AS avg_cost
FROM orders
GROUP BY cus_id
ORDER BY cus_id;

A subquery is nested inside another query. A single-value subquery can be used with comparison operators.

SELECT order_id
FROM orders
WHERE cost > (
  SELECT AVG(cost)
  FROM orders
);

Multiple-value subqueries can use IN, EXISTS, ANY or ALL.

SELECT order_id
FROM orders
WHERE cus_id = ANY (
  SELECT id AS cus_id
  FROM customers
  WHERE country = 'USA'
);

Window functions compute results over a related set of rows. The source groups them into aggregate, ranking and value functions.

PARTITION BY divides rows into groups called partitions. Without it, the entire result set is the partition.

SELECT order_id, cus_id, cost,
       SUM(cost) OVER (PARTITION BY cus_id) AS sum_cost
FROM orders;

Within a window, ORDER BY specifies the row ordering. Without ORDER BY, row order within a partition is arbitrary.

SELECT order_id, cus_id, cost,
       SUM(cost) OVER (
         PARTITION BY cus_id
         ORDER BY cost ASC
       ) AS sum_cost
FROM orders;

RANK() assigns ranks based on the specified ordering.

SELECT order_id, cost,
       RANK() OVER (
         ORDER BY cost DESC
       ) AS order_rank
FROM orders;

LAG() accesses data from a previous row and is often used for Year-over-Year or Month-over-Month calculations.

SELECT YEAR(date) AS year,
       SUM(cost) AS cur_sales,
       LAG(SUM(cost)) OVER (
         ORDER BY YEAR(date)
       ) AS last_year_sales
FROM orders
GROUP BY YEAR(date)
ORDER BY YEAR(date);

Common Table Expressions are temporary result sets referenced within a query. They improve readability and simplify complex queries.

WITH sum_sales AS (
  SELECT cus_id, SUM(cost) AS tot_sales
  FROM orders
  GROUP BY cus_id
)
SELECT cus_id, tot_sales
FROM sum_sales
WHERE tot_sales > 350;

CASE evaluates conditions in order and returns the value for the first true condition; ELSE is used when none are true.

SELECT order_id, cus_id, cost,
CASE
  WHEN cost > 175 THEN 'luxury'
  WHEN cost > 100 THEN 'mid-tier'
  ELSE 'budget'
END AS product_type
FROM orders;

Set operations combine query results. UNION removes duplicates; UNION ALL keeps duplicate rows.

SELECT name
FROM actors
WHERE country = 'Canada'
UNION ALL
SELECT name
FROM singers
WHERE country = 'Canada';