各位大佬，看过记得留言，毕竟我也不晓得答案正确性（老师不改的，写这个博客纯粹是为了记录生活），答案错误就会修改，同时可以给后人指明前路，你的留言是我不断更新的动力。
- 3.1
a）
select title
from course
where dept_name='Comp. Sci.' and credits=3;
b)
select distinct student.id
from student natural join takes
where instructor.name='Einstein';
c)
select max(salary)
from instructor;
d)
select rich_name
from (select name as rich_name
from instructor
where salary in (select max(salary) from instructor));
e)
select course_id,sec_id,count(id)
from teaches
where semester='Fall' and year=2009;
f)
select max(ave_salary)
from (select count(id) as ave_salary
from section natural join takes
where semester='Autumn' and year=2009
group by sec_id,course_id
);
g)
select id,course_id,sec_id
from takes
having semester='Autumn' and count(*)=(select count(*)
from takes
group by course_id
)
- 3.2
a)
select final_grades
from (select sum(credits*points) as final_grades
from (takes natural join course) natural join grade_points
where id='12345')
b)
select GPA
from (select sum(credits*pointers)/sum(credits) as GPA
from (takes natural join course) natural join grade_points
where id='12345')
c)
select id ,sum(credits*pointers)/sum(credits) GPA
from (takes natural join course) natural join grade_points
group by id;
- 3.3
a)
update instructor
set salary=salary*1.1
where dept_name='Comp. Sci.';
b)
delete from takes
where course_id not in(select course_id
from section);
c)
insert into instructor
select id,name,dept_name,10000
from student
where dept_name='Comp. Sci' and tot_cred>=100;
- 3.4
a)
select distinct count(report_number)t
from accident,car,participated
where year=2009 and accident.report_number=participated.report_number;
b)
insert into accident
values(3211,'2016,3,8','King Road');
c)
delete from car
where model='Mazda' and license in(select license
from person p,owns o
where p.drive_id=o.drive_id and p.name='John Smith');
- 3.5
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)
with grades as(select ID，
case when score<40 then 'F'
when score<60 then 'C'
when score<80 then 'B'
else 'A'
end as grade
from marks;)
select grade ,count(ID)
from grades
group by grade;
- 3.6
select dept_name
from dept_name
where lower(dept_name) like '%sci%';
- 3.8
a)
(select customer_namme
from depositor)
minus
(select customer_name
from borrower);
b)
select c.customer_name
from custormer c join customer f using(customer_street,customer_city)
where f.customer_name='Smith';
c)
select branch_name
from account natural join customer
where customer_ity=Harrison';
- 3.9
a)
select e.employee_name,city
from employee e natural join works w
where w.company_name='First Bank Corporation';
b)
select e.employee_name,e.street,e,city
from employee e natural join works w
where w.salary>=10000 and w.company_name='First Bank Corporation';
c)
select employee_name
from works
where employee not in (select e.employee_name
from employee e natural join works w
where w.company_name='First Bank Corporation');
d)
select employee_name
from works
where salary>(select salary
from works
where company_name='Small Bank Corporation');
e)
select ditinct(city)
from company
where company_name='Small Bank Corporation';
f)
select company_name
from works
group by company_name
having max(count(w.employee_name)) in (select count(distinct(w.employee_name))
from works
group by company_name );
g)
select company_name
from works
group by company_name
having avg(salary)>(select avg(salary)
from works
where company_name='First Bank Corporation');
- 3.19
a)
update employee
set city='Newtown'
where employee.employee_name='Jones';
b)
update works w
set w.salary=w.salary*1.1
where w.employee_name in (select m.managers_name
from manager m
where m.employee_name=w.employee_name and w.salary<100000
and w.employ_name='First Bank Corporation');
- 3.11
a)
select distinct(name)
from student
where dept_name='Comp. Sci.';
b)
select id, name
from student natural join takes t
minus
select id,name
from student natural join takes t
where year < 2009 ;
c)
select distinct(dept_name),max(salary)
from instructor
group by dept_name
Order by max(salary);
d)
select min(maxsalary)
from (select dept_name,max(salary) maxsalary
from instructor
group by dept_name);
- 3.12
a)
insert into course
values('CS-001','Weekly Seminar','Comp. Sci',0);
b)
insert into section
values('CS-001',1,'Autumn',2009,null,null,null);
c)
insert into takes
select id,'CS-001',1,'Autumn',2009,null
from student
where dept_name='Comp. Sci';
d)
delete from takes
where course_id= 'CS-001' and sec_id = 1 and year = 2009 and semester = 'Autumn' and id in
(select id from student where name = 'Chavez');
e)
delete from takes
where course_id='CS-001'
delete from section
where course_id='CS-001'
delete from course
where course_id='CS-001'
如果先运行删除语句，没有删除这门课程的授课信息，就会违背外键原则，应为section 与course 之间有外键关系。
f)
delete from takes
where course_id in(select course_id
from course
where lower(title) like'%database%');
- 3.13
create table person
(drive_id varchar(20),
name varchar(20),
address varchar(30),
primary key(drive_id));
create table car
(license varchar(20),
model varchar(20),
year integer,
primary key(licese));
create table accident
(report_number integer,
date date,
location varchar(20),
primary key(report_number));
create table owns
(drive_id varchar(20),
license varchar(20),
primary key(drive_id),
foriegn key(drive_id) references person,
foriegn key(license) references car);
create table participated
(report_number integer,
license varchar(20),
driver_id varchar(20),
damage_amount integer,
primary key(report_number,license),
foriegn key(report_number) references accident,
foriegn key(license) references car,
foriegn key(driver_id) references owns);
- 3.14
a)
select count(report_number)
from accident,person p natural join participated P natural join owns o
where p.name='John Smith'and o.drive_id=P.drive_id;
b)
update participated
set damage_amount=3000
where report_number='AR2197', license='AABB2000';
- 3.15
a)
select discount customer_name
from depositor a
where not exists(select 1 from branch b
where branch_city='*lyn' and not exists(select 1 from account c
where a.account_number=c.account_number and c.branch_name=b.branch_name))
b)
select sum(amount)
from loan
c)
select branch_name
from branch
where assets>some
(select assets
from branch
where branch_city='*lyn');
- 3.16
a)
select employee_name
from employee e natural join works w
where w.company_name='First Bank Corporation';
b)
select e.employee_name
from employee e,company c,works w
where e.city=c.city and e.employee_name=w.employee_name and w.company_name=c.company_name;
c)
select e.employee_name
from employee e,employe r, managers m
where e.employee_name=r.employee_name
and r.employee_name=m.manager_name
and r.strret=e .street
and r.city=e.city;
d)
.select w.employee_name
from works w
having salary >(select avg(salary)
from works w natural join employee e
where e.employee_name=w.employee_name);
e)
select company_name
from works
group by company_name
having sum(salary) =(select min(sum(salary))
from works
group by company_name)
- 3.17
a)
update works
set salary=salary*1.1
where company_name=''First Bank Corporation';
b)
update works
set salary=salary*1.1
where company_name='First Bank Corporation' and employee_name in(select employ_name
from managers m natural join employee_name e where e.employee_name =m.manager_name)
c)
delete from works
where company_name='Small Bank Corporation';
- 3.21
a)
select m.name
from member m,book b,borrowed Because
where b.publisher='McGraw-Hill' and m.memb_no=B.memb_no and b.isbn=B.isbn;
b)
select m.name
from member m,book b, borrowed B
where b.isbn=all(select isbn
from books
where publisher='McGraw-Hill') and m.memb_no=B.memb_no and b.isbn=B.isbn;
c)
select b.publisher,m.name
from member m,book b,borrowed B
having b.count(isbn)>5 and m.memb_no=B.memb_no and b.isbn=B.isbn
group by publisher;
d)
with countmember(countid) as(select count(*)
from member)
select count(*)/countmember.countid avg_book
from borrowed,countmember ;
- 3.23
因为 takes 和 section有共同的外键，所以takes的某一个元组不会因此增加额外的元组。- 3.24
select dept_name
from instructor
having sum(salary)>=(select avg(sum(salary))
from instructor
group by dept_name )
group by dept_name;