数据库实验报告Transact-SQL语句的高级应用实验报告.pdf
. .-优选西北师 X 大学计算机科学与工程学院学生实验报告学号专业计算机科学与技术XX 课程名称数据库应用与开发班级实验名称Transact-SQL语句的高级应用课程类型专业限选课实验目的：利用 SELECT 语句中更复杂的特性， 可以使用多个表进展查询并获取结果。在实际应用过程中， SELECT 语句查询通常是由多表连接、子查询等有机地组合而形成的，同时还可以处理大型数据块，使用游标处理结果集。实验内容：1、查询每一位教授的教师号、XX 和讲授的课程名称SQL 代码：use teaching go select teacherno as 教授号 , tname as XX , major as 课程名称 from teacher where prof=教授 go 实验结果：. .-优选2、利用现有的表生成新表，新表中包括学号、学生XX、课程名称和总评成绩其中：总评成绩 =final*0.9+usually*0.1 。SQL 代码：use teaching go . .-优选select student .studentno as 学号, sname as 学生XX, ame as 课程名称 ,0.9*final+0.1*usually as 总评成绩 into new_stuscore from student , score , course where student .studentno = score .studentno and course .courseno = score .courseno go select * from new_stuscore 实验结果：. .-优选3、分别统计每个学生期末成绩高于75分的课程门数SQL 代码：use teaching go select student .studentno ,student.sname ,count(*)as 课程门数 from score ,student where final 75 and student .studentno = score.studentno groupby student.studentno ,student.sname 实验结果：. .-优选4、输出 student表中年龄大于女生平均年龄的男生的所有信息SQL 代码：use teaching go select * from student wheresex =男anddatediff(year ,birthday,getdate ()( . .-优选select avg(datediff(year ,birthday,getdate () from student where sex =女 ) 实验结果：5、计算每个学生获得的学分。SQL 代码：use teaching go select student .studentno, student .sname ,sum(credit) . .-优选from student innerjoin score on student.studentno =score .studentno innerjoin course on course .courseno = score .courseno where score .final 60 groupby student.studentno , student.sname go 实验结果：6、获取入学时间在2008 2009年的所有学生中入学年龄小于19岁的学号、 XX 及所修课程的课程名称。. .-优选SQL 代码：use teaching go select student .studentno, student .sname , course .ame from student innerjoin score on student.studentno = score .studentno innerjoin course on course .courseno = score .courseno where (substring (student .studentno,1,2)=08 and(datediff(year ,birthday,2008-01-01 ) 19) or(substring (student .studentno ,1,2)=09 and(datediff(year ,birthday,2009-01-01 ) 19) go 实验结果：. .-优选7、查询 09级学生的学号、 XX 、课程名及学分。SQL 代码：use teaching go select student .studentno, student .sname , course .ame ,course .credit from student innerjoin score on student.studentno = score .studentno innerjoin course on course .courseno = score .courseno where (substring (student .studentno,1,2)=09) . .-优选go 实验结果：8、查询选修课程少于3 门或期末成绩含有60分以下课程的学生学号、XX、和. SQL 代码：. .-优选use teaching go select studentno ,count(*)as countNUM into count1 from score groupby studentno go select student .syudentno ,sname ,phone,Email from student innerjoin score on student.studentno =score .studentno innerjoin count1 on student.studentno =count1.studentno where score .final60 and countNUM3 go 实验结果：. .-优选实验总结：通过本次实验掌握使用多个表进展查询并获取结果，同时根本掌握处理大型数据块，使用游标处理结果集。实验评语：实验成绩教师签字
