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的句式可以用来查询“存在于所有的。。的”,但是比较难理解,需要多看几遍