數(shù)據(jù)庫SQL查詢語句練習(xí)題

1、36:查詢所學(xué)課程包含學(xué)生S3所學(xué)課程的學(xué)生學(xué)號(hào)1.SELECT Sname FROM S WHERE Sno NOT IN(SELECT Sno FROM SCWHERE Cno= 'C2');(4)檢索選修課程號(hào)為 C2或C4的學(xué)生學(xué)號(hào);SELECT SnoFROM SCWHERE Cno=' C2' OR Cno= ' C4'設(shè)教學(xué)數(shù)據(jù)庫 Education 有三個(gè)關(guān)系：學(xué)生關(guān)系 S (SNQ SNAMEAGE SEX SDEPT;學(xué)習(xí)關(guān)系 SC (SNQCNQ GRADE;課程關(guān)系 C (CNQ CNAME CDEPT TNAME查詢問題

2、：(1)檢索計(jì)算機(jī)系的全體學(xué)生的學(xué)號(hào)，姓名和性別；(2)檢索學(xué)習(xí)課程號(hào)為 C2的學(xué)生學(xué)號(hào)與姓名；(3)檢索選修課程名為“ DS'的學(xué)生學(xué)號(hào)與姓名；(4)檢索選修課程號(hào)為 C2或C4的學(xué)生學(xué)號(hào)；(5)檢索至少選修課程號(hào)為 C2和C4的學(xué)生學(xué)號(hào)；(6)檢索不學(xué)C2課的學(xué)生姓名和年齡；(7)檢索學(xué)習(xí)全部課程的學(xué)生姓名；(8)查詢所學(xué)課程包含學(xué)生 S3所學(xué)課程的學(xué)生學(xué)號(hào)。(1)檢索計(jì)算機(jī)系的全體學(xué)生的學(xué)號(hào)，姓名和性別；SELECT Sno Sname SexFROM SWHERE Sdept =' CS ;(2)檢索學(xué)習(xí)課程號(hào)為 C2的學(xué)生學(xué)號(hào)與姓名；1.SELECT Sno , Sn

3、ame FROM S WHERE Sno IN(SELECT Sno FROM SCWHERE Cno= ' C2')2.SELECT S.Sno , Sname FROM S , SCWHERE S.Sno=SC.SnoAND SC.Cno= ' CZ ;(3)檢索選修課程名為“ DS'的學(xué)生學(xué)號(hào)與姓名本查詢涉及到學(xué)號(hào)、姓名和課程名三個(gè)屬性，分別存放在S和C表中，但S和C表沒有直接聯(lián)系，必須通過SC表建立它們二者的聯(lián)系。C - SC - S 基本思路：(1)首先在C表中找出“ DS'課程的課程號(hào)Cno；(2)然后在SC表中找出Cno等于第一步給出的 C

4、no集合中的某 個(gè)元素Cno；(3)最后在S關(guān)系中選出Sno等于第二步中Sno集合中某個(gè)元 素的元組，取出Sno和Sname送入結(jié)果表列。SELECT Sno Sname FROM S WHERE Sno IN(SELECTSnoFROM SCWHERE Cno IN(SELECT Cno FROM C WHERE Cname='DS);(5)檢索至少選修課程號(hào)為 C2和C4的學(xué)生學(xué)號(hào)；SELECT SnoFROM SC X SC 丫W(wǎng)HERE X.Sno=Y.Sno AND X.Cno= 'C2' AND Y.Cno= ' C4'(6)檢索不學(xué)C2課的

5、學(xué)生姓名和年齡；2.SELECT SnameFROM S WHERE NOT EXISTS (SELECT * FROM SCWHERE SC.Sno=S.SnoAND Cno='C2');(7)檢索學(xué)習(xí)全部課程的學(xué)生姓名；在表S中找學(xué)生，要求這個(gè)學(xué)生學(xué)了全部課程。換言之，在 S表 中找學(xué)生，在 C中不存在一門課程，這個(gè)學(xué)生沒有學(xué)。 SELECT Sname FROM S WHERE NOT EXISTS(SELECT * FROM C WHERE NOT EXISTS(SELECT * FROM SC WHERE SC.Sno=S.Sno AND SC.Cno=C.Cno )

6、;(8)查詢所學(xué)課程包含學(xué)生 S3所學(xué)課程的學(xué)生學(xué)號(hào)。分析：不存在這樣的課程 Y,學(xué)生S3選了 Y,而其他學(xué)生沒有選。 SELECT DISTINCT Sno FROM SC AS XWHERE NOT EXISTS(SELECT *FROM SC AS YWHERE Y.Sno= 'S3' AND NOT EXISTS (SELECT * FROM SC AS Z WHERE Z.Sno=X.Sno AND Z.Cno=Y.Cno );設(shè)教學(xué)數(shù)據(jù)庫Education 有三個(gè)關(guān)系：學(xué)生關(guān)系S（ SNO， SNAM，E AGE， SEX， SDEPT） ；學(xué)習(xí)關(guān)系SC（ SNO，

7、 CNO， GRAD）E；課程關(guān)系C（ CNO， CNAM， E CDEPT， TNAM）E查詢問題：1：查所有年齡在20 歲以下的學(xué)生姓名及年齡。2：查考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)3：查所年齡在20 至 23 歲之間的學(xué)生姓名、系別及年齡。4：查計(jì)算機(jī)系、數(shù)學(xué)系、信息系的學(xué)生姓名、性別。5：查既不是計(jì)算機(jī)系、數(shù)學(xué)系、又不是信息系的學(xué)生姓名、性別6：查所有姓“劉”的學(xué)生的姓名、學(xué)號(hào)和性別。7：查姓“上官”且全名為3 個(gè)漢字的學(xué)生姓名。8：查所有不姓“張”的學(xué)生的姓名。9：查DB_Design 課程的課程號(hào)。10：查缺考的學(xué)生的學(xué)號(hào)和課程號(hào)。11：查年齡為空值的學(xué)生的學(xué)號(hào)和姓名。12：查計(jì)算機(jī)系

8、20 歲以下的學(xué)生的學(xué)號(hào)和姓名。13：查計(jì)算機(jī)系、數(shù)學(xué)系、信息系的學(xué)生姓名、性別。14：查詢選修了C3 課程的學(xué)生的學(xué)號(hào)和成績(jī)，其結(jié)果按分?jǐn)?shù)的降序排列。15：查詢?nèi)w學(xué)生的情況，查詢結(jié)果按所在系升序排列，對(duì)同一系中的學(xué)生按年齡降序排列。16：查詢學(xué)生總?cè)藬?shù)。17：查詢選修了課程的學(xué)生人數(shù)。18：計(jì)算選修了C1 課程的學(xué)生平均成績(jī)。19:查詢學(xué)習(xí)C3課程的學(xué)生最高分?jǐn)?shù)。20：查詢各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)。21：查詢計(jì)算機(jī)系選修了3 門以上課程的學(xué)生的學(xué)號(hào)。22:求基本表S中男同學(xué)的每一年齡組（超過50人）有多少人?要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同按年齡降序排列。23：查詢每個(gè)學(xué)生及其選修課

