數(shù)據(jù)庫原理及應用教程綜合實訓參考模板

1、二、簡單的數(shù)據(jù)查詢 本題中所用的數(shù)據(jù)庫是第1題中所建立的Study數(shù)據(jù)庫。(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

2、、性別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運算符)。 三、復

3、雜數(shù)據(jù)查詢1 / 31 本題中所用的數(shù)據(jù)庫是第l題中所建立的Study數(shù)據(jù)庫。 (1)查詢所有同學的選課及成績情況，要求顯示學生的學號s _no、姓名s_name、課程號Course_no和課程的成績score。 (2)查詢所有同學的選課及成績情況，要求顯示學生的姓名s _name、課程名稱course_name、課程的成績score，并將查詢結果存放到一個新的數(shù)據(jù)表new_table中。 (3)查詢“計算機99-1”班的同學的選課及成績情況，要求顯示學生的學號s_ no、姓名s _name、課程號course _no、課程名稱course_name、課程的成績score。 (4)查詢所有同學

4、的學分情況(假設課程成績>=60時可獲得該門課程的學分)，要求顯示學生的學號s _no、姓名s_ name、總學分(將該列定名為：total_score)。(用JOIN) (5)查詢所有同學的平均成績及選課門數(shù)，要求顯示學生的學號s_ no、姓名s_ name、平均成績(將該列定名為：average_score)、選課的門數(shù)(將該列定名為：choice_num)。 (6)查詢所有選修了課程但未參加考試的所有同學及相應的課程，要求顯示學生的學號S_ no、姓名s_ name、課程號course_no、課程名稱course_name。 (7)查詢所有選修了課程但考試不及格(假設<60分

5、為不及格)的所有同學及相應的課程，要求顯示學生的學號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、擔任課程的名稱c

6、ourse _name。 四、用Transact-SQL語句定義存儲過程1、 創(chuàng)建一個能向學生表 Student 中插入一條記錄的存儲過程Insert_student，該過程需要5個參數(shù)，分別用來傳遞學號、姓名、班級、性別、出生日期。2、 寫出執(zhí)行存儲過程 Insert_student 的 SQL 語句， 向數(shù)據(jù)表 Student 中插入一個新同學，并提供相應的實參值（實參值自己給出）。 3、創(chuàng)建一個向課程表中插入一門新課程的存儲過程Insert_course，該存儲過程需要三個參數(shù)，分別用來傳遞課程號、課程名、學分，但允許參數(shù)“學分”的默認值為2，即當執(zhí)行存儲過程Insert_course時

7、，未給參數(shù)“學分”提供實參值時，存儲過程將按默認值2進行運算。4、執(zhí)行存儲過程Insert_course，向課程表Course中插入一門新課程。分兩種情況寫出相應的SQL命令（1）提供三個實參值執(zhí)行存儲過程Insert_course（三個參數(shù)值由用戶提供）（2）只提供二個實參值執(zhí)行存儲過程Insert_course，即：不提供與參數(shù)“學分”對應的實參值。5、創(chuàng)建一個名為Query_student的存儲過程，該存儲過程的功能是根據(jù)學號查詢學生表中某一學生的姓名、年級、性別及出生日期。6、執(zhí)行存儲過程Query_student，查詢學號為”001101”的學生的學號、班級號、性別及出生日期。寫出完

8、成此功能的SQL命令。五、用Transact-SQL語句自定義觸發(fā)器1、創(chuàng)建一個向學生表Student中插入一新同學時能自動列出全部同學信息的觸發(fā)器Display_trigger2、執(zhí)行存儲過程insert_student，向學生表中插入一新同學，看觸發(fā)器Display_trigger是否被執(zhí)行2簡單的數(shù)據(jù)查詢(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,

9、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

10、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 <60order by score desc(10) select course_no ,course_namefrom Course where course_no in ('01001','02001','02003')(11) select t_no ,t_

11、name ,t_birthdayfrom Teacher where t_birthday between'1970-1-1'and '1970-12-12'(12) select course_no ,count(*)人數(shù)from Choice group by course_no;(13) select *from (select t_no ,count (*)cfrom Teaching group by t_no )ccwhere cc.c >=2(14) select avg(score)as 平均成績,max(score)as 最高分,min(

12、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.復雜的數(shù)據(jù)查詢（1）select student.s_no ,s_name ,course_no ,scorefrom Student left outer join Choice on Student .s_no =Choice .s_no

13、（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-1&#

14、39;and 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）selec

15、t 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.

16、s_no,st.s_name,co.course_no,co.course_name,co.course_score from Choice c,Course co,Student st where c.score<60 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='

17、;程序設計語言'(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,T

18、eacher te,Course co where t.t_no=te.t_no and co.course_no=t.couse_no;(11) select t_no ,t_name ,t_birthdayfrom Teacher where t_birthday between'1970-1-1'and '1970-12-12'(12) select course_no ,count(*)人數(shù)from Choice group by course_no;(13) select *from (select t_no ,count (*)cfrom Teach

19、ing group by t_no )ccwhere cc.c >=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.復雜的數(shù)據(jù)查詢（1）select student.s_no ,s_

20、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_n

21、o ,course_name ,score from class ,Student ,Choice ,Course where class_name='計算機99-1'and 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

22、 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

23、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<60 and c.s_no=st.s_no and co.course_no=c.course_no;(8) select st.s_name,c.score from Choice c ,Cours

24、e 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.

25、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

26、 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,Tea

27、ching,Course where Teacher.t_no=Teaching.t_no and Course.course_no=Teaching.Couse_no and (Course.course_name ='數(shù)據(jù)庫原理與應用') 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

28、_name ='數(shù)據(jù)結構'); (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_no