Lintcode SQL教程 LEVEL5

一、分组查询

1.GROUP BY 子句

2078 · 查询不同年龄的教师的人数

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

SELECT `age`, COUNT(`age`) AS `age_count`
FROM `teachers`
GROUP BY `age`
ORDER BY `age` desc;

2082 · 统计每个老师教授课程的数量
描述：
请编写 SQL 语句，查询教师表 teachers 和课程表 courses,统计每个老师教授课程的数量，并将结果按课程数量从大到小排列，如果相同课程数量则按照教师姓名排列，返回列名老师姓名列名显示为 teacher_name ，课程数量列名显示为 course_count。

select t.name as teacher_name,ifnull(COUNT(c.ID),0) AS course_count
from teachers t left join courses c on t.id=c.teacher_id
group by t.name
order by course_count desc,t.name;

2. HAVING 子句

2076 · 查询教师平均年龄大于所有教师平均年龄的国家的教师信息

描述：
请编写 SQL 语句，查询 teachers 表中，各个国家所有教师的平均年龄大于所有国家教师的平均年龄的教师信息。

代码：

SELECT * 
from teachers
where country = any(
    select country 
    from teachers
    group by `country` 
    having  avg(age)>(select avg(age) from teachers));

二、简单的子查询

1.SELECT语句中的子查询

2060 · 查询 ‘Big Data’ 课程对应的老师姓名

描述：
请编写 SQL 语句，查询 courses 表和 teachers 表，查询 ‘Big Data’ 课程对应的老师姓名。

代码：

SELECT name
FROM `teachers`
WHERE `id` = (
	SELECT `teacher_id`
	FROM `courses`
	WHERE `name` = 'Big Data'
);

2.INSERT语句中的子查询

2056 · 将教师表中年龄大于 20 的数据复制到另一张表中

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

代码：

insert into `teachers_bkp`
select * from teachers
where age>20

3.UPDATE语句中的子查询

2057 · 修改教师 Eastern Heretic 创建的课程信息

描述:
请编写 SQL 语句，从 teachers 表中查询教师名字为 Eastern Heretic 的信息，并根据教师 id 将教师 Eastern Heretic 创建的课程名称全部改为 PHP，并将学生总数设为 300 人。

代码：

UPDATE `courses`
SET `name` = 'PHP',student_count=300
WHERE `teacher_id` = (
        SELECT `id`
        FROM `teachers`
        WHERE `name` = 'Eastern Heretic'
);

4.DELETE语句中的子查询

2059 · 删除在 2020 年前创建过课程的教师

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

代码：

DELETE FROM `teachers` 
WHERE `id` IN ( 
                SELECT `teacher_id` 
                FROM `courses` 
                WHERE `created_at` < '2020-1-1' 
        );

三、子查询进阶

1.内联视图子查询

2077 · 查询学生总数最多的课程和教师信息

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

代码：

select course_name,student_count,teacher_name 
from (
    select `c`.`name` as `course_name`,c.student_count as `student_count`,`t`.`name` as `teacher_name`
    from  `teachers` `t`
    left join `courses` `c` on `c`.`teacher_id`=`t`.`id`) `T`
WHERE student_count=(SELECT max(student_count) from `courses`)

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

2065 · 查询所有年龄大于 20 岁的老师所教的所有课程的课程名

描述：
请编写 SQL 语句，联合教师表和课程表，查询课程表 courses 中所有年龄大于 20 岁的教师所教的所有课程的课程名（name）。

代码：

SELECT `name`
FROM `courses`
WHERE `teacher_id` IN (
	SELECT `id`
	FROM `teachers`
	WHERE age>20
);

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

2070 · 查询课程创建时间晚于指定教师任意一门课程创建时间的课程名称

描述：
请编写 SQL 语句，从 courses 表和 teachers 表中查询课程创建时间晚于 ‘Southern Emperor’ 教师任意一门课程创建时间的课程名称。

代码：

SELECT `name` 
FROM `courses`
WHERE `created_at` > ANY (
		SELECT `created_at`
		FROM `courses`
		WHERE `teacher_id` = (
			SELECT `id`
			FROM `teachers`
			WHERE `name` = 'Southern Emperor'
		)
	)
	AND `teacher_id` <> (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Southern Emperor'
	);

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

2066 · 查询课程学生数超过最年长教师所有课程学生数的课程信息

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

代码：

SELECT *
FROM `courses`
WHERE `student_count` > ALL(
		SELECT `student_count`
		FROM `courses`
		WHERE `teacher_id` in (
				SELECT `id`
				FROM `teachers`
				WHERE `age` = (select max(age) from teachers)
		)
);

5.多列子查询

2069 · 查询每个教师授课学生人数最高的课程名称和上课人数

描述：
请编写 SQL 语句，从课程表 courses 中查询每个教师授课学生人数最高的课程名称 name 和上课人数 student_count 。

代码：

select name,student_count 
from courses
where (teacher_id,student_count)
in (select teacher_id,max(student_count)
from `courses`
group by teacher_id)

6.HAVING子句中的子查询

2086 · 查询 ‘U’ 字开头且学生总数在 2000 到 5000 之间的教师国籍和该国籍的学生总数

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

代码：

SELECT t.country as country,sum(c.student_count) as student_count 
from teachers t 
left join courses c
on t.id=c.teacher_id
group by country
having sum(student_count)
between 2000 and 5000
and country like 'U%'