Every city once

List the cities the students come from, each city once. Leave out students whose city is unknown (NULL).

What to build

A query that lists each known city once.

Requirements

• Leave out students whose city is NULL.
• Remove repeated cities with DISTINCT.
• Sort the cities alphabetically.

Tables

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

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);

Output

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

Constraints

Several students may share a city, and some cities may be NULL.

Example

When a test starts with:

INSERT INTO students VALUES
  (1, 'Rahim', 'Dhaka', 85),
  (2, 'Karim', 'Sylhet', 72),
  (3, 'Nadia', 'Dhaka', 91),
  (4, 'Tania', 'Khulna', 55),
  (5, 'Sumon', 'Sylhet', 88);

your SQL should print:

city
------
Dhaka
Khulna
Sylhet
(3 rows)

Why it works

DISTINCT removes repeated rows from the result, and IS NOT NULL removes the unknown cities before that.

Examples

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);
INSERT INTO students VALUES
  (1, 'Rahim', 'Dhaka', 85),
  (2, 'Karim', 'Sylhet', 72),
  (3, 'Nadia', 'Dhaka', 91),
  (4, 'Tania', 'Khulna', 55),
  (5, 'Sumon', 'Sylhet', 88);

city
------
Dhaka
Khulna
Sylhet
(3 rows)

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);
INSERT INTO students VALUES
  (1, 'Rina', NULL, 70),
  (2, 'Imran', 'Rajshahi', 80),
  (3, 'Joya', 'Barishal', 60);

city
--------
Barishal
Rajshahi
(2 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 DISTINCT city
FROM students
WHERE city IS NOT NULL
ORDER BY city;

Lesson: Sorting, limiting and DISTINCT →