挑战SQL经典题（一）

最新推荐文章于 2022-07-16 20:18:27 发布

原创最新推荐文章于 2022-07-16 20:18:27 发布 · 364 阅读

6 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

28 篇文章

订阅专栏

首先得建表，建表脚本：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) DEFAULT NULL COMMENT 'ID',
  `cname` varchar(32) DEFAULT NULL COMMENT '课程名',
  `tid` int(11) DEFAULT NULL COMMENT '教师Id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '英语', '3');
INSERT INTO `course` VALUES ('4', '物理', '4');

-- ----------------------------
-- Table structure for `sc`
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '56');
INSERT INTO `sc` VALUES ('1', '2', '78');
INSERT INTO `sc` VALUES ('1', '3', '67');
INSERT INTO `sc` VALUES ('1', '4', '58');
INSERT INTO `sc` VALUES ('2', '1', '79');
INSERT INTO `sc` VALUES ('2', '2', '81');
INSERT INTO `sc` VALUES ('2', '3', '92');
INSERT INTO `sc` VALUES ('2', '4', '68');
INSERT INTO `sc` VALUES ('3', '1', '91');
INSERT INTO `sc` VALUES ('3', '2', '47');
INSERT INTO `sc` VALUES ('3', '3', '88');
INSERT INTO `sc` VALUES ('3', '4', '56');
INSERT INTO `sc` VALUES ('4', '2', '88');
INSERT INTO `sc` VALUES ('4', '3', '90');
INSERT INTO `sc` VALUES ('4', '4', '93');
INSERT INTO `sc` VALUES ('5', '1', '46');
INSERT INTO `sc` VALUES ('5', '3', '78');
INSERT INTO `sc` VALUES ('5', '4', '53');
INSERT INTO `sc` VALUES ('6', '1', '35');
INSERT INTO `sc` VALUES ('6', '2', '68');
INSERT INTO `sc` VALUES ('6', '4', '71');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL COMMENT 'ID',
  `sname` varchar(32) NOT NULL COMMENT '姓名',
  `sage` int(11) NOT NULL COMMENT '年龄',
  `ssex` varchar(8) NOT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `student` VALUES ('3', '张三', '17', '男');
INSERT INTO `student` VALUES ('4', '李四', '18', '女');
INSERT INTO `student` VALUES ('5', '王五', '17', '男');
INSERT INTO `student` VALUES ('6', '赵六', '19', '女');

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) DEFAULT NULL,
  `tname` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '贺高');
INSERT INTO `teacher` VALUES ('3', '杨艳');
INSERT INTO `teacher` VALUES ('4', '周磊');

表说明：
student(sid,sname,sage,ssex) 学生表
course(cid,cname,tid) 课程表
sc(sid,cid,score) 成绩表
teacher(tid,tname) 教师表

问题：
1、查询“1”课程比“2”课程成绩高的所有学生的学号；（3号-张三）

SELECT a.sid FROM
(SELECT sid,score FROM sc WHERE cid='1') a ,
(SELECT sid,score FROM sc WHERE cid='2') b
WHERE a.score>b.score AND a.sid=b.sid

2、查询平均成绩大于60分的同学的学号和平均成绩；（1、2、3、4号）

SELECT sid,AVG(score) FROM sc 
GROUP BY sid HAVING AVG(score)>60

3、查询所有同学的学号、姓名、选课数、总成绩；

SELECT a.sid,a.sname,COUNT(b.cid),SUM(b.score) FROM student a 
LEFT OUTER JOIN sc b ON a.sid=b.sid GROUP BY a.sid,a.sname

4、查询姓“李”的老师的个数；（0个）

SELECT COUNT(DISTINCT(tname)) FROM teacher
WHERE tname LIKE '李%'

5、查询没学过“叶平”老师课的同学的学号、姓名；（4号-李四）

SELECT sid,sname FROM student WHERE sid NOT IN
(SELECT a.sid FROM student a,course b,sc c,teacher d    
WHERE d.tname='叶平' AND d.tid=b.tid AND b.cid=c.cid AND c.sid=a.sid)

