What each index is sorted by

A composite index is sorted by its first column, then its second. Build one, then look inside every index on the table.

What to build

A composite index on (dept, marks), followed by a list of every index's columns in order.

Requirements

• Create an index named idx_dept_marks on results(dept, marks).
• List every index on results with each of its columns and the column's position, starting at 0.
• Sort by index name, then by position.

Tables

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

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  marks INTEGER
);

Output

Columns index_name, position and column_name. 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 on the table. This uses SQLite's catalogue; other systems have information_schema tables for the same purpose.

Example

When the tables start empty,

your SQL should print:

index_name      position  column_name
--------------  --------  -----------
idx_dept_marks  0         dept
idx_dept_marks  1         marks
(2 rows)

Why it works

An index stores its columns in a fixed order, and that order decides which searches it can help. Listing positions shows that idx_dept_marks is sorted by dept first.

Examples

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  marks INTEGER
);

index_name      position  column_name
--------------  --------  -----------
idx_dept_marks  0         dept
idx_dept_marks  1         marks
(2 rows)

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  marks INTEGER
);
CREATE INDEX idx_name ON results(name);

index_name      position  column_name
--------------  --------  -----------
idx_dept_marks  0         dept
idx_dept_marks  1         marks
idx_name        0         name
(3 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_dept_marks ON results(dept, marks);

SELECT m.name AS index_name, i.seqno AS position, i.name AS column_name
FROM sqlite_master m, pragma_index_info(m.name) i
WHERE m.type = 'index' AND m.tbl_name = 'results'
ORDER BY m.name, i.seqno;

Lesson: Indexes and how queries find rows →