Model student-course relation and query enrollments
Filed under Oracle
Free read 1 of 3Design an intermediate `Enrollment(sid, cid)` table linking Student and Course.
The question
Given tables Student(sid, sname) and Course(cid, cname) in a many-to-many relationship, design the relational schema and write SQL queries to find the number of students enrolled in each course, and the course with the highest enrollment.
Constraints
- Must support many-to-many relationship mapping.
- Queries must handle aggregate counts correctly.
Examples
- Input
- Students enrolled in courses via an intermediate Enrollment table.
- Output
- List of courses with student counts, and top course.
- Why
- Uses GROUP BY, COUNT, and ordering/limiting constructs.
Approach
Design an intermediate `Enrollment(sid, cid)` table linking Student and Course.
Define primary keys, foreign keys, and referential integrity rules.
Write the SQL query using `GROUP BY` and `COUNT` to aggregate student numbers per course.
Discuss indexing foreign keys for faster join performance.
Handling orphan records or missing student entries.
Tips
- What they are testing: Verify schema normalization, foreign key constraints, and correct handling of courses with zero enrollments.
- Common mistake: Omitting the junction table for many-to-many relationships or mishandling ties in maximum enrollment queries.
Related Oracle questions
Collected from public candidate posts and coding forums. Updated Nov 2026.