过滤和排序数据练习二

1. 显示系统时间

select to_char(sysdate,'yyyy-mm-dd  hh:mi:ss') from dual;

2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

select empno,ename,sal,sal*(1+0.2) from emp;

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

select ename,length(ename) from emp order by ename;

4. 查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。

select first_name,months_between(sysdate,hire_date) worked_month from employees;

5. 查询员工的姓名和工资，按下面的形式显示

Last_name	SALARY
king	$$$$$$$$$$24000

 select last_name,lpad(salary,15,'$') from employees;

6. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列

select first_name,months_between(sysdate,hire_date) worked_month from employees order by worked_month desc;

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

<last_name> earns <salary> monthly but wants <salary*3>

Dream Salary
King earns $24000 monthly but wants $72000

select last_name ||’earns’|| to_char(salary,’$99,999’) ||’ monthly but wants’|| to_char(salary*3,’$99,999’) “Dream Salary” from employees;

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

Last_name	Hire_date	reiew
king	17-jun-87	Monday,the twenty-first of December , 1987

select last_name,hire_date,to_char(add_months(hire_date,6)+4,'DAY ",the" ddspth "of" Month","yyyy')review from employees;

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

Employees_and_their_salarys
King***************************

其中每一个*代表一千元。

select rpad(last_name,length(last_name)+salary/1000,'*') Employees_and_their_salarys from employees;

10. 使用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,job_id,decode(job_id,'AD_PRES','A',
  'ST_MAN' ,'B',
  'IT_PROG', 'C',
  'SA_REP','D',
  'ST_CLERK','E') GRADE from employees;

11. 将第9题的查询用case函数再写一遍。

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'
  else 'F'end grade from employees;