描述
题目描述:请编写 SQL 语句,查询课程表 courses
所有课程中的课程名(name
)和创建日期中的年份(别名:year
)和月份( 别名:month
)。
表定义:courses
(课程表)
列名 | 类型 | 注释 |
---|---|---|
id | int unsigned | 主键 |
name | varchar | 课程名称 |
student_count | int | 学生总数 |
created_at | datetime | 课程创建时间 |
teacher_id | int unsigned | 讲师 id |
- 查询返回列名需要与样例输出的列名大小写一致
- 如果 SELECT 的值中有 null 值,则会返回 null。
样例
样例一
表内容:courses
id | name | student_count | created_at | teacher_id |
---|---|---|---|---|
1 | Senior Algorithm | 880 | 2020-06-01 09:03:12 | 4 |
2 | System Design | 1350 | 2020-07-18 10:03:12 | 3 |
3 | Django | 780 | 2020-02-29 12:03:12 | 3 |
4 | Web | 340 | 2020-04-22 13:03:12 | 4 |
5 | Big Data | 700 | 2020-09-11 16:03:12 | 1 |
6 | Artificial Intelligence | 1660 | 2018-05-13 18:03:30 | 3 |
7 | Java P6+ | 780 | 2019-01-19 13:03:12 | 3 |
8 | Data Analysis | 500 | 2019-07-12 13:03:12 | 1 |
10 | Object Oriented Design | 300 | 2020-08-08 13:03:12 | 4 |
12 | Dynamic Programming | 2000 | 2018-08-18 20:03:12 | 1 |
在运行你的 SQL 语句之后,表应返回:
name | year | month |
---|---|---|
Advanced Algorithms | 2020 | 6 |
System Design | 2020 | 7 |
Django | 2020 | 2 |
Web | 2020 | 4 |
Big Data | 2020 | 9 |
Artificial Intelligence | 2018 | 5 |
Java P6+ | 2019 | 1 |
Data Analysis | 2019 | 7 |
Object Oriented Design | 2020 | 8 |
Dynamic Programming | 2018 | 8 |
样例二
表内容:courses
id | name | student_count | created_at | teacher_id |
---|---|---|---|---|
1 | Senior Algorithm | 880 | NULL | 4 |
2 | System Design | 1350 | NULL | 3 |
3 | Django | 780 | NULL | 3 |
4 | Web | 340 | NULL | 4 |
5 | Big Data | 700 | NULL | 1 |
6 | Artificial Intelligence | 1660 | NULL | 3 |
7 | Java P6+ | 780 | NULL | 3 |
8 | Data Analysis | 500 | NULL | 1 |
10 | Object Oriented Design | 300 | NULL | 4 |
12 | Dynamic Programming | 2000 | NULL | 1 |
在运行你的 SQL 语句之后,表应返回:
name | year | month |
---|---|---|
Advanced Algorithms | NULL | NULL |
System Design | NULL | NULL |
Django | NULL | NULL |
Web | NULL | NULL |
Big Data | NULL | NULL |
Artificial Intelligence | NULL | NULL |
Java P6+ | NULL | NULL |
Data Analysis | NULL | NULL |
Object Oriented Design | NULL | NULL |
Dynamic Programming | NULL | NULL |
今天的打卡任务很简单 就一条代码:
-- Write your SQL Query here --
-- example: SELECT * FROM XX_TABLE WHERE XXX --
SELECT name, YEAR(created_at) as year, MONTH(created_at) as month FROM courses;