sql练习题，经典面试题

曾经终败给现在 2023-06-27 08:53 26阅读 0赞

# 准备数据 #

CREATE TABLE 成绩表(
    stu_no VARCHAR(10),
    subject_no INT(5),
    score INT(3)
    );
    INSERT INTO `成绩表` VALUES ('st1',1,72),('st1',2,89),('st1',3,78),('st3',1,87),('st3',3,89),('st4',1,98),('st4',2,94);
    
    CREATE TABLE 班级信息表(
    class_no VARCHAR(10),
    st_no VARCHAR(10),
    NAME VARCHAR(10)
    );
    INSERT INTO `班级信息表` VALUES ('c1','st1','sam'),('c1','st2','lucy'),('c2','st3','jean'),('c2','st4','lock'),('c2','st5','lily');
    
    
    CREATE TABLE 课程表(
    subject_no INT(5),
    subject_name VARCHAR(20)
    );
    INSERT INTO `课程表` VALUES (1,'chinese'),(2,'mathmatics'),(3,'english');

# 观察图表 #

## 班级信息表 ##

![在这里插入图片描述][20200105224026825.png]

## 成绩表 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70]

## 课程表 ##

![在这里插入图片描述][20200105224140598.png]

# 参考查询 #

## 1.查询每个班级各科成绩总和 ##

-- 1.查询每个班级各科成绩总和
    SELECT class_no, 
    SUM(CASE subject_no WHEN 1 THEN score ELSE 0 END) 语文,
    SUM(CASE subject_no WHEN 2 THEN score ELSE 0 END) 数学,
    SUM(CASE subject_no WHEN 3 THEN score ELSE 0 END) 英语
    FROM (
    SELECT class_no, st_no, NAME, subject_no, score FROM 班级信息表 c LEFT JOIN 成绩表 sc ON c.st_no = sc.stu_no 
    ) m
     GROUP BY class_no

![在这里插入图片描述][20200105223302151.png]

## 2.查询每个班级语文成绩大于85分人数 ##

-- 2.查询每个班级语文成绩大于85分人数
    SELECT class_no, COUNT(score) 人数 FROM (
    SELECT class_no, st_no, NAME, subject_no, score FROM 班级信息表 c LEFT JOIN 成绩表 sc ON c.st_no = sc.stu_no 
    ) m WHERE subject_no = '1' AND score > 85 GROUP BY class_no

![在这里插入图片描述][20200105223509931.png]

## 3. 查询语文成绩大于数学成绩的姓名和归属班级 ##

-- 3. 查询语文成绩大于数学成绩的姓名和归属班级
     SELECT sc1.*, sc2.*,
     (SELECT NAME FROM 班级信息表 WHERE st_no = sc1.stu_no) 姓名,
    (SELECT class_no FROM 班级信息表 WHERE st_no = sc1.stu_no) 班级
      FROM
     (SELECT stu_no, score FROM 成绩表 WHERE subject_no = 1) sc1 ,
     (SELECT stu_no, score FROM 成绩表 WHERE subject_no = 2) sc2 
     WHERE sc1.stu_no = sc2.stu_no AND sc1.score > sc2.score

![在这里插入图片描述][20200105223623519.png]

[20200105224026825.png]: https://img-blog.csdnimg.cn/20200105224026825.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200105224116759.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx,size_16,color_FFFFFF,t_70
[20200105224140598.png]: https://img-blog.csdnimg.cn/20200105224140598.png
[20200105223302151.png]: https://img-blog.csdnimg.cn/20200105223302151.png
[20200105223509931.png]: https://img-blog.csdnimg.cn/20200105223509931.png
[20200105223623519.png]: https://img-blog.csdnimg.cn/20200105223623519.png