一、試用SQL查詢語句表達(dá)下列對教學(xué)數(shù)據(jù)庫中三個基本表 S、SC 、C 的查詢：
S(sno,sname,SAGE,SSEX) 各字段表示學(xué)號，姓名，年齡，性別
Sc(sno,cno,grade) 各字段表示學(xué)號，課程號，成績、
C(cno,cname, TEACHER) 各字段表示課程號，課程名和教師名 其 中 SAGE， grade 是數(shù)值型，其他均為字符型。
要求用 SQL 查詢語句實(shí)現(xiàn)如下處理：
1．求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。
SELECTSNAME,SAGE FROM S AS X
WHERE X.SSEX=' 男'AND X.SAGE >ALL (SELECT SAGE FROMS AS Y WHERE
Y.SSEX=' 女')
2.求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。
SELECT SNAME,SAGE
FROM S
WHERE SSEX=' 男'
AND SAGE>(SELECTAVG(SAGE) FROM S WHERE SSEX='女')
3.在 SC 中檢索成績?yōu)榭罩档膶W(xué)生學(xué)號和課程號。
SELECT Sno,CnoFROM SC WHERE GRADE IS NULL
4.檢索姓名以 WANG 打頭的所有學(xué)生的姓名和年齡。
SELECT SNAME,SAGE FROM S
WHERE SNAME LIKE 'WANG%'
5.檢索學(xué)號比 WANG 同學(xué)大，而年齡比他小的學(xué)生姓名。
SELECTX.SNAME FROM S AS X, S AS Y
WHERE Y .SNAME='WANG' AND X.Sno>Y.Sno AND X.SAGE
或：
SELECT SNAME
from s
where sno>(select sno from s where SNAME='WANG') andSAGE<(select sAGE from s where SNAME='WANG')
6.統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù) （超過 2 人的課程才統(tǒng)計(jì)） 。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。
SELECTDISTINCT Cno,COUNT(Sno) FROM SC
GROUPBY Cno HAVING COUNT(Sno)>2
ORDER BY 2 DESC, Cno ASC
或：
SELECT DISTINCT Cno,COUNT(Sno) as 人數(shù)
FROMSC GROUP BY Cno
HAVING COUNT(Sno)>2
ORDER BY 人 數(shù) DESC, Cno ASC
7.求 LIU 老師所授課程的每門課程的學(xué)生平均成績。
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
8.求選修 C4 課程的學(xué)生的平均年齡。
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'
9．統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。
SELECT COUNT(DISTINCT Cno) FROM SC
二、試用 SQL 更新語句表達(dá)對教學(xué)數(shù)據(jù)庫中三個基本表 S、
SC 、C的各個更新操作：
要求用 SQL 更新語句實(shí)現(xiàn)如下處理：
1 ．在基本表 SC 中修改 4 號課程的成績，若成績小于等于 75 分時提高 5% ， 若成績大于 75 分時提高 4% （用兩個 UPDATE 語句實(shí)現(xiàn)）。
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
2 ．把低于總平均成績的女同學(xué)成績提高 5% 。
UPDATE SC
SETGRADE=GRADE*1.05
WHERE GRADE<(SELECT AVG(GRADE) FROM SC)
AND Sno IN (SELECT Sno FROM SWHERE SSEX=' 女')
3 ．把選修數(shù)據(jù)庫原理課不及格的成績?nèi)臑榭罩怠?
UPDATE SC SET GRADE=NULL
WHERE GRADE<60 AND Cno IN(SELECT Cno FROM C
WHERE CNAME=' 數(shù)據(jù)庫原理 ')
4 ．把WANG 同學(xué)的學(xué)習(xí)選課和成績?nèi)縿h去。
DELETEFROM SC WHERE Sno IN(SELECT Sno FROM S
WHERE SNAME='WANG')
5 ．在基本表 SC 中刪除尚無成績的選課元組。
DELETE FROM SCWHERE GRADE IS NULL
6 ．在基本表 S 中檢索每一門課程成績都大于等于 80 分的學(xué)生學(xué)號、姓名和性別， 并把檢索到的值送往另一個已存在的基本表 S1 （ Sno ， SNAME ， 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)
7．往基本表 S 中插入一個學(xué)生元組（ ' S9’，' WU ’，18 ）。
INSERT INTO S(Sno,SNAME,SAGE) VALUES('59','WU',18)
三、問題描述：為管理崗位業(yè)務(wù)培訓(xùn)信息，建立 3 個表 :
S (Sno,SN,SD,SA) Sno,SN,SD,SA 分別代表學(xué)號、學(xué)員姓名、所屬單位、學(xué)員年齡
C (Cno,CN ) Cno,CN 分別代表課程編號、課程名稱
SC ( Sno,Cno,G ) Sno,Cno,G 分別代表學(xué)號、所選修的課程編號、學(xué)習(xí)成績
要求實(shí)現(xiàn)如下 5 個處理：
1.使用標(biāo)準(zhǔn) SQL 嵌套語句查詢選修課程名稱為’稅收基礎(chǔ)’的學(xué)員學(xué)號和姓名
SELECT SN,SD FROM S WHERE [Sno] IN(
SELECT [Sno] FROM C,SC
WHERE C.[Cno]=SC.[Cno] AND CN=N' 稅收基礎(chǔ)')
2.使用標(biāo)準(zhǔn) SQL 嵌套語句查詢選修課程編號為’ C2 ’的學(xué)員姓名和所屬單位
SELECTS.SN,S.SD FROM S,SC WHERE S.[Sno]=SC.[Sno]
ANDSC.[Cno]='C2'
3.使用標(biāo)準(zhǔn) SQL 嵌套語句查詢不選修課程編號為’ C5 ’的學(xué)員姓名和所屬單位
SELECT SN,SD FROM S WHERE [Sno] NOT IN(
SELECT [Sno] FROM SC
WHERE[Cno]='C5')
4.使用標(biāo)準(zhǔn) SQL 嵌套語句查詢只選修了一門課程的學(xué)員姓名和所屬單位
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.查詢選修了課程的學(xué)員人數(shù)
SELECT 學(xué)員人數(shù) =COUNT(DISTINCT[Sno]) FROM SC
6.查詢選修課程超過 5 門的學(xué)員學(xué)號和所屬單位
SELECT SN,SD FROM S WHERE [Sno] IN(
SELECT [Sno] FROM SC GROUP BY [Sno]
HAVING COUNT(DISTINCT [Cno])>5)
四、問題描述：已知關(guān)系模式：
S(SNO,SNAME ） 學(xué)生關(guān)系。 SNO 為學(xué)號， SNAME 為姓名
C (CNO,CNAME,TEACHER) 課程關(guān)系。 CNO 為課程號， CNAME 為課程名，
TEACHER 為任課教師
SC(SNO,CNO,GRADE) 選課關(guān)系。 GRADE 為成績
要求實(shí)現(xiàn)如下 5 個處理：
1 ． 找出沒有選修過“李明”老師講授課程的所有學(xué)生姓名
select sname from swhere 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 ．列出有二門以上（含兩門）不及格課程的學(xué)生姓名及其平均成績
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 ． 列出既學(xué)過“ 1 ”號課程，又學(xué)過“ 2 ”號課程的所有學(xué)生姓名
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 ”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號
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 ”號課成績高的所有學(xué)生的學(xué)號及其“ 1 ”號課和“ 2 ”號課的成績
SELECT SC1.SNO,[1 號課成績 ]=SC1.GRADE,[2 號課成績 ]=SC2.GRADE FROMSC SC1,SC SC2
WHERE SC1.CNO='1' AND SC2.CNO='2'
ANDSC1.SNO=SC2.SNO
ANDSC1.GRADE>SC2.GRADE
四、通配符理解題（請說出下列通配符的含義）
1．LIKE 'Mc%'
將搜索以字母 Mc 開頭的所有字符串（如 McBadden）。
2．LIKE '%inger'
將搜索以字母 inger 結(jié)尾的所有字符串（如 Ringer 、Stringer ）。
3．LIKE '%en%'
將搜索在任何位置包含字母 en 的所有字符串（如 Bennet 、Green、McBadden）。
4．LIKE '_heryl'
將搜索以字母 heryl 結(jié)尾的所有六個字母的名稱（如Cheryl、Sheryl ）。
5．LIKE '[M-Z]inger'
將搜索以字符串 inger 結(jié)尾、以從 M 到 Z 的任何單個字母開頭的所有名稱（如 Ringer ）。
6．LIKE 'M[^c]%'
將搜索以字母 M 開頭，并且第二個字母不是 c 的所有名稱（如 MacFeather ）。
五．問答題
1.請自定義分頁存儲過程！
答：
CREATE PROCEDURE [dbo].[Pro_GetPageData]
@TableName nvarchar(3000), -- 表名
@ReturnFields nvarchar(3000) = '*', -- 需要返回的列
@PageSize int = 10, -- 每頁記錄數(shù)
@PageIndex int = 0, -- 當(dāng)前頁碼
@Where nvarchar(3000) = '', -- 查詢條件
@OrderBy nvarchar(200), -- 排序字段名 最好為唯一主鍵
@OrderType int = 1 -- 排序類型 1:降序 其它為升序
AS
DECLARE @TotalRecord int
DECLARE @TotalPage int
DECLARE @CurrentPageSize int
DECLARE @TotalRecordForPageIndex int
declare @CountSql nvarchar(4000)
if @OrderType = 1
BEGIN
set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' desc,') + ' desc '
END
else
BEGIN
set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' asc,') + ' asc '
END
-- 總記錄
set @CountSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out
SET @TotalPage=(@TotalRecord-1)/@PageSize+1
-- 查詢頁數(shù)不得大于總頁數(shù)
if(@PageIndex > @TotalPage)
set @PageIndex = @TotalPage
SET @CurrentPageSize=(@PageIndex-1)*@PageSize
-- 返回記錄
set @TotalRecordForPageIndex=@PageIndex*@PageSize
exec ('SELECT *
FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS ROWNUM
FROM '+@TableName+ ' ' + @Where +' ) AS TempTable
WHERE TempTable.ROWNUM >
'+@CurrentPageSize)
-- 返回總頁數(shù)和總記錄
SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
2.請列舉數(shù)據(jù)庫層面性能優(yōu)化策略！把你了解到的一種或者多種實(shí)現(xiàn)出來！
答：從Sql語句的優(yōu)化，少使用like “%xxx%”,雙百分號的模糊查詢，盡量使用系統(tǒng)函數(shù)，如果有需要可使用臨時表，添加數(shù)據(jù)庫索引，配置數(shù)據(jù)讀寫分離（詳細(xì)見文檔---數(shù)據(jù)庫讀寫分離配置文檔）
3.說說數(shù)據(jù)庫作業(yè)的實(shí)現(xiàn)！
答：數(shù)據(jù)庫作業(yè)就是從數(shù)據(jù)庫層面的定時任務(wù)，可以設(shè)置執(zhí)行時間，和執(zhí)行邏輯，到時間點(diǎn)就可以自動執(zhí)行任務(wù)。（詳細(xì)另外見文檔—數(shù)據(jù)庫job自動備份數(shù)據(jù)庫）;
4．請盡可能多的描述數(shù)據(jù)庫函數(shù)！
答：SubString(表達(dá)式,起始,長度)：取子串
Right(表達(dá)式,長度) ：右邊取子串
Str(浮點(diǎn)數(shù)[,總長度[,小數(shù)位]])：數(shù)值型轉(zhuǎn)換字符型
LTrim(表達(dá)式)、RTrim(表達(dá)式)：去左、右空格
CharIndex(子串,母串) ：返回子串起始位置
Convert(數(shù)據(jù)類型[(長度)],表達(dá)式[,日期轉(zhuǎn)字符樣式])：表達(dá)式類型轉(zhuǎn)換
Cast(表達(dá)式 As 數(shù)據(jù)類型[(長度)])：表達(dá)式類型轉(zhuǎn)換
Abs(表達(dá)式)：取絕對值
Power(底,指數(shù))：底的指數(shù)次方
Rand([整形數(shù)])；隨機(jī)數(shù)產(chǎn)生器
Round(表達(dá)式,精度)：按精度四舍五入
Sqrt(表達(dá)式)：算術(shù)平方根
GetDate()：當(dāng)前的日期和時間
Day(表達(dá)式)：表達(dá)式的日期值
Month(表達(dá)式)：表達(dá)式的月份值
Year(表達(dá)式)：表達(dá)式的年份值
IsDate(表達(dá)式)：是否合理日期
IsNULL(是否空值,替代值)：若空用指定值代替
IsNumeric(表達(dá)式)：是否為合理的數(shù)值
Exists(子查詢)：檢查結(jié)果集
Avg([Distinct 列名])：取均值
Count([Distinct 列名])：行數(shù)
Max([Distinct 列名])：最大值
Min([Distinct 列名])：最小值
Sum([Distinct 列名])：求和
