摘要：
-- 1、查询“01”课程比“02”课程成绩高的所有学生的学号；
SELECT a.sid
FROM
(SELECT sid, score
FROM sc
WHERE cid=01) a,
(SELECT sid ,score
FROM sc
WHERE cid=02) b
WHERE a.score > b.score AND a.sid= b.sid;
SELECT a.sid
FROM student a
LEFT JOIN SC b ON b.cid=01 AND a.sid=b.sid
LEFT JOIN SC c ON c.cid=02 AND a.sid=c.sid
WHERE b.score>c.score;
-- 2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid 学号, AVG(score) 平均成绩
FROM sc
GROUP BY sid
HAVING 平均成绩 > 60;
SELECT sid, AVG(score)
FROM sc
GROUP BY sid
HAVING AVG(score)>60;
-- 3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT s.sid 学号, s.sname 姓名, COUNT(*) 选棵数, SUM(score) 总成绩
FROM student s, sc
WHERE s.sid = sc.sid
GROUP BY sc.sid;
SELECT s.sid, sname, COUNT(*), SUM(score)
FROM student s
LEFT JOIN sc ON s.sid = sc.sid
GROUP BY sc.sid;
2022-03-27 15:20:44
18KB
1