SQLServer中幾個常見的測試題

 題目1

問題描述:

為管理崗位業務培訓資訊，建立3個表:

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

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

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

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

--實現代碼:

SELECT S#,SN FROM S

WHERE [S#] IN(

SELECT [S#] FROM C,SC

WHERE C.[C#]=SC.[C#]

AND CN=N'稅收基礎')

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

--實現代碼:

SELECT S.SN,S.SD FROM S,SC

WHERE S.[S#]=SC.[S#]

AND SC.[C#]='C2'

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

--實現代碼:

SELECT SN,SD FROM S

WHERE [S#] NOT IN(

SELECT [S#] FROM SC

WHERE [C#]='C5')

4. 使用標準SQL嵌套語句查詢選修全部課程的學員姓名和所屬單位

--實現代碼:

SELECT SN,SD FROM S

WHERE [S#] IN(

SELECT [S#] FROM SC

RIGHT JOIN

C ON SC.[C#]=C.[C#] GROUP BY [S#]

HAVING COUNT(*)=COUNT([S#]))

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

--實現代碼:

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

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

--實現代碼:

SELECT SN,SD FROM S

WHERE [S#] IN(

SELECT [S#] FROM SC

GROUP BY [S#]

HAVING COUNT(DISTINCT [C#])>5)

 題目2

問題描述:

已知關係模式:

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

C (CNO,CNAME,CTEACHER) 課程關係。CNO 為課程號，CNAME 為課程名，CTEACHER 為任課教師

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

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

--實現代碼:

SELECT SNAME FROM S

WHERE NOT EXISTS(

SELECT * FROM SC,C

WHERE SC.CNO=C.CNO

AND CNAME='李明'

AND SC.SNO=S.SNO)

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

--實現代碼:

SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)

FROM S,SC,(

SELECT SNO

FROM SC

WHERE SCGRADE<60

GROUP BY SNO

HAVING COUNT(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,(

SELECT SC.SNO

FROM SC,C

WHERE SC.CNO=C.CNO

AND C.CNAME IN('1','2')

GROUP BY SNO

HAVING COUNT(DISTINCT CNO)=2

)SC WHERE S.SNO=SC.SNO

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

--實現代碼:

SELECT S.SNO,S.SNAME

FROM S,(

SELECT SC1.SNO

FROM SC SC1,C C1,SC SC2,C C2

WHERE SC1.CNO=C1.CNO AND C1.NAME='1'

AND SC2.CNO=C2.CNO AND C2.NAME='2'

AND SC1.SCGRADE>SC2.SCGRADE

)SC WHERE S.SNO=SC.SNO

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

--實現代碼:

SELECT S.SNO,S.SNAME,SC.[1號課成績],SC.[2號課成績]

FROM S,(

SELECT SC1.SNO,[1號課成績]=SC1.SCGRADE,[2號課成績]=SC2.SCGRADE

FROM SC SC1,C C1,SC SC2,C C2

WHERE SC1.CNO=C1.CNO AND C1.NAME='1'

AND SC2.CNO=C2.CNO AND C2.NAME='2'

AND SC1.SCGRADE>SC2.SCGRADE

 )
SC WHERE S.SNO=SC.SNO