###3.1
####a
select title
from course
where dept_name = ‘Comp. Sci.’ and credits = 3;
####b
select distinct student.ID
from (student join takes using(ID))
join
(instructor join teaches using(ID)
using(course_id,sec_id,semester,year)
where instructor.name='Einstein';
####c
select max(salary)
from instructor;
####d
select ID, name
from instructor
where salary = (select max(salary) from instructor);
####e
select course_id,sec_id,count(ID)
from section natural join takes
where semester='Fall'
and year=2009
group by course_id,sec_id;
####f
select max(enrollment)
from (select count(ID)as enrollment
from section natural join takes
where semester='Fall'
and year=2009
group by course_id,sec_id);
####g
with sec_enrollment as(
select course_id,sec_id,count(ID)as enrollment
from section natural join takes
where semester='Fall'
and year=2009
group by course_id,sec_id);
select course_id,sec_id
from sec_enrollment
where enrollment=(select max(enrollment)from sec_enrollment;
###3.2
####a
select sum(credits * points)
from (takes natural join course) natural join grade points
where ID = '12345';
####b
select sum(credits * points)/ sum(credits)as GPA
from (takes natural join course) natural join grade points
where ID = '12345';
####c
select ID, sum(credits * points)/sum(credits) as 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 course
where course_id not in (select course id from section);
####c
insert into instructor
select ID,name,dept_name,10000
from student
where tot_cred>100;
###3.4
####a
select count(driver_id)
from (person natural join participated)natural join accident
where date between date '2009-00-00'and '2009-12-31';
####b
insert into accident
values('911','2001-09-11',New York);
####c
delete from car
where model in (select model
from (car natural join owns)natural person
where name='John Smith' and model='Mazda';
###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 < 60 then 'B'
else 'A'
end as grade
from marks)
select grade, count(ID)
from grades
group by grade;
###3.6
select dept_name
from department
where lower(dept_name)='%sci%';
###3.7
r1和r2不全为空
###3.8
####a
(select customer_name
from depositor)
minus
(select customer_name
from borrower);
####b
select F.customer name
from customer F join customer S using(customer_street, customer_city)
where S.customer name = 'Smith';
####c
select distinct branch_name
from account natural join depositor natural join customer
where customer_city = 'Harrison';
###3.9
####a
select e.employee name, city
from employee e, works w
where w.company_name = 'First Bank Corporation' and w.employee_name = e.employee_name;
####b
select * from employee
where employee_name in
(select employee_name
from works
where company_name = 'First Bank Corporation' and salary > 10000);
####c
select employee_name
from works
where company_name <> 'First Bank Corporation';
####d
select employee_name
from works
where salary>all
(select salary
from works
where company_name='Small Bank Corporation');
####e
select T.company_name
from company T
where (select R.city
from company R
where R.company_name = T.company_name)
contains
(select S.city
from company S
where S.company_name = 'Small Bank Corporation');
####f
select company_name
from works
group by company_name
having count (distinct employee_name) >= all
(select count (distinct 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.10
####a
update employee
set city='Newtown'
where employee_name='Jones';
####b
update works T set T.salary = T.salary ∗
(case when (T.salary ∗ 1.1 > 100000) then 1.03
else 1.1 )
where T.employee_name in
(select manager_name
from manages)
and T.company_name = 'First Bank Corporation';
###3.1
####a
select name
from student natural join takes natural join course
where course.dept_name = 'Comp. Sci.';
####b
(select id, name
from student)
minus
(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 min(maxsalary)
from (select dept_name, max(salary) as 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,'Fall',2009,null,null,null);
####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 year=2009 and semester='Fall' 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';
####f
delete from takes
where course_id in
(select course_id
from course
where lower(title)like '%database%');
###3.13
create table person
(driver_id varchar(50),
name varchar(50),
address varchar(50),
primary key (driver_id));
create table car
(license varchar(50),
model varchar(50),
year integer,
primary key (license));
create table accident
(report_number integer,
date date,
location varchar(50),
primary key (report_number));
create table owns
(driver_id varchar(50),
license varchar(50),
primary key (driver_id,license)
foriegn key (driver_id) references person
foriegn key (license) references car);
create table participated
(report_number integer,
license varchar(50),
driver_id varchar(50),
damage_amount integer,
primary key (report_number,license)
foriegn key (license) references car
foriegn key (report_number) references accident));
###3.14
####a
select count (*)
from accident
where exists
(select * from participated, owns, person
where owns.driver_id = person.driver_id
and person.name='John Smith'
and owns.license = participated.license
and accident.report_number=participated.report_number);
####b
update participated
set damage_amount = 3000
where report_number = 'AR2197' and license = 'AABB2000');
###3.15
####a
with branchcount as
(select count(*)
from branch
where branch_city = 'Brooklyn')
select customer_name
from customer c where branchcount =
(select count(distinct branch_name)
from (customer natural join depositor natural join account natural join branch) as d
where d.customer_name = c.customer_name);
####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
####a
select employee_name
from works
where company_name='First Bank Corporation';
####b
select employee_name
from employee, works, company
where employee.employee_name=works.employee_name
and employee.city=company.city
and works.company_name=company.company_name;
####c
select P.employee_name
from employee P, employee R, manages M
where P.employee_name = M.employee_name
and M.manager_name = R.employee_name
and P.street = R.street
and P.city = R.city;
####d
select employee_name
from works T
where salary > (select avg (salary)
from works S
where T.company_name = S.company_name);
####e
select company_name
from works
group by company_name
having sum (salary) <= all (select 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 employee_name in (select manager_name
from manages)
and company_name = 'First Bank Corporation;'
####c
delete from works
where company_name = 'Small Bank Corporation';
###3.21
####a
select name
from member m, book b, borrowed l
where m.memb_no = l.memb_no and l.isbn = b.isbn and b.publisher = 'McGrawHill';
####b
select distinct m.name
from member m
where not exists
((select isbn
from book
where publisher = 'McGrawHill')
except
(select isbn
from borrowed l
where l.memb_no = m.memb_no));
####c
select publisher, name
from (select publisher, name, count (isbn)
from member m, book b, borrowed l
where m.memb_no = l.memb_no and l.isbn = b.isbn
group by publisher, name) as membpub(publisher, name, count_books)
where count_books > 5;
####d
不会
###3.23
因为takes和section的共同成员是takes关于section的外键,而且也是takes的主键,所以不会改变查询结果。
###3.24
select distinct dept_name d
from instructor i
where
(select sum(salary)
from instructor
where department=d)
>=
(select avg(s)
from
(select sum(salary)as s
from instructor
group by department));