《数据库第9章查询练习题目(有答案).doc》由会员分享，可在线阅读，更多相关《数据库第9章查询练习题目(有答案).doc（7页珍藏版）》请在三一文库上搜索。
1、第九章习题9.1分组查询练习a). 基于“生源情况”表，统计不同姓的人数，列出：姓、人数，结果按人数降序排序。（先统计所有学生的人数）SELECT left(姓名,1) as 姓,count(*) as 人数 FROM 生源情况 group by left(姓名,1)ORDER BY count(*) DESCb). 基于“生源情况”表，按地区分类统计入学成绩的平均值、最大值、最小值，并按平均值降序排列。先统计所有入学成绩的的平均值、最大值、最小值）SELECT 地区,avg(入学成绩) as 平均值,max(入学成绩) as 最大值,min(入学成绩) as 最小值 FROM 生源情况 gr
2、oup by 地区ORDER BY avg(入学成绩) DESCc). 基于“生源情况”表，找出年龄平均值最高的班级。（提示：先按班级名称统计每个班级的平均年龄，再找年龄的最大值）SELECT TOP 11注意top不在相等值之间进行选择 2 汇聚函数不能嵌套使用，即写成max(avg()的形式 班级名称, AVG(year(date()-year(出生日期) as 平均年龄FROM 生源情况GROUP BY 班级名称ORDER BY AVG(year(date()-year(出生日期) DESCd). 基于“生源情况”表，列出学生人数少于100的地区。select 地区, count(*)
3、as 学生人数from 生源情况group by 地区having count(*)1009.2联接查询练习a). 列举学号为“200894006”的同学所参加的考试的场次、教室和课程名称。select 场次,教室,课程名称from 学生考试 inner join 课程 on 学生考试.课程编号=课程.课程编号where 学号=200894006或select 场次,教室,课程名称from 学生考试,课程 where 学号=200894006 and 学生考试.课程编号=课程.课程编号b). 列举学号为“200894006”的同学所参加的每场考试的监考教师的姓名。select 姓名from (
4、学生考试 inner join 教师监考 on 学生考试.场次=教师监考.场次 and 学生考试.课程编号=教师监考.课程编号 and 学生考试.教室=教师监考.教室)inner join 教师 on 教师监考.教师编号=教师.教师编号where 学号=200894006或select 姓名from 学生考试,教师监考,教师 where 学号=200894006 and 学生考试.场次=教师监考.场次and 学生考试.课程编号=教师监考.课程编号 and 学生考试.教室=教师监考.教室 and 教师监考.教师编号=教师.教师编号c). 列举来自“浙江”的学生的所在学院名称、班级名称、学号和姓名
5、。SELECT 学院名称, 班级名称, 学号, 姓名FROM (学院 INNER JOIN 班级 ON 学院.学院编号 = 班级.学院编号) INNER JOIN 学生 ON 班级.班级编号 = 学生.班级编号WHERE 地区=浙江或SELECT 学院名称, 班级名称, 学号, 姓名FROM 学院,班级,学生 WHERE 地区=浙江 and 学院.学院编号 = 班级.学院编号 and 班级.班级编号 = 学生.班级编号d). 查询“梁子平”同学所在班级的学生人数。SELECT 班级人数FROM 班级 INNER JOIN 学生 ON 班级.班级编号 = 学生.班级编号WHERE 姓名=梁子平或
6、SELECT 班级人数FROM 班级,学生WHERE 姓名=梁子平 and 班级.班级编号 = 学生.班级编号或select count(*)from 学生where 班级编号 in (SELECT 班级编号 from 学生 WHERE 姓名=梁子平)或select 班级人数from 班级where 班级编号 in (SELECT 班级编号 from 学生 WHERE 姓名=梁子平)9.3 子查询a). 找出与学号“200894006”的学生同学院且同年出生的学生。先作这个查询，学院编号为”01”和1991出生的学生信息select 学生.*from 学生 inner join 班级 on 学
7、生.班级编号=班级.班级编号where 学院编号=01 and year(出生日期)=1991第一步：先找到该同学所在学院的编号select 学院编号from 班级where 班级编号 in (select 班级编号 from 学生 where 学号=200894006)第二步：找到年份SELECT year(出生日期) from 学生 where 学号=200894006第三步：多表连接select 学生.*from 学生如果班级表写在前面要用 学生.* inner join 班级 on 学生.班级编号=班级.班级编号where 学院编号 in (select 学院编号from 班级wher
8、e 班级编号 in(select 班级编号from 学生where 学号=200894006) and year(出生日期) in (select year(出生日期)from 学生where 学号=200894006)或select 学生.*from 班级,学生where 学生.班级编号=班级.班级编号 and 学院编号 in (select 学院编号from 班级where 班级编号 in (select 班级编号 from 学生 where 学号=200894006)and year(出生日期) in (SELECT year(出生日期) from 学生 where 学号=2008940
9、06)b). 例举班级数少于20的那些学院的名称和班级数。SELECT 学院名称,(select count(*) from 班级 where 学院.学院编号=班级.学院编号 ) as 班级数from 学院where (select count(*) from 班级 where 学院.学院编号=班级.学院编号 )20c). 例举教师“罗亮”所监考的学生来自哪些班级。第一步：“罗亮”监考的场次select 场次from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)第二步：“罗亮”监考的课程编号select 课程编号from 教师监考 wh
10、ere 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)第三步：“罗亮”监考的教室select 教室from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)第四步：满足以上三个条件考试的学生的学号select 学号from 学生考试where 场次 in (select 场次from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)and课程编号 in (select 课程编号from 教师监考 where 教师编号 in (select 教师编号fro
11、m 教师 where 姓名=罗亮)and 教室 in (select 教室from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)第五步：多表连接select distinct 班级名称from 班级,学生where 学生.班级编号=班级.班级编号 and 学号 in (select 学号from 学生考试where 场次 in (select 场次from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)and课程编号 in (select 课程编号from 教师监考 where 教
12、师编号 in (select 教师编号from 教师 where 姓名=罗亮)and 教室 in (select 教室from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)d). 找出“7月01日08:30-10:30”这一场次没有监考任务的教师。SELECT 姓名from 教师where 教师编号 not in(select 教师编号此处必须是教师编号，不能是*号 from 教师监考 where 场次=7月01日08:30-10:30)或SELECT 姓名from 教师where not exists(select *此处是*号，不关
13、心返回的哪个字段，只关心是否有记录返回 from 教师监考 where 教师.教师编号=教师监考.教师编号 and 场次=7月01日08:30-10:30)补充查询：1. 基于“生源情况”表，统计最低入学成绩、最高入学成绩相差多少分。SELECT Max(入学成绩)-Min(入学成绩) FROM 生源情况2. 基于“生源情况”表，按地区统计各性别学生人数。提示：多级分组，一项统计SELECT 地区,性别,count(*) as 人数from 生源情况group by 地区,性别3. 基于“生源情况”表，按地区统计各性别学生人数和平均年龄，平均年龄四舍五入到小数点2位。提示：多级分组，多项统计S
14、ELECT 地区,性别,count(*) as 人数,round(avg(year(date()-year(出生日期),2) as 平均年龄from 生源情况group by 地区,性别4. 基于“学生考试”表，列举参加考试的学生人数少于30的、课程号以11开头的课程编号及考试人数，结果按考试人数的统计值排序。提示：条件的书写；所有子句出现的顺序SELECT 课程编号, Count(*) AS 考试人数 FROM 学生考试WHERE 课程编号 LIKE 11*GROUP BY 课程编号HAVING Count(*)30ORDER BY Count(*)或SELECT 课程编号, Count(*
15、) AS 考试人数 FROM 学生考试GROUP BY 课程编号HAVING Count(*)30 and 课程编号 LIKE 11*ORDER BY Count(*)5. 基于“生源情况”表，求来自江西地区的学生的入学成绩总和。提示：教材116页例3，采用两种方法SELECT 江西 AS 地区,Sum(入学成绩) AS 总成绩FROM 生源情况WHERE 地区=江西或SELECT 地区,Sum(入学成绩) AS 总成绩FROM 生源情况group by 地区having 地区=江西6. 查询与某教师”21010287”同一时间监考的其他教师的教师编号。（采用自联接完成）SELECT 教师监考B.教师编号FROM 教师监考 as 教师监考A INNER JOIN 教师监考 as 教师监考B ON 教师监考A.场次=教师监考B.场次WHERE 教师监考A.教师编号=21010287 and 教师监考B.教师编号210102877. 找出没有安排场次的考试。（采用Exists运算）SELECT 场次from 场次where not exists(select * from 学生考试 where 场次.场次=学生考试.场次)