Find IDs and names of students who took CS-101 and did not take CS-319

Can someone help with this homework problem:

Find IDs and names of students who took CS-101 and did not take CS-319.

These are the two tables that I can use for this assignment:

TAKES (Table)

ID (PK)

course_id (PK)

sec_id (PK)

semester (PK)

year (PK)

grade



STUDENT (Table)

ID (PK)

name

dept_name

tot_cred

I tried this but was unable to successfully exclude the students who who took “CS-319”

     SELECT ID, name, course_id
     FROM (
          SELECT ID, name, course_id
          FROM student NATURAL JOIN takes
          WHERE course_id != "CS-319") AS T1
     WHERE course_id = "CS-101"

Answer

There are at least three separate ways to solve this problem in SQL.

1. Set operations ( SELECT then exclude using EXCEPT ).
   • As MySQL doesn’t support EXCEPT we can use NOT IN instead.
2. Anti-join (LEFT OUTER JOIN).
3. Join and discriminate on COUNT.

Approach 1: SELECT then exclude using EXCEPT:

SELECT
    student_id
FROM
    takes
WHERE
    course_id = 'CS-101'

EXCEPT

SELECT
    student_id
FROM
    takes
WHERE
    course_id = 'CS-319'

Then use this as an inner-query to JOIN on student information:

SELECT
    iq.student_id,
    student.name
FROM
    (
        SELECT
            student_id
        FROM
            takes
        WHERE
            course_id = 'CS-101'

        EXCEPT

        SELECT
            student_id
        FROM
            takes
        WHERE
            course_id = 'CS-319'
    ) AS iq

    INNER JOIN students ON iq.student_id = students.student_id

Approach 1 for MySQL (using NOT IN):

SELECT
    iq.student_id,
    student.name
FROM
    (
        SELECT
            student_id
        FROM
            takes
        WHERE
            course_id = 'CS-101'
            AND
            student_id NOT IN
            (
                SELECT
                    student_id
                FROM
                    takes
                WHERE
                    course_id = 'CS-319'
            )
    ) AS iq

    INNER JOIN students ON iq.student_id = students.student_id

Approach 2: Anti-join:

SELECT
    s.student_id,
    s.name
FROM
    students AS S

    LEFT OUTER JOIN
    (
        SELECT
            student_id,
            COUNT(*) AS Cs101Count
        FROM
            takes
        WHERE
            course_id = 'CS-101'
        GROUP BY
            student_id
    ) AS c1 ON s.student_id = c1.student_id

    LEFT OUTER JOIN
    (
        SELECT
            student_id,
            COUNT(*) AS Cs319Count
        FROM
            takes
        WHERE
            course_id = 'CS-319'
        GROUP BY
            student_id
    ) AS c2 ON s.student_id = c2.student_id
WHERE
    c1.Cs101Count > 0
    AND
    c2.cS319Count = 0

Approach 3: Join and discriminate on COUNT:

SELECT
    s.student_id,
    s.name
FROM
    students AS S
    INNER JOIN
    (
        SELECT
            student_id,
            COUNT( CASE WHEN course_id = 'CS-101' END ) AS Cs101Count,
            COUNT( CASE WHEN course_id = 'CS-319' END ) AS Cs319Count
        FROM
            takes
        GROUP BY
            student_id
    ) AS c ON c.student_id = s.student_id
WHERE
    c.Cs101Count > 0
    AND
    c.Cs319Count = 0

More approaches:

There are other alternatives using WHERE EXISTS, WHERE NOT EXISTS, and so on. One of the problems (or challenges) when using SQL is there are different ways to query the source data to get the exact same results. Have fun!