MySQL_Z7单行函数

【题目】

# 1.显示系统时间(注：日期+时间)
select now()
from dual;
select curdate() , curtime()
from dual;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
select employee_id, last_name, salary, salary * 1.2 "new salary"
from employees;

# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
select last_name, length(last_name) "name length"
from employees
order by last_name asc;

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
select concat(employee_id, last_name,' salary') "out_put"
from employees;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
select employee_id, extract( year from now()) - extract(year from hire_date) + 1  "工作年数", datediff(curdate(), hire_date) "工作天数"
from employees
order by "工作年数" desc;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或90 或110, commission_pct不为空

select last_name, hire_date, department_id
from employees
where extract(year from hire_date) >= 1997
and department_id in(80, 90, 110)
and commission_pct is not null;

# 7.查询公司中入职超过10000天的员工姓名、入职时间
select last_name, hire_date
from employees
where datediff(curdate() - hire_date) > 10000;

# 8.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3>
select concat(last_name, 'earns', salary, 'monthly but wants', salary * 3);

# 9.使用case-when，按照下面的条件：
/*job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
*/
#产生下面的结果:
select last_name , job_id, 
case job_id when 'AD_PRES' then 'a'
			when 'ST_MAN' then 'b'
			when 'IT_PROG' then 'c'
			when 'SA_REP' then 'd'
			when 'ST_CLERK' then 'e'
			end "grade";
from employees;