df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录

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)

employees hr/employees
#emp_idint64namestringdept_idint64manager_idint64hire_datedatesalaryint64titlestringemailstring
1 1 Grace Liu 1 null 2015-10-01 98000 CEO grace.liu@corp.example
2 2 Leo Wang 2 1 2017-09-09 72000 VP Engineering leo.wang@corp.example
3 3 Sara Chen 3 1 2020-06-05 70000 VP Sales sara.chen@corp.example
4 4 Lucas Zhang 4 2 2023-04-20 52000 Engineering Manager lucas.zhang@corp.example
5 5 Mia Zhao 5 2 2016-07-16 52000 Data Manager mia.zhao@corp.example
6 6 Kevin Wu 3 3 2016-10-23 45000 Sales Manager kevin.wu@corp.example
7 7 Lily Xu 6 4 2025-03-17 48000 Infra Lead lily.xu@corp.example
8 8 Tom Huang 4 4 2022-01-03 46000 Senior Engineer tom.huang@corp.example
9 9 Liam Zhou 4 4 2020-10-01 39000 Software Engineer liam.zhou@corp.example
10 10 Nina Yang 4 4 2020-10-02 39000 Software Engineer nina.yang@corp.example
11 11 Oscar Lin 4 8 2022-07-15 26000 Junior Engineer oscar.lin@corp.example
12 12 Linda Ma 5 5 2016-08-25 41000 Data Engineer linda.ma@corp.example
13 13 Eric Sun 5 5 2021-09-09 33000 Data Analyst eric.sun@corp.example
14 14 Ivy He 5 5 2018-05-29 41000 Data Engineer ivy.he@corp.example
15 15 Lei Gao 5 12 2016-06-02 27000 Junior Data Engineer lei.gao@corp.example
16 16 Hao Guo 6 7 2016-12-18 44000 Site Reliability Engineer hao.guo@corp.example
17 17 Fang Luo 6 7 2020-11-11 38000 Infra engineer fang.luo@corp.example
18 18 Jun Liang 6 16 2020-09-08 25000 Junior Engineer jun.liang@corp.example
19 19 Dana Song 3 6 2016-10-13 31000 Account Executive dana.song@corp.example
20 20 Victor Tang 3 6 2018-09-12 31000 Account Executive victor.tang@corp.example
显示前 20 行,共 40 行

期望输出 (case base,行顺序不限)

#emp_idInt64nameStringdept_idInt64joined_onDatesalaryInt64job_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 行