数据库原理及应用教程综合实训

二、简单的数据查询本题中所用的数据库是第1题中所建立的Study数据库。(1)查询所有同学的基本信息，包括：学号s_no、班级号class_no、姓名s_name、性别S_sex、出生日期s_birthday。(2)查询所有同学，要求显示其学号s_no、姓名s_name。(3)查询所有男同学，要求显示其学号s_no、姓名s_name、出生日期s_birthday。(4)查询所有出生日期在“1980一01一01”前的女同学，要求显示其学号sno、姓名S_name、性别s_sex、出生日期s_birthday。(5)查询所有姓“李”的男同学，要求显示其学号s_no、姓名s_name、性别s_sex、出生日期s_birthday。(6)查询所有姓名中含有“一”字的同学，要求显示其学号s_no、姓名s_name。(7)查询所有职称不是“讲师”的教师，要求显示其教师号t_no、姓名t_name、职称t_title。(8)查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号s_no。(9)查询所有考试不及格的同学，要求显示出这些同学的学号s_no、成绩score，并按成绩降序排列。(10)查询出课程号为01001，02001，02003的所有课程，要求显示出课程号course_no、Course_name。(要求用in运算符)。三、复杂数据查询本题中所用的数据库是第l题中所建立的Study数据库。(1)查询所有同学的选课及成绩情况，要求显示学生的学号s_no、姓名s_name、课程号Course_no和课程的成绩score。(2)查询所有同学的选课及成绩情况，要求显示学生的姓名s_name、课程名称course_name、课程的成绩score，并将查询结果存放到一个新的数据表new_table中。(3)查询“计算机99-1”班的同学的选课及成绩情况，要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name、课程的成绩score。(4)查询所有同学的学分情况(假设课程成绩=60时可获得该门课程的学分)，要求显示学生的学号s_no、姓名s_name、总学分(将该列定名为：total_score)。(用JOIN)(5)查询所有同学的平均成绩及选课门数，要求显示学生的学号s_no、姓名s_name、平均成绩(将该列定名为：average_score)、选课的门数(将该列定名为：choice_num)。(6)查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学生的学号S_no、姓名s_name、课程号course_no、课程名称course_name。．(7)查询所有选修了课程但考试不及格(假设60分为不及格)的所有同学及相应的课程，要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name、课程成绩course_score。(8)查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示学生的姓名s_name、课程的成绩score。(使用ANY)(9)查询“计算机系”的所有同学及成绩情况，要求显示学生的学号s_no、姓名s_name、班级名称class_name、课程号course_no、课程名称course_name、课程的成绩score。(10)查询所有教师的任课情况，要求显示教师姓名t_name、担任课程的名称course_name。四、用Transact-SQL语句定义存储过程1、创建一个能向学生表Student中插入一条记录的存储过程Insert_student，该过程需要5个参数，分别用来传递学号、姓名、班级、性别、出生日期。2、写出执行存储过程Insert_student的SQL语句，向数据表Student中插入一个新同学，并提供相应的实参值（实参值自己给出）。3、创建一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为2，即当执行存储过程Insert_course时，未给参数“学分”提供实参值时，存储过程将按默认值2进行运算。4、执行存储过程Insert_course，向课程表Course中插入一门新课程。分两种情况写出相应的SQL命令（1）提供三个实参值执行存储过程Insert_course（三个参数值由用户提供）（2）只提供二个实参值执行存储过程Insert_course，即：不提供与参数“学分”对应的实参值。5、创建一个名为Query_student的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的姓名、年级、性别及出生日期。6、执行存储过程Query_student，查询学号为”001101”的学生的学号、班级号、性别及出生日期。写出完成此功能的SQL命令。五、用Transact-SQL语句自定义触发器1、创建一个向学生表Student中插入一新同学时能自动列出全部同学信息的触发器Display_trigger2、执行存储过程insert_student，向学生表中插入一新同学，看触发器Display_trigger是否被执行2．简单的数据查询(1)select*fromStudent;(2)selects_no,s_namefromStudent(3)selects_no,s_name,s_birthdayfromStudentwheres_sex='男'(4)Selects_no,s_name,s_sex,s_birthdayFromStudentWhere(s_sex='女')and(s_birthday='1980-01-01')(5)selects_no,s_name,s_sex,s_birthdayfromStudentWheres_sex='男'ands_namelike'李%'(6)selects_no,s_nameFromstudentWheres_namelike'%一%'(7)selectt_no,t_name,t_titlefromTeacherwheret_titlenotin('讲师')(8)selects_nofromChoicewherescoreisnull(9)selects_no,scorefromChoicewherescore60orderbyscoredesc(10)selectcourse_no,course_namefromCoursewherecourse_noin('01001','02001','02003')(11)selectt_no,t_name,t_birthdayfromTeacherwheret_birthdaybetween'1970-1-1'and'1970-12-12'(12)selectcourse_no,count(*)人数fromChoicegroupbycourse_no;(13)select*from(selectt_no,count(*)cfromTeachinggroupbyt_no)ccwherecc.c=2(14)selectavg(score)as平均成绩,max(score)as最高分,min(score)as最低分fromChoicewherecourse_no='01001'(15)selectt_name,t_birthdayfromTeacherwhere(t_birthday1960)and(t_title='讲师')orderbyt_birthdaydesc3.复杂的数据查询（1）selectstudent.s_no,s_name,course_no,scorefromStudentleftouterjoinChoiceonStudent.s_no=Choice.s_no（2）selects_name,Course.course_name,scoreintonew_tablefromStudent,Choice,CoursewhereCourse.course_no=Choice.course_noandStudent.s_no=Choice.s_no（3）selectStudent.s_no,s_name,Choice.course_no,course_name,scorefromclass,Student,Choice,Coursewhereclass_name='计算机99-1'andChoice.course_no=Course.course_noandChoice.s_no=Student.s_no（4）selectStudent.s_no,s_name,sum(course_score)astotal_scoreFromStudentInnerjoinChoiceonStudent.s_no=Choice.s_noInnerjoinCourseonChoice.course_no=Course.course_noandscore=60groupbyStudent.s_no,s_name（5）selectc.s_no,s.s_name,avg(c.score)average_score,count(*)choice_numfromChoicec,Studentswherec.s_no=s.s_nogroupbyc.s_no,s.s_name;(6)selects.s_no,s.s_name,co.course_no,co.course_namefromChoicec,Students,Coursecowherec.score=0andc.s_no=s.s_noandco.course_no=c.course_no;(7)selectst.s_no,st.s_name,co.course_no,co.course_name,co.course_scorefromChoicec,Courseco,Studentstwherec.score60andc.s_no=st.s_noandco.course_no=c.course_no;(8)selectst.s_name,c.scorefromChoicec,Courseco,Studentstwherest.s_no=c.s_noandco.course_no=c.course_noandco.course_name='程序设计语言';(9)selectst.s_no,st.s_name,c.class_name,co.course_no,co.course_name,ch.scorefromStudentst,Classc,Choicech,CoursecoWherec.class_no=st.class_noandc.class_dept='计算机系'andch.s_no=st.s_noandco.course_no=ch.course_no;(10)selectte.t_name,co.course_namefromTeachingt,Teacherte,Coursecowheret.t_no=te.t_noandco.course_no=t.couse_no;(11)selectt_no,t_name,t_birthdayfromTeacherwheret_birthdaybetween'1970-1-1'and'1970-12-12'(12)selectcourse_no,count(*)人数fromChoicegroupbycourse_no;(13)select*from(selectt_no,count(*)cfromTeachinggroupbyt_no)ccwherecc.c=2(14)selectavg(score)as平均成绩,max(score)as最高分,min(score)as最低分fromChoicewherecourse_no='01001'(15)selectt_na