1、第九章習(xí)題9.1分組查詢練習(xí)a). 基于“生源情況”表，統(tǒng)計(jì)不同姓的人數(shù)，列出：姓、人數(shù)，結(jié)果按人數(shù)降序排序。（先統(tǒng)計(jì)所有學(xué)生的人數(shù)）SELECT left(姓名,1) as 姓,count(*) as 人數(shù) FROM 生源情況 group by left(姓名,1)ORDER BY count(*) DESCb). 基于“生源情況”表，按地區(qū)分類統(tǒng)計(jì)入學(xué)成績(jī)的平均值、最大值、最小值，并按平均值降序排列。先統(tǒng)計(jì)所有入學(xué)成績(jī)的的平均值、最大值、最小值）SELECT 地區(qū),avg(入學(xué)成績(jī)) as 平均值,max(入學(xué)成績(jī)) as 最大值,min(入學(xué)成績(jī)) as 最小值 FROM 生源情況 gr

2、oup by 地區(qū)ORDER BY avg(入學(xué)成績(jī)) DESCc). 基于“生源情況”表，找出年齡平均值最高的班級(jí)。（提示：先按班級(jí)名稱統(tǒng)計(jì)每個(gè)班級(jí)的平均年齡，再找年齡的最大值）SELECT TOP 11注意top不在相等值之間進(jìn)行選擇 2 匯聚函數(shù)不能嵌套使用，即寫成max(avg()的形式 班級(jí)名稱, AVG(year(date()-year(出生日期) as 平均年齡FROM 生源情況GROUP BY 班級(jí)名稱ORDER BY AVG(year(date()-year(出生日期) DESCd). 基于“生源情況”表，列出學(xué)生人數(shù)少于100的地區(qū)。select 地區(qū), count(*)

3、as 學(xué)生人數(shù)from 生源情況group by 地區(qū)having count(*)<1009.2聯(lián)接查詢練習(xí)a). 列舉學(xué)號(hào)為“200894006”的同學(xué)所參加的考試的場(chǎng)次、教室和課程名稱。select 場(chǎng)次,教室,課程名稱from 學(xué)生考試 inner join 課程 on 學(xué)生考試.課程編號(hào)=課程.課程編號(hào)where 學(xué)號(hào)="200894006"或select 場(chǎng)次,教室,課程名稱from 學(xué)生考試,課程 where 學(xué)號(hào)="200894006" and 學(xué)生考試.課程編號(hào)=課程.課程編號(hào)b). 列舉學(xué)號(hào)為“200894006”的同學(xué)所參加的

4、每場(chǎng)考試的監(jiān)考教師的姓名。select 姓名from (學(xué)生考試 inner join 教師監(jiān)考 on 學(xué)生考試.場(chǎng)次=教師監(jiān)考.場(chǎng)次 and 學(xué)生考試.課程編號(hào)=教師監(jiān)考.課程編號(hào) and 學(xué)生考試.教室=教師監(jiān)考.教室)inner join 教師 on 教師監(jiān)考.教師編號(hào)=教師.教師編號(hào)where 學(xué)號(hào)="200894006"或select 姓名from 學(xué)生考試,教師監(jiān)考,教師 where 學(xué)號(hào)="200894006" and 學(xué)生考試.場(chǎng)次=教師監(jiān)考.場(chǎng)次and 學(xué)生考試.課程編號(hào)=教師監(jiān)考.課程編號(hào) and 學(xué)生考試.教室=教師監(jiān)考.教室 an

5、d 教師監(jiān)考.教師編號(hào)=教師.教師編號(hào)c). 列舉來自“浙江”的學(xué)生的所在學(xué)院名稱、班級(jí)名稱、學(xué)號(hào)和姓名。SELECT 學(xué)院名稱, 班級(jí)名稱, 學(xué)號(hào), 姓名FROM (學(xué)院 INNER JOIN 班級(jí) ON 學(xué)院.學(xué)院編號(hào) = 班級(jí).學(xué)院編號(hào)) INNER JOIN 學(xué)生 ON 班級(jí).班級(jí)編號(hào) = 學(xué)生.班級(jí)編號(hào)WHERE 地區(qū)="浙江"或SELECT 學(xué)院名稱, 班級(jí)名稱, 學(xué)號(hào), 姓名FROM 學(xué)院,班級(jí),學(xué)生 WHERE 地區(qū)="浙江" and 學(xué)院.學(xué)院編號(hào) = 班級(jí).學(xué)院編號(hào) and 班級(jí).班級(jí)編號(hào) = 學(xué)生.班級(jí)編號(hào)d). 查詢“梁子平”同學(xué)

