ASAmol Shukla

19. Real life: college records

Step 1 of 4 · Lesson

Lesson 19 of 21

Real life database practice: college records (50 SQL problems)

Practise 50 SQL problems on a 2,000-result college database — students, courses, enrolments, marks and pass rates, checked in your browser.

200 min 3 min read 50 problems · 0 solved City College (2,000 exam results)

In short

What is in the college practice database?

Four tables: 400 students, 40 courses, 1,200 enrollments and 2,000 exam results with marks and a letter grade. Enrollments answer 'who is taking what' while exam_results answer 'how they did', so joins must name which one they mean. The database is already selected in every problem.

By the end of this lesson you can

  • Filter and sort 2,000 exam results
  • Report pass rates and grade distributions with CASE and GROUP BY
  • Join students to courses and results without mixing up grains
  • Rank students and courses with CTEs and window functions

The database you are practising on

City College is a fictional institution with 400 students across five programs, 40 courses in five departments, 1,200 enrolments and 2,000 exam results. Grades range from A+ to F, and marks are stored as whole numbers so pass rates are easy to reason about.

The whole set is fictional but shaped like the real thing: keys, foreign keys, dates, statuses and 2,000 rows in the busiest table. You do not import anything to start — practice mode loads the same rows into your browser. When you want the same data in MySQL Workbench, download the script (4 tables, 20 columns) and run it there.

What one row means

Read the grain before you write a join. Joining two tables without knowing what one row of each means is the single most common cause of doubled totals.

Table One row is Columns you will use
students one student student_id, student_name, city, program, admission_year, email
courses one course course_id, course_name, department, credits, instructor
enrollments one student in one course for a semester enrollment_id, student_id, course_id, semester, enrolled_on
exam_results one student's result in one exam result_id, student_id, course_id, exam_date, marks, grade

Your database is already selected

Every problem below runs against this database only. The code arena preselects it and does not show a database dropdown, so you never see MySQL's ERROR 1046: No database selected here.

You write one query, press Check answer, and it is compared row by row with the reference answer — same rows, same columns, any valid query wins. Use Hint first, Compare with the reference answer once you have tried, and Reset database if you changed rows while experimenting.

Common mistakes

Wrong

GROUP BY department while selecting course_name

Fix

Group by both columns, or rank inside a CTE and select the winner outside.

Why: ONLY_FULL_GROUP_BY rejects columns that are neither grouped nor aggregated.

Wrong

AVG(CASE WHEN marks >= 50 THEN 1 ELSE 0 END) * 100 written as SUM(...) / COUNT(1)

Fix

Multiply by 100 before dividing, or let AVG handle the fraction as in the reference answers.

Why: Integer division can truncate; MySQL returns a decimal here, but other engines may not.

Wrong

Assuming every enrollment has an exam result

Fix

Choose the table that matches the question and use LEFT JOIN when you need both sides.

Why: Enrollments and results are separate facts; counting one does not count the other.

Check yourself

  1. Can you say in one sentence what one row of students means?
  2. Can you write a query that returns exactly one number from a 2,000-row table without scrolling the result?
  3. Can you explain why two of your joins could double a total, and how you avoided it?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Do I need to install MySQL and import this data?

No. The code arena loads the college database in your browser and preselects it for every problem. Download the .sql script to run the same queries in Workbench.

Are the answers checked automatically?

Yes. Your query and the reference answer run on fresh copies of the same rows and the results are compared row by row, so any correct query is accepted.

Is the college data real?

No. It is fictional and generated from a fixed seed so every learner sees the same rows and the same expected answers.

Quick quiz — 3 questions

Quick Check: Real life: college records

1 / 3
What is the difference between the enrollments and exam_results tables?

