數(shù)據(jù)查詢(數(shù)據(jù)庫實(shí)驗(yàn)2)

1、數(shù)據(jù)庫基礎(chǔ)與實(shí)踐實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)二 數(shù)據(jù)查詢 班級(jí)： 惠普測(cè)試142 學(xué)號(hào)：1408090213 姓名：閆偉明日期：2016-11-061 實(shí)驗(yàn)?zāi)康模?) 掌握針對(duì)單張基本表的數(shù)據(jù)查詢方法；2) 掌握SQL常用庫函數(shù)及統(tǒng)計(jì)匯總查詢方法；3) 掌握分組查詢方法和結(jié)果排序的方法；4) 掌握連接查詢和嵌套查詢的方法。2 實(shí)驗(yàn)平臺(tái)：操作系統(tǒng)：Windows xp。實(shí)驗(yàn)環(huán)境：SQL Server 2000以上版本。3 實(shí)驗(yàn)內(nèi)容與步驟利用實(shí)驗(yàn)一創(chuàng)建的sch_id數(shù)據(jù)庫完成下列查詢，并對(duì)查詢語句的功能進(jìn)行測(cè)試。1. 查詢學(xué)號(hào)s5的同學(xué)的姓名和所在系，將查詢結(jié)果的列名顯示為中文。代碼： SELECT sn AS

2、姓名,dn AS 所在系 FROM S,D WHERE S.sno=S5 AND S.dno = D.dno測(cè)試記錄：S：D：運(yùn)行結(jié)果截圖：2. 查詢教過課程號(hào)為C2或C5的課程的教師的編號(hào)（寫兩個(gè)查詢語句完成同樣的功能）代碼：SELECT tno FROM TC WHERE cno=C2 OR cno=C5SELECT tno FROM TC WHERE cno IN (C2,C5)測(cè)試記錄：運(yùn)行結(jié)果截圖：3. 查詢學(xué)號(hào)為S5的同學(xué)所學(xué)課程的平均成績(jī)。代碼：SELECT AVG(score) AS 平均分 FROM SC WHERE sno=S5測(cè)試記錄：運(yùn)行結(jié)果截圖：4. 查詢選過課程號(hào)為

3、C1的課程的人數(shù)。代碼：SELECT COUNT(*) FROM SC WHERE cno=C1測(cè)試記錄： 運(yùn)行結(jié)果截圖：5. 查詢計(jì)算機(jī)系學(xué)生的最大年齡，顯示系名和最大年齡（查詢結(jié)果的每列都要有列名且為中文）。代碼：SELECT dn AS 系名,MAX(age)AS 最大年齡 FROM S,D WHERE S.dno=D.dno AND D.dn=計(jì)算機(jī) GROUP BY dn測(cè)試記錄： 運(yùn)行結(jié)果截圖：6. 查詢所有教過課的老師的人數(shù)（教過多門課的同一位老師只計(jì)數(shù)1次）。代碼：SELECT COUNT(DISTINCT tno) AS 所有教過課的老師的人數(shù) FROM TC測(cè)試記錄：運(yùn)行結(jié)

4、果截圖：7. 查詢信息系年齡在30到39歲之間（包括30和39）的男老師人數(shù)。代碼：SELECT COUNT(*) AS 信息系年齡在到歲之間（包括和）的男老師人數(shù) FROM T,D WHERE T.dno=D.dno AND age BETWEEN 30 AND 39 AND dn=信息測(cè)試記錄：運(yùn)行結(jié)果截圖：8. 查詢選過課的各位學(xué)生的學(xué)號(hào)，選課門數(shù)和平均成績(jī)。代碼：SELECT sno AS 學(xué)號(hào), COUNT (*) AS 選課門數(shù),AVG(score) AS 平均成績(jī) FROM SC GROUP BY sno測(cè)試記錄：運(yùn)行結(jié)果截圖：9. 查詢各系的名稱和所屬教師人數(shù)（查詢結(jié)果的每列都

5、要有列名且為中文）。代碼：SELECT dn AS 系名,COUNT(*) AS 教師人數(shù) FROM T,D WHERE T.dno=D.dno GROUP BY dn測(cè)試記錄：運(yùn)行結(jié)果截圖：10. 查詢選課表中各門課的課程號(hào)，最高分，最低分和平均分（查詢結(jié)果的每列都要有列名且為中文）。代碼：SELECT cno AS 課程號(hào),MAX(score) AS 最高分,MIN(score) AS 最低分,AVG(score) AS 平均分 FROM SC GROUP BY cno測(cè)試記錄：運(yùn)行結(jié)果截圖：11. 查詢計(jì)算機(jī)系女生的學(xué)生信息，按年齡的降序、學(xué)號(hào)的升序顯示學(xué)生學(xué)號(hào)，姓名，年齡。代碼：SEL

6、ECT * FROM S WHERE sex=女 AND dno=(SELECT dno FROM D WHERE dn=計(jì)算機(jī)) ORDER BY age DESC,sno ASC測(cè)試記錄：運(yùn)行結(jié)果截圖：12. 查詢各職稱的老師的職稱，最高工資，最低工資，最高和最低工資間的差額，平均工資，按平均工資的降序顯示上述信息（查詢結(jié)果的每列都要有列名且為中文）。代碼：SELECT prof AS 職稱, MAX(sal) AS 最高工資,MIN(sal) AS 最低工資,MAX(sal) - MIN(sal) AS最高和最低工資間的差額,AVG(sal) AS 平均工資 FROM T GROUP B

