数据库第六次作业

前言

教材p713.3是第一题的前面三个小问
教材p723.12是第二题

参考代码

# 1.1
update instructor
set salary = 1.1*salary
where dept_name="Comp. Sci.";

# 1.2
delete from course
where course_id not in (
	select distinct course_id
	from section
);

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

# 1.4
UPDATE students
SET tot_cred = (
    SELECT SUM(credits)
    FROM courses
    WHERE courses.course_id IN (
        SELECT course_id
        FROM takes
        WHERE takes.ID = students.ID AND takes.grade >= 60 -- 假设60分以上为成功完成的课程
    )
);

# 2.1
insert into course 
values("CS-001", "Weekly Seminar", "Comp. Sci.", 1);

# 2.2
insert into section
values("CS-001", 1, "Fall", 2019, "Taylor", "3128", "A");

# 2.3
insert into takes
select ID, "CS-001", 1, "Fall", 2019, null
from student
where dept_name="Comp. Sci.";

# 2.4
delete from takes
where course_id="CS-001" and ID = (
	select ID
	from student
	where name="Chavez");

# 2.5
delete from course
where course_id="CS-001";

# create table 时使用了 on delete cascade 所以即使没有删除 section 里的开课信息，也可以正常删除 course 里的 CS-001，同时也会自动删除 section 和 takes 里面和该课有关的元组。

# 2.6
delete from takes
where course_id in (
	select course_id
	from course
	where lower(title) like "%database%");