一、實驗名稱和性質
分類查詢
驗證? 綜合 設計
二、實驗目的
1．掌握數據查詢的Group by ；
2． 掌握聚集函數的使用方法。
三、實驗的軟硬件環境要求
硬件環境要求：
?????? PC機(單機)
使用的軟件名稱、版本號以及模塊：
?????? Windows 10下的SQL Server 2019
四、知識準備
前期要求掌握的知識：
數據檢索的語句格式：
SELECT [ALL|DISTINCT] <目標列表達式列表>??????????????????????????????????????????????? ??
FROM <表名或視圖名列表>?
[ WHERE <條件表達式> ]
[ GROUP BY <列名1>,… [WITH ROLLUP | CUBE]? [CUBE(…)| ROLLUP(…)]
[GROUPING SETS (…)]
[ HAVING <條件表達式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]
?其中,SELECT后的目標列表達式可以是列名、表達式或函數。
GROUP BY子句：對查詢結果按指定列的值分組,該屬性列值相等的元組為一個組。通常會在每組中作用集函數。
HAVING短語：篩選出只有滿足指定條件的組
ORDER BY子句：對查詢結果表按指定列值的升序或降序排序
注意：T-SQL語句中關鍵字不區分大小寫
五、實驗內容(以實驗5的數據為基礎)
1．對數據表進行分類匯總；
2．在檢索中使用聚集函數；
六、驗證性實驗
- 使用聚集函數
(1)查詢選課表中最高分、平均分、最低分
SELECT MAX(DEGREE), AVG(DEGREE),MIN(DEGREE)
FROM SCORE023
(2)查詢“3-105”課程的最高分、平均分和最小成績。
SELECT MAX(DEGREE), AVG(DEGREE),MIN(DEGREE)
FROM? SCORE023
WHERE? CNO=‘3-105’
- 進行分組統計：
(1)查詢各學生的選課數
SELECT? SNO,COUNT(*)
FROM? SCORE023
GROUP? BY? SNO
(2)使用HAVING 字句：“選課表”中查詢選修了3 門以上課程的學生學號。
SELECT ?SNO ,COUNT(*)FROM SCORE023
GROUP ?BY ?SNO
HAVING ?COUNT(*)>=3
???? 
(3)“選課表”中按學號分組匯總學生的平均分,并按平均分的降序排列。
SELECT ?SNO? 學號, AVG(DEGREE)? 平均分? FROM ?SCORE023
GROUP ?BY ?SNO
ORDER ?BY ?平均分? DESC??? -- -- ORDER ?BY AVG(DEGREE)? DESC
???????? 
“選課表”中查詢平均成績80以上課程的學生學號和平均成績。
SELECT ?SNO ,avg(degree) 平均 FROM SCORE023
GROUP ?BY ?SNO
HAVING ?avg(degree)>=80
“選課表”中95031班中平均成績80以上課程的學生學號,姓名,平均成績。
SELECT ?S.SNO ,SNAME,avg(degree) 平均 FROM SCORE023? SC
JOIN STUDENTXXX? S ON S.SNO=SC.SNO? WHERE CLASS=’95031’
GROUP ?BY ?S.SNO,SNAME
HAVING ?avg(degree)>=80
(4)分析下面SELECT語句執行結果
SELECT? SNO, CNO ,AVG(DEGREE)? FROM? SCORE023
GROUP? BY? SNO??????????????????????? --???
SELECT? SNO,CNO,AVG(DEGREE)? FROM? SCORE023
GROUP? BY? SNO,CNO WITH ROLLUP
SELECT? SNO,CNO,AVG(DEGREE)? FROM? SCORE023
GROUP? BY ROLLUP(SNO,CNO)?
SELECT? SNO, CNO,AVG(DEGREE) FROM? SCORE023
GROUP? BY? SNO,CNO WITH CUBE
SELECT? SNO,CNO,AVG(DEGREE) FROM? SCORE023
GROUP? BY CUBE(SNO,CNO)
SELECT? SNO,CNO,AVG(DEGREE)? FROM SCORE023
GROUP BY GROUPING SETS (SNO,CNO)
SELECT? SNO,CNO,AVG(DEGREE)? FROM SCORE023
GROUP BY GROUPING SETS (ROLLUP(SNO,CNO))
SELECT? SNO,CNO,AVG(DEGREE)? FROM SCORE023
GROUP BY GROUPING SETS (ROLLUP(SNO,CNO),CUBE(SNO,CNO))
七、設計性實驗
1．實驗要求
(1)查詢每門課程的平均分,包括課程號和平均分。
(2)查詢每門課程的平均分,包括課程號、課程名和平均分。
(3)查詢男的平均分,包括學號和平均分。
(4)查詢女的平均分,包括學號,姓名和平均分。
(5)查詢每門課程的最高、最低分,包括課程號和最高、最低分。
(6)查詢每位學生的最高、最低分、平均分,包括學號,姓名和最高、最低分、平均分。
(7)查詢所有成績都及格的學號,姓名
(8)查詢每位老師所授課程的最高、最低分,包括教師號,姓名,課程名和最高、最低分。
(9)查詢學生信息,包括學號,課程號,成績以及學生的平均分。(一個語句)
? 
(10)查詢學生信息,包括姓名,課程名,成績以及每位學生的平均分和每門課程的平均分。(一個語句)
(11)查詢學生信息,包括學號,姓名,課程號,課程名,成績以及每位學生的平均分和每門課程的平均分。(一個語句)
?(12)查詢學生信息,包括學號,姓名,課程號,課程名以及每位學生的平均分和每門課程的平均分。(一個語句)
