數(shù)據(jù)庫(kù)1數(shù)據(jù)查詢

1、實(shí)驗(yàn)一 數(shù)據(jù)庫(kù)查詢課程名稱：數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)實(shí)驗(yàn)類型：驗(yàn)證型實(shí)驗(yàn)名稱數(shù)據(jù)庫(kù)查詢學(xué)時(shí)4學(xué)時(shí)實(shí)驗(yàn)?zāi)康模菏箤W(xué)生掌握SQL Server Query Analyzer的使用方法，加深對(duì)SQL和T-SQL語(yǔ)言的查詢語(yǔ)句的理解。熟練掌握表的基本查詢，連接查詢和嵌套查詢，以及掌握數(shù)據(jù)排序和數(shù)據(jù)分組的操作方法。實(shí)驗(yàn)原理：SELECT ALL|DISTINCT <目標(biāo)列表達(dá)式>，<目標(biāo)列表達(dá)式> FROM <表名或視圖名>，<表名或視圖名> WHERE <條件表達(dá)式> GROUP BY <列名1> HAVING <條件表達(dá)式> o

2、rder by <列名2> ASC|DESC；實(shí)驗(yàn)方法：將查詢需求用T-SQL語(yǔ)言表示；在SQL Server Query Analyzer的輸入?yún)^(qū)中輸入T-SQL查詢語(yǔ)句；設(shè)置 Query Analyzer的結(jié)果區(qū)為Standard Execute（標(biāo)準(zhǔn)執(zhí)行）或Execute to Grid（網(wǎng)格執(zhí)行）方式；發(fā)布執(zhí)行命令，并在結(jié)果區(qū)中查看查詢結(jié)果；如果結(jié)果不正確，要進(jìn)行修改，直到正確為止。實(shí)驗(yàn)內(nèi)容：1. 分別用帶DISTINCT和不帶DISTINCT關(guān)鍵字的SELELCT在student中進(jìn)行查詢.帶distinct:Select class_id from student不帶d

3、istinct:select distinct class_id from student2. 將teacher表中各教師的姓名、教工號(hào)及工資按95發(fā)放的信息，并將工資按95發(fā)放后的列名改為預(yù)發(fā)工資select teacher_id,teacher_name,salary*0.95 as 預(yù)發(fā)工資from teacher3. 查詢course表中所有學(xué)分大于2并且成績(jī)不及格的學(xué)生的信息.select distinct student.* from student,course,sc where student.student_id=sc.student_id and sc.course_id=

4、course.course_id and course.credit>2 and sc.grade<604. 查詢學(xué)分在48之間的學(xué)生信息.（用between.and和復(fù)合條件分別實(shí)現(xiàn)）select distinct student.* from student,course,sc where student.student_id=sc.student_id and sc.course_id=course.course_id and course.credit between 4 and 85. 從student_course表中查詢出學(xué)生為“g9940201”，“g9940202

5、”的課程號(hào)、學(xué)生號(hào)以及學(xué)分，并按學(xué)分降序排列（用in實(shí)現(xiàn)）select * from sc,course where student_id in('g9940201','g9940202') and course.course_id=sc.course_id order by course.credit desc6. 從teacher表中分別檢索出姓王的教師的資料，或者姓名的第2個(gè)字是遠(yuǎn)或輝的教師的資料select * from teacher where teacher_name like '王%' or teacher_name like&#

6、39;_遠(yuǎn)%' or teacher_name like '_輝%'7. 查詢每個(gè)學(xué)生及其選修課情況 select student_name,course_name from student, sc,course where sc.student_id=student.student_id and sc.course_id=course.course_id8. 以student表為主體列出每個(gè)學(xué)生的基本情況及其選課情況，如果學(xué)生沒有選課，只輸出其基本情況 select student.*,course.* from student join sc on student.

7、student_id=sc.student_id left join course on course.course_id=sc.course_id9. 查詢選修dep04_s001號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生信息。（分別用連接，in和exists實(shí)現(xiàn)）連接：select student.* from sc,student where sc.student_id=student.student_id and sc.course_id='dep04_s001' and sc.grade>80In：select * from student where student_id

8、 in( select student_id from sc where course_id='dep04_s001' and grade>80) exists：select * from student where exists( select student_id from sc where student.student_id=sc.student_id and course_id='dep04_s001' and grade>80)10. 查詢所有上計(jì)算機(jī)基礎(chǔ)課程的學(xué)生的學(xué)號(hào)、選修課程號(hào)以及分?jǐn)?shù)（分別用連接，in和exists實(shí)現(xiàn)）連接：se

