SQL Quest › SQL Interview Questions › NULL Handling
Ages on Record by Class
A researcher wants to study survival by age, but the passenger list is missing many ages. Before trusting any average, they want to know how many ages are on record in each class, and how many are missing.
Show exactly these 4 columns, in this order: pclass, passengers (everyone in that class), age_recorded (passengers whose age is filled in) and age_missing (passengers whose age is empty). Order by pclass.
passengers must count everyone, including the people with no age. The two other columns must add up to it.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect". Free accounts get 10 free challenge solves, and this can be one of them.
Schema
passengers
| passenger_id | survived | pclass | name | sex | age | sibsp | parch | fare | embarked |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 0 | 3 | Braund, Mr. Owen Harris | male | 22 | 1 | 0 | 7.25 | S |
| 2 | 1 | 1 | Cumings, Mrs. John Bradley | female | 38 | 1 | 0 | 71.28 | C |
| 3 | 1 | 3 | Heikkinen, Miss. Laina | female | 26 | 0 | 0 | 7.93 | S |
Expected output: 3 rows — 1st class: 216 passengers, 174 ages on record, 42 missing
Hint
Concepts
SELECT NULL Handling COUNT GROUP BY
Practise the topic: SQL practice questions · NULL handling practice · GROUP BY exercises
Read the concept: IS NULL vs = NULL
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test