SQL實(shí)驗(yàn)二數(shù)據(jù)庫(kù)查詢實(shí)驗(yàn)報(bào)告

1、實(shí)驗(yàn)二 數(shù)據(jù)庫(kù)的查詢實(shí)驗(yàn)一、 實(shí)驗(yàn)?zāi)康暮鸵螅?）掌握SQL Server查詢分析器的使用方法，加深對(duì)SQL和Transact-SQL語(yǔ)言的查詢語(yǔ)句的理解。（2）熟練掌握簡(jiǎn)單表的數(shù)據(jù)查詢、數(shù)據(jù)排序和數(shù)據(jù)連接查詢的操作方法。（3）熟練掌握數(shù)據(jù)查詢中的分組、統(tǒng)計(jì)、計(jì)算和組合的操作方法。二、 實(shí)驗(yàn)內(nèi)容和原理在實(shí)驗(yàn)一定義的“學(xué)生成績(jī)數(shù)據(jù)庫(kù)”中，使用T-SQL語(yǔ)句完成以下查詢：（1）求計(jì)算機(jī)系學(xué)生的學(xué)號(hào)和姓名。（2）求選修了數(shù)學(xué)的學(xué)生學(xué)號(hào)、姓名和成績(jī)。（3）求選修01課程的學(xué)生學(xué)號(hào)和成績(jī)，并要求對(duì)查詢結(jié)果按成績(jī)降序排列，如果成績(jī)相同則按學(xué)號(hào)升序排列。（4）查找選修課程的平均成績(jī)位于前三名的學(xué)生的學(xué)號(hào)。（

2、5）查詢計(jì)算機(jī)系的姓劉且單名的學(xué)生的信息。（6）查詢至少選修兩門課程的學(xué)生學(xué)號(hào)。（7）查詢學(xué)生的學(xué)號(hào)、課程號(hào)以及對(duì)應(yīng)成績(jī)與所有學(xué)生所有課程的最高成績(jī)的百分比。（8）查詢選修“數(shù)據(jù)庫(kù)”課程，且成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和成績(jī)。（9）查詢所有姓“王”的同學(xué)沒有選修的課程名。（請(qǐng)分別用exists和in完成該查詢）（10）查詢選修了全部課程的學(xué)生的姓名。（請(qǐng)至少寫出兩種查詢語(yǔ)句）（11）求選修了學(xué)生“95001”所選修的全部課程的學(xué)生學(xué)號(hào)和姓名。（12）查詢每一門課的間接先修課。（13）列出所有學(xué)生所有可能的選課情況。（14）列出每個(gè)學(xué)生的學(xué)號(hào)及選修課程號(hào)，沒有選修的學(xué)生的學(xué)號(hào)也一并列出。（15）

3、輸出與“張三”同性別并位于同一個(gè)系的所有同學(xué)的姓名。（請(qǐng)至少寫出兩種查詢語(yǔ)句）（16）查詢至少被兩名男生選修的課程名。（17）對(duì)被兩名以上學(xué)生所選修的課程統(tǒng)計(jì)每門課的選課人數(shù)。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列；若人數(shù)相同，按課程號(hào)升序排列。（18）列出選修課程超過3門的學(xué)生姓名及選修門數(shù)。（19）檢索至少選修課程號(hào)為01和03的學(xué)生姓名。（20）檢索至少選修課程“數(shù)學(xué)”和“操作系統(tǒng)”的學(xué)生學(xué)號(hào)。（21）查詢操作系統(tǒng)課程的最高分的學(xué)生的姓名、性別、所在系（22）查詢數(shù)據(jù)結(jié)構(gòu)的成績(jī)低于操作系統(tǒng)的成績(jī)的學(xué)生姓名及該生的這兩門課的成績(jī)（23）所有成績(jī)都在70分以上的學(xué)生姓名及所在系。三

