數(shù)據(jù)庫(kù)原理及應(yīng)用教程綜合實(shí)訓(xùn)

1、二、簡(jiǎn)單的數(shù)據(jù)查詢 本題中所用的數(shù)據(jù)庫(kù)是第1題中所建立的Study數(shù)據(jù)庫(kù)。(1)查詢所有同學(xué)的基本信息，包括：學(xué)號(hào)s_no、班級(jí)號(hào)class_no、姓名s_name、性別S_sex、出生日期s_birthday。 (2)查詢所有同學(xué)，要求顯示其學(xué)號(hào)s_no、姓名s_name。 (3)查詢所有男同學(xué)，要求顯示其學(xué)號(hào)s_no、姓名s_name、出生日期s_birthday。 (4)查詢所有出生日期在“1980一01一01”前的女同學(xué)，要求顯示其學(xué)號(hào)s no、姓名S_name、性別s_sex、出生日期s_birthday。 (5)查詢所有姓“李”的男同學(xué)，要求顯示其學(xué)號(hào)s _no、姓名s _name

2、、性別s _sex、出生日期s _birthday。 (6)查詢所有姓名中含有“一”字的同學(xué)，要求顯示其學(xué)號(hào)s _no、姓名s_ name。 (7)查詢所有職稱不是“講師”的教師，要求顯示其教師號(hào)t _no、姓名t _name、職稱t _title。 (8)查詢雖選修了課程，但未參加考試的所有同學(xué)，要求顯示出這些同學(xué)的學(xué)號(hào)s _no。 (9)查詢所有考試不及格的同學(xué)，要求顯示出這些同學(xué)的學(xué)號(hào)s _no、成績(jī)score，并按成績(jī)降序排列。 (10)查詢出課程號(hào)為01001，02001，02003的所有課程，要求顯示出課程號(hào)course_no、Course_name。(要求用in運(yùn)算符)。 三、復(fù)

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

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

5、所有同學(xué)及相應(yīng)的課程，要求顯示學(xué)生的學(xué)號(hào)s_no、姓名s_name、課程號(hào)course_no、課程名稱course _name、課程成績(jī)course_score。 (8)查詢選修了課程名為“程序設(shè)計(jì)語(yǔ)言”的所有同學(xué)及成績(jī)情況，要求顯示學(xué)生的姓名s_ name、課程的成績(jī)score。(使用ANY) (9)查詢“計(jì)算機(jī)系”的所有同學(xué)及成績(jī)情況，要求顯示學(xué)生的學(xué)號(hào)s_ no、姓名s _name、班級(jí)名稱class _name、課程號(hào)course _no、課程名稱course_name、課程的成績(jī)score。 (10)查詢所有教師的任課情況，要求顯示教師姓名t _name、擔(dān)任課程的名稱course

6、_name。 四、用Transact-SQL語(yǔ)句定義存儲(chǔ)過(guò)程1、 創(chuàng)建一個(gè)能向?qū)W生表 Student 中插入一條記錄的存儲(chǔ)過(guò)程Insert_student，該過(guò)程需要5個(gè)參數(shù)，分別用來(lái)傳遞學(xué)號(hào)、姓名、班級(jí)、性別、出生日期。2、 寫(xiě)出執(zhí)行存儲(chǔ)過(guò)程 Insert_student 的 SQL 語(yǔ)句， 向數(shù)據(jù)表 Student 中插入一個(gè)新同學(xué)，并提供相應(yīng)的實(shí)參值（實(shí)參值自己給出）。 3、創(chuàng)建一個(gè)向課程表中插入一門新課程的存儲(chǔ)過(guò)程Insert_course，該存儲(chǔ)過(guò)程需要三個(gè)參數(shù)，分別用來(lái)傳遞課程號(hào)、課程名、學(xué)分，但允許參數(shù)“學(xué)分”的默認(rèn)值為2，即當(dāng)執(zhí)行存儲(chǔ)過(guò)程Insert_course時(shí)，未給參數(shù)“

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

8、QL命令。五、用Transact-SQL語(yǔ)句自定義觸發(fā)器1、創(chuàng)建一個(gè)向?qū)W生表Student中插入一新同學(xué)時(shí)能自動(dòng)列出全部同學(xué)信息的觸發(fā)器Display_trigger2、執(zhí)行存儲(chǔ)過(guò)程insert_student，向?qū)W生表中插入一新同學(xué)，看觸發(fā)器Display_trigger是否被執(zhí)行2簡(jiǎn)單的數(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,s_name

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

10、eacher 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_name ,

11、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 平均成績(jī),max(score)as 最高分,min(score)

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

13、ect 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='計(jì)算機(jī)99-1'and

14、 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_

15、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,s

16、t.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='程序設(shè)計(jì)語(yǔ)

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='計(jì)算機(jī)系' 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

18、 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 Teaching gr

19、oup by t_no )ccwhere cc.c >=2(14) select avg(score)as 平均成績(jī),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.復(fù)雜的數(shù)據(jù)查詢（1）select student.s_no ,s_name ,

20、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 ,cou

21、rse_name ,score from class ,Student ,Choice ,Course where class_name='計(jì)算機(jī)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 Choic

22、e.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,Cour

23、se 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 ,Course co,S

24、tudent st where st.s_no=c.s_no and co.course_no=c.course_no and co.course_name='程序設(shè)計(jì)語(yǔ)言'(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='計(jì)算機(jī)系' and ch.s_no=st.s_no a

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

26、nt st where st.class_no in (select class_no from Student where s_name='李建國(guó)')(13)select * from Choice ch,Student st where not Exists(select course_no from Course where course_name='計(jì)算機(jī)基礎(chǔ)' and ch.course_no!=course_no) and st.s_no=ch.s_no;(14)select Teacher.t_name from Teacher,Teaching,

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

28、='數(shù)據(jù)結(jié)構(gòu)'); (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語(yǔ)句定義存儲(chǔ)過(guò)程(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,