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

二、简单的数据查询 本题中所用的数据库是第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”前的女同学，要求显示其学号s no、姓名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 * from Student;(2) select s_no,s_namefrom Student(3) select s_no,s_name,s_birthdayfrom Student where s_sex=男(4)Select s_no,s_name,s_sex,s_birthdayFrom StudentWhere (s_sex=女)and(s_birthday=1980-01-01)(5) select s_no,s_name,s_sex,s_birthdayfrom StudentWhere s_sex=男and s_name like 李%(6) select s_no,s_nameFrom studentWhere s_name like %一%(7) select t_no ,t_name ,t_title from Teacher where t_title not in (讲师)(8) select s_no from Choice where score is null(9) select s_no ,scorefrom Choice where score =2(14) select avg(score)as 平均成绩,max(score)as 最高分,min(score)as 最低分from Choice where course_no =01001(15) select t_name ,t_birthdayfrom Teacher where (t_birthday 1960)and (t_title=讲师)order by t_birthday desc3.复杂的数据查询（1）select student.s_no ,s_name ,course_no ,scorefrom Student left outer join Choice on Student .s_no =Choice .s_no （2）select s_name ,Course .course_name ,score into new_tablefrom Student ,Choice ,Course where Course .course_no =Choice .course_no and Student .s_no =Choice .s_no（3）select Student .s_no ,s_name ,Choice .course_no ,course_name ,score from class ,Student ,Choice ,Course where class_name=计算机99-1and Choice .course_no=Course.course_no andChoice.s_no=Student.s_no（4）select Student.s_no ,s_name,sum(course_score )as total_score From Student Inner join Choice on Student.s_no=Choice.s_noInner join Course on Choice.course_no=Course.course_no and score=60 group by Student.s_no ,s_name（5）select c.s_no,s.s_name,avg(c.score) average_score,count(*) choice_num from Choice c,Student s where c.s_no=s.s_no group by c.s_no,s.s_name;(6) select s.s_no,s.s_name,co.course_no,co.course_name from Choice c,Student s,Course co where c.score=0 and c.s_no=s.s_no and co.course_no=c.course_no;(7) select st.s_no,st.s_name,co.course_no,co.course_name,co.course_score from Choice c,Course co,Student st where c.score=2(14) select avg(score)as 平均成绩,max(score)as 最高分,min(score)as 最低分from Choice where course_no =01001(15) select t_name ,t_birthdayfrom Teacher where (t_birthday 1960)and (t_title=讲师)order by t_birthday desc3.复杂的数据查询（1）select student.s_no ,s_name ,course_no ,scorefrom Student left outer join Choice on Student .s_no =Choice .s_no （2）select s_name ,Course .course_name ,score into new_tablefrom Student ,Choice ,Course where Course .course_no =Choice .course_no and Student .s_no =Choice .s_no（3）select Student .s_no ,s_name ,Choice .course_no ,course_name ,score from class ,Student ,Choice ,Course where class_name=计算机99-1and Choice .course_no=Course.course_no andChoice.s_no=Student.s_no（4）select Student.s_no ,s_name,sum(course_score )as total_score From Student Inner join Choice on Student.s_no=Choice.s_noInner join Course on Choice.course_no=Course.course_no and score=60 group by Student.s_no ,s_name（5）select c.s_no,s.s_name,avg(c.score) average_score,count(*) choice_num from Choice c,Student s where c.s_no=s.s_no group by c.s_no,s.s_name;(6) select s.s_no,s.s_name,co.course_no,co.course_name from Choice c,Student s,Course co where c.score=0 and c.s_no=s.s_no and co.course_no=c.course_no;(7) select st.s_no,st.s_name,co.course_no,co.course_name,co.course_score from Choice c,Course co,Student st where c.score60 and c.s_no=st.s_no and co.course_no=c.course_no;(8) select st.s_name,c.score from Choice c ,Course co,Student st where st.s_no=c.s_no and co.course_no=c.course_no and co.course_name=程序设计语言;(9) select st.s_no,st.s_name,c.class_name,co.course_no,co.course_name,ch.score from Student st,Class c,Choice ch,Course co Where c.class_no=st.class_no and c.class_dept=计算机系 and ch.s_no=st.s_no and co.course_no=ch.course_no;(10) select te.t_name,co.course_name from Teaching t,Teacher te,Course co where t.t_no=te.t_no and co.course_no=t.couse_no;(11)select t.t_no,te.t_name,count(*) course_number from Teaching t,Teacher te where t.t_no=te.t_no group by t.t_no,te.t_name;(12)select * from Student st where st.class_no in (select class_no from Student where s_name=李建国)(13)select * from Choice ch,Student st where not Exists(select course_no from Course where course_name=计算机基础 and ch.course_no!=course_no) and st.s_no=ch.s_no;(14)select Teacher.t_name from Teacher,Teaching,Course where Teacher.t_no=Teaching.t_no and Course.course_no=Teaching.Couse_no and (Course.course_name =数据库原理与应用) group by Teacher.t_name union( select Teacher.t_name from Teacher,Teaching,Course whereTeacher.t_no=Teaching.t_no and Course.course_no=Teaching.Couse_no and Course.course_name =数据结构); (15)select t.t_name from Teacher t,(select te.t_no,count(*) c from Teaching te group by te.t_no) cc where cc.c=6 and cc.t_no=t.t_no;4.用Transact-SQL语句定义存储过程(1)create procedure insert_student(s_no char(6),s_name char(6),class_no char(6),s_sex char(2),s_birthday datetime)asinsert into Student(s_no,s_name,class_no,s_sex,s_birthday) VALUES(s_no,s_name,class_