6、所在班級(jí)的學(xué)生人數(shù)。SELECT 班級(jí)人數(shù)FROM 班級(jí) INNER JOIN 學(xué)生 ON 班級(jí).班級(jí)編號(hào) = 學(xué)生.班級(jí)編號(hào)WHERE 姓名="梁子平"或SELECT 班級(jí)人數(shù)FROM 班級(jí),學(xué)生WHERE 姓名="梁子平" and 班級(jí).班級(jí)編號(hào) = 學(xué)生.班級(jí)編號(hào)或select count(*)from 學(xué)生where 班級(jí)編號(hào) in (SELECT 班級(jí)編號(hào) from 學(xué)生 WHERE 姓名="梁子平")或select 班級(jí)人數(shù)from 班級(jí)where 班級(jí)編號(hào) in (SELECT 班級(jí)編號(hào) from 學(xué)生 WHERE 姓名

7、="梁子平")9.3 子查詢a). 找出與學(xué)號(hào)“200894006”的學(xué)生同學(xué)院且同年出生的學(xué)生。先作這個(gè)查詢，學(xué)院編號(hào)為”01”和1991出生的學(xué)生信息select 學(xué)生.*from 學(xué)生 inner join 班級(jí) on 學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào)where 學(xué)院編號(hào)="01" and year(出生日期)=1991第一步：先找到該同學(xué)所在學(xué)院的編號(hào)select 學(xué)院編號(hào)from 班級(jí)where 班級(jí)編號(hào) in (select 班級(jí)編號(hào) from 學(xué)生 where 學(xué)號(hào)="200894006")第二步：找到年份SELECT y

8、ear(出生日期) from 學(xué)生 where 學(xué)號(hào)="200894006"第三步：多表連接select 學(xué)生.*from 學(xué)生如果班級(jí)表寫在前面要用 學(xué)生.* inner join 班級(jí) on 學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào)where 學(xué)院編號(hào) in (select 學(xué)院編號(hào)from 班級(jí)where 班級(jí)編號(hào) in(select 班級(jí)編號(hào)from 學(xué)生where 學(xué)號(hào)="200894006") and year(出生日期) in (select year(出生日期)from 學(xué)生where 學(xué)號(hào)="200894006")或selec

9、t 學(xué)生.*from 班級(jí),學(xué)生where 學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào) and 學(xué)院編號(hào) in (select 學(xué)院編號(hào)from 班級(jí)where 班級(jí)編號(hào) in (select 班級(jí)編號(hào) from 學(xué)生 where 學(xué)號(hào)="200894006")and year(出生日期) in (SELECT year(出生日期) from 學(xué)生 where 學(xué)號(hào)="200894006")b). 例舉班級(jí)數(shù)少于20的那些學(xué)院的名稱和班級(jí)數(shù)。SELECT 學(xué)院名稱,(select count(*) from 班級(jí) where 學(xué)院.學(xué)院編號(hào)=班級(jí).學(xué)院編號(hào) ) as

10、 班級(jí)數(shù)from 學(xué)院where (select count(*) from 班級(jí) where 學(xué)院.學(xué)院編號(hào)=班級(jí).學(xué)院編號(hào) )<20c). 例舉教師“羅亮”所監(jiān)考的學(xué)生來自哪些班級(jí)。第一步：“羅亮”監(jiān)考的場(chǎng)次select 場(chǎng)次from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")第二步：“羅亮”監(jiān)考的課程編號(hào)select 課程編號(hào)from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")第三步：“羅亮”監(jiān)考的教室select

