Tracks Above Average Price
The pricing team wants to identify premium-priced Track. Find all tracks that are priced above the average track price...
SubqueryAVGWHERE
Beginner
6m15 pts
35100%
SQL Practice Hub
SQL Subqueries Practice Questions
Nest one query inside another when you need its answer first.
16 questionsFreeRuns in your browser
- Scalar subquery
- Returns one value, usable in SELECT or a comparison.
- Correlated subquery
- Refers to the outer row and runs once per row.
- Subquery vs JOIN
- Pick whichever reads clearer and runs faster.
What you will practice
- Use a scalar subquery to compare each row against an overall value such as the company average
- Write correlated subqueries with EXISTS and NOT EXISTS to test for related rows
- Filter with IN versus EXISTS and understand the NULL trap in NOT IN
- Put a subquery in the FROM clause as a derived table to aggregate twice
- Recognise when to rewrite a nested subquery as a CTE or a join for readability
Read the full overview
A subquery is a SELECT nested inside another statement, used when one query needs the answer to a smaller query first. A scalar subquery returns a single value and can sit in the SELECT list or a comparison, while a correlated subquery references the outer row and is re-evaluated for each of them. These questions build the judgement for when a subquery is the clearest tool and when a join or CTE would read better and run faster.
16 questionsPage 1 of 2
Correlated Subquery: Above Genre Average
A correlated subquery references the outer query. Find tracks that are longer than the average length of tracks in...
SubqueryCorrelated SubqueryAVGIntermediate9m25 pts19100%EXISTS: Find Customers with Large Purchases
Use EXISTS to find customers who have made at least one purchase over $10. EXISTS is efficient for checking if related...
SubqueryEXISTSCorrelated SubqueryBeginner6m15 pts24100%NOT EXISTS: Artists Without Albums
Use NOT EXISTS to find artists who don't have any albums in the catalog. This is a common pattern for finding orphan or...
SubqueryNOT EXISTSData QualityBeginner6m15 pts23100%Scalar Subquery in SELECT
Use a scalar subquery in the SELECT clause to add computed values. Show each artist alongside the total number of...
SubqueryScalar SubquerySELECTBeginner5m10 pts17100%Nested Aggregation: Average of Totals
Calculate the average customer lifetime value (average of each customer's total spending). This requires aggregating...
SubqueryNested AggregationAVGSUMIntermediate7m20 pts16100%Calculate Percentage of Total
Calculate each genre's share of total tracks as a percentage. This requires comparing individual counts to the overall...
SubqueryPercentageAggregationGROUP BY+1Intermediate8m25 pts15100%Catalog Snapshot Report
Generate a single-row "catalog overview" for the platform's daily dashboard. Schema: Track ColumnType TrackIdINTEGER...
COUNTCOUNT DISTINCTScalar SubqueryAVG+3Intermediate10m25 pts267%Customers Active in Multiple Years
Find customers who have purchased across multiple calendar years — a proxy for loyalty. Schema: Customer ColumnType...
GROUP BYHAVINGCOUNT DISTINCTEXTRACT+3Intermediate10m25 pts2100%Top 3 Spenders per Country
For each country with customers, identify the top 3 spenders using a window function. Schema: Customer ColumnType...
Window FunctionsROW_NUMBERPARTITION BYJOIN+2Intermediate10m25 pts10100%Genres With Above-Average Pricing
Find genres whose average track price exceeds the overall catalog average. Schema: Track ColumnType TrackIdINTEGER (PK)...
SubqueryGROUP BYAVGHAVING+1Intermediate10m25 pts10100%First-Purchase Cohort Count by Month
Show how many customers made their first-ever purchase each month — the new-customer cohort series. Schema: Invoice...
SubqueryGROUP BYMINTO_CHAR+3Intermediate10m25 pts267%Invoices Above the Overall Average (Scalar Subquery)
The finance team wants to spot unusually large orders. Find every invoice whose Total is greater than the average Total...
SubqueriesScalar SubqueryIntermediate6m25 pts1100%Tracks That Have Never Been Purchased (Anti-Join)
The catalog team wants to find tracks that have never appeared in any customer purchase, as candidates for promotion or...
SubqueriesCorrelated SubqueryNOT EXISTSAnti-Join+1Intermediate7m25 pts1100%Tracks Priced Above the Catalog Average (Scalar Subquery)
Find every track whose UnitPrice is greater than the average UnitPrice across the entire catalog, using a scalar...
SubqueriesScalar SubqueryIntermediate5m25 pts1100%
FAQ
SQL Subqueries practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
Should I use a subquery or a JOIN?
Both can often give the same result. Choose by readability and by which columns you need.
- JOIN: when you need columns from both tables in the result.
- Subquery: when you only filter by, or compute one value from, the other table.
- EXISTS usually reads best for "has at least one" checks (shown here).
- Modern optimizers often produce the same plan, so check EXPLAIN before rewriting for speed.
SQL
-- customers with at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);What is a correlated subquery?
A correlated subquery references a column from the outer query, so it is logically re-evaluated for every outer row.
- Typical use: compare each row to a value for its own group, like each salary against its department average (shown here).
- It can be slow on large tables if the database cannot optimize it away.
- Often rewritable as a JOIN to a pre-aggregated subquery, or as a window function.
SQL
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);Why does NOT IN return no rows?
If the subquery returns even one NULL, NOT IN returns no rows at all.
- x NOT IN (1, NULL) evaluates to unknown, never true.
- Use NOT EXISTS instead (shown here); it is NULL-safe.
- Or add WHERE col IS NOT NULL inside the subquery.
SQL
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.