二、操作表
1、自行创建测试数据；
-- 创建数据库
create database practice charset utf8;
-- 1、自行创建测试数据；
--
-- 创建班级表:class
create table class(
cid int primary key auto_increment,
caption char(10),
grade_id int not null
);
-- 插入数据到班级表：
insert into class(caption,grade_id) values ('一年一班',1),('二年一班',2),('三年二班',3);
-- 创建学生表：student
create table student(
sid int primary key auto_increment,
sname char(10) not null,
gender enum("男","女") default "男",
class_id int not null,
foreign key(class_id) references class(cid)
on delete cascade
on update cascade);
--插入数据到student表
insert into student(sname,gender,class_id) values ('乔丹','女',1),('艾弗森','女',1),('科比','男',2);
--创建老师表：teacher
create table teacher(
tid int primary key auto_increment,
tname char(10) not null
);
-- 插入数据到teacher表
insert into teacher(tname) values ('张三'),('李四'),('王五'); -- 创建课程表：course
create table course(
cid int primary key auto_increment,
cname char(10) not null,
teacher_id int,
foreign key(teacher_id) references teacher(tid)
on delete cascade on update cascade
);
-- 插入数据到课程表：
insert into course(cname,teacher_id) values ('生物',1),('体育',1),('物理',2);
--
-- 创建成绩表：score
create table score(
sid int primary key auto_increment,
student_id int not null,
course_id int not null,
score int default 0,
foreign key(student_id) references student(sid) on delete cascade on update cascade,
foreign key(course_id) references course(cid) on delete cascade on update cascade
);
--插入数据到score
insert into score(student_id,course_id,score) values (1,1,60),(1,2,59),(2,2,99);
--
-- 创建年级表：class_grade
create table class_grade(
gid int primary key auto_increment,
gname char(10) not null
);
-- 插入数据到class_grade
insert into class_grade(gname) values ('一年级'),('二年级'),('三年级');
--
-- 创建班级任职表
create table teach2cls(
tcid int primary key auto_increment,
tid int not null,
cid int not null,
foreign key(tid) references teacher(tid) on delete cascade on update cascade,
foreign key(cid) references course(cid) on delete cascade on update cascade
);
-- 插入数据到teach2cls
insert into teach2cls(tid,cid) values (1,1),(1,2),(2,1),(3,2); --更多测试数据
insert class_grade(gname) values ('四年级'),('五年级');
insert class(caption,grade_id) values ('四年一班',4),('四年二班',4),('五年一班',5),('五年二班',5);
insert teacher (tname) values ('甜甜'),('小花'),('阿狗'),("阿猫");
insert course(cname,teacher_id) values('语文',4),('数学',5),('英语',6),('化学',3),('美术',4);
insert teach2cls (tid,cid) values(4,6),(5,5),(6,4),(6,2); insert student(sname,gender,class_id) values
('alsa','女',4),('lily','女',5),('jack','男',6),('alsa','女',7),
('lucy','女',1),('bob','男',3),('乔丹','女',4),('nico','女',5),
('alex','男',3),('张良','男',4),('lily','女',2),('jack','男',7),
('kitty','女',5),('诸葛','男',5),('妲己','女',4),('甄姬','女',6); insert score (student_id, course_id,score) values
(4,1,58),(5,2,88),(6,3,73),(16,4,75),(16,5,92),
(7,4,65),(8,5,98),(9,6,72),(16,6,88),(17,7,63),(18,7,77),(19,7,90),
(11,3,81),(10,5,85),(12,1,63),(12,2,87),
(13,6,55),(14,3,55),(15,5,41),(6,1,80);
-- 2、查询学生总人数；
select count(sid) from student;
-- +------------+
-- | count(sid) |
-- +------------+
-- | 19 |
-- +------------+
--
-- 3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
select
sid,
sname
from
student
where
sid
in(
select student_id from
score inner join course on score.course_id = course.cid
where cname='生物' or cname='物理' and score>=60
group by student_id
);
-- +-----+--------+
-- | sid | sname |
-- +-----+--------+
-- | 1 | 乔丹 |
-- | 4 | alsa |
-- | 12 | alex |
-- | 6 | jack |
-- | 11 | nico |
-- +-----+--------+
--
-- 4、查询每个年级的班级数，取出班级数最多的前三个年级；
select
t1.gname,t2.class_count
from
class_grade t1
inner join
(select
grade_id,count(cid) as class_count
from
class
group by
grade_id) as t2
on
t1.gid=t2.grade_id
order by
t2.class_count desc
limit 3;
-- +-----------+-------------+
-- | gname | class_count |
-- +-----------+-------------+
-- | 四年级 | 2 |
-- | 五年级 | 2 |
-- | 一年级 | 1 |
-- +-----------+-------------+
--
-- 5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
select
t1.sid,
t1.sname,
t2.avg_score
from
student as t1
inner join(
select
student_id,avg(score) as avg_score
from
score
group by
student_id
having
avg(score) in(
(select avg(score) as lowest_score from score group by student_id order by avg(score) asc limit 1),
(select avg(score) as highest_score from score group by student_id order by avg(score) desc limit 1)
)
) as t2
on t1.sid=t2.student_id;
-- +-----+-----------+-----------+
-- | sid | sname | avg_score |
-- +-----+-----------+-----------+
-- | 2 | 艾弗森 | 99.0000 |
-- | 15 | jack | 41.0000 |
-- +-----+-----------+-----------+
--
-- 6、查询每个年级的学生人数；
select
gname,
student_count
from
class_grade as t1
inner join
(select
grade_id,count(sid) as student_count
from
student
inner join
class
on student.class_id = class.cid
group by
grade_id) as t2
on t1.gid=t2.grade_id;
-- +-----------+---------------+
-- | gname | student_count |
-- +-----------+---------------+
-- | 一年级 | 3 |
-- | 二年级 | 2 |
-- | 三年级 | 2 |
-- | 四年级 | 8 |
-- | 五年级 | 4 |
-- +-----------+---------------+
--
-- 7、查询每位学生的学号，姓名，选课数，平均成绩；
select
t1.sid,t1.sname,count_course,avg_score
from
student as t1
inner join
(select
student_id,count(course_id) as count_course,avg(score) as avg_score
from score
group by
student_id) as t2
on t1.sid=t2.student_id;
-- +-----+-----------+--------------+-----------+
-- | sid | sname | count_course | avg_score |
-- +-----+-----------+--------------+-----------+
-- | 1 | 乔丹 | 2 | 59.5000 |
-- | 2 | 艾弗森 | 1 | 99.0000 |
-- | 5 | lily | 1 | 88.0000 |
-- | 6 | jack | 2 | 76.5000 |
-- | 8 | lucy | 1 | 98.0000 |
-- | 9 | bob | 1 | 72.0000 |
-- | 12 | alex | 2 | 75.0000 |
-- | 13 | 张良 | 1 | 55.0000 |
-- | 18 | 妲己 | 1 | 77.0000 |
-- +-----+-----------+--------------+-----------+ -- 8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
select
t3.sid,
t3.sname,
t4.cname,
t3.score
from
course as t4
inner join
(select
sid,
sname,
course_id,
score
from
student as t1
inner join
(select
student_id,
course_id,
score
from
score
where
student_id=2 and
score in(
(select score from score where student_id=2 order by score limit 1),
(select score from score where student_id=2 order by score desc limit 1)
)
)as t2
on t1.sid=t2.student_id) as t3
on t4.cid=t3.course_id;
-- +-----+-----------+--------+-------+
-- | sid | sname | cname | score |
-- +-----+-----------+--------+-------+
-- | 2 | 艾弗森 | 体育 | 99 |
-- | 2 | 艾弗森 | 数学 | 68 |
-- +-----+-----------+--------+-------+
--
-- 9、查询姓“李”的老师的个数和所带班级数； select
count_li,
count(teach2cls.cid)as count_class
from
teach2cls
inner join(
select
tid,
count(tid)as count_li
from
teacher
where
tname like "李%"
)as teach
on
teach2cls.tid=teach.tid
group by
teach2cls.tid;
-- +--------------------+-----------------+
-- | 姓李老师个数 | 所带班级数 |
-- +--------------------+-----------------+
-- | 1 | 1 |
-- +--------------------+-----------------+
--
-- 10、查询班级数小于5的年级id和年级名；
select
gid,
gname
from
class_grade
where
gid in
(select
grade_id
from
class
group by
grade_id
having
count(cid)<5);
-- +-----+-----------+
-- | gid | gname |
-- +-----+-----------+
-- | 1 | 一年级 |
-- | 2 | 二年级 |
-- | 3 | 三年级 |
-- | 4 | 四年级 |
-- | 5 | 五年级 |
-- +-----+-----------+
--
-- 11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果 如下：
-- 班级id 班级名称 年级 年级级别
-- 1 一年一班 一年级 低
select
class.cid as "班级id",
class.caption as "班级名称",
class_grade.gname as "年级",
case
when class_grade.gid between 1 and 2
then "低"
when class_grade.gid between 3 and 4
then "中"
when class_grade.gid between 5 and 6
then "高"
else
0
end as "年级级别"
from
class
inner join
class_grade
on class.grade_id=class_grade.gid
-- +----------+--------------+-----------+--------------+
-- | 班级id | 班级名称 | 年级 | 年级级别 |
-- +----------+--------------+-----------+--------------+
-- | 1 | 一年一班 | 一年级 | 低 |
-- | 2 | 二年一班 | 二年级 | 低 |
-- | 3 | 三年二班 | 三年级 | 中 |
-- | 4 | 四年一班 | 四年级 | 中 |
-- | 5 | 四年二班 | 四年级 | 中 |
-- | 6 | 五年一班 | 五年级 | 高 |
-- | 7 | 五年二班 | 五年级 | 高 |
-- +----------+--------------+-----------+--------------+
--
-- 12、查询学过“张三”老师2门课以上的同学的学号、姓名；
select
t1.sid,
t1.sname
from
student as t1
inner join
(select
student_id
from
score
inner join
course
on score.course_id=course.cid
where
course.teacher_id=
(select
tid
from
teacher
where
tname="张三")
group by
student_id
having
count(course_id)>=2) as t2
on t1.sid=t2.student_id;
-- +-----+--------+
-- | sid | sname |
-- +-----+--------+
-- | 1 | 乔丹 |
-- | 12 | alex |
-- +-----+--------+
--
-- 13、查询教授课程超过2门的老师的id和姓名；
select
teacher.tid,
teacher.tname
from
teacher
inner join(
select
teacher_id
from
course
group by
teacher_id
having
count(cid)>=2) as t1
on teacher.tid=t1.teacher_id;
-- +-----+--------+
-- | tid | tname |
-- +-----+--------+
-- | 1 | 张三 |
-- | 4 | 甜甜 |
-- +-----+--------+
--
-- 14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
select
sid,
sname
from
student
where
sid in(
select
student_id
from
score
where
course_id=1 or course_id=2
group by
student_id
having
count(course_id)=2);
-- +-----+--------+
-- | sid | sname |
-- +-----+--------+
-- | 1 | 乔丹 |
-- | 12 | alex |
-- +-----+--------+
--
-- 15、查询没有带过高年级的老师id和姓名；
select
tid,
tname
from
teacher
where
tid in(
select
tid
from
teach2cls
inner join
class
on teach2cls.cid=class.cid
where
grade_id<5
group by
tid);
-- +-----+--------+
-- | tid | tname |
-- +-----+--------+
-- | 1 | 张三 |
-- | 2 | 李四 |
-- | 3 | 王五 |
-- | 5 | 小花 |
-- | 6 | 阿狗 |
-- +-----+--------+
--
-- 16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
select
sid,
sname
from
student
where
sid in (
select distinct
student_id
from
score
where
course_id in (
select
cid
from
course
where
teacher_id in(
select
tid
from
teacher
where
tname='张三'
)
)
);
-- +-----+-----------+
-- | sid | sname |
-- +-----+-----------+
-- | 1 | 乔丹 |
-- | 2 | 艾弗森 |
-- | 5 | lily |
-- | 6 | jack |
-- | 12 | alex |
-- +-----+-----------+
--
-- 17、查询带过超过2个班级的老师的id和姓名；
select
tid,
tname
from
teacher
where tid in (
select
tid
from
teach2cls
group by
tid
having
count(cid)>=2
);
-- +-----+--------+
-- | tid | tname |
-- +-----+--------+
-- | 1 | 张三 |
-- | 6 | 阿狗 |
-- +-----+--------+
--
-- 18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select
sid,
sname
from student
where sid in (
select s1.student_id
from score as s1 inner join score as s2 on s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 2
where s1.score < s2.score);
-- +-----+-------+
-- | sid | sname |
-- +-----+-------+
-- | 12 | alex |
-- +-----+-------+
--
-- 19、查询所带班级数最多的老师id和姓名；
select
tid,
tname
from
teacher
where
tid in (
select
tid
from
teach2cls
group by
tid
having
count(cid)=(
select
count(cid)
from
teach2cls
group by
tid
order by
count(cid) desc
limit 1
)
);
-- +-----+--------+
-- | tid | tname |
-- +-----+--------+
-- | 1 | 张三 |
-- | 6 | 阿狗 |
-- +-----+--------+
--
-- 20、查询有课程成绩小于60分的同学的学号、姓名；
select
sid,
sname
from
student
where
sid in (
select
student_id
from
score
where
score<60
);
-- +-----+--------+
-- | sid | sname |
-- +-----+--------+
-- | 1 | 乔丹 |
-- | 13 | 张良 |
-- +-----+--------+
--
-- 21、查询没有学全所有课的同学的学号、姓名；
select
sid,
sname
from
student
left join (
select
student_id
from
score
group by
student_id
having
count(course_id)<(
select count(cid) from course)
)as t1
on
student.sid=t1.student_id
-- +-----+-----------+
-- | sid | sname |
-- +-----+-----------+
-- | 1 | 乔丹 |
-- | 2 | 艾弗森 |
-- | 5 | lily |
-- | 6 | jack |
-- | 8 | lucy |
-- | 9 | bob |
-- | 12 | alex |
-- | 13 | 张良 |
-- | 18 | 妲己 |
-- +-----+-----------+
--
-- 22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select
sid,sname
from
student
where
sid in (
select
distinct student_id
from
score
where
course_id in(
select
course_id
from
score
where
student_id =1
)
)
and sid !=1;
-- +-----+-----------+
-- | sid | sname |
-- +-----+-----------+
-- | 12 | alex |
-- | 6 | jack |
-- | 2 | 艾弗森 |
-- | 5 | lily |
-- +-----+-----------+
--
-- 23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
select
sid,sname
from
student
where
sid in (
select
distinct student_id
from
score
where
course_id in(
select
course_id
from
score
where
student_id =1
)
)
and sid !=1;
-- +-----+-----------+
-- | sid | sname |
-- +-----+-----------+
-- | 12 | alex |
-- | 6 | jack |
-- | 2 | 艾弗森 |
-- | 5 | lily |
-- +-----+-----------+
--
-- 24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；
select
sid,
sname
from
student
where sid in (
select student_id from score,
(select course_id from score where student_id=2)as t1
where score.course_id = t1.course_id and score.student_id !=2 group by score.student_id
having count(score.course_id)=(select count(course_id)from score where student_id=2)
);
--
-- 25、删除学习“张三”老师课的score表记录；
delete from score where course_id in (
select cid from course where teacher_id =(
select tid from teacher where tname='张三')
);
--
--26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课 程的平均成绩；
insert score(student_id,course_id,score)
select t1.sid,2,t2.avg_score from(
(select sid from student where sid not in (select student_id from score where course_id = 2)) as t1,
(select avg(score)as avg_score from score where course_id = 2) as t2);
-- 27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文, 数学,英语,课程数和平均分；
select
student_id,
(select score from score where course_id =(select cid from course where cname='语文') and score.student_id =s1.student_id ) as '语文',
(select score from score where course_id =(select cid from course where cname='数学') and score.student_id =s1.student_id ) as '数学',
(select score from score where course_id =(select cid from course where cname='英语') and score.student_id =s1.student_id ) as '英语',
count(course_id) as '有效课程数',
avg(score) as '有效平均分'
from
score as s1
group by
student_id
order by
avg(score);
-- +------------+--------+--------+--------+-----------------+-----------------+
-- | student_id | 语文 | 数学 | 英语 | 有效课程数 | 有效平均分 |
-- +------------+--------+--------+--------+-----------------+-----------------+
-- | 1 | NULL | NULL | NULL | 3 | 62.3333 |
-- | 13 | NULL | NULL | 55 | 2 | 69.0000 |
-- | 12 | NULL | NULL | NULL | 2 | 75.0000 |
-- | 9 | NULL | NULL | 72 | 2 | 77.5000 |
-- | 6 | NULL | NULL | NULL | 3 | 78.6667 |
-- | 18 | NULL | NULL | NULL | 2 | 80.0000 |
-- | 2 | 88 | 68 | NULL | 3 | 85.0000 |
-- | 5 | NULL | NULL | NULL | 1 | 88.0000 |
-- | 8 | NULL | 98 | NULL | 2 | 90.5000 |
-- +------------+--------+--------+--------+-----------------+-----------------+
--
-- 28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select
course_id as '课程id',
max(score.score) as '最高分',
min(score.score) as '最低分'
from
course
left join score
on score.course_id=course.cid
group by course_id;
-- +----------+-----------+-----------+
-- | 课程id | 最高分 | 最低分 |
-- +----------+-----------+-----------+
-- | NULL | NULL | NULL |
-- | 1 | 80 | 60 |
-- | 2 | 99 | 59 |
-- | 3 | 73 | 68 |
-- | 4 | 88 | 88 |
-- | 5 | 98 | 68 |
-- | 6 | 72 | 55 |
-- | 7 | 77 | 77 |
-- +----------+-----------+-----------+
--
-- 29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id,
avg(score) as avg_score,
sum(case when score.score >= 60 then 1 else 0 end) / count(sid) * 100 as percent
from
score
group by
course_id
order by
avg(score) asc,percent desc;
-- +-----------+-----------+----------+
-- | course_id | avg_score | percent |
-- +-----------+-----------+----------+
-- | 6 | 63.5000 | 50.0000 |
-- | 1 | 67.6667 | 100.0000 |
-- | 3 | 70.5000 | 100.0000 |
-- | 7 | 77.0000 | 100.0000 |
-- | 5 | 83.0000 | 100.0000 |
-- | 2 | 83.1111 | 88.8889 |
-- | 4 | 88.0000 | 100.0000 |
-- +-----------+-----------+----------+
--
-- 30、课程平均分从高到低显示（现实任课老师）；
select
t1.cid,
t1.tname,
t2.avg_score
from(
select
teacher.tid as tid,
teacher.tname as tname,
course.cid as cid
from
teacher
inner join
course
on teacher.tid = teacher_id
)as t1
inner join
(select course_id,avg(score)as avg_score from score group by course_id )as t2
on
t1.cid=t2.course_id
order by
avg_score desc;
-- +-----+--------+-----------+
-- | cid | tname | avg_score |
-- +-----+--------+-----------+
-- | 4 | 甜甜 | 88.0000 |
-- | 2 | 张三 | 83.1111 |
-- | 5 | 小花 | 83.0000 |
-- | 7 | 王五 | 77.0000 |
-- | 3 | 李四 | 70.5000 |
-- | 1 | 张三 | 67.6667 |
-- | 6 | 阿狗 | 63.5000 |
-- +-----+--------+-----------+
--
-- 31、查询各科成绩前三名的记录(不考虑成绩并列情况) ；
select
student_id,
score,
course_id
from score r1
where (SELECT count(1)
from (select distinct
score,
course_id
from score) r2
where r2.course_id = r1.course_id AND r2.score > r1.score) <= 2
order by course_id, score DESC;
-- +------------+-------+-----------+
-- | student_id | score | course_id |
-- +------------+-------+-----------+
-- | 6 | 80 | 1 |
-- | 12 | 63 | 1 |
-- | 1 | 60 | 1 |
-- | 2 | 99 | 2 |
-- | 5 | 88 | 2 |
-- | 12 | 87 | 2 |
-- | 6 | 73 | 3 |
-- | 1 | 68 | 3 |
-- | 2 | 88 | 4 |
-- | 8 | 98 | 5 |
-- | 2 | 68 | 5 |
-- | 9 | 72 | 6 |
-- | 13 | 55 | 6 |
-- | 18 | 77 | 7 |
-- +------------+-------+-----------+
--
-- 32、查询每门课程被选修的学生数；
select
course_id,
count(student_id)
from
score
group by
course_id;
-- +-----------+-------------------+
-- | course_id | count(student_id) |
-- +-----------+-------------------+
-- | 1 | 3 |
-- | 2 | 9 |
-- | 3 | 2 |
-- | 4 | 1 |
-- | 5 | 2 |
-- | 6 | 2 |
-- | 7 | 1 |
-- +-----------+-------------------+
--
-- 33、查询选修了2门以上课程的全部学生的学号和姓名；
select
sid,
sname
from
student
where sid in(
select
student_id
from
score
group by
student_id
having
count(course_id)>2);
-- +-----+-----------+
-- | sid | sname |
-- +-----+-----------+
-- | 1 | 乔丹 |
-- | 2 | 艾弗森 |
-- | 6 | jack |
-- +-----+-----------+
--
-- 34、查询男生、女生的人数，按倒序排列；
select
gender,
count(sid)
from
student
group by
gender
order by
count(sid) desc;
-- +--------+------------+
-- | gender | count(sid) |
-- +--------+------------+
-- | 女 | 5 |
-- | 男 | 4 |
-- +--------+------------+
--
-- 35、查询姓“张”的学生名单；
select
*
from
student
where
sname like "张%";
-- +-----+--------+--------+----------+
-- | sid | sname | gender | class_id |
-- +-----+--------+--------+----------+
-- | 13 | 张良 | 男 | 4 |
-- +-----+--------+--------+----------+
--
-- 36、查询同名同姓学生名单，并统计同名人数；
select
sname,
count(sid)
from
student
group by
sname
having
count(sid)>1;
--
-- 37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select
course_id,
avg(score)
from
score
group by
course_id
order by
avg(score),
course_id desc;
-- +-----------+------------+
-- | course_id | avg(score) |
-- +-----------+------------+
-- | 6 | 63.5000 |
-- | 1 | 67.6667 |
-- | 3 | 70.5000 |
-- | 7 | 77.0000 |
-- | 5 | 83.0000 |
-- | 2 | 83.1111 |
-- | 4 | 88.0000 |
-- +-----------+------------+
--
-- 38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select
student.sname,
t1.score
from
student
inner join (
select
student_id,
score
from
score
where score.score<60 and course_id in (
select
cid
from
course
where cname='数学'
)
)as t1
on
t1.student_id=student.sid;
--
-- 39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
select
sid,
sname
from
student
where sid in(
select
student_id
from
score
where
course_id=3 and score>80
);
--
-- 40、求选修了课程的学生人数
select
coalesce(student_id,"总人数")as '课程id',
count(course_id) as '人数'
from
score
group by
student_id
with rollup;
-- +-----------+--------+
-- | 课程id | 人数 |
-- +-----------+--------+
-- | 1 | 3 |
-- | 2 | 3 |
-- | 5 | 1 |
-- | 6 | 3 |
-- | 8 | 2 |
-- | 9 | 2 |
-- | 12 | 2 |
-- | 13 | 2 |
-- | 18 | 2 |
-- | 总人数 | 20 |
-- +-----------+--------+
-- 41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
select
s1.student_id,
low_score,
s2.student_id,
high_score
from(
select
tid,
student_id,
score as low_score
from
(select student_id,cid,cname,score,tid
from score
inner join
(select tid,tname,cid,cname from teacher inner join course on teacher.tid=course.teacher_id where tname='王五')as t1
on score.course_id=t1.cid)as t2 order by score limit 1) as s1
inner join (
select tid,student_id,score as high_score from
(select student_id,cid,cname,score,tid
from score
inner join
(select tid,tname,cid,cname from teacher
inner join
course
on teacher.tid=course.teacher_id where tname='王五')as t1
on score.course_id=t1.cid)as t2 order by score desc limit 1) as s2
on s1.tid=s2.tid;
-- +------------+-----------+------------+------------+
-- | student_id | low_score | student_id | high_score |
-- +------------+-----------+------------+------------+
-- | 18 | 77 | 18 | 77 |
-- +------------+-----------+------------+------------+
--
-- 42、查询各个课程及相应的选修人数；
select
course_id ,
count(student_id)
from
score
group by
course_id;
-- +-----------+-------------------+
-- | course_id | count(student_id) |
-- +-----------+-------------------+
-- | 1 | 3 |
-- | 2 | 9 |
-- | 3 | 2 |
-- | 4 | 1 |
-- | 5 | 2 |
-- | 6 | 2 |
-- | 7 | 1 |
-- +-----------+-------------------+
--
-- 43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select distinct
s1.course_id,
s1.student_id,
s1.score,
s2.course_id,
s2.student_id,
s2.score
from
score as s1,
score as s2
where
s1.score = s2.score and s1.course_id != s2.course_id;
-- +-----------+------------+-------+-----------+------------+-------+
-- | course_id | student_id | score | course_id | student_id | score |
-- +-----------+------------+-------+-----------+------------+-------+
-- | 4 | 2 | 88 | 2 | 5 | 88 |
-- | 5 | 2 | 68 | 3 | 1 | 68 |
-- | 2 | 5 | 88 | 4 | 2 | 88 |
-- | 3 | 1 | 68 | 5 | 2 | 68 |
-- +-----------+------------+-------+-----------+------------+-------+
--
-- 44、查询每门课程成绩最好的前两名学生id和姓名；
select student.sid,student.sname,course.cname, score.score
from score
inner join (
select course_id, score, ranking
from (
select a.course_id, a.score, count(1) as ranking
from
(select course_id, score from score group by course_id, score order by course_id, score desc)as a
inner join
(select course_id, score from score group by course_id, score order by course_id, score desc)as b
on a.course_id = b.course_id and a.score <= b.score group by course_id, score
) as t1
where ranking in (1, 2) order by course_id, ranking)as s1
on score.course_id = s1.course_id and score.score = s1.score
inner join student
on score.student_id = student.sid
inner join course
on score.course_id = course.cid;
-- +-----+-----------+--------+-------+
-- | sid | sname | cname | score |
-- +-----+-----------+--------+-------+
-- | 12 | alex | 生物 | 63 |
-- | 6 | jack | 生物 | 80 |
-- | 2 | 艾弗森 | 体育 | 99 |
-- | 5 | lily | 体育 | 88 |
-- | 6 | jack | 物理 | 73 |
-- | 1 | 乔丹 | 物理 | 68 |
-- | 2 | 艾弗森 | 语文 | 88 |
-- | 8 | lucy | 数学 | 98 |
-- | 2 | 艾弗森 | 数学 | 68 |
-- | 9 | bob | 英语 | 72 |
-- | 13 | 张良 | 英语 | 55 |
-- | 18 | 妲己 | 化学 | 77 |
-- +-----+-----------+--------+-------+
--
-- 45、检索至少选修两门课程的学生学号；
select
student_id
from
score
group by
student_id
having
count(course_id)>=2;
-- +------------+
-- | student_id |
-- +------------+
-- | 1 |
-- | 2 |
-- | 6 |
-- | 8 |
-- | 9 |
-- | 12 |
-- | 13 |
-- | 18 |
-- +------------+
--
-- 46、查询没有学生选修的课程的课程号和课程名；
select
course.cid,
course.cname
from
course
left join
score
on
course.cid=score.course_id
where
score.student_id is null;
-- +-----+--------+
-- | cid | cname |
-- +-----+--------+
-- | 8 | 美术 |
-- +-----+--------+
--
-- 47、查询没带过任何班级的老师id和姓名；
select
teacher.tid,
tname
from
teacher
left join
teach2cls
on
teacher.tid=teach2cls.tid
where
teach2cls.tcid is null;
-- +-----+--------+
-- | tid | tname |
-- +-----+--------+
-- | 7 | 阿猫 |
-- +-----+--------+
--
-- 48、查询有两门以上课程超过80分的学生id及其平均成绩；
select
student_id,
avg(score)
from
score
where
score>80
group by
student_id
having
count(course_id)>2;
--
-- 49、检索“3”课程分数小于60，按分数降序排列的同学学号；
select
student_id,
score
from
score
where
score<60 and course_id=3
order by
score desc;
--
-- 50、删除编号为“2”的同学的“1”课程的成绩；
delete from score where student_id='2' and course_id='1';
--
-- 51、查询同时选修了物理课和生物课的学生id和姓名；
select
student.sid,
student.sname
from
student
where sid in (
select
student_id
from
score
where
course_id IN (
select
cid
from
course
where cname = '物理' or cname = '生物'
)
group by
student_id
having
count(course_id) = 2
);
-- +-----+--------+
-- | sid | sname |
-- +-----+--------+
-- | 1 | 乔丹 |
-- | 6 | jack |
-- +-----+--------+