SQL Basics

# SQL Basics

Zerethon Official Sample `sql-basics`, version 1.0.0, SQL Playground (SQLite 3).

- `setup/schema.sql` — the tables and made-up data; runs first on every run
- `main.sql` — entry file: filter, join, group and a window function; each result is a table
- `test_queries.sql` — checks; set it as the entry file to run them

Every run starts from a new, empty in-memory database (Run is also Reset). There is no server and
no real data: nothing you run here reaches any other database.

This project is your own copy. Changing it never changes the official sample.

-- SQL Basics 1.0.0 (Zerethon Official Sample). Each statement's result appears as its own
-- table. Every run starts again from setup/schema.sql, so you can change data freely.

-- 1. Filter and sort
SELECT name, year FROM students WHERE year >= 2 ORDER BY year, name;

-- 2. Join: who takes what
SELECT s.name, c.title, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

-- 3. Group: students and average grade per course (NULL grades are ignored by avg)
SELECT c.title, count(*) AS students, round(avg(e.grade), 2) AS average
FROM courses c
JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id
ORDER BY average DESC;

-- 4. Window function: rank students inside each course
SELECT c.title, s.name, e.grade,
       rank() OVER (PARTITION BY c.id ORDER BY e.grade DESC) AS place
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE e.grade IS NOT NULL
ORDER BY c.title, place;

-- SQL Basics 1.0.0 (Zerethon Official Sample): schema and made-up data.
-- Runs before the entry file on every run, in a new in-memory SQLite database.
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    year INTEGER NOT NULL CHECK (year BETWEEN 1 AND 4)
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL UNIQUE,
    credits INTEGER NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students (id),
    course_id INTEGER NOT NULL REFERENCES courses (id),
    grade REAL,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO students (name, year) VALUES
    ('Ada', 2), ('Ben', 1), ('Chau', 3), ('Duy', 2), ('Eve', 4);

INSERT INTO courses (title, credits) VALUES
    ('Algorithms', 4), ('Databases', 3), ('Networks', 3);

INSERT INTO enrollments (student_id, course_id, grade) VALUES
    (1, 1, 9.0), (1, 2, 8.5), (2, 2, 7.0), (3, 1, 6.5), (3, 3, 8.0),
    (4, 1, 7.5), (4, 2, NULL), (5, 3, 9.5);

-- Checks for the sample data and queries. To run them, set this file as the entry file
-- (the ▶ button next to it in Files) and press Run. A failed check stops the run:
-- the CHECK constraint on `ok` rejects a 0.
CREATE TEMP TABLE checks (name TEXT NOT NULL, ok INTEGER NOT NULL CHECK (ok = 1));

INSERT INTO checks VALUES ('5 students', (SELECT count(*) = 5 FROM students));
INSERT INTO checks VALUES ('3 courses', (SELECT count(*) = 3 FROM courses));
INSERT INTO checks VALUES ('Algorithms has 3 students',
    (SELECT count(*) = 3 FROM enrollments e JOIN courses c ON c.id = e.course_id WHERE c.title = 'Algorithms'));
INSERT INTO checks VALUES ('avg ignores NULL grades',
    (SELECT avg(grade) = 7.75 FROM enrollments WHERE course_id = 2));
INSERT INTO checks VALUES ('Eve tops Networks',
    (SELECT s.name = 'Eve' FROM enrollments e JOIN students s ON s.id = e.student_id
     WHERE e.course_id = 3 ORDER BY e.grade DESC LIMIT 1));
INSERT INTO checks VALUES ('year is limited to 1-4',
    (SELECT count(*) = 0 FROM students WHERE year NOT BETWEEN 1 AND 4));

SELECT 'All ' || count(*) || ' checks passed.' AS result FROM checks;