4、、實(shí)驗(yàn)環(huán)境四、實(shí)驗(yàn)方法 1. 將查詢需求用Transact-SQL語(yǔ)言表示。 2. 在 SQL Server查詢分析器的輸入?yún)^(qū)中輸入 Transact-SQL查詢語(yǔ)句。 3. 發(fā)布執(zhí)行命令，查看查詢結(jié)果；如果結(jié)果不正確，進(jìn)行修改，直到正確為止。 4. 查詢分析器及使用方法。圖1 SQL Server 2000查詢分析器 查詢分析器是在開發(fā)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)時(shí)使用最多的工具。查詢分析器的主要作用是編輯Transact-SQL，將其發(fā)送到服務(wù)器，并將執(zhí)行結(jié)果及分析顯示出來(lái)（或進(jìn)行存儲(chǔ)）。查詢分析功能主要通過測(cè)試查詢成本，判斷該查詢是否需要增加索引以提高查詢速度，并可以實(shí)現(xiàn)自動(dòng)建立索引的功能、查詢分析器

5、的界面如圖1所示。 在查詢分析器中的左邊窗口是對(duì)象瀏覽器，其中按樹結(jié)構(gòu)列出了數(shù)據(jù)庫(kù)對(duì)象；右上方是SQL代碼區(qū)域用于輸入 SQL的查詢語(yǔ)句；右下方為結(jié)果區(qū)，用于顯示查詢結(jié)果和分析結(jié)果、對(duì)于TSQL語(yǔ)句的執(zhí)行結(jié)果，在結(jié)果區(qū)中可以有4種不同的輸出形式：標(biāo)準(zhǔn)執(zhí)行將結(jié)果直接顯示在結(jié)果區(qū)：網(wǎng)格執(zhí)行將結(jié)果以表格形式顯示在結(jié)果區(qū)；計(jì)劃執(zhí)行顯示執(zhí)行計(jì)劃；索引分析為在結(jié)果區(qū)中顯示查詢的索引情況。上述輸出形式，可以通過菜單或按鈕選擇。五、調(diào)試過程五、實(shí)驗(yàn)結(jié)果六、總結(jié)附錄： -（1）求計(jì)算機(jī)系學(xué)生的學(xué)號(hào)和姓名。select sno,sname from student where sdept='計(jì)算機(jī)'

6、;-（2）求選修了數(shù)學(xué)的學(xué)生學(xué)號(hào)、姓名和成績(jī)。select s.sno,sname,grade from student s,sc,course c where s.sno=sc.sno and o=o and cname='數(shù)學(xué)'-（3）求選修課程的學(xué)生學(xué)號(hào)和成績(jī)，并要求對(duì)查詢結(jié)果按成績(jī)降序排列，如果成績(jī)相同則按學(xué)號(hào)升序排列。select sno,grade from sc where cno='1' order by grade desc,sno asc-（4）查找選修課程的平均成績(jī)位于前三名的學(xué)生的學(xué)號(hào)。select top 3 sno from sc g

