《Database System Concepts（数据库系统概念）》第六版——第二次作业（第三、四章）

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为非空不在上述情况中，故无可能