201421314431-张和杰-数据库实验三报告

1、仲恺农业程学院实验报告纸计算科学（院、系）信息与计算科学专业 144班数据库原理与应用学号2姓名张和杰实验日期2016-3-21教师评定第14页共10页实验三实验项目:数据库多表查询与视图的创建和使用。实验目的:实验要求:通过实验，要求学生掌握多表查询和使用视图。 成功使用多表查询和视图。实验内容：在SM数据库中进行以下操作:1.使用EXISTS查询所有选修了 001号课程的学生的姓名。select 3口皈乞frait 3Cude口二where ex二atsI select "from 3Csc-Stic = strident.and 3C . CNoI 001 F10srimeII

2、 Vll P-ll -BII j马行空 马舂花商宝盍程灵素 姜按山李莊萌王神萍 篥飞虹 任邇武11凤南天2.使用 NOT EXISTS 查询未选修了 001 号课程的学生的姓名。占七二tut anmwfrort rudenEwheie zrycth ect Kwjiere sn.SNo =3no arid sc-CNoIsnamei馀铮2ffl 归;3苗人風4王剑英5李沅正6汤沛7吕小嫌S钟兆英3童琢道W南匸週11孙馴嵯12余鱼同13苗若兰3.使用NOT EXISTS查询选修了课程学分为4的全部课程的学生的姓名。f rorr 3t:i(±ent vher« :二匸 exis

3、tsfselect 1from course"弓'皂Tii net existsselect .frciL 3Cwtiere 3C. SKo= tudent. 5110 已二;1 ac* Cffo =course. CRoluu 了b¥王仲萃j商宝靈 马行空 马春花 李延駒4.select凤南天统计教职工的总人数、最高工资、最少工资、平均工资。讯口七 总人数,ir.aK （distinct sal最高工资,iriyi （distinct sal最少工资,avj : sal）平均工资-土二匸IE teacher总人数彊高工摄少工一平均工资IIHII 11'=1

4、1111=11111 + 111f 20 i 453+ 孟別lllBIH Illi HI lllll 5.查询选修两门课程以上的学生学号。select snofrorc scgroup by having coujic.(* ) >»2Snoi 00001 jIII II Illi Il -r0000200004 00014 00015 0001600019 000210002510M(rG11000306.查询选修了三门课程的学生学号。I select snofront se-group ty ano liaving coiinr i * ii =3SnoHlllli-hll

5、lllM Illi HI!1 = 00015 =2 000307.使用分组查询选修了所有课程的学生学号、姓名。select snc snsjtefroir 3t;identwhere sno in <select ano from sc gro-jp fcvlaelect cc:二t (*3 f roir. coursei5NoShlame100001码行至200002马春花3000041商至震1在选修课8.001中，使用ALL查询比学号00001和学号00004的成绩都低的学生学号;select 3710from 3CCNo'001 * and Score <£

6、11 (select StoreH二QU占百 whei« tiano00001' or sno = *0000') and CKo = 001'snoIII IO 1 i OOOIO、 1000149.在选修课001中，使用ANY查询比学号00001或学号00004的成绩低的学生学号;select 3110£rcir. sc.where CNo =from 3Cwhere sne =and Scorea： aelecV Score00001' a* sne =00004') and CN& =0口1" >sno

7、i 00002 i0000400010000 u025001中，查询比平均成绩低的学生的学号;10.在选修课select sncfrorr scwhere CNo 二'001' ard Score < select AVG Score) from scii group by SHoSTlQ->>l 11 “一|“亠“I j "ssr"OOOIO00014WO25001的所有学生的总平均成绩;11.按总平均值降序给出所有课程都及格但不包括课程select anc,AVG(frort scwhere CTJo <> '*Or

8、oijp toy 5Nohaving MIN>-60ordfii by AVG(sccxe) desc1SnoK列名j 33.000000j 000212Il IIHBI ml00004"91333333300013ao.ocooao4OOOU89.0000005OD009saoooaoo60002&67.500000700022szocooaoe00Ln2Q/.ooooaog0001104.000000100002560.00000012.更新表student中的年龄，年龄为当前年份减去出生年份（提示，使用year();I »E：date Sfjdentse

9、t SAgs = 2'Oie - YEAR (SBir)13.更新表SC中的成绩，把选修了 “数据库”的成绩加1；updare scset 5ccTe = 5ccTe - 1where eno(elecx eno tioir course Oere CHaire14.在表student中删除学号为00001的学生信息;EXEC SP_HELPCONSTRAINT 3Calter table scdtoF cons匸lain匸 EK_3G_5t=cle匸filter catle scac3d foreign key Csriom references student- isnoj on

10、cSelete cascadedeletefrort Studentwhere student.SNo = '00001 *15.在表course中删除“数据库”课程的信息;EXE匚 5F_ME匚FCCWEHRiHm； scalter tafcle sc-drop constraint fTC se CNo 2 2AA2 9 9ecable3Cadd foreignkey iCNo) reJerencea course CCNq.j on delete cascadedeletefrom course=遨据库,16.在表SC中删除男同学的信息。deletefrcir. 3cwh.ere

11、 Stfo i* (select SMo from Student- where S汙皀jc男*17.建立一个系号为 003的学生表(学号，姓名)，要求把student的所有学号和姓名插入该表。create tat le学生表adept char 115川 defiulE '003'匕 hart 5) not- null jr姓名 Tarchar (not- nailinsertStudent;sdepii学号姓屯00300013執兆英00300014钟兆能00'300019金®同003000030030002700i300021王忡萍0200007王制英00

12、3ooais童杯道00i300022ffl青文QO0Q5田归宓0030001100'300017孙刚嵯02oom商宝震00200a24臺飞虹00'300025任通武曲QEW南仁通0030002)0苗若兰00'300006ffl人風00500002马春花18.使用SQL语句写出课本P70页第(1)求供应工程J1零件的供应商号码 SNO:SELECT DIST SNO FROM SPJ WHERE JNO='日学生表学号姓容-select ano, SMaire £i:om6题的查询。(2)求供应工程J1零件P1的供应商号码 SNO:SELECT DIST

13、 SNO FROM SPJ WHERE JNO='J1' AND PNO='P1' (3)求供应工程J1零件为红色的供应商号码 SNO:SELECT SNO FROM SP J, P WHERE JNO='J1' AND SPJ.PNO=P.PNO AND COLORSJNO:(4)求没有使用天津供应商生产的红色零件的工程号SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELE JNO FROM SP J, P,S WHERE S.CITY 津' AND COLOR红'AND S.SNO=S

14、 PJ.SNO AND P.PN O=S PJ.PNO) 求至少用了供应商 S1所供应的全部零件的工程号JNQ将查询分为两步1、查询S1供应商供应的零件号SELECT DIST PNO FROM SPJ WHERE SNO='S结果是（P1, P2）2、 查询哪一个工程既使用P1零件又使用P2零件。SELECT JNO FROM SPJ WHERE PN O=' P1'AND JNO IN (SELECT JNO FROM SPJ WHERE PNO='P 2')源代码:select sn ame from student where exists(se

15、lect*from scwhere sc.SNo = student.sno and scCNo = '001' select sn ame from student where not exists(select*from scwhere sc.SNo = student.sno and scCNo = '001' select sn ame from student where not exists(select*from coursewhere CCredits = '4'and not existsselect *from scwhere

16、 sc.SNo = student.SNo and sc.CNo = course.CNoselect count(*)总人数，max(distinct sal)最高工资,min (distinet sal)最少工资，avg(sal)平均工资from teacherselect snofrom scgroup by sno having count(*)>= 2select snofrom scgroup by sno having count(*)= 3select s no,sn amefrom studentwhere sno in(select snofrom scgroup b

17、y snohavingcount(*) = (select count(*) from course)select snofrom scwhere CNo ='001'and Score <all( select Scorefrom scwhere (sno = '00001'or sno = '00004') and CNo = '001')select snofrom scwhere CNo = '001' and Score vany( select Scorefrom scwhere (sno = &

18、#39;00001' or sno = '00004') and CNo = '001')select snofrom scwhere CNo = '001' and Score v(selectAVG (Score) from sc)group by SNoselect sno,AVG (score)from scwhere CNo <>'001'group by SNohaving MIN (score) >=60 order by AVG (score) desc up date Stude nts

19、et SAge = 2016 - YEAR (SBir)up date scset Score = Score + 1where eno = (select eno from course where CName ='数据库')EXEC SP HELPCONSTRAINT scalter table scdrop constraint FK_sc_Studentalter table scadd foreign key(sno) references student (sno) on delete cascadedeletefrom Studentwhere student.SNo = '00001'EXEC SP HELPCONSTRAINT scalter table scdrop constraint FK_sc_CNo_22AA2