轉載自：https://wenku.baidu.com/view/cda288f1b90d6c85ed3ac671.html

去看文庫中的這個比較好，有字型變化，易讀。

下面是為了方便複製，下載的pdf轉的。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL  查詢語句   課堂練習和習題

一、試用SQL查詢語句表達下列對教學資料庫中三個基本表 S、SC 、C 的查詢：

S(sno,sname,SAGE,SSEX)                各欄位表示學號，姓名，年齡，性別

Sc(sno,cno,grade)               各欄位表示學號，課程號，成績C(cno,cname, TEACHER)    各欄位表示課程號，課程名和教師名             其 中 SAGE，          grade     是數值型，其他均為字元型。

要求用 SQL 查詢語句實現如下處理：

1  ．統計有學生選修的課程門數。

2  ．求選修   C4 課程的學生的平均年齡。

3  ． 求  LIU 老師所授課程的每門課程的學生平均成績。

4  ．統計每門課程的學生選修人數     （超過 2 人的課程才統計） 。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列。

5  ． 檢索學號比   WANG    同學大，而年齡比他小的學生姓名。

6  ．檢索姓名以    WANG    打頭的所有學生的姓名和年齡。

7  ．在 SC 中檢索成績為空值的學生學號和課程號。

8  ． 求年齡大於女同學平均年齡的男學生姓名和年齡。

9  ．求年齡大於所有女同學年齡的男學生姓名和年齡。

其中涉及單表題：    1.4.6.7

參考答案：

1 ．統計有學生選修的課程門數。

SELECT COUNT(DISTINCT Cno) FROM SC

2  ．求選修   C4 課程的學生的平均年齡。

SELECT AVG(SAGE )

FROMS WHERE Sno

IN(SELECT Sno FROM SC WHERE Cno='4')

或者，

SELECT AVG(SAGE)

FROM S,SC WHERES.Sno=SC.Sno AND Cno='4'

3  ．求 LIU 老師所授課程的每門課程的學生平均成績。

SELECT AVG(GRADE)

FROM SC join C on SC.Cno=C.Cno WHERE      TEACHER='liu'

GROUP BY c.Cno

另：

SELECTCNAME,AVG(GRADE) FROM SC ,C WHERE SC.Cno=C.Cno AND TEACHER='liu'

GROUP BY c.Cno,cname

4  ．統計每門課程的學生選修人數     （超過 2 人的課程才統計） 。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列。

SELECTDISTINCT Cno,COUNT(Sno) FROM SC

GROUPBY Cno HAVING COUNT(Sno)>2

ORDER BY 2 DESC, Cno ASC

或：

SELECT DISTINCT Cno,COUNT(Sno) as               人數

FROMSC GROUP BY Cno

HAVING COUNT(Sno)>2

ORDER BY    人 數 DESC, Cno ASC

5  ．檢索學號比    WANG    同學大，而年齡比他小的學生姓名。

SELECTX.SNAME FROM S AS X, S AS Y

WHERE Y  .SNAME='WANG' AND X.Sno>Y.Sno AND X.SAGE<Y.SAGE

或：

SELECT SNAME

from s

where sno>(select sno from s where SNAME='WANG') andSAGE<(select sAGE from s where SNAME='WANG')

6  ．檢索姓名以    WANG    打頭的所有學生的姓名和年齡。

SELECT SNAME,SAGE FROM S

WHERE SNAME LIKE 'WANG%'

7 ．在 SC 中檢索成績為空值的學生學號和課程號。

SELECT Sno,CnoFROM SC WHERE GRADE IS NULL

8  ．求年齡大於女同學平均年齡的男學生姓名和年齡。 SELECT SNAME,SAGE

FROM S

WHERE      SSEX='   男'

AND SAGE>(SELECTAVG(SAGE) FROM S WHERE SSEX='女')

9  ．求年齡大於所有女同學年齡的男學生姓名和年齡。

SELECTSNAME,SAGE FROM S AS X

WHERE X.SSEX='      男'AND X.SAGE >ALL (SELECT SAGE                                  FROMS AS Y WHERE

Y.SSEX='   女')

二、試用  SQL  更新語句表達對教學資料庫中三個基本表      S、

SC 、C的各個更新操作：

要求用 SQL 更新語句實現如下處理：

1 ．往基本表   S 中插入一個學生元組（  ‘ S9 ’，‘ WU  ’，18  ）。

