【题目】
# 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;