数据库作业答案

1、SQL作业：第三章：课后习题2：(1)select sno,sname from student where height>1.80(2)select cno,credit from course where semester='秋' and cno like 'CS%'(3)select sname,o,credit,grade from student s,course c,scwhere s.sno=sc.sno and o=o and o like 'CS%' and sex='男'and semester='

2、;秋'and grade is not null(4)select sname from student s,scwhere s.sno=sc.sno and cno like 'EE%' and sex='女' or:select sname from student s,scwhere s.sno=sc.sno and cno like 'EE%' and sex='女' group by s.sno,sname having count(cno)>=1(5)select sno,count(cno),avg(g

3、rade) from scwhere grade is not null group by sno(6)select cno,count(sno),max(grade),min(grade),avg(grade) from scwhere grade is not null group by cno(7)select s.sno,sname from student s,scwhere s.sno=sc.sno and s.sno not in(select sno from sc where grade is null) and grade is not null group by s.sn

4、o,sname having min(grade)>=80(8)select sname,o,credit from student s,course c,scwhere s.sno=sc.sno and o=o and grade is null(9)select sname from student s,course c,scwhere s.sno=sc.sno and o=o and credit>=3 and grade<70or:select sname from student s,course c,scwhere s.sno=sc.sno and o=o and

5、 credit>=3 group by sc.sno,sname having min(grade)<70(10)select sname,avg(grade),sum(credit) from student s,course c,scwhere s.sno=sc.sno and o=o and year(bdate) between 1974 and 1976 group by sc.sno,sname习题3：delete from Studentswhere SNO LIKE '91%' delete from SCwhere SNO LIKE '91

6、%'习题4： INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)VALUES ('9409101','何平','女','1977-03-02',1.62) INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)VALUES ('9408130','向阳','男','1976-12-11',1.75) 习题5：UPDATE CoursesSET CREDIT=3,LHOUR=

7、60WHERE CNAME='CS-221'补充题：试写出下述查询的SELECT命令1.1查c4比c16成绩好,但比c9成绩差的课程及其成绩;select course-name,gradefrom student s4,sc sc4,course cou4,student s16,sc sc16,course cou16,student s9,sc sc9,course cou9where s4.sno=sc4.sno and o=o and s4.sname='c4' and s16.sno=sc16.sno and o=o and s16.sname=&#

8、39;c16' ands9.sno=sc9.sno and o=o and s9.sname='c9' and and o=o and o=osc4.grade>sc16.grade and sc4.grade<sc9.grade1.2查修过计算机系课程，但未修过电机系课程的学生的数量;select count(distinct sno)from sc,coursewhere o=o and cname like 'cs%' and sno not in (select sno from sc,course where o=o and cna

9、me like 'ee%') 1.3 列出学生张学友不比学生李连杰成绩高的课程名及其成绩;select ame,z.grade from (select cno,grade from sc sc1,student s1where sc1.sno=s1.sno and s1.sname='张学友') as z(cno,grade), (select cno,grade from sc sc2,student s2where sc2.sno=s2.sno and s2.sname='李连杰')as l(cno,grade),course cwhere

10、 o=o and z.grade<=l.grade and o=o下面的题目要利用db2中的sample数据库employee(eid,ename,dept_id,salary);manager(eid,dept-id);sale(eid,district,sale-year,sale-month,sale-goods,sale-amount);2.寻找一个部门中,工资数额多于本部门一半人的工资数额的雇员的名字和工资和部门编号.select ename, salary, dept-idfrom employee ewhere (select count(*) from employee

11、where dept_id=e.dept_id)/2 <=(select count(*) from employee where dept_id=e.dept_id and salary<e.salary)3寻找职员和经理有相同名字的部门select dept_idfrom manager,employee ewhere manager.eid=employee.eid and exists (select ename from emplyee where eid!=e.eid and e.dept_id=dept.id and e.ename=ename)4找有相同职员数的部门

12、名对select d1.dept_id, d2.dept_idfrom employee d1, employee d2where d1.dept_id>d2.dept_id and(select count(*) from emplyee where dept_id=d1.dept_id)=(select count(*) from emplyee where dept_id=d2.dept_id)5分别按年月,按区域和销售人员统计( 最大）销售额.select sum(sale-amount)from sale sgroup by distirct, sale-year,sale-m

13、onth, eidhaving sum(sale-amount) >= all (select sum(sale-amount)from sale s1where s1.district=s.district and s1.sale-year=s.sale-year and s1.sale-month=s.sale-monthgroup by distirct, sale-year,sale-month, eid)附sample数据库模式employee(eid,ename,dept_id,salary);manager(eid,dept_id);sale(eid,district,sa

14、le-year,sale_month,sale-goods,sale-amount).6 用SQL命令产生如下的关系模式： 供应者supplier(sno, sname, city),主键是sno 部件part(pno,pname,color,weight), 主键是pno 工程job(jno,jname,city)，主键是jno 供应数量spj(sno,pno,jno,quantity),主键是(sno pno jno)各个字段的类型自行定义，但需满足如下的约束：apart中的weight应大于100;b supplier中的city取值范围是(上海西安南京)；c spj中如果quantit

15、y小于10,则删除该记录；d spj中的sno、pno、jno分别和supplier.sno、part.pno、job.jno相关联，并且如果supplier、part或job中的记录被删除，则spj的相关记录也被删除。7 据第6题中的关系模式，完成下列查询：7.1 取出所有在上海的工程的全部细节(即所有相关信息)；select * from supplier s,part p,job j,spj where j.city='上海' and j.jno=spj.jno and spj.pno=p.pno and spj.sno=s.sno7.2 取出给上海或北京的任何工程提供部

16、件的供应者号(sno)；select distinct sno from job,spj where job.jno=spj.jno and (job.city='北京' or job.city='上海')7.3 取出不提供红色部件给上海的工程的北京的供应者号；select sno from supplier where city='北京' and sno not in (select distinct sno from job,spj,part p where job.city='上海' and p.pno=spj.pno an

17、d j.jno=spj.jno and p.color='红色')7.4 列出每个城市中每一种零件的供应者个数及其总数；select city,p.pno,count(distinct sno),sum(quantity) from supplier s,part p,spjwhere s.sno=spj.sno and p.pno=spj.pnogroup by city,p.pno7.5 列出至少有5个电动机供应者的城市。select distinct city from supplier s1where (select count(distinct s.sno) from

18、 supplier s,part p ,spj where s.sno=spj.sno and spj.pno=p.pno and p.pname='电动机' and s.city=s1.city)>=5设有学生选修课程数据库s(sno,sname,age,sex,department,address)sc(sno,cno,grade)c(cno,cname,teacher)试用SQL命令查询下列问题。4.1列出每个男生的平均成绩、最高分，和总分select s.sno,avg(grade),max(grade),sum(grade) from s,sc where s.

19、sno=sc.sno and s.sex='男'group by s.sno4.2统计各系的男生和女生的人数select department,sex,count(*) from s group by department,sex4.3求出平均成绩在85分以上的课程名及其平均成绩、最高成绩select cname,avg(grade),max(grade) from sc,c where o=ogroup by o,cnamehaving avg(grade)>=854.4选出学习过编译，数据库或体系结构课程，且这些课程的成绩之一在90分以上的学生的名字select sname from swhere sno in(select sno from sc,c where o=o and cname in ('编译'，'数据库'，'体系结构') and grade>=90) orselect sname from swhere 90<=some(select grade from sc,c where s.sno=sc.sno and o=o and cname in ('编译'，'数据库'，'