6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
（1号-刘一 2号-钱二 3号-张三 6号-赵六除了4号和5号）---考查EXISTS

SELECT stu.sid,stu.sname FROM student stu,sc sc_sc 
WHERE stu.sid=sc_sc.sid AND sc_sc.cid='1' 
AND EXISTS 
(SELECT * FROM sc as b WHERE b.sid=sc_sc.sid AND b.cid='2')

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；（除了4号）

SELECT sid,sname FROM student WHERE sid IN
(SELECT c.sid FROM course b,sc c,teacher d 
WHERE d.tname='叶平' AND d.tid=b.tid AND b.cid=c.cid )

8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
（3号-张三）

SELECT stu_stu.sid,stu_stu.sname FROM
(SELECT student.sid,student.sname,score,
(SELECT score FROM sc sc_sc WHERE sc_sc.sid=student.sid AND sc_sc.cid='2') score2
FROM student,sc WHERE student.sid=sc.sid and cid='1') stu_stu 
WHERE score2 <score

9、查询所有课程有成绩小于60分的同学的学号、姓名；（1号、3号、5号、6号除了2号和4号）

SELECT sid,sname FROM student WHERE sid IN(
SELECT sid FROM sc WHERE score<60)

10、查询没有学全所有课的同学的学号、姓名；（4号、5号、6号）

SELECT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid=sc_sc.sid GROUP BY stu.sid,stu.sname 
HAVING COUNT(cid)!='4'

写法二（推荐）：
SELECT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid=sc_sc.sid GROUP BY stu.sid,stu.sname 
HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course)

11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；（All）

SELECT DISTINCT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid=sc_sc.sid AND sc_sc.cid IN
(SELECT cid FROM sc WHERE sid='1')

12、查询跟学号为“1”的同学学的所有课一样的其他同学学号和姓名；（2号和3号）

SELECT stu.sid,stu.sname FROM student stu,sc sc_sc
WHERE stu.sid!='1' AND stu.sid=sc_sc.sid GROUP BY stu.sid,stu.sname 
HAVING COUNT(cid) = (SELECT COUNT(cid) FROM sc WHERE sid='1')

13、把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；（61.4）

思路：
1）叶平老师教语文，cid='1'，先查cid为1的所有成绩，一共5条
MYSQL>SELECT sc_sc.score,sc_sc.cid FROM sc sc_sc,course cou,teacher tea 
WHERE tea.tname='叶平' AND tea.tid=cou.tid AND cou.cid=sc_sc.cid
2）计算出平均成绩
MYSQL>SELECT AVG(score) FROM sc sc_sc,course cou,teacher tea 
WHERE tea.tname='叶平' AND tea.tid=cou.tid AND cou.cid=sc_sc.cid
3）修改成绩为平均成绩
UPDATE sc SET score=(第2步) WHERE cid=(第1步)

14、删除学习“叶平”老师课的SC表记录；

看图其实是这么一句话：DELETE FROM sc WHERE cid='1'，所以：
DELETE sc FROM course cou,teacher tea
WHERE tea.tname='叶平' AND tea.tid=cou.tid AND cou.cid=sc_sc.cid

15、向sc表中插入一条记录，要求：没有上过编号“3”课程的同学学号、2号课的平均成绩；（6号童鞋没有上过3号课程）

INSERT sc SELECT sid,'2',(SELECT AVG(score) FROM sc WHERE cid='2') 
FROM student WHERE sid NOT IN (SELECT sid FROM sc WHERE cid='3')

16、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分（待更正语句有错）

