Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
SQL & Databases · Question Set
CTEs, Subqueries & Advanced Joins interview questions for placements and exams.
Questions
14
Included in this set
Subject
SQL & Databases
Explore more sets
Difficulty
Mixed
Level of this set
Go through each question and its explanation. Use this set as a focused practice pack for SQL & Databases.
A semi-join answers whether a matching row exists on the right, but it does not return right-hand columns and does not multiply left rows. Implement with EXISTS or IN when you only need to filter the left side by presence on the right.
SELECT c.* FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );
For complete preparation, combine this set with full subject-wise practice for SQL & Databases. You can also explore other subjects and sets from the links below.
A CTE created with WITH gives a readable name to an intermediate query and exists only for that statement. Many engines inline it like a subquery. CTEs are about clarity and stepwise logic. Performance depends on the optimizer and data shape, not the syntax alone.
WITH recent AS ( SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '30 days' ) SELECT * FROM recent WHERE status = 'PAID';
First, identify each customer’s latest order using ROW_NUMBER over orders partitioned by customer and ordered by date. Filter to rn equals one to keep the latest row. Next, compute the average per category in a separate step. Join the two steps and compare the latest order amount to the category average. This avoids correlated re-computations and keeps the intent simple to tune.
WITH latest AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) rn
FROM orders o
), avg_cat AS (
SELECT category_id, AVG(total) AS avg_total FROM orders GROUP BY category_id
)
SELECT l.customer_id, l.total > a.avg_total AS above_avg
FROM latest l
JOIN avg_cat a ON a.category_id = l.category_id
WHERE l.rn = 1;A correlated subquery depends on values from the current row of the outer query, so the database re-evaluates it for each outer row. That makes it expressive but potentially slower on large inputs. A non-correlated subquery is independent and runs once, with its result reused. It is easier for optimizers to transform into joins or set operations.
SELECT e.* FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id );
EXISTS stops at the first matching row and is not affected by NULLs in the subquery. It works well with an index on the correlated predicate. IN compares a value to a set. If the subquery can yield NULLs, NOT IN can return no rows due to three-valued logic. Deduplicate the subquery or prefer NOT EXISTS.
SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM order_items i WHERE i.order_id = o.id );
The anti-join pattern uses a left join and then filters for rows where the right side is missing. This expresses “in A but not in B”. The same logic can be written with NOT EXISTS. Choose the form that is clearest and performs well in your engine.
SELECT A.* FROM A LEFT JOIN B ON B.k = A.k WHERE B.k IS NULL;
The anchor produces the seed rows. The recursive member references the CTE and expands the result, usually via UNION ALL. A safety condition limits depth. This pattern models hierarchies like org charts and folder trees without procedural code.
WITH RECURSIVE org AS ( SELECT id, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, o.lvl+1 FROM employees e JOIN org o ON e.manager_id = o.id ) SELECT * FROM org;
UNION performs a distinct operation across inputs, which costs CPU and memory. UNION ALL concatenates results and is usually faster. Use UNION when you truly need deduplication. Otherwise prefer UNION ALL and dedupe later if needed.
SELECT id FROM a UNION ALL SELECT id FROM b;
Correlated subqueries can execute once per outer row. A join lets the optimizer find an efficient join order and use indexes on the join keys. Ensure the join does not multiply rows unintentionally. Use grouping or semi-join style predicates if you only need existence.
SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.vip = true;
Self joins let you relate a row to another row in the same table via a relationship like manager_id or previous_id. Alias the table twice so each role is clear. Add indexes on the linking columns for speed.
SELECT e.name AS emp, m.name AS mgr FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;
A cross join returns every combination of rows from both inputs. It is useful for generating grids or filling a date series crossed with categories. Use with care. Without a subsequent filter, the row count is rows(A) × rows(B), which can explode quickly.
SELECT p.product_id, d.dt FROM products p CROSS JOIN dates d;
Choose window functions when you need to keep row detail while adding analytics like running totals, ranks, or group totals. Windows compute across related rows but do not collapse the result, so you can show both the raw row and the metric together. GROUP BY is right when you want one row per group. It summarizes and discards the original granularity. Use it for final aggregates, and windows for per-row insights.
SELECT order_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS cust_total
FROM orders;Add a termination condition that stops expansion when there are no new edges to follow or a maximum depth is reached. In hierarchical data, stop when the parent is NULL or repeats. Track visited nodes in the recursive member. Accumulate the path and exclude nodes already seen. This prevents cycles from re-entering the same vertex and emitting duplicates.
WITH RECURSIVE walk AS ( SELECT id, parent_id, ARRAY[id] AS path FROM nodes WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id, path || n.id FROM nodes n JOIN walk w ON w.id = n.parent_id WHERE n.id <> ALL(w.path) ) SELECT * FROM walk;
Use a CTE when naming the step improves clarity, you reuse the step, or the engine supports readable recursion. A derived table is fine for small, local transformations and keeps the scope tight. Performance often depends on the optimizer. Many engines inline both forms. When reuse, indexing, or materialization control is needed, consider a temporary table with indexes instead.
SELECT * FROM ( SELECT customer_id, SUM(total) AS s FROM orders GROUP BY 1 ) t JOIN customers c ON c.id = t.customer_id;