// a.
select title from course where credits = 3 and dept_name = 'Comp.Sci'
// b. 我做的
select distinct ID from takes where course_id in (select tea.course_id from instructor as ins nature join teaches as tea where ins.name = 'Einstein' )
// 标准答案 使用了 using 简化连接
select distinct student.ID from (student join takes using(ID)) join (instructor join teaches using (ID)) using (course_id, sec_id, year) where instructor.name = 'Einstein'
// c. 不使用聚合
select salary from instructor where id not in (select ins1.id from instructor as ins1 join instructor as ins2 where ins1.salary < ins2.salary)
// 使用聚合 标准答案
select max(salary) from instructor
// d. 不使用聚合
select salary from instructor where id not in (select ins1.id from instructor as ins1 join instructor as ins2 where ins1.salary < ins2.salary)
// 使用聚合
select ID, name from instructor where salary in (select max(salary) from instructor)
// e.
select course_id, sec_id, count(ID) from takes join section using (course_id, sec_id, semester, year) where year = 2009 and semester = 'Fall' group by course_id, sec_id
// 或者
select course_id, sec_id, count(ID) from takes natural join section where year = 2009 and semester = 'Fall' group by course_id, sec_id
// f.
select max(maxCount) from (select count(ID) as maxCount from takes join section using (course_id, sec_id, semester, year) where year = 2009 and semester = 'Fall' group by course_id, sec_id) as T
// g.
with sec_enrollment as (select course_id, sec_id, count(ID) as maxCount from takes natural join section where year = 2009 and semester = 'Fall' group by course_id, sec_id)
select course_id, sec_id from sec_enrollment where maxCount in (select max(maxCount) from sec_enrollment)
// a.
select sum(c.credits* g.points) from takes as t natural join course as c natural join grade_points as g where t.ID = 12345
// 当然，上面的查询方式在没查到时会返回空而不是0，所以做以下修正
select sum(c.credits* g.points) from takes as t natural join course as c natural join grade_points as g where t.ID = 12345 union select 0 from student where not exists(select * from student where takes.ID = 12345)
// b.
select sum(c.credits* g.points)/ sum(c.creaits) as GPA from takes as t natural join course as c natural join grade_points as g where t.ID = 12345 union select null as GPA from student where not exists(select * from student where takes.ID = 12345)
// c. 使用左连接 可以使未选课的学生置空
select student.ID, student.name sum(course.credits* grade_points.points)/ sum(course.creaits) as GPA from student natural left outer join takes natural join course natural join grade_points group by student.ID
// a.
update instructor set salary = salary*1.10 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
// a. 我做的答案
select count(distinct p.driver_id) from accident as a natural join participated as p where accident.date = '2009'
// 标准答案
select count(distinct driver_id) from accident, person, participated where accident.report_number = participated.report_number and person.driver_id = participated.driver_id and accident.date = '2009'
// b. 需要同时更新accident和participated两张表
insert into accident(report_number, date, location) values (2021, '2021-01-01', 'Changchun')
insert into participated
select o.driver id, c.license, 4007, 3000
from person p, owns o, car c
where p.name = ’Jones’ and p.driver id = o.driver id and
o.license = c.license and c.model = ’Toyota’
// c.
delete from car where model = 'Mazda' and license in (select license from person natural join owns where name = 'John Smith')
// a.
select ID , case
when score < 40 then 'F'
when score < 60 and score >= 40 then 'C'
when score < 80 and score >= 60 then 'B'
else 'A'
end from marks
// b.
with grades as(
select ID, level = case
when score < 40 then 'F'
when score < 60 and score >= 40 then 'C'
when score < 80 and score >= 60 then 'B'
else 'A'
end from marks)
select count(ID), level from grades group by level
select name from myTable where lower(name) like '%sci%'
当r1和r2都不为空的时候
// a 我做的答案
select d.account_number, d.customer_name from depositor as d where not exists (select * from borrower as b where b.customer_name = d.customer_name)
// 标准答案
(select customer_name from depositor) except (select customer_name from borrower)
// b.
select distinct c2.customer_name from customer as c1 join customer as c2 using (customer_street, customer_city) where c1.customer_name = 'Smith'
// c.
select distinct branch_name from account natural join depositor natural join customer where customer_city = 'Harrison'
// a.
select distinct employee_name, city from employee natural join works where company_name = 'First Bank Corporation'
// b.
select distinct employee_name, street, city from employee natural join works where company_name = 'First Bank Corporation' and salary > 10000
// c.
select distinct employee_name from employee where employee_name not in (select employee_name from where company_name = 'First Bank Corporation')
// d.
select distinct employee_name from works where salary > all(select salary from works where company_name = 'Small Bank Corporation')
// e. 我做的答案
select distinct company_name from company where city in (select city from company where company_name = 'Small Bank Corporation')
// 标准答案1
select c1.company_name from company as c1 where not exists(select city from company where company_name = 'Small Bank Corporation' except select city from company as c2 where c1.company_name = c2.company_name)
// 标准答案2
select c1.company_name from company as c1 where not exists (select city from company as c2 where c1.company_name = c2.company_name contains select city from company as c3 where c3.company_name = 'Small Bank Corporation')
// f.
select company_name from works group by company_name having count(employee_name) > all (select count(distinct employees_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')
// a.
update from employee set city = 'Newtown' where employee_name = 'Jones'
// b.
update from works set salary =
(case
when salary <= 100000 then salary* 1.10
else salary*1.03) where company_name = 'First Bank Corporation' and employee_name in (select employee_name from managers)