Select * from student where ’01’ is higher than ’02’; select * from student where ’02’ is higher than ’01’; select * from student where ’01’ is higher than ’02’;
SELECT * FROM sc a, sc b, student c WHERE a.SId = b.SId and a.SId = c.SId AND a.CId = 01 AND b.CId = 02 AND a.score > b.score
1.1 Querying courses “01” and “02” exist at the same time
SELECT * FROM sc a, sc b WHERE a.SId = b.SId AND a.CId = 01 AND b.CId = 02
1.2 Query exists “01” course but may not exist “02” course (null if not)
SELECT * FROM sc a LEFT JOIN sc b ON a.SId = b.SId AND b.CId = 02 WHERE a.CId = 01
1.3 Querying the case where “01” course does not exist but “02” course exists
SELECT * FROM sc b WHERE b.SId NOT IN ( SELECT a.SId FROM sc a WHERE a.CId = 01 ) and b.CId = 02
2. Select student id, student name, and student gpa from student whose gpa is 60 or higher
SELECT AVG( a.score ) AS score, b.sid, b.sname FROM sc a, student b WHERE a.SId = b.SId GROUP BY a.SId HAVING AVG( a.score )> 60
3. Query information about students whose grades exist in SC table
SELECT DISTINCT b.* FROM sc a, student b WHERE a.SId = b.SId;
Select student id, student name, total number of courses, total score of all courses (null if no result is displayed)
SELECT a.sid, a.sname, count( b.cid ), sum( b.score ) FROM student a LEFT JOIN sc b ON a.SId = b.SId GROUP BY a.sid
4.1 Check the information of students with grades
SELECT a.sid, a.sname, count( b.cid ), sum( b.score ) FROM student a JOIN sc b ON a.SId = b.SId GROUP BY a.sid
5. Query the number of teachers whose surname is Li
SELECT count(*) FROM teacher WHERE t name LIKE ‘li %’
6. Query information about students taught by “John”
SELECT a.* FROM student a, sc b, SELECT sid FROM teacher WHERE sid = b.id AND sid = c.id AND sid = (SELECT sid FROM teacher WHERE Tname = ‘zhang3’)
Select * from student where not enrolled in all courses
SELECT * FROM student a JOIN sc b ON a.SId = b.SId GROUP BY a.SId HAVING count( b.CId ) < ( SELECT count(*) FROM course)
Select * from student where student id is’ 01 ‘and student in at least one course
SELECT c.* FROM sc b, student c WHERE b.SId = c.SId AND b.SId ! = 01 AND b.CId IN ( SELECT a.CId FROM sc a WHERE a.SId = 01 ) GROUP BY sid
Query information about other students who are taking the same course as student “01
SELECT sid FROM sc WHERE sid NOT IN ( SELECT sid FROM sc a WHERE a.cid NOT IN ( SELECT cid FROM sc WHERE SId = ’01’ )) AND sid ! = ’01’ GROUP BY sid HAVING count( 1 ) =( SELECT count(*) FROM sc WHERE sid = ’01’)
Select * from student where no student has taken any courses taught by Mr. John
SELECT * FROM student d WHERE d.SId NOT IN ( SELECT a.SId FROM sc a WHERE a.CId = ( SELECT c.CId FROM course c WHERE C.id = (SELECT b.id FROM teacher b WHERE b.name = ‘张三’))