A view that keeps up with the data

See the difference between a saved query and a saved copy.

What to build

A view of the students who passed, used before and after a change to the marks.

Requirements

Create a view called passed with the name and marks of everyone with 60 or more.
Show how many rows the view has, then give 5 grace marks to everyone with 55 to 59, as the starter does.
Finally show the view's rows, from the highest marks, then by name.

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,
  marks INTEGER NOT NULL
);

Output

Two results: one column passed with the count, then 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

Marks are whole numbers from 0 to 100.

Example

When a test starts with:

INSERT INTO results VALUES (1, 'A', 85), (2, 'B', 57), (3, 'C', 40), (4, 'D', 60);

your SQL should print:

passed
------
2
(1 row)

name  marks
----  -----
A     85
B     62
D     60
(3 rows)

Why it works

A view stores no rows. Each time it is used, its query runs on the current data, so students who reach 60 with grace marks appear in it straight away. A table copied earlier would still show the old rows.

Examples

Example 1 · Input

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER NOT NULL
);
INSERT INTO results VALUES (1, 'A', 85), (2, 'B', 57), (3, 'C', 40), (4, 'D', 60);

Expected output

passed
------
2
(1 row)

name  marks
----  -----
A     85
B     62
D     60
(3 rows)

Example 2 · Input

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER NOT NULL
);
INSERT INTO results VALUES (1, 'X', 55);

Expected output

passed
------
0
(1 row)

name  marks
----  -----
X     60
(1 row)

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

Hint 1

`CREATE VIEW passed AS SELECT name, marks FROM results WHERE marks >= 60;` saves the query, not its rows.

Hint 2

After the UPDATE, the same view shows the new marks without being changed.

Solution

Show one correct solution (try it yourself first)

CREATE VIEW passed AS SELECT name, marks FROM results WHERE marks >= 60;

SELECT COUNT(*) AS passed FROM passed;

UPDATE results SET marks = marks + 5 WHERE marks BETWEEN 55 AND 59;

SELECT name, marks FROM passed ORDER BY marks DESC, name;

Lesson: CASE, views and set operations →