SELECT t.sid AS 学生ID,
(SELECT score FROM sc sc_sc WHERE sc_sc.cid='1' AND sc_sc.sid=t.sid) AS 语文,
(SELECT score FROM sc sc_sc WHERE sc_sc.cid='2' AND sc_sc.sid=t.sid) AS 数学,
(SELECT score FROM sc sc_sc WHERE sc_sc.cid='3' AND sc_sc.sid=t.sid) AS 英语,
AVG(t.score) AS 平均成绩
FROM sc AS t
GROUP BY t.sid ORDER BY AVG(t.score)

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT nn.cid As 课程ID,nn.score AS 最高分,mm.score AS 最低分 FROM sc nn,sc AS mm 
WHERE nn.cid=mm.cid AND nn.score=(
      SELECT MAX(sc_sc.score) FROM sc AS sc_sc,student AS stu 
          WHERE nn.cid=sc_sc.cid AND stu.sid=sc_sc.sid GROUP BY sc_sc.cid)
AND mm.score=(
      SELECT MIN(sc_sc_min.score) FROM sc AS sc_sc_min
          WHERE mm.cid=sc_sc_min.cid GROUP BY sc_sc_min.cid)

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

SELECT sc_sc.cid AS 课程号,MAX(cou.cname) AS 课程名,
IFNULL(AVG(score),0) AS 平均成绩 ,
100 * SUM(CASE WHEN  IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 
    FROM sc sc_sc,course cou
    WHERE sc_sc.cid=cou.cid
    GROUP BY sc_sc.cid
    ORDER BY 100 * SUM(CASE WHEN  IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

19、查询如下课程平均成绩和及格率的百分数(用"1行"显示):语文-1、数学-2、英语-3、物理-4

SELECT SUM(CASE WHEN cid ='1' THEN score ELSE 0 END)/SUM(CASE cid WHEN '1' THEN 1 ELSE 0 END) AS 语文平均分 
        ,100 * SUM(CASE WHEN cid = '1' AND score >= 60 THEN 1 ELSE 0 END)/
                SUM(CASE WHEN cid = '1' THEN 1 ELSE 0 END) AS 语文及格百分数 
        ,SUM(CASE WHEN cid = '2' THEN score ELSE 0 END)/SUM(CASE cid WHEN '2' THEN 1 ELSE 0 END) AS 数学平均分 
        ,100 * SUM(CASE WHEN cid = '2' AND score >= 60 THEN 1 ELSE 0 END)/
                SUM(CASE WHEN cid = '2' THEN 1 ELSE 0 END) AS 数学及格百分数 
        ,SUM(CASE WHEN cid = '3' THEN score ELSE 0 END)/SUM(CASE cid WHEN '3' THEN 1 ELSE 0 END) AS 英语平均分 
        ,100 * SUM(CASE WHEN cid = '3' AND score >= 60 THEN 1 ELSE 0 END)/
                SUM(CASE WHEN cid = '3' THEN 1 ELSE 0 END) AS 英语及格百分数 
        ,SUM(CASE WHEN cid = '4' THEN score ELSE 0 END)/SUM(CASE cid WHEN '4' THEN 1 ELSE 0 END) AS 物理平均分 
        ,100 * SUM(CASE WHEN cid = '4' AND score >= 60 THEN 1 ELSE 0 END)/
                SUM(CASE WHEN cid = '4' THEN 1 ELSE 0 END) AS 物理及格百分数       
FROM sc

20、查询不同老师所教不同课程平均分从高到低显示

SELECT MAX(tea.tid) AS 教师ID,MAX(tea.tname) AS 教师姓名,
        sc_sc.cid AS 课程ID,MAX(cou.cname) AS 课程名称,AVG(score) AS 平均成绩 
    FROM sc AS sc_sc,course AS cou,teacher AS tea
    WHERE tea.tid=cou.tid and cou.cid=sc_sc.cid
    GROUP BY cou.cid ORDER BY AVG(score) DESC

21、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT sc_sc.cid AS 课程ID, cname AS 课程名称 
        ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]'
        ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85-70] '
        ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '[70-60]' 
        ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS '[60-]' 
    FROM sc sc_sc,course cou
    WHERE sc_sc.cid=cou.cid GROUP BY sc_sc.cid,cname;

22、查询学生平均成绩及其名次

