數(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、XXs_name、性別S_sex、出生日期s_birthday。(2)查詢所有同學(xué)，要求顯示其學(xué)號(hào)s_no、XXs_name。(3)查詢所有男同學(xué)，要求顯示其學(xué)號(hào)s_no、XXs_name、出生日期s_birthday。(4)查詢所有出生日期在“1980一01一01”前的女同學(xué)，要求顯示其學(xué)號(hào)sno、XXS_name、性別s_sex、出生日期s_birthday。(5)查詢所有姓“李”的男同學(xué)，要求顯示其學(xué)號(hào)s_no、XXs_name、性別s_sex

2、、出生日期s_birthday。(6)查詢所有XX中含有“一”字的同學(xué)，要求顯示其學(xué)號(hào)s_no、XXs_name。(7)查詢所有職稱(chēng)不是“講師”的教師，要求顯示其教師號(hào)t_no、XXt_name、職稱(chēng)t_title。(8)查詢雖選修了課程，但未參加考試的所有同學(xué)，要求顯示出這些同學(xué)的學(xué)號(hào)(9)查詢所有考試不及格的同學(xué)，要求顯示出這些同學(xué)的學(xué)號(hào)s_no、成績(jī)s_no。score，并按成績(jī)降序排列。(10)查詢出課程號(hào)為01001，02001，02003的所有課程，要求顯示出課程號(hào)course_no、Course_name。(要求用in運(yùn)算符)。三、復(fù)雜數(shù)據(jù)查詢本題中所用的數(shù)據(jù)庫(kù)是第l題中所建立的

3、Study數(shù)據(jù)庫(kù)。(1)查詢所有同學(xué)的選課及成績(jī)情況，要求顯示學(xué)生的學(xué)號(hào)s_no、XXs_name、課程號(hào)Course_no和課程的成績(jī)score。(2)查詢所有同學(xué)的選課及成績(jī)情況，要求顯示學(xué)生的XXs_name、課程名稱(chēng)course_name、課程的成績(jī)score，并將查詢結(jié)果存放到一個(gè)新的數(shù)據(jù)表new_table中。(3)查詢“計(jì)算機(jī)99-1”班的同學(xué)的選課及成績(jī)情況，要求顯示學(xué)生的學(xué)號(hào)s_no、XXs_name、課程號(hào)course_no、課程名稱(chēng)course_name、課程的成績(jī)score。(4)查詢所有同學(xué)的學(xué)分情況(假設(shè)課程成績(jī)=60時(shí)可獲得該門(mén)課程的學(xué)分)，要求顯示學(xué)生的學(xué)號(hào)s_

4、no、XXs_name、總學(xué)分(將該列定名為：total_score)。(用JOIN)(5)查詢所有同學(xué)的平均成績(jī)及選課門(mén)數(shù)，要求顯示學(xué)生的學(xué)號(hào)s_no、XXs_name、平均成績(jī)(將該列定名為：average_score)、選課的門(mén)數(shù)(將該列定名為：choice_num)。(6)查詢所有選修了課程但未參加考試的所有同學(xué)及相應(yīng)的課程，要求顯示學(xué)生的學(xué)號(hào)S_no、XXs_name、課程號(hào)course_no、課程名稱(chēng)course_name。(7)查詢所有選修了課程但考試不及格(假設(shè)60分為不及格)的所有同學(xué)及相應(yīng)的課程，要求顯示學(xué)生的學(xué)號(hào)s_no、XXs_name、課程號(hào)course_no、課程名

5、稱(chēng)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、XXs_name、班級(jí)名稱(chēng)class_name、課程號(hào)course_no、課程名稱(chēng)course_name、課程的成績(jī)score。(10)查詢所有教師的任課情況，要求顯示教師XXt_name、擔(dān)任課程的名稱(chēng)course_name。四、用Transact-SQL語(yǔ)句定義存儲(chǔ)過(guò)程1、創(chuàng)建一個(gè)能向?qū)W生表Student中插入一條記錄的存儲(chǔ)過(guò)程要5個(gè)