2  ．在基本表   S 中檢索每一門課程成績都大於等於       80   分的學生學號、姓名和性別，                                      並把檢索到的值送往另一個已存在的基本表      S1  （ Sno ， SNAME      ， SSEX ）。

3  ．在基本表   SC 中刪除尚無成績的選課元組。

4  ．把 WANG 同學的學習選課和成績全部刪去。

5  ．把選修資料庫原理課不及格的成績全改為空值。

6  ．把低於總平均成績的女同學成績提高         5%   。

7  ．在基本表   SC 中修改  4 號課程的成績，若成績小於等於  75   分時提高     5%      ， 若成績大於 75                 分時提高                4%  （用兩個     UPDATE   語句實現）。

參考答案：

1 ．往基本表   S 中插入一個學生元組（  ‘ S9 ’，‘ WU  ’，18  ）。

INSERT INTO S(Sno,SNAME,SAGE) VALUES('59','WU',18)

2  ．在基本表   S 中檢索每一門課程成績都大於等於       80   分的學生學號、姓名和性別，                                      並把檢索到的值送往另一個已存在的基本表      S1  （ Sno ， SANME      ， SSEX ）。

select Sno,SNAME,SSEX            intos1 from student delete from s1

INSERT INTO S1(Sno,SNAME,SSEX) SELECT Sno,SNAME,SSEX

FROMS WHERE NOT EXISTS(SELECT * FROM SC WHERE GRADE<80 AND S.Sno=SC.Sno)

select * from s1

考慮：以上會有什麼問題？

INSERTINTO S1(Sno,SNAME,SSEX) SELECT Sno,SNAME,SSEX

FROM S WHERE NOT EXISTS(SELECT * FROM SC WHERE

GRADE<80 AND S.Sno=SC.Sno or S.Sno=SC.Sno and gradeis null) and sno in (select sno from sc)

3  ．在基本表   SC 中刪除尚無成績的選課元組。

DELETE FROM SCWHERE GRADE IS NULL

4  ．把 WANG 同學的學習選課和成績全部刪去。

DELETEFROM SC WHERE Sno IN(SELECT Sno FROM S

WHERE SNAME='WANG')

5  ．把選修資料庫原理課不及格的成績全改為空值。 UPDATE SC SET GRADE=NULL

WHERE GRADE<60 AND Cno IN(SELECT Cno FROM C

WHERE CNAME='     資料庫原理 ')

6  ．把低於總平均成績的女同學成績提高         5%   。

UPDATE SC

SETGRADE=GRADE*1.05

WHERE GRADE<(SELECT AVG(GRADE) FROM SC)

AND Sno IN (SELECT Sno FROM SWHERE SSEX='                女')

7  ．在基本表   SC 中修改  4 號課程的成績，若成績小於等於  75   分時提高     5%      ， 若成績大於 75                 分時提高                4%  （用兩個     UPDATE   語句實現）。

UPDATE SC

SETGRADE=GRADE*1.05 WHERE Cno='4' AND GRADE<=75 UPDATE SC

SET GRADE=GRADE*1.04 WHERE Cno='4' AND GRADE>75

三、問題描述：為管理崗位業務培訓資訊，建立                                                                            3 個表 :

S (Sno,SN,SD,SA)   Sno,SN,SD,SA                    分別代表學號、學員姓名、所屬單位、學員年齡

C (Cno,CN )       Cno,CN                      分別代表課程編號、課程名稱

SC ( Sno,Cno,G )    Sno,Cno,G                      分別代表學號、所選修的課程編號、學習成績

要求實現如下   5 個處理：

1.      使用標準   SQL 巢狀語句查詢選修課程名稱為’稅收基礎’的學員學號和姓名

2.      使用標準   SQL  巢狀語句查詢選修課程編號為’        C2 ’的學員姓名和所屬單位

3.      使用標準   SQL  巢狀語句查詢不選修課程編號為’        C5 ’的學員姓名和所屬單位

4.      使用標準   SQL 巢狀語句查詢只選修了一門課程的學員姓名和所屬單位

5.      查詢選修了課程的學員人數

6.     查詢選修課程超過    5 門的學員學號和所屬單位

參考答案：

1 ．使用標準   SQL  巢狀語句查詢選修課程名稱為’稅收基礎’的學員學號和姓名

SELECT SN,SD FROM S WHERE [Sno] IN(

SELECT [Sno] FROM C,SC

WHERE C.[Cno]=SC.[Cno] AND CN=N'                         稅收基礎')

