经典SQL50题（11~20）

创建表SQL

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
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' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

表结构

学生表 Student
sid 学生编号 sname 学生姓名 sage 出生年月 ssex 学生性别
课程表 Course
cid 课程编号 cname 课程名称 tid 教师编号
教师表 Teacher
tid 教师编号 tname 教师姓名
成绩表 SC
sic 学生编号 cid 课程编号 score 分数

11~20题

-- 11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
SELECT Student.sid,Student.sname
FROM Student
WHERE Student.sid in(
SELECT SC.sid
FROM SC
WHERE SC.cid in(
SELECT SC.cid
FROM SC
WHERE SC.sid='01'
)
AND SC.sid !='01'
)

-- 12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
SELECT Student.sid,Student.sname
FROM Student
WHERE Student.sid in(
SELECT t2.sid
FROM (
SELECT SC.cid
FROM SC
WHERE
SC.sid='01'
) t1 INNER JOIN (SELECT SC.sid,SC.cid FROM SC WHERE SC.sid!='01') t2 on t1.cid=t2.cid
GROUP BY t2.sid
HAVING COUNT(t2.cid)=(SELECT COUNT(DISTINCT SC.cid) FROM SC WHERE SC.sid='01')
)

-- 13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
-- update跳过

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT Student.sname
FROM Student 
WHERE Student.sid NOT in(
SELECT SC.sid
FROM SC
INNER JOIN Course ON SC.cid=Course.cid
INNER JOIN Teacher ON Course.tid=Teacher.tid
WHERE
Teacher.tname='张三'
)

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT Student.sid,Student.sname,t.avg
FROM Student
INNER JOIN (
SELECT SC.sid,AVG(SC.score) AS avg
FROM SC
WHERE SC.sid in(
SELECT SC.sid
FROM SC
WHERE SC.score<60
GROUP BY SC.sid
HAVING COUNT(*)>=2
)
GROUP BY SC.sid
) AS t ON Student.sid=t.sid

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT Student.*,SC.score
FROM Student
INNER JOIN SC ON Student.sid=SC.sid
WHERE
SC.cid='01'
AND
SC.score<60
ORDER BY SC.score DESC

-- 17、按平均成绩从高到低显示所有学生的平均成绩
SELECT Student.sid,Student.sname,t.avg
FROM Student
LEFT JOIN (
SELECT SC.sid,AVG(SC.score) AS avg
FROM SC
GROUP BY SC.sid
) AS t ON Student.sid=t.sid
ORDER BY t.avg DESC

-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
SELECT Course.cid,Course.cname,t.max,t.min,t.avg,t.rate
FROM Course
INNER JOIN (
SELECT 
SC.cid,MAX(SC.score) AS max,
MIN(SC.score) AS min,
AVG(SC.score) AS avg,
100*SUM(CASE WHEN IFNULL(SC.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS rate
FROM SC
GROUP BY SC.cid
) AS t ON Course.cid=t.cid

-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT Course.cid,Course.cname,t.avg,t.rate
FROM Course
INNER JOIN (
SELECT 
SC.cid,
AVG(SC.score) AS avg,
100*SUM(CASE WHEN IFNULL(SC.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS rate
FROM SC
GROUP BY SC.cid
) AS t ON Course.cid=t.cid
ORDER BY t.avg ASC,t.rate desc

-- 20、查询学生的总成绩并进行排名
SELECT Student.sid,Student.sname,t.total
FROM Student
LEFT JOIN (
SELECT SC.sid,SUM(SC.score) AS total
FROM SC 
GROUP BY SC.sid
) AS t ON Student.sid=t.sid
ORDER BY t.total DESC