homework8

-- 关系代数课堂练习题
-- 试用关系代数表达式表示下列查询语句：
USE studentmanager;
-- （1）检索年龄小于17岁的女学生的学号和姓名
SELECT s_id 学号, s_name 姓名 
	FROM student
		WHERE YEAR(s_borndate) > 15 AND s_sex = '女';
-- （2）检索男学生所学课程的任课老师的职工号和姓名
SELECT t_id 职工号, t_name 姓名
	FROM teacher
		WHERE t_id IN (SELECT DISTINCT t_id FROM t_c WHERE course_id IN (SELECT DISTINCT course_id FROM s_c WHERE s_id in (SELECT s_id FROM student WHERE s_sex = '男')));
-- （3）检索男学生所学课程的课程号和课程名
SELECT course_id 课程号, course_name 课程名称
	FROM course 
		WHERE course_id IN (SELECT course_id FROM s_c WHERE s_id IN (SELECT s_id FROM student WHERE s_sex = '男'));
-- （4）检索至少选修2门课程的学生的学号
SELECT s_id 学号
	FROM s_c 
		WHERE s_id IN (SELECT s_id FROM (SELECT s_id, count(course_id) c FROM s_c HAVING c >= 2 GROUP BY s_id ));
-- （5）检索至少有学号为s2和s4的学生选修的课程的课程号
SELECT DISTINCT a.course_id 
	FROM s_c a, s_c b
		WHERE a.s_id = '2016010101' AND b.s_id = '2016010201';
-- （6）检索王同学不学的课程的课程号
SELECT course_id 
	FROM course 
WHERE
	course_id NOT IN (
		SELECT
			course_id
		FROM
			s_c
		WHERE
			s_id in (
				SELECT
					s_id
				FROM
					student
				WHERE
					s_name = '王丽'
			));
-- （7）检索选修全部课程的学生的姓名
SELECT s_name
	FROM student
		WHERE s_id IN  (SELECT s_id FROM (SELECT s_id, count(course_id) c FROM s_c GROUP BY s_id ) s WHERE s.c = 3) ;
-- (SELECT count(*) FROM course));
-- （8）检索选修课程包含liu老师所授全部课程的学生学号
SELECT s_id 
	FROM 

-- 
-- 当拿到一个需求时，先学会分析： 
-- （1）我要查询哪些数据？  
-- （2）从哪几个表中查？ 
-- （3）假设存在多张表查询，慢慢来，先对两张表进行运算，再慢慢增加
-- 
-- 
--