By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS Student
(
sID INT NOT NULL,
sName VARCHAR(30) NOT NULL,
GPA FLOAT NOT NULL,
sizeHS INT NOT NULL,
PRIMARY KEY (sID)
);
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);
CREATE TABLE IF NOT EXISTS Apply
(
decision SET('Y', 'N') NOT NULL,
sID INT NOT NULL,
cName VARCHAR(20) NOT NULL,
major VARCHAR(30) NOT NULL,
PRIMARY KEY (sID, cName, major),
FOREIGN KEY (sID) REFERENCES Student(sID)
);
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');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'U of O', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (234, 'U of O', 'biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'bioengineering', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'EE', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'MIT', 'bioengineering', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (543, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'history', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'psychology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (765, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'biology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'marine biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'WSU', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'U of O', 'CS', 'Y');
SELECT s.sID, s.sName
FROM Student s INNER JOIN Apply a
ON a.sID = s.sID
WHERE a.cName IN ('WSU', 'U of O')
GROUP BY s.sID
HAVING COUNT(CASE WHEN a.cName = 'U of O' THEN 1 END) = 0
ORDER BY s.sName ASC;
| sID | sName |
|---|---|
| 678 | Fay |
| 876 | Irene |
| 765 | Jay |
SELECT s.sID, s.sName
FROM Student s INNER JOIN Apply a
ON a.sID = s.sID
GROUP BY s.sID
HAVING COUNT(CASE WHEN a.cName = 'WSU' THEN 1 END) > 0
AND COUNT(CASE WHEN a.cName = 'U of O' THEN 1 END) = 0
ORDER BY s.sName ASC;
| sID | sName |
|---|---|
| 678 | Fay |
| 876 | Irene |
| 765 | Jay |