So I have this problem in SQL where the question is this: List the sId and name of students that applied to “WSU” But not “U of O”. and my attempt was this:
SELECT DISTINCT sName,
student.sID
FROM Student
LEFT JOIN Apply ON Student.sID = Apply.sID
WHERE (cName<>'U of O' and cName = 'WSU')
ORDER BY sName ASC;
However this does not do the trick as it doesn't catch that some of the students had already applied to U of O. MRE:
CREATE TABLE IF NOT EXISTS College
(
State CHAR(2) NOT NULL,
cName VARCHAR(20) NOT NULL,
enrollment INT NOT NULL,
PRIMARY KEY (cName)
);
CREATE TABLE IF NOT EXISTS Major
(
major VARCHAR(30) NOT NULL,
PRIMARY KEY (major)
);
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)
);
CREATE TABLE IF NOT EXISTS MinimumGPA
(
minGPA FLOAT NOT NULL,
cName VARCHAR(20) NOT NULL,
major VARCHAR(30) NOT NULL,
PRIMARY KEY (cName, major),
FOREIGN KEY (cName) REFERENCES College(cName),
FOREIGN KEY (major) REFERENCES Major(major)
);
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),
FOREIGN KEY (cName, major) REFERENCES MinimumGPA(cName, major)
);
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 College (cName, State, enrollment) VALUES ('Cornell', 'NY', 21000);
INSERT INTO College (cName, State, enrollment) VALUES ('MIT', 'MA', 10000);
INSERT INTO College (cName, State, enrollment) VALUES ('WSU', 'WA', 28000);
INSERT INTO College (cName, State, enrollment) VALUES ('U of O', 'OR', 25000);
INSERT INTO Major (major) VALUES ('CS');
INSERT INTO Major (major) VALUES ('EE');
INSERT INTO Major (major) VALUES ('history');
INSERT INTO Major (major) VALUES ('biology');
INSERT INTO Major (major) VALUES ('bioengineering');
INSERT INTO Major (major) VALUES ('psychology');
INSERT INTO Major (major) VALUES ('marine biology');
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'CS', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'EE', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'history', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'CS', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'biology', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','bioengineering', 3.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','CS', 3.4);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','EE', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','history', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','psychology', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'biology', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'bioengineering', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'CS', 3.9);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'marine biology', 3.5);
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');
CREATE TABLE IF NOT EXISTS CollegeStats
(
cName VARCHAR(20) NOT NULL,
appCount INT NOT NULL,
minGPA dec(3, 2) NOT NULL,
maxGPA dec(3, 2) NOT NULL,
PRIMARY KEY (cName)
);
And then of course the function at the end that i am trying to work on:
SELECT DISTINCT sName,
student.sID
FROM Student
LEFT JOIN Apply ON Student.sID = Apply.sID
WHERE (cName<>'U of O' and cName = 'WSU')
ORDER BY sName ASC;
what i get:
'Amy', '123'
'Fay', '678'
'Helen', '987'
'Irene', '876'
'Jay', '765'
expected:
'Fay', '678'
'Irene', '876'
'Jay', '765'
SELECT * FROM Student RIGHT JOIN Apply ON Student.sID = Apply.sID ORDER BY sName ASC;
SELECT DISTINCT sName, student.sID FROM Student LEFT JOIN Apply ON Student.sID = Apply.sID WHERE (cName<>'U of O' and cName = 'WSU') ORDER BY sName ASC;
The relation above what i am trying to accomplish should very nicely list all of the schools and majors the students applied to.
CodePudding user response：
You don't need a join. You want to see students? So, select from the students table. They shall meet criteria? Use a where clause. Straight-forward with IN clauses:
SELECT *
FROM Student
WHERE sid IN (SELECT sid FROM apply WHERE cname = 'WSU')
AND sid NOT IN (SELECT sid FROM apply WHERE cname = 'U of O')
ORDER BY sName ASC;
CodePudding user response：
You could use EXISTS and not EXISTS which might perform faster for larger data. Is the same as @Thorsten Kettner answer , only replaced in with exists.
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;
Result:
sID sName 678 Fay 876 Irene 765 Jay
CodePudding user response：
You can use aggregation and set the conditions in the HAVING clause:
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;
or:
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;
See the demo.