分组与子查询

第一章 分组查询

1.GROUP BY子句

请编写 SQL 语句，查询教师表 teachers，统计不同年龄教师的人数，并将结果按照年龄从大到小排列，返回列名显示为 age_count。

SELECT age,count(age) as age_count
FROM teachers
group by age
order by age desc

请编写 SQL 语句，查询教师表 teachers 和课程表 courses,统计每个老师教授课程的数量，并将结果按课程数量降序排列，课程数量相同时按教师姓名升序排列，返回老师姓名和课程数量，分别命名为 teacher_name 和 course_count。

SELECT t.name as teacher_name,count(c.teacher_id) as course_count
FROM teachers t
left join courses c on t.id=c.teacher_id
group by (t.id)
order by course_count desc, teacher_name asc;

2.HAVING 子句

请编写 SQL 语句，从 teachers 表中，筛选出同一国家的教师平均年龄大于所有教师平均年龄的国家，并获取这些国家的所有教师信息。

select * from teachers where country in (
    select country from teachers group by country having avg(age) > 
    (select avg(age) from teachers ) 
)

第二章 简单子查询

1.SELECT 语句中的子查询

请编写 SQL 语句，查询 courses 表和 teachers 表，查询 'Big Data' 课程对应的老师姓名。
SELECT t.name
FROM teachers t
WHERE t.id=(
SELECT c.teacher_id
FROM courses c
WHERE c.name='Big Data'
)

2.INSERT 语句中的子查询

请编写 SQL 语句，将教师表 teachers 中年龄大于 20（不包括 20） 的教师的数据复制到与它结构相同的空表 teachers_bkp 表中。

INSERT INTO teachers_bkp
SELECT *
FROM teachers
WHERE age>20

3.UPDATE 语句中的子查询

学校教务处排课时发现教师 Western Venom 创建的课程有误，现紧急需要将该教师创建的课程名称修改为 Java，请你使用相关的 SQL 语句完成。(课程中例题需收费)

UPDATE `courses`
SET `name` = 'Java'
WHERE `teacher_id` = (
	SELECT `id`
    FROM `teachers`
    WHERE `name` = 'Western Venom'
);

4.DELETE 语句中的子查询

请编写 SQL 语句，删除教师表 teachers 中在 2020 年前（不包括 2020 年）创建过课程的教师。

DELETE FROM teachers
WHERE id in(
    SELECT teacher_id
    FROM courses
    WHERE created_at<'2020-01-01'
)

第三章：子查询进阶

1.内联视图子查询

请编写 SQL 语句，使用内连接（INNER JOIN），连接 teachers 表和 courses 表，并将查询的课程名称（起别名为 course_name）、学生总数、讲师姓名（起别名为 teacher_name）信息作为查询表，并从该查询表中选出学生总数最多的信息。

select courses.name course_name, student_count,teachers.name teacher_name 
from teachers 
inner join courses on teachers.id = courses.teacher_id 
where student_count = (
    select max(student_count) s_c from courses
)

2.IN 操作符的多行子查询

现需要查询国籍为美国（USA）的教师所开的所有课程，请使用 IN 操作符进行多行子查询。

SELECT `name`
FROM `courses`
WHERE `teacher_id` IN (
	SELECT `id`
	FROM `teachers`
	WHERE `country` = 'USA'
);

3.ANY 操作符的多行子查询

请编写 SQL 语句，对于 Southern Emperor 教师的所有课程，从 courses 表和 teachers 表中查询课程创建时间晚于其中任意一门课程创建时间的课程名称（不包含该教师的课程）。

select name from courses --（3）查比S E课程创建时间晚的 课程名称
where created_at > ANY (
    select created_at from courses --（2）根据S E的id查所有他课程的创建时间
    where teacher_id = (
        select id from teachers --（1）查S E的id
        where name = 'Southern Emperor'
    )
)
and teacher_id <> ( --（4）这个查出来的课程还不能是 S E 他自己的
        select id from teachers
        where name = 'Southern Emperor'
    )

4.ALL 操作符的多行子查询

请编写 SQL 语句，查询教师表 teachers 和课程表 courses，查询最年长的老师所开课程的学生数，最后返回学生数均超过该教师的课程的课程信息。

SELECT *
FROM `courses`
WHERE student_count > ALL(
    SELECT c.student_count as student_count
    FROM courses AS c,teachers as t
    WHERE c.teacher_id = t.id and t.age IN (
        SELECT MAX(age)
        FROM teachers
    )
)

5.多列子查询

6.HAVING 子句中的子查询

请编写 SQL 语句，连接 courses 与 teachers 表，统计不同国籍教师所开课程的学生总数，对于没有任课的老师，学生总人数计为 0 。最后查询教师国籍是 'U' 开头且这个国籍的所有教师的学生总数在 2000 到 5000 之间（包括 2000 和 5000）的教师国籍及学生总数 （别名为 student_count ），结果按照学生总数降序排列，如果学生总数相同，则按照教师国籍升序排列。

select t.country country ,sum(c.student_count) student_count
from teachers t 
inner join courses c 
on t.id = c.teacher_id
where t.country like 'U%'  
group by t.country
having student_count between 2000 and 5000
order by student_count desc, country