Courses Nobody Registered For in the Odd Semester

Easy
124.

Courses Nobody Registered For in the Odd Semester

The academic office wants to drop courses that drew no registrations for the 2024-25 Odd semester. Registrations from other semesters do not count — a course popular last year but empty this semester must still be listed.

Return every course with no registration in the '2024-25 Odd' semester, with the columns course_code and title. Return the rows in any order.

Tables

Table: Course

ColumnType
course_codevarchar
titlevarchar
creditsint

Primary key: course_code.

One row per course in the catalogue.

Table: Registration

ColumnTypeValues
registration_idint
roll_noint
course_codevarchar
semesterenum2023-24 Even, 2024-25 Odd, 2024-25 Even

Primary key: registration_id.

One row per student registering for a course in a semester; course_code is always in Course.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Course

course_codetitlecredits
CS301Operating Systems4
CS302Computer Networks4
MA201Probability and Statistics3
HS101Professional Communication2
EC210Digital Electronics3

Registration

registration_idroll_nocourse_codesemester
12101CS3012024-25 Odd
22102CS3012024-25 Odd
32101MA2012023-24 Even
42103CS3022024-25 Odd
52104HS1012024-25 Even
62102MA2012023-24 Even

Output

course_codetitle
MA201Probability and Statistics
HS101Professional Communication
EC210Digital Electronics
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Courses Nobody Registered For in the Odd Semester

The academic office wants to drop courses that drew no registrations for the 2024-25 Odd semester.

  • Difficulty: Easy
  • Topics: Joins, Subqueries
  • Dialect: MySQL
  • Problem: #124

Problem statement

The academic office wants to drop courses that drew no registrations for the 2024-25 Odd semester. Registrations from other semesters do not count — a course popular last year but empty this semester must still be listed.

Return every course with no registration in the '2024-25 Odd' semester, with the columns course_code and title. Return the rows in any order.

Tables

Table: Course

ColumnType
course_codevarchar
titlevarchar
creditsint

Primary key: course_code.

One row per course in the catalogue.

Table: Registration

ColumnType
registration_idint
roll_noint
course_codevarchar
semesterenum(2023-24 Even, 2024-25 Odd, 2024-25 Even)

Primary key: registration_id.

One row per student registering for a course in a semester; course_code is always in Course.

Examples

Example 1

Course

course_codetitlecredits
CS301Operating Systems4
CS302Computer Networks4
MA201Probability and Statistics3
HS101Professional Communication2
EC210Digital Electronics3

Registration

registration_idroll_nocourse_codesemester
12101CS3012024-25 Odd
22102CS3012024-25 Odd
32101MA2012023-24 Even
42103CS3022024-25 Odd
52104HS1012024-25 Even
62102MA2012023-24 Even

Output

course_codetitle
MA201Probability and Statistics
HS101Professional Communication
EC210Digital Electronics

How to solve Courses Nobody Registered For in the Odd Semester

This is an anti join with a twist: only registrations of one semester count. The natural form is a LEFT JOIN from Course to Registration and a test for the missing partner, r.registration_id IS NULL.

The semester condition must sit in the ON clause. There it limits which registrations may match, so a course with registrations only in other semesters finds no partner and comes through with NULLs. Written in WHERE instead, r.semester = '2024-25 Odd' is evaluated after the join; on the unmatched rows r.semester is NULL, the condition is unknown, and every course you wanted is filtered away — while a course with only old registrations is dropped too.

NOT EXISTS with the semester inside the subquery says the same thing directly, and NOT IN is safe here because course_code is never NULL in Registration. All three read each course once and probe the registrations by course code.

Reference solution (MySQL)

SELECT c.course_code, c.title
FROM Course c
LEFT JOIN Registration r
  ON r.course_code = c.course_code AND r.semester = '2024-25 Odd'
WHERE r.registration_id IS NULL

Another way

SELECT course_code, title FROM Course c WHERE NOT EXISTS (SELECT 1 FROM Registration r WHERE r.course_code = c.course_code AND r.semester = '2024-25 Odd')

Another way

SELECT course_code, title FROM Course WHERE course_code NOT IN (SELECT course_code FROM Registration WHERE semester = '2024-25 Odd')

← Students Short of 75% Attendance in a Course · Fee Invoices Paid Late or Still Overdue →