数据库练习

-- 作业讲解

-- 1. 查询 01 课程成绩比 02 课程成绩高的学生的学号、姓名以及两门课分别的成绩

SELECT st .s_id ,st .s_name ,a .s_score AS score01,

b .s_score AS score02

FROM

(

SELECT * FROM score

WHERE c_id = '01'

) a JOIN

(

SELECT * FROM score

WHERE c_id = '02'

) b

ON a .s_id = b .s_id

JOIN student st

ON a .s_id = st .s_id

WHERE a .s_score > b .s_score

-- 2. 查询所有学生的学号、姓名、选课门数以及平均成绩

SELECT st .s_id ,st .s_name , COUNT ( sc .c_id ) AS

cnt, AVG ( IFNULL ( s_score, 0 ))

AS avg_score

FROM score sc RIGHT JOIN student st

ON sc .s_id = st .s_id

GROUP BY sc .s_id -- 3. 查询没有学过张三老师所教课程的学生的学号和姓名

-- 第一种方式：使用子查询

SELECT s_id,s_name

FROM student

WHERE s_id NOT IN

(

SELECT s_id FROM score WHERE c_id IN

(

SELECT c_id FROM course

WHERE t_id =

( SELECT t_id FROM teacher WHERE t_name = ' 张三 ' )

)

)

-- 使用多表联接方式

SELECT s_id,s_name

FROM student

WHERE s_id NOT IN

(

SELECT sc .s_id

FROM score sc JOIN student st

ON sc .s_id = st .s_id

JOIN course co

ON sc .c_id = co .c_id

JOIN teacher te

ON co .t_id = te .t_id

WHERE te .t_name = ' 张三 '

)

-- 4. 查询所有选修的都不及格课程成绩的学生的学号和姓名

-- 等价于最高分不及格

SELECT st .s_id ,s_name

FROM score sc JOIN student st

ON sc .s_id = st .s_id

GROUP BY sc .s_id HAVING MAX ( s_score ) < 60

-- 5. 查询每门课程的编号、课程名称、平均分、最高分、最低分

SELECT sc .c_id ,c_name, AVG ( s_score ) avg_score,

MAX ( s_score ) max_score, MIN ( s_score ) AS min_score

FROM score sc JOIN course co

ON sc .c_id = co .c_id

GROUP BY sc .c_id

-- 6. 查询每个学生的学号、姓名以及每门课的成绩（每个学生对应查询结果中的一条记

录，多门课程成绩显示在同一行）

-- 对应的是一个比较特殊的应用场景： 行转列

SELECT st .s_id ,st .s_name ,

MAX ( IF ( c_name = ' 语文 ' ,s_score, 0 )) AS yuwen,

MAX ( IF ( c_name = ' 数学 ' ,s_score, 0 )) AS shuxue, MAX ( IF ( c_name = ' 英语 ' ,s_score, 0 )) AS yingyu

FROM score sc RIGHT JOIN student st

ON sc .s_id = st .s_id

LEFT JOIN course co

ON sc .c_id = co .c_id

GROUP BY sc .s_id

-- 不相关的表现：

-- 1. 子查询可以独立执行的，没有用到主查询的字段值

-- 2. 整个查询执行过程中子查询只执行一次

SELECT *

FROM employees

WHERE salary = ( SELECT MIN ( salary ) FROM employees )

-- 相关子查询

-- 问题 1 ：查询工资比全公司所有员工的平均工资高的员工信息

-- 问题 2 ：查询工资比本部门平均工资高的所有员工信息

-- 特点：

-- 1. 子查询中用到了外层查询的表中字段，不能独立的执行

-- 2. 子查询执行了多次（效率不高）

SELECT *

FROM employees e

WHERE salary > (

SELECT AVG ( salary ) FROM employees

WHERE department_id = e .department_id

)

-- 需求： 查询每个部门的信息以及部门对应的员工人数

-- 使用 join 的方式

SELECT d. * , COUNT ( e .employee_id ) AS cnt

FROM departments d LEFT JOIN employees e

ON d .department_id = e .department_id

GROUP BY d .department_id

-- 使用相关子查询的方式进行实现

-- 子查询还可以出现在 select 短语的后面

SELECT * , ( SELECT COUNT ( 1 ) FROM employees

WHERE department_id = d .department_id ) cnt

FROM departments d

-- 需求：查询有员工的部门信息

-- 使用子查询实现

SELECT * FROM departments

WHERE department_id

IN

(

SELECT department_id FROM employees )

-- 使用多表联接

SELECT DISTINCT d. *

FROM departments d JOIN employees e

ON d .department_id = e .department_id

-- 实现相关子查询的方式

-- exists 子查询返回的结果为 1 或 0

-- 只要子查询有结果行记录返回则为 1 ，否则为 0

-- select exists(select * from employees where employee_id=-1)

SELECT *

FROM departments d

WHERE EXISTS

( SELECT * FROM employees WHERE department_id = d .department_id )

SELECT SUM(s_score) FROM score WHERE c_id=02

 

 

 

 

 查询没有学全所有课的学生的学号、姓名

 1990年出生的学生名单

 

查询出每门课程的及格人数和不及格人数

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名|

 

 行表转列表

 -查询任何一门课程成绩在70分以上的姓名、课程名称和分数（与上题类似）

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

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

SELECT DISTINCT s.c_id,s.s_id,s.s_score FROM 
score s INNER JOIN score p on s.s_id=p.s_id WHERE 
s.s_score=p.s_score AND 
s.c_id !=p.c_id 

-查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

-查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

查询学过“张三”老师所教的所有课的同学的学号、姓名

.

-查询没学过"张三"老师讲授的任一门课程的学生姓名（与上题类似，"没学过"用not in来实现)

 

-查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）

 

-查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名

 

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