6、參數(shù)，分別用來(lái)傳遞學(xué)號(hào)、XX、班級(jí)、性別、出生日期。Insert_student，該過(guò)程需2、寫(xiě)出執(zhí)行存儲(chǔ)過(guò)程Insert_student的SQL語(yǔ)句，向數(shù)據(jù)表Student中插入一個(gè)新同學(xué)，并提供相應(yīng)的實(shí)參值（實(shí)參值自己給出）。3、創(chuàng)建一個(gè)向課程表中插入一門(mén)新課程的存儲(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ù)“學(xué)分”提供實(shí)參值時(shí)，存儲(chǔ)過(guò)程將按默認(rèn)值進(jìn)行運(yùn)算。4、執(zhí)行存儲(chǔ)過(guò)程Insert_course，向課程表Course中插入一門(mén)新課程。分兩種情況寫(xiě)出相應(yīng)的

7、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é)生的XX、年級(jí)、性別及出生日期。6、執(zhí)行存儲(chǔ)過(guò)程Query_student，查詢學(xué)號(hào)為”001101”的學(xué)生的學(xué)號(hào)、班級(jí)號(hào)、性別及出生日期。寫(xiě)出完成此功能的SQL命令。五、用Transact-SQL語(yǔ)句自定義觸發(fā)器1、創(chuàng)建一個(gè)向?qū)W生表Student中插入一新同學(xué)時(shí)能自動(dòng)列出全部同學(xué)信息的觸發(fā)器Display_tr

8、igger2、執(zhí)行存儲(chǔ)過(guò)程insert_student，向?qū)W生表中插入一新同學(xué)，看觸發(fā)器Display_trigger是否被執(zhí)行2簡(jiǎn)單的數(shù)據(jù)查詢select*fromStudent;selects_no,s_namefromStudentselects_no,s_name,s_birthdayfromStudentwheres_sex=男Selects_no,s_name,s_sex,s_birthdayFromStudentWhere(s_sex=女)and(s_birthday=1980-01-01)(5)selects_no,s_name,s_sexfromStudentWheres_s

9、ex=男ands_namelike,s_birthday李%(6)selectFromstudents_no,s_nameWheres_namelike%一%selectt_no,t_name,t_titlefromTeacherwheret_titlenotin(講師)selects_nofromChoicewherescoreisnullselects_no,scorefromChoicewherescore=2(14)selectavg(score)as平均成績(jī),max(score)as最高分,min(score)as最低分fromChoicewherecourse_no=01001se

10、lectt_name,t_birthdayfromTeacherwhere(t_birthday1960)and(t_titleorderbyt_birthdaydesc=講師)3.復(fù)雜的數(shù)據(jù)查詢1）selectstudent.s_no,s_name,course_no,scorefromStudentleftouterjoinChoiceonStudent.s_no=Choice.s_no（2）selects_name,Course.course_name,scoreintonew_tablefromStudent,Choice,CoursewhereCourse.course_no=Cho

11、ice.course_noandStudent.s_no=Choice.s_no3）selectStudent.s_no,s_name,Choice.course_no,course_name,scorefromclass,Student,Choice,Coursewhereclass_name=計(jì)算機(jī)99-1andChoice.course_no=Course.course_noandChoice.s_no=Student.s_no（4）selectStudent.s_no,s_name,sum(course_score)astotal_scoreFromStudentInnerjoinCh

12、oiceonStudent.s_no=Choice.s_noInnerjoinCourseonChoice.course_no=Course.course_noandscore=60groupbyStudent.s_no,s_name（5）selectc.s_no,s.s_name,avg(c.score)average_score,count(*)choice_numfromChoicec,Studentswherec.s_no=s.s_nogroupbyc.s_no,s.s_name;(6)selects.s_no,s.s_name,co.course_no,co.course_namef