7、roup by sno order by avg(grade) desc-（5）查詢計(jì)算機(jī)系的姓劉且單名的學(xué)生的信息。select * from student where sdept='計(jì)算機(jī)' and sname like '劉_'-（6）查詢至少選修兩門課程的學(xué)生學(xué)號(hào)。select sno from sc group by sno having count(*)>=2-（7）查詢學(xué)生的學(xué)號(hào)、課程號(hào)以及對(duì)應(yīng)成績(jī)與所有學(xué)生所有課程的最高成績(jī)的百分比。select sno,cno,grade,最高成績(jī)百分比=grade*100/(select max(gr

8、ade)from sc)from sc -（8）查詢選修“數(shù)據(jù)庫(kù)”課程，且成績(jī)?cè)诜忠陨系膶W(xué)生的學(xué)號(hào)和成績(jī)。select sno,gradefrom course c,scwhere cname='數(shù)據(jù)庫(kù)' and grade>80 and o=o-（9）查詢所有姓“王”的同學(xué)沒有選修的課程名。（請(qǐng)分別用exists和in完成該查詢）-exists方法select cnamefrom course cwhere not exists(select s.snofrom student s,scwhere s.sno=sc.sno and sname like '王%&

9、#39; and o=o )-in方法select cnamefrom course cwhere cno not in(select cnofrom student s,scwhere s.sno=sc.sno and sname like '王%' and o=o )-（10）查詢選修了全部課程的學(xué)生的姓名。（請(qǐng)至少寫出兩種查詢語(yǔ)句）-法一select Snamefrom student swhere not exists(select *from course cwhere not exists(select *from scwhere sno = s.sno and c

10、no = o)-法二select Snamefrom student swhere (select count(*)from sc where sno = s.sno)=(select count(*) from course)-法三select Snamefrom studentwhere Sno in(select Sno from sc group by sno having count(*) = (select count(*) from course)-（11）求選修了學(xué)生“”所選修的全部課程的學(xué)生學(xué)號(hào)和姓名。select s.sno,sname from student s,scw

11、here o in( select cno from sc where sno='95001')and s.sno=sc.snogroup by s.sno,snamehaving count(cno)=(select count(cno) from sc where sno='95001') and s.sno!='95001'-（12）查詢每一門課的間接先修課。select o,c2.cpnofrom course c1,course c2where c1.cpno = o-（13）列出所有學(xué)生所有可能的選課情況。select s.sno,s

12、.sname,o,amefrom student s cross join course c-（14）列出每個(gè)學(xué)生的學(xué)號(hào)及選修課程號(hào)，沒有選修的學(xué)生的學(xué)號(hào)也一并列出。select s.sno,ofrom student s left outer join scon s.sno = sc.sno-（15）輸出與“張三”同性別并位于同一個(gè)系的所有同學(xué)的姓名。（請(qǐng)至少寫出兩種查詢語(yǔ)句）-法一select snamefrom studentwhere sdept in(select sdept from studentwhere sname = '張三' and ssex =(sele

13、ct ssex from student where sname='張三') group by snamehaving sname!='張三'-法二select snamefrom student where sdept =(select sdept from studentwhere sname = '張三' and ssex =(select ssex from student where sname='張三')group by snamehaving sname!='張三'-（16）查詢至少被兩名男生選修的課

14、程名。select cnamefrom course c,student s,scwhere ssex='男' and o=o and s.sno=sc.snogroup by cnamehaving count(*)>=2-（17）對(duì)被兩名以上學(xué)生所選修的課程統(tǒng)計(jì)每門課的選課人數(shù)。- 要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列；若人數(shù)相同，按課程號(hào)升序排列。select cno,count(*) as '選修人數(shù)'from scgroup by cnohaving count(*)>2order by '選修人數(shù)'desc,c

15、no asc-（18）列出選修課程超過門的學(xué)生姓名及選修門數(shù)。select sname,count(*) as '選修門數(shù)'from student s,scwhere s.sno=sc.snogroup by s.sno,snamehaving count(*)>3-（19）檢索至少選修課程號(hào)為和的學(xué)生姓名。select snamefrom studentwhere sno in(select s1.snofrom sc s1,sc s2where o='1' and o='3' and s1.sno=s2.sno)-（20）檢索至少選修

16、課程“數(shù)學(xué)”和“操作系統(tǒng)”的學(xué)生學(xué)號(hào)。select sc.snofrom course c,sc where ame = '數(shù)學(xué)' and o = o and sno in ( select sc.snofrom sc ,course c where ame = '操作系統(tǒng)' and o = o)-（21）查詢操作系統(tǒng)課程的最高分的學(xué)生的姓名、性別、所在系select sname,ssex,sdeptfrom student s,scwhere s.sno=sc.sno and grade= (select max(grade) from course c,sc where o=o and cname='操作系統(tǒng)')-（22）查詢數(shù)據(jù)結(jié)構(gòu)的成績(jī)低于操作系統(tǒng)的成績(jī)的學(xué)生姓名及該生的這兩門課的成績(jī)select s1.sname,sc1.grade as '操作系統(tǒng)成績(jī)',sc2.grade as '數(shù)據(jù)結(jié)構(gòu)成績(jī)'from course c1,course c2,sc sc1,sc sc2,student s1,student s2where