3.3
-
a
update instructor set salary=salary*1.1 where dept_name='Comp.Sci.'
-
b
delete from course where course_id not exists (select course_id from section)
-
c
insert into instruct select ID,name,dept_name,10000 from student where tot_cred>100
3.4
-
a
select count(distinct driver_id) from participated natural join accidents where date like '2009%'
-
b
insert into accident values(114514,'2022-04-02','Shanghai')
-
c
delete from car where model='Mazda' and license = (select license from person natural join participated where name='John Smith')
3.6
-
select dept_name from department where lower(dept_name) like '%sci%'
3.7
-
当p,r1,r2的a1均不为空时。当三者其中之一a1为空时,where表达式值为unkonwn,无法正确查询.
3.14
-
b
update participated set damage_amount=3000 where report_number='AR2197' and license='AABB2000'
3.15
-
a
with c(customer_name,num) as (select customer_name,count(branch_name) from account natural join depositor natural join branch where brach_city='Brooklyn' group by customer_name ) select customer_name from c where c.num=(select count(brach_name) from branch natural join account where brach_city='Brooklyn')
-
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 natural join works,company where works.company_name=company.company_name and employee.city=company.city
-
c
select A.employee_name from employee A,employee B,managers C where A.employee_name=C.employee_name and B.employee_name=C.manager_name and A.street=B.street and A.city=B.city
-
d
with c(company_name,avg_salary) as (select company_name,avg(salary) from works group by company_name) select employee_name from works natural join c where salary > avg_salary
-
e
select company_name from works group by company_name having sum(salary)=min(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 works,managers where managers.manager_name=works.employee_name and works.company_name='First Bank Corporation')
-
c
delete from works where company_name='Small Bank Corporation'
3.23
-
takes 和 section 是通过一些共同的外键相连接,加上与section的连接后不会增加额外的元组,故不会改变查询结果
4.2
-
a
(select * from student natural join takes) union (select ID,name,dept_name,tot_cred,null,null,null,null,null from student where student.ID not exists (select ID from student natural join takes))
-
b
((select * from student natural join takes) union (select ID,name,dept_name,tot_cred,null,null,null,null,null from student where student.ID not exists (select ID from student natural join takes)) union (select ID,null,null,null,course_id,sec_id,semester,year,grade from takes where takes.ID not exists (select ID from student natural join takes)))
4.3
-
a
r(1,2) r(2,3) s(2,3) t(2,4) t(3,6)
-
b
只会出现A B C D,A B C null,和A B null null三种情况 而C为空,D为非空不在上述情况中,故无可能