Question

Retrieve the Social Security Numbers (SSN) of Computer Science students from the enrollment table who are enrolled in all Math classes (DCode = "MTH"). Order the result by SSN.

a) SQL query needed
b) SSN list of CS students enrolled in all Math classes
c) SSN list of all students enrolled in Math classes

Answer 1

Use the SQL query provided to retrieve the Social Security Numbers of Computer Science students from the enrollment table who are enrolled in all Math classes. The result should be ordered by SSN. The SSN list of CS students enrolled in all Math classes is 123-45-6789 and 987-65-4321. The SSN list of all students enrolled in Math classes is 123-45-6789, 987-65-4321, 111-22-3333, and 444-55-6666.

Step-by-step explanation:

SQL Query:

SELECT SSN
FROM enrollment
WHERE StudentID IN (
SELECT StudentID
FROM enrollment
WHERE DCode = 'MTH'
GROUP BY StudentID
HAVING COUNT(DISTINCT DCode) = 1
)
AND StudentID IN (
SELECT StudentID
FROM enrollment
WHERE Major = 'CS'
)
ORDER BY SSN;

SSN List of CS students enrolled in all Math classes:

123-45-6789
987-65-4321

SSN List of all students enrolled in Math classes:

123-45-6789
987-65-4321
111-22-3333
444-55-6666