多表查询 多表练习

多表设置

练习

1.查询所有的课程的名称以及对应的任课老师姓名

SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;

2.查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT
	student.sname,
	t1.avg_score 
FROM
	student
	INNER JOIN (
	SELECT
		student_id,
		AVG( score ) avg_score 
	FROM
		score
		INNER JOIN student ON score.student_id = student.sid 
	GROUP BY
		score.student_id 
	HAVING
		AVG( score ) > 80 
	) AS t1 ON student.sid = t1.student_id;

3.查询没有报刘诗诗老师课的学生姓名

SELECT
	student.sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		score.student_id 
	FROM
		student
		INNER JOIN score ON student.sid = score.student_id 
	WHERE
	score.course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '刘诗诗老师' ));

4.查询没有同时选修物理课程和体育课程的学生姓名

SELECT
	student.sname 
FROM
	student
	INNER JOIN score ON student.sid = score.student_id 
WHERE
	score.course_id IN (
	SELECT
		cid 
	FROM
		course 
	WHERE
	course.cname IN ( '物理', '体育' )) 
GROUP BY
	score.student_id 
HAVING
	COUNT( score.student_id ) = 1;

------------------------------------------

SELECT
	student.sname 
FROM
	student 
WHERE
	student.sid IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
		score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) 
	GROUP BY
		score.student_id 
	HAVING
		COUNT( score.course_id ) = 1 
	);

5.查询挂科超过两门(包括两门)的学生姓名和班级

SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT score.student_id FROM score WHERE score.score < 60 GROUP BY score.student_id HAVING COUNT( score.student_id ) >= 2 );