How do I write SQL queries for a student course registration database?

SQL assignments become much easier when you start from relationships. In a course registration system, students and courses usually have a many-to-many relationship, resolved by an Enrolments table. Most useful queries pass through that table.

Typical schema

textStudents(student_id, name, email)
Courses(course_id, title, department_id, credits)
Departments(department_id, department_name)
Enrolments(student_id, course_id, semester, grade)

Primary keys identify rows. Foreign keys connect tables. In this design, Enrolments.student_id points to Students.student_id, and Enrolments.course_id points to Courses.course_id.

Query pattern

1. 1Decide what the output should show.
2. 2Identify which tables contain those columns.
3. 3Join through the relationship keys.
4. 4Add filters in WHERE.
5. 5Use GROUP BY only when summarising.
6. 6Use HAVING for conditions on grouped results.

Mini query examples

List students enrolled in Database Systems:

sqlSELECT s.student_id, s.name, c.title
FROM students s
JOIN enrolments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id
WHERE c.title = 'Database Systems';

Count enrolments per course:

sqlSELECT c.course_id, c.title, COUNT(*) AS enrolment_count
FROM courses c
JOIN enrolments e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title;

The second query groups by course because one output row should represent one course.

Sample university-style questions and how to answer them

Common mistakes

• Joining tables without using the correct key
• Using WHERE for aggregate conditions that belong in HAVING
• Selecting non-grouped columns in a grouped query
• Forgetting the Enrolments table in many-to-many relationships
• Using COUNT(column) when COUNT(*) is clearer for counting rows
• Not explaining what the query result means

Testing notes

Create small sample data and predict the result before running queries. For example, if Course A has three enrolment rows, your count query should return 3. If it returns 9, you probably created a duplicate-producing join.

Academic use note

This guide is for database assignment support. SQL dialects vary slightly, so follow your module's database system where syntax differs.

Sources and further reading

• PostgreSQL documentation: SELECT
• PostgreSQL documentation: table expressions and joins