9、程的情況。24:查詢選修了 C2課程且成績(jī)?cè)?0分以上的所有學(xué)生。25：查詢每個(gè)學(xué)生選修的課程名及其成績(jī)。26：統(tǒng)計(jì)每一年齡選修課程的學(xué)生人數(shù)。27:查詢選修了 C2課程的學(xué)生姓名。28：查詢與“張三”在同一個(gè)系學(xué)習(xí)的學(xué)生學(xué)號(hào)、姓名和系別。29：查詢選修課程名為“數(shù)據(jù)庫”的學(xué)生學(xué)號(hào)和姓名。30：查詢與“張三”在同一個(gè)系學(xué)習(xí)的學(xué)生學(xué)號(hào)、姓名和系別。31：查詢選修課程名為“數(shù)據(jù)庫”的學(xué)生學(xué)號(hào)和姓名。32:查詢選修了 C2課程的學(xué)生姓名。33:查詢所有未選修 C2課程的學(xué)生姓名。34：查詢與“張三”在同一個(gè)系學(xué)習(xí)的學(xué)生學(xué)號(hào)、姓名和系別。35：查詢選修了全部課程的學(xué)生姓名。（ 1 ）比較例 1：查所有

10、年齡在20 歲以下的學(xué)生姓名及年齡。SELECT Sname， SageFROM SWHERE Sage<20; （NOT age>=20）例 2： 查考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)SELECT DISTINCT SnoFROM SCWHERE grade<60 ；（2） 確定范圍例 3： 查所年齡在20 至 23 歲之間的學(xué)生姓名、系別及年齡。SELECT Sname， Sdept， SageFROM SWHERE Sage BETWEEN 20 AND 23；（ 3）確定集合例 4： 查計(jì)算機(jī)系、數(shù)學(xué)系、信息系的學(xué)生姓名、性別。SELECT Sname， SsexFROM

