数据库第三章作业

作业 数据库
发布日期:   2024-04-29

数据库系统概念，本科教学版，原书第7版

第三章习题


3.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
a. select title from course where dept_name='Comp. Sci.' and credits=3;
b. select s_id from advisor,instructor where i_id=id and name='Einstein';
c. select max(salary) from instructor;
d. select name from instructor where salary=(select max(salary) from instructor); 
e. select course_id,count(id) 
   from section natural join takes where semester='Fall' and year=2017 
   group by course_id;
f. select max(count) 
   from (select count(id) count from section natural join takes 
   where semester='Fall' and year=2017 group by course_id); 
g. with enroll as
   (select course_id,count(id) count 
   from section natural join takes 
   where semester='Fall' and year=2017 group by course_id)
   select course_id from enroll where count=(select max(count) from enroll);

3.2

1
2
3
4
5
6
7
8
9
10
11
12
a. select sum(points*credits) from
   course_id natural join (takes natural join grade_points) 
   where id='12345';
b. select sum(points*credits)/sum(credits) from
   course_id natural join (takes natural join grade_points) 
   where id='12345';
c. select id,sum(points*credits)/sum(credits) from
   course_id natural join (takes natural join grade_points) 
   group by id;
d. --如果成绩为空，则其成绩不会被a计算进去,不会对结果造成影响
   --如果成绩为空是为0，则将sum(points*credits)改为
   --sum(case when points is null then 0 else points*credits end)

3.3

1
2
3
4
a. update instructor set salary=salary*1.1 where dept_name='Comp. Sci.'
b. delete from section where NOT exists (select course_id from takes)
c. insert into instructor 
   (select id,name,dept_name, 10000 from student where tot_cred>100)

3.4

1
2
3
4
a. select  COUNT(DISTINCT driver_id) 
   from accident natural join participated where year=2017
b. delete from car where year=2010 and exists 
   (select license_plate from owns where driver_id='12345')

3.5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
a. select ID,case
   when  score<40 then 'F'
   when  score<60 then 'C'
   when  score<80 then 'B'
   else 'A' end
   from marks
b. select COUNT(*)
   from (select ID,case
   when  score<40 then 'F'
   when  score<60 then 'C'
   when  score<80 then 'B'
   else 'A' end grade
   from marks) 
   group by grade

3.6

1
LOWER(name) LIKE '%sci%'

3.7

  首先p中存在a1与r1或r2中某些值相等,r1和r2的交集要为空；r1,r2都不可以是空集，否则p,r1,r2的笛卡尔集为空集。

3.8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

a. (select ID from customer) MINUS (select ID from borrower)
b. WITH place AS
   (select customer_street,customer_city from customer where ID='12345') 
   select ID 
   from customer 
   where customer.customer_street=place.customer_street 
   and customer.customer_city=place.customer_city
   and ID!='12345' 
c. select branch_name 
   from branch 
   where exists 
   (select * from customer natural join depositor 
   natural join account 
   where branch.branch_name=branch_name and customer_city='Harrison')

3.9

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
a. select ID,person_name,city 
   from employee natural join works natural join company 
   where company_name='First Bank Corporation'
b. select ID,person_name,city 
   from employee natural join works natural join company 
   where company_name='First Bank Corporation' and salary>10000
c. select ID from works a
   where NOT exists (select * from works b 
   where comany_name='First Bank Corporation' AND a.ID=b.ID)
d. select ID from works
   where salary>ALL
   (select salary from works where company_name='Small Bank Corporation')
e. select DISTINCT company_name 
   from company 
   where city in
   (select city from company where company_name='Small Bank Corporation')
f. WITH a as (select company_name,COUNT(*) cnt from works group by company_name)
   select company_name from a where cnt=(select MAX(cnt) from a)
g. WITH a as (select company_name,AVG(salary) avg from work group by company_name)
   select company_name from a 
   where avg>(select avg from a where company_name='First Bank Corporation')

3.10

1
2
3
4
5
6
a. update employee set city='Newtown' where ID='12345'
b. update works set salary=case
   when salary<100000 THEN salary=salary*1.1
   else salary=salary*1.03 end
   where company_name='First Bank Corporation' AND
   exists (select * from manage where manage.id=works.id)

