MySQL练习题3

1、查询所有的课程的名称以及对应的任课老师姓名
SELECT course.cname,teacher.tname FROM course JOIN teacher ON course.teacher_id = teacher.tid;
2、查询学生表中男女生各有多少人
SELECT gender 性别,count(1) 人数 FROM student GROUP BY gender;
3、查询物理成绩等于100的学生的姓名
SELECT student.sname FROM student WHERE sid IN (

SELECT student_id FROM score INNER JOIN course ON score.course_id = course.cid

WHERE course.cname = ‘物理’ AND score.num = 100);
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT student.sname,t1.avg_num FROM student INNER JOIN (

SELECT student_id,avg(num) AS avg_num FROM score GROUP BY student_id HAVING avg(num) > 80) AS t1 ON student.sid = t1.student_id;

5、查询所有学生的学号，姓名，选课数，总成绩
SELECT student.sid,student.sname,t1.course_num,t1.total_num FROM student LEFT JOIN (

SELECT student_id,COUNT(course_id) course_num,sum(num) total_num FROM score GROUP BY student_id) AS t1 ON student.sid = t1.student_id;
6、 查询姓李老师的个数
SELECT count(tid) FROM teacher WHERE tname LIKE ‘李%’;
7、 查询没有报李平老师课的学生姓名
SELECT student.sname FROM student WHERE sid NOT IN (

SELECT DISTINCT student_id FROM score WHERE course_id IN (

SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = ‘李平老师’ ));

8、 查询物理课程比生物课程高的学生的学号
SELECT t1.student_id FROM

(SELECT student_id,num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = ‘物理’)) AS t1

INNER JOIN (

SELECT student_id,num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = ‘生物’)

) AS t2 ON t1.student_id = t2.student_id WHERE t1.num > t2.num;
9、 查询没有同时选修物理课程和体育课程的学生姓名
SELECT student.sname FROM student WHERE sid IN (

SELECT student_id FROM score WHERE course_id IN (SELECT cid FROM course WHERE cname = ‘物理’ OR cname = ‘体育’)

GROUP BY student_id HAVING COUNT(course_id) = 1);
10、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT student.sname,class.caption FROM student INNER JOIN (

SELECT student_id FROM score WHERE num = 2) AS t1

INNER JOIN class ON student.sid = t1.student_id AND student.class_id = class.cid;
11、查询选修了所有课程的学生姓名
SELECT student.sname FROM student WHERE sid IN (

SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id) = (SELECT count(cid) FROM course));
12、查询李平老师教的课程的所有成绩记录
SELECT * FROM score WHERE
course_id IN (SELECT cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = ‘李平老师’);
13、查询全部学生都选修了的课程号和课程名
SELECT cid,cname FROM course WHERE cid IN (
SELECT course_id FROM score GROUP BY course_id HAVING COUNT(student_id) = (SELECT COUNT(sid) FROM student));
14、查询每门课程被选修的次数
SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;
15、查询之选修了一门课程的学生姓名和学号
SELECT sid,sname FROM student WHERE

sid IN (SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id) = 1);
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT num FROM score ORDER BY num DESC;
17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT sname 姓名,num 生物成绩 FROM score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE course.cname = ‘生物’ AND score.num
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

进阶练习一：
1 所有有门派的人员信息
select * from t_emp a inner join t_dept b on a.deptId = b.id;
2 列出所有用户，并显示其机构信息
select * from t_emp a left join t_dept b on a.deptId = b.id;
3 列出不入派的人员：
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;
4 所有没人入的门派 ：
select * from t_emp b left join t_dept b on a.deptId = b.id where b.id is null;
5 列出所有人员和门派的对照关系
select * from t_dept b left join t_emp a on a.deptId = b.id where a.deptId is null;
6 列出所有没入派的人员和没人入的门派
SELECT * from t_emp left JOIN t_dept on t_emp.deptId = t_dept.id where t_dept.id is null
UNION
SELECT * FROM t_dept LEFT JOIN t_emp on t_emp.deptId = t_dept.id where t_emp.deptId is null;
7 求各个门派对应的掌门人名称:
select t_emp.name FROM t_dept LEFT JOIN t_emp on t_emp.id = t_dept.CEO ;

select a.name from t_dept b left join t_emp a on b.CEO=a.id;
8 求所有当上掌门人的平均年龄:
SELECT AVG(age) from t_dept LEFT JOIN t_emp on t_emp.id = t_dept.CEO ;
9 求所有人物对应的掌门名称: