复杂sql语句练习

复杂sql语句练习

1.查询所有的课程的名称以及对应的任课老师姓名
    SELECT
        course.cname,
        teacher.tname 
    FROM
        teacher INNER JOIN course ON teacher.tid = course.teacher_id
        
        cname	tname
        生物		张磊老师
        物理		李平老师
        美术		李平老师
        体育		刘海燕老师
        

2.查询平均成绩大于80分的同学的姓名和平均成绩
SELECT
	student.sname,
	t1.ag 
FROM
	student
	INNER JOIN (
	SELECT
		score.student_id,
		AVG( score.num ) AS ag 
	FROM
		score 
	GROUP BY
		score.student_id 
	HAVING
		AVG( score.num ) > 80 
	) AS t1 ON t1.student_id = student.sid;
	sname		ag
	张三			82.2500
	刘三			87.0000

	

3.查询没有报李平老师课的学生姓名
	SELECT
	sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT
		student_id 
	FROM
		score 
	WHERE
		course_id IN ( SELECT DISTINCT cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE tname = '李平老师' ) 
	);
	sanme
	刘三
	刘一
	刘二
	刘四

4.查询没有同时选修物理课程和体育课程的学生姓名
	SELECT
	sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		student_id 
	FROM
		(
			( SELECT * FROM score INNER JOIN course ON cid = course_id WHERE course.cname = '物理' OR course.cname = '体育' ) AS t1 
		) 
	GROUP BY
		student_id 
	HAVING
		COUNT( student_id ) = 1 
	);
	sname
	理解
	钢蛋
	刘三

5.查询挂科超过两门（包括两门）的学生姓名和班级
	SELECT
	t2.caption,
	t2.sname 
FROM
	( ( SELECT * FROM class INNER JOIN student ON cid = class_id ) AS t2 ) 
WHERE
	sname IN (
	SELECT
		sname 
	FROM
		student 
	WHERE
		sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING COUNT( student_id ) >= 2 ) 
	);
	caption		sname
	三年二班		理解