sql练习题（经典）

CREATE DATABASE student;

USE student;

SELECT * FROM student;
-- 学生表
CREATE TABLE student(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20),
    sage INT,
    ssex CHAR(1)
);

INSERT INTO student (sname,sage,ssex) VALUES ('张三',18,0);
INSERT INTO student (sname,sage,ssex) VALUES ('李四',19,1);
INSERT INTO student (sname,sage,ssex) VALUES ('王二',20,0);
INSERT INTO student (sname,sage,ssex) VALUES ('麻子',21,1);
INSERT INTO student (sname,sage,ssex) VALUES ('刘一',21,1);
INSERT INTO student (sname,sage,ssex) VALUES ('陈二',22,0);
INSERT INTO student (sname,sage,ssex) VALUES ('赵三',19,0);
INSERT INTO student (sname,sage,ssex) VALUES ('王五',20,0);
DELETE FROM student WHERE sid=8;

-- 教师表
CREATE TABLE teacher(
    tid INT PRIMARY KEY AUTO_INCREMENT,
    tname VARCHAR(20)
);

INSERT INTO teacher (tname) VALUES ('李');
INSERT INTO teacher (tname) VALUES ('潘');
INSERT INTO teacher (tname) VALUES ('王');

-- 课程表
CREATE TABLE course(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(20),
    tid INT,
    CONSTRAINT fk_course_teach FOREIGN KEY (tid) REFERENCES teacher(tid)
);

INSERT INTO course (cname,tid) VALUES ('语文',1);
INSERT INTO course (cname,tid) VALUES ('英语',1);
INSERT INTO course (cname,tid) VALUES ('数学',2);
INSERT INTO course (cname,tid) VALUES ('政治',2);
INSERT INTO course (cname,tid) VALUES ('历史',3);
 


-- 成绩表
CREATE TABLE score(
    sid INT,
    cid INT,
    score INT,
    CONSTRAINT fk_score_stu FOREIGN KEY (sid) REFERENCES student(sid),
    CONSTRAINT fk_score_course FOREIGN KEY (cid) REFERENCES course(cid)
);

-- drop table score;

INSERT INTO score (sid,cid,score) VALUES (1,1,60);
INSERT INTO score (sid,cid,score) VALUES (1,2,65);
INSERT INTO score (sid,cid,score) VALUES (1,3,70);
INSERT INTO score (sid,cid,score) VALUES (1,4,20);
INSERT INTO score (sid,cid,score) VALUES (2,1,71);
INSERT INTO score (sid,cid,score) VALUES (2,2,63);
INSERT INTO score (sid,cid,score) VALUES (2,3,82);
INSERT INTO score (sid,cid,score) VALUES (2,4,64);
INSERT INTO score (sid,cid,score) VALUES (3,1,52);
INSERT INTO score (sid,cid,score) VALUES (3,2,90);
INSERT INTO score (sid,cid,score) VALUES (3,4,82);
INSERT INTO score (sid,cid,score) VALUES (3,5,61);
INSERT INTO score (sid,cid,score) VALUES (4,1,76);
INSERT INTO score (sid,cid,score) VALUES (4,2,85);
INSERT INTO score (sid,cid,score) VALUES (4,3,93);
INSERT INTO score (sid,cid,score) VALUES (4,4,53);
INSERT INTO score (sid,cid,score) VALUES (4,5,57);
INSERT INTO score (sid,cid,score) VALUES (5,3,41);
INSERT INTO score (sid,cid,score) VALUES (5,4,53);
INSERT INTO score (sid,cid,score) VALUES (5,5,55);
INSERT INTO score (sid,cid,score) VALUES (5,1,43);
INSERT INTO score (sid,cid,score) VALUES (5,2,56);
INSERT INTO score (sid,cid,score) VALUES (6,5,60);
INSERT INTO score (sid,cid,score) VALUES (7,1,60);
INSERT INTO score (sid,cid,score) VALUES (9,1,76);

SELECT * FROM score WHERE sid=4

-- delete from score where sid=5 and cid in (5)

-- 1.麻子的老师信息
-- <1>
SELECT * FROM teacher WHERE tid IN(
    SELECT DISTINCT tid FROM course WHERE cid IN(SELECT cid FROM score WHERE sid = (
            SELECT sid FROM student WHERE sname = '麻子'
        )
    )

);

