Turn a whole table into a single row of numbers.
What to build
A query that summarises every result in one row.
Requirements
- Return how many students there are, the lowest mark, the highest mark and the average mark rounded to 2 decimal places.
- Name the columns
students,lowest,highestandaverage. - Return exactly one row, even when the table is empty.
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
One row with columns students, lowest, highest and average. 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', 72), (3, 'C', 91);
your SQL should print:
students lowest highest average
-------- ------ ------- -------
3 72 91 82.67
(1 row)
Why it works
Aggregates without GROUP BY treat the whole table as one group. On an empty table COUNT gives 0, while MIN, MAX and AVG give NULL, because there is nothing to measure.
Examples
CREATE TABLE results ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, marks INTEGER NOT NULL ); INSERT INTO results VALUES (1, 'A', 85), (2, 'B', 72), (3, 'C', 91);
students lowest highest average -------- ------ ------- ------- 3 72 91 82.67 (1 row)
CREATE TABLE results ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, marks INTEGER NOT NULL ); INSERT INTO results VALUES (1, 'A', 50);
students lowest highest average -------- ------ ------- ------- 1 50 50 50.0 (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
With no GROUP BY, aggregate functions summarise the whole table in one row.
Hint 2
`ROUND(AVG(marks), 2)` keeps two decimal places.
Solution
Show one correct solution (try it yourself first)
SELECT COUNT(*) AS students, MIN(marks) AS lowest, MAX(marks) AS highest, ROUND(AVG(marks), 2) AS average FROM results;