數(shù)據(jù)庫查詢練習(xí)題目有答案

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

2、oup by 地區(qū)ORDER BY avg(入學(xué)成績) DESCc). 基于“生源情況”表，找出年齡平均值最高的班級。（提示：先按班級名稱統(tǒng)計每個班級的平均年齡，再找年齡的最大值）SELECT TOP 11注意top不在相等值之間進(jìn)行選擇 2 匯聚函數(shù)不能嵌套使用，即寫成max(avg()的形式 班級名稱, AVG(year(date()-year(出生日期) as 平均年齡FROM 生源情況GROUP BY 班級名稱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é)號為“200894006”的同學(xué)所參加的考試的場次、教室和課程名稱。select 場次,教室,課程名稱from 學(xué)生考試 inner join 課程 on 學(xué)生考試.課程編號=課程.課程編號where 學(xué)號="200894006"或select 場次,教室,課程名稱from 學(xué)生考試,課程 where 學(xué)號="200894006" and 學(xué)生考試.課程編號=課程.課程編號b). 列舉學(xué)號為“200894006”的同學(xué)所參加的

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

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

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

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

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

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

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

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

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

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

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

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

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