SQL Server SQL实验与练习题参考答案

数据库原理与应用(数据库原理与应用(SQLServer)练习题-#-操作5.12:显示前n条学生记录信息，n为局部变量DECLARE@nINTSET@n=4SELECTTOP@n*FROMstudent实验6数据查询(2)——条件查询1．使用关系表达式表示查询条件操作6.1：查询dept表中系号为11的院系信息SELECT*FROMdeptWHEREdp_id='11'操作6.2:查询student表中11系的学生学号、姓名、性别和所在系编号SELECTst_id,st_nm,st_sex,st_dpidFROMstudentWHEREst_dpid='11'操作6.3:查询student表中2008年及以后入学的学生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)>=2008操作6.4:在查询student表080808班学生的学号、姓名、性别和入学成绩SELECTst_id,st_nm,st_sex,st_scoreFROMstudentWHERELeft(st_id,6)='080808'2．使用逻辑表达式表示查询条件操作6.5：查询student表中非11系的学生信息SELECT*FROMstudentWHERENOT(st_dpid='11')操作6.6：查询选修了1002号课程且成绩在60以下的学生学号SELECTst_idFROMslt_couseWHERE(cs_id='1002')AND(score<60)操作6.7：查询2007年入学的11系所有男生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)=2007ANDst_dpid='11'ANDst_sex='男'操作6.8：查询11系和12系的学生信息SELECT*FROMstudentWHEREst_dpid='11'ORst_dpid='12'操作6.9：查询11系和12系所有2007年入学的学生信息SELECT*FROMstudentWHERE(st_dpid='11'ORst_dpid='12')ANDDATEPART(yy,st_date)=20073•使用LIKE关键字进行模糊查询操作6.10：查询所有“计算机”开头的课程信息SELECT*FROMcouseWHEREcs_nmLIKE'计算机%'操作6.11：查询所有由三个字组成的“王”姓学生信息SELECT*FROMstudentWHEREst_nmLIKE'王__'操作6.12：查询所有课程名中包含“信息”的课程信息SELECT*FROMcouseWHEREcs_nmLIKE'%信息%'操作6.13：查询学生姓名介于王姓到张姓的信息SELECT*FROMstudentWHEREst_nmLIKE'［王-张］%'4■使用Between^And关键字进行查询操作6.14：查询在1989.7.1到1990.6.30之间出生的学生信息SELECTst_id,st_nm,st_sex,st_birthFROMstudentWHEREst_birthBETWEEN'1981.7.1'AND'1999.6.30'操作6.15：查询选修了1001号课程且成绩在60到80之间的学生选课信息SELECT*FROMslt_couseWHEREcs_id='1001'AND(scoreBETWEEN60AND80)5■使用IN关键字进行查询操作6.16：查询11系、12系、13系的学生信息SELECT*FROMstudentWHEREst_dpidIN('11','12','13')操作6.17：查询所有张，王，李，赵姓的学生的学号、姓名、性别SELECTst_id,st_nm,st_sexFROMstudentWHERELeft(st_nm,1)IN('张','王','李','赵')6•使用［NOT］NULL关键字进行查询操作6.18：查询所有生源为非空的学生信息SELECT*FROMstudentWHEREst_fromISNOTNULL操作6.19：查询选修了1001号课程且成绩为空的学生选课信息SELECT*FROMslt_couseWHEREcs_id='1001'ANDscoreISNULL实验7数据查询(3)——查询排序与查询结果存储操作7.1：查询课程信息，按课程名称降序排序SELECT*FROMcouseORDERBYcs_nmDESC操作7.2：查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序SELECTst_id,scoreFROMslt_corseWHEREcs_id='1001'ANDscoreISNOTNULLORDERBYscoreDESC操作7.3：查询11系学生学号、姓名和年龄，按年龄升序排序SELECTst_id,st_nm,DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)ASageFROMstudentORDERBYageASC操作7.4：查询学生信息，按姓名升序排序，再按系号降序排序SELECT*FROMstudentORDERBYst_nm,st_dpidDESC操作7.5：创建学生表副本studentOl,仅保留学生学号、姓名和性别SELECTst_id,st_nm,st_sexINTOstudent01FROMstudent操作7.6：查询陕西籍学生，将结果保存在新表st_shanxiSELECT*INTOst_shanxiFROMstudentWHEREst_from=，陕西省'操作7.7:查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表sltlOOlSELECT*INTOslt1001FROMslt_corseWHEREcs_id='1001'ORDERBYst_id操作7.8：用局部变量@stage保存学生张三的年龄DECLARE@stageintSELECT@stage=DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)FROMstudentWHEREst_nm='张三'操作7.9：用局部变量@name和@stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩DECLARE@namenVarChar(8),@stscoreintSELECT@name=st_nm,@stscore=st_scoreFROMstudentWHERELEFT(st_id,6)='070101'ORDERBYst_id实验8数据查询(4)——查询统计与汇总操作8.1：查询课程总数SELECTCOUNT(*)FROMcouse操作8.2：查询选修1001号课程的学生人数SELECTCOUNT(st_id)FROMslt_couseWherecs_id='1001'操作8.3：查询被选修课程的数量SELECTCOUNT(DISTINCTcs_id)FROMslt_couse操作8.4：查询选修070101班学生的平均入学成绩SELECTAVG(st_score)FROMstudentWHERELEFT(st_id,6)='070101'操作8.5：查询070101001号学生选修课程的数量、总分以及平均分SELECTCOUNT(cs_id)AS课程数量，SUM(score)AS总分,AVG(score)AS平均分FROMslt_couseWHEREst_id='070101001'操作8.6：查询选修1001号课程的学生人数、最高分、最低分和平均分SELECTCOUNT(*)AS学生人数,MAX(score)AS最高分,MIN(score)AS最低分,AVG(score)AS平均分FROMslt_couseWHEREcs_id='1001'操作8.7：求各个课程号和相应的选课人数SELECTcs_id,COUNT(st_id)FROMslt_couseGROUPBYcs_id操作8.8：统计各班人数SELECTLEFT(st_id,6)AS班级,COUNT(st_id)AS人数FROMstudentGROUPBYLEFT(st_id,6)操作8.9：依次按班级、系号对学生进行分类统计人数、入学平均分SELECTst_dpidAS系号,LEFT(st_id,6)AS班级,COUNT(st_nm)AS人数,AVG(st_score)AS均分FROMstudentGROUPBYLEFT(st_id,6),st_dpid操作8.10：查询选修了均分在75以上的课程号及均分SELECTcs_idAS课程编号,AVG(score)AS均分FROMslt_couseGROUPBYcs_idHAVINGAVG(score)>75操作8.11：查询选修了2门以上课程的学生学号SELECTst_idFROMslt_couseGROUPBYst_idHAVINGCOUNT(*)>2操作8.12：明细汇总年龄<20的学生，并汇总学生数量、平均年龄SELECTst_nm,DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)AS年龄FROMstudentWHEREDATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20COMPUTECOUNT(st_nm),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth))操作8.13：按班级明细汇总成绩<85分的学生，汇总学生数、均分SELECTst_nm,LEFT(st_id,6)AS班级,st_scoreFROMstudentWHEREst_score<85ORDERBY班级COMPUTECOUNT(st_nm),AVG(st_score)BY班级实验9数据查询(5)——连接查询操作9.1：用SQLServer形式连接查询学生学号、姓名、性别及其所选课程编号SELECTa.st_id,st_nm,st_sex,cs_idFROMstudenta,slt_cousebWHEREa.st_id=b.st_idORDERBYa.st_id操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号SELECTa.st_id,st_nm,st_sex,cs_idFROMstudentaINNERJOINslt_cousebONa.st_id=b.st_idORDERBYa.st_id操作9.3:用SQLServer形式连接查询学生学号、姓名及其所选课程名称及成绩SELECTa.st_id,st_nm,cs_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=b.st_idANDb.cs_id=c.cs_idORDERBYa.st_id操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩SELECTa.st_id,st_nm,cs_nm,scoreFROMslt_couseaINNERJOINstudentbONa.st_id=b.st_idINNERJOINcousecONa.cs_id=c.cs_idORDERBYb.st_id操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩SELECTa.st_id,st_nm,scoreFROMstudenta，slt_cousebWHEREa.st_id=b.st_idANDb.cs_id='1002'ORDERBYb.st_id操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩SELECTa.st_id,st_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=b.st_idANDb.cs_id=c.cs_idANDc.cs_nm=数据结构'ORDERBYa.st_id操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名SELECTa.st_id,st_nm,scoreFROMstudentaLEFTOUTERJOINslt_cousebONa.st_id=b.st_idWHEREb.cs_idISNULLORDERBYb.st_id操作9.8：用右外连接查询选修各个课程的学生学号SELECTb.cs_id,a.st_idFROMslt_couseaRightOUTERJOINcousebONa.cs_id=b.cs_idORDERBYb.cs_id实验10数据查询(6)——子查询操作10.1：用子查询对各班人数进行查询(新增列)SELECTDISTINCTLEFT(a.st_id,6)AS班级,人数=(SELECTCOUNT(st_id)FROMstudentbWHERELEFT(a.st_id,6)=LEFT(b.st_id,6))FROMstudentaORDERBYLEFT(a.st_id,6)ASC操作10.2：用子查询对各课程的选课人数进行查询(新增列)SELECTDISTINCTa.cs_id,人数=(SELECTCOUNT(st_id)FROMslt_cousebWHEREa.cs_id=b.cs_id)FROMslt_couseaORDERBYa.cs_idASC操作10.3：查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序通过子查询实现：——使用IN关键字SELECTst_id,st_nm,st_sexFROMstudentWHEREst_idIN(SELECTst_idFROMslt_couseWHEREcs_id='1002'ANDscore<60)ORDERBYst_nm通过子查询实现：——使用比较运算符SELECTst_id,st_nm,st_sexFROMstudentaWHERE(SELECTscoreFROMslt_cousebWHEREa.st_id=b.st_idANDcs_id='1002')<60ORDERBYst_nm操作10.4：查询“东方红”同学所在班的学生信息，并按姓名降序排序通过子查询实现：——IN运算符SELECTst_id,st_nm,st_sexFROMstudentWHERELEFT(st_dpid,6)IN(SELECTLeft(st_dpid,6)FROMstudentWHEREst_nm='东方红')ORDERBYst_nmDESC通过自连接查询实现：——JOINSELECTs1.st_id,s1.st_nm,s1.st_sexFROMstudents1JOINstudents2ONLEFT(s1.st_dpid,6)=LEFT(s2.st_dpid,6)WHEREs2.st_nm='东方红'ORDERBYs2.st_nm操作10.5:查询其它班比070511班某一学生的1002号课程成绩高的学生信息(ANY/ALL)SELECT*FROMslt_couseWHEREscore>ANY(SELECTscoreFROMslt_couseWHEREcs_id='1002'ANDLEFT(st_id,6)='070511')ANDLEFT(st_id,6)<>'070511'ANDcs_id='1002'操作10.6:查询其它班比070511班任一学生的1002号课程成绩高的学生信息(ANY/ALL)SELECT*FROMslt_couseWHEREscore>ALL(SELECTscoreFROMslt_couseWHEREcs_id='1002'ANDLEFT(st_id,6)='070511')ANDLEFT(st_id,6)<>'070511'ANDcs_id='1002'操作10.7:查询大于等于60分且且比1003课程平均成绩低的学生课程信息(Between-And)SELECT*FROMslt_couseaWHEREa.scoreBETWEEN60AND(SELECTAVG(b.score)FROMslt_cousebWHEREb.cs_id='1003')操作10.8：查询系主任为“赵虎”的系的所有学生信息通过子查询实现：——IN运算符SELECT*FROMstudentWHEREEXISTS(SELECT*FROMdeptWHEREst_dpid=dp_idANDdp_drt='赵虎')通过子查询实现：——=运算符SELECT*FROMstudentWHEREst_dpid=(SELECTdp_idFROM