sql題目和答案全解
㈠ 資料庫SQ習題
最佳答案：
習題
一、選擇題
1．關於查詢語句中ORDER BY子句使用正確的是( D )。
A．如果未指定排序欄位，則默認按遞增排序
B．表的欄位都可用於排序
C．如果在SELECT子句中使用了DISTINCT關鍵字，則排序欄位必須出現在查詢結果中
D．聯合查詢不允許使用ORDER BY子句
第2～4題使用7．7節「示例分析」中數據表。
2．使用查詢語句：
SELECT STUDENT.學號,STUDENT.姓名,SUM(分數)
FROM STUDENT,GRADE
WHERE STUDENT.學號=GRADE.學號
GROUP BY STUDENT.學號,STUDENT.姓名
查詢結果是(A )。
A．按學號分類的每個學生所有課程成績的總分 B．按學號分類的每個學生各課程成績
C．全體學生的按各課程分類的成績總分 D．所有學生所有課程成績總分
3．使用查詢語句：
SELECT MAX(分數) AS 最高分
FROM STUDENT,curriculum,GRADE
WHERE STUDENT.學號=GRADE.學號
AND curriculum.課程編號=GRADE.課程編號
AND 課程名稱='資料庫技術及應用'
查詢的結果是( )。
A．87 B．91 C．82 D．90
很遺憾！不知道表中的記錄數據，無法判斷是哪個選項，但是題意是檢索出：選修課程'資料庫技術及應用'的學生中成績最高的。看一下表就知道了。
4．查詢選修了課程編號為「0002」的學生的學號和姓名，以下( A )語句是錯誤的。
A．
SELECT 學號,姓名FROM STUDENT
WHERE 學號=(SELECT 學號FROM GRADE WHERE 課程編號='0002')
B．
SELECT STUDENT.學號,STUDENT.姓名
FROM STUDENT,GRADE
WHERE STUDENT.學號=GRADE.學號AND 課程編號='0002'
C．
SELECT STUDENT.學號，STUDENT.姓名
FROM STUDENT JOIN GRADE ON STUDENT.學號=GRADE.學號
WHERE GRADE.課程編號='0002'
D．
SELECT 學號,姓名FROM STUDENT
WHERE 學號IN (SELECT 學號FROM GRADE WHERE 課程編號='0002')
5．下列關於查詢結果錯誤的是( D )。
A．查詢結果可以顯示在表格中
B．查詢結果可以按文本方式顯示
C．以文本和表格顯示的查詢結果在保存時，其文件格式不同
D．不管以哪種方式查看，查詢結果都會顯示在查詢結果窗口中
6．在T-sql語句中，與表達式「倉庫號NOT IN ('wh1','wh2')」功能相同的表達式是( D )。
A．倉庫號='wh1' AND 倉庫號='wh2' B．倉庫號!='wh1' OR 倉庫號# 'wh2'
C．倉庫號<>'wh1' OR 倉庫號!='wh2' D．倉庫號!='wh1' AND 倉庫號!='wh2'
7．在T-SQL 的SELECT語句中用於實現關系的選擇運算的短語是( C )。
A．FOR B．WHILE C．WHERE D．CONDITION
8．使用SQL語句進行分組檢索時，為了去掉不滿足條件的分組，應當( B )。
A．使用WHERE子句 B．在GROUP BY後面使用HAVING子句
C．先使用WHERE子句，再使用HAVING子句 D．先使用HAVING子句，再使用WHERE子句
二、填空題
1．在查詢語句中，應在__SELECT____子句中指定輸出欄位。
2．如果要使用SELECT語句返回指定條數的記錄，則應使用_TOP__關鍵字來限定輸出欄位。
3．當一個子SELECT的結果作為查詢的條件，即在一個SELECT命令的WHERE子句中出現另一個SELECT命令，這種查詢稱為__嵌套____查詢。
4．連接查詢可分為3種類型：_內連接___、__外連接__和交叉連接。
5．若要把查詢結果存放到一個新建的表中，可使用__into tabale 或into dbf____子句。
三、判斷題
1．在關系資料庫SQL Server中，用於檢索數據的語句是T-SQL的定義語言。 (× )
2．邏輯運算符(AND、NOT、OR)的運算順序是AND→OR→NOT。 ( × )
3．用於WHERE子句的查詢條件表達式可用的比較運算符為：=(等於)、!=或<>(不等於)、>(大於)、>=(大於等於)、<(小於)、<=(小於等於)。 ( √ )
4．SELECT語句的DISTINCT參數表示輸出無重復結果的記錄。 ( √ )
5．如果要使SELECT的查詢結果有序輸出，需要用GROUP BY子句配合。 ( × )
㈡ 鍏充簬SQL鐨勫嚑閬撳皬棰樿﹁В
棰樼洰涓錛氬備笅琛ㄦ墍紺猴紝鐜伴渶瑕佹寜鐓ф敹嬈懼憳緇熻℃敹嬈懼拰閫嬈懼悎璁￠噾棰濄
瀹炵幇緇撴灉闇濡備笅鏄劇ず錛
鍒嗘瀽錛氭兂瑕佺殑緇撴灉錛堣頒負琛˙錛夊拰婧愭暟鎹錛堣頒負琛ˋ錛夌浉姣旓紝鏈夊叡鍚岀殑鍒楋紙鏀舵懼憳錛夛紝涓嶅悓鐨勬槸琛ˋ鐨勯噾棰濇牴鎹鏍囪板拰鏀舵懼憳鍒嗘垚浜嗕袱鍒楋紝鎵浠ヨ繖涓闇奼傚彲浠ョ敤璇璦琛ㄨ堪涓涓嬶細棣栧厛鏍規嵁鏀舵懼憳鍒嗙粍錛坓roup by錛,鐒跺悗褰撴爣璁頒負鈥滄敹鈥濇椂錛岄噾棰濊″叆鏀舵懼悎璁★紙sum錛夛紱褰撴爣璁頒負鈥滈鈥濇椂錛岄噾棰濊″叆閫嬈懼悎璁★紙sum錛夈傚綋??鏃??錛岃繖涓嶅氨鏄瘲QL鐨勬潯浠跺垽鏂鍢涳紵鐩樼偣SQL鐨勬潯浠惰鍙ヤ笉澶氾紝if??else??鍜宑ase??when??then??else??end銆傝繖鏍烽棶棰樺氨榪庡垉鑰岃В浜嗐
瑙ｅ喅鏂規堝備笅錛
with ta as
(select '鏀' as 鏍囪,'100' as 鏀舵懼憳,150 as 閲戦
union
select '鏀','100',375
union
select '閫','100',78
union select '鏀','200',74
)
select 鏀舵懼憳,sum(case when 鏍囪='鏀' then 閲戦 else 0 end) as 鏀舵懼悎璁,
sum(case when 鏍囪='閫' then 閲戦 else 0 end) as 閫嬈懼悎璁 from ta
group by 鏀舵懼憳
棰樼洰浜岋細濡備笅琛ˋ錛堝乏杈癸級鑱屽憳淇℃伅琛錛屽叾涓璉D涓鴻亴鍛樺伐鍙鳳紝name涓鴻亴鍛樺撳悕錛涜〃B錛堝彸杈癸級涓鴻亴鍛樹換鍔″垎閰嶈〃錛屽叾涓璉D涓鴻亴鍛樺伐鍙鳳紙鍜岃〃A涓璉D瀵瑰簲錛夛紝Task涓轟換鍔＄紪鍙楓
鐜伴渶奼傛瘡涓鑱屽憳鐨勪換鍔℃暟銆傜粨鏋滃備笅鏄劇ず錛
鍏跺疄鍘熼樻槸榪欐牱鐨勶細鍙鏈変竴寮犺〃B錛屾眰奼傛瘡涓鑱屽憳鐨勪換鍔℃暟銆傛病鏈夋壘鍒版瘮杈冨ソ鐨勬柟娉曞疄鐜幫紝涓嶅仛璁ㄨ猴紝嬈㈣繋楂樹漢鎸囩偣銆
鍒嗘瀽錛氭ら樼殑闅劇偣鍦ㄤ簬琛˙涓鐨処D澶嶆潅琛ㄧず錛屽叾瀹炶繖鏍鋒湁鎮栦簬鏁版嵁搴撶殑璁捐″師鍒欙紝鐞嗗簲琛ˋ鍜岃〃B鐨処D涓涓瀵瑰簲銆傛棦鐒舵槸棰橈紝鎴戜滑鍙鑳戒粠褰撳墠鐨勬潯浠跺叆鎵嬩簡錛屾敾鐮撮毦鐐圭殑鍏抽敭鍦ㄤ簬鍒ゆ柇A涓璉D鍦˙涓璉D鍑虹幇涓庡惁錛屽傛灉鍑虹幇閭ｄ箞濡備綍緇熻″嚭鐜扮殑嬈℃暟銆傚垽鏂鍑虹幇涓庡惁闇瑕佺敤鍒板嚱鏁癈HARINDEX銆
瑙ｅ喅鏂規堝備笅錛
--鍒涘緩嫻嬭瘯鏁版嵁
WITH TA
AS
(SELECT '1,2' AS ID,'job1' AS task
UNION SELECT '1,2,3','job3'
UNION SELECT '2,3','job2'
UNION SELECT '3,4,5','job4')
,TB AS
(SELECT '1' AS ID,'寮犱笁' as name
UNION SELECT '2','鐜嬩簩'
UNION SELECT '3','鏉庡洓'
UNION SELECT '4','鏉庢槑'
UNION SELECT '5','鐜嬩簲')
SELECT B.ID,B.name,COUNT(1) AS TASKS
FROM TA A,TB B
WHERE CHARINDEX(B.ID,A.ID)>0
GROUP BY B.ID,B.name
order by B.ID
棰樼洰涓夛細鍘熼樺弬瑙佸崥瀹錛歨ttp://www.cnblogs.com/Lumia1020/p/4571301.html
濡備笅琛–ity鎵紺猴紝code涓鴻屾斂鍖哄煙鐮侊紙鍏浣嶆暟瀛楋紝鍓嶄袱浠ｈ〃鐪佺駭錛屼腑闂翠袱浣嶄唬琛ㄥ競綰э紝鏈鍚庝袱浣嶄唬琛ㄥ幙綰э紝涓嶈冭檻xx00xx鎯呭喌錛夛紝city涓哄煄甯傚悕縐幫紝CCode涓鴻ュ煄甯傛墍灞炵殑鐪佺駭鎴栬呭競綰ц屾斂鍖哄煙鐮併
鐜伴渶奼傚備笅緇撴灉錛
鍒嗘瀽錛氬垎鏋愯〃city錛宑ode鐨勫惈涔夊嶮鍒嗘槑鏄撅紝鎵闇瑕佺殑緇撴灉涔熷緢鏄庢樉錛屽傛灉鏄鐪佸氨鏄鏄劇ず鐪佷喚錛涙槸甯傚垯鏄劇ず涓烘墍灞炵渷綰+甯傜駭錛涙槸鍘跨駭鍒欐樉紺轟負鎵灞炵渷綰+鎵灞炲競綰+鍘跨駭銆傝矊似鍙浠ョ敤棰樼洰涓鍒嗘瀽涓鎻愬埌鐨凷QL鏉′歡璇鍙ュ疄鐜幫紝浣嗘槸杞蹇典竴鎯籌紝榪樻槸鏈夊樊鍒錛岃繖閲岄渶瑕佸厛鍒ゆ柇city灞炰簬鐪佺駭錛熷競綰э紵鍘跨駭錛熺劧鍚庡湪瀵瑰簲璧鋒潵鐨勶紝榪欐牱榪樺緱鏈夊弬鐓ц〃錛屽嶆潅浜嗐傚洖鍒扮粨鏋滆〃涓鏉ヨ繘琛屽垎鏋愶紝鍏跺疄鍒ゅ畾city灞炰簬鐪佸競鍘跨殑闂棰樺苟涓嶉毦錛宑ode鐨勫惈涔夊凡緇忚存槑浜嗭紝鍙瑕佽漿鎹㈣〃榪幫細鍦ㄨ〃City涓錛屽綋code鐨勫悗鍥涗綅涓衡0000鈥濇椂錛岃偗瀹氭槸鐪佺駭錛涘綋code鐨勫悗涓や綅涓衡00鈥濓紝騫朵笖鍚庡洓浣嶄笉涓衡0000鈥濇椂錛岃偗瀹氭槸甯傜駭錛涘綋code鍚庝袱浣嶄笉涓衡00鈥濇椂錛屼負鍘跨駭銆傝繖鏍風渷甯傚幙鐨勫垽瀹氬氨涓鐩浜嗙劧浜嗭紝鐒跺悗錛屾牴鎹甯傜駭緙栫爜榪芥湐鎵灞炵殑鐪佺駭錛屽苟寰楀嚭鎵灞炵渷綰+甯傜駭錛屽幙綰ц拷鏈旀墍灞炵殑甯傜駭錛屽緱鍑烘墍灞炵渷綰+鎵灞炲競綰+鎵灞炲幙綰э紝閫氳繃榪愮敤榪欑嶇畝鍗曠殑閫掑綊鎬濇兂錛岃В鍐蟲柟妗堜究璺冪劧綰鎬笂浜嗐
瑙ｅ喅鏂規堝備笅錛
--嫻嬭瘯鏁版嵁
with ta as
(select '110000' as code, '鍖椾含甯' city, '110000' Ccode
union
select N'110200', N'瑗垮煄鍖', N'110200'
union
select N'110300', N'宕囨枃鍖', N'110300'
union
select N'430000', N'婀栧崡鐪', N'430000'
union
select N'430100', N'闀挎矙甯', N'430100'
union
select N'430101', N'鏈涘煄鍘', N'430100')
select * into City from ta
select * from City;
--瑙ｅ喅鏂規
with ta
as(
--鐪佺駭
select code,city,Ccode,city content from City where right(code,4)='0000'),
tb as(
--甯傜駭
select b.code,b.city,b.Ccode,a.city+','+b.city as content from ta a,City b where left(a.Ccode,2)=left(b.Ccode,2)
and right(b.code,2)='00' and right(b.code,4)<>'0000'),
tc as(
select c.code,c.city,c.Ccode,b.content+','+c.city content from tb b,City c where left(b.Ccode,4)=left(c.Ccode,4)
and right(c.code,2)<>'00')
select * from ta
union
select * from tb
union
select * from tc
閫氳繃涓婅堪鍑犻亾灝忛橈紝甯告濆父鏂幫紝娓╂晠浜哠QL鐨勯儴鍒嗙煡璇嗭紝褰撶劧鏂規硶寰堝氾紝鍙樺紡寰堝氾紝濡傞樼洰浜岀粺璁¤〃B涓姣忎釜Task鐨勪漢鏁扮瓑銆備笉瓚充箣澶勶紝嬈㈣繋鍚勪綅鎸囩偣錛
鍏充簬SQL鐨勫嚑閬撳皬棰樿﹁В
鏍囩撅細sql闈㈣瘯棰
㈢ SQL多項選擇題，急求答案，最好有解析
1.答案A,D,E DESC是降序,省略ASC和DESC，默認為升序。
2.答案A,C,D,E 創建視圖時不允許在所用SELECT語句中使用ORDER BY、COMPUTE子句
3.C,D,E 執行存儲過程時可用WITH RECOMPLE選項進行重新編譯;只有當執行存儲過程的語句是批處理中的第一個語句，才可以直接通過名稱來調用存儲過程
4.C,E 使用SQL Server Management Studio不能運行命令行實用程序sqlcmd
5.C,D,F 一個局部變數只能在一個語句批中使用,使用SET語句只能對一個局部變數賦值,剛定義的局部變數的初值為空值
6.B 用戶定義函數可以有輸入參數和返回值
㈣ 鍑犻亾sql棰樻眰絳旀
1錛宻elect * from 琛ㄥ悕 order by 濮撳悕
2錛宻elect * from 琛ㄩ潰 where 璇劇▼=鈥滄暟鎹緇撴瀯鈥 and 濮撳悕 like 鈥%鏉%鈥
3錛宻elect max(鎴愮嘩錛夛紝min(鎴愮嘩)錛宎vg(鎴愮嘩) from 琛ㄥ悕 group by 鎴愮嘩
4,select into 鐢峰︾敓琛 from 琛ㄥ悕 where 鎬у埆=鈥滅敺鈥
5,select * from 琛ㄥ悕 where 鎬у埆=鈥滃コ鈥 and 騫撮緞>20
6,create uniwue clustered index i_name on 琛ㄥ悕錛坰_name錛
7,create nonclustered index i_score on 琛ㄥ悕(score)
8,create view v_nopass as select 瀛﹀彿錛屽撳悕錛岀戠洰錛屾垚緇 where 鎴愮嘩<60
㈤ SQL查詢面試題與答案
SQL查詢面試題與答案
SQL語言是一種資料庫查詢和程序設計語言，用於存取數據以及查詢、更新和管理關系資料庫系統;同時也是資料庫腳本文件的擴展名。下面是我搜集的SQL查詢面試題與答案，歡迎大家閱讀。
SQL查詢面試題與答案一
1.一道SQL語句面試題，關於group by表內容：
2005-05-09 勝
2005-05-09 勝
2005-05-09 負
2005-05-09 負
2005-05-10 勝
2005-05-10 負
2005-05-10 負
如果要生成下列結果, 該如何寫sql語句?
勝 負
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-10','勝')
insert into #tmp values('2005-05-10','負')
insert into #tmp values('2005-05-10','負')
1)select rq, sum(case when shengfu='勝' then 1 else 0 end)'勝',sum(case when shengfu='負' then 1 else 0 end)'負' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='勝'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='負'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 勝,b.b1 負 from
(select col001,count(col001) a1 from temp1 where col002='勝' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='負' group by col001) b
where a.col001=b.col001
2.請教一個面試中遇到的SQL語句的查詢問題
表中有A B C三列,用SQL語句實現：當A列大於B列時選擇A列否則選擇B列，當B列大於C列時選擇B列否則選擇C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面試題：一個日期判斷的sql語句?
請取出tb_send表中日期(SendTime欄位)為當天的所有記錄?(SendTime欄位為datetime型，包含日期與時間)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一張表，裡面有3個欄位：語文，數學，英語。其中有3條記錄分別表示語文70分，數學80分，英語58分，請用一條sql語句查詢出這三條記錄並按以下條件顯示出來(並寫出您的思路)：
大於或等於80表示優秀，大於或等於60表示及格，小於60分表示不及格。
顯示格式：
語文 數學 英語
及格 優秀 不及格
------------------------------------------
select
(case when 語文>=80 then '優秀'
when 語文>=60 then '及格'
else '不及格') as 語文,
(case when 數學>=80 then '優秀'
when 數學>=60 then '及格'
else '不及格') as 數學,
(case when 英語>=80 then '優秀'
when 英語>=60 then '及格'
else '不及格') as 英語,
from table
5.在sqlserver2000中請用sql創建一張用戶臨時表和系統臨時表，裡麵包含兩個欄位ID和IDValues,類型都是int型，並解釋下兩者的區別?
------------------------------------------
用戶臨時表:create table #xx(ID int, IDValues int)
系統臨時表:create table ##xx(ID int, IDValues int)
區別:
用戶臨時表只對創建這個表的用戶的Session可見,對其他進程是不可見的.
當創建它的進程消失時這個臨時表就自動刪除.
全局臨時表對整個SQL Server實例都可見,但是所有訪問它的Session都消失的時候,它也自動刪除.
6.sqlserver2000是一種大型資料庫，他的`存儲容量只受存儲介質的限制，請問它是通過什麼方式實現這種無限容量機制的。
------------------------------------------
它的所有數據都存儲在數據文件中(*.dbf),所以只要文件夠大,SQL Server的存儲容量是可以擴大的.
SQL Server 2000 資料庫有三種類型的文件：
主要數據文件
主要數據文件是資料庫的起點，指向資料庫中文件的其它部分。每個資料庫都有一個主要數據文件。主要數據文件的推薦文件擴展名是 .mdf。
次要數據文件
次要數據文件包含除主要數據文件外的所有數據文件。有些資料庫可能沒有次要數據文件，而有些資料庫則有多個次要數據文件。次要數據文件的推薦文件擴展名是 .ndf。
日誌文件
日誌文件包含恢復資料庫所需的所有日誌信息。每個資料庫必須至少有一個日誌文件，但可以不止一個。日誌文件的推薦文件擴展名是 .ldf。
7.請用一個sql語句得出結果
從table1,table2中取出如table3所列格式數據，注意提供的數據及結果不準確，只是作為一個格式向大家請教。
如使用存儲過程也可以。
table1
月份mon 部門dep 業績yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部門dep 部門名稱dname
--------------------------------
01 國內業務一部
02 國內業務二部
03 國內業務三部
04 國際業務部
table3 (result)
部門dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部門名稱dname,b.業績yj as '一月份',c.業績yj as '二月份',d.業績yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部門dep = b.部門dep and b.月份mon = '一月份' and
a.部門dep = c.部門dep and c.月份mon = '二月份' and
a.部門dep = d.部門dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.華為一道面試題
一個表中的Id有多個記錄，把所有這個id的記錄查出來，並顯示共有多少條記錄數。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
SQL查詢面試題與答案二
1、查詢不同老師所教不同課程平均分從高到低顯示
SELECT max(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ID,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
2、查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理(001)，馬克思(002)，UML (003)，資料庫(004)
[學生ID],[學生姓名],企業管理,馬克思,UML,資料庫,平均成績
SELECT DISTINCT top 3
SC.S# As 學生學號,
Student.Sname AS 學生姓名 ,
T1.score AS 企業管理,
T2.score AS 馬克思,
T3.score AS UML,
T4.score AS 資料庫,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 總分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
3、統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as 課程ID, Cname as 課程名稱
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
4、查詢學生平均成績及其名次
SELECT 1+(SELECT COUNT( distinct 平均成績)
FROM (SELECT S#,AVG(score) AS 平均成績
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成績 > T2.平均成績) as 名次,
S# as 學生學號,平均成績
FROM (SELECT S#,AVG(score) 平均成績
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成績 desc;
5、查詢各科成績前三名的記錄:(不考慮成績並列情況)
SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
6、查詢每門課程被選修的學生數
select c#,count(S#) from sc group by C#;
7、查詢出只選修了一門課程的全部學生的學號和姓名
select SC.S#,Student.Sname,count(C#) AS 選課數
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名;
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
9、查詢所有課程成績小於60分的同學的學號、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查詢沒有學全所有課的同學的學號、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名;
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名;
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平');
14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名;
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、刪除學習“葉平”老師課的SC表記錄;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';
16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學學號、2、
號課的平均成績;
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成績從高到低顯示所有學生的“資料庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示： 學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分
SELECT S# as 學生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 資料庫
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語
,COUNT(*) AS 有效課程數, AVG(t.score) AS 平均成績
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
SELECT L.C# As 課程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成績從低到高和及格率的百分數從高到低順序
SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 企業管理(001)，馬克思(002)，OO&UML (003)，資料庫(004)
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企業管理及格百分數
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 馬克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 馬克思及格百分數
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分數
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 資料庫平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 資料庫及格百分數
FROM SC
;