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

二、简单的数据查询

本题中所用的数据库是第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_trigger

2、执行存储过程insert_student，向学生表中插入一新同学，看触发器Display_trigger是否被执行

2．简单的数据查询 (1)select*

fromStudent;

(2)selects_no,s_name

fromStudent

(3)selects_no,s_name,s_birthday

fromStudent wheres_sex='男'

(4)Selects_no,s_name,s_sex,s_birthday

FromStudent

Where (s_sex='女')and(s_birthday<='1980-01-01')

(5) selects_no,s_name,s_sex,s_birthday

fromStudent

Wheres_sex='男'ands_namelike'李%'

(6) selects_no,s_name

Fromstudent

Wheres_namelike'%一%'

(7) selectt_no,t_name,t_title

fromTeacher

wheret_titlenotin('讲师')

(8) selects_no

fromChoice wherescoreisnull

(9) selects_no,score

fromChoice wherescore<60 orderbyscoredesc

(10) selectcourse_no,course_name

fromCourse

wherecourse_noin('01001','02001','02003')

(11) selectt_no,t_name,t_birthday

fromTeacher

wheret_birthdaybetween'1970-1-1'and'1970-12-12'

(12) selectcourse_no,count(*)人数

fromChoice groupbycourse_no;

(13) select*

from (selectt_no,count(*)c fromTeaching groupbyt_no)cc wherecc.c>=2

(14) selectavg(score)as平均成绩,max(score)as最高分,min(score)as最低分

fromChoice

wherecourse_no='01001'

(15) selectt_name,t_birthday

fromTeacher

where (t_birthday>1960)and(t_title='讲师') orderbyt_birthdaydesc

3.复杂的数据查询

（1）selectstudent.s_no,s_name,course_no,score

fromStudent

leftouterjoinChoice onStudent.s_no=Choice.s_no

（2）selects_name,Course.course_name,score

intonew_table

fromStudent,Choice,Course

whereCourse.course_no=Choice.course_no andStudent.s_no=Choice.s_no

（3）selectStudent.s_no,s_name,Choice.course_no,course_name,score fromclass,Student,Choice,Course

whereclass_name='计算机99-1'andChoice.course_no=Course.course_noand Choice.s_no=Student.s_no

（4）selectStudent.s_no,s_name,sum(course_score)astotal_score

FromStudent

InnerjoinChoiceonStudent.s_no=Choice.s_no

InnerjoinCourseonChoice.course_no=Course.course_noandscore>=60 groupbyStudent.s_no,s_name

（5）selectc.s_no,s.s_name,avg(c.score)average_score,count(*)choice_num

fromChoicec,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,Courseco

wherec.score=0 andc.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_score

fromChoicec,Courseco,Studentst

wherec.score<60 andc.s_no=st.s_noandco.course_no=c.course_no;

(8) selectst.s_name,c.scorefromChoicec,Courseco,Studentstwhere

st.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,Courseco

Wherec.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,Courseco

wheret.t_no=te.t_noandco.course_no=t.couse_no;

(11) selectt_no,t_name,t_birthday

fromTeacher

wheret_birthdaybetween'1970-1-1'and'1970-12-12'

(12) selectcourse_no,count(*)人数

fromChoice groupbycourse_no;

(13) select*

from (selectt_no,count(*)c fromTeaching groupbyt_no)cc wherecc.c>=2

(14) selectavg(score)as平均成绩,max(score)as最高分,min(score)as最低分

fromChoice

wherecourse_no='01001'

(15) selectt_name,t_birthday

fromTeacher

where (t_birthday>1960)and(t_title='讲师') orderbyt_birthdaydesc

3.复杂的数据查询

（1）selectstudent.s_no,s_name,course_no,score

fromStudent

leftouterjoinChoice onStudent.s_no=Choice.s_no

（2）selects_name,Course.course_name,score

intonew_table

fromStudent,Choice,Course

whereCourse.course_no=Choice.course_no

andStudent.s_no=Choice.s_no

（3）selectStudent.s_no,s_name,Choice.course_no,course_name,score fromclass,Student,Choice,Course

whereclass_name='计算机99-1'andChoice.course_no=Course.course_noand Choice.s_no=Student.s_no

（4）selectStudent.s_no,s_name,sum(course_score)astotal_score

FromStudent

InnerjoinChoiceonStudent.s_no=Choice.s_no

InnerjoinCourseonChoice.course_no=Course.course_noandscore>=60 groupbyStudent.s_no,s_name

（5）selectc.s_no,s.s_name,avg(c.score)average_score,count(*)choice_num

fromChoicec,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,Courseco

wherec.score=0 andc.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_score

fromChoicec,Courseco,Studentst

wherec.score<60 andc.s_no=st.s_noandco.course_no=c.course_no;

(8) selectst.s_name,c.scorefromChoicec,Courseco,Studentstwhere

st.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.score fromStudentst,Classc,Choicech,Courseco

Wherec.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,Courseco

wheret.t_no=te.t_noandco.course_no=t.couse_no;

(11)selectt.t_no,te.t_name,count(*)course_number

fromTeachingt,Teacherte wheret.t_no=te.t_no groupbyt.t_no,te.t_name;

(12)select*fromStudentst

wherest.class_noin(selectclass_nofromStudentwheres_name='李建国')

(13)select*fromChoicech,Studentst

wherenotExists(selectcourse_nofromCoursewherecourse_name='计算机基础'andch.course_no!=course_no) andst.s_no=ch.s_no;

(14)selectTeacher.t_namefromTeacher,Teaching,Coursewhere

Teacher.t_no=Teaching.t_noandCourse.course_no=Teaching.Couse_noand (Course.course_name='数据库原理与应用')groupbyTeacher.t_name union

(selectTeacher.t_namefromTeacher,Teaching,Coursewhere

Teacher.t_no=Teaching.t_noandCourse.course_no=Teaching.Couse_no andCourse.course_name='数据结构');

(15)selectt.t_namefromTeachert,(selectte.t_no,count(*)c

fromTeachingtegroupbyte.t_no)cc wherecc.c=6 andcc.t_no=t.t_no;

4.用Transact-SQL语句定义存储过程 (1)createprocedureinsert_student(

)as

@s_nochar(6), @s_namechar(6), @class_nochar(6), @s_sexchar(2), @s_birthdaydatetime

insertintoStudent(s_no,s_name,class_no,s_sex,s_birthday)VALUES(@s_no,@s_name,@class_no,@s_sex,@s_birthday);

(2)execinsert_student@s_no='003102',@s_name='王一一',@class_no='xx001',@s_sex='女

',@s_birthday='1982-01-01';

(3)createprocedureinsert_couese(

)as

insertintoCourse(course_no,course_name,course_score)values(@course_no,@course_name,@course_score);

@course_nochar(6), @course_namechar(6), @course_scorenumeric(6)='2'

(4)

?execinsert_couese@course_no='03102',@course_name='数据库基础',@course_score='3';

?execinsert_couese@course_no='01102',@course_name='计算机程序';

(5)createprocedurequery_student(

)as

selects_name,class_no,s_sex,s_birthdayfromStudentwheres_no=@s_no;

@s_nochar(6)

(6)execquery_student@s_no='001101';

5.Transact-SQL语句自定义触发器 （1）createtriggerdisplag_trigger

onStudentAfterinsert AS

select*fromStudent;

（2）execinsert_student@s_no='001301',@s_name='杨子',@class_no='xx0002',@s_sex='男',@s_birthday='1987-06-05';

本文来源：https://www.bwwdw.com/article/u6yv.html