SQL · Quick Reference

Project 1 — School Database Analytics Cheat Sheet

SQL

In one line: Design and query a complete school database with students, teachers, subjects, grades, and attendance. Produce real analytics reports using JOINs, aggregation, window functions,...

Key Ideas

1Build the Database. Start by creating all tables. Run these CREATE TABLE statements in order — note the foreign key dependencies.

Code Examples

-- 1. Teachers
CREATE TABLE teachers (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(150) UNIQUE,
  department VARCHAR(80),
  joined_on  DATE
);

-- 2. Subjects
CREATE TABLE subjects (
  id ...

-- Total students enrolled
SELECT COUNT(*) AS total_students FROM students;

-- Average score across all subjects
SELECT ROUND(AVG(score), 2) AS avg_score FROM grades;

-- Top 10 students by average score
SELECT
  s.name,
  ROUND(AVG(g.score), 2) A...

-- Full student → subject → teacher report
SELECT
  s.name    AS student,
  sub.name  AS subject,
  t.name    AS teacher,
  g.score,
  g.exam_date
FROM students s
JOIN grades g    ON s.id = g.student_id
JOIN subjects sub ON sub.id = g.subject_id
J...