SQL Quest › SQL Interview Questions › NULL Handling

Ages on Record by Class

EasyFreeQuerying BasicsNULL HandlingAggregation & Grouping

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_idsurvivedpclassnamesexagesibspparchfareembarked
103Braund, Mr. Owen Harrismale22107.25S
211Cumings, Mrs. John Bradleyfemale381071.28C
313Heikkinen, Miss. Lainafemale26007.93S

Expected output: 3 rows — 1st class: 216 passengers, 174 ages on record, 42 missing

Hint

Counting a column is not the same as counting rows: a count over a column skips the rows where it is empty, while counting rows does not. Use one of each, per class, and the missing ages are the difference.

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

Default Age for Missing Records (COALESCE)Easy · FreeFailures with No Loss EstimateEasy · FreeProperties Without Listed OwnerEasy · FreeFare Imputation AnalysisMedium · FreeHandle NULL AgesMedium · FreeOut-Earning the Boss: What COUNT CountsMedium · Free

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