databasehomework2

第三章课后习题

如何下载课本的数据库？

这个是作者提供的网址啦~https://db-book.com/db6/lab-dir/sample_tables-dir/index.html

1. 我使用的是mysql，所以首先使用create database homework;创建一个新的数据库

2. 然后下载https://db-book.com/db6/lab-dir/sample_tables-dir/DDL.sql

3. 调用该sql文件，建表source C:\Users\Administrator\Desktop\DDL.sql

4. 然后下载https://db-book.com/db6/lab-dir/sample_tables-dir/smallRelations/smallRelationsInsertFile.sql

5. 调用该sql文件，增添数据source C:\Users\Administrator\Desktop\smallRelationsInsertFile.sql

3.11

# a.
SELECT distinct `name`
FROM student NATURAL JOIN takes NATURAL JOIN course
WHERE dept_name = 'Comp. Sci.';

# b.
select ID,name
from student
except 
(select ID,name
from student
natural join takes
where year < 2009);

# c.
select dept_name,max(salary) 
from instructor
group by dept_name;

# d.
select d.dept_name,min(d.max_salary ) 
from (select dept_name,max(salary) as max_salary 
from instructor
group by dept_name
) as d;

3.12

# a.
insert into course
values('CS-001', 'Weekly Seminar', 'Comp. Sci.', 0);

# b.
insert into section(course_id, sec_id, semester,year )
values('CS-001',1,'Fall', 2009);

# c.
insert into takes
select ID, 'CS-001', 1, 'Fall', '2009', NULL
from student
where dept_name = 'Comp. Sci.';

# d.
delete from takes
where course_id = 'CS-001' and sec_id = 1 and semester = 'Fall' and year = 2009
and ID in 
(select ID
from student
where 'name' = 'Chavez');

# e.
delete from course
where course_id = 'CS-001';
#如果没有先删除这门课的授课信息，则删除course删除后该门课的授课信息也会被自动删除（这是因为外码依赖）;

# f.
delete from takes
where course_id in
(select course_id
from course
where lower(title) like '%database%');

3.13

#创建表person
create table person(
driver_id char(20) not null unique,
name varchar(20) not null,
address varchar(100),
primary key(driver_id)
);
#创建表car
create table car(
license char(20) not null unique,
model char(20) not null,
year int,
primary key(license)
);
#创建表accident
create table accident(
report_number char(20) not null unique,
date char(20) not null,
location varchar(100),
primary key(report_number)
);
#创建表owns
create table owns(
driver_id char(20) not null unique,
license char(20) not null unique,
primary key(driver_id), 
foreign key (driver_id) references person(driver_id) on delete cascade,
foreign key (license) references car(license) on delete cascade
);
#创建表participated
create table participated(
report_number char(20) not null unique,
license char(20) not null unique,
driver_id char(20) not null unique,
damage_amount bigint,
primary key(report_number,license), 
foreign key (report_number) references accident(report_number) on delete cascade,
foreign key (driver_id) references person(driver_id) on delete cascade,
foreign key (license) references car(license) on delete cascade
);

3.14

# a.
select count(report_number) 
from accident a,person p, participated par 
where a.report_number = a.report_number and p.driver_id = participated.driver_id and p.name = 'John Smith';

# b.
update participated
set damage_amount = 3000
where report_number = "AR2197" and driver_id = "AABB2000";

3.15

# a. 先列出Brooklyn所有账户和其对应支行，再列出一个Brooklyn所有支行的表。用not exit判断(北京所有支行-当前用户的所有支行)
with customer_branch(customer_name, branch_name) as (
    select customer_name, branch_name from depositor
    natural join account natural join branch
    where branch_city = 'Brooklyn'
), branch_name_beijing(branch_name) as (
    select branch_name from branch where branch_city = 'Brooklyn'
)
select distinct customer_name
from customer_branch as S
where not exists (
    (select branch_name from branch_name_beijing)		
    except
    (select branch_name from customer_branch where customer_name = S.customer_name)
    );
# b.
select branch_name,sum(amount) from loan group by branch_name;

# c.
select branch_name from branch
where assets > some(
    select assets from branch where branch_city = 'Brooklyn'
);

3.19

答：<>all的对立面是=any，即只要与集合中的某一个元素相等即可。

而not in 的对立面是in，强调的也是只要与集合中的某一个元素相等就满足了。

所以<>all等价于not in.

3.23

答：因为takes用到了四个属性：course_id,sec_id,semester,year。这四个属性都是外码，参照于section中的四个属性。又因为，这四个属性是section的主码，具有唯一性，所以如果不是需要section中的其他属性，连接section将没有影响。