数据库系统概念 第三章习题

3.1.f

(select sec_id, course_id,count(ID)
from takes natural join section
where year=2009 AND semester="Fall"
group by sec_id, course_id
)
union 
(
select sec_id, course_id, 0
from section
where (sec_id, course_id) not in 
(select sec_id, course_id
from takes natural join section
) and year=2009 AND semester="Fall"
);

自己想了一个好的解，先用自然连接找所有有学生的课，遍历一下找到所有不在这个集合中的课，但是这样可能效率要低

 

3.1.e

SELECT (
SELECT COUNT(ID) 
FROM takes
WHERE takes.year = section.year
and takes.semester = section.semester and section.sec_id=takes.sec_id AND section.course_id=takes.course_id 
// group by section.sec_id 这里是错误的
) as x
FROM section
WHERE section.year="2009" AND section.semester="FALL";

这是一不小心写出的错误解，值得注意的是，如果不加被注释的group by 语句，当一门课没有人选修时会得到0，如果加上会得到null，其原因是加上后如果没有人选修会得到一个对零个对象的group by，即零个分组，然后对零个分组中每个分组做count，会得到一个包含零个行的关系，也就是null。所以得到空的原因不是count(null)，而是对零个分组分别做count，得到null

 

3.2.a

SELECT *
FROM (
(SELECT SUM(points*credits) as sum
FROM takes NATURAL JOIN grade_points NATURAL JOIN course
WHERE ID=11111)         /* 如果存在就走这个 */
UNION                    /* 将两种结果并一下 */
(SELECT 0 as sum
WHERE NOT EXISTS (SELECT * FROM takes WHERE ID=11111)
)) as x                /* 不存在走这个*/
WHERE sum IS NOT NULL;        /* 如果不存在的话，上面的结果是NULL，所以需要一个判断把它去掉 */

要考虑学生没有选课的可能

 

3.2.b

(SELECT SUM(points*credits)/SUM(credits) as GPA
FROM takes NATURAL JOIN grade_points NATURAL JOIN course
WHERE ID=10100)
UNION
(SELECT null as GPA
WHERE NOT EXISTS (SELECT * FROM takes WHERE ID=10100)
)

对于没有选课的学生，最好的选择是将GPA设为NULL而不是0，这样计算平均GPA等操作时可以不计算这些学生。数据库的设计还是要符合实际情况。

 

3.c

insert into instructor
	select ID, name, dept_name, 10000
    from student
    where tot_cred>100;

本来工资设置为10000，返回错误  Error Code: 3819. Check constraint 'instructor_chk_1' is violated.

违反约束，可以查到salary的定义是大于29000，需要注意这种错误

 

5.b

create temporary table tmp (
	grade varchar(2)
);
insert into tmp values('A');
insert into tmp values('B');
insert into tmp values('C');
insert into tmp values('D');
insert into tmp values('S');


select (select count(*)
from(
	select ID, tot_cred, (case 
		when tot_cred<40 then 'D'
		when tot_cred<70 and tot_cred>=40 then 'C'
		when tot_cred<100 and tot_cred>=70 then 'B'
		when tot_cred<130 and tot_cred>=100 then 'A'
		else 'S'
	end) as x 
	from student) as g
where g.x=tmp.grade ) as y
from tmp;

同样的需要解决人数位0的等级如何出现在统计中的问题，这类问题的解决方法总结有两种，一种是不使用group by 改为在外层多嵌套一个select，另一种是使用union将group by结果与一个not in判断结果合并

 

3.9.e

create table company (
	ID int primary key,
	name varchar(4),
    city varchar(2)
);

insert into company values(1,'SBC','a');
insert into company values(2,'SBC','b');
insert into company values(3,'SBC','c');
insert into company values(4,'AC','a');
insert into company values(5,'AC','c');
insert into company values(6,'TT','d');
insert into company values(7,'QR','d');
insert into company values(8,'TT','a');
insert into company values(9,'TT','b');
insert into company values(10,'TT','c');

select * 
from company as c0
where not exists(
	select *
    from company as c1
    where name='SBC' and not exists (
		select city
        from company as c2
        where c1.city=c2.city and c0.city=c2.city
    )
);

这种双重not exsists的句式可以用来查询“存在于所有的。。的”，但是比较难理解，需要多看几遍