3.11

1
2
3
4
5
6
7
8
9
10
11
a. select ID,name from student 
   where exists 
   (select * from takes natural join course 
   where ID=student.ID and dept_name='Comp. Sci.') 
b. select ID,name from student
   where not exists
   (select * from takes where year<2017 and ID=student.ID)
c. select dept_name,max(salary) from instructor group by dept_name
d. with a as 
   (select max(salary) mx from instructor group by dept_name)
   select min(mx) from a

3.12

1
2
3
4
5
6
7
8
9
a. insert into course values('CS-001','Weekly Seminar','Comp. Sci.',0)
b. insert into section values('CS-001','1','Fall',2017,null,null,null)
c. insert into takes select ID,'CS-001','1','Fall',2017,null 
   from student where dept_name='Comp. Sci.'
d. delete from takes where ID='12345'
e. delete from course where course_id='CS-001' 
   -- section和takes中对应的元组将会级联删除
f. delete from takes where exists(select * from course 
   where course_id=takes.course_id and lower(dept_name) like '%advanced%')

3.13

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
create table preson
(
   driver_id char(5) primary key,
   name char(20) not null,
   address char(50)
)
create table car
(
   license_plate char(10) primary key,
   model char(20) not null,
   year numeric(4,0)
)
create table accident
(
   report_number char(5) primary key,
   year numeric(4,0),
   location char(50)
)
create table owns
(
   driver_id char(5),
   license_plate char(10),
   primary key(driver_id,license_plate),
   foreign key(driver_id) references person(driver_id),
   foreign key(license_plate) references car(license_plate)
)
create table participated
(
   report_number char(5),
   license_plate char(10),
   driver_id char(5),
   damage_amount numeric(6,0),
   primary key(report_number,license_plate),
   foreign key(report_number) references accident(report_number),
   foreign key(license_plate) references car(license_plate),
   foreign key(driver_id) references person(driver_id)
)

3.14

1
2
3
4
5
a. select count(distinct report_number)
   from person natural join owns join participated using(license_plate) 
   where name='John Smith'
b. update participated set damage_amount=3000 
   where report_number='AR2197' and license_plate='AABB2000'

3.15

1
2
3
4
5
6
7
8
9
10
a. with a as(select count(*) cnt from branch where branch_city='Brooklyn'),
        b as (select ID,customer_name count(*) cnt 
        from customer natural join depositor natural join account
        where branch_name='Brooklyn'
        group by ID,customer_name
        )
   select ID,customer_name from b where cnt=(select cnt from a)      
b. select sum(amount) from loan
c. select branch_name from branch 
   where assets>some(select assets from branch where branch_city='Brooklyn')

3.16

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
a. select ID,person_name 
   from employee natural join works natural join company
   where employee.city=company.city
b. select a.ID,a.person_name 
   from employee a 
   where exists
   (select * from manages,employee b 
   where manager.id=b.id and a.ID=manager.ID and a.city=b.city and a.street=b.street)
c. with a as
   (select company_name,avg(salary) avg from works group by company_name)
   select b.ID,b.person_name from (employee natural join works) b 
   where exists(select * from a where b.company_name=a.company_name and b.salary>a.avg)
d. with a as
   (select company_name,sum(salary) sum from works group by company_name)
   select compay_name from a where sum=(select min(sum) from a)

3.17

1
2
3
4
5
a. update works set salary=salary*1.1 
   where company_name='First Bank Corporation' and ID in(select ID from manages)
b. update works set salary=salary*1.1 
   where company_name='First Bank Corporation' and ID in(select manager_id from manages)
c. delete from works where company_name='Small Bank Corporation'

3.18

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create table employee
(
   ID char(5) primary key,
   person_name varchar(20) not null,
   street varchar(20),
   city varchar(20)
)
create table works
(
   ID char(5) pirmary key,
   company_name varchar(20) not null,
   salary numeric(10,2),
   foreign key(company_name) references company(company_name)
)
create table company
(
   company_name varchar(20) primary key,
   city varchar(20)
)
create table manages
(
   ID char(5) primary key,
   manager_id char(5)
)