11、教室from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")第四步：滿足以上三個(gè)條件考試的學(xué)生的學(xué)號(hào)select 學(xué)號(hào)from 學(xué)生考試where 場(chǎng)次 in (select 場(chǎng)次from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")and課程編號(hào) in (select 課程編號(hào)from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")and 教室 in (

12、select 教室from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")第五步：多表連接select distinct 班級(jí)名稱from 班級(jí),學(xué)生where 學(xué)生.班級(jí)編號(hào)=班級(jí).班級(jí)編號(hào) and 學(xué)號(hào) in (select 學(xué)號(hào)from 學(xué)生考試where 場(chǎng)次 in (select 場(chǎng)次from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")and課程編號(hào) in (select 課程編號(hào)from 教師監(jiān)考 where 教師編號(hào)

13、in (select 教師編號(hào)from 教師 where 姓名="羅亮")and 教室 in (select 教室from 教師監(jiān)考 where 教師編號(hào) in (select 教師編號(hào)from 教師 where 姓名="羅亮")d). 找出“7月01日08:30-10:30”這一場(chǎng)次沒有監(jiān)考任務(wù)的教師。SELECT 姓名from 教師where 教師編號(hào) not in(select 教師編號(hào)此處必須是教師編號(hào)，不能是*號(hào) from 教師監(jiān)考 where 場(chǎng)次="7月01日08:30-10:30")或SELECT 姓名from 教師w

14、here not exists(select *此處是*號(hào)，不關(guān)心返回的哪個(gè)字段，只關(guān)心是否有記錄返回 from 教師監(jiān)考 where 教師.教師編號(hào)=教師監(jiān)考.教師編號(hào) and 場(chǎng)次="7月01日08:30-10:30")補(bǔ)充查詢：1. 基于“生源情況”表，統(tǒng)計(jì)最低入學(xué)成績(jī)、最高入學(xué)成績(jī)相差多少分。SELECT Max(入學(xué)成績(jī))-Min(入學(xué)成績(jī)) FROM 生源情況2. 基于“生源情況”表，按地區(qū)統(tǒng)計(jì)各性別學(xué)生人數(shù)。提示：多級(jí)分組，一項(xiàng)統(tǒng)計(jì)SELECT 地區(qū),性別,count(*) as 人數(shù)from 生源情況group by 地區(qū),性別3. 基于“生源情況”表，按地

15、區(qū)統(tǒng)計(jì)各性別學(xué)生人數(shù)和平均年齡，平均年齡四舍五入到小數(shù)點(diǎn)2位。提示：多級(jí)分組，多項(xiàng)統(tǒng)計(jì)SELECT 地區(qū),性別,count(*) as 人數(shù),round(avg(year(date()-year(出生日期),2) as 平均年齡from 生源情況group by 地區(qū),性別4. 基于“學(xué)生考試”表，列舉參加考試的學(xué)生人數(shù)少于30的、課程號(hào)以11開頭的課程編號(hào)及考試人數(shù)，結(jié)果按考試人數(shù)的統(tǒng)計(jì)值排序。提示：條件的書寫；所有子句出現(xiàn)的順序SELECT 課程編號(hào), Count(*) AS 考試人數(shù) FROM 學(xué)生考試WHERE 課程編號(hào) LIKE "11*"GROUP BY 課程編

16、號(hào)HAVING Count(*)<30ORDER BY Count(*)或SELECT 課程編號(hào), Count(*) AS 考試人數(shù) FROM 學(xué)生考試GROUP BY 課程編號(hào)HAVING Count(*)<30 and 課程編號(hào) LIKE "11*"ORDER BY Count(*)5. 基于“生源情況”表，求來自江西地區(qū)的學(xué)生的入學(xué)成績(jī)總和。提示：教材116頁例3，采用兩種方法SELECT "江西" AS 地區(qū),Sum(入學(xué)成績(jī)) AS 總成績(jī)FROM 生源情況WHERE 地區(qū)="江西"或SELECT 地區(qū),Sum(入學(xué)成績(jī)) AS 總成績(jī)FROM 生源情況group by 地區(qū)having 地區(qū)="江西"6. 查詢與某教師”21010287”同一時(shí)間監(jiān)考的其他教師的教師編號(hào)。（采用自聯(lián)接完成）SE