数据库MySQL-查询练习2

复杂的查询通常先子查询，然后嵌套

11.查询每门课的平均成绩
关键字 avg（）
单门课：

 SELECT c_no,AVG(sc_degree) FROM SCORE where c_no = '3-105';

每门课：

SELECT c_no,AVG(sc_degree) FROM SCORE GROUP BY c_no;

12.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分

SELECT AVG(sc_degree),c_no  from score GROUP BY c_no HAVING COUNT(c_no)  >= 2 AND c_no like '3%'  ;

13.查询分数大于70但是小于90的s_no列:

SELECT s_no,sc_degree  FROM score WHERE sc_degree BETWEEN 71 AND 89;

14.查询所有的学生 s_name , c_no, sc_degree列（多表查询）

SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;

15.查询所有学生的s_no, c_name, sc_degree列（多表查询）

SELECT student.s_no, c_name, sc_degree FROM student, course,score WHERE student.s_no = score.s_no AND score.c_no = course.c_no ;

17.查询班级是’95031’班学生每门课的平均分（子查询，分组求平均）

select c_no,AVG(sc_degree) from score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no;

18.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录

SELECT * FROM score  WHERE c_no = '3-105' AND sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') ;

19.查询成绩高于学号为’109’,课程号为’3-105’的成绩的所有记录

SELECT * FROM score WHERE sc_degree >(SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105');

20.查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN('108','101'));

21.查询 张旭 教师任课的学生的成绩（多层嵌套子查询）.

select * from student where s_no IN (SELECT s_no FROM score WHERE c_no = (SELECT c_no FROM course WHERE t_no = (SELECT t_no FROM teacher WHERE t_name='张旭')));

22.查询选修课程的同学人数多余 5 人的教师姓名

SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(s_no) > 5));

23.查询95033班和95031班全体学生的记录（in用法）

SELECT * FROM student WHERE s_class IN('95031','95033') ORDER BY s_class 

24.查询存在85分以上成绩的课程c_no

SELECT * FROM score where sc_degree > 85;

25.查出所有’计算机系’ 教师所教课程的成绩表

SELECT * FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_depart = '计算机系'));