单行函数案列讲解_01

USE myemployees;

1. 显示系统时间(注：日期+时间)

– select NOW();

2. 查询员工号，姓名，工资，以及工职提高百分之20后的结果(NEW salary)

/*
select
employee_id,
last_name,
salary,
salary * 1.2 “NEW salary”
from
employees ;
*/

3. 将员工的姓名按首字母排序，并写出姓名的长度（LENGTH）

/*
select
length(last_name) 长度,
substr(last_name, 1, 1) 首字符,
last_name
from
employees
order by 首字符 ;
*/

4. 做一个查询，产生下面结果

/*
<last_name> earns monthly but wants<salary*3>
Dream Salary
King earns 24000 monthly but wants 72000
*/
/*解答：
select
concat(
last_name,
‘earns’,
salary,
‘monthly but wants’,
salary
) as “Dream salary”
from
employees
where salary = 24000;
*/

5.使用case-when 按照下面的条件

/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLEAR E
产生下面的结果
last_name JOB_id grade
King AD_PRES A
*/
/解答：/
SELECT
last_name AS 姓名 ,job_id AS job,
CASE
job_id
WHEN ‘AD_PRES’
THEN ‘A’
WHEN ‘ST_MAN’
THEN ‘B’
WHEN ‘IT_PROG’
THEN ‘C’
WHEN ‘SA_PRES’
THEN ‘D’
WHEN ‘ST_CLEAR’
THEN ‘E’
END AS Grade
FROM
employees ;