Add the missing index

Searches by city are slow. Give the table an index, and check that it exists.

What to build

An index called idx_people_city on the city column, followed by a list of the table's indexes.

Requirements

• Create an index named exactly idx_people_city on people(city).
• Keep the starter's query, which reads the index names from SQLite's catalogue, sqlite_master.
• Do not drop any index the table already has.

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,
  email TEXT
);

Output

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

Constraints

Some tests start with other indexes already on the table. In MySQL you would list indexes with SHOW INDEX FROM people.

Example

When a test starts with:

INSERT INTO people VALUES (1, 'Rahim', 'Dhaka', 'r@mail'), (2, 'Karim', 'Sylhet', NULL);

your SQL should print:

index_name
---------------
idx_people_city
(1 row)

Why it works

An index is a separate, sorted structure with its own name, recorded in the database's catalogue. Creating it changes no rows, but the catalogue now lists it beside any others.

Examples

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  email TEXT
);
INSERT INTO people VALUES (1, 'Rahim', 'Dhaka', 'r@mail'), (2, 'Karim', 'Sylhet', NULL);

index_name
---------------
idx_people_city
(1 row)

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  email TEXT
);
CREATE INDEX idx_people_name ON people(name);
INSERT INTO people VALUES (1, 'Nadia', 'Dhaka', NULL);

index_name
---------------
idx_people_city
idx_people_name
(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

CREATE INDEX idx_people_city ON people(city);

SELECT name AS index_name
FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'people'
ORDER BY name;

Lesson: Indexes and how queries find rows →