數(shù)據(jù)庫作業(yè)答案

1、SQL作業(yè)：第三章：課后習(xí)題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習(xí)題3：delete from Studentswhere SNO LIKE '91%' delete from SCwhere SNO LIKE '91

6、%'習(xí)題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) 習(xí)題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查修過計算機系課程，但未修過電機系課程的學(xué)生的數(shù)量;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 列出學(xué)生張學(xué)友不比學(xué)生李連杰成績高的課程名及其成績;select ame,z.grade from (select cno,grade from sc sc1,student s1where sc1.sno=s1.sno and s1.sname='張學(xué)友') 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數(shù)據(jù)庫employee(eid,ename,dept_id,salary);manager(eid,dept-id);sale(eid,district,sale-year,sale-month,sale-goods,sale-amount);2.尋找一個部門中,工資數(shù)額多于本部門一半人的工資數(shù)額的雇員的名字和工資和部門編號.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尋找職員和經(jīng)理有相同名字的部門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找有相同職員數(shù)的部門

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分別按年月,按區(qū)域和銷售人員統(tǒng)計( 最大）銷售額.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數(shù)據(jù)庫模式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命令產(chǎn)生如下的關(guān)系模式： 供應(yīng)者supplier(sno, sname, city),主鍵是sno 部件part(pno,pname,color,weight), 主鍵是pno 工程job(jno,jname,city)，主鍵是jno 供應(yīng)數(shù)量spj(sno,pno,jno,quantity),主鍵是(sno pno jno)各個字段的類型自行定義，但需滿足如下的約束：apart中的weight應(yīng)大于100;b supplier中的city取值范圍是(上海西安南京)；c spj中如果quantit

15、y小于10,則刪除該記錄；d spj中的sno、pno、jno分別和supplier.sno、part.pno、job.jno相關(guān)聯(lián)，并且如果supplier、part或job中的記錄被刪除，則spj的相關(guān)記錄也被刪除。7 據(jù)第6題中的關(guān)系模式，完成下列查詢：7.1 取出所有在上海的工程的全部細(xì)節(jié)(即所有相關(guān)信息)；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 取出給上?；虮本┑娜魏喂こ烫峁┎?/p>

16、件的供應(yīng)者號(sno)；select distinct sno from job,spj where job.jno=spj.jno and (job.city='北京' or job.city='上海')7.3 取出不提供紅色部件給上海的工程的北京的供應(yīng)者號；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 列出每個城市中每一種零件的供應(yīng)者個數(shù)及其總數(shù)；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個電動機供應(yīng)者的城市。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設(shè)有學(xué)生選修課程數(shù)據(jù)庫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統(tǒng)計各系的男生和女生的人數(shù)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選出學(xué)習(xí)過編譯，數(shù)據(jù)庫或體系結(jié)構(gòu)課程，且這些課程的成績之一在90分以上的學(xué)生的名字select sname from swhere sno in(select sno from sc,c where o=o and cname in ('編譯'，'數(shù)據(jù)庫'，'體系結(jié)構(gòu)') 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 ('編譯'，'數(shù)據(jù)庫'，'