数据库第三章作业
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
班级：软件工程2班
姓名：方添翼
学号：2012551523
3.1
A.select * from course where dept_name='Comp. Sci.'and credits=3
B.select distinct student.ID
from (student join takes using(ID))
join(instructor join teaches using(ID))
using(course_id,sec_id,semester,year)
where ='Einstein'
C.select max(salary)from instructor
D.select from instructor a where salary=(select
max(salary)from instructor)
E.select course_id,sec_id,count(ID)
from section natural join takes
where semester='Autumn'and year=2009
group by course_id,sec_id;
F.select max(a)
from(select count(id)as a
from takes natural join section
where semester='Autumn'
and year=2009
group by course_id,sec_id)
G.
3.2
A.(select sum(credits*points)
from(takes natural join course)natural join grade points
where ID='12345')
B.select sum(credits*points)/sum(credits)as GPA
from(takes natural join course)natural join grade_points
where id='12345'
C.select id,sum(credits*points)/sum(credits)as GPA
from(takes natural join course)natural join grade_points
group by id
3.3
A.update instructor
set salary=salary*1.1
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
3.4
A.select count(distinct name)
from accident as a,participated as b,person as c
where a.report_number=b.report_number
and b.driver_id=c.driver_id
and data between'2009-00-00'and'2009-12-31'
B.insert into accident
values(1234,'2014-01-01','xiangtan')
insert into participated
select b.driver_id,c.licence,1234,5000
from person a,owns b,car c
where ='jake'and a.driver_id=b.driver_id and
b.license=
c.license and c.model='joe'
C.delete from car
where model='Mazda'and license in
(select license
from person a,owns b
where a.driver_id=b.driver_id and ='john smith')
3.5
A.select id
case
when score<40then'F'
when score<60then'C'
when score<80then'B'
else'A'
end
from marks
B.witn grades as
(
select id,
case
when score<40then'F'
when score<60then'C'
when score<80then'B'
else'A'
end as grade
from marks
)
select grade,count(id)
from grades
group by grade
3.6
select dept_name
from department
where lower(dept_name)like'%sci%'
3.8
a.(select customer_name
from depositor)
except
(select customer_name
from borrower)
B.select F.customer_name
from customer F join customer T using(customer_street,customer_city)
where T.customer_name='Smith'
C.select brance_name
from customer natural join account natural join depositor
where customer_name='Harrison'
3.9
A.select e.employee_name,city
from employee e,wroks w
where pany_name='First Bnak Corporation'and
w.employee_name=e.emloyee_name
B.select * from employee
Where employee_name in
(select employee_name
From wroks