数据库原理实验报告四(有答案)

1、数据库原理与应用课程实验报告实验四查询设计实验所在院（系）:数学与信息技术学院班级：学号：姓名：1. 实验目的(1) 了解查询的目的，掌握 SELECT语句的基本语法和查询条件的表示方法。(2) 掌握数据排序和数据联接查询的方法。(3) 掌握 SQL Server 查询分析器的使用方法。2. 实验要求(1) 针对 “TM数据库，在 SQLServer查询分析器中，用 T-SQL语句实现以下单表查询操作，并将将调试成功的 T-SQL命令，填入实验报告中。a) 查询所有课程的详细情况。b) 查询来自江苏或山东的学生学号和姓名， 并以中文名称显示输出的列名。c) 查询选修了课程的学生学号 (一人选了

2、多门课程的话， 学号只显示一次)。d) 查询选修课程号为 07253001 的学号和成绩，并要求对查询结果按成绩降 序排列，如果成绩相同则按学号升序排列。e) 查询所有学生的学号、姓名和年龄。f) 查询选修课程号为 07253001 的成绩在 85-95 之间的学生学号和成绩， 并 将成绩乘以 0.7 输出。g) 查询数学与信息技术学院( DEPT_ID 为 07)或物理与电子工程学院 (DEPT_ID为09)姓张的学生的信息。h) 查询所有核心课程(课程名中带 * 的)的情况。i) 查询缺少了成绩的学生的学号和课程号，查询结果按课程号升序排列。(2) 在SQL Server查询分析器中，用

3、T-SQL语句实现下列数据联接查询操作：a) 查询每个学生的情况以及他(她)所选修的课程。b) 查询学生的学号、姓名、选修的课程名及成绩。c) 查询选修C语言程序设计且成绩为85分以上的学生学号、姓名及成绩。d) 查询和学生柏文楠是同一个民族的学生(用自身联接实现)。e) 分别用等值联接和内联接查询有授课记录的老师的姓名。f) 用外联接查询所有老师的授课情况，输出老师的工号、姓名、职称、院 系、担任的课程号和授课的学期，结果按院系和职称升序排列。如果该 老师没有授课历史，在课程号和授课的学期中显示空值(3) 在 SQL Server Management Studio 中新建查询，完成以上查询

4、命令的同时，熟悉 SQL 编辑器工具栏中各快捷按钮的作用。(4) 按要求完成实验报告。3. 实验步骤、结果和总结实验步骤/结果将调试成功的T-SQL语句写在下方(标明题号)。一、针对 “TM'数据库，在SQL Server查询分析器中，用T-SQL语句实现以下单表查询操 作，并将将调试成功的 T-SQL命令，填入实验报告中。(a) 查询所有课程的详细情况：select * from Courselec alhostVS. . - UQoeryT . sql* 表 - dbo. S tudent select - trorn Course结果岳消息CNameModule匚.TypeCre

5、ditC_Hoirs1! 00000000拓展训婕累嵐拓展必修032200000007大学英语(二)邇识数莆必越课程4723D0D0U01大学英语遇识教育必修课程必修16232400001002大学体育学科基础課程必修4110500001003军事理论学科基础课程必修220S00001004马克思主义基本原理学科基础谍程必修342700001005毛泽乐思想和中国特色社合主义理论悴系拇论学科基础课程必修S2300001006想樟道鴛修养与法锂基础学斜基础课程342900001007中園近现代史鋼要学科基酣课程必修228WOODG1Q08思韻谊待修养与法逮基砒週识教育必修谍程必修34211D00

6、C2003中国丈化槪论学科基础课程必修 3441200002004中凰文化槨论学科基础课程必修2281300002005大学语文学科基罰谍程必修3441400002眦大学语立学科基础课程必修2291500002007学科基础课程91231600002C11大学物理匚学科墓础课程必修5721700002012大学物理匚学科基砒課程必修 3431300002015大学物理实验亡学科基础谋程必修1221900005994职业生櫃与凌用規创素质拓展必修122000005935文化索质讲座素质茹展必隆1W2100005996社团活动耒嚴祐展必修11表1-a(b) 查询来自江苏或山东的学生学号和姓名，并以

