- 数据库 : mysql8
- 编辑器 : navicate12
工作之余工作在网上找的sql练习题,看到还有些难度,练习下来会有不少提升. 原来的数据库是oracle, 我用的是mysql8(语法个别不同于oracle), 第三方sql连接工具用的是navicate12用的都是最新的 ,当然性能也很不错,例如navicate12 使用起来比以前的版本流畅许多!
第一部分: 基础强化
-
首先初始化建表语句:
-- 1）建表： "--" 加上 "空格" = 注释 注意 不能使用 "#"
CREATE TABLE Student
(
S INT,
Sname nvarchar(32),
Sage INT,
Ssex nvarchar(8)
)
CREATE TABLE Course
(
C INT,
Cname nvarchar(32),
T INT
)
CREATE TABLE Sc
(
S INT,
C INT,
score INT
)
CREATE TABLE Teacher
(
T INT,
Tname nvarchar(16)
)
-- 插入数据
insert into tudent select 1,N'刘一',18,N'男' union all
select 2,N'钱二',19,N'女' union all
select 3,N'张三',17,N'男' union all
select 4,N'李四',18,N'女' union all
select 5,N'王五',17,N'男' union all
select 6,N'赵六',19,N'女'
insert into Teacher select 1,N'叶平' union all
select 2,N'贺高' union all
select 3,N'杨艳' union all
select 4,N'周磊'
insert into Course select 1,N'语文',1 union all
select 2,N'数学',2 union all
select 3,N'英语',3 union all
select 4,N'物理',4
insert into SC
select 1,1,56 union all
select 1,2,78 union all
select 1,3,67 union all
select 1,4,58 union all
select 2,1,79 union all
select 2,2,81 union all
select 2,3,92 union all
select 2,4,68 union all
select 3,1,91 union all
select 3,2,47 union all
select 3,3,88 union all
select 3,4,56 union all
select 4,2,88 union all
select 4,3,90 union all
select 4,4,93 union all
select 5,1,46 union all
select 5,3,78 union all
select 5,4,53 union all
select 6,1,35 union all
select 6,2,68 union all
select 6,4,71
第一部分练习 大概1-20题 (难度比较大,还是有营养的)
SELECT * FROM student
-- 1. 查询“001”课程比“002”课程成绩高的所有学生的学号；
select a.S from
(select S,Score from SC where C='001') a,
(select S,Score from SC where C='002') b
where a.S=b.S and a.Score>b.Score
-- 2 查询平均成绩大于60分的同学的学号和平均成绩
select s,AVG(score) as avgscore from sc group by s having AVG(score)>60;
-- 扩展2 查询平均成绩大于60分的同学的学号和平均成绩 和学生名称
select s.s , s.sname , a.avgscore from student +s,(select s,AVG(score) as avgscore from sc group by s having AVG(score)>60) a where s.s = a.s
-- 3 查询所有同学的学号，姓名，选课数，总成绩
-- 分析: 比较简的一题 , 聚合函数和分组函数配合使用,再加上联表查询
select s.s as '学号', s.sname as '姓名', count(sc.c) as '选课数', sum(sc.score) as '总成绩' from SC sc , Student s
where sc.s = s.s
group by s.s , s.sname
-- 4 查询性 ‘李’ 的老师的个数count(distinct(tname))
-- 经过实际上分析， 这里不能使用distinct
-- 因为如果有两个 同名的老师（但是两个人） 用distinct的话，就变成了一个，造成误差
select count(tname) as count
from teacher
where tname like '叶%';
-- 5 查询没学过“叶平” 老师课的同学的学号、姓名；
-- 分析： 先查到学过的， 然后使用not in
-- 错误分析：
select distinct(s.s) from student s, course c , sc sc, teacher t
where s.s = sc.s and sc.c = c.c and c.t = '叶平' -- （这里c.t是教师号，而不能当作教师姓名 ，所以是错的）
-- 正解：
select distinct(s.s) from student s, course c , sc sc, teacher t
where s.s = sc.s and sc.c = c.c and c.t = t.t and t.tname = '叶平';
-- (注意： 使用distinct 不然会有重复的值)
-- 使用not in
select s.s,s.sname from student s where s.s not in (
select distinct(s.s) from student s, course c , sc sc, teacher t
where s.s = sc.s and sc.c = c.c and c.t = t.t and t.tname = '叶平'
)
-- 6 查询学过 “001” 并且也学过编号 “002” 课程的同学的学号、姓名；
-- 1）. 先查询学过 001 课程的
select s.s , s.sname from student s , course c , sc sc
where s.s = sc.s and sc.c = c.c and c.c = 1;
-- 2）. 先查询学过 002 课程的
select s.s , s.sname from student s , course c , sc sc
where s.s = sc.s and sc.c = c.c and c.c = 2;
-- 取交集 mysql 要借助 inner join 不同于 oracle
select a.* from
(select s.s as a, s.sname from student s , course c , sc sc
where s.s = sc.s and sc.c = c.c and c.c = 1) a
inner JOIN
(select s.s as b, s.sname from student s , course c , sc sc
where s.s = sc.s and sc.c = c.c and c.c = 2) b
on a.a= b.b
-- 注： 这里是MySQL 不能用 intersect(oracle的用法) 要用内连接 取共同的部分（即交集）
-- 7 查询学过“叶平”老师所教的所有课的同学的学号、姓名；
-- 分析：1.先查出叶平老师所教的所有课程 总数 k课程表和教师表关联即可
select count(c.c) from course c , teacher t
where c.t = t.t and t.tname = '叶平'
-- 2. 查出学过所有叶平老师的课程的 同学的 “学号”
-- 这里满足两个条件即可保证查到学了叶平老师的所有课
-- 1）. 所学课程为 “叶平” 老师教的 2）. 所学叶平老师的课程总数 = 叶平老师所教所有课的总数
select sc.s from sc sc, course c , teacher t
where sc.c = c.c and c.t = t.t and t.tname = '叶平'
group by sc.s
having count(sc.c) =
(
select count(c.c) from course c , teacher t
where c.t = t.t and t.tname = '叶平'
)
-- 3. 以2 为基础， 查出 学号， 和 姓名
select s.s,s.sname from student s
where s.s in
(
select sc.s from sc sc, course c , teacher t
where sc.c = c.c and c.t = t.t and t.tname = '叶平'
group by sc.s
having count(sc.c) =
(
select count(c.c) from course c , teacher t
where c.t = t.t and t.tname = '叶平'
)
)
-- ok 完工 该题看起比较复杂一点点
-- 这里是解题关键 “这里满足两个条件即可保证查到学了叶平老师的所有课
-- 1）. 所学课程为 “叶平” 老师教的 2）. 所学叶平老师的课程总数 = 叶平老师所教所有课的总数”
-- 8 查询课程编号 ‘002’ 的成绩比课程编号 ‘001’ 课程低的所有同学的 学号 、 姓名
-- 分析： 首先将题目分解 先查 002 和 001 的每个同学的成绩
(select sc.score as a ,sc.s as s from sc sc where sc.c = 1 ) a;
(select sc.score as b ,sc.s as s from sc sc where sc.c = 2 ) b;
-- 加上条件即可 注意 表间关联 (尤其a 和 b 表之间的关联 a.s=b.s 保证是同一个学生之间的比较)
select s.s , s.sname from student s ,
(select sc.score as score ,sc.s as s from sc sc where sc.c = 1 ) a,
(select sc.score as score ,sc.s as s from sc sc where sc.c = 2 ) b
where s.s = a.s and s.s = b.s and a.s = b.s and a.score > b.score
-- 总结： 本题关键点在于 表之间的关联 保证是同一个学生之间的两门课程的比较
-- 9 查询有课程成绩小于60 分的同学的学号、姓名
-- 分析 首先是 在成绩表 中查出 成绩小于60分的同学的学号
select distinct(sc.s) from sc sc where sc.score < 60
-- 将上一步作为子查询 在学生表中 查出 学号 和姓名了
select s.s,s.sname from student s
where s.s in
( select distinct(sc.s) from sc sc where sc.score < 60)
-- 总结： 比较简单的一道题，注意 多个结果的话用in，
-- 用distinct(因为在成绩表中学号和分数是一对多的关系， 即 一个同学可能有多门课程小于60 ，我们只需要取一个即可)
-- 10 查询没有学全所有课的同学的序号、姓名
-- 分析： 直接查询没有学全的比价麻烦，so可以先查出学全的同学的姓名，然后再使用not in 即可
-- 1. 查出学全所有课程的同学
-- 错误示例：
select sc.s from course c , sc sc
where sc.c = c.c and count(c.c) =
(
select distinct(count(c.c)) from course c
) -- count 是分组函数需要和 group by 一起使用
-- 正解：
select s from sc
group by sc.s having count(distinct(sc.c)) =
(
select distinct(count(c.c)) from course c
)
--加上not in
select s.s , s.sname from student s
where s.s not in
(
select s from sc
group by sc.s having count(distinct(sc.c)) =
(
select distinct(count(c.c)) from course c
)
)
-- 总结： HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
-- HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
-- 11 查询至少有一门课与学号 “001” 的同学所学相同的同学的学号和姓名；
-- 分析 先查出学号 001 同学所学的所有课程,再使用in
select distinct(sc.c) from SC sc where sc.s = 1
-- 学生表和成绩表 关联
select distinct(s.s) , s.sname from student s , SC sc
where s.s = sc.s and sc.c in
(
select distinct(sc2.c) from SC sc2 where sc2.s = 1
)
order by s.s asc
-- 总结： 这里如果不加 distinct 的话，比如a学生有3们课程和1学生一样，就会有3条重复的数据。
-- 还有就是如果子查询里和总查询里都用到相同的表 ， 最好起个别名 区分开来
-- 12 ?????????????????????????????????????????????????????????????
-- 把“SC” 表中“叶平” 老师教的课的成绩都更改为此课程的平均成绩
-- 先查出叶平老师所教每门课的平均成绩
select sc.c as c, avg(sc.score) as avgcount
from SC sc,teacher t ,course c
where sc.c = c.c and c.t = t.t and t.tname = '叶平'
group by c
-- 然后再作更新， 只要有学生学叶平老师的课的 全部修改为该们课程的平均成绩
-- 错误示例：
update SC sc1 set sc1.Score =
(
select a.score from ( select avg(sc.score) as score
from SC sc,teacher t ,course c
where sc.c = c.c and c.t = t.t and t.tname = '叶平') a
)
where sc1.c in
(
select distinct(sc2.c) from course c , teacher t, SC sc2
where t.t = c.t and c.c = sc2.c and t.tname = '叶平'
)
-- 注：上述执行报错：You can't specify target table '表名' for update in FROM clause
-- 即不能先select出同一表中的某些值，再update这个表(在同一语句中)
-- 即不能依据某字段值做判断再来更新某字段的值。
-- 解决： 将SELECT出的结果再通过中间表SELECT一遍，这样就规避了错误。
select a.score from (
select sc.c, avg(sc.score) as score
from SC sc,teacher t ,course c
where sc.c = c.c and c.t = t.t and t.tname = '叶平'
) a
-- 14 查询和 “002” 号的同学学习的课程完成相同的其他同学学号和姓名
-- 分析：满足 课程数量相等，且 使用 ‘in’ 即可确保课程完全相同
-- 1. 查询 002 学生学习的课程总数
select count(distinct(sc.c)) from SC sc where sc.s = 2
-- 2. 查询sc表中满足条件(和002所学课程完全相同的学号)的学号
select distinct(sc.s) from SC sc where sc.c in (select sc2.c from SC sc2 where sc2.s = 2)
group by sc.s having count(distinct(c)) =
(select count(distinct(sc.c)) from SC sc where sc.s = 2)
-- 3. 查询学生表 得出最终结果
select s.s, s.sname from student s
where s.s != 2 and s.s in
(
select distinct(sc.s) from SC sc where sc.c in (select sc2.c from SC sc2 where sc2.s = 2)
group by sc.s having count(distinct(sc.c)) =
(select count(distinct(sc.c)) from SC sc where sc.s = 2)
)
-- 15 删除学习“叶平” 老师课的SC 表记录、
-- 比较简单的额一道题， 首先查询叶平老师有哪些课程 需要 课程表、成绩表、教师表 三张表关联
select distinct(sc.c) from course c , teacher t , SC sc
where sc.c = c.c and c.t = t.t and t.tname = '叶平'
-- 删除sc表中的 课程为 上步查出的 记录
delete from SC where c in
(
select distinct(sc.c) from course c , teacher t , SC sc
where sc.c = c.c and c.t = t.t and t.tname = '叶平'
)
-- 16 向SC表中插入一些记录，这些记录要求符合以下条件：
-- ① 没有上过编号“002” 课程的同学学号；② 插入“002” 号课程的平均成绩
-- 分析 如题目一样 2个子模块，分别查询出来 ，组后执行插入操作
-- 1 找出没有上过编号 002 课程的同学
-- (注：不能用sc.c != 2 因为学号和所学的课程是一对多的关系，之能用 = 2 然后使用not in的方式)
select s.s from student s where s.s not in
(select distinct(sc.s) from SC sc where sc.c = 2)
-- 2 查询出002 课程的平均成绩
select avg(score) as avg_score from SC where c = 2
-- 将1 和 2 组合 执行插 入
insert into SC
select s.s, 2, (select avg(score) as avg_score from SC where c = 2)
from student s
where s.s not in
(select distinct(sc.s) from SC sc where sc.c = 2)
-- 总结： 本题先将条件拆分，分别查询然后组合，注：将select 出来的数据直接插入到sc表中
-- 17 按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语” 三门的课程成绩，按如下形式显示
-- 学生ID，语文，数学，英语，有效课程数，有效平均分；
-- 分析 关键 子查询中的学号 要和 主查询的 学号 关联，保证一行中的数据是一个人的
select t.s as '学生ID' ,
(select score from SC where s = t.s and c = 2) as '语文' ,
(select score from SC where s = t.s and c = 3) as '数学' ,
(select score from SC where s = t.s and c = 4) as '英语' ,
count(t.c) as '有效课程数',
avg(t.score) as '有效平均分'
from SC t
group by t.s
order by avg(t.score)
-- 特别注意： 和聚合函数一起查询的 要放在group by 后
-- （这里需要是表中的字段才需要放在其后，比如常量或者如上就不需要）
-- 18 查询各科成绩最高和最低的分，如下形式显示： 课程ID, 最高分，最低分
-- 一个简单的 聚合函数和分组 配合使用
select sc.c as '课程ID', MAX(score) as '最高分' , MIN(score) as '最低分'
from SC
group by sc.c
-- 19 按各科平均成绩从低到高和及格率的百分数从高到低顺序; (难度4颗星)
-- 分析 这个 合格率 = 合格的人数/总人数
-- 难点在于如何求 合格率(如何求合格的人数)
-- MySQL 数字类型转换函数(concat/cast)
-- 1)、将Int 转为varchar经常用 concat函数，比如concat(8,’0′) 得到字符串 ’80′。
-- 2)、将varchar 转为Int 用 cast(a as signed) a为varchar类型的字符串。
-- 1. 查出每一门课的 课号 和 合格的人数
SELECT sc.c ,
concat(
left((sum( case when ifnull(sc.score,0)>=60 then 1 else 0 end) / count(*) * 100), 5) , '%'
) as '合格率%'
from sc group by sc.c
-- 2. 合格率搞出来后 查询, 按 课程号, 该课程平均成绩, 该课程的合格率 展示
select sc.c as '课程号', c.cname as '课程名称' , left(avg(sc.score),5) as '平均成绩' ,
concat
(
left((sum( case when ifnull(sc.score,0)>=60 then 1 else 0 end) / count(*) * 100), 5) , '%'
)
as '合格率%'
from SC sc ,course c
where sc.c = c.c
group by sc.c , c.cname
order by '平均成绩' asc , '合格率%' desc
-- 总结: 这是一道难度较大的题,做出来看到整整齐齐的结果也挺有成就感的 ^_^
-- 涉及到的知识点有: left字符串截取,sum(),case when xxx then x else x end , concat字符串连接 ,ifnull
-- 注意和其他数据库之间的区别
-- 20 查询如下课程平均成绩和及格率的百分数(备注:需要在1行内显示):
-- 企业管理(002) , OO&UML （003），数据库（004）
-- 分析:该题也就是对上题求合格率用到的知识点的强化 ,
-- 首先看清题目,大概是求没门课的 平均成绩和合格率百分数 ,并在一行显示
-- 这里没有用group by 所以求合格率时 分母需要 专门求出来
select
left(SUM(CASE WHEN C='002' THEN Score ELSE 0 END)/SUM(CASE C WHEN '002' THEN 1 ELSE 0 END),5) as '企业管理平均分',
-- 这里注意 如果使用avg(score)的话 要注意 加上条件
-- 在课号 c= 2 单门课程的平均分,否则 会变成所有课程的平均分也可以使用子查询的方式
concat(left(sum(case when sc.c = 2 and ifnull(sc.score,0)>=60 then 1 else 0 end)/
( select count(*) f0 rom sc where c = 2) * 100 , 5),'%') as '企业管理合格率'
from SC sc
-- 总结: 其他两门课程 类似 这里不再多写了
-
21- 47题 前几题比较有营养,后边简单,就当做基础强化
-- 21 查询不同老师所教不同课程平均分从高到低显示
select c.c as '课程号', c.cname as 'cname' , t.t as '教师号' , t.tname as '教师姓名' ,
round(avg(sc.score),2) as '平均成绩'
from SC sc, Course c , Teacher t
where sc.c = c.c and c.t = t.t
group by c.c , t.tname, t.t ,c.cname
order by avg(sc.score) desc
-- 注: 数字类型的 如果用left 等截取后, 变成字符串 就是去了排序的意义,这时可以使用 round() 函数
-- 可以看到 查询目标 非聚合函数还有其他多个字段,但是group by 只写一个 语法没有报错, 但是会造成结果误差, 建议还是全部写上 是肯定不会遗漏的
-- 22 统计各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
-- 分析: 主要用到了 sum聚合函数 和 case when xx then xx else xx end 以及bewteen and 的结合使用
select sc.c , c.cname as '课程名称' ,
sum(case when sc.score BETWEEN 85 and 100 then 1 else 0 end) as '[85-100]',
sum(case when sc.score BETWEEN 70 and 85 then 1 else 0 end) as '[85-70]',
sum(case when sc.score BETWEEN 60 and 70 then 1 else 0 end) as '[70-60]',
sum(case when sc.score BETWEEN 0 and 60 then 1 else 0 end) as '[ <60]'
from SC sc, Course c
where sc.c = c.c
group by c.c ,c.cname
order by sc.c
-- 23 查询学生的平均成绩及名次;
-- 分析: 该题难点在于排名
# mysql本身不像oracle那样使用 rownum
# 来显示行号的功能.但是可以使用设置变量的方式来实现这一功能.
# mysql使用 @ 定义用户变量(有效期是整个会话), @@ 定义系统变量.
#定义一个变量,变量名随意 注意这里使用分号,但是执行时需要和下边的一块执行
set @rowno := 0;
select a.* , (@rowno := @rowno +1) as '名次'
from (
select s.s, s.sname as 'name', round(avg(sc.score),2) as 'avgscore'
from Student s , SC sc
where s.s = sc.s
group by s.s ,s,sname
order by avg(sc.score) desc
) a
where @rowno < 5 # 增加该条件可以显示 指定的前多少名
-- 24 查询各科成绩前三名的记录: (不考虑成绩并列情况)
select s.s , c.cname, sc.score
from student s, sc sc , course c
where s.s = sc.s and c.c = sc.c
group by c.c
order by c.c desc,sc.score desc
-- 思路 : 计算比当前大的 然后计数. 比当前大的数量有三个, 那当前这个自然不是前3了
-- bug : 相同的成绩就可能会出现排名并排现象
-- 规律 : 出现bug需要满足以下两个条件 :
-- 1).如取排名前x个的 , 有 y(y>=x) 个相同的成绩
-- 2).以上成绩相同的成绩必须排名前x里(不考虑并列)
-- 新知 : 在子查询的条件里 可以用到 外部查询的表(即和外部的表做表间关联查询),反之外部不可以用子查询的
select sc2.s as '学号',s.sname as '姓名', c.c as '课程编号', c.cname as '课程名称', sc2.score as '分数'
from SC sc2 ,student s , course c
where ( select count(*) as count from SC sc1 where sc1.c = sc2.c and sc2.score < sc1.score) < 3 -- 该数决定前多好位, < > 决定前排 后排
and s.s = sc2.s and c.c = sc2.c
order by sc2.c , sc2.score desc
-- select * from SC sc where sc.c = 4 order by sc.score desc
-- update sc set score = 71 where score = 58
-- 25 查询每门课程被选修的学生数
-- 分析: 课程表和成绩表关联 , 关键点: 查询成绩表, 以课程号分组的 学生人数
select c.c as '课程号' , c.cname as '课程名称' , count(distinct(sc.s)) as '人数'
from course c , SC sc
where sc.c = c.c
group by sc.c
-- 26 查询出只选修了一门课程的全部学生的学号和姓名
-- 分析: 跟上题类似 查成绩表, 反过来 以学号分组, 查课程数.
-- group by count(distinct(sc.c))=1
-- 错误示例 (group by 后边只能加分组的字段,要想再加条件需要加在having)
select s.s as '学生号' , s.sname as '学生姓名'
from Student s
where s.s in (
select sc.s from SC sc
group by sc.s
having count(distinct(sc.c))=1 )
-- 27 查询男生, 女生的人数
-- 分析:很简单, 但是要优化一下显示
select m.m as '男生人数' , w.w as '女生人数'
from
(select count(s.s) as m from student s where s.Ssex = '男' ) m ,
(select count(s.s) as w from student s where s.Ssex = '女' ) w ;
-- 28 查询姓 '张' 的学生名单
select s.s as '学号', s.sname as '姓名'
from Student s
where s.sname like '张%' ;
-- 29 查询同名同姓的学生名单, 并统计同名人数;
-- 分析: 比较简单, 以学生姓名分组,having条件 count(姓名) > 1查出重名的学号
select s.sname , count(sname) as 'sameCount'
from Student s
group by s.sname
having count(sname) >1
-- 造数据测试
-- 30 查询每门课程的平均成绩, 结果按平均成绩升序排列,
-- 平均成绩相同时, 按课程号降序排列 , 虽简单但,通过该题应清楚掌握排序问题
-- 分析: 成绩表SC asc 小到大 ; desc 大到小
select sc.c as '课程号' , LEFT(AVG(sc.score),5) as '平均成绩'
from SC sc
group by sc.c
order by AVG(sc.score) , sc.c desc
-- 31 查平均成绩大于85的所有学生的学号, 姓名和平均成绩;
select s.s, s.sname ,left(AVG(SC.score),5) as '平均分'
from Student s , SC sc
where s.s = sc.s
group by s.s
having AVG(SC.score)>85
-- 32 查询课程名称为'数学' , 且分数低于60的学生姓名和分数;
-- 分析: 简单的三表查询
select s.sname as '姓名' , sc.score as '分数'
from SC sc , course c ,Student s
where sc.c = c.c and sc.s = s.s and c.cname = '数学' and sc.score < 60
-- 33 查询所有学生的选课情况
-- 次题 主题应该是学生,所以排序应该先学生后 课程
select s.s,s.Sname,c.c,c.Cname
from Student s,SC sc,Course c
where s.s = sc.s and c.c = sc.c
order by s.s ,c.c
-- 34 查询任何一门课程成绩在70分以上的姓名,课程名称和分数;
-- 分析: 简单的联表 查询
select distinct(s.s) , s.sname , c.cname , sc.score
from Student s , SC sc, Course c
where s.s = sc.s and sc.c = c.c and sc.score >=70
-- 35 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
-- 简单量表关联查询
select s.s , s.sname
from Student s , SC sc
where sc.s = s.s and sc.c = 3 and sc.score >80
-- 36 查询已经选了课程的学生人数(很简单)
select count(distinct(sc.s)) as 'stucount' from SC sc
-- 37 查询选修'杨艳' 老师所授课的学生中, 成绩最高的学生姓名及其成绩;
-- 分析
-- 错误示例: 这里s.s s.sname 不能和 max(sc.score) 一起 会对应不上
select s.s , s.sname , max(sc.score)
from Student s ,SC sc ,Teacher t,course c
where s.s = sc.s and sc.c = c.c and c.t = t.t and t.tname = '杨艳'
-- 正确
select s.s , s.sname , sc.score
from Student s ,SC sc ,Teacher t,course c
where s.s = sc.s and sc.c = c.c and c.t = t.t and t.tname = '杨艳'
and sc.score = (
select max(sc2.score) from SC sc2 where sc.c = sc2.c
-- 这里子查询和外查询做个联结查询(固定是查询杨艳课程的)
)
-- 38 查询各个课程及相应的选修人数
select sc.c as '课程号', c.cname as '课程名', count(sc.s) as '选课人数'
from SC sc , Course c
where sc.c = c.c
group by sc.c
-- 39 查询不同课程但成绩相同的学生的学号, 课程号, 学生成绩;
-- 自联结查询 注意加上sc1.s != sc2.s 条件
select distinct(sc1.s) , sc1.c , sc1.score
from SC sc1, SC sc2
where sc1.c = sc2.c and sc1.score = sc2.score and sc1.s != sc2.s
order by sc1.score asc
-- 40 查询每门课程成绩最好的前两名
-- 该题 方法类似24题
select s.s, s.sname , sc2.c as '课程号', c.cname as '课程名', sc2.score
from Student s , SC sc2 , Course c
where (select count(sc1.s) from SC sc1 where sc1.c = sc2.c and sc1.score > sc2.score) < 2
and sc2.s = s.s and sc2.c = c.c
order by sc2.c , sc2.score desc
-- 41 统计每门课程的学生选修人数(超过10人的课程才统计). 要求输出课程和选修人数,
-- 查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号降序排列
select sc.c , count(distinct s) as 'stuCount' from SC sc
group by sc.c
having count(distinct s) >=10
order by stuCount desc , sc.c asc
-- 42 检索至少选修两门课程的学生学号
select distinct sc.s from SC sc
group by sc.s
having count(sc.c) >=2
-- 43 查询全部学生都选修的课程的课程号和课程名;
-- 分析 首先子查询得出所有的学生数 , 再以课程号分组 having 学号 = 学生总数
select count(distinct(s.s)) from Student s
select c.c as '课程号' , c.cname as '课程名'
from Course c
where c.c in
(
select sc2.c from SC sc2
group by sc2.c
having count(distinct sc2.s)= (select count(distinct(s.s)) from Student s)
)
-- 44 查询没学过 '叶平' 老师讲授的任一门课程的学生姓名;
-- 分析 先查出学过"叶平"老师的学生学号, 然后其他的则是没学过的
select s.sname from student s
where s.s not in (
select sc.s
from SC sc , Course c, Teacher t
where sc.c = c.c and c.t = t.t and t.tname = '叶平' )
-- 45 查询两门以上不及格课程的同学的学号及其平均成绩;
-- 分析: 复杂查询首先分步
-- 1 先查出有两门不及格的学生的学号
select s.s ,s.sname , avg(sc2.score) as '平均成绩'
from SC sc2 , Student s
where s.s in (
select sc.s
from SC sc
where sc.score <60
group by sc.s
having count(sc.c)>1
-- 这里数字代表不及格的课程数
) and s.s = sc2.s
group by sc2.s
order by sc2.s
-- 46 检索'004'课程分数小于60 , 按分数降序 排列的同学学号; (简单)
select sc.s from SC sc
where sc.c = 4 and sc.score < 60
order by sc.score desc
-- 47 删除 002 同学的 001 课程的成绩; (简单)
delete from SC sc WHERE sc.s = 2 and sc.c = 1
感悟: 到此基础部分这暂时到这里 , 经过逐题的仔细认真研究,真真切切的得到了很大的提升. 后续还会有进阶的学习.
之所以感到眼前迷雾重重,那是因为你没有深入进去. 找到问题的根源,拨开云雾,一切便会豁然开朗.
世上无难事,只怕有心人!
扫一扫
1180
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
