搜索
1北京联合大学实验报告专业班级：姓名：学号：一、实验名称实验2SQL与T-SQL二、实验目的1.掌握标准SQL语言的查询、定义和操纵功能2.掌握T-SQL语言，会使用T-SQL语言建立存储过程3.进一步掌握查询分析器的使用三、实验时间2013年3月21日-2013年4月8日四、实验任务（其中带*任务写在实验报告中，其余任务作为平时练习）1．用SQL查询分析器编辑本章中所有查询实例，执行并查看结果。2．使用“学生数据库”中的数据完成下列查询：(*)1)求“选“数据库原理与技术”课程的学生名单”2)求“没有任课的教师名单”。3)求“一人任多门（1）课程的教师名单”。4)求“选修人数少于2人的课程名单”。5)求“高于所有课程总平均分的学生姓名、课程名及成绩。使用SQL查询分析器编辑查询、执行并查看结果。3．使用SQL命令建立：（*）1)数据库“工程管理”。2)表：工程（工程编号，预算，地址）、供应商（供应商编号，名称，地址）、零件（零件编号，名称，单价）和供应（供应商编号，工程编号，零件编号，数量）。3)视图：“北京供应商的供应情况”。4．使用SQL查询分析器的“打开表”功能完成三个表的数据录入。2工程表：供应商表：工程编号预算（万元）地址供应商编号名称地址J1123.4北京S1群星北京J278.8天津S2胜利杭州J323.98上海S3飞马上海S4解放上海零件表：零件编号名称规格单价（元）P1螺母大0.5P2螺栓大0.4P3螺丝刀大4.3P4螺母小0.4供应表：供应商编号零件编号工程编号数量S1P1J1500S1P1J2800S1P1J3900S1P2J1400S1P2J21130S2P1J1380S2P1J2300S2P2J1400S2P3J4210S2P4J1980S3P1J1200S3P2J11200S3P4J16005．使用“工程管理”中的数据完成下列查询：(*)1)求“供应零件P1的供应商的名称”。2)求“将零件P1供应给工程J1的供应商的名称”。3)求“向工程J1提供零件的供应商的地址”。4)求“在同一城市的工程编号和供应商的名称”。5)求“只向一个工程提供零件的供应商的名称”。6)求“供应零件数量最多的供应商的名称”。使用SQL查询分析器编辑查询、执行并查看结果。6．编写存储过程，计算每个学生的年龄，将其放在一个新表“学生年龄表”中。(*)五、实验内容、结果及分析1)求“选“数据库原理与技术”课程的学生名单”查询语句：SELECT姓名FROM学生成绩join课程基本信息on(学生成绩.课程号=课程基本信息.课程号)3join学生基本信息on(学生基本信息.学号=学生成绩.学号)WHERE课程名='数据库原理与技术'查询结果：2)求“没有任课的教师名单”。查询语句：SELECT姓名FROM教师基本信息WHERE教师编号NOTIN(SELECTDISTINCT任课教师号FROM课程基本信息WHERE任课教师号ISnotNULL)运行结果：3)求“一人任多门（1）课程的教师名单”。SELECT姓名FROM教师基本信息where教师编号IN(SELECT任课教师号FROM课程基本信息GROUPBY任课教师号HAVINGCOUNT(*)1)4)求“选修人数少于2人的课程名单”。查询语句：SELECT课程名FROM课程基本信息where课程号IN(SELECT课程号FROM学生成绩GROUPBY课程号HAVINGCOUNT(*)2)运行结果：45)求“高于所有课程总平均分的学生姓名、课程名及成绩。查询语句：select姓名,课程名,AVG(成绩)from学生成绩join课程基本信息on(学生成绩.课程号=课程基本信息.课程号)join学生基本信息on(学生基本信息.学号=学生成绩.学号)GROUPBY姓名,课程名havingAVG(成绩)=all(selectAVG(成绩)AS课程平均分from学生成绩GROUPBY课程号)查询结果:使用SQL查询分析器编辑查询、执行并查看结果。2．使用SQL命令建立：（*）1)数据库“工程管理”。2)表：工程（工程编号，预算，地址）、供应商（供应商编号，名称，地址）、零件（零件编号，名称，单价）和供应（供应商编号，工程编号，零件编号，数量）。1.工程（工程编号，预算，地址）、2.供应商（供应商编号，名称，地址）53.零件（零件编号，名称，单价）供应（供应商编号，工程编号，零件编号，数量）3)视图：“北京供应商的供应情况”。4.使用SQL查询分析器的“打开表”功能完成三个表的数据录入。工程表6供应商零件表供应表5.使用“工程管理”中的数据完成下列查询：(*)4)求“供应零件P1的供应商的名称”。查询语句：select名称from供应商where供应商编号in(select供应商编号from供应where(供应.零件编号='P1'))查询结果：75)求“将零件P1供应给工程J1的供应商的名称”。查询语句：select名称from供应商where供应商编号in(select供应商编号from供应where(供应.零件编号='P1'and供应.工程编号='J1'))查询结果:6)求“向工程J1提供零件的供应商的地址”。查询语句：select地址from供应商where供应商编号in(select供应商编号from供应where(供应.工程编号='J1'))查询结果：7)求“在同一城市的工程编号和供应商的名称”。查询语句：select工程编号,名称from供应商join工程on(工程.地址=供应商.地址)查询结果：88)求“只向一个工程提供零件的供应商的名称”。9)求“供应零件数量最多的供应商的名称”。查询语句：select名称,sum(数量)as总数from供应join供应商on(供应.供应商编号=供应商.供应商编号)groupby名称havingsum(数量)=all(selectsum(数量)assumfrom供应join供应商on(供应.供应商编号=供应商.供应商编号)GROUPBY名称)查询结果：6.编写存储过程，计算每个学生的年龄，将其放在一个新表“学生年龄表”中。(*)CREATETABLE学生年龄表(姓名VARCHAR(20)NOTNULLPRIMARYKEY,年龄VARCHAR(20)NOTNULL)INSERT学生年龄表(姓名,年龄)SELECT姓名,年龄=YEAR(GETDATE())-YEAR(出生日期)FROM学生基本信息SELECT*FROM学生年龄表运行结果：9建立好的表