Sql 练习50道（掌握可解大部分的sql问题）（11 - 20）

题目（详解 11-20）：

11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
SELECT s.sid,s.sname 
FROM student s,sc 
WHERE s.sid=sc.sid 
AND sc.cid IN (SELECT cid FROM sc WHERE sid=01) 
AND sc.sid<>01
GROUP BY s.sid;

12.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名 
SELECT cid FROM sc WHERE sid=01;
SELECT s.sid,s.sname FROM student s,sc 
WHERE s.sid=sc.sid 
AND s.sid<>01 
GROUP BY sc.sid 
HAVING COUNT(*)=
(SELECT COUNT(*) FROM sc WHERE sid=01 GROUP BY sid) 

13.把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
update SC,(
SELECT t.tid,avg(score) as ascore
from Course as c,SC as sc,Teacher as t 
WHERE t.tname='张三' 
AND c.tid=t.tid AND c.cid=sc.cid) a 
set score=a.ascore;

14、查询没学过"张三"老师讲授的任一门课程的学生姓名（同5）
SELECT * FROM Student WHERE sid in(
        SELECT sid FROM SC WHERE cid NOT in(
                    SELECT cid
                    FROM Teacher,Course WHERE Teacher.tname='张三' AND Teacher.tid=Course.tid
        )
)

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
SELECT s.sid,s.sname,AVG(sc.score) 
FROM student s,sc 
WHERE s.sid=sc.sid 
AND sc.score<60 
GROUP BY sc.sid 
HAVING COUNT(cid)>=2;

16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT s.*,sc.score 
FROM student s,sc 
WHERE s.sid=sc.sid 
AND sc.cid=01 AND sc.score<60 
ORDER BY sc.score DESC;

SELECT  Student.*,SC.score FROM Student,SC WHERE EXISTS (
SELECT  sid,score FROM SC WHERE cid=01 AND score<60 AND sid=Student.sid
) AND Student.sid=SC.sid AND SC.cid=01 ORDER BY SC.score DESC;


17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s.*,
SUM(CASE WHEN sc.cid='01' THEN sc.score ELSE 0 END) s01,
SUM(CASE WHEN sc.cid='02' THEN sc.score ELSE 0 END) s02,
SUM(CASE WHEN sc.cid='03' THEN sc.score ELSE 0 END) s03,
AVG(CASE WHEN sc.score IS NULL THEN 0 ELSE sc.score END) avs 
FROM student s LEFT JOIN sc 
ON s.sid=sc.sid 
GROUP BY 1,2,3,4	
#GROUP BY s.sid
ORDER BY avs DESC;

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

SELECT c.cid,c.cname,
MAX(sc.score),MIN(sc.score),AVG(sc.score),
SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)/COUNT(1) s1,
SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END)/COUNT(1) s2,
SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END)/COUNT(1) s3,
SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END)/COUNT(1) s4
FROM course c,sc
WHERE c.cid=sc.cid
GROUP BY sc.cid

19.按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT cid,avs,CONCAT(rate,'%') 及格率
 FROM(
SELECT sc0.cid,
    AVG(sc0.score) avs,
    CAST((
        SELECT COUNT(1) FROM SC WHERE cid=sc0.cid AND score>=60
    )*1.0/(
        SELECT COUNT(1) FROM SC WHERE cid=sc0.cid
    )*100 AS DECIMAL(10,2)) rate
FROM sc sc0
GROUP BY sc0.cid
ORDER BY avs,rate DESC)a;

20、查询学生的总成绩并进行排名

SELECT s.sid,s.sname,
SUM(CASE WHEN sc.score IS NULL THEN 0 ELSE sc.score END) ss 
FROM student s LEFT JOIN sc 
ON s.sid=sc.sid 
GROUP BY sc.sid 
ORDER BY ss DESC;