数据库原理实战-实验三

使用University数据库的数据库结构和数据（smallRelations即可）

(1) Find the names of courses in Computer science department which have 3 credits

题目大意:找到那些计算机系开的课程,而且它的学分是3

select course_id
from course
where dept_name = 'Comp. Sci.' and credits = 3;

**(2) For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student. **

题目大意:找到一位学生的记录，这位学生的学号是12345或者其它都行，显示该学生注册的所有课程的所有课程ID和标题

首先题目会给定一个id,然后要求我们去找takes表里面这个id所选的course，列出course_id和title

思路:查找对应ID所选的所有课程ID->根据课程ID查找得到course_id和title

该子查询能够查找12345的所有选课ID

select course_id
from takes
where ID = '12345'

然后根据选课ID去查找这个课程就可以了

select course_id,title
from course
where course_id in
(select course_id
from takes
where ID = '12345');

**(3) As above, but show the total number of credits for such courses (taken by that student). Don’t display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student. **

大致题意:如上所述，但请显示此类课程的总学分数（该学生所修）。不要显示student表中的tot_creds值，应该对学生所学的课程使用SQL聚合

这道题要求我们求出一条记录，这条包含ID、tot_creds，同时要求我们不能够直接查询student的tot_creds

思路:第二题中我们已经查找出了这个学生所学的所有课程，那么我们只需要对这个所有的课程进行一个统计即可

select sum(credits)
from course
where course_id in
(select course_id
from takes
where ID = '12345');

**(4) As above, but display the total credits for each of the students, along with the ID of the student; don’t bother about the name of the student. (Don’t bother about students who have not registered for any course, they can be omitted) **

题目大意:如上所述，但显示每个学生的总学分，以及学生的ID；不用担心学生的名字。（不要为没有注册任何课程的学生操心，他们可以省略）

这道题要求我们分别算出每个学生的总学分，要显示的字段为ID,tot_cred

在第三题中,我们计算的学生是固定的,是单条的,如果我们现在想要计算出所有学生的总学分，思路:

• 首先题目说没有选课程的学生是可以被省略的，因此我们只需要对takes表内的学生进行统计就可以了，我们按照ID进行分组，然后对这些ID分组内的学生获得他们所有的课程ID，然后拿到课程ID后去统计他们的得分就可以了

需要进行分步，总体思路是通过ID->course_id->credit

然后我们观察发现，ID,course_id在takes表中能够拿到,credits在course能够拿到，而course和takes有外键course_id，很自然的，我们就可以将其进行连接，得到一张完整的表

takes join course using(course_id)

这个表中就包含了两个表联查所需要的信息，接着我们对这个表按照ID进行分组,聚合得到结果即可

select ID,sum(credits) as total_credits
from takes join course using(course_id)
group by(ID)
order by total_credits asc;

**(5) Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names) **

题目大意:找出所有有选过Comp.Sci的课程的学生的名字。当然（不应该有重复的名字)

这道题的限制因素是Comp. Sci.的系开的课程，梳理一下表的数据以及结构

name<-(student)ID->course_id(course)->dept_name

这道题中ID、course_id作为一个中间桥梁，可以通过ID找到student中的name，可以通过course_id找到dept_name，再判断它是否是Comp. Sci.，我的做法是分三步来做:

• 先找到那些Comp. Sci.开的所有课程,course

select course_id
from course
where dept_name = 'Comp. Sci.';

• 然后再在takes表中查询学生的选课记录,找到合适的ID就先丢进去

select distinct ID
from takes
where takes.course_id in(select course.course_id
								from course
								where course.dept_name = 'Comp. Sci.');

• 最终再查询student即可

select name
from  student
where student.ID in(select distinct ID
from takes
where takes.course_id in(select course.course_id
from course
where course.dept_name = 'Comp. Sci.'));

**(6)Display the IDs of all instructors who have never taught a course **

题目大意:找到那些没有教过课的所有教师

这道题一个最直观的想法就是从teaches选出一个ID的集合，然后用这个ID集合去和instructor表做差集即可。可是mysql里面是没有差集的，通常的手段是通过连接的方式来找到差集。

left join求差集原理:

• 什么是left join?有什么效果?

tabel1 left join table2 using(column):在这个情况下,left join会将左表中的所有数据查询出来，on(或者using)以及后面的条件仅仅会影响右边的数据，符合就显示，不符合就显示为null

我们来先直观了解一下:

select *
from instructor left join teaches using(ID);

上面这个结果我们要关注的是course_id为空那几行，它代表着这些教师在instructor中存在记录但是在teaches中不存在记录，因此直接将对应的数据显示为null

总结

当我们想要做差集的时候,一般来说，left join左边的是大范围，右边的是小范围。

然后最终通过右表中某些数据为null来过滤数据即可

select ID
from instructor left join teaches using(ID)
where course_id is null;

(7)As above, but display the names of the instructors also, not just the IDs.

select ID,name
from instructor left join teaches using(ID)
where course_id is null;

(8)Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don’t worry about those that had no students taking that section

题目大意:找出所有部分的最高和最低选课的人数，只考虑有入学选课的部分，不要担心那些没有学生选课的部分

这道题可以分为两步:

• 创建一个视图，这个视图包含一个course_id、section_count，也就是一门课的选课人数。

create view section_count as
select course_id,count(ID)as s_count
from takes
group by(course_id)
order by s_count asc;

• 对该视图进行分组统计最大值和最小值即可

select max(s_count)as max_section,min(s_count)as min_section
from section_count;

(9)As in Question (8), but now also include sections with no students taking them; the enrollment for such sections should be treated as 0.

题目大意:续题(8),现在选课人数可以为0,请你继续求出最大值和最小值。

如果选课人数可以为0，那么我们的查询就不能够就仅局限于takes表了，而是应该针对与全部课的表course，其关键是对于那些没有人选课的course，该如何处理?

我们可以先从最简单的做起，也就是求出那些没有人选课的记录出来先。

• 首先先把有人选课的清单给选出来

create view takes_course as
(select distinct course_id,sec_id from takes);

• 求差集

select course_id
from course left join takes_course using(course_id)
where sec_id is null;

那么到这一步的course_id，这些课程就是没有人选课的了。

其中比较关键的一点是, 我们判断一门课如果没有人选，那么就不会出现在takes中，如果不会出现在takes中，那么sec_id就是null。

注意到mysql中提供了一个api:ifnull()

用法如下：

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

那么这时候就可以利用这个api了,当我们测试这个s_count为null的时候就给它赋值为0

好了，接下来我们想要得到的是一个具有course_id,s_count的视图，那么一个自然的方法就是与原来的视图建立一个左外连接。

select course_id,ifnull(s_count,0)as s_count
from course left join takes_course using(course_id) 
			left join section_count using(course_id)
order by s_count asc;

**(10)Find all courses whose identifier starts with the string “CS-101” **

查找标识符以字符串“CS-101”开头的所有课程

select course_id
from course
where course_id like 'CS-101%'

3. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D), namely, there would exist the same (ID,course_id) pairs with different grades in “takes” table. Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).

insert into takes values('00128','CS-190',1,'Spring',2017,'F'); -- *testing with it if needed

题目大意:

大学规则允许F成绩被任何及格成绩（A、B、C、D）覆盖，也就是说，在takes表中会存在具有不同成绩的相同（ID、course_ID）对。现在，创建一个视图，列出所有未被覆盖的不及格等级的信息（该视图应包含take关系中的所有属性）

create view fail as
select *
from takes
where grade = 'F';
insert into takes values('00128','CS-190',1,'Spring',2017,'F');