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

(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';

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库数据库原理及应用教程综合实训(5)在线全文阅读。