SQL50题（7、10、11、12、17、18）

学生表： Student(s_id,s_name,s_birth,s_sex) – –学生编号,学生姓名, 出生年月,学生性别
课程表：Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
教师表： Teacher(t_id,t_name) – –教师编号,教师姓名
成绩表： Score(s_id,c_id,s_s_score) – –学生编号,课程编号,分数

–学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT ‘’,
s_birth VARCHAR(20) NOT NULL DEFAULT ‘’,
s_sex VARCHAR(10) NOT NULL DEFAULT ‘’,
PRIMARY KEY(s_id)
);
–课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT ‘’,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
–教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY(t_id)
);
–成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);
–插入学生表测试数据
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
–课程表测试数据
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

–教师表测试数据
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);

–成绩表测试数据
insert into Score values(‘01’ , ‘01’ , 80);
insert into Score values(‘01’ , ‘02’ , 90);
insert into Score values(‘01’ , ‘03’ , 99);
insert into Score values(‘02’ , ‘01’ , 70);
insert into Score values(‘02’ , ‘02’ , 60);
insert into Score values(‘02’ , ‘03’ , 80);
insert into Score values(‘03’ , ‘01’ , 80);
insert into Score values(‘03’ , ‘02’ , 80);
insert into Score values(‘03’ , ‘03’ , 80);
insert into Score values(‘04’ , ‘01’ , 50);
insert into Score values(‘04’ , ‘02’ , 30);
insert into Score values(‘04’ , ‘03’ , 20);
insert into Score values(‘05’ , ‘01’ , 76);
insert into Score values(‘05’ , ‘02’ , 87);
insert into Score values(‘06’ , ‘01’ , 31);
insert into Score values(‘06’ , ‘03’ , 34);
insert into Score values(‘07’ , ‘02’ , 89);
insert into Score values(‘07’ , ‘03’ , 98);

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

写法1：

select s.s_id,s.s_name 
from score sc
join student s
on s.s_id = sc.s_id
where sc.c_id = '02' 
and s.s_id in(
	select s_id
	from score
	where c_id = '01');

写法2：

select * from student
where s_id in (select s_id
							from score
							where c_id = '01')
and s_id in (select s_id
								from score
								where c_id = '02');

变化：查询学过编号为“01”的课程但没有学过编号为“02”的课程的学生的学号、姓名（重点）

写法1：

select s.s_id,s.s_name 
from score sc
join student s
on s.s_id = sc.s_id
where sc.c_id = '01' 
and s.s_id not in(
	select s_id
	from score
	where c_id = '02');

重点：用not in进行反向，而不能用<>‘02’

写法2：

select * from student
where s_id in (select s_id
							from score
							where c_id = '01')
and s_id not in (select s_id
								from score
								where c_id = '02');

10.查询没有学全所有课的学生的学号、姓名(重点)

写法1：

select * from student
where s_id not in(
		select s_id
		from student
		where s_id in (select s_id from score where c_id='01')
		and s_id in (select s_id from score where c_id='02')
		and s_id in (select s_id from score where c_id='03'));

写法2：

select * from student
where s_id not in(
		select s_id from(
		select *,row_number() over(partition by s_id order by s_score) as r 
		from score) t
		where r = (select count(c_id) from course));

写法3：

select s.*
from student s
left join score sc
on s.s_id = sc.s_id
group by s.s_id
having count(sc.c_id)<(select count(c_id) from course);

使用having和count计数比较简便

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

select distinct s.s_id, s.s_name
from student s
left join score sc
on s.s_id = sc.s_id
where sc.c_id in(
select c_id from score where s_id ='01') and s.s_id != '01';

12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

对比11题的部分匹配，12是要完全匹配，故引入group_concat函数

select s_id,GROUP_CONCAT(c_id) 
from score 
group by s_id

select s_id from score group by s_id
having GROUP_CONCAT(c_id)=(
						select GROUP_CONCAT(c_id) 
						from score 
						where s_id = '01')
and s_id != '01';

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

select s_id,c.c_name,s_score,avg(s_score) over(partition by s_id) as avg
from score sc
join course c
on sc.c_id = c.c_id
order by avg desc;

投机取巧，只能部分展示其含义

select s_id,
max(case when c_id='01' then s_score else null end) '语文',
max(case when c_id='02' then s_score else null end) '数学',
max(case when c_id='03' then s_score else null end) '英语',
avg(s_score) avg
from score
group by s_id
order by avg desc;

重点：在于case函数的使用

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

select c.c_id,c.c_name,max(sc.s_score) max,min(sc.s_score) min,avg(sc.s_score) avg,
count(if(sc.s_score>=60,1,null))/count(sc.s_score) '及格率',count(if((sc.s_score>=70 and sc.s_score<=80),1,null))/count(sc.s_score) '中等率',
count(if((sc.s_score>=80 and sc.s_score<=90),1,null))/count(sc.s_score) '优良率',count(if(sc.s_score>=90,1,null))/count(sc.s_score) '优秀率'
from course c
join score sc
on c.c_id = sc.c_id
group by sc.c_id;

select c.c_id,c.c_name,max(sc.s_score) max,min(sc.s_score) min,avg(sc.s_score) avg,
avg(case when sc.s_score >= 60 then 1 else 0 end) as 及格率,
avg(case when sc.s_score >= 70 and sc.s_score <= 80 then 1 else 0 end)as 中等率,
avg(case when sc.s_score >= 80 and sc.s_score <= 90 then 1 else 0 end)as 优良率,
avg(case when sc.s_score >= 90 then 1 else 0 end) as 优秀率
from course c
join score sc
on c.c_id = sc.c_id
group by sc.c_id;