1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
(1)
SELECT c.SId,c.score_01,c.score_02,student.Sname,Sage,Ssex
FROM
(SELECT a.SId,a.score_01,b.score_02
FROM
(SELECT SId,score AS score_01 FROM sc WHERE CId='01') a
JOIN (SELECT SId,score AS score_02 FROM sc WHERE CId='02') b
ON a.SId=b.SId
WHERE a.score_01>b.score_02) c
JOIN student
ON student.SId=c.SId;
(2)
SELECT student.*,
01_score.CId,
1_score,
02_score.CId,
2_score
FROM
student
JOIN (SELECT SId,CId,score AS 1_score FROM sc WHERE CId = '01')AS 01_score ON student.SId = 01_score.SId
JOIN (SELECT SId,CId,score AS 2_score FROM sc WHERE CId = '02') AS 02_score ON 01_score.SId = 02_score.SId
WHERE
1_score > 2_score;
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT student.*,a.avgscore
FROM student
JOIN
(SELECT SId,AVG(score) AS avgscore
FROM sc
GROUP BY SId
HAVING AVG(score)>60) a
ON a.SId=student.SId;
3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT student.SId,student.Sname,a.选课总数,a.总成绩
FROM student
JOIN
(SELECT SId,COUNT(CId) AS 选课总数,SUM(score) AS 总成绩
FROM sc
GROUP BY SId)a
ON a.SId=student.SId;
4.查询姓”张“的老师个数
SELECT COUNT(1) FROM teacher WHERE Tname LIKE '张%';
5.查询没学过张三老师课的学生的学号和姓名
SELECT a.SId,student.Sname
FROM student
JOIN
(SELECT SId
FROM sc
WHERE sc.CId=(SELECT Cid
FROM course
JOIN (SELECT Tid FROM teacher WHERE Tname='张三') a
ON course.Tid=a.Tid)) a
ON a.SId=student.SId
