4试验四复杂查询

1、实验四复杂查询一、实验目的掌握两个表以上的连接查询的应用,包括嵌套查询.二、实验内容(1)查询比 林红年纪大的男学生信息.select * from Studentwhere Sex =男andYEAR( Birth )-( select YEAR( Birth )from Student where Sname =林红) 0ZQY-PC.StuderitJnfo - dbo.Stuart SQLQueryl.tqI - ZQ2qy-PCzqy (55)* jel&Gt T fromwhere -男Birth - i select ifEAK.10航 5匚口点ur匚 whets =林号工；.J

2、j国结果I今消息! ,| Sho | 5口日rne | Seoc | EWh Cfanmo ErirarM ddt 匕 H口ne mdd r dept Pbateode(2)检索所有学生的选课信息,包括学号、姓名、课号、课程名、成绩.select SC. Sno , Sname , Sex , Classno , Cname , Gradefrom Student s, SC, Course cwhere s. Sno =SC. Sno and SC. cno =c. cnoSQLQu eryLsql - ZQ-(wqy-PCqy (S3) )*E select SC . Snor Sname

3、, 5ex., Clas3n. Cname. Grade from Srudent s,SCf Course c-wheie s . Snc=SC . Snc mn S C. crLO=. cnc二结果| 3消息ISno 1SnarneSex| ClassnCname| Grade120220001晒里051高搠85a2022&001晒s051c语言程序设计蔼_3_Mil MOIW题里051JAVA语言程序设计S342Q11&002椒s051匚语言程序设计60J_20220103赵青男061高麴3.(3)查询已选课学生的学号、姓名、课程名、成绩.select SC. Sno , Sname ,

4、 Cname , Grade from Student s, course c, SCwhere s. sno =SC. sno and c. cno =SC. cnoSQLQueryLI - ZQ.(iqy-PCzqy (52)*El select SC. Stio, f Crarr.e, ade from Student 3 tcourse SCw;ieze s . 3.o-SC. sno &二d. c. c-no = SC. cn.c|0结果|_3消息|SrwSname| CnanteGrade之:20220001张虹翩的加1刖张虹.语言程序设计7320220M1张虹闻依酉言卷序设计脯J

5、_201KM2林红C语言程序设计即_5_20220103赵青即(4)查询选修了 C语言程序设计的学生的学号和姓名. select sc . Sno , Sname from Student s, course c, scwhere c. Cname = C 语言程序设计 and s . Sno =sc . Sno and sc . Cno = c. CnoQLQueryl.s.ql - ZQ._(zqy-PCzqy (52), aelecv 5G. SnoSnaTTie|from StnderDt sr courss cr sc-where t.gxire二11二语台程序设计、and a. S

6、nc=s . Sne =rd sc , Cnc =e . Cue11口,果岛消色| Snp , Snarne i |j 2oijoooi 张虹2 ioHoSi?林红(5)查询与 张虹在同一个班级的学生学号、姓名、家庭住址. a.用子查询select Sno , Sname , Home_addrfrom Studentwhere Classno =051 and Sname !=张虹5QLQuery2.ql - ZQCiqy-PCzqy 04)*SQLQueryLM ssltet 与七匕 1r Enair且.Hait.5_addfzoic 3七口五目二it-Mhu匚七 Cla35no=, 05

7、Lr and 3口己mu! = 张虹卜Jjra结果|母消号I殳? Sfiwi. I Hame_addrT- 笳彳痂弋林互北京b.用连接查询select Sno , Sname , Home_addrfrom Studentwhere Classno =( select Classno from Student where Sname =张虹) and Sname !=张虹SQLQuEry25ql-NQ.在qy-PCVqy (54)* 1号ryLsql - NQ“.但qy-PCqy (52)yB select 3rLeSnamsrHoir.e_dddrfrom StuderLCwhere Cla

8、ssno= i select： Classno f rorr. Student; where 5nair.e=,张虹,Land Sname!=,张虹.,一日产果展消息|一生:Sham金 | Hernefddr | 1 J 2022000Z 林红 北京irmiiii ravm nan lirnf(6)查询其他班级中比 051班所有学生年龄大的学生的学号、姓名.select Sno , Snamefrom Studentwhere Classno 051and Birth all( select Birth from Studentwhere Classno = 051)SQLQuEryl.5ql

9、 - ZQ-值qy-PCzqy (52)* ZQY-PC.StudertjnfQ - dbo.Gt日 select Sno, Snair.efrom Srudenuwhere Classno o 105L1 and Birth 202200021 and fl&t exists select * from SC as 5C3 where SC3.Sno-SCl.Snc andSC3 -cno=SC2 -cno)- )l结果| A消息IjSn Snam.120220001 I 弓融1220220002 林红(9)检索学生的学号、姓名、学习课程名及课程成绩.select s . Sno , Sna