3.21

1
2
3
4
5
6
7
8
9
10
11
12
a. select memb_no,nmae from member
   where memb_no in(select memb_no from book natural join borrowed 
   where pulisher='McGraw-Hill')
b. with a as
   (select count(*) cnt from book where pulisher='McGraw-Hill')
   select memb_no,nmae from member b where exists
   (select memb_no from book natural join borrowed where pulisher='McGraw-Hill' 
   and b.memb_no=borrowed.memb_no  
   group by memb_no having count(*)=(select cnt from a))
c. select memb_no,nmae from member natural join book natural join borrowed
   group by memb_no,name,publisher having count(isbn)>5
d. (select count(*) from borrowed)/(select count(*) from member)

3.22

1
where 1>=(select count(title) from course)

3.23

1
2
3
4
select dept_name
from (select dept_name,sum(salary) value from instructor group by dept_name) dept_total,
(select avg(value) value from dept_total) dept_total_avg
where dept_total.value>=dept_total_avg.value

3.24

1
2
3
4
select student.name,student.ID
from student,advisor,instructor 
where student.ID=advisor.s_id and instructor.ID=advisor.i_id
and student.dept_name='Accounting' and instructor.dept_name='Physics'

3.25

1
2
3
select a.dept_name from department a, department b
where a.budget>b.budget and b.dept_name='Philosophy'
order by a.dept_name asc

3.26

1
2
3
4
select course_id,ID from takes
group by ID,course_id
having count(*)>2
order by course_id

3.27

1
2
3
4
5
6
7
with a as
(select course_id,ID from takes
group by ID,course_id
having count(*)>1
order by course_id)
select ID from a
group by ID having count(*)>2

3.28

1
2
3
4
select distinct a.ID, a.name from instructor a,teaches b,course c
where b.ID=a.ID and c.course_id=b.course_id 
and a.dept_name=c.dept_name
order by a.name

3.29

1
2
3
4
5
with a as (select ID 
from takes natural join course where dept_name='Music'
group by ID having count(*)<5) 
select name,ID from student 
where name like 'D%' and dept_name='History' and ID in(select * from a)

3.30

  当某些元组的salary为空值时，avg函数在计算时会忽略控制，但是count(*)计算的行数，不会忽略空值。例子如下

salary
10000
null

avg(salary)的计算结果时10000，null被忽略；sum(salary)的计算结果是10000,count(*)计算的结果是2，最终结果是5000。


3.31

1
2
3
4
5
6
7
8
9

select ID,name 
from instructor a  
where not exists
(select * from teaches b,takes c 
where a.ID=b.ID and b.course_id=c.course_id
and b.sec_id=c.sec_id and b.semester=c.semester 
and b.year=c.year and c.grade='A')


3.32

1
2
3
4
5
6
7
8
9
10
11
12
select ID,name 
from instructor a  
where not exists
(select * from teaches b,takes c 
where a.ID=b.ID and b.course_id=c.course_id
and b.sec_id=c.sec_id and b.semester=c.semester 
and b.year=c.year and c.grade='A') 
minus
select ID,name from instructor 
where not exists
(select * from takes join teaches using(course_id,sec_id,semester,year) 
where instructor.ID=teaches.ID and grade is not null)

3.33

1
2
3
select course_id,title from course a where dept_name='Comp. Sci.'
and exists (select * from section natural join time_slot 
where  a.course_id=course_id and end_hr>=12)

3.34

1
2
3
4
select course_id,sec_id,year,semester,count(*) num 
from takes
group by course_id,sec_id,year,semester
having count(*)>0

3.35

1
2
3
4
5
6
7
with a
as 
(select course_id,sec_id,year,semester,count(*) num 
from takes
group by course_id,sec_id,year,semester
having count(*)>0)
select * from a where num=(select max(num) from a)
文章作者: Steven04hub
文章链接: https://steven04hub.github.io/2024/04/29/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AC%AC%E4%B8%89%E7%AB%A0%E4%BD%9C%E4%B8%9A/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Steven04hub !
作业 数据库
赏
  目录