加载中...

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)
posted @ 2022-04-26 15:45  键天帝  阅读(1019)  评论(0编辑  收藏  举报