The academy database, built and queried

The situation

A training academy keeps students, courses and enrolments in three spreadsheets that disagree with each other. You will design the three tables properly, load them with realistic rows, and answer the questions the office actually asks — including the one every SQL interview asks and the deletion you must never run without looking first.

What you'll be able to show

• Design tables with keys that refer to each other, before writing any SQL
• Filter, sort, group and join with confidence
• Say a query in plain words before writing it
• Handle a deletion the professional way: look, prefer an update, delete only when told

What you need

• DB Browser for SQLite (free), or MySQL if you already have it
• A .sql file you keep — it is day one of your portfolio

Tasks

1. 1Design on paper
   Write the three tables — students (id, name, city, phone, admission_date), courses (id, title, fee), enrollments (id, student_id, course_id, fee_paid, enrolled_on) — and mark which column in enrollments refers to which table. Write one sentence on why enrolments are not a column in students.
   A correct result: Three table definitions with the two foreign keys marked, and the sentence.
2. 2Create and load
   CREATE the three tables with primary keys. INSERT at least 15 students across four cities, 4 courses, and 20 enrolments — including two students with no enrolment and one student enrolled in two courses.
   A correct result: SELECT COUNT(*) gives 15, 4 and 20; the three special cases exist by name.
3. 3Filter and sort
   Write and run: students from one city; enrolments where fee_paid is below 30,000, lowest first; the five most recent admissions. Say each aloud in plain words before writing it.
   A correct result: Three queries with their results pasted.
4. 4Group
   Total fee collected per course title; number of students per city; cities with at least three students (HAVING).
   A correct result: Three grouped results; the HAVING one drops at least one city.
5. 5Join — including the interview question
   Every student name with every course title they are enrolled in; total fees per course title; and the names of students with NO enrolment at all (a LEFT JOIN with a NULL test).
   A correct result: Three joined results; the third lists exactly your two unenrolled students.
6. 6The deletion, done properly
   Student id 8 has left. Write, in order: the SELECT you run first to see everything you would touch (their row and their enrolments); the UPDATE that marks them inactive instead (add a status column); and the DELETE you would run only if told the record must go, with the expected row count for each.
   A correct result: Three statements in the professional order, each with its expected row count, the DELETE not executed.

What to hand in

The .sql file with every CREATE, INSERT and query, and a results document with each query's output pasted under it.

How it is marked