MediumSQL
Best Available Contact Number
SQLDatabases
A contacts table stores a phone and a mobile number, either of which
may be NULL. Write a SQL query that returns each contact's name and their
best available number: phone first, then mobile, then 'N/A' if both
are NULL. Order by name.
Table: contacts
| id | name | phone | mobile |
|---|---|---|---|
| 1 | Alice | 555-1001 | NULL |
| 2 | Bob | NULL | 555-2002 |
| 3 | Carol | 555-3003 | 555-3004 |
| 4 | Dave | NULL | NULL |
| 5 | Eve | 555-5005 | NULL |
Expected output (columns: name, contact_number)
Alice|555-1001
Bob|555-2002
Carol|555-3003
Dave|N/A
Eve|555-5005
COALESCE(a, b, c)returns the first non-NULL argument.
Sample tests
Test #1All COALESCE cases: phone only, mobile only, both, neither, phone preferred
Input: "CREATE TABLE contacts (id INTEGER, name TEXT, phone TEXT, mobile TEXT);\nINSERT INTO contacts VALUES\n (1, 'Alice', '555-1001', NULL),\n (2, 'Bob', NULL, '555-2002'),\n (3, 'Carol', '555-3003', '555-3004'),\n (4, 'Dave', NULL, NULL),\n (5, 'Eve', '555-5005', NULL);"
Output: "Alice|555-1001\nBob|555-2002\nCarol|555-3003\nDave|N/A\nEve|555-5005"