By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Student
(
sID INT NOT NULL,
sName VARCHAR(30) NOT NULL,
GPA FLOAT NOT NULL,
sizeHS INT NOT NULL,
PRIMARY KEY (sID)
);
CREATE TABLE Apply
(
decision SET('Y', 'N') NOT NULL,
sID INT NOT NULL,
cName VARCHAR(20) NOT NULL,
major VARCHAR(30) NOT NULL
);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (123, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (234, 'Bob', 3.60, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (345, 'Craig', 3.50, 500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (456, 'Doris', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (543, 'Craig', 3.40, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (567, 'Edward', 2.90, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (654, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (678, 'Fay', 3.80, 200);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (765, 'Jay', 2.90, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (789, 'Gary', 3.40, 800);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (876, 'Irene', 3.90, 400);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (987, 'Helen', 4.00, 800);
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'Cornell', 'EE', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'EE', 'N');
SELECT DISTINCT sName,
Student.sID ,
cName
FROM Student
LEFT JOIN Apply ON Student.sID = Apply.sID
where cName = 'WSU'
group by sID
WHERE cName<>'U of O' and cName = 'WSU'
ORDER BY sName ASC;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE cName<>'U of O' and cName = 'WSU'
ORDER BY sName ASC' at line 8
SELECT sID,sName
FROM Student
WHERE exists (SELECT sid FROM Apply WHERE Student.sID = Apply.sID and cname = 'WSU')
AND not exists (SELECT sid FROM Apply WHERE Student.sID = Apply.sID AND cname = 'U of O')
ORDER BY sName ASC;
| sID | sName |
|---|---|
| 678 | Fay |
| 876 | Irene |
| 765 | Jay |