SQL · Theory

MIN()()

Theory

205/439

MIN()()

MIN(marks) lowest value, skips NULL. SELECT name, marks FROM students WHERE marks = (SELECT MIN(marks) FROM students); can return several students who tied.

MIN on strings is alphabetical (collation). MIN on dates is earliest.

Trap — MIN as ‘first inserted row’ — it is not, unless you MIN(id).

On the example next to this theory — MIN()() — aggregate functions collapse many rows into one summary row.

Exam tip

MIN(marks) lowest score. Subquery to get those students.

MIN()() — sample query

CREATE TABLE scores (id INTEGER PRIMARY KEY, marks INTEGER);
INSERT INTO scores VALUES (1, 80), (2, 90), (3, 70);
SELECT COUNT(*) AS n, AVG(marks) AS avg_marks, MAX(marks) AS top
FROM scores;

MIN()() — aggregate functions collapse many rows into one summary row.

Short notes

DefMIN = smallest non-NULL value.
RuleTies: several rows can share the min.
TrapMIN meaning ‘first row’.

SQL · Theory

MIN()()

← All stacks

Theory

205/439

MIN()()

MIN(marks) lowest value, skips NULL. SELECT name, marks FROM students WHERE marks = (SELECT MIN(marks) FROM students); can return several students who tied.

MIN on strings is alphabetical (collation). MIN on dates is earliest.

Trap — MIN as ‘first inserted row’ — it is not, unless you MIN(id).

On the example next to this theory — MIN()() — aggregate functions collapse many rows into one summary row.

Exam tip

MIN(marks) lowest score. Subquery to get those students.

MIN()() — sample query

CREATE TABLE scores (id INTEGER PRIMARY KEY, marks INTEGER);
INSERT INTO scores VALUES (1, 80), (2, 90), (3, 70);
SELECT COUNT(*) AS n, AVG(marks) AS avg_marks, MAX(marks) AS top
FROM scores;

MIN()() — aggregate functions collapse many rows into one summary row.

Short notes

DefMIN = smallest non-NULL value.
RuleTies: several rows can share the min.
TrapMIN meaning ‘first row’.

MIN()()

MIN()() — sample query

Short notes

Questions

MIN()()

MIN()() — sample query

Short notes

Questions