SQL practice tasks
Practice the basics of SQL in 12 tasks, using a gym's member data (gym.db) and data with history migrated from an old system (gym_legacy.db). You start by listing tables and reading their definitions, then move on to filtering, sorting, and counting rows, handling NULL and boundary values, JOIN and LEFT JOIN, and finally an UPDATE with WHERE. Answers are judged by the result table, and checked again against hidden data with different values.
-
List the Tables in a Database
gym.db is an SQLite database holding a gym's member data. Open it with sqlite3 and list the tables inside. In Linux terms, this is like ls.
Solve in the terminal (answered with a shell command)
-
View a Table's Column Definitions
Give the sqlite3 dot command .schema a table name to show only the definition (the CREATE TABLE statement) of the members table. In Linux terms, this is like cat.
Solve in the terminal (answered with a shell command)
-
Count the Members
Count the members in the members table with COUNT(*). In Linux terms, that is wc -l members.csv. Run it too: the two numbers should differ by one. Find out why.
Solve this task
-
Pick Only the Matching Rows
From the members table, pick only the name of members whose city is Tokyo, using WHERE. In Linux terms, this is like grep.
Solve this task
-
Sort and Show Only the Top Rows
Show the name and age of the 3 oldest members, highest age first (ties in name order), with ORDER BY and LIMIT. In Linux terms: sort -rn | head -3.
Solve this task
-
Count by Group
Count the members in each city with GROUP BY. In Linux terms, that is cut -d , -f 4 members.csv | sort | uniq -c. Run it too: the counts match, but there is one extra line.
Solve this task
-
Pipe SQL Output into Linux Commands
sqlite3 output is plain text, so it can flow through a Linux pipe. Output the city column of members with sqlite3 -csv, pass it to sort and uniq -c, and count the members in each city.
Solve in the terminal (answered with a shell command)
-
Find Members Without an Email
Show the name of members with no email. SELECT name FROM members WHERE email = NULL; returns 0 rows, because NULL cannot be compared with =. Fix it. In Linux terms: grep -v @ members.csv.
Solve this task
-
Check the Boundary of "Or Older"
Count the members aged 20 or older. SELECT COUNT(*) FROM members WHERE age > 20; skips members who are exactly 20. "20 or older" includes the boundary 20. Fix it to match the question.
Solve this task
-
Join Tables to Show the Current Plan
From here on, use gym_legacy.db: data migrated from an old system, with history rows for plan changes. Show each member's name and the name of their current plan with JOIN.
Solve this task
-
Find Members Who Never Enrolled
In gym_legacy.db, show the name of members with no row in enrollments (they never enrolled). A plain JOIN drops members with no match; LEFT JOIN keeps them.
Solve this task
-
Fix Just One Row with WHERE
In gym_legacy.db, change the city of member id 3 to Osaka with UPDATE. First run SELECT COUNT(*) with the same WHERE to confirm only 1 row changes. Like sed -i in Linux, it edits data in place.
Solve this task