7、中文名称显示输出的列名。SELECT S_ID as 学号,S_Name as 姓名 FROM STUDENTWHERE Birth Place='江苏'or Birth Place='山东'J结果消息学号姓名1100E0101卞威210060103金輝310060104王威410060105吴春生51006010E尹爽E10060107卞莉710060108曹格e10060109戴燃910060110戴挺表1-b(c) 查询选修了课程的学生学号(一人选了多门课程的话，学号只显示一次)。select disti net S_ID from sc$D1100601

8、01 210060102310060103410060104510060105610060106710060107310060103310060109W100601101110060111121006011213100B011314100601141510060115IB100GM1G17100601171810060116191006011920100601202110060121表1-c如果成(d) 查询选修课程号为 07253001的学号和成绩，并要求对查询结果按成绩降序排列,绩相同则按学号升序排列。select S_ID,EXAM_Grade from scwhere C ID=

9、9;07253001'order by EXAM_Grade desc,S_ID asc£DEXAM® de11006011599,0021006013799,0031006013599.0041006010997 005100S011497 0061008013397 00710060102S6.008100601 OB96.00g10060113960010100601199SOOn100801089S 0012100B011E95.00131006012295.00141006013G95.00151008013495,001S1006012493.00171

10、008013592.00181006012291.00iqmARm 19qn nn表1-d(e) 查询所有学生的学号、姓名和年龄。as 年龄 FROMStudentSELECT S_ID,S_NAME, year(getdate()-year(Date_of_Birth)£DSNAME年龄110060101卞威23210060102萋华2231006010322410060104王威24510060105吴春主2261006010E尹駆22710060107卞莉23310060106曹格24910060109221010060110戴陡231110060111戴真噩231210060

11、112方宏221006011313231410060114郭政231510060115何超2216100116洪扬231710060117241810060116金略231910060119李丹丹22表1-e(f) 查询选修课程号为 07253001的成绩在85-95之间的学生学号和成绩，并将成绩乘以0.7 输出。Select S_ID,EXAM_Grade*0.7 as grade from SCwhere C_ID='07253001'a nd EXAM_Grade between 85 and 95n结果e匕消息SJDgrade110060112&3.000210

12、06011763.00031006012059.50041006012266.50051006012465.100e1006012763.00071006013462.30031006013666.50031006010162.300101008010259.500111006011059.500121006012263 700131008012561 600141000012863010151006013061.6001E1008C13466.50017100G013564.400IB1O0EO13E59 500表1-f(g) 查询数学与信息技术学院(DEPT_ID为07)或物理与电子工程学

13、院(DEPT_ID为09)姓张的学生的信息。select *from Stude nt where DEPT_ID i n ('07', '09') and S_Name like '张 %'二I结果消息£DS_NameGerid&r0irth_Plac8Date_of_BirthNationalitjJDEPT_.Cldss_ID110070115张明浩男江苏1991*11-1200:00:00073210070225张瞒江苏1992 03 27 00:00:00074310070225立山东1991-OMOiOQOOiOO0

14、74410070227女江苏1931-10-15 00:00:0007451C070935张丽娟立江苏199204 00:00:00075e10070935立江苏1933-04-07 00-00:00浅族0?5i10070937张晓玄江茅1992-128 Oft00:00075表1-g(h) 查询所有核心课程(课程名中带 *的)的情况。SELECT * FROM Course WHERE C Name like '%*%'回结果曲消息C_IDC_NameModuleC_TvpeCreditC_Hous1j 00904001 ；计算机辱论浹专业理论课348200304003匚语言

15、程序设计*专业理论课472300804005离散数学貿专业理论课必修32400804006数据结构与算法尸专业理论课必修4.512500804007软件工程1*专业理论课必修348600804008数据库原理与应用*专业理论课必修472700804009操ft系统*通识教育必修课槎必修35E80080401Q计算机组成原理”通识教育必修课程応修356901304006娥据箱构与篦法专业理论课必修4.5721007254001计算机导论瀬专业理论课亦修253607254004计篡机组成原理”专业理论课赵修3.5561207254009计篦机网络*专业理论课亦修35581307254010徴机原理

16、与接口技术*专业理论课亦修35591407255012Androidlg端移动开发技术”专业绿合选修课选修4721507204002匚语言程序设计”专业理论课47216074003Jav鏑言程序设计专业理论课4721707284004数据结构与算法11专业理论课45721807284005软件工程"专业理论课必修34S| 1907294006数据库原理与应用专业理论课必修472表1-h(i) 查询缺少了成绩的学生的学号和课程号，查询结果按课程号升序排列。select S_ID,C_ID from SC where EXAM_Grade IS NULLorder by C_ID、在SQ

17、L Server查询分析器中，用T-SQL语句实现下列数据联接查询操作:1结果_j消息S_IDQJD1b00601020729400221006011807294002310080126072940024100702040729400351007022107234005E10070104而29500771007011207299007e1007020609003011表1-i(a) 查询每个学生的情况以及他(她)所选修的课程。Select Stude nt.S_ID, S_Name,Ge nder, Birth_Place,DEPT_ID, Class_ID,Course.C_ name fr

18、om Stude nt join SC on Stude nt.S_ID=SC.S_ID join Course onCourse.C_ID=SC.C_ID13结果匕消息S_IDS_Name Gender Birth_PlaceDEPT_. Class.lDC_fhame1fl0060101卞威男江苏06 2犬学数学日C 一 )2 10060101卞威男江苏06 2C语咅程序设计310060102姜华男北京06 2大学数学日C 1)410060102姜华男北京06 2匚语言程序设计510060103金辉男江苏06 2大学数学E C -)610060103金辉男江苏06 2匚语言程序设计7100