11、SWHERE Sdept IN （CS , IS , MATH ） ；例5： 查既不是計(jì)算機(jī)系、數(shù)學(xué)系、又不是信息系的學(xué)生姓名、性別SELECT Sname， SsexFROM SWHERE Sdept NOT IN （CS , IS , MATH ） ；（ 4）字符匹配例 6： 查所有姓“劉”的學(xué)生的姓名、學(xué)號(hào)和性別。SELECT Sname， Sno， SsexFROM SWHERE Sname LIKE 劉% ；例 7： 查姓“上官”且全名為 3 個(gè)漢字的學(xué)生姓名。SELECT SnameFROM SWHERE Sname LIKE 上官_ _ ；例 8： 查所有不姓“張”的學(xué)生的姓名。

12、SELECT Sname， Sno， SsexFROM SWHERE Sname NOT LIKE 張% ；例 9： 查 DB_Design 課程的課程號(hào)。SELECT CnoFROM CWHERE Cname LIKE DB_Design ESCAPE ；（5） 涉及空值的查詢例 10 ：查缺考的學(xué)生的學(xué)號(hào)和課程號(hào)。SELECT Sno， CnoFROM SCWHERE Grade IS NULL ； （不能用=代替） 有成績(jī)的WHERE Grade IS NOT NULLL ； 例 11： 查年齡為空值的學(xué)生的學(xué)號(hào)和姓名。SELECT Sno， SnameFROM SWHERE Sage

13、IS NULL ；（ 6）多重條件查詢例 12 ： 查計(jì)算機(jī)系20 歲以下的學(xué)生的學(xué)號(hào)和姓名。SELECT Sno， SnameFROM SWHERE Sdept= CS AND Sage<20;例 13： 查計(jì)算機(jī)系、數(shù)學(xué)系、信息系的學(xué)生姓名、性別。SELECT Sname， SsexFROM SWHERE Sdept = CS OR Sdept = IS OR Sdept = MATH ） ； 3、對(duì)查詢結(jié)果排序例14:查詢選修了 C3課程的學(xué)生的學(xué)號(hào)和成績(jī)，其結(jié)果按分?jǐn)?shù)的降序排列。SELECT Sno， GradeFROM SCWHERE Cno= C3ORDER BY Grade

14、 DESC;例 15： 查詢?nèi)w學(xué)生的情況，查詢結(jié)果按所在系升序排列，對(duì)同一系中的學(xué)生按年齡降序排列。SELECT *FROM SORDER BY Sdep， Sage DESC；4. 聚合函數(shù)的使用例 16：查詢學(xué)生總?cè)藬?shù)。SELECT COUNT （ *）FROM S例 17 ：查詢選修了課程的學(xué)生人數(shù)。SELECT COUNT（ DISTINCT Sno ）FROM SC例 18： 計(jì)算選修了C1 課程的學(xué)生平均成績(jī)。SELECT AVG（ Grade）FROM SCWHERE Cno= C1 ；例19:查詢學(xué)習(xí)C3課程的學(xué)生最高分?jǐn)?shù)。SELECT MAX （ Grade）FROM SC

15、WHERE Cno= C3 ；5、對(duì)查詢結(jié)果分組例 20：查詢各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)。SELECT Cno， COUNT（ Sno）FROM SCGROUP BY Cno；該SELEC喃句對(duì)SC表按Cno的取值進(jìn)行分組，所有具有相同Cno值的元組為一組，然后對(duì)每一組作用聚合函數(shù)COUN©求得該組的學(xué)生人數(shù)。如果分組后還要求按一定的條件對(duì)這些組進(jìn)行篩選，最終只輸出滿足指定條件 組，則可以使用 HAVING®語指定篩選條件。例 21 ： 查詢計(jì)算機(jī)系選修了3 門以上課程的學(xué)生的學(xué)號(hào)。SELECT SnoFROM SCWHERE Sdept= CSGROUP BY SnoHA

16、VING COUNT （ *） >3；WHERE?句與HAVING®語的根本區(qū)別在于作用對(duì)象不同。WHERE子句作用于基本表或視圖，從中選擇滿足條件的元組。HAVING雙 語作用于組，從中選擇滿足條件的組。例 22： 求基本表S 中男同學(xué)的每一年齡組（超過50 人）有多少人？要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同按年齡降序排列。SELECT Sage， COUNT （ Sno）FROM SWHERE Ssex='M'GROUP BY SageHAVING COUNT （ *） > 50ORDER BY 2 ， Sage DESC；二、多表查詢1 、聯(lián)接查詢例

