Above the class average

Use the answer to one question inside another.

What to build

A query that finds the students who scored above the average of everyone.

Requirements

• Work out the average with a subquery, not a number typed by hand.
• Keep the students whose marks are strictly above it.
• 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,
  marks INTEGER NOT NULL
);

Output

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

Constraints

The table has at least one student.

Example

When a test starts with:

INSERT INTO students VALUES (1, 'Rahim', 85), (2, 'Karim', 72), (3, 'Nadia', 91), (4, 'Tania', 64);

your SQL should print:

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

Why it works

The subquery is worked out once and gives a single value, so it can be compared with each row's marks. Because it is computed from the data, it stays right when the rows change.

Examples

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER NOT NULL
);
INSERT INTO students VALUES (1, 'Rahim', 85), (2, 'Karim', 72), (3, 'Nadia', 91), (4, 'Tania', 64);

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

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER NOT NULL
);
INSERT INTO students VALUES (1, 'A', 50), (2, 'B', 50);

name  marks
----  -----
(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, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students)
ORDER BY marks DESC, name;

Lesson: Subqueries and WITH →