SQL Practice

Write SQL against a gym's member database, run it, and check your answer against the result table. The tasks are the same as the Database category in the terminal; progress here is recorded separately from the terminal.

Hint

Table definitions

                        

Every run starts from the original data. Changes such as UPDATE are not carried over to the next run.

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. 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.

  12. 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.