数据库作业2答案

1、.SQL操作：第三章：课后练习2:(1)Select SnO，sname from student where height 1.80(2)Select cno，credit from course where semester=秋天和cno like CS %；(3)Select sname、 o、credit、grade from students、course c、scwhere s . SnO=sc . SnO and c . cn o=sc . cn o and c . cn o like cs % and sex=男人and semester=秋天and grade is

2、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 havingcount (cno)=1(5)Select SnO、count (cno)、avg (grade) from scWhere grade is not null组郑智薰SnO(6)Select

3、 cno、count (SnO)、max (grade)、min (grade)、avg (grade) from scWhere grade is not null群组郑智薰cno(7)Select s.sno，sname from students，scwhere s . SnO=sc . SnO and s . SnO not in(select SnO from sc where grade is null)And grade is not nullGroup by s.sno，sname havingmin (grade)=80(8)Select sname、o、credi

4、t from students、course c、scwhere s . SnO=sc . SnO and c . cn o=sc . cn o and grade is null(9)Select sname from students，course c，scwhere s . SnO=sc . SnO and sc . cn o=c . cn o and credit=3 and grade 70Or:Select sname from students，course c，scwhere s . SnO=sc . SnO and sc . cn o=c . cn o and credit=

5、3组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 sc . cn o=c . cn o and year(bdate)between 1974 and 1976组郑智薰sc。SnO，sname练习3:从Students中删除Where SNO LIKE 91%从Sc中删除Where SNO LIKE 91%练习4:Insert into students (SnO、sname、s

6、ex、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=60WHERE CNAME=CS-221补充问题：请尝试为以下查询编写SELECT命令1.1 C4比C16成绩好，但调查低于C9成绩的课程及其成绩。Select course-name，gradeFrom student S4、scsc4、co

7、urse cou4、student s16、scsc16、course cou16、student S9、scsc9和course cou9wheres 4 . SnO=sc4 . SnO and cou 4 . cn o=sc4 . cn o and S4 . sname=C4 ands16 . SnO=sc16 . SnO and cou 16 . cn o=sc16 . cn o and s16 . sname=C16 andS9 . SnO=sc9 . SnO and cou 9 . cn o=sc9 . cn o and S9 . sname=C9 and andsc4 . cn o

8、=sc16 . cn o and sc4 . cn o=sc9 . cn osc4 . gradesc 16 . grade and sc4 . graded 2 . dept _ id and(count(*)from emplyee where dept _ id=D1 . dept _ id)=(select count(*)from emplyee where dept _ id=D2 . dept _ id)5按地区、地区、销售人员统计(最大)销售额。Select sum(sale-amount)从sale s按distirct、sale-year、sale-month、Eid组ha

9、ving sum(sale-amount)=all(select sum(sale-amount)从sale S1where S1 . district=s . district and S1 . sale-year=s . sale-year and S1 . sale-month=s . sale组by distirct、sale-year、sale-month、Eid)包含示例数据库架构职员(Eid、ename、dept _ id、salary)；经理(Eid、dept _ id)；Sale (Eid、district、sale-year、sale _ month、sale-goods、

10、sale-amount)。6使用SQL命令创建以下关系模式：提供程序supplier(sno、sname、city)，主键为sno零件part(pno、pname、color、weight)，主键为pno工程作业(jno、jname、city)，主键为jno供应数量spj(sno、pno、jno、quantity)、主键(SnO pno jno)每个字段的类型都是自定义的，但必须满足以下约束条件：Apart的weight必须大于100。B supplier的city范围是(上海西安南京)。在C spj中，如果quantity小于10，则将删除记录。D spj中的SnO、pno和jno分别与su

11、pplier.sno、part.pno和job.jno相关联，删除supplier、part或job的记录时，也会删除spj中的相关记录7根据问题6中的关系模式完成以下查询：7.1删除上海所有工程的所有细节(即所有相关信息)。Select * from suppliers，part p，job j，spjWhere j.city=上海and j . jno=spj . jno and spj . pno=p . pno and spj . SnO=s . SnO7.2删除为上海或北京所有项目提供部件的供应商编号(SNO)。Select distinct SnO from job，spjwher

12、e job . jno=spj . jnoand(job . city=北京or job.city=上海)7.3取出不为上海工程提供红色零部件的北京供应商编号。Select SnO from supplierWhere city=北京and SnO not in(distinct SnO from job，spj，part pwhere job.city=上海and p . pno=spj . pno and j . jno=spj . jno ana7.4列出每个城市中每个部件的供应商数和总数。Select city、p.pno、count (distinct SnO)、sum (quant

13、ity) from suppliers、part p、spjwhere s . SnO=spj . SnO and p . pno=spj . pno群组非时间，p.pno7.5列出至少具有5个电动机提供者的城市。在Supplier S1中选择distinct citywhere(select count(distincts . SnO)from suppliers，part p，spjwhere s . SnO=spj . SnO and spj . pno=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、scwhere s.sno=sc.sno and S组郑智薰S. SnO4.2统计各系男生和女生的数量Select department、sex、count (*) fr