数据分析师

eternal-immortal 2021-12-10 原文

根据下面3张表格,按要求使用SQL语句实现结果查询

**班级信息表**
class_no	st_no	st_name
c1	st1	Sam
c1	st2	lucy
c2	st3	jean
c2	st4	lock
c2	st5	lily

**成绩表**
st_no	subject_no	score
st1	1	72
st1	2	89
st1	3	78
st3	1	87
st3	3	89
st4	1	98
st4	2	94

**课程表**
subject_no	subject_name
1	Chinese
2	mathmatics
3	English

(1) 查询每个班级各科成绩总和

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

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

解:

(1)

SELECT 班级信息表.[class_no] , 课程表.[subject_name] , SUM(成绩表.[score]) AS 成绩总和

FROM 班级信息表,课程表,成绩表

WHERE 班级信息表.[st_no] = 成绩表.[st_no] AND 成绩表.[subject_no] = 课程表.[subject_no]

GROUP BY 班级信息表.[class_no] ,课程表.[subject_name];

**(1)运行结果**
class_no	subject_name	成绩总和
c1	Chinese	72
c1	English	78
c1	mathmtics	89
c2	Chinese	185
c2	English	89
c2	mathmtics	94

(2)

SELECT 班级信息表.[class_no] , COUNT(成绩表.[st_no]) AS 人数

FROM (成绩表 LEFT JOIN 班级信息表 ON 成绩表.[st_no] = 班级信息表.[st_no]) LEFT JOIN 课程表 ON 成绩表.[subject_no] = 课程表.[subject_no]

WHERE 课程表.[subject_name] = "Chinese" AND 成绩表.score > 85

GROUP BY 班级信息表.[class_no] ;

**(2)运行结果**
class_no	人数
c2	2

(3)

SELECT * FROM

(SELECT 班级信息表.[class_no] , 班级信息表.[st_name] , SUM(IIf(课程表.[subject_name] = "Chinese",成绩表.score,NULL)) AS 语文成绩 , SUM(IIf(课程表.[subject_name] = "mathmatics",成绩表.score,NULL)) AS 数学成绩

FROM 班级信息表,成绩表,课程表

WHERE 成绩表.[st_no] = 班级信息表.[st_no] AND 成绩表.[subject_no] = 课程表.[subject_no]

GROUP BY 班级信息表.[class_no] , 班级信息表.[st_name])

WHERE 语文成绩 > 数学成绩;