4.1.c
select C.*, I.ID, case I.name when I.name then I.name else '-' end
from course as C natural left outer join teaches as T natural left outer join instructor as I
MySql中没有decode,可以用case来代替,如上
或者还有一种做法,使用coalesce,如下
select C.*, I.ID, coalesce(I.name, '-')
from course as C natural left outer join teaches as T natural left outer join instructor as I
4.2.b
select * from student natural join takes
union
(
select student.*, null, null, null, null, null
from student
where ID not in (select ID from student natural join takes)
)
union
(
select null, null, null, takes.*
from takes
where not exists (select * from student where takes.ID=student.ID)
)
可以看到这道题的两个union中分别用到了not in 和 not exisists,not exisists应该是比较好的形式。因为如果将not in用在not exisists的地方子查询将会很长,而后者没有这个问题
4.5 记录一个奇怪的问题
SELECT *
FROM (
(SELECT ID as id, SUM(points*credits) as sum
FROM takes NATURAL JOIN grade_points NATURAL JOIN course
group by ID)
union
(SELECT S.ID as id ,0 as sum
from student as S
WHERE NOT EXISTS (SELECT * FROM takes as T WHERE S.ID=T.ID)
)
) as x;
这个查询不会查到union后半部分的结果,而如果把union的两部分互换,则会有正确的结果
不知道为什么,感觉应该是数据库底层运算的原因,可能是上面的join语句造成的
4.16
问题:如何让一个属性a的值至少在b,c的集合中存在一个
这个不会做,可以用断言,但是基本没有数据库支持这样的断言