-- 做的<2>
SELECT DISTINCT t.tid,t.tname FROM teacher t INNER JOIN course c ON t.tid=c.`tid`
INNER JOIN score sc ON sc.cid= c.cid INNER JOIN student s ON s.sid=sc.sid
WHERE s.sname='麻子'
-- <3>
SELECT DISTINCT t.`tid`,t.`tname` FROM
(SELECT sid,sname FROM student WHERE sname='麻子')AS s
INNER JOIN score sc ON sc.`sid`=s.`sid`
INNER JOIN course c ON c.`cid`=sc.`cid`
INNER JOIN teacher t ON t.`tid`=c.`tid`;
-- <4>
SELECT DISTINCT t.* FROM student s,score sc,course c,teacher t WHERE s.`sid`=sc.`sid` AND
c.`cid`=sc.`cid` AND t.`tid`= c.`tid` AND s.`sname`='麻子';

-- 2.求所有学生每个人的平均成绩大于60
-- 先求出每个人的平均成绩
-- <1>
SELECT sid,AVG(score) FROM score GROUP BY sid HAVING AVG(score)>=60
-- <2>
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,score sc WHERE s.sid = sc.sid GROUP BY s.sid,s.`sname` HAVING AVG(sc.score)>=60

-- 3.查询所有同学的学号、姓名、选课数、总成绩
-- <1>
SELECT s.sid,s.sname,COUNT(sc.cid),SUM(sc.score) FROM student s,score sc WHERE s.sid = sc.sid GROUP BY s.sid,s.sname
-- <2>
SELECT s.sid,s.sname,COUNT(sc.cid),SUM(sc.score) FROM student s INNER JOIN score sc ON s.sid=sc.sid GROUP BY s.sid,s.sname;

-- 4.查询姓李的老师的个数
SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%';
-- 5.查询没学过李老师课程的学生的姓名和编号
-- <1>
SELECT * FROM student WHERE sid NOT IN(
    SELECT sid FROM score WHERE cid IN(
        SELECT cid FROM course WHERE tid IN
            (SELECT tid FROM teacher WHERE tname='李')
    )
)
-- <2>
SELECT * FROM student s WHERE s.sid NOT IN(
    SELECT DISTINCT sc.cid FROM
    (SELECT tid FROM teacher WHERE tname LIKE '李%')t
    INNER JOIN course c ON c.tid=t.tid
    INNER JOIN score sc ON sc.cid=c.cid
)
-- <3>
SELECT * FROM student WHERE sid NOT IN(
    SELECT sc.`sid` FROM
    (SELECT tid FROM teacher WHERE tname LIKE '李%')t
    ,course c,score sc,student s WHERE t.tid=c.tid AND
     c.cid = sc.cid AND sc.sid=s.sid
)
-- 6.查询“某1”课程比“某2”课程成绩高的所有学生的学号；
SELECT sid,sname FROM student WHERE sid IN(
    SELECT a.sid FROM
    (SELECT sc.sid,sc.score FROM score sc,course c WHERE (c.cid=sc.cid) AND c.cname='语文' ) AS a,

    (SELECT sc.sid,sc.score FROM score sc,course c WHERE (c.cid=sc.cid) AND c.cname='英语' ) AS b
    WHERE a.sid=b.sid AND a.score>b.score
)

SELECT sid,sname FROM student WHERE sid IN
(
    SELECT a.sid FROM
        (SELECT s.sid,s.score FROM score s RIGHT JOIN course c ON (s.cid=c.cid) WHERE c.cname='语文') AS a,
        (SELECT s.sid,s.score FROM score s RIGHT JOIN course c ON (s.cid=c.cid) WHERE c.cname='英语') AS b
    WHERE a.sid=b.sid AND a.score>b.score
)
-- 7.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名；
SELECT a.sid,a.sname FROM
(SELECT s.sname,s.sid FROM student s,course c,score sc WHERE s.sid=sc.sid AND sc.cid=c.cid AND c.cname='语文')AS a,

(SELECT s.sname,s.sid FROM student s,course c,score sc WHERE s.sid=sc.sid AND sc.cid=c.cid AND c.cname='英语')AS b
WHERE a.sid=b.sid

SELECT a.sid,a.sname FROM
(SELECT s.sname,s.sid FROM student s,course c,score sc
WHERE cname='语文'AND sc.sid=s.sid AND sc.cid=c.cid) a,
(SELECT s.sname,s.sid FROM student s,course c,score sc
WHERE cname='英语'AND sc.sid=s.sid AND sc.cid=c.cid) b
WHERE a.sid=b.sid;