7、Y prof ORDER BY prof DESC測(cè)試記錄：運(yùn)行結(jié)果截圖：13. 查詢選修了C1和C2兩門課的學(xué)生的學(xué)號(hào)。（使用連接和嵌套兩種方式完成查詢）代碼：SELECT sno FROM SC WHERE sno IN (SELECT sno FROM SC WHERE cno=C2) AND cno=C1SELECT SC1.sno FROM SC SC1,SC SC2 WHERE SC1.sno=SC2.sno AND SCo=C2 AND SCo=C1測(cè)試記錄：運(yùn)行結(jié)果截圖：14. 查詢計(jì)算機(jī)系所有學(xué)生選修信息，顯示學(xué)生學(xué)號(hào)，課程號(hào)和成績(jī)。代碼：SELECT sno,cno,sco

8、re FROM SC WHERE sno IN (SELECT sno FROM S WHERE dno=(SELECT dno FROM D WHERE dn=計(jì)算機(jī))測(cè)試記錄：運(yùn)行結(jié)果截圖：15. 查詢所有教過課程號(hào)為C1的課程的老師姓名，職稱，系別。代碼：SELECT tn AS 姓名,prof AS 職稱,dn AS 系別 FROM T,TC,D WHERE T.tno=TC.tno AND cno=C1 AND T.dno=D.dno測(cè)試記錄：運(yùn)行結(jié)果截圖：16. 查詢具有教授和副教授職稱的老師所教課程的編號(hào)和名稱。代碼：SELECT C.cno,C.cn FROM T,TC,C W

9、HERE prof IN (教授,副教授) AND T.tno=TC.tno AND TC.cno=C.cno測(cè)試記錄：運(yùn)行結(jié)果截圖：17. 查詢孫珊同學(xué)的成績(jī)單，顯示課程號(hào)，課程名和成績(jī)。代碼：SELECT sn AS 姓名,SC.cno AS 課程號(hào),cn AS 課程名,score AS 成績(jī) FROM SC,C,S WHERE SC.cno=C.cno AND S.sno=SC.sno AND S.sn=孫珊測(cè)試記錄：運(yùn)行結(jié)果截圖：18. 查詢選修了C1但沒有選修C2課程的學(xué)生的學(xué)號(hào)。代碼：SELECT sno FROM SC WHERE sno NOT IN (SELECT sno F

10、ROM SC WHERE cno=C2) AND cno=C1測(cè)試記錄：運(yùn)行結(jié)果截圖：19. 查詢計(jì)算機(jī)系學(xué)生選修過的課程編號(hào)和名稱。代碼：SELECT DISTINCT SC.cno AS 課程編號(hào),cn AS 名稱 FROM C,SC,S WHERE C.cno=SC.cno AND SC.sno=S.sno AND S.dno=(SELECT dno FROM D WHERE dn=計(jì)算機(jī))測(cè)試記錄：運(yùn)行結(jié)果截圖：20. 查詢所有沒有選修過任何課程的學(xué)生的學(xué)號(hào)和姓名。代碼：SELECT sno AS 學(xué)號(hào),sn AS 姓名 FROM S WHERE sno NOT IN (SELECT

11、sno FROM SC)測(cè)試記錄：運(yùn)行結(jié)果截圖：21. 查詢教授課程門數(shù)超過3門的老師的編號(hào)和姓名。代碼：SELECT tno 教師編號(hào),tn 姓名 FROM T WHERE tno IN (SELECT tno FROM TC GROUP BY tno HAVING COUNT(*) 3)測(cè)試記錄：運(yùn)行結(jié)果截圖：22. 查詢選課表中出現(xiàn)過的不及格情況的學(xué)生編號(hào)，學(xué)生姓名及其不及格門數(shù)。代碼：-版本1SELECT 學(xué)生編號(hào),sn 姓名,不及格門數(shù) FROM S,(SELECT sno 學(xué)生編號(hào),COUNT(sno) 不及格門數(shù) FROM SC WHERE score 60 GROUP BY s

12、no) A WHERE S.sno=A.學(xué)生編號(hào)-版本2SELECT SC.sno 學(xué)生編號(hào),sn 姓名,COUNT(cno) 不及格門數(shù) FROM S,SC WHERE S.sno=SC.sno AND score 60 GROUP BY sn,SC.sno測(cè)試記錄：運(yùn)行結(jié)果截圖：4 討論1） 請(qǐng)根據(jù)自己的理解解釋exists謂詞常用于進(jìn)行相關(guān)子查詢的原因。只要子查詢有記錄 exists謂詞就返回true，而不用去管子查詢中一共有多少條記錄，因此，如果外層的主查詢記錄較少，子查詢中的表大時(shí)使用exists會(huì)提高性能和查詢效率。2） 請(qǐng)用除exists外的謂詞構(gòu)造相關(guān)子查詢，先定義查詢需求再給出查詢語句。查詢選課學(xué)生的學(xué)生信息SELECT * FROM S WHERE