A contact for everyone outside the big cities

Missing values make simple-looking questions tricky. Handle every NULL on purpose.

What to build

A query that gives a contact for everyone who does not live in Dhaka or Sylhet, including people whose city is unknown.

Requirements

• Keep people whose city is neither Dhaka nor Sylhet, and people whose city is NULL.
• Show the phone number, or the email when there is no phone, or no contact when there is neither.
• Return name and contact, sorted by name.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  phone TEXT,
  email TEXT
);

Output

Columns name and contact, in alphabetical order of name. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

City names are written with a capital first letter. Any of city, phone and email may be NULL.

Example

When a test starts with:

INSERT INTO people VALUES (1, 'Rahim', 'Dhaka', '017', NULL), (2, 'Karim', NULL, NULL, 'k@mail'), (3, 'Nadia', 'Khulna', NULL, NULL), (4, 'Tania', 'Rajshahi', '018', 't@mail');

your SQL should print:

name   contact
-----  ----------
Karim  k@mail
Nadia  no contact
Tania  018
(3 rows)

Why it works

A NULL city makes NOT IN unknown rather than true, so those rows need their own test. COALESCE works through its values in order and returns the first one that is present.

Examples

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  phone TEXT,
  email TEXT
);
INSERT INTO people VALUES (1, 'Rahim', 'Dhaka', '017', NULL), (2, 'Karim', NULL, NULL, 'k@mail'), (3, 'Nadia', 'Khulna', NULL, NULL), (4, 'Tania', 'Rajshahi', '018', 't@mail');

name   contact
-----  ----------
Karim  k@mail
Nadia  no contact
Tania  018
(3 rows)

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  phone TEXT,
  email TEXT
);
INSERT INTO people VALUES (1, 'Sumon', 'Sylhet', '019', NULL);

name  contact
----  -------
(0 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT name, COALESCE(phone, email, 'no contact') AS contact
FROM people
WHERE city IS NULL OR city NOT IN ('Dhaka', 'Sylhet')
ORDER BY name;

Lesson: Choosing rows and columns: SELECT and WHERE →