How to compare two sets in MySQL or SQL SERVER?

In this University Database:

CREATE TABLE Stud(
    [S#] INT PRIMARY KEY,
    Sname NVARCHAR(50),
    City NVARCHAR(50),
    GPA FLOAT,
    [Clg#] SMALLINT
);

CREATE TABLE Sec(
    [Sec#] SMALLINT,
    [C#] INT,
    [S#] INT,
    Semester SMALLINT,
    Pname NVARCHAR(50),
    Score NVARCHAR(30),
    PRIMARY KEY([Sec#], [C#], Semester)
);

Stud
Sec#,C#,S#,Semester,Pname,Score
1724,10172,1133848,161,Sullivan,62%
1516,51516,4182532,152,Cooper,73%
1747,10174,1133848,152,Sullivan,75%
1747,10174,2130502,153,Sullivan,55%
1540,10156,2203305,161,Sullivan,60%
1540,10156,1133848,181,Sullivan,87%
1540,10156,2203305,182,Sullivan,40%
1802,10156,4182532,192,Sullivan,60%

Sec
S#,Sname,City,GPA,Clg#
1133848,Parker,NY,3.8,10
2130502,White,Chicago,2.8,10
2203305,Gardner,LA,3.5,1
3120504,West,Boston,3.85,4
3166801,Warren,Seattle,3.2,5
4182532,Rogers,NY,3.3,5
4209836,Allen,Austin,1.5,6

I have this question:

Find names and IDs of students who have had ALL the courses that teacher Sullivan has taught.

I can easily find the answer with relational algebra:

Π ????#,????????????????????,????#(???????????????? ⋈ ????????????) ÷ Π c#(σ pname="Sullivan" (sec))

How do I implement the Division operator in SQL?

And our teacher gave us this answer for SQL SERVER:

???????????????????????? ????????????????.????#, ????????????????????
???????????????? ????????????????, ????????????
???????????????????? ????????????????.????# = ????????????.????#
???????????????????? ???????? ????????????????.????#, ????????????????????
???????????????????????? ????# ???????????????????????????????? (???????????????????????? DISTINCT ????#
???????????????? ????????????
WHERE pname ="Sullivan");

And from the slides of our online class, this is what they mean by CONTAINS:

But from what I have googled, the contains function has nothing to do with sets, it seems to be a full-indexed test searching function. Please correct me if I’m wrong.

After a while, I came up with this solution:

SELECT Stud.* FROM Stud WHERE S# IN
(SELECT S#/*,COUNT(DISTINCT C#) as c*/ FROM Sec WHERE Pname='Sullivan' GROUP BY S# HAVING COUNT(DISTINCT C#)=
(SELECT COUNT(DISTINCT [C#]) FROM Sec WHERE Pname='Sullivan'));

Answer

Gag. This would seem to answer the question:

SELECT stud.s#, stud.sname
FROM stud s JOIN
     sec
     ON stud.s# = sec.s# 
WHERE secs.pname = 'Sullivan'
GROUP BY stud.s#, sname
HAVING COUNT(DISTINCT sec.c#) = (SELECT COUNT(DISTINCT sec2.c# FROM sec sec2 WHERE sec2.pname = 'Sullivan');

Let me say that I’m not impressed with the teacher:

Never use commas in the FROM clause.
Always use proper, explicit, standard, readable JOIN syntax.
CONTAINS() is a SQL Server full-text function. It doesn’t exist in MySQL. And I have not heard of its use as a set operator.
Spaces after the . is non-standard . . . and I would suggest table aliases as well.

Advertisement

Answer