2  ．使用標準   SQL  巢狀語句查詢選修課程編號為’         C2 ’的學員姓名和所屬單位

SELECTS.SN,S.SD FROM S,SC WHERE S.[Sno]=SC.[Sno]

ANDSC.[Cno]='C2'

3  ．使用標準   SQL  巢狀語句查詢不選修課程編號為’         C5 ’的學員姓名和所屬單位

SELECT SN,SD FROM S WHERE [Sno] NOT IN(

SELECT [Sno] FROM SC

WHERE[Cno]='C5')

4  ．使用標準   SQL  巢狀語句查詢只選修了一門課程的學員姓名和所屬單位

SELECT SN,SD FROM S WHERE [Sno] IN(

SELECT [Sno] FROM SC inner JOIN C ON SC.[Cno]=C.[Cno] GROUP BY [Sno]

HAVINGCOUNT(*)=1)

5 ．查詢選修了課程的學員人數

SELECT   學員人數 =COUNT(DISTINCT[Sno]) FROM SC

6  ．查詢選修課程超過     5 門的學員學號和所屬單位

SELECT SN,SD FROM S WHERE [Sno] IN(

SELECT [Sno] FROM SC GROUP BY [Sno]

HAVING COUNT(DISTINCT [Cno])>5)

四、問題描述：已知關係模式：

S(SNO,SNAME     ）                               學生關係。  SNO                      為學號，  SNAME     為姓名

C (CNO,CNAME,TEACHER)                    課程關係。  CNO   為課程號，  CNAME                                                  為課程名，

TEACHER   為任課教師

SC(SNO,CNO,GRADE)                         選課關係。  GRADE 為成績

要求實現如下    5 個處理：

1  ． 找出沒有選修過“李明”老師講授課程的所有學生姓名

2  ．列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績

3  ． 列出既學過“ 1 ”號課程，又學過“     2 ”號課程的所有學生姓名

4  ．  列出“  1 ”號課成績比“  04010002     ”號同學該門課成績高的所有學生的學號

5 ． 列出“ 1 ”號課成績比“    2 ”號課成績高的所有學生的學號及其“    1 ”號課和“ 2 ”號課的成績

參考答案：

1  ．找出沒有選修過“李明”老師講授課程的所有學生姓名

select    sname     from    s where         not                        exists       (select *         from    c,sc        where c.cno=sc.cno        AND       c.teacher=N'             李明 '     AND   s.sno=sc.sno)

參考： select sno,sname from s where sno not in

(select sno from sc,c where c.cno=sc.cno ANDc.teacher=N'liu')

2  ．列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績

SELECT S.SNO,SNAME,AVG_GRADE=AVG(SC.GRADE) FROM S,SC

WHERE GRADE<60 and s.sno=sc.sno GROUP BY s.SNO,s.sname

HAVINGCOUNT(DISTINCT CNO)>=2

或

SELECT S.SNO,S.SNAME,AVG_GRADE=AVG(SC.GRADE) FROM S,SC,(

SELECT SNO FROM SC

WHERE GRADE<60

GROUP BY SNO

HAVINGCOUNT(DISTINCT CNO)>=2

)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME

3 ．列出既學過“     1 ”號課程，又學過“        2 ”號課程的所有學生姓名SELECT S.SNO,S.SNAME

FROM S,sc

where S.SNO=SC.SNO and cno='1' and s.sno in (select S.snofrom S,sc

where S.SNO=SC.SNO and cno='2')

或

SELECT S.SNO,S.SNAME FROM S,(

SELECT SC.SNO FROM SC,C

WHERE SC.CNO=C.CNO

AND C.cno IN('1','2') GROUP BY SNO

HAVINGCOUNT(DISTINCT c.CNO)=2

)SC WHERES.SNO=SC.SNO

4  。列出“   1 ”號課成績比“      04010002    ”號同學該門課成績高的所有學生的學號

SELECT S.SNO,S.SNAME FROM S,SC

WHERESC.CNO='1'and SC.sNO=S.sNO

ANDgrade>(select grade from s,sc

wheres.SNO='04010002'and SC.CNO='1'and SC.sNO=S.sNO)

5  。列出“ 1 ”號課成績比“     2 ”號課成績高的所有學生的學號及其“ 1 ”號課和“ 2 ”號課的成績

SELECT SC1.SNO,[1       號課成績  ]=SC1.GRADE,[2                                       號課成績 ]=SC2.GRADE FROMSC SC1,SC SC2

WHERE SC1.CNO='1' AND SC2.CNO='2'