13、romChoicec,Students,Coursecowherec.score=0andc.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_scorefromChoicec,Courseco,Studentstwherec.score=2(14)selectavg(score)as平均成績(jī),max(score)as最高分,min(score)as最低分fromChoicewherecourse_no=01001selectt_name

14、,t_birthdayfromTeacherwhere(t_birthday1960)and(t_titleorderbyt_birthdaydesc=講師)3.復(fù)雜的數(shù)據(jù)查詢1）selectstudent.s_no,s_name,course_no,scorefromStudentleftouterjoinChoiceonStudent.s_no=Choice.s_no（2）selects_name,Course.course_name,scoreintonew_tablefromStudent,Choice,CoursewhereCourse.course_no=Choice.course

15、_noandStudent.s_no=Choice.s_no3）selectStudent.s_no,s_name,Choice.course_no,course_name,scorefromclass,Student,Choice,Coursewhereclass_name=計(jì)算機(jī)99-1andChoice.course_no=Course.course_noandChoice.s_no=Student.s_no（4）selectStudent.s_no,s_name,sum(course_score)astotal_scoreFromStudentInnerjoinChoiceonStud

16、ent.s_no=Choice.s_noInnerjoinCourseonChoice.course_no=Course.course_noandscore=60groupbyStudent.s_no,s_name（5）selectc.s_no,s.s_name,avg(c.score)average_score,count(*)choice_numfromChoicec,Studentswherec.s_no=s.s_nogroupbyc.s_no,s.s_name;(6)selects.s_no,s.s_name,co.course_no,co.course_namefromChoicec

17、,Students,Coursecowherec.score=0andc.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_scorefromChoicec,Courseco,Studentstwherec.score60andc.s_no=st.s_noandco.course_no=c.course_no;(8)selectst.s_name,c.scorefromChoicec,Courseco,Studentstwherest.s

18、_no=c.s_noandco.course_no=c.course_noandco.course_name=程序設(shè)計(jì)語(yǔ)言;selectst.s_no,st.s_name,c.class_name,co.course_no,co.course_name,ch.scorefromStudentst,Classc,Choicech,CoursecoWherec.class_no=st.class_noandc.class_dept=計(jì)算機(jī)系andch.s_no=st.s_noandco.course_no=ch.course_no;(10)selectte.t_name,co.course_nam

19、efromTeachingt,Teacherte,Coursecowheret.t_no=te.t_noandco.course_no=t.couse_no;(11)selectt.t_no,te.t_name,count(*)course_numberfromTeachingt,Teachertewheret.t_no=te.t_nogroupbyt.t_no,te.t_name;(12)select*fromStudentstwherest.class_noin(selectclass_nofromStudentwheres_name=李建國(guó))(13)select*fromChoicech

20、,StudentstwherenotExists(selectcourse_nofromCoursewherecourse_name=計(jì)算機(jī)基礎(chǔ)andch.course_no!=course_no)andst.s_no=ch.s_no;(14)selectTeacher.t_namefromTeacher,Teaching,CoursewhereTeacher.t_no=Teaching.t_noandCourse.course_no=Teaching.Couse_noand(Course.course_name=數(shù)據(jù)庫(kù)原理與應(yīng)用)groupbyTeacher.t_nameunion(sele

21、ctTeacher.t_namefromTeacher,Teaching,CoursewhereTeacher.t_no=Teaching.t_noandCourse.course_no=Teaching.Couse_noandCourse.course_name=數(shù)據(jù)結(jié)構(gòu));(15)selectt.t_namefromTeachert,(selectte.t_no,count(*)cfromTeachingtegroupbyte.t_no)ccwherecc.c=6andcc.t_no=t.t_no;4.用Transact-SQL語(yǔ)句定義存儲(chǔ)過(guò)程(1)createprocedureinsert_student(s_nochar(6),s_namechar(6),class_nochar(6),s_sexchar(2),s_birthdaydatetime)asinsertintoStudent(s_no,s_name,class_no,s_sex,s_birthday)VALUES(s_no,s_name,class_no,s_sex,s_birthday);(2