10、me , Cname , Gradefrom Student s, Course c, SCwhere s. Sno =sc . Sno and sc . Cno =c. CnoV-PC.Studentjnfo - dbo.SC 冷LQuoryl叫I - ZQ. select 号.3口口. 5门mrr.f,匚nmirw,白工43型from Student 3FComs c,5C-wriere b . Snc=sc . Sno encl sc . Cn = c . CncSnoSriameCnameGrade20H&0011张虹高效明20220001张虹匚语言程庠设计7820220001张虹JA

11、VR语司酹设计四20220002林红匚语言程序设计60202201(B赵青高数口结果|二消息4223课程号、(10)检索选修了 高数课且成绩至少高于选修课程号为002课程的学生的学号、成绩,并按成绩从高到低次序排列.由题意得,选修了高数课的学生的成绩要高于选修00鲫号课程的学生的成绩select distinct Sno , Cno, Grade from SCwhere Cno in( select Cno from Course where Cname =高数) and Grade ( select MAX( Grade ) from SC where cno =002) order by

12、 Grade descZQ-式,Studentjrr2-dbc.SC 织LQueryLsql - ZQ.(zqyE select distinct 5nof Cno Grade from SC where Cnc in( select Cno froir Course EhEtu局数*andt3elect MAX(Gradeifrom 5C where cnc-002) ardtr by Srade d&aeJ国幕果| J消息|殳g| On. Grade1 j 20l-loi j DOI 肘2 ,元而而 001 M(不统计不及格的课程),并要求按总成(11)检索选修3门以上课程的学生的学号、总

13、成绩 绩的降序排列出来.select Sno , sum ( grade ) as 总成绩from SCwhere Sno in(select Sno from SCgroup by Sno having count (*) 3) and Grade = 60group by Snoorder by 总成绩 desc/NQrPQStudgrrtJnfo - dboViqLqoryl ,s select Sno, aCgradej as .成绩 SCwhere Snp 土r r select Sno from 3匚 group by Snc having count iT i 3： a.r.d

14、GradeDgroup by 3noL order 总成债 descJ结果_j消息I Sho |总成寂(12)检索多于3名学生选修的并以3结尾的课程号的平均成绩.select avg ( Grade ) as 平均成绩from SCwhere Cno like %3group by Cnohaving count ( Cno ) 3/ZQPCtudwrtJn1b - dlMhSg5QLQueryLE select a va (Gi a de ： as 平均成绩I ram SC-where Cno likre R %3,group by CnoLeaving ca.t *n.o 3IdB结果14

15、消息|：平均成转(13)检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分.select distinct SC. Sno 学号,Sname 姓名,max ( grade ) as 最高分,min ( grade ) as 最低分from Student , SCwhere SC. Sno =Student . Snogroup by SC. Sno , Snamehavingmax ( grade )- min ( grade ) 52QY-PC.tudent_info - dbo.SC EQLQueryl.sql - ZQ_(zqy-PCzqy (S2H selecL dis

16、tinct; 5C5no 学号口皿 姓名,ir.ax (grade) as 最高分irin praciEW巴 最低分from SELidenc,. 5Cwhers SC.Snc=5tdent,5nogroup by SC. Sdq, Snair.eL having max ；grade; -irin i grade 5* I口结果|由消即|道 姓名最高分|最低511 Jj 20220001 j 张虹的 7B(14)外连接对实验二中的表6和表7做一个外连接查询,显示每门课程的课号、课名、选修该门课的学号、成绩,没有同学选修的课程(如 Visual_Basic )也要在查询结果中.select c

17、. Cno 课号,Cname 课名,Sno 学号,Grade 成绩from Course c left outer join SCon ( c. Cno =SC. Cno)ZQY PC-Stude-n- dbo.SC SQLQueryl.scfl - ZQ.(zqy-PCzqy (5S select c o课号,Satte课名,3nci学号,仁工0加 成结from Course c Left 亡亡史二 join SC-on (c*Cno=5C o)J结果I A消息I津里课名字号成绩17离数2022CCT1392DOT离数20220103_3_002c语言程序设计20220017B4002c语言

18、程惇世计2022(X)0260003此办语言程序设计刻1阿1的J_004Visual_BasicNULLNULL(15)创立一个表 Student_other,结构同Student,输入假设干记录,局部记录和Student表中的相同.创立过程：create tableStudentotherSno char (8)Sname varcharSex char (2)primary(8) not not null,key ,null,Birth smalldatetimenot null,Classno char ( 3) not null, Entrance_date smalldatetime

19、 not null, Home_addr varchar (40), Sdept char ( 2) not null, Postcode char ( 6) )ZQ Y - PC. St d e nt_i nfo - dboStud?nt SQLQu&ryl.sql - czeate table 5tudent_othei( Srci ehar primary Jcey, Eriarre vazchai S j f口七 二七2.二, Sex ahax 2 not nullt Sixth 自mal210atut二it虎 二f Classno char (3j net. null,Entxanc

20、edate ama 21 d at er line not rJll f icme_addr varchar (40 f Sdept chai 12 not ziull, Pcatcode chaxL)1必消息|命令已成功完成.1= j StudenLinfo田数揖奉关玉圄日以表,_ dbc.Coure n dbo.SC,+ n dbo.Student0 口 d b o. St jd e nt_other国 lJ F+i引中指lZ * PC. StG - dbokuckE 2QV - PC. Mu dent_i nT_ dba.Student_athrSnoSrameSexf

21、trthClassic&i1raic2022DC01张虹同1952-0-01.口;一051201L-09-wiin ：oj越青月汨 qmgii mOS1州 114d201X)111王五J991O629OO：,0 0610 2022-09*MULLNULLNULLHJULNULLNULL随意输入几条Student表中没有的信息,完成创立a.查询同时出现在 Student表和Student_other表中的记录 select * from student_other so, Student s where so . Sno =s . SnoSQLqueryL&ql - ZQ.Xzqy-PCVzqy

22、QLQuery2,sql - ZQ.；zqy-PC2qy select * f工口而 student other sorStudent 5 uhere so * Sao-s.Snod1J 回手果岛消尊|Sno Snarnm | Ssk Bhth ,Classno Entjance_dateHome_addr | SJept |1； 2022 MOI j 张虹 用 1992()100:(B;W 12022801 MHXHW 南京 CS 2 Slloioa里 1993-DM1 OD：:00 061M:OT:00 上海 MSb. 查询Student表和Student_other表中的全部记录sele

23、ct* fromstudentunionselect* fromstudent_other&QLQueryl,ql - ZQ(aqy-PCqy (52)y QLQuery2,sql - ZQ. Jzqy-PC2qy (54)产日n曰工噌口七二工0n3工中立噌atgis-select 七 front student; otiierI(16)(选作)创立一个数据库Student_info_other, 参数 臼 定.m结果i消息S-DSnameSexEiittiQassnoEntrance_datel-k)rrie_dddradept1201T0001i兆虹里1 的2 旬%1 D0:00:00时1

24、2022-mi (HK0M南京CS1_20220M2慢工女1 Ml-11-12 00:00:00051mu-mi (w oo oo北京CS2_20224143赵青勇1为+后11 M;帅览Ml2&n-mi m w上海MSj_20220111王五女1M1-D&29 00:00:00EKI11-09-11 00:00:00北克MS创立过程:if 5 X.6 ZQV-PC gL ScrwrJ国二 王 臼ljh新建数据库rrmftj.丽竺*EE?ws：tHLrSrt&-I n dbc tbJn Q小* 国Q可三触推月女斗0):名称文件隅I文件我j.行树aPUHARTEtudit_.日志不适用迹型:竺口自延长j洋量为1股,不琲制熠长 :妈里为1出彳限方1擅长C Fir,pn FilacVflicC Fro-an FslftEHic名称确定,参数自定义,然后“确定即可a.当前数据库为 Stu