SQL Practice Test

• A1 → B. SQL is logically evaluated FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, which is why SELECT aliases generally cannot be used in WHERE.
• A2 → B. A LEFT OUTER JOIN keeps every row from the left table (customers) and fills NULLs where no matching order exists.
• A3 → C. NULL is not equal to anything, including NULL, so you must use IS NULL; = NULL never returns true.
• A4 → B. COUNT(*) counts rows, while COUNT(category) ignores rows where category is NULL.
• A5 → A. WHERE filters rows before grouping/aggregation; HAVING filters the resulting groups and may reference aggregates.
• A6 → B. Any non-aggregated SELECT column in a grouped query must appear in the GROUP BY clause in standard SQL.
• A7 → C. Aggregates other than COUNT return NULL over an empty set; SUM of no rows is NULL (use COALESCE to get 0).
• A8 → A. SELECT DISTINCT country and grouping by country both collapse to one row per distinct value.
• A9 → B. UNION removes duplicate rows; UNION ALL keeps all rows and is faster because it skips the de-duplication step.
• A10 → B. An index speeds up equality/range lookups and joins on the indexed column but slows down inserts/updates/deletes that must maintain it.

SELECT name, country
FROM customers
ORDER BY name;

SELECT chooses the two columns and ORDER BY sorts ascending by name (ASC is the default).

SELECT product_name, unit_price
FROM products
WHERE category = 'Electronics'
  AND unit_price > 100
ORDER BY unit_price DESC;

Two conditions are combined with AND in the WHERE clause; DESC sorts the most expensive products first.

SELECT c.name, p.product_name, o.quantity
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products  AS p ON p.product_id  = o.product_id
ORDER BY o.order_id;

Two INNER JOINs link orders to customers and products; only orders with matches on both keys appear.

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The LEFT JOIN keeps all customers; the IS NULL test on the order key isolates customers with no matching order. (NOT IN / NOT EXISTS are valid alternatives.)

SELECT p.category, SUM(o.quantity) AS total_quantity
FROM orders AS o
JOIN products AS p ON p.product_id = o.product_id
GROUP BY p.category
ORDER BY total_quantity DESC;

Rows are grouped by category and SUM aggregates the quantity within each group; the alias is used in ORDER BY, which runs after SELECT.

SELECT c.name, COUNT(*) AS order_count
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

HAVING filters the groups by an aggregate (the count); WHERE cannot do this because aggregation has not happened yet. Grouping by customer_id keeps the count correct even if two customers share a name.

SELECT product_name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products)
ORDER BY unit_price DESC;

The scalar subquery computes the overall average once, and the outer query keeps only products priced above it.

SELECT order_id,
       customer_id,
       order_date,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date
       ) AS order_seq
FROM orders;

ROW_NUMBER assigns 1, 2, 3, ... within each customer (PARTITION BY) ordered by order_date, without collapsing rows the way GROUP BY would. Use RANK() or DENSE_RANK() if ties on the same date should share a number.