MySQL查询练习--答案1
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号;
SELECT * FROM
(SELECT * FROM sc WHERE cno='c001')a,
(SELECT * FROM sc WHERE cno='c002')b
WHERE a.sno=b.sno AND a.score>b.score;
2、查询平均成绩大于60 分的同学的学号和平均成绩;
SELECT sno,AVG(score)平均成绩 FROM sc
GROUP BY sno
HAVING AVG(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩;
SELECT a.*,s.sname FROM
(SELECT sno 学号,SUM(score)总成绩,COUNT(cno)选课数 FROM sc
GROUP BY sno) a ,student s WHERE a.sno=s.sno
4、查询姓“刘”的老师的个数;
SELECT COUNT(tname) FROM teacher
WHERE tname LIKE '刘%'
5、查询没学过“谌燕”老师课的同学的学号、姓名;
SELECT * FROM student st WHERE st.sno NOT IN
(SELECT DISTINCT sno FROM sc s JOIN course c ON s.cno=c.cno
JOIN teacher t ON c.tno=t.tno WHERE tname='谌燕')
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
SELECT * FROM student WHERE sno IN
(SELECT a.sno FROM(SELECT sno FROM sc s WHERE s.cno='c001')a
,(SELECT sno FROM sc s WHERE s.cno='c002')b WHERE a.sno=b.sno);
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名;
SELECT * FROM student st WHERE st.sno IN
(SELECT DISTINCT sno FROM sc s JOIN course c ON s.cno=c.cno
JOIN teacher t ON c.tno=t.tno WHERE tname='谌燕')
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名;
SELECT * FROM student st
JOIN sc a ON st.sno=a.sno
JOIN sc b ON st.sno=b.sno
WHERE a.cno='c002' AND b.cno='c001' AND a.score < b.score
9、查询所有课程成绩小于60 分的同学的学号、姓名;
SELECT st.*,s.score FROM student st
JOIN sc s ON st.sno=s.sno
JOIN course c ON s.cno=c.cno
WHERE s.score <60
10、查询没有学全所有课的同学的学号、姓名;
SELECT stu.sno,stu.sname,COUNT(sc.cno) FROM student stu
LEFT JOIN sc ON stu.sno=sc.sno
GROUP BY stu.sno,stu.sname
HAVING COUNT(sc.cno)<(SELECT COUNT(DISTINCT cno)FROM course)