โ† Oracle questions

Model student-course relation and query enrollments

Filed under Oracle

Free read 1 of 3

Design 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

  1. Design an intermediate `Enrollment(sid, cid)` table linking Student and Course.

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.