数据库第三章作业

###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));