19、60104王威男江苏06 2大学数学E C 一 )810060104王威男江苏06 2匚语言程序设计910060105吴春生男江苏06 2犬学数学E1 一)1010060105吴春生男江苏06 2匚语言程序设计11 10060106尹取男江苏06 2丈学数学E ( 一 )12 10060106尹爽男江苏06 2匚语言程序设计1310060107卞莉男江苏06 2丈学数学B ( 一)1410060107下莉男江苏06 2匚语言程序设计1510060108曹格男江苏06 2大学数学E < 一)16 10060108曹格男江苏06 2C语言程序设计171D06010S戟燃男江苏06 2大学数学

20、E < -)1010060109戴燃男江苏06 2匚语言程序设计1910060110戴挺男江苏06 2大学数学B C -)表2-a(b) 查询学生的学号、姓名、选修的课程名及成绩。select Stude nt.S_ID, S_Name,C_Name, EXAM_Gradefrom Stude nt inner join SC on Stude nt.S_ID=SC.S_ID inner join Course onSC.C_ID=Course.C_IDSDS_NameC_Name1f 10060101|卞威 HP大学数学B一)72.00210060101卞威c语言程序设计64.0031

21、0060102姜华犬学数学日c 一 )96 00410060102姜华匸语言程序设计NULL510060103金辉犬学数学B C 一 )58.00610060103金辉C语言程序设计91.00110060104壬威犬学数学B (-)71.00S10060104王威匚语言程序设计B7.00910060105吴舂生大学数学B C -)74.001010060105吴春主C语書程序设计65.001110060106尹娈大学数学日£ 一 )74.001210060106尹爽C语言程序设计74.001310060107下莉大学数学R)7b.001410060107卞莉C语言程序设计&1

22、.0015100S010S曹格大学数学B( 一)96.001610060109曹格匚语言程序设计76.001710060109戴燃大学数学B ( 一)97.001810060109戴燃匚语言程序设计77.001510060110犬学数学BC-).62.00表2-b(c) 查询选修C语言程序设计且成绩为 85分以上的学生学号、姓名及成绩。Select Stude nt.S_ID,S_NAME,EXAM_Grade from Stude nt,SC,Course语言程序设where Stude nt.S_ID=SC.S_ID and Course.C_ID=SC.C_ID and C_Name=&

