数据库之多表查询.

1、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cno ,MAX(score),MIN(score) FROM sc GROUP BY cno;

2、查询每门课程被选修的学生数

SELECT cno,COUNT(sno)FROM sc GROUP BY cno;

3、查询出只选修了一门课程的全部学生的学号和姓名

SELECT sc.sno,st.sname,COUNT(cno) FROM student st
LEFT JOIN sc
ON sc.sno=st.sno
GROUP BY st.sname,sc.sno HAVING COUNT(cno)=1;

4、查询男生、女生人数

SELECT ssex,COUNT(*)FROM student GROUP BY ssex;

5、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT cno,AVG(score) FROM sc GROUP BY cno ORDER BY AVG(score)ASC,cno DESC;

6、查询平均成绩大于70 的所有学生的学号、姓名和平均成绩

SELECT st.sno,st.sname,AVG(score) FROM student st
LEFT JOIN sc
ON sc.sno=st.sno
GROUP BY st.sno,st.sname HAVING AVG(score)>70;

7、查询课程名称为“SSH”，且分数低于60 的学生姓名和分数

SELECT sname,score FROM student st,sc,course c
WHERE st.sno=sc.sno AND sc.cno=c.cno AND c.cname='SSH' AND sc.score<60

8、查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT a.* FROM sc a ,sc b WHERE a.score=b.score AND a.cno<>b.cno

9、检索至少选修两门课程的学生学号

SELECT sno FROM sc GROUP BY sno HAVING COUNT(cno)>1;

10、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

SELECT st.sname FROM student st
WHERE st.sno NOT IN
(SELECT DISTINCT sc.sno FROM sc,course c,teacher t
WHERE sc.cno=c.cno AND c.tno=t.tno AND t.tname='谌燕')

11、查询两门以上不及格(百分制)课程的同学的学号及其平均成绩

SELECT sno,AVG(score)FROM sc
WHERE sno IN
(SELECT sno FROM sc WHERE sc.score<60
GROUP BY sno HAVING COUNT(sno)>1
) GROUP BY sno

12、检索“c004”课程分数小于60，按分数降序排列的同学学号

SELECT sno FROM sc WHERE cno='c004' AND score<60 ORDER BY score DESC;

13、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

SELECT a.* FROM
(SELECT * FROM sc a WHERE a.cno='c001') a,
(SELECT * FROM sc b WHERE b.cno='c002') b
WHERE a.sno=b.sno AND a.score > b.score;

14、查询平均成绩大于60 分的同学的学号和平均成绩；

SELECT sno,AVG(score) FROM sc  GROUP BY sno HAVING AVG(score)>60;

15、查询所有同学的学号、姓名、选课数、总成绩；

SELECT a.*,s.sname FROM (SELECT sno,SUM(score),COUNT(cno) FROM sc GROUP BY sno) a ,student s WHERE a.sno=s.sno

-- 16、查询姓“刘”的老师的个数；

SELECT COUNT(*) FROM teacher WHERE tname LIKE '刘%';

-- 17、查询没学过“谌燕”老师课的同学的学号、姓名；

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 = '谌燕')

-- 18、查询没有学全所有课的同学的学号、姓名；

 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)