实验7

-- 实验七 多表查询实验
-- 1、用连接查询实现多表数据的检索
USE studentmanager;
-- （1）检索学生的学号、姓名和所在班级名称
SELECT s_id 学号, s_name 姓名, c_name 班级名称 
	FROM student s JOIN class c ON s.c_id = c.c_id; 
-- （2）检索选修“数据库原理与应用”课程的学生的姓名和成绩
SELECT s_name 姓名, grade 成绩
	FROM student s, s_c sc, course c
	WHERE c.course_name = "数据库原理与应用" and sc.course_id = c.course_id AND s.s_id = sc.s_id;
-- （3）查询每门课程的课程号、任课教师姓名及其选课人数
SELECT c.course_id 课程号, t_name 任课教师姓名, COUNT(sc.course_id) 选课人数
	FROM course c, teacher t, s_c sc, t_c tc
	WHERE c.course_id = tc.course_id AND tc.t_id = t.t_id AND 
	GROUP BY sc.course_id;
-- （4）检索与“王丽”在同一班级的其他学生的姓名
SELECT s.s_name 学生姓名
	FROM student s, student s1
	WHERE s1.s_name = "王丽" AND s.c_id = s1.c_id AND s.s_name <> '王丽';
-- （5）查询“大学英语”，成绩在85分以上的学生的学号和姓名
SELECT s.s_id 学号, s_name 姓名
	FROM student s, s_c sc, course c
	WHERE course_name = "大学英语" AND sc.course_id = c.course_id AND s.s_id = sc.s_id;
-- （6）查询讲授“大学英语”的教师的姓名和所在部门名
SELECT t.t_name 姓名, d.dep_name 部门名
	FROM teacher t, department d, course c, t_c tc
	WHERE c.course_name = "大学英语" AND c.course_id = tc.course_id AND t.dep_id = d.dep_id AND t.t_id = tc.t_id ;
-- （7）查询与“刘清华”在同一部门的其他教师的姓名
SELECT t.t_name 姓名
	FROM teacher t, teacher t1
	WHERE t1.t_name = '刘清华' AND t.dep_id = t1.dep_id AND t.t_name <> '刘清华';

-- 2、子查询（嵌套查询）
-- （1）查询选修0003号课程且成绩低于该门课程平均分的学生的学号
-- （2）查询年龄高于20160101班所有学生年龄的其他班的学生的学号和姓名
-- （3）查询所有成绩大于90分的学生的姓名
-- （4）查询没有选修大学英语的学生的学号和姓名
-- （5）查询成绩高于平均分的学生的学号和课程号
-- （6）查询未选修任何课程的学生的学号和姓名
-- （7）查询所有工资高于平均工资的教师的教师名
-- （8）查询年龄比“白沧铭”小的学生的姓名，结果按学号降序排序
-- （9）查询出生日期大于所有女同学的出生日期的男同学的姓名
-- 
--