Albums with Artist Names
The current album data only contains an ArtistId, but users want to see the actual artist name alongside each album....
JOININNER JOIN
Intermediate
5m20 pts
44100%
SQL Practice Hub
SQL Joins Practice Questions
Combine rows from two or more tables by matching a shared key.
48 questionsFreeRuns in your browser
- INNER JOIN
- Only the rows that match on both sides.
- LEFT JOIN
- Every left row, with NULL where there is no match.
- Anti-join
- Rows with no match: LEFT JOIN ... WHERE right.id IS NULL.
What you will practice
- Pick the right join type by asking which side you are allowed to lose rows from
- Spot and fix row multiplication caused by a non-unique join key
- Write anti-joins with LEFT JOIN ... IS NULL or NOT EXISTS to find missing records
- Self-join a table to compare a row against another row in the same table, such as employee to manager
- Chain three or more tables and keep the ON conditions and aliases readable
Read the full overview
A join combines rows from two or more tables by matching values in a shared key column. INNER JOIN keeps only the rows that match on both sides, LEFT JOIN keeps every row from the left table and fills the missing columns with NULL, and an anti-join finds rows with no match at all by adding WHERE right.id IS NULL. These questions cover the join shapes that actually show up in analytics work: fact-to-dimension lookups, self-joins over a hierarchy, and three- and four-table chains.
48 questionsPage 1 of 4
Tracks with Genre Names
The recommendation system needs track data enriched with genre information. Currently, tracks only have a GenreId. You...
JOININNER JOINIntermediate5m20 pts40100%Customer Purchase History
The customer service team needs to see purchase history. They want to see each invoice along with the customer's name...
JOININNER JOINIntermediate5m20 pts40100%Track Count by Genre
The content team wants to understand the distribution of tracks across different Genre. Which genres have the most...
GROUP BYCOUNTJOINAggregationIntermediate6m20 pts39100%Average Song Duration by Genre
The data science team is analyzing listening patterns. They want to know the average track length for each genre. Some...
GROUP BYAVGJOINAggregationIntermediate6m20 pts36100%Most Prolific Artists
The editorial team is writing a feature about the most prolific artists in the catalog. Find artists who have released...
GROUP BYHAVINGJOINAggregationIntermediate8m20 pts3597%Rank Artists by Album Count
Create a leaderboard of artists ranked by how many albums they've released. Artists with the same number of albums...
Window FunctionsRANKGROUP BYJOIN+1Intermediate8m25 pts2897%Segment Customers into Quartiles
The marketing team wants to segment customers into 4 groups (quartiles) based on their total spending. This will help...
Window FunctionsNTILEGROUP BYJOIN+1Intermediate10m25 pts25100%CTE: Top Tracks Per Genre
Use a CTE to find the longest track in each genre. CTEs are especially useful when you need to reference aggregated...
CTEWITHMAXJOINAdvanced10m35 pts27100%Multiple CTEs: Customer Analysis
Use multiple CTEs to perform a comprehensive customer analysis: calculate total spending and invoice count, then...
CTEWITHMultiple CTEsJOINAdvanced10m35 pts2392%Self-Join: Employees and Managers
The Employee table has a self-referencing ReportsTo column. Use a self-join to show each employee alongside their...
Self-JoinLEFT JOINEmployee HierarchyIntermediate8m30 pts9100%Self-Join: Customers in Same City
Find pairs of customers who live in the same city. This can help with organizing local meetups or referral programs....
Self-JoinJOINDeduplicationIntermediate9m25 pts1594%HAVING with Multiple Conditions
Find genres that are both popular (many tracks) AND have high-quality tracks (high average file size). Use HAVING with...
GROUP BYHAVINGJOINMultiple Conditions+1Intermediate8m25 pts17100%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%LEFT JOIN: All Artists Including Those Without Albums
An INNER JOIN only returns matching rows. Use LEFT JOIN to include ALL artists, even those without any albums. Write a...
LEFT JOINAggregationNULL HandlingIntermediate6m25 pts1493%
FAQ
SQL Joins practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table, with NULLs where the right table has no match.
- INNER JOIN: use it when a match is required, like orders with their customers.
- LEFT JOIN: use it when the left table is what you are counting, like all customers including those with no orders.
- RIGHT JOIN is just a LEFT JOIN with the tables swapped.
- FULL OUTER JOIN keeps unmatched rows from both sides.
SQL
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- customers with no orders show order_id = NULLHow do I find rows in one table that have no match in another?
Use an anti-join: keep the rows from table A that have no partner in table B.
- LEFT JOIN B and keep rows where B's key IS NULL, or use NOT EXISTS (shown here).
- Both handle NULLs correctly and usually run as an efficient anti-join.
- Avoid NOT IN: one NULL in the subquery makes the whole result empty.
SQL
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Why does my LEFT JOIN seem to drop rows?
A filter on the right table in WHERE removes the NULL rows a LEFT JOIN creates, so it behaves like an INNER JOIN.
- Why: NULL > 100 is not true, so unmatched rows are filtered out.
- Fix: move the condition into the ON clause (shown here).
- Too many rows instead? That is usually a one-to-many fan-out. Aggregate before you join.
SQL
-- keeps every customer, even those with no order over 100
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id AND o.amount > 100;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.