23、#39;C计'and EXAM_Grade>=85S_|W|EEXtM-Gradei-100601C391 .CO210000104王威87.00310D6011I1蒙真Ht93.00i10060112方宏980051006011397.00B1008C114郭政I.CO110DEC11E洪场87.0Q91006012086009100G0122刘艺雯3S.C010100SD12589.001110060127唐凤94.0Q121000)129王潘拎88001310060130王希垮32.C014looeoui张寿隹95.0015100S013385.0DIE1006013496

24、001710060135叶稱sun1810060136张去娟B3.00191006013794.00表2-c(d) 查询和学生柏文楠是同一个民族的学生(用自身联接实现)。SELECT S_Name from Stude ntwhere Nationality=(select Nationality from Student where S_Name='柏文楠')(e) 分别用等值联接和内联接查询有授课记录的老师的姓名。等值联接select DISTINCT T_Name fromTeacher inner join TC on Teacher.T_ID=TC.T_ID内联接se

25、lect DISTINCT T_Name from Teacher,TC where Teacher.T_ID=TC.T_IDH结果丄消息1戴文祥2董娅3管苹4郭拓5黄长江e芮鹏7石佳es张晓敏10周乐表2-e(f) 用外联接查询所有老师的授课情况，输出老师的工号、姓名、职称、院系、担任的课程号和授课的学期，结果按院系和职称升序排列。如果该老师没有授课历史，在课程号和授课的学期中显示空值。select Teacher.T_ID,Teacher.T_Name,Teacher.Academic_Title,Departme nt.DEPT_Name,TC.C_ID,TC.Semesterfrom

26、Teacher Right join TC on Teacher.T_ID=TC.T_ID joinDepartmentONDepartme nt.DEPT_ID=Teacher.DEPT_ID order by Departme nt.DEPT_Name , Teacher.Academic_Title二1結果崗消息LIDT_NameAcademic_TiteDEPT_ManeCJDSemester1T0000003副哉授数学与荷息技点学陽072940022011-2012-22T0000003芮鹏副教授数学与信息拽术学院072940022011-2012-23T0001006戴丈祥数学与值

27、息技耒学院072950082012-2013-14T000100E戴文祥副教援数学与営息技术学院0720082012-2C13-15T0002013数学与價亂技求学隔072530012011-2012-2ST0002013副教援数学与信息技术学院072530012011-2012-27T00990D2郭箔数学与值息技术学院072940022011-2012-13T0099002郭拓副教授数学与信息技术学院072940032012-2013-1910002011!讲帰一数学与信息技术学院072950022012013110T0002011董娅讲师数学与信息技术学院072990022012-201

28、11r0000005石隹讲师数学与信恩拽术学院072940022011-2012-212TOODOOCJ5石隹讲师数学与信息技术学院072940022011-2012-213T0000011讲师数学与信息拽术学曉0729600620122013 J14TOOOQQ11张晓敏讲师数学与信息技术学院072950062012-2013-115T0099006敎授数学与信息技术学院072940032012013-116T0099005黄故江敎授数学与信息技术学降072950072011-20121710099001昔苹数授数学与信息技术学院072940052D12-2013416TOO 珈（JI晉苹数

29、学与信息技术学院072940052012-2013119T0000010副教授物理与电子工程学院09003C112011-2012-1表2-f4. 实验思考: 联接查询中，输出列名时何时可以忽略列名前的表名，何时不能?完整写法：表名.列名若选择的字段名在各个表中是唯一的，则可以省略字段名前的表名。 联接查询中，INNER JOIN、LEFT OUTEFJOIN、RIGHT OUTEJOIN、FULL OUTERJOIN 的结果各有什么不同？INNER JOIN :Inner join产生的结果集中，是 A和B的交集INNER JOINSELECT * FROM TableA INNER JOI

30、N TableB ON TableA. name = TableB. nameLEFT OUTER JOIN:null(1) Left outer join产生表A的完全集，而 B表中匹配的则有值，没有匹配的则以值取代。A1 »b1i= ISLeft outer jnin(i)SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA. name = TableB. name(2) 产生在A表中有而在B表中没有的集合。Left outer j oin(iiSELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA. name = TableB