Answers — Real life: college records

  1. Easy. List students enrolled in BSc Data Science. Target: 20 rows · student_id, student_name, admission_year · (3, Aarav Gill, 2025); (9, Riya Verma, 2025) …

    SELECT student_id, student_name, admission_year FROM students WHERE program = 'BSc Data Science' ORDER BY student_id LIMIT 20;

  2. Easy. List courses worth 5 credits. Target: 11 rows · course_id, course_name, credits · (3, Management Ethics 3, 5); (5, Commerce Fundamentals 5, 5) …

    SELECT course_id, course_name, credits FROM courses WHERE credits = 5 ORDER BY course_id;

  3. Easy. Show the 20 highest exam results. Target: 20 rows · result_id, student_id, marks · (137, 12, 100); (142, 375, 100) …

    SELECT result_id, student_id, marks FROM exam_results ORDER BY marks DESC, result_id LIMIT 20;

  4. Easy. List every program once. Target: 5 rows · program · (BA Economics); (BBA) …

    SELECT DISTINCT program FROM students ORDER BY program;

  5. Easy. List students admitted in 2025. Target: 20 rows · student_id, student_name, program · (2, Aman Joshi, BBA); (3, Aarav Gill, BSc Data Science) …

    SELECT student_id, student_name, program FROM students WHERE admission_year = 2025 ORDER BY student_id LIMIT 20;

  6. Easy. List Data Science department courses. Target: 10 rows · course_id, course_name · (2, Data Science Analytics 2); (7, Data Science Analytics 7) …

    SELECT course_id, course_name FROM courses WHERE department = 'Data Science' ORDER BY course_id;

  7. Easy. Show the first 20 results with 90 marks or more. Target: 20 rows · result_id, course_id, marks · (137, 30, 100); (142, 10, 100) …

    SELECT result_id, course_id, marks FROM exam_results WHERE marks >= 90 ORDER BY marks DESC, result_id LIMIT 20;

  8. Easy. List enrollments for semester 2025-S1. Target: 20 rows · enrollment_id, student_id, course_id · (6, 120, 7); (11, 267, 40) …

    SELECT enrollment_id, student_id, course_id FROM enrollments WHERE semester = '2025-S1' ORDER BY enrollment_id LIMIT 20;

  9. Easy. Count all students. Target: 1 row · students · (400)

    SELECT COUNT(*) AS students FROM students;

  10. Easy. List students from Chennai. Target: 20 rows · student_id, student_name, program · (3, Aarav Gill, BSc Data Science); (4, Aarav Patel, BA Economics) …

    SELECT student_id, student_name, program FROM students WHERE city = 'Chennai' ORDER BY student_id LIMIT 20;

  11. Medium. List students admitted between 2020 and 2022. Target: 20 rows · student_id, student_name, admission_year · (8, Ishaan Bose, 2022); (15, Arjun Mehta, 2020) …

    SELECT student_id, student_name, admission_year FROM students WHERE admission_year BETWEEN 2020 AND 2022 ORDER BY student_id LIMIT 20;

  12. Medium. List courses in Data Science or Computer Science. Target: 17 rows · course_id, course_name, department · (2, Data Science Analytics 2, Data Science); (6, Computer Science Practicum 6, Computer Science) …

    SELECT course_id, course_name, department FROM courses WHERE department IN ('Data Science', 'Computer Science') ORDER BY course_id;

  13. Medium. List students whose name starts with P. Target: 20 rows · student_id, student_name · (10, Pranav Bose); (53, Pooja Kaur) …

    SELECT student_id, student_name FROM students WHERE student_name LIKE 'P%' ORDER BY student_id LIMIT 20;

  14. Medium. List exam results from 2024. Target: 20 rows · result_id, exam_date, marks · (1, 2024-10-20, 96); (2, 2024-07-04, 80) …

    SELECT result_id, exam_date, marks FROM exam_results WHERE exam_date >= '2024-01-01' AND exam_date < '2025-01-01' ORDER BY result_id LIMIT 20;

  15. Medium. Show each result's exam year. Target: 20 rows · result_id, exam_date, exam_year · (1, 2024-10-20, 2024); (2, 2024-07-04, 2024) …

    SELECT result_id, exam_date, YEAR(exam_date) AS exam_year FROM exam_results ORDER BY result_id LIMIT 20;

  16. Medium. Label each result as Pass or Fail. Target: 20 rows · result_id, marks, outcome · (1, 96, Pass); (2, 80, Pass) …

    SELECT result_id, marks, CASE WHEN marks >= 50 THEN 'Pass' ELSE 'Fail' END AS outcome FROM exam_results ORDER BY result_id LIMIT 20;

  17. Medium. List courses whose name contains Analytics. Target: 8 rows · course_id, course_name · (2, Data Science Analytics 2); (7, Data Science Analytics 7) …

    SELECT course_id, course_name FROM courses WHERE course_name LIKE '%Analytics%' ORDER BY course_id;

  18. Medium. Show each student's name in capitals and their program. Target: 20 rows · student, program · (AMAN IYER, BCom Honours); (AMAN JOSHI, BBA) …

    SELECT UPPER(student_name) AS student, program FROM students ORDER BY student_id LIMIT 20;

  19. Medium. List enrollments before 2024-07-01. Target: 20 rows · enrollment_id, enrolled_on · (978, 2024-01-01); (412, 2024-01-02) …

    SELECT enrollment_id, enrolled_on FROM enrollments WHERE enrolled_on < '2024-07-01' ORDER BY enrolled_on, enrollment_id LIMIT 20;

  20. Medium. Count how many different departments run courses. Target: 1 row · departments · (5)

    SELECT COUNT(DISTINCT department) AS departments FROM courses;

  21. Medium. Show each course name in capitals with its credits. Target: 40 rows · course, credits · (ECONOMICS PRACTICUM 1, 3); (DATA SCIENCE ANALYTICS 2, 3) …

    SELECT UPPER(course_name) AS course, credits FROM courses ORDER BY course_id;

  22. Medium. List results between 40 and 50 marks. Target: 20 rows · result_id, marks · (296, 40); (337, 40) …

    SELECT result_id, marks FROM exam_results WHERE marks BETWEEN 40 AND 50 ORDER BY marks, result_id LIMIT 20;

  23. Hard. Count exam results per grade. Target: 6 rows · grade, results · (B, 452); (A, 376) …

    SELECT grade, COUNT(*) AS results FROM exam_results GROUP BY grade ORDER BY results DESC, grade;

  24. Hard. Show the 10 courses with the highest average marks. Target: 10 rows · course_id, course_name, avg_marks · (10, Commerce Fundamentals 10, 74.09); (24, Computer Science Studio 24, 72.04) …

    SELECT c.course_id, c.course_name, ROUND(AVG(e.marks), 2) AS avg_marks FROM courses c JOIN exam_results e ON e.course_id = c.course_id GROUP BY c.course_id, c.course_name ORDER BY avg_marks DESC, c.course_id LIMIT 10;

  25. Hard. Count students per program. Target: 5 rows · program, students · (BSc Data Science, 85); (BSc Computer Science, 82) …

    SELECT program, COUNT(*) AS students FROM students GROUP BY program ORDER BY students DESC, program;

  26. Hard. List courses with more than 40 exam results. Target: 37 rows · course_id, course_name, results · (35, Data Science Fundamentals 35, 69); (18, Commerce Ethics 18, 60) …

    SELECT c.course_id, c.course_name, COUNT(*) AS results FROM courses c JOIN exam_results e ON e.course_id = c.course_id GROUP BY c.course_id, c.course_name HAVING COUNT(*) > 40 ORDER BY results DESC, c.course_id;

  27. Hard. Show the average marks per department. Target: 5 rows · department, avg_marks · (Commerce, 68.81); (Management, 68.53) …

    SELECT c.department, ROUND(AVG(e.marks), 2) AS avg_marks FROM courses c JOIN exam_results e ON e.course_id = c.course_id GROUP BY c.department ORDER BY avg_marks DESC, c.department;

  28. Hard. Show the pass rate per course, best first. Target: 20 rows · course_name, pass_pct · (Economics Studio 4, 93.8); (Commerce Studio 19, 93.8) …

    SELECT c.course_name, ROUND(100 * SUM(CASE WHEN e.marks >= 50 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_pct FROM courses c JOIN exam_results e ON e.course_id = c.course_id GROUP BY c.course_id, c.course_name ORDER BY pass_pct DESC, c.course_id LIMIT 20;

  29. Hard. Count enrollments per semester. Target: 4 rows · semester, enrollments · (2024-S1, 289); (2024-S2, 291) …

    SELECT semester, COUNT(*) AS enrollments FROM enrollments GROUP BY semester ORDER BY semester;

  30. Hard. Count exam results per month of 2025. Target: 12 rows · month, results · (2025-01, 84); (2025-02, 72) …

    SELECT DATE_FORMAT(exam_date, '%Y-%m') AS month, COUNT(*) AS results FROM exam_results WHERE exam_date >= '2025-01-01' AND exam_date < '2026-01-01' GROUP BY month ORDER BY month;

  31. Hard. List students with more than 6 exam results. Target: 20 rows · student_id, student_name, results · (191, Aman Reddy, 13); (41, Ira Mehta, 11) …

    SELECT s.student_id, s.student_name, COUNT(*) AS results FROM students s JOIN exam_results e ON e.student_id = s.student_id GROUP BY s.student_id, s.student_name HAVING COUNT(*) > 6 ORDER BY results DESC, s.student_id LIMIT 20;

  32. Hard. List courses with no enrollments. Target: 0 rows · course_id, course_name · 

    SELECT c.course_id, c.course_name FROM courses c LEFT JOIN enrollments e ON e.course_id = c.course_id WHERE e.enrollment_id IS NULL ORDER BY c.course_id;

  33. Hard. Show the average marks per program. Target: 5 rows · program, avg_marks · (BSc Computer Science, 69.22); (BCom Honours, 68.87) …

    SELECT s.program, ROUND(AVG(e.marks), 2) AS avg_marks FROM students s JOIN exam_results e ON e.student_id = s.student_id GROUP BY s.program ORDER BY avg_marks DESC, s.program;

  34. Hard. Show departments whose average marks are above 65. Target: 5 rows · department, avg_marks · (Commerce, 68.81); (Management, 68.53) …

    SELECT c.department, ROUND(AVG(e.marks), 2) AS avg_marks FROM courses c JOIN exam_results e ON e.course_id = c.course_id GROUP BY c.department HAVING AVG(e.marks) > 65 ORDER BY avg_marks DESC, c.department;

  35. Hard. Show the 5 best students by average marks, with at least 5 results. Target: 5 rows · student_id, student_name, avg_marks, results · (6, Saanvi Gill, 92.6, 5); (244, Saanvi Patel, 86.2, 5) …

    SELECT s.student_id, s.student_name, ROUND(AVG(e.marks), 2) AS avg_marks, COUNT(*) AS results FROM students s JOIN exam_results e ON e.student_id = s.student_id GROUP BY s.student_id, s.student_name HAVING COUNT(*) >= 5 ORDER BY avg_marks DESC, s.student_id LIMIT 5;

  36. Hard. Count results per grade inside each department. Target: 30 rows · department, grade, results · (Commerce, B, 133); (Commerce, A, 119) …

    SELECT c.department, e.grade, COUNT(*) AS results FROM courses c JOIN exam_results e ON e.course_id = c.course_id GROUP BY c.department, e.grade ORDER BY c.department, results DESC, e.grade;

  37. Professional. List students enrolled in more than 3 courses. Target: 20 rows · student_id, student_name, courses · (364, Pranav Reddy, 9); (91, Harpreet Reddy, 8) …

    SELECT s.student_id, s.student_name, COUNT(DISTINCT e.course_id) AS courses FROM students s JOIN enrollments e ON e.student_id = s.student_id GROUP BY s.student_id, s.student_name HAVING COUNT(DISTINCT e.course_id) > 3 ORDER BY courses DESC, s.student_id LIMIT 20;

  38. Professional. Rank students by average marks. Target: 20 rows · student_name, avg_marks, rnk · (Saanvi Gill, 92.6, 1); (Anaya Reddy, 92, 2) …

    WITH per AS (SELECT s.student_id, s.student_name, AVG(e.marks) AS avg_marks FROM students s JOIN exam_results e ON e.student_id = s.student_id GROUP BY s.student_id, s.student_name) SELECT student_name, ROUND(avg_marks, 2) AS avg_marks, RANK() OVER (ORDER BY avg_marks DESC) AS rnk FROM per ORDER BY rnk, student_id LIMIT 20;

  39. Professional. Show 2025 results per month with a running total. Target: 12 rows · month, results, running_total · (2025-01, 84, 84); (2025-02, 72, 156) …

    WITH m AS (SELECT DATE_FORMAT(exam_date, '%Y-%m') AS month, COUNT(*) AS results FROM exam_results WHERE exam_date >= '2025-01-01' AND exam_date < '2026-01-01' GROUP BY month) SELECT month, results, SUM(results) OVER (ORDER BY month) AS running_total FROM m ORDER BY month;

  40. Professional. Show the month-over-month change in exam results for 2025. Target: 12 rows · month, results, prev_results, change · (2025-01, 84, NULL, NULL); (2025-02, 72, 84, -12) …

    WITH m AS (SELECT DATE_FORMAT(exam_date, '%Y-%m') AS month, COUNT(*) AS results FROM exam_results WHERE exam_date >= '2025-01-01' AND exam_date < '2026-01-01' GROUP BY month) SELECT month, results, LAG(results) OVER (ORDER BY month) AS prev_results, results - LAG(results) OVER (ORDER BY month) AS change FROM m ORDER BY month;

  41. Professional. List courses whose average marks beat their department's average. Target: 20 rows · course_name, department, avg_marks, dept_avg · (Commerce Fundamentals 10, Commerce, 74.09, 68.88); (Commerce Fundamentals 5, Commerce, 71.2, 68.88) …

    WITH course_avg AS (SELECT c.course_id, c.course_name, c.department, AVG(e.marks) AS avg_marks FROM courses c JOIN exam_results e ON e.course_id = c.course_id GROUP BY c.course_id, c.course_name, c.department), dept_avg AS (SELECT department, AVG(avg_marks) AS dept_avg FROM course_avg GROUP BY department) SELECT ca.course_name, ca.department, ROUND(ca.avg_marks, 2) AS avg_marks, ROUND(da.dept_avg, 2) AS dept_avg FROM course_avg ca JOIN dept_avg da ON da.department = ca.department WHERE ca.avg_marks > da.dept_avg ORDER BY ca.department, ca.avg_marks DESC LIMIT 20;

  42. Professional. List students who have failed at least one exam. Target: 20 rows · student_id, student_name · (1, Aman Iyer); (3, Aarav Gill) …

    SELECT s.student_id, s.student_name FROM students s WHERE EXISTS (SELECT 1 FROM exam_results e WHERE e.student_id = s.student_id AND e.grade = 'F') ORDER BY s.student_id LIMIT 20;

  43. Professional. Show each student's first exam date. Target: 20 rows · student_id, student_name, first_exam · (37, Yash Singh, 2025-11-02); (46, Simran Verma, 2025-11-01) …

    SELECT s.student_id, s.student_name, MIN(e.exam_date) AS first_exam FROM students s JOIN exam_results e ON e.student_id = s.student_id GROUP BY s.student_id, s.student_name ORDER BY first_exam DESC, s.student_id LIMIT 20;

  44. Professional. Show each department's share of all enrollments. Target: 5 rows · department, enrollments, pct · (Commerce, 325, 27.1); (Data Science, 272, 22.7) …

    SELECT c.department, COUNT(*) AS enrollments, ROUND(100 * COUNT(*) / (SELECT COUNT(*) FROM enrollments), 1) AS pct FROM enrollments e JOIN courses c ON c.course_id = e.course_id GROUP BY c.department ORDER BY enrollments DESC, c.department;

  45. Professional. List students whose average marks beat the overall average. Target: 20 rows · student_id, student_name, avg_marks · (6, Saanvi Gill, 92.6); (21, Anaya Reddy, 92) …

    WITH per AS (SELECT student_id, AVG(marks) AS avg_marks FROM exam_results GROUP BY student_id) SELECT s.student_id, s.student_name, ROUND(p.avg_marks, 2) AS avg_marks FROM per p JOIN students s ON s.student_id = p.student_id WHERE p.avg_marks > (SELECT AVG(marks) FROM exam_results) ORDER BY p.avg_marks DESC, s.student_id LIMIT 20;

  46. Professional. Build a semester dashboard: enrollments, results and average marks. Target: 4 rows · semester, enrollments, results, avg_marks · (2024-S1, 289, 1423, 67.9); (2024-S2, 291, 1481, 68.1) …

    WITH en AS (SELECT semester, COUNT(*) AS enrollments FROM enrollments GROUP BY semester), res AS (SELECT e.semester, COUNT(*) AS results, ROUND(AVG(r.marks), 2) AS avg_marks FROM enrollments e JOIN exam_results r ON r.student_id = e.student_id GROUP BY e.semester) SELECT en.semester, en.enrollments, res.results, res.avg_marks FROM en LEFT JOIN res ON res.semester = en.semester ORDER BY en.semester;

  47. Professional. Show the best student in each department by average marks. Target: 5 rows · department, student_name, avg_marks · (Commerce, Meera Verma, 100); (Computer Science, Aarav Gill, 100) …

    WITH per AS (SELECT s.student_id, s.student_name, c.department, AVG(e.marks) AS avg_marks FROM students s JOIN exam_results e ON e.student_id = s.student_id JOIN courses c ON c.course_id = e.course_id GROUP BY s.student_id, s.student_name, c.department), r AS (SELECT department, student_name, avg_marks, ROW_NUMBER() OVER (PARTITION BY department ORDER BY avg_marks DESC, student_id) AS rn FROM per) SELECT department, student_name, ROUND(avg_marks, 2) AS avg_marks FROM r WHERE rn = 1 ORDER BY department;

  48. Professional. List courses that have never been examined. Target: 0 rows · course_id, course_name · 

    SELECT c.course_id, c.course_name FROM courses c WHERE NOT EXISTS (SELECT 1 FROM exam_results e WHERE e.course_id = c.course_id) ORDER BY c.course_id;

  49. Professional. Show the grade distribution per program. Target: 30 rows · program, grade, results · (BA Economics, A, 83); (BA Economics, A+, 43) …

    SELECT s.program, e.grade, COUNT(*) AS results FROM students s JOIN exam_results e ON e.student_id = s.student_id GROUP BY s.program, e.grade ORDER BY s.program, e.grade;

  50. Professional. Show the 2025 pass rate per month. Target: 12 rows · month, pass_pct · (2025-01, 82.1); (2025-02, 88.9) …

    SELECT DATE_FORMAT(exam_date, '%Y-%m') AS month, ROUND(100 * SUM(CASE WHEN marks >= 50 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_pct FROM exam_results WHERE exam_date >= '2025-01-01' AND exam_date < '2026-01-01' GROUP BY month ORDER BY month;

Up next · Lesson 20

Real life: ride-hailing

200 min · 50 practice problems

Start next lesson
Real life: e-commerce marketplace Real life: ride-hailing