9、lect sc.* from sc,course where course.course_name='計(jì)算機(jī)基礎(chǔ)' and course.course_id=sc.course_idIn：select student_id,course_id,grade from sc where course_id in( select course_id from course where course_name='計(jì)算機(jī)基礎(chǔ)')exists：select student_id,course_id,grade from sc where exists( select * f

10、rom course where course_id=sc.course_id and course_name='計(jì)算機(jī)基礎(chǔ)')11. 查詢選修了課程名為“數(shù)據(jù)庫(kù)基礎(chǔ)”的學(xué)生學(xué)號(hào)和姓名（分別用連接，in和exists實(shí)現(xiàn)）連接：select student.student_id,student.student_name from sc,student,course where course.course_name='數(shù)據(jù)庫(kù)開發(fā)技術(shù)' and sc.student_id=student.student_id and course.course_id=sc.cour

11、se_idIn：select student_id,student_name from student where student_id in( select student_id from sc where course_id=( select course_id from course where course_name='數(shù)據(jù)庫(kù)開發(fā)技術(shù)' ) )exists：select student_id,student_name from student where exists( select * from sc where sc.student_id=student.stude

12、nt_id and sc.course_id=( select course_id from course where course_name='數(shù)據(jù)庫(kù)開發(fā)技術(shù)' )12. 查詢所有計(jì)算機(jī)系學(xué)生的學(xué)號(hào)、選修課程號(hào)以及分?jǐn)?shù)（分別用連接，in和exists實(shí)現(xiàn)）連接：select sc.* from sc,department,student where department.department_name='計(jì)算機(jī)科學(xué)' and department.department_id=student.department_id and sc.student_id=stu

13、dent.student_idIn：select* from sc where student_id in( select student_id from student where department_id=( select department_id from department where department_name='計(jì)算機(jī)科學(xué)')exists：select* from sc where exists ( select * from student where student.student_id =sc.student_id and student.depar

14、tment_id=( select department_id from department where department_name='計(jì)算機(jī)科')13查詢每個(gè)dep_04系學(xué)生的總成績(jī)、平均成績(jī)， 僅顯示平均成績(jī)及格的學(xué)生的記錄。select student.student_name ,sum(grade) as 總成績(jī),avg(grade) as 平均成績(jī) from sc, student,department where sc.student_id=student.student_id and student.department_id=department.Dep

15、artment_id and department.department_id='dep_04' group by student.student_id,student_name having avg(grade)>6014查詢“數(shù)據(jù)庫(kù)開發(fā)技術(shù)”的平均成績(jī)select avg(grade) as 數(shù)據(jù)庫(kù)開發(fā)平均成績(jī) from sc where course_id in( select course_id from course where course_name='數(shù)據(jù)庫(kù)開發(fā)技術(shù)')15按職稱查詢教師的平均工資，并按總工資降序排列select profes

16、sion,avg(salary) as 平均工資,sum(salary) as 總工資 from teacher group by profession order by sum(salary) desc附錄1：教務(wù)管理數(shù)據(jù)庫(kù)jwgl結(jié)構(gòu)student表結(jié)構(gòu)列名稱數(shù)據(jù)類型長(zhǎng)度允許空值說明Student_idChar8否學(xué)生學(xué)號(hào)Student_nameVarchar8否學(xué)生姓名SexBit1否性別AgeInt4否年齡Class_id Char6否班級(jí)號(hào)Department_idChar6否系編號(hào)Addressvarchar50否家庭住址Course表字段名稱數(shù)據(jù)類型長(zhǎng)度允許空說明Course_i

17、dChar10否課程號(hào)Course_nameVarchar20否課程名稱Book_idChar15否書標(biāo)識(shí)SC表字段名稱數(shù)據(jù)類型長(zhǎng)度允許空說明Course_idChar10否課程號(hào)Student_idVarchar8否學(xué)生號(hào)gradeTinyint否成績(jī)creditTinyint否學(xué)分Teacher表字段名稱數(shù)據(jù)類型長(zhǎng)度允許空說明Teacher_idChar9否教師編號(hào)Teacher_namenvarchar8否教師姓名Department_idChar6否所在系號(hào)ProfessionNvarchar16職稱SexBit否性別phoneNvarchar15是電話adressNchar40是住址SalaryNumeric7.2是工資BirthSmalldatetime否出生日期PostalcodeNumeric6,0是郵編Book表字段名稱數(shù)據(jù)類型長(zhǎng)度允許空說明Book_idChar13否書號(hào)Book_nameVa