1. 求解 sql server 2000 考試試題
一、填空題（4小題、每空1分、共5分）
1、 在SELECT查詢語句中用_distinct____關鍵字來刪除重復記錄。
2、 DATEDIFF（YEAR,』2004-5-6』,』2008-9-7』）這個表達式的值__4__。
3、在SQL Server中，根據索引對數據表中記錄順序的影響，索引可以分為____asc____和___desc_____。
4、____檢查___約束通過檢查一個或多個欄位的輸入值是否符合設定的檢查條件來強制數據的完整性。
2. 求幫解這幾道SQL的題
1.創建數據表[宿舍表]代碼；
宿舍表(宿舍號char(6)，宿舍電話)
要求使用：主鍵(宿舍號)、宿舍電話：以633開頭的7位電話號碼
createtable宿舍表
(宿舍號char(6)primarykey,
宿舍電話varchar(7)check(宿舍電話like'633%'))
2.將下列宿舍信息添加到宿舍表的代碼
宿舍號宿舍電話
1016331157
1026331777
insertinto宿舍表values('101','6331157')
insertinto宿舍表values('102','6331777')
修改宿舍號為101的宿舍電話：6331158
update宿舍表set宿舍電話='6331158'wherertrim(宿舍號)='101'
刪除宿舍號為102的宿舍信息
deletefrom宿舍表wherertrim(宿舍號)='102'
3.創建視圖[同學表視圖]代碼；
同學表視圖(學號,姓名,性別,年齡,民族,身份證號,宿舍號,宿舍電話)
createview同學表視圖
as
select同學表.學號,同學表.姓名,同學表.性別,同學表.年齡,同學表.民族,同學表.身份證號,同學表.宿舍號,同學表.宿舍電話
from同學表,宿舍表where同學表.宿舍號=宿舍表.宿舍號
4.從同學表視圖中查詢姓張的女同學的姓名、性別、宿舍電話。
select姓名,性別,宿舍電話
from同學表視圖where姓名like'張%'and性別='女'
5.從同學表中查詢女同學的最大年齡、最小年齡、平均年齡。
selectmax(年齡)as最大年齡,min(年齡)as最小年齡,avg(年齡)as平均年齡
from同學表where性別='女'
少年，這個其實沒什麼太難的，多看看書就會，希望你能順利通過考試
3. SQL的題目，最基礎的～等～～
1. 從職工關系中檢索所有工資值。
答：select 工資 from 職工表
2. 檢索倉庫關系中的所有記錄
答：select * from 倉庫表
3. 檢索工資多於1230元的職工號
答：select 職工號 from 職工表 where 工資>1230
4.檢索哪些倉庫有工資多於1210元的職工。
答：select distinct 倉庫號 from 職工表 where 工資>1210
5. 給出在倉庫「wh1」或「wh2」工作，並且工資少於1250元的職工號。
答：select 職工號 from 職工表 where 工資<1250 ;
and (倉庫號="wh1" or 倉庫號="wh2")
注意：邏輯運算符的優先順序從高到低依次為not、and、or。運算符的優先順序:括弧 算術運算 關系運算 邏輯運算.
說明：前面的幾個例子在from之後只指定了一張表，也就是說這些檢索只基於一張表。如果有where子句，系統首先根據指定的條件依次檢驗關系中的每條記錄，然後選出滿足條件的記錄（相當於關系的選擇操作），並顯示select子句中指定屬性的值（相當於關系的投影操作）。
6. 找出工資多於1230元的職工號和他們所在的城市。
答：select 職工表.職工號, 倉庫表.城市 from 職工表,倉庫表 ;
where 職工表.倉庫號=倉庫表.倉庫號 and 工資>1230
7. 找出工作在面積大於400的倉庫的職工號以及這些職工工作所在的城市。
答:select 職工表.職工號, 倉庫表.城市, 倉庫表.面積 ;
from 職工表,倉庫表 where 職工表.倉庫號=倉庫表.倉庫號 ;
and 倉庫表.面積>400
說明:以上兩題為簡單的聯接查詢.
8. 哪些城市至少有一個倉庫的職工工資為1250元
答:
方法一:
Select 倉庫表.城市 from 職工表,倉庫表 where 職工表.倉庫號=倉庫表.倉庫號 and 職工表.工資=1250
方法二:
select 倉庫號 from 職工表 where 工資=1250 into dbf abc.dbf
select 倉庫表.城市 from 倉庫表,abc where 倉庫表.倉庫號=abc.倉庫號
方法三:
select 城市 from 倉庫表 where 倉庫號 in (select 倉庫號 from 職工表 where 工資=1250)
說明: 這屬於嵌套查詢. 這類查詢所要求的結果出自一個關系,但相關的條件卻涉及多個關系.
可以看到,方法三的命令中含有兩個select-from-where查詢塊,即內層查詢塊和外層查詢塊,內層查詢塊檢索到的倉庫值是wh1和wh2,這樣就可以寫出等價命令:
select 城市 from 倉庫表 where 倉庫號 in ("wh1","wh2")
或者
select 城市 from 倉庫表 where 倉庫號="wh1" or 倉庫號="wh2"
9. 查詢所有職工的工資都多於1210元的倉庫的信息。
答:
方法一：
select 倉庫號,min(工資) as 工資 from 職工表 group by 倉庫號 into dbf 倉庫min工資.dbf
select 倉庫表.* from 倉庫表,倉庫min工資 where 倉庫表.倉庫號=倉庫min工資.倉庫號 and 倉庫min工資.工資>1210
方法二：
select * from 倉庫表 where 倉庫表.倉庫號 not in (select 倉庫號 from 職工表 where 工資<=1210 ) and 倉庫表.倉庫號 in (select 倉庫號 from 職工表)
（錯誤方法）
select * from 倉庫表 where 倉庫表.倉庫號 not in (select 倉庫號 from 職工表 where 工資<=1210 )
注意:上述檢索結果錯誤,會將沒有職工的倉庫檢索出來.如果要求排除那些還沒有職工的倉庫,檢索要求可以敘述為:檢索所有職工的工資都大於1210元的倉庫的信息,並且該倉庫至少要有一名職工.
（錯誤方法）
select * from 倉庫表 where 倉庫表.倉庫號 in (select 倉庫號 from 職工表 where 工資>1210 )
注意：上述查詢結果錯誤。它會查出倉庫號為wh1的信息，但wh1的職工工資並不都大於1210。
10. 找出和職工e4掙同樣工資的所有職工。
答: Select 職工號 from 職工表 where 工資 in (select 工資 from 職工表 where 職工號="e4")
說明:7、9、10題都是基於多個關系的查詢,這類查詢所要求的結果出自一個關系,但相關的條件卻涉及多個關系.我們稱之為嵌套查詢。嵌套查詢優選含有兩個select-from-where查詢塊的查詢結構。
11. 檢索出工資在1220元到1240元范圍內的職工信息。
答:select * from 職工表 where 工資 between 1220 and 1240
說明: "工資 between 1220 and 1240"等價於"工資>=1220 and 工資<=1240"
如果要求查詢工資不在1220元到1240元范圍內的職工信息
說明: select * from 職工表 where 工資 not between 1220 and 1240
12. 從供應商關系中檢索出全部公司的信息，不要工廠或其他供應商的信息。
Select * from 供應商表 where "公司" $ 供應商名
13. 找出不在北京的全部供應商信息。
Select * from 供應商表 where 地址!="北京"
或者
Select * from 供應商表 where not(地址="北京")
14. 按職工的工資值升序檢索出全部職工信息。
答:select * from 職工表 order by 工資
如果需要將結果按降序排列,只要加上desc
select * from 職工表 order by 工資 desc
說明:使用SQL SELECT可以將查詢結果排序,排序的短語是order by ,具體格式如下:
order by order_item [ASC|DESC] [,order_item [ASC|DESC]……]
15. 先按倉庫號排序，再按工資排序並輸出全部職工信息。
答：Select * from 職工表 order by 倉庫號,工資
16. 找出供應商所在地的數目。
答:select count(distinct 地址) from 供應商表
注意:除非對表中的記錄數進行計數,一般count函數應該使用distinct
比如: select count(*) from 供應商表
查詢結果是供應商表中的記錄數.
說明:可用於計算檢索的函數有:count——計數 sum——求和
avg——計算平均值 max——求最大值 min——求最小值
17. 求支付的工資總數
答：select sum(工資) from 職工表
18. 求北京和上海的倉庫職工的工資總和
答: select sum(工資) from 職工表,倉庫表 where 職工表.倉庫號=倉庫表.倉庫號 and (城市="北京" or 城市="上海")
方法二:
select sum(工資) from 職工表 where 倉庫號 in (select 倉庫號 from 倉庫表 where 城市="北京" or 城市="上海")
19. 求所有職工的工資都多於1210元的倉庫的平均面積
答：Select avg(面積) from 倉庫表 where 倉庫號 not in(select 倉庫號 from 職工表 where 工資<=1210) and 倉庫號 in(select 倉庫號 from 職工表)
20. 求在wh2倉庫工作的職工的最高工資值
答:select max(工資) from 職工表 where 倉庫號="wh2"
21. 求每個倉庫的職工的平均工資
答:select 倉庫號,avg(工資) from 職工表 group by 倉庫號
說明:可以利用group by 子句進行分組計算查詢.group by短語的格式如下:group by groupcolumn[,groupcolumn……][having filtercondition]
可以按一列或多列分組,還可以用having 進一步限定分組的條件.
注意：where /group by等子句都不能放在from子句之前.
22. 求至少有兩個職工的每個倉庫的平均工資。
答: select 倉庫號,count(*),avg(工資) from 職工表 group by 倉庫號 having count(*)>=2
說明:having子句總是跟在group by 子句之後,不可以單獨使用.having子句用於限定分組.
23. 找出尚未確定供應商的訂購單
答：select * from 訂購單表 where 供應商號 is null
24. 列出已經確定了供應商的訂購單信息
答：select * from 訂購單表 where 供應商號 is not null
4. sql 期末考試題
1、建立一個「學生」表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、 所在系Sdept五個屬性組成，其中學號屬性不能為空，並且其值是唯一的。
其值是唯一的，並不是說是主鍵，應該用關鍵字UNIQUE
CREATE TABLE Student(
Sno SMALLINT NOT NULL UNIQUE,
Sname CHAR(8),
Ssex CHAR(1),
Sage SMALLINT,
Sdept CHAR(20))
2、查詢全體學生的詳細記錄
SELECT * FROM Student
3、查詢年齡在20至23歲之間的學生的姓名、系別、和年齡
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23
4、計算1號課程的學生平均成績
SELECT AVG(Grade) FROM SC WHERE Cno = 1
5、將計算機科學系全體學生的成績置零
UPDATE SC
SET grade = 0
WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = '計算機科學系')
5. SQL50題及答案
習題來源於網路，sql語句是自己的答案，部分有參考。歡迎指正及探討。
1. 查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數
1.1 查詢同時存在" 01 "課程和" 02 "課程的情況
1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null)
null提示：使用left join
1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況
2. 查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
3. 查詢在 SC 表存在成績的學生信息
4. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null)
4.1 查有成績的學生信息
5. 查詢「李」姓老師的數量
6. 查詢學過「張三」老師授課的同學的信息
7. 查詢沒有學全所有課程的同學的信息
8. 查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息
9. 查詢和" 01 "號的同學學習的課程 完全相同的其他同學的信息
10. 查詢沒學過"張三"老師講授的任一門課程的學生姓名
11. 查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
12. 檢索" 01 "課程分數小於 60，按分數降序排列的學生信息
13. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
14. 查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率（及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90
14.1要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
15. 按各科成績進行排序，並顯示排名， Score 重復時保留名次空缺
15.1 按各科成績進行排序，並顯示排名， Score 重復時合並名次
16. 查詢學生的總成績，並進行排名，總分重復時保留名次空缺
16.1 查詢學生的總成績，並進行排名，總分重復時不保留名次空缺
17. 統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比
18. 查詢各科成績前三名的記錄
19. 查詢每門課程被選修的學生數
20. 查詢出只選修兩門課程的學生學號和姓名
21. 查詢男生、女生人數
22. 查詢名字中含有「風」字的學生信息
23. 查詢同名同性學生名單，並統計同名人數
24. 查詢 1990 年出生的學生名單
25. 查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
26. 查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績
27. 查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數
28. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）
29. 查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數
30. 查詢不及格的課程
31. 查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名
32. 求每門課程的學生人數
33. 成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
34. 成績有重復的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
35. 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
36. 查詢每門功成績最好的前兩名
37. 統計每門課程的學生選修人數（超過 5 人的課程才統計）
38. 檢索至少選修兩門課程的學生學號
39. 查詢選修了全部課程的學生信息
40. 查詢各學生的年齡，只按年份來算
41. 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
42. 查詢本周過生日的學生
43. 查詢下周過生日的學生
44. 查詢本月過生日的學生
45. 查詢下月過生日的學生
1.1 查詢同時存在" 01 "課程和" 02 "課程的情況
1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況
4.1 查有成績的學生信息
15.1 按各科成績進行排序，並顯示排名， Score 重復時合並名次
16.1 查詢學生的總成績，並進行排名，總分重復時不保留名次空缺
6. sql基礎選擇題
d;
d;
d;
a;
b;
7. SQL經典50題題解
本篇文章主要是對SQL經典50題進行詳細解析。
解析包含：1、解題思路，2、考核知識點，3、答案；
首先，表結構用腦圖輸出出來，如下所示：
先進行數據准備，建表以及插入數據。
1.查詢"01"課程比"02"課程成績高的學生的信息及課程分數
解題思路：
第一步：關鍵詞有「課程編號」、「課程成績」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：給出學生信息及課程分數，通過主鍵sid關聯學生表和課程表。
第三步：比較同一個學生不同課程的成績，再關聯一次課程表，利用sid、cid進行關聯。
第四步：根據題目，用where比較分數篩選結果。
考核知識點： join，where
答案：
1.1 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
解題思路：
第一步：關鍵詞有「課程編號」、「學生編號」，鎖定使用表：成績表。
第二步：分別查詢出存在" 01 "課程的學生和存在" 02 "課程的學生。
第三步：對兩個子查詢進行關聯，用sid進行左聯接。
考核知識點： where, 子查詢，left join
答案：
1.2 查詢同時存在01和02課程的情況
解題思路： 同1.1，把left join改為join
考核知識點： where, 子查詢，join
答案：
1.3 查詢選擇了02課程但沒有01課程的情況
解題思路： 類似1.1，把left join改為right join
考核知識點： where, 子查詢，right join
答案：
小結： 上面的題主要考察join、left join、right join。
2.查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
解題思路：
第一步：關鍵詞有「平均成績」、「學生編號」、「學生姓名」，鎖定使用表：成績表、學生表。
第二步：給出學生信息及課程分數，通過主鍵sid關聯學生表和課程表。。
第三步：根據題目，用group by聚合計算出平均成績，然後篩選出大於等於60分的學生。
考核知識點： join、group by、avg()
答案：
3.查詢在 SC 表存在成績的學生信息
解題思路：
第一步：關鍵詞有「SC」、「學生信息」，鎖定使用表：成績表、學生表。
第二步：用EXISTS判斷在SC表存在成績的學生信息
考核知識點： EXISTS語句
答案：
4.查詢所有同學的學生編號、學生姓名、選課總數、所有課程的成績總和
解題思路：
第一步：關鍵詞有「學生編號」、「學生姓名」、「選課總數」、「課程成績」，鎖定使用表：成績表、學生表。
第二步：通過主鍵sid關聯學生表成績表，得到學生信息、學生成績的寬表
第三步：根據題目，用group by聚合計算選課總數和總成績
考核知識點： left join、group by、count()、sum()、ifnull()
答案：
5.查詢「李」姓老師的數量
解題思路：
第一步：關鍵詞有「老師的數量」，鎖定使用表：教師表。
第二步：先篩選出「李」姓老師，再匯總統計「李」姓老師的數量
考核知識點： like、where、%、count()
答案：
6.查詢學過「張三」老師授課的同學的信息
解題思路：
第一步：關鍵詞有「老師」、「學生信息」，鎖定使用表：教師表、學生表、成績表、課程表。
第二步：通過sid關聯學生表、成績表，再通過cid關聯課程表，最後通過tid關聯教師表。
第三步：用where篩選出「張三」老師授課的同學的信息。
考核知識點： 多重連接join
答案：
7.查詢沒有學全所有課程的同學的信息。
解題思路：
第一步：關鍵詞有「課程」、「學生信息」，鎖定使用表：學生表、成績表、課程表。
第二步：先統計學生的課程數量，再篩選出小於所有課程數量的學生。
考核知識點： left join、group by、count()
答案：
8.查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息。
解題思路：
第一步：關鍵詞有「課程」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：先查詢學號「01」的同學學習的課程。
第三步：通過sid關聯學生表和成績表，獲取所有學生信息、課程信息。
第四步：用EXISTS篩選出至少1門課相同的同學信息。
考核知識點： left join、子查詢、EXISTS
答案：
9.查詢和" 01 "號的同學學習的課程完全相同的其他同學的信息
解題思路：
第一步：關鍵詞有「課程」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：用「01」號的同學學習的課程左關聯學生課程表，篩選出關鏈課程數一致的其他同學的sid
第三步：通過sid關聯學生表和成績表，獲取完整的學生信息。
考核知識點： left join、子查詢、group by
答案：
10.查詢沒學過"張三"老師講授的任一門課程的學生姓名
解題思路：
第一步：關鍵詞有「老師」、「學生姓名」，鎖定使用表：學生表、成績表、課程表、教師表。
第二步：反向求解。先查詢至少學過「張三」老師講授的課程的學生sid
第三步：用NOT EXISTS篩選出不在第二步查詢結果的學生信息,。
考核知識點： 多重連接join、NOT EXISTS、子查詢
答案：
11.查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
解題思路：
第一步：關鍵詞有「姓名」、「平均成績」，鎖定使用表：學生表、成績表。
第二步：通過sid關聯學生表和成績表，得到學生成績信息寬表
第三步：用group by聚合統計，having對聚合的結果進行篩選。
考核知識點： join、group by、having、case when語句、avg()
答案：
12.檢索" 01 "課程分數小於 60，按分數降序排列的學生信息
解題思路：
第一步：關鍵詞有「課程分數」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：通過sid關聯學生表和成績表，得到學生成績信息寬表
第三步：用where篩選" 01 "課程分數小於 60的記錄，並按照分數降序排列。
考核知識點： join、where、order by
答案：
13.按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
解題思路：
第一步：關鍵詞有「平均成績」，鎖定使用表：成績表。
第二步：用group by將學生課程的成績由行轉換為列並計算平均成績。
第三步：按照平均成績降序顯示學生的所有課程的成績以及平均成績。
考核知識點： join
答案：
14.查詢各科成績最高分、最低分和平均分,以如下形式顯示：
以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，
優良率，優秀率
及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90
要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
解題思路：
第一步：關鍵詞有「課程name」、「最高分」，鎖定使用表：課程表、成績表。
第二步：用group by聚合計算課程最高分、最低分、平均分。
第三步：用case語句判斷及格、中等、優良、優秀，並結合group by計算。
考核知識點： join、group by、max()、min()、avg()、sum()、case when語句
答案：
15.按各科成績進行排序，並顯示排名， Score 重復時保留名次空缺
解題思路：
第一步：關鍵詞有「各科成績」，鎖定使用表：成績表。
第二步：用rank()排名。
考核知識點： rank() over(partition by)
答案：
15.1 按各科成績進行行排序，並顯示排名， Score 重復時合並名次
解題思路：
第一步：關鍵詞有「各科成績」，鎖定使用表：成績表。
第二步：用dense_rank()排名。
考核知識點： dense_rank() over(partition by)
答案：
16.查詢學生的總成績，並進行排名，總分重復時保留名次空缺
解題思路：
第一步：關鍵詞有「總成績」，鎖定使用表：成績表。
第二步：用group by統計學生的總成績。
第三步：用left join自關聯進行排名。
考核知識點： group by、 left join
答案：
16.1 查詢學生的總成績，並進行排名，總分重復時不保留名次空缺
解題思路：
第一步：關鍵詞有「總成績」，鎖定使用表：成績表。
第二步：用group by統計學生的總成績。
第三步：用變數進行排名。
考核知識點： group by、變數
答案：
17. 統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比
解題思路：
第一步：關鍵詞有「各科成績」、「課程名稱」，鎖定使用表：成績表、課程表。
第二步：通過cid關聯課程表和成績表，得到課程、成績信息寬表。
第三步：用group by聚合統計各分段的人數及百分比。
考核知識點： join、group by、case when條件語句
答案：
18.查詢各科成績前三名的記錄
解題思路：
第一步：關鍵詞有「各科成績」，鎖定使用表：成績表。
第二步：篩選出各科比當前成績高的人數小於3的學生記為各科的前三名。
考核知識點： 子查詢
答案：
19.查詢每門課程被選修的學生數
解題思路：
第一步：關鍵詞有「每門課程」、「學生數」，鎖定使用表：課程表、成績表。
第二步：用left join關聯課程表和成績表，再用group by分組匯總各科的學生數。
考核知識點： left join、group by
答案：
20.查詢出只選修兩門課程的學生學號和姓名
解題思路：
第一步：關鍵詞有「選修課程」、「學生姓名」，鎖定使用表：學生表、成績表。
第二步：用join關聯學生表和成績表，再用group by分組匯總每個學生的選修課程數，最後用having對分組匯總結果篩選出選修兩門課程的學生。
考核知識點： join、group by、having
答案：
21. 查詢男生、女生人數
解題思路：
第一步：關鍵詞有「男生、女生」，鎖定使用表：學生表。
第二步：通過ssex學生表用group by分組匯總男生、女生人數。
考核知識點： group by
答案：
22. 查詢名字中含有「風」字的學生信息
解題思路：
第一步：關鍵詞有「學生信息」，鎖定使用表：學生表。
第二步：用like匹配姓名中含有風」字的學生。
考核知識點： like、%
答案：
23查詢同名同性學生名單，並統計同名人數
解題思路：
第一步：關鍵詞有「學生名單」，鎖定使用表：學生表。
第二步：使用group by，匯總同名同性人數，再用having篩選出大於1的記錄
考核知識點： group by、having
答案：
24.查詢 1990 年出生的學生名單
解題思路：
第一步：關鍵詞有「學生名單」，鎖定使用表：學生表。
第二步：用where篩選出1990年出生的學生名單
考核知識點： where、year
答案：
25.查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編
號升序排列。
解題思路：
第一步：關鍵詞有「平均成績」，鎖定使用表：成績表。
第二步：用group by分組計算各科平均成績，再用order by完成多列排序
考核知識點： group by、order by
答案：
26.查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績
解題思路：
第一步：關鍵詞有「平均成績」、「學生姓名」，鎖定使用表：成績表、學生表。
第二步：用join關聯學生表和成績表
第三步：用group by分組匯總計算每個學生的平均成績，再用having篩選平均成績>=85的記錄
考核知識點： join、group by、having
答案：
27.查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數
解題思路：
第一步：關鍵詞有「課程名稱」、「分數」、「學生姓名」，鎖定使用表：課程表、成績表、學生表。
第二步：用join關聯學生表、成績表、課程表，再用where篩選
考核知識點： 多重join、where
答案：
28. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）
解題思路：
第一步：關鍵詞有「所有學生」、「分數」，鎖定使用表：學生表、成績表
第二步：用left join關聯學生表、成績表
考核知識點： left join
答案：
29.查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數
解題思路：
第一步：關鍵詞有「課程成績」、「姓名」、「課程名稱」，鎖定使用表：學生表、成績表、課程表
第二步：用join關聯學生表、成績表、課程表，再篩選出課程成績在70分以上的。
考核知識點： 多重join
答案：
30.查詢不及格的課程
解題思路：
第一步：關鍵詞有「不及格的課程」，鎖定使用表：成績表、課程表
第二步：關聯課程表和成績表，再條件篩選出不及格的課程信息。
考核知識點： join、where、去重
答案：
31.查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名
解題思路：
第一步：關鍵詞有「課程編號」、「課程成績」、「姓名」，鎖定使用表：成績表、學生表
第二步：關聯成績表和學生表，再條件篩選出結果。
考核知識點： join、where
答案：
32.求每門課程的學生人數
解題思路：
第一步：關鍵詞有「課程」、「學生人數」，鎖定使用表：成績表
第二步：用group by分組匯總各科的學生人數。
考核知識點： group by
答案：
33.成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
解題思路：
第一步：關鍵詞有「成績」、「「張三」老師」、「學生信息」，鎖定使用表：成績表、課程表、學生表、教師表
第二步：關聯所有表，篩選出選修「張三」老師所授課程的學生。
第三步：因為成績不重復，對學生成績由高到低排序，篩選出第一行記錄。
考核知識點： 多重join、order by、limit
答案：
34.成績有重復的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生
信息及其成績
解題思路：
第一步：關鍵詞有「成績」、「「張三」老師」、「學生信息」，鎖定使用表：成績表、課程表、學生表、教師表
第二步：關聯所有表，篩選出選修「張三」老師所授課程的學生。
第三步：因為成績有重復，先求出最高成績，再匹配最高成績對應的學生信息。
考核知識點： 多重join、max()
答案：
35.查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
解題思路：
第一步：關鍵詞有「成績」，鎖定使用表：成績表
第二步：自聯接，篩選出不同課程成績相同的記錄
考核知識點： 自聯接join
答案：
36. 查詢每門成績最好的前兩名
解題思路：
第一步：關鍵詞有「成績」，鎖定使用表：成績表
第二步：自聯接，篩選出各科低於自身成績的人數為2的。
考核知識點： left join
答案：
37. 統計每門課程的學生選修人數（超過 5 人的課程才統計）。
解題思路：
第一步：關鍵詞有「選修人數」，鎖定使用表：成績表
第二步：先用group by分組匯總各科的選修人數，再條件篩選出超過5人的課程。
考核知識點： group by、having
答案：
38.檢索至少選修兩門課程的學生學號
解題思路：
第一步：關鍵詞有「兩門課程」，鎖定使用表：成績表
第二步：先用group by分組匯總每個學生的選修課程數，再用having篩選出至少2門課程的學生學號
考核知識點： group by、having
答案：
39.查詢選修了全部課程的學生信息
解題思路：
第一步：關鍵詞有「全部課程」、「學生信息」，鎖定使用表：成績表、課程表、學生表
第二步：關聯學生表和成績表，再用group by分組統計每個學生的選修課程數
第三步：最後用having篩選出等於全部課程數的學生信息。
考核知識點： join、 group by、having、子查詢
答案：
40.查詢各學生的年齡，只按年份來算
解題思路：
第一步：關鍵詞有「學生的年齡」，鎖定使用表：學生表
第二步：用year和now來統計
考核知識點： year、now
答案：
41. 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
解題思路：
第一步：關鍵詞有「出生日期」，鎖定使用表：學生表
第二步：用timestampdiff()統計年齡
考核知識點： timestampdiff()
答案：
42.查詢本周過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用week函數
考核知識點： week()
答案：
43. 查詢下周過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用week函數
考核知識點： week()
答案：
44.查詢本月過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用month函數
考核知識點： month()
答案：
45.查詢下月過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用month函數
考核知識點： month()
答案：
8. SQL 查詢語句期中考試題2
--1.分別查詢學生表和學生修課表中的全部數據。
SELECT * FROM student--查詢學生表
SELECT * FROM course--查詢課程表
--2.查詢成績在70到80分之間的學生的學號、課程號和成績。
SELECT Sno 學號,Cno 課程號,Grade 成績
FROM SC
WHERE Grade BETWEEN 70 AND 80
--3．查詢C01號課程成績最高的分數
SELECT TOP 1 Grade
FROM SC
WHERE Cno='C01'
ORDER BY Grade DESC--降序DESC，升序ASC
--4.查詢學生都選修了哪些課程,要求列出課程號。
SELECT Cname AS 學生選修的課程,Cno AS 課程號
FROM course
WHERE Cno IN(SELECT DISTINCT Cno FROM SC)--DISTINCT用來去除重復
--5.查詢Northwind資料庫中orders表的OrderID、CustomerID和OrderDate,
--並將最新的定購日期(OrderDate)列在前邊。
USE Northwind
GO
SELECT OrderID,CustomerID,OrderDate
FROM orders
ORDER BY OrderDate DESC--降序DESC，升序ASC
--6.查詢Northwind資料庫中orders表的ShipCountry列以B,C,D,F --//ShipCountry BCDF
--開始且第三個字元為"a"的OrderID、CustomerID和ShipCountry的信息。//OrderID、CustomerID第三個字元為"a"
USE Northwind
GO
SELECT OrderID,CustomerID,ShipCountry FROM orders
WHERE ShipCountry LIKE '[BCDF]_a%'--_下劃線表示任意一個字元，%表示一個或多個字元
--7.查詢Northwind資料庫中orders表的ShipCountry列不以A,B,C,D,E,F開始且最後一個字
--母是"a"的OrderID、CustomerID和ShipCountry的信息。
USE Northwind
GO
SELECT OrderID,CustomerID,ShipCountry
FROM orders
WHERE ShipCountry LIKE '[^ABCDEF]%a'
--8.查詢修了C02號課程的所有學生的平均成績、最高成績和最低成績。
SELECT AVG(Grade) 平均成績,MAX(Grade) 最高成績,MIN(Grade) 最低成績
FROM SC
WHERE Cno='C02'
--9.統計每個系的學生人數。
SELECT Sdept 系別,COUNT(*) 人數
FROM student
WHERE Sdept IN(SELECT DISTINCT Sdept FROM student)
GROUP BY Sdept
--10.統計每門課程的修課人數和考試最高分。
SELECT Cname 課程名,COUNT(*) 修課人數,MAX(Grade) 考試最高分
FROM SC,course
WHERE SC.Cno IN(SELECT DISTINCT Cno FROM SC ) AND course.Cno=SC.Cno
GROUP BY course.Cname
--11.統計每個學生的選課門數,並按選課門數的遞增順序顯示結果。
SELECT student.Sname 學生姓名,student.Sno 學生學號,COUNT(SC.Sno) 選課門數
FROM student
inner join SC ON SC.Sno=student.Sno
GROUP BY student.Sname,student.Sno
ORDER BY COUNT(SC.Sno) ASC
--12.統計選修課的學生總數和考試的平均成績。
SELECT COUNT(DISTINCT(Sno)) AS 學生總數,AVG(Grade) AS 平均成績
FROM SC --//用DISTINCT消除重復的行
--13.查詢選課門數超過2門的學生的平均成績和選課門數。
SELECT Student.Sname,AVG(Sc.Grade) 平均成績,COUNT(SC.Sno) 選課門數
FROM SC
--內聯接join或inner join,內聯系是比較運算符，只返回符合條件的行
JOIN Student ON (SC.Sno = Student.Sno)
JOIN Course ON (SC.Cno = Course.Cno)
GROUP BY Student.Sname
HAVING COUNT(distinct Course.Cno) >2--分組條件
--14.列出總成績超過200分的學生,要求列出學號、總成績。
SELECT Sno 學號,SUM(Grade) 總成績
FROM SC
GROUP BY Sno
HAVING SUM(Grade)>200
--15.查詢pubs資料庫的titles表中每類圖書的平均價格超過12.0元的書的類型(Type)、
--平均價格和最高價格。
USE pubs
GO
SELECT Type AS 書的類型,AVG(price) AS 平均價格,MAX(price) AS 最高價格
FROM titles
GROUP BY Type
HAVING AVG(price)>12.0
--16.查詢pubs資料庫的titles表中每類圖書中圖書的數目超過3本的圖書的總價格。
USE pubs
GO
SELECT 圖書類型=Type,圖書的數目=count(Type),圖書的總價格=SUM(price)
FROM titles
GROUP BY Type
HAVING count(Type)>3
--17.查詢選修了c02號課程的學生的姓名和所在系。
SELECT Sname 學生姓名,Sdept 所在系,SC.Cno AS 選修課程
FROM student
inner join SC ON student.Sno=SC.Sno
WHERE SC.Cno='C02'
--18.查詢成績80分以上的學生的姓名、課程號和成績,並按成績的降序排列結果。
SELECT Sname 學生姓名,SC.Cno 課程號,SC.Grade 成績
FROM student
inner join SC ON student.Sno=SC.Sno--內連接表SC查詢
WHERE SC.Grade>80
ORDER BY SC.Grade DESC
--19.查詢計算機系男生修了"資料庫基礎"的學生的姓名、性別、成績。
SELECT Sname 姓名,Ssex 性別,SC.Grade 成績
FROM student
inner join SC ON Cno IN(SELECT Cno FROM course WHERE Cname='資料庫基礎') --顯示成績的條件
AND student.Sno=SC.Sno --顯示成績的學生的學號
WHERE Sdept='計算機系' AND Ssex='男'
--20.查詢哪些學生的年齡相同,要求列出年齡相同的學生的姓名和年齡。
--這題使用到了表的自連接，所以需要給表取兩個別名，如A和B
SELECT A.Sname 年齡相同的學生的姓名,A.Sage 年齡
FROM student A
inner join student B ON A.Sage IN(SELECT Sage FROM student WHERE A.Sage=B.Sage AND A.Sname!=B.Sname)
GROUP BY A.Sname,A.Sage
ORDER BY A.Sage
--21.查詢哪些課程沒有人選,要求列出課程號和課程名。
SELECT Cno AS 課程號,Cname AS 課程名
FROM course
WHERE Cno NOT IN(SELECT DISTINCT SC.Cno FROM SC)
--22.查詢有考試成績的所有學生的姓名、修課名稱及考試成績
--要求將查詢結果放在一張新的永久表(假設新表名為new-sc)中。
SELECT student.Sname AS 有考試成績的學生的姓名,course.Cname AS 修課名稱,SC.Grade AS 考試成績
INTO [new_sc] --將查詢結果放入新表new_sc中
FROM student,course,SC
WHERE SC.Grade IS NOT NULL AND student.Sno=SC.Sno AND course.Cno=SC.Cno
--23.分別查詢信息系和計算機系的學生的姓名、性別、修課名稱、修課成績,
--並要求將這兩個查詢結果合並成一個結果集,
--並以系名、姓名、性別、修課名稱、修課成績的順序顯示各列。
--//此題用到了並union查詢
SELECT Sdept 系名,Sname 姓名,Ssex 性別,course.Cname 修課名稱,SC.Grade 修課成績
FROM student
inner join SC ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept='信息系'
UNION
SELECT Sdept 系名,Sname 姓名,Ssex 性別,course.Cname 修課名稱,SC.Grade 修課成績
FROM student
inner join SC ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept='計算機系'
或
select sdept, sname,ssex,cname,grade from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and (sdept='信息系' or sdept='計算機系')
--24.用子查詢實現如下查詢:
--(1) 查詢選修了C01號課程的學生的姓名和所在系。
SELECT Sname AS 選修了C01號課程的學生的姓名,Sdept AS 所在系
FROM student
WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='C01')
--(2) 查詢數學系成績80分以上的學生的學號、姓名。
SELECT Sno AS 數學系成績80分以上的學生的學號,Sname AS 姓名
FROM student
WHERE Sno IN(SELECT Sno FROM SC WHERE Grade>80)
AND Sno IN(SELECT Sno FROM student WHERE Sdept='數學系')
--(3) 查詢計算機系學生所選的課程名.
SELECT Cname AS 計算機系學生所選的課程名
FROM course
WHERE
course.Cno IN(SELECT DISTINCT Cno FROM SC WHERE SC.Sno IN(SELECT Sno FROM student WHERE Sdept='計算機系'))
--25.將計算機系成績高於80分的學生的修課情況插入到另一張表中,分兩種情況實現：//////////////?????/
--(1) 在插入數據過程中建表。
--使用SELECT INTO插入數據的方法，是在插入數據的過程中建立新表
SELECT student.Sname AS 學生名字,course.Cname AS 選修課程,SC.Cno AS 課程編號
INTO [SC_Info1]--將計算機系成績高於80分的學生的修課情況插入到表SC_Info1中
FROM student,course,SC
WHERE student.Sdept='計算機系' AND SC.Grade>80 AND course.Cno=SC.Cno AND student.Sno=SC.Sno
--(2) 先建一個新表,然後再插入數據。
--創建表SC_Info2
CREATE TABLE SC_Info2
(
Sname char(7),
Cname char(20),
Cno char(10)
)
--往表SC_info2插入查詢得到的結果
INSERT SC_Info2
SELECT student.Sname AS 學生名字,course.Cname AS 選修課程,SC.Cno AS 課程編號
FROM student,course,SC
WHERE student.Sdept='計算機系' AND SC.Grade>80 AND course.Cno=SC.Cno AND student.Sno=SC.Sno
--26.刪除修課成績小於50分的學生的修課記錄
DELETE SC WHERE Grade<50 OR Grade IS NULL
--27.將所有選修了"c01"課程的學生的成績加10分。
UPDATE SC
SET Grade=Grade+10
WHERE Cno='C01'
9. 挑戰SQL經典題（一）
建表語句：
表說明：
student(sid,sname,sage,ssex) 學生表
course(cid,cname,tid) 課程表
sc(sid,cid,score) 成績表
teacher(tid,tname) 教師表
問題：
1、查詢「1」課程比「2」課程成績高的所有學生的學號；（3號-張三）
2、查詢平均成績大於60分的同學的學號和平均成績；（1、2、3、4號）
3、查詢所有同學的學號、姓名、選課數、總成績；
4、查詢姓「李」的老師的個數；（0個）
5、查詢沒學過「葉平」老師課的同學的學號、姓名； （4號-李四）
6、查詢學過「1」並且也學過編號「2」課程的同學的學號、姓名；
（1號-劉一 2號-錢二 3號-張三 6號-趙六 除了4號和5號）---考查EXISTS
7、查詢學過「葉平」老師所教的所有課的同學的學號、姓名； （除了4號）
8、查詢課程編號「2」的成績比課程編號「1」課程低的所有同學的學號、姓名；
（3號-張三）
9、查詢所有課程有成績小於60分的同學的學號、姓名；
（1號、3號、5號、6號 除了2號和4號）
10、查詢沒有學全所有課的同學的學號、姓名； （4號、5號、6號）
11、查詢至少有一門課與學號為「1」的同學所學相同的同學的學號和姓名； （All）
12、查詢跟學號為「1」的同學學的所有課一樣的其他同學學號和姓名； （2號和3號）
13、把「sc」表中「葉平」老師教的課的成績都更改為此課程的平均成績； （61.4）
14、刪除學習「葉平」老師課的SC表記錄；
15、向sc表中插入一條記錄，要求符合以下條件：沒有上過編號「3」課程的同學學號、2號課的平均成績；（6號童鞋沒有上過3號課程）
16、按平均成績從高到低顯示所有學生的「語文」、「數學」、「英語」三門的課程成績，按如下形式顯示： 學生ID,語文,數學,英語,有效課程數,有效平均分（待更正 語句有錯）
17、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
18、按各科平均成績從低到高和及格率的百分數從高到低順序
19、查詢如下課程平均成績和及格率的百分數(用"1行"顯示):
語文-1、數學-2、英語-3、物理-4
20、查詢不同老師所教不同課程平均分從高到低顯示
21、統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
22、查詢學生平均成績及其名次
23、查詢各科成績前三名的記錄:(不考慮成績並列情況)---------SQL不識別TOP
24、查詢每門課程被選修的學生數
25、查詢出只選修了一門課程的全部學生的學號和姓名
26、查詢男生、女生人數
27、查詢姓「張」的學生名單
28、查詢同名同姓學生名單，並統計同名人數
29、查詢平均成績大於85的所有學生的學號、姓名和平均成績
30、查詢「物理」課分數不及格的學生姓名和分數
31、查詢所有學生的選課情況；
32、查詢課程的成績在80分以上的學生姓名、課程名稱和分數；
33、查詢選修「周磊」老師所授課程的學生中，成績最高的學生姓名及其成績（李四-93）
34、查詢不同課程成績相同的學生的學號、課程號、學生成績
35、查詢兩門以上課程大於80分的同學的學號及其平均成績
36、檢索「4」課程分數小於60，按分數降序排列的同學學號
10. sql面試題50題（mysql版）
--插入學生表測試數據
insert into Student values(༽' , '趙雷' , -01-01' , '男');
insert into Student values(༾' , '錢電' , -12-21' , '男');
insert into Student values(༿' , '孫風' , -05-20' , '男');
insert into Student values(ཀ' , '李雲' , -08-06' , '男');
insert into Student values(ཁ' , '周梅' , -12-01' , '女');
insert into Student values(ག' , '吳蘭' , -03-01' , '女');
insert into Student values(གྷ' , '鄭竹' , -07-01' , '女');
insert into Student values(ང' , '王菊' , -01-20' , '女');
--課程表測試數據
insert into Course values(༽' , '語文' , ༾');
insert into Course values(༾' , '數學' , ༽');
insert into Course values(༿' , '英語' , ༿');
--教師表測試數據
insert into Teacher values(༽' , '張三');
insert into Teacher values(༾' , '李四');
insert into Teacher values(༿' , '王五');
--成績表測試數據
insert into Score values(༽' , ༽' , 80);
insert into Score values(༽' , ༾' , 90);
insert into Score values(༽' , ༿' , 99);
insert into Score values(༾' , ༽' , 70);
insert into Score values(༾' , ༾' , 60);
insert into Score values(༾' , ༿' , 80);
insert into Score values(༿' , ༽' , 80);
insert into Score values(༿' , ༾' , 80);
insert into Score values(༿' , ༿' , 80);
insert into Score values(ཀ' , ༽' , 50);
insert into Score values(ཀ' , ༾' , 30);
insert into Score values(ཀ' , ༿' , 20);
insert into Score values(ཁ' , ༽' , 76);
insert into Score values(ཁ' , ༾' , 87);
insert into Score values(ག' , ༽' , 31);
insert into Score values(ག' , ༿' , 34);
insert into Score values(གྷ' , ༾' , 89);
insert into Score values(གྷ' , ༿' , 98);
-- 1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數
select c.*,a.s_score as 01課程score,b.s_score as 02課程score from
score a,score b
left join student c
on b.s_id = c.s_id
where a.s_id = b.s_id and a.c_id = ༽' and b.c_id = ༾' and a.s_score > b.s_score;
-- 2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數
select a.* ,b.s_score as 01課程,c.s_score as 02課程 from student a
join score b
on a.s_id=b.s_id and b.c_id = ༽'
left join score c
on b.s_id = c.s_id and c.c_id = ༾'
where b.s_score < c.s_score ;
-- 3、查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績
select a.s_id,a.s_name,round(avg(b.s_score),2) as 平均成績 from student a
join score b
on a.s_id = b.s_id
group by b.s_id having 平均成績 >= 60;
備註：round[avg(成績),1]里,round是四捨五入函數，1代表保留1位小數
-- 4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績
-- (包括有成績的和無成績的)
select b. ,round(avg(a.s_score),2) as 平均成績 from
student b
left join score a on b.s_id = a.s_id group by a.s_id having 平均成績 < 60
union
select b. ,0 as 平衡成績 from student b where b.s_id not in (select s_id from score);
-- 5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績
select a.s_id,a.s_name,count(b.c_id) as 選課總數 ,sum(b.s_score) as 總分 from student a
left join score b
on a.s_id = b.s_id group by s_id ;
-- 6、查詢"李"姓老師的數量
select count(*) as 李姓老師數量 from teacher where t_name like '李%'
-- 7、查詢學過"張三"老師授課的同學的信息
select a.* from student a join score b
on a.s_id = b.s_id
where b.c_id in (select c.c_id from course c
join teacher d on c.t_id = d.t_id where d.t_name = '張三');
-- 8、查詢沒學過"張三"老師授課的同學的信息
select a.* from student a left join score b on a.s_id = b.s_id where a.s_id not in
(select s_id from score where c_id =
(select c_id from course where t_id =
(select t_id from teacher where t_name = '張
三'))) group by a.s_id;
-- 9、查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息
select * from student where s_id in
(select a.s_id from score a join score b on a.s_id = b.s_id
where a.c_id = ༽' and b.c_id = ༾');
-- 10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息
select * from student where s_id in
(select s_id from score where c_id = ༽' )
and s_id not in (select s_id from score where c_id = ༾' );
-- 11、查詢沒有學全所有課程的同學的信息
select * from student where s_id not in
(select s_id from score group by s_id having count(c_id) = 3);
-- 12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息
select distinct a.* from student a left join score b
on a.s_id = b.s_id where b.c_id in
(select c_id from score where s_id = ༽') and a.s_id != ༽' ;
注意：distinct是去重的
-- 13、查詢和"01"號的同學學習的課程完全相同的其他同學的信息
select * from student where s_id in
(select s_id from score group by s_id having count(c_id) =
(select count(c_id) from score where s_id = ༽') and s_id not in
(select s_id from score where c_id not in
(select c_id from score where s_id = ༽')) and s_id != ༽');
-- 14、查詢沒學過"張三"老師講授的任一門課程的學生姓名
select s_name from student where s_id not in
(select s_id from score where c_id in
(select c_id from course where t_id in
(select t_id from teacher where t_name ='張三')));
-- 15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成績 from score a
left join student b on a.s_id = b.s_id
where s_score < 60 group by s_id having count(1) >=2;
或者試試
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成績 from score a
left join student b on a.s_id = b.s_id
where a.s_score < 60 group by a.s_id having count(*) >=2;
-- 16、檢索"01"課程分數小於60，按分數降序排列的學生信息
select a.* ,b.c_id ,b.s_score from student a
left join score b on a.s_id = b.s_id
where b.c_id = ༽' and b.s_score < 60
order by b.s_score desc;
-- 17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
select a.s_name ,
sum(case when b.c_id = ༽' then s_score else null end ) as 語文,
sum(case when b.c_id = ༾' then s_score else null end ) as 數學,
sum(case when b.c_id = ༿' then s_score else null end ) as 英語,
round(avg(s_score),2) as 平均成績
from student a left join score b on a.s_id = b.s_id group by a.s_name
order by 平均成績 desc;
-- 18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
--及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90
select b.c_id,b.c_name,
max(a.s_score) as 最高分,
min(a.s_score) as 最低分,
round(avg(a.s_score),2) as 平均分,
round(sum(case when a.s_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,
round(sum(case when a.s_score>= 70 and a.s_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,
round(sum(case when a.s_score>= 80 and a.s_score <90 then 1 else 0 end)/count(s_id),2) as 優良率,
round(sum(case when a.s_score>= 90 then 1 else 0 end)/count(s_id),2) as 優秀率
from score a
left join course b
on a.c_id = b.c_id group by b.c_id;
-- 19、按各科成績進行排序，並顯示排名
第一種：
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2;
如果看不懂用第二種方法：
SELECT a.c_id,a.s_id,a.s_score,COUNT(b.s_score)+1 AS 排名
FROM score a LEFT JOIN score b ON a.s_score<b.s_score AND a.c_id = b.c_id
GROUP BY a.c_id,a.s_id,a.s_score ORDER BY a.c_id,排名,a.s_id ASC
-- 20、查詢學生的總成績並進行排名
set @rank:=0;
select * ,(@rank:=@rank+1) as rank from
(select s_id ,sum(s_score) as 總成績 from score
group by s_id order by 總成績 desc) tb1;
-- 21、查詢不同老師所教不同課程平均分從高到低顯示
select a.c_id, d.t_name,round(avg(a.s_score)) as 平均分 from score a
left join student b on a.s_id = b.s_id
left join course c on a.c_id = c.c_id
left join teacher d on c.t_id = d.t_id group by a.c_id
order by 平均分 desc;
-- 22、查詢所有課程的成績第2名到第3名的學生信息及該課程成績
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 = 2 or 排名 =3;
-- 23、統計各科成績各分數段人數：課程編號,課程名稱,[100-85],(85-70],(70-60],(0-60]及所佔百分比
select b.c_id,b.c_name ,
sum(case when a.s_score >=85 then 1 else 0 end) as 100-85 ,
concat(round(100 sum(case when a.s_score >=85 then 1 else 0 end)/count( ),2), '%') as 百分比,
sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end) as 85-70 ,
concat(round(100 sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end)/count( ),2),'%') as 百分比,
sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end) as 70-60 ,
concat(round(100 sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,
sum(case when a.s_score <60 and a.s_score >=0 then 1 else 0 end) as 60-0 ,
concat(round(100 sum(case when a.s_score <60 and a.s_score >=0 then 1
else 0 end)/count( ),2),'%') as 百分比
from score a left join course b on a.c_id = b.c_id group by b.c_id;
-- 24、查詢學生平均成績及其名次
select tb1.*,(@rank:=@rank +1 ) as rank from
(select s_id ,round(avg(s_score),2) as 平均成績 from score
group by s_id order by 平均成績 desc) tb1,(select @rank:=0) b;
-- 25、查詢各科成績前三名的記錄
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 <4;
-- 26、查詢每門課程被選修的學生數
select c_id ,count(s_id) as 選修人數 from score group by c_id;
-- 27、查詢出只有兩門課程的全部學生的學號和姓名
select a.s_id ,b.s_name from score a left join student b on a.s_id = b.s_id group by s_id having count(*) = 2;
-- 28、查詢男生、女生人數
select sum(case s_sex when '男' then 1 else 0 end) as 男生人數,
sum(case s_sex when '女' then 1 else 0 end) as 女生人數 from student;
-- 29、查詢名字中含有"風"字的學生信息
select * from student where s_name like '%風%'
-- 30、查詢同名同性學生名單，並統計同名人數
--略，不想寫
-- 31、查詢1990年出生的學生名單
select * from student where s_birth like %'
-- 32、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
select c_id ,round(avg(s_score),2) as 平均成績 from score group by c_id order by 平均成績 desc, c_id asc;
-- 33、查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
select a.s_id,b.s_name ,round(avg(s_score),2) as 平均成績 from score a
left join student b on a.s_id = b.s_id group by a.s_id having 平均成績>=85;
-- 34、查詢課程名稱為"數學"，且分數低於60的學生姓名和分數
select b.s_name ,a.s_score from score a
left join student b on a.s_id = b.s_id
where a.c_id=(select c_id from course where c_name = '數學')and a.s_score < 60;
-- 35、查詢所有學生的課程及分數情況；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 語文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 數學,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英語
from score a right join student b on a.s_id = b.s_id group by b.s_name
-- 36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 語文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 數學,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英語
from score a right join student b on a.s_id = b.s_id group by b.s_name having 語文>= 70 or 數學>= 70 or 英語>= 70 ;
-- 37、查詢不及格的課程
select a.s_id,a.c_id,b.c_name,a.s_score from score a
left join course b on a.c_id = b.c_id where a.s_score<60;
--38、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名；
select a.s_id,b.s_name from score a left join student b on a.s_id = b.s_id where a.c_id = ༽' and a.s_score>=80;
-- 39、求每門課程的學生人數
select c_id,count(*) as 學生人數 from score group by c_id ;
-- 40、查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績
select a.*,b.c_id,max(b.s_score) as 最高成績 from student a
right join score b on a.s_id = b.s_id
group by b.c_id
having b.c_id = (select c_id from course
where t_id = (select t_id from teacher where t_name = '張三'));
-- 41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
--（這題我搞不清題目是什麼意思,是指查找學生個體參加了的所有課程的成績各不相同的那個學生信息呢？還是所有課程之間做對比呢，我更傾向於理解為前者）
--理解為前者的寫法
select * from
(select * from score group by s_id,s_score) tb1
group by s_id having count(*) = 1;
--理解為後者的寫法
select distinct a.s_id,a.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score;
-- 42、查詢每門課程成績最好的前兩名
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2
join student b on tb2.s_id = b.s_id where 排名 <3;
-- 43、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人相同，按課程號升序排列
select c_id ,count(*) as 選修人數 from score group by c_id having 選修人數>5 order by 選修人數 desc , c_id asc;
-- 44、檢索至少選修兩門課程的學生學號
select s_id from score group by s_id having count(*) >= 2;
-- 45、查詢選修了全部課程的學生信息
select * from student where s_id in
(select s_id from score group by s_id having count(*) = 3)
--46、查詢各學生的年齡
select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else 1 end)) as age
from student
-- 47、查詢本周過生日的學生
---(實現得並不完全，因為例如出生月日為『01-01』在每一年可能會輸入不同周）
select * from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;
-- 48、查詢下周過生日的學生
select * from student
where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));
-- 49、查詢本月過生日的學生
select * from student where date_format(s_birth,'%m') = date_format(now(),'%m')
-- 50、查詢下月過生日的學生
select * from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')