数据库5th第三章课后部分习题答案.docVIP

3.1 ************************************************************* Note: The participated relation relates drivers, cars, and accidents. a. Note: this is not the same as the total number of accidents in 1989.We must count people with several accidents only once. select count (distinct name) from accident, participated, person where accident.report number = participated.report number and participated.driver id = person.driver id and date between date ’1989-00-00’ and date ’1989-12-31’ b. insert into accident values (4007, ’2001-09-01’, ’Berkeley’) insert into participated select o.driver id, c.license, 4007, 3000 from person p, owns o, car c where = ’Jones’ and p.driver id = o.driver id and o.license = c.license and c.model = ’Toyota’ c. delete car where model = ’Mazda’ and license in (select license from person p, owns o where = ’John Smith’ and p.driver id = o.driver id) ************************************************************* 3.2 a. Query: select e.employee name, city from employee e, works w where pany name = ’First Bank Corporation’ and w.employee name = e.employee name b. If people may work for several companies, the following solution will only list those who earn more than $10,000 per annum from “First Bank Corporation” alone. select * from employee where employee name in (select employee name from works where company name = ’First Bank Corporation’ and salary ? 10000) c. The following solution assumes that all people work for exactly one company. select employee name from works where company name _= ’First Bank Corporation’ If one allows people to appear in the database (e.g. in employee) but not appear in works, or if people may have jobs with more than one company, the solution is slightly more complicated. select employee name from employee where employee name not in (select employee name from works where company name = ’First Bank Corporation’) d. The following solution assumes that all people work for at most one company. select employee