Good marks in Dhaka

Show the students from Dhaka who got 60 or more marks.

What to build

A query that finds the Dhaka students with 60 or more marks.

Requirements

• Combine the two conditions with AND.
• Put the city name in single quotes.
• Sort by marks from highest, then by name.

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

Columns name and marks, from the highest marks to the lowest. Students with equal marks are 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

Some students may have NULL as their city or marks.

Example

When a test starts with:

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

your SQL should print:

name   marks
-----  -----
Nadia  91
Rahim  85
(2 rows)

Why it works

A row is kept only when both conditions are true. A NULL city or mark makes the condition unknown, so such rows are left out.

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', 'Dhaka', 55),
  (5, 'Sumon', 'Khulna', 88);

name   marks
-----  -----
Nadia  91
Rahim  85
(2 rows)

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

name   marks
-----  -----
Imran  70
Rina   70
Joya   60
(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

SELECT name, marks
FROM students
WHERE city = 'Dhaka' AND marks >= 60
ORDER BY marks DESC, name;

Lesson: Choosing rows and columns: SELECT and WHERE →