Oracle数据库练习
单行函数练习
1.显示系统时间(注:日期+时间)。
select to_char(sysdate, ‘YYYY"年"MM"月"DD"日" HH:MI:SS’) 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) from employees order by last_name asc;
4.查询各员工的姓名,并显示出各员工在公司工作的月份数。
select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month from employees;
5.查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列。
Select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month
from employees order by workded_month desc;
6.做一个查询,产生下面的结果
<last_name> earns monthly but wants <salary3>
Dream Salary
King earns $24000 monthly but wants $72000
select last_name || ’ earns ‘|| to_char(salary,’$999999’)||’ monthly,but wants '||to_char(3salary,’$999999’) "Dream Salary"from employees;
7.使用decode函数,按照下面的条件:
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
select last_name “Last_name”,job_id “Job_id”,decode(job_id,‘AD_PRES’,‘A’,‘ST_MAN’,‘B’, ‘IT_PROG’,‘C’, ‘SA_REP’,‘D’, ‘ST_CLERK’,‘E’) “Grade” from employees;
8.将第7题的查询用case函数再写一遍。
select last_name “Last_name”,job_id “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;