17、 23： 查詢每個(gè)學(xué)生及其選修課程的情況。SELECT S.Sno,Sname,Sage,Ssex,Sdept,Cno,GradeFROM S, SCWHERE S.Sno=SC.Sno；例24:查詢選修了 C2課程且成績(jī)?cè)?0分以上的所有學(xué)生。SELECT S.Sno， SnameFROM S ， SCWHERE S.Sno=SC.SnoAND SC.Cno= C2AND SC.Grade > 90;例 25： 查詢每個(gè)學(xué)生選修的課程名及其成績(jī)。SELECT S.Sno， Sname， Cname， SC.GradeFROM S， SC， CWHERE S.Sno=SC.Sno AND

18、 SC.Cno=C.Cno例 26： 統(tǒng)計(jì)每一年齡選修課程的學(xué)生人數(shù)。SELECT Sage， COUNT （ DISTINCT S.Sno ）FROM S ， SCWHERE S.Sno=SC.Sno GROUP BY S ;由于要統(tǒng)計(jì)每一個(gè)年齡的學(xué)生人數(shù)，因此要把滿足WHER皆句中條件的查詢結(jié)果按年齡分組，在每一組中的學(xué)生年齡相同。此時(shí)的SELECT子句應(yīng)對(duì)每一組分開進(jìn)行操作，在每一組中，年 齡只有一個(gè)值，統(tǒng)計(jì)的人數(shù)是這一組中的學(xué)生人數(shù)。1、嵌套查詢(1)帶有IN謂詞的子查詢指父查詢與子查詢之間用IN進(jìn)行聯(lián)接，判斷某個(gè)屬性列值是否在子查詢的結(jié)果中。例27:查詢選修了 C2課程的學(xué)生姓名。S

19、ELECT Sname FROM S WHERE Sno IN (SELECT Sno FROM SC WHERE Cno= 'C2');例28:查詢與“張三”在同一個(gè)系學(xué)習(xí)的學(xué)生學(xué)號(hào)、 姓名和系別。分析：(1)確定“張三”所在的系；(2)查找所有在X系學(xué)習(xí)的學(xué)生。SELECT Sdept FROM S WHERE Sname='張三'；SELECT Sno, Sname, Sdept FROM S WHERE Sdept= 'X'把第一步查詢嵌入到第二步查詢中，用以構(gòu)造第二步查詢的條 件。SELECT Sno , Sname, Sdept FR

20、OM S WHERE Sdept IN (SELECT Sdept FROM S WHERE Sname='張三')；例29:查詢選修課程名為“數(shù)據(jù)庫”的學(xué)生學(xué)號(hào)和姓名。本查詢涉及到學(xué)號(hào)、姓名和課程名三個(gè)屬性，分別存放在S 和C表中，但S和C表沒有直接聯(lián)系，必須通過SC表建立它們二者的聯(lián)系。C - SC - S基本思路：(1)首先在C表中找出“ DB'課程的課程號(hào)Cno；(2)然后在SC表中找出Cno等于第一步給出的 Cno集合中的某 個(gè)元素Cno；FROMS AS S1,S AS S2WHERE S1.Sdept=S2.SdeptANDS2.Sname=，張三，(3)

21、最后在S關(guān)系中選出Sno等于第二步中Sno集合中某個(gè)元 素的元組，取出Sno和Sname送入結(jié)果表列。SELECT Sno, SnameFROM SWHERE Sno IN(SELECT SnoFROM SCWHERE Cno IN(SELECT Cno FROM C WHERE Cname= 'DB');聯(lián)接查詢方式(2)帶有比較運(yùn)算符的子查詢例30:查詢與“張三”在同一個(gè)系學(xué)習(xí)的學(xué)生學(xué)號(hào)、 姓名和系別。SELECT Sno, Sname, SdeptFROM SWHERE Sdept =(SELECT SdeptFROM SWHERE Sname='張三')

22、；例31:查詢選修課程名為“數(shù)據(jù)庫”的學(xué)生學(xué)號(hào)和姓名。SELECT Sno, SnameFROM SWHERE Sno IN(SELECT SnoFROM SCWHERE Cno =(SELECT Cno FROM C WHERE Cname= 'DB');(3)帶有EXISTS謂詞的子查詢(1)帶有EXISTS謂詞的子查詢不返回任何實(shí)際數(shù)據(jù)，它只產(chǎn)生 邏輯值。例32:查詢選修了 C2課程的學(xué)生姓名。1.SELECT SnameFROM SWHERE Sno IN(SELECT SnoFROM SCWHERE Cno= ' C2');2.SELECT SnameFROM SWHERE EXISTS(SELECT *FROM SCWHERE SC.Sno=S.Sno AND Cno= '