-- 这个没懂
SELECT student.sid,student.sname
FROM
student,score sc
WHERE
student.sid=sc.sid -- AND sc.cid='1'
AND NOT EXISTS
( SELECT * FROM score WHERE 1=1
AND score.sid=sc.sid
AND score.cid=2);

-- 7.查询学过“李”老师所教的所有课的同学的学号、姓名；
SELECT sid,sname FROM student WHERE sid IN(
SELECT sc.sid FROM course c,score sc,teacher t WHERE c.`tid`=t.`tid` AND c.`cid`=sc.`cid` AND t.`tname`='李' GROUP BY sc.`sid` HAVING  COUNT(sc.`cid`)=(SELECT COUNT(c.`cid`) FROM teacher t,course c WHERE t.`tid`=c.`tid` AND t.`tname`='李' )

)

 SELECT sid,sname FROM student WHERE sid IN
 (
    SELECT sid
    -- ,COUNT(score.cid)
    FROM score,course,teacher WHERE
    score.cid=course.cid AND teacher.tid=course.tid AND
    teacher.tname='李'
    GROUP BY sid
    HAVING COUNT(score.cid)=
    (
        SELECT COUNT(cid) FROM course,teacher  
        WHERE teacher.tid=course.tid AND tname='李'
    )
)

-- 1.有哪些列
-- 2.用到哪些表
-- 3.条件是什么
-- 8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；

-- 13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT stu.sid,stu.sname,COUNT(s.cid) num FROM student stu
INNER JOIN score s ON stu.sid = s.sid
AND s.cid IN
(SELECT sc.cid FROM score sc WHERE sc.sid = 1)
GROUP BY stu.sid,stu.sname
HAVING COUNT(s.cid) =
(
    SELECT COUNT(1) num FROM score sc WHERE sc.sid = 1
)
-- 14.删除学习“李”老师课的score表记录；
DELETE FROM score WHERE cid IN
(
    SELECT c.cid FROM teacher t,course c WHERE t.`tid`=c.`tid` AND t.`tname`='李'用
)
-- 16.按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩;
SELECT sid,语文,英语,政治 FROM
(
    SELECT sc.sid,
    MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
    MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
    MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
    CONVERT(AVG(sc.score),SIGNED) AS score
    FROM student s LEFT JOIN score sc ON (sc.sid=s.sid) WHERE sc.cid IN
    (SELECT cid FROM course WHERE cname IN('语文','英语','政治'))
    GROUP BY sc.sid
)AS a ORDER BY score DESC;

SELECT sid,语文,英语,政治 FROM
(
    SELECT sc.sid,
    MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
    MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
    MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
    CONVERT(AVG(score),SIGNED) score
    FROM student s LEFT JOIN score sc ON (s.sid=sc.sid) WHERE cid IN
    (SELECT cid FROM course WHERE cname IN ('语文','英语','政治'))
    GROUP BY sc.sid
) AS a ORDER BY score DESC

-- 17.按如下形式显示：学生ID,语文,英语,数学,政治,历史,有效课程数,有效平均分；
SELECT sid,语文,数学,英语,政治,历史,num,score FROM
(
    SELECT sc.sid,COUNT(sc.cid) num,
    MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
    MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
    MAX(CASE sc.cid WHEN 3 THEN sc.score ELSE 0 END) '数学',
    MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
    MAX(CASE sc.cid WHEN 5 THEN sc.score ELSE 0 END) '历史',
    CONVERT(AVG(score),SIGNED) score
    FROM student s LEFT JOIN score sc ON (s.sid=sc.sid) WHERE cid IN
    (SELECT cid FROM course WHERE cname IN ('语文','英语','政治','数学','历史'))
    GROUP BY sc.sid
) AS a ORDER BY score DESC



-- 18.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT cid,AVG(score),MAX(score),MIN(score)  FROM score GROUP BY cid;

-- 19.按各科平均成绩从低到高和及格率的百分数从高到低顺序???
SELECT * FROM
(
SELECT cid,AVG(score) score FROM score GROUP BY cid
) AS a ORDER BY score ASC

-- 每科及格人数，每科总人数
SELECT * FROM
(
SELECT a.cid,jg,zs,CONVERT((jg/zs)*100,SIGNED) 'jgl' FROM
(SELECT cid,COUNT(*) 'jg' FROM score WHERE score>60 GROUP BY cid) a,
(SELECT cid,COUNT(*) 'zs' FROM score GROUP BY cid) b
WHERE a.cid=b.cid
) AS c ORDER BY jgl DESC

