数据库概论第四章部分答案

这里用的数据库是mysql

4.1

a.

 SELECT  ID,NAME,(CASE WHEN course_id IS NULL THEN '0'
ELSE course_id END)AS course_id

FROM instructor A NATURAL LEFT OUTER JOIN teaches B;

这里的一共有21行，这个名字为Brandt的老师所教授的CS-190课程就出现了两次,是因为这个老师教了课程id号相同但是不同的课程。

如果把这两个课程看成一个课程的话，可以使用distinct，即在select后面使用distinct,使用distinct之后的数据结果如下所示：

b.

SELECT instructor.ID,instructor.name,teaches.`course_id`
AS courseID

FROM instructor,teaches

WHERE instructor.`ID`=teaches.`ID`

UNION all

(SELECT instructor.ID,instructor.name,'0' AS courseID

FROM instructor

WHERE instructor.ID NOT IN (SELECT ID FROM teaches));

执行结果如下所示：

可以看出，结果和第一题的结果是一样的，只是顺序不同，这里如果不用union all的话，Brandt的两门courseID相同的但是不同的课就会被吞掉，如下所示：

c.执行语句如下所示：

select course_id,(case when name=null then '-' else name end) as TNAME 

from section natural left outer join (instructor join teaches using
(ID)

) where semester='Spring' and section.year='2010';

执行结果如下所示：

Section中所有内容如下所示：

可以从图中可以知道在2010春季段开课的课程段只有七个，执行结果刚好满足条件。

d.

执行语句为：

select dept_name,count(ID) from department natural left outer join
instructor group by dept_name;

因为count（1）与count(*)得到的结果是一样的，包含null值，count(字段)不计算null值，

Count(null)的结果恒为0；

所以这里用的是count(ID)不用担心系下面老师数目为null的情况。

执行结果如下所示：

Department中的内容如下所示：

Instructory中的内容如下所示：

很容易验证结果是正确的。

4.2

a.执行语句：

SELECT * FROM student NATURAL JOIN takes

UNION 

SELECT ID,NAME, dept_name, tot_cred,NULL,NULL,NULL,NULL,NULL FROM
student

WHERE student.ID NOT IN (SELECT ID FROM student NATURAL JOIN takes);

执行结果如下所示 ：

这个和执行

SELECT * FROM student NATURAL LEFT OUTER JOIN takes;

得到结果是一样的，只是显示顺序不一样：

B． 执行语句如下所示

SELECT * FROM student NATURAL JOIN takes

UNION

SELECT ID,NAME,dept_name,tot_cred,NULL,NULL,NULL,NULL,NULL FROM student

WHERE student.`ID` NOT IN (SELECT ID FROM student NATURAL JOIN takes)

UNION

SELECT NULL,NULL,NULL,NULL,course_id,sec_id,semester,YEAR,grade FROM
takes

WHERE takes.`ID` NOT IN (SELECT ID FROM takes NATURAL JOIN student);

执行结果如下所示：

因为mysql中是不支持全连接的，所以这里没办法验证。

Student表格的内容如下所示：

Takes表格中的内容如下所示

通过观察，还是可以看出，这个语句的执行结果还是正确的。