School sample database

Students, courses and enrollments with grades and GPAs.

4 tables · 92 rows · 20 KB

Tables

course_enrollment 5 rows
  • course_id integer PK
  • course_name text
  • enrolled integer
  • completed integer
course_idcourse_nameenrolledcompleted
1SQL Mastery150120
2Python Basics200180
3Data Science10065
courses 15 rows
  • course_id integer PK
  • course_name text
  • credits integer
  • department text
course_idcourse_namecreditsdepartment
1SQL Basics3Computer Science
2Python 1013Computer Science
3Data Structures4Computer Science
enrollments 47 rows
  • enrollment_id integer PK
  • student_id integer → students
  • course_id integer → courses
  • semester text
  • grade text
enrollment_idstudent_idcourse_idsemestergrade
111Fall 2024A
213Fall 2024A
315Fall 2024A-
students 25 rows
  • student_id integer PK
  • name text
  • email text
  • grade_level integer
  • gpa real
  • enrollment_date text
student_idnameemailgrade_levelgpaenrollment_date
1Alice Johnson[email protected]123.92024-09-01
2Bob Smith[email protected]123.72024-09-01
3Carol Williams[email protected]123.82024-09-01

How the tables connect

Try a query

SELECT c.course_name, COUNT(e.student_id) AS students
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_name
ORDER BY students DESC;

Use it on your computer

The SQLite file opens with the sqlite3 command-line tool or any SQLite app:

sqlite3 school.sqlite
.tables

For MySQL, PostgreSQL or SQL Server, run the SQL script instead. It is plain CREATE TABLE and INSERT statements written for SQLite, so another database may need a small change.

11 practice questions on this database

  1. Easy Completion Rate as a Percentage Course Completion Percentage Free
  2. Easy Count Rows per Group Course Enrollment Numbers Free
  3. Easy Rows Above the Average Students Exceeding Average GPA Free
  4. Medium Cumulative Sum Over Time Cumulative Student Enrollment Growth Premium
  5. Medium Ranking With Ties Student GPA Ranking with Ties Premium
  6. Medium DELETE Rows With a Condition Academic Fresh Start Premium
  7. Medium Unique Index One Account per Email Premium
  8. Medium INSERT and UPDATE in One Transaction Enroll and Count, Together Premium
  9. Medium Subquery vs CTE Courses Above the Average Enrollment Free
  10. Medium COUNT DISTINCT With GROUP BY Unique Students per Department Free
  11. Hard DELETE Rows With No Match Remove the Ghost Students Premium

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

More sample databases