EXCLUDE / REPLACE / RENAME
简单
24-duckdb-sql-only DuckDB SQL 专属
starexcludereplacerenamefriendly-sql
整理员工表的列:
- 去掉
email 和 manager_id 两列;
name 去掉首尾空格(列名不变);
salary 是月薪,换成年薪(乘以 12),列名不变;
title 改名为 job_title,hire_date 改名为 joined_on,值都不变;
- 其余列(
emp_id、dept_id)原样保留。
null 原样保留(月薪为 null 的年薪也为 null)。
考点:在 * 的基础上一次性完成“去掉列、替换列的值、给列改名”,不要逐列列出。判题只看结果,列顺序不限。
输入
employees(emp_id, name, dept_id, manager_id, hire_date, salary, title, email)
输出
列为 emp_id, name, dept_id, joined_on, salary, job_title,每个员工一行,行顺序不限。
输入表 (case base,共 2 个 case:base、edge)
期望输出 (case base,行顺序不限)
| # | emp_idInt64 | nameString | dept_idInt64 | joined_onDate | salaryInt64 | job_titleString |
| 1 |
1 |
Grace Liu |
1 |
2015-10-01 |
1176000 |
CEO |
| 2 |
2 |
Leo Wang |
2 |
2017-09-09 |
864000 |
VP Engineering |
| 3 |
3 |
Sara Chen |
3 |
2020-06-05 |
840000 |
VP Sales |
| 4 |
4 |
Lucas Zhang |
4 |
2023-04-20 |
624000 |
Engineering Manager |
| 5 |
5 |
Mia Zhao |
5 |
2016-07-16 |
624000 |
Data Manager |
| 6 |
6 |
Kevin Wu |
3 |
2016-10-23 |
540000 |
Sales Manager |
| 7 |
7 |
Lily Xu |
6 |
2025-03-17 |
576000 |
Infra Lead |
| 8 |
8 |
Tom Huang |
4 |
2022-01-03 |
552000 |
Senior Engineer |
| 9 |
9 |
Liam Zhou |
4 |
2020-10-01 |
468000 |
Software Engineer |
| 10 |
10 |
Nina Yang |
4 |
2020-10-02 |
468000 |
Software Engineer |
| 11 |
11 |
Oscar Lin |
4 |
2022-07-15 |
312000 |
Junior Engineer |
| 12 |
12 |
Linda Ma |
5 |
2016-08-25 |
492000 |
Data Engineer |
| 13 |
13 |
Eric Sun |
5 |
2021-09-09 |
396000 |
Data Analyst |
| 14 |
14 |
Ivy He |
5 |
2018-05-29 |
492000 |
Data Engineer |
| 15 |
15 |
Lei Gao |
5 |
2016-06-02 |
324000 |
Junior Data Engineer |
| 16 |
16 |
Hao Guo |
6 |
2016-12-18 |
528000 |
Site Reliability Engineer |
| 17 |
17 |
Fang Luo |
6 |
2020-11-11 |
456000 |
Infra engineer |
| 18 |
18 |
Jun Liang |
6 |
2020-09-08 |
300000 |
Junior Engineer |
| 19 |
19 |
Dana Song |
3 |
2016-10-13 |
372000 |
Account Executive |
| 20 |
20 |
Victor Tang |
3 |
2018-09-12 |
372000 |
Account Executive |
| 21 |
21 |
Wen Han |
3 |
2017-01-06 |
354000 |
Account Executive |
| 22 |
22 |
Lu Feng |
3 |
2022-03-07 |
432000 |
Sales Engineer |
| 23 |
23 |
Yan Deng |
3 |
2020-10-04 |
144000 |
Sales Intern |
| 24 |
24 |
Zoe Cao |
7 |
2016-08-30 |
732000 |
General Counsel |
| 25 |
25 |
Ping Peng |
4 |
2025-04-09 |
444000 |
Software Engineer |
| 26 |
26 |
Qing Xie |
4 |
2022-05-05 |
360000 |
QA Engineer |
| 27 |
27 |
Rui Lu |
null |
2017-05-22 |
264000 |
Contractor |
| 28 |
28 |
Uma Jiang |
null |
2018-07-03 |
288000 |
Contractor |
| 29 |
29 |
Xin Cai |
5 |
2023-01-27 |
564000 |
Data Scientist |
| 30 |
30 |
Bob Pan |
4 |
2023-01-13 |
636000 |
Staff Engineer |
| 31 |
31 |
Chen Du |
6 |
2022-07-15 |
456000 |
Infra Engineer |
| 32 |
32 |
Amy Yu |
3 |
2016-09-10 |
558000 |
Account Manager |
| 33 |
33 |
Larry Ren |
2 |
2022-06-21 |
792000 |
Engineering Director |
| 34 |
34 |
Wei Shi |
5 |
2022-07-26 |
312000 |
Junior Data Analyst |
| 35 |
35 |
Zhang Min |
4 |
2020-06-12 |
444000 |
Software Engineer |
| 36 |
36 |
王磊 |
3 |
2016-07-22 |
336000 |
Sales Rep |
| 37 |
37 |
李静 |
5 |
2024-02-29 |
276000 |
Research Assistant |
| 38 |
38 |
Henry Kong |
6 |
2016-07-09 |
300000 |
Junior Engineer |
| 39 |
39 |
Logan Bai |
4 |
2022-03-30 |
552000 |
Senior Engineer |
| 40 |
40 |
Sophie Fan |
3 |
2025-08-17 |
336000 |
Sales Rep |
共 40 行
参考答案 · EXCLUDE / REPLACE / RENAME