Databases & SQL Interview Questions — Joins and Querying Practice

Databases & SQL/querying/joins

For an INNER JOIN, swapping which table is written first (A JOIN B vs B JOIN A) changes which rows appear in the result.#

Databases & SQL/querying/joins

customers(id, name) has (1, Ann), (2, Bo), (3, Cy). orders(customer_id) has rows for customer_id 1 and 1. What does this query return?#

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL
ORDER BY c.name;

Bo, Cy

Databases & SQL/querying/joins

To keep every row from the left table even when there is no match, use customers c _____ JOIN orders o _____ o.customer_id = c.id.#

Databases & SQL/querying/joins

A LEFT JOIN between customers (left) and orders (right) returns a customer who has placed no orders. What appears in that row's orders columns?#

Databases & SQL/querying/joins

customers(id, name) has (1, Ann), (2, Bo), (3, Cy). orders(customer_id, amount) has (1, 10), (1, 20), (2, 5). How many rows does this query return?#

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

3 rows

Databases & SQL/querying/joins

The customers table has columns id (INTEGER) and name (TEXT). The orders table has columns id (INTEGER), customer_id (INTEGER referencing customers.id), and total (INTEGER). Write a query returning the customers who have placed NO orders at all, with a single column name, ordered by name ascending. Use an anti-join pattern (LEFT JOIN ... IS NULL or NOT EXISTS) — an inner join cannot find these rows.#

-- Find customers with zero orders via LEFT JOIN ... IS NULL or NOT EXISTS.
-- Return: name, ordered by name ASC.
SELECT

Databases & SQL/querying/joins

The employees table has columns id (INTEGER), name (TEXT), salary (INTEGER), and manager_id (INTEGER referencing employees.id, NULL for employees with no manager). Write a query returning each employee who earns strictly more than their direct manager, with columns name and salary (the employee's own name and salary), ordered by name ascending. You will need to join the table to itself.#

-- Self-join employees (as employee and as manager) on manager_id.
-- Return: name, salary of employees paid more than their manager, ordered by name ASC.
SELECT

Databases & SQL/querying/joins

This is meant to be a LEFT JOIN listing every customer and their order count, but customers with no orders are dropped. Which line causes that?#

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'
GROUP BY c.name;

Databases & SQL/querying/joins

Two payment ledgers need reconciling. The bank_ledger table has columns payment_ref (TEXT, unique) and amount (INTEGER); the book_ledger table has the same two columns. Write a single query using FULL OUTER JOIN on payment_ref that returns one row per payment present in EITHER ledger, with columns bank_ref, bank_amount, book_ref, book_amount (the bank-side pair is NULL when the payment is missing from the bank ledger, and likewise for the book side). Order by COALESCE(bank_ref, book_ref) ascending.#

-- FULL OUTER JOIN the two ledgers on payment_ref so unmatched rows on
-- either side survive with NULLs. Return: bank_ref, bank_amount, book_ref,
-- book_amount, ordered by COALESCE(bank_ref, book_ref) ASC.
SELECT

Databases & SQL/querying/joins

The customers table has columns id (INTEGER) and name (TEXT). The orders table has columns id (INTEGER), customer_id (INTEGER referencing customers.id), and price (INTEGER). Return every order priced strictly above ITS OWN customer's average order price, with columns order_id (the order's id), name (the customer's name), and price, ordered by order_id ascending. Do NOT use a correlated subquery in the WHERE clause — instead JOIN against a grouped derived table (or CTE) of per-customer averages. The average is only compared against, never output, so fractional averages are fine.#

-- Build a derived table of per-customer AVG(price), then JOIN orders to it
-- and keep orders priced above their customer's average.
-- Return: order_id, name, price, ordered by order_id ASC.
SELECT