考试管理——第9章查询练习题目(有答案)

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 生源情况 group by 地区ORDER BY avg(入学成绩) DESCc).

2、 基于“生源情况”表，找出年龄平均值最高的班级。SELECT TOP 1注意top不在相等值之间进行选择 班级名称, AVG(year(date()-year(出生日期) as 平均年龄FROM 生源情况GROUP BY 班级名称ORDER BY AVG(year(date()-year(出生日期) DESCd). 基于“生源情况”表，列出学生人数少于100的地区。select 地区, count(*) as 学生人数from 生源情况group by 地区having count(*)1009.2联接查询练习a). 列举学号为“200894006”的同学所参加的考试的场次、教室和课程名称。s

3、elect 场次,教室,课程名称from 学生考试 inner join 课程 on 学生考试.课程编号=课程.课程编号where 学号=200894006或select 场次,教室,课程名称from 学生考试,课程 where 学号=200894006 and 学生考试.课程编号=课程.课程编号b). 列举学号为“200894006”的同学所参加的每场考试的监考教师的姓名。select 姓名from (学生考试 inner join 教师监考 on 学生考试.场次=教师监考.场次 and 学生考试.课程编号=教师监考.课程编号 and 学生考试.教室=教师监考.教室)inner join 教

4、师 on 教师监考.教师编号=教师.教师编号where 学号=200894006或select 姓名from 学生考试,教师监考,教师 where 学号=200894006 and 学生考试.场次=教师监考.场次and 学生考试.课程编号=教师监考.课程编号 and 学生考试.教室=教师监考.教室 and 教师监考.教师编号=教师.教师编号c). 列举来自“浙江”的学生的所在学院名称、班级名称、学号和姓名。SELECT 学院名称, 班级名称, 学号, 姓名FROM (学院 INNER JOIN 班级 ON 学院.学院编号 = 班级.学院编号) INNER JOIN 学生 ON 班级.班级编号

5、= 学生.班级编号WHERE 地区=浙江或SELECT 学院名称, 班级名称, 学号, 姓名FROM 学院,班级,学生 WHERE 地区=浙江 and 学院.学院编号 = 班级.学院编号 and 班级.班级编号 = 学生.班级编号d). 查询“梁子平”同学所在班级的学生人数。SELECT 班级人数FROM 班级 INNER JOIN 学生 ON 班级.班级编号 = 学生.班级编号WHERE 姓名=梁子平或SELECT 班级人数FROM 班级,学生WHERE 姓名=梁子平 and 班级.班级编号 = 学生.班级编号或select count(*)from 学生where 班级编号 in (SEL

6、ECT 班级编号 from 学生 WHERE 姓名=梁子平)或select 班级人数from 班级where 班级编号 in (SELECT 班级编号 from 学生 WHERE 姓名=梁子平)9.3 子查询a). 找出与学号“200894006”的学生同学院且同年出生的学生。第一步：先找到该同学所在学院的编号select 学院编号from 班级where 班级编号 in (select 班级编号 from 学生 where 学号=200894006)第二步：找到年份SELECT year(出生日期) from 学生 where 学号=200894006第三步：多表连接select 学生.*f

7、rom 学院,班级,学生where 学生.班级编号=班级.班级编号 and 班级.学院编号=学院.学院编号 and 学院.学院编号 in (select 学院编号from 班级where 班级编号 in (select 班级编号 from 学生 where 学号=200894006)and year(出生日期) in (SELECT year(出生日期) from 学生 where 学号=200894006)b). 例举班级数少于20的那些学院的名称和班级数。SELECT 学院名称,(select count(*) from 班级 where 学院.学院编号=班级.学院编号 ) as 班级数f

8、rom 学院where (select count(*) from 班级 where 学院.学院编号=班级.学院编号 )20c). 例举教师“罗亮”所监考的学生来自哪些班级。第一步：“罗亮”监考的场次select 场次from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)第二步：“罗亮”监考的课程编号select 课程编号from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)第三步：“罗亮”监考的教室select 教室from 教师监考 where 教师编号 in (select

9、 教师编号from 教师 where 姓名=罗亮)第四步：满足以上三个条件考试的学生的学号select 学号from 学生考试where 场次 in (select 场次from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)and课程编号 in (select 课程编号from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)and 教室 in (select 教室from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)第五步：

10、多表连接select distinct 班级名称from 班级,学生where 学生.班级编号=班级.班级编号 and 学号 in (select 学号from 学生考试where 场次 in (select 场次from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)and课程编号 in (select 课程编号from 教师监考 where 教师编号 in (select 教师编号from 教师 where 姓名=罗亮)and 教室 in (select 教室from 教师监考 where 教师编号 in (select 教师编号fr

11、om 教师 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 *此处是*好，不关心返回的哪个字段，只关心是否有记录返回 from 教师监考 where 教师.教师编号=教师监考.教师编号 and 场次=7月01日08:30-10:30)补充查询：1. 基于“生源情况”表，统

12、计最低入学成绩、最高入学成绩相差多少分。SELECT Max(入学成绩)-Min(入学成绩) FROM 生源情况2. 基于“生源情况”表，按地区统计各性别学生人数。SELECT 地区,性别,count(*) as 人数from 生源情况group by 地区,性别3. 基于“学生考试”表，列举参加考试的学生人数少于30的、课程号以11开头的课程编号及考试人数，结果按考试人数的统计值排序。SELECT 课程编号, Count(*) AS 考试人数 FROM 学生考试WHERE 课程编号 LIKE 11*GROUP BY 课程编号HAVING Count(*)30ORDER BY Count(*)或SELECT 课程编号, Count(*) AS 考试人数 FROM 学生考试GROUP BY 课程编号HAVING Count(*)30 and 课程编号 LIKE 11*ORDER BY Count(*)4. 基于“生源情况”表，求来自江西地区的学生的入学成绩总和。SELECT 江西 AS 地区,Sum(入学成绩) AS 总成绩FROM 生源情况WHERE 地区=江西或SELECT 地区,Sum(入学成绩) AS 总成绩FROM