SELECT 1+(SELECT COUNT(DISTINCT 平均成绩) FROM 
            (SELECT sid,AVG(score) AS 平均成绩 FROM sc GROUP BY sid ) AS T1 
          WHERE 平均成绩 > T2.平均成绩) AS 名次, 
      sid AS 学生学号,平均成绩 
    FROM (SELECT sid,AVG(score) 平均成绩 FROM sc GROUP BY sid ) AS T2 
    ORDER BY 平均成绩 DESC;

23、查询各科成绩前三名的记录:(不考虑成绩并列情况)---------SQL不识别TOP

SELECT sc_sc.sid AS 学生ID,sc_sc.cid AS 课程ID,sc_sc.score AS 分数 
      FROM sc sc_sc WHERE sc_sc.score IN 
        (SELECT TOP 3 score FROM sc WHERE cid=sc_sc.cid ORDER BY score DESC ) 
      ORDER BY sc_sc.cid;

24、查询每门课程被选修的学生数

SELECT cid,COUNT(sid) FROM sc GROUP BY cid;

25、查询出只选修了一门课程的全部学生的学号和姓名

SELECT stu.sid,stu.sname FROM student stu,course cou,sc sc_sc
    WHERE sc_sc.cid=cou.cid AND sc_sc.sid=stu.sid AND sc_sc.cid=
        (SELECT cid FROM sc GROUP BY sid HAVING COUNT(cid)=1)

26、查询男生、女生人数

SELECT COUNT(ssex) AS 男生人数 FROM student WHERE ssex='男' 
SELECT COUNT(ssex) AS 女生人数 FROM student GROUP BY ssex HAVING ssex='女'

27、查询姓“张”的学生名单

SELECT sname FROM student WHERE sname LIKE '张%';

28、查询同名同姓学生名单，并统计同名人数

SELECT sname,COUNT(*) AS 同名人数 FROM student GROUP BY sname HAVING COUNT(*)>1;

29、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT sc_sc.sid,stu.sname,AVG(score) FROM student stu,sc sc_sc
    WHERE stu.sid=sc_sc.sid GROUP BY sc_sc.sid,stu.sname 
            HAVING AVG(sc_sc.score)>85

30、查询“物理”课分数不及格的学生姓名和分数

SELECT sname,IFNULL(score,0) AS 成绩 FROM student stu,sc sc_sc,course cou
    WHERE sc_sc.sid=stu.sid AND sc_sc.cid=cou.cid 
        AND cou.cname='物理' AND sc_sc.score <60;

31、查询所有学生的选课情况；

SELECT sc_sc.sid,sc_sc.cid,stu.sname,cou.cname 
FROM sc sc_sc,student stu,course cou
    WHERE sc_sc.sid=stu.sid AND sc_sc.cid=cou.cid;

32、查询课程的成绩在80分以上的学生姓名、课程名称和分数；

SELECT DISTINCT stu.sname AS 学生姓名,cou.cname AS 课程名称,sc_sc.score AS 分数
    FROM student stu,sc sc_sc,course cou
    WHERE sc_sc.score>=80 AND sc_sc.cid=cou.cid AND sc_sc.sid=stu.sid;

33、查询选修“周磊”老师所授课程的学生中，成绩最高的学生姓名及其成绩（李四-93）

SELECT stu.sname,score FROM student stu,sc sc_sc
    WHERE sc_sc.sid=stu.sid AND sc_sc.score= 
        (SELECT MAX(score) FROM sc,teacher,course 
        WHERE tname='周磊' AND teacher.tid=course.tid AND course.cid=sc.cid );

34、查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT DISTINCT sc_sca.sid,sc_sca.cid,sc_scb.score FROM sc sc_sca,sc sc_scb 
    WHERE sc_sca.score=sc_scb.score AND sc_sca.cid <>sc_scb.cid;

35、查询两门以上课程大于80分的同学的学号及其平均成绩

SELECT sid,AVG(IFNULL(score,0)) FROM sc WHERE sid IN
    (SELECT sid FROM sc WHERE score<60 GROUP BY sid HAVING COUNT(*)>2)GROUP BY sid;

36、检索“4”课程分数小于60，按分数降序排列的同学学号

SELECT sid FROM sc WHERE cid='4' AND score <60 ORDER BY score DESC;