SELECT cid,AVG(score) a FROM score GROUP  BY cid ORDER BY a ASC

SELECT * FROM
(
SELECT a.cid,jg,zs,CONVERT((jg/zs)*100,SIGNED) 'jgl' FROM
(SELECT cid,COUNT(*) 'jg' FROM score WHERE score>60 GROUP BY cid) AS a,
(SELECT cid,COUNT(*) 'zs' FROM score GROUP BY cid) AS b
WHERE a.cid=b.cid
) AS c ORDER BY jgl DESC

-- 20.查询每门课程被选修的学生数
SELECT cid,COUNT(sid) FROM score GROUP BY cid;

SELECT cid,COUNT(sid) FROM score GROUP BY cid

-- 21.查询出只选修了一门课程的全部学生的学号和姓名
SELECT s.sid,s.sname FROM score sc,student s WHERE
s.sid=sc.sid GROUP BY s.sid HAVING COUNT(sc.cid)=1

SELECT sid,sname FROM student WHERE sid IN
(
    SELECT sid FROM score GROUP BY sid HAVING COUNT(cid)=1
)
-- 22.查询每门课程的平均成绩，结果按平均成绩升序排列，
-- 平均成绩相同时，按课程号降序排列
SELECT c.cid,CONVERT(AVG(sc.score),SIGNED) pj FROM course c,score sc
 WHERE c.cid=sc.cid GROUP BY c.cid ORDER BY  pj ASC,cid DESC
 
 SELECT cid,CONVERT(AVG(score),SIGNED) score FROM score
GROUP BY cid ORDER BY score ASC,cid DESC;
 
-- 23.查询选修“李”老师所授课程的学生中，成绩最高的学生姓名及其成绩
-- 列：学生姓名,成绩
-- 条件：成绩最高,李老师
-- 表：成绩，老师，学生
SELECT sc.*
FROM score sc,teacher t,student s,course c
WHERE sc.cid = c.cid AND c.tid= t.tid AND t.tname='李' AND (sc.sid=s.sid)
AND sc.score=(SELECT MAX(score) FROM score WHERE sc.cid=cid)

SELECT s.*
FROM score s LEFT JOIN course c ON (s.cid=c.cid)
LEFT JOIN teacher t ON (c.tid=t.tid)
WHERE t.tname='李' AND s.score=
(SELECT MAX(score) FROM score WHERE cid=s.cid)
-- 24.查询每门课程成绩最好的前两名
-- select * from score where cid=1
-- SELECT COUNT(score) FROM score WHERE cid = 1 AND score >= 60

-- select * from score group by cid;

SELECT a.sid, a.cid, a.score FROM score AS a WHERE
(
    SELECT COUNT(score) FROM score AS b
    WHERE b.cid = a.cid AND b.score >= a.score
) <= 2
ORDER BY a.cid ASC, a.score DESC


SELECT a.sid,a.cid,a.score FROM score AS a LEFT JOIN score AS b ON a.cid=b.cid AND a.score>=b.score
GROUP BY a.cid,a.sid,a.score
HAVING COUNT(a.cid)>=5
ORDER BY a.cid,a.score DESC;

SELECT t0.sid AS "姓名",t1.score AS "分数排行" FROM score t0
RIGHT JOIN (SELECT score FROM score GROUP BY score
 ORDER BY score DESC LIMIT 2) t1 ON t0.score
 = t1.score

-- 25.统计每门课程的学生选修人数（超过0人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，
-- 若人数相同，按课程号升序排列
SELECT cid,COUNT(sid) AS c FROM score GROUP BY cid HAVING COUNT(sid)>0 ORDER BY c DESC,cid ASC;

-- 26.检索至少选修两门课程的学生学号
SELECT sid,COUNT(cid) FROM score GROUP BY sid HAVING COUNT(cid)>=2;
-- 27.查询两门以上不及格课程的同学的学号及其平均成绩
SELECT sid,AVG(score) FROM score WHERE sid IN
(
    SELECT sid FROM score WHERE score<60
    GROUP BY sid HAVING COUNT(cid)>2
)

-- 28.检索“”课程分数小于90的同学学号，按分数降序排列.
SELECT * FROM score WHERE cid=1 AND score<90 ORDER BY score DESC