数据库第四次作业

该次作业是在课后习题的基础上，混合，修改，增加得到的题目

注意把2017改成2019

第一题

Consider the insurance database of Figure 3.17, where the primary keys are underlined. Construct the following SQL queries for this relational database.

1.1

Find the total number of people who owned cars that were involved in accidents in 2019.

select count(distinct driver_id)
from participates join accident using (report_number)
where date = "2019";

1.2

Find the number of accidents in which the cars belonging to “John Smith” were involved.

select count(report_number)
from participates
where driver_id in (
	select driver_id
	from person
	where name="John Smith");
	
	
select count(report_number)
from participates natural join person
where name="John Smith";

1.3

Add a new accident to the database; assume any values for required attributes.

insert into accident values("66666", "2019", "NewYork");

1.4

Update the damage amount for the car with the license number “AABB2000” in the accident with report number “AR2197” to $3000.

update participates
set damage_amount = 3000
where report_number="AR2197" and license="AABB2000";

1.5

Delete the Mazda belonging to “John Smith”.

delete from owns
where (owns.driver_id, owns.license) = (
	select driver_id, license
	from (person natural join owns) join car using (license)
	where name="John Smith" and model="Mazda");

第二题

Write the following queries in SQL, using the university schema.

2.1

Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result.

select distinct name, ID
from (takes natural join student) join course using(course_id)
where course.dept_name="Comp. Sci."
group by name,ID
having count(*) > 1
order by name;

2.2

Find the IDs and names of all students who have not taken any course offering before Spring 2019.

select name, ID
from (takes natural join student) 
group by name, ID
having min(year)>2017;

2.3

For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor.

select dept_name, max(salary)
from instructor
group by dept_name;

2.4

Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query

select dept_name, min(max_salary)
from (select dept_name, max(salary)
  	from instructor
  	group by dept_name)
  	as dept_salary(dept_name, max_salary);