测 试
1. 显示系统时间(取别名为"DATE").
    select to_char(sysdate,'yyyy-mm-dd') "DATE" from dual

2. 查询员工号，姓名，工资(若为NULL则作为0处理)，以及工资提高百分之20%后四舍五入到整数的结果（取别名为new salary）
    select empno,ename,nvl(sal,0), round(nvl(sal,0)*1.2) "new salary" from emp

3. 将员工的姓名（取别名为"Name"）按字母表先后顺序排序，并写出姓名的长度（取别名为"length"）
    select ename "Name",length(ename) from emp order by ename

4. 查询各员工的姓名，并显示出各员工在公司工作了多少个月份（起别名为"worked_month"）四舍五入到整数.
   c

5. 查询员工的姓名和工资，按下面的形式显示结果(工资字段必须为15位,空位用$填充)
    姓名 工资
    KING $$$$$$$$$$24000
    MIKE $$$$$$$$$$$4800
    select upper(ename),lpad(sal,15,'$') from emp
   
6.  查询员工的姓名，以及在公司工作满了多少个月（worked_month），并按月份数降序排列
     select ename,trunc(months_between(sysdate,hiredate)) "worked_month" from emp order by   "worked_month"  desc

7. 做一个查询，按下面的形式显示结果
    <ename> earns <sal> monthly but wants <sal*3>
    Dream Salary
    KING earns $24000 monthly but wants $72000
    JONE earns $18000 monthly but wants $54000
    ……………………………………………………
    select upper(ename) ||'  earn  ' ||to_char(sal,'$99999999') ||'  monthly but wants  '||to_char(sal*3,'$99999999') " Dream Salary"
    from emp

8. 做一个查询，按下面的形式显示结果(注意两个日期之间的关系)需要设置语言环境:
  ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
  ENAME HIREDATE 6个月零4天后
  KING 17-JUN-87 MONDAY,   the TWENTY-FIRST of DECEMBER , 1987

  select upper(ename) "ENAME",to_char(hiredate,'dd-MON-yy') "HIREDATE",
         to_char(add_months(hiredate,6)+4,'" the " DDSPTH  " of  "  MONTH ","  YYYY')   "6个月零4天后"
  from emp

9. 做一个查询，按下面的形式显示结果
Employees_and_their_salarys
King************************
Zhang*******************
Wang*****************
………………………….

其中每一个*代表一千元(四舍五入)。按工资从多到少排序

select rpad(initcap(ename),length(ename)+sal/1000,'*') from emp

10. 使用decode函数，按照下面的条件：(根据job类别来确定级别GRADE)
job       grade
AD_PRES     A
ST_MAN     B
IT_PROG     C
SA_REP      D
ST_CLERK        E
…….      OTHER
产生下面的结果:
ENAME Job_id Grade
king AD_PRES A
kate IT_PROG C

  select lower(ename) "ENAME", job "Job_id",decode(job,
                    'CLERK','A',
                    'SALESMAN','B',
                    'MANAGER','C',
                    'ANALYST','D',
                    'PRESIDENT','E',
                    'OTHER')  "Grade"
  from emp                

11. 将上一题的查询用case函数再写一遍。
  select lower(ename) "ENAME", job "Job_id",
                               case job
                               when 'CLERK' then 'A'
                               when 'SALESMAN' then 'B'
                               when 'MANAGER' then 'C'
                               when 'ANALYST' then 'D'
                               when 'PRESIDENT' then 'E'
                               else  'OTHER'
                               end  "Grade"
  from emp

***********************************************************************************************

测 试
1. 组函数处理多行返回一行（yes）判断题

2. 组函数不计算空值（no）判断题
   
3. where子句在分组之前对检索进行过滤（yes）判断题

4. 查询公司员工工资的最大值，最小值，平均值，总和
     select max(sal),min(sal),avg(sal),sum(sal) from emp;

5. 查询各job的员工工资的最大值，最小值，平均值，总和
    select job,max(sal),min(sal),avg(sal),sum(sal) from emp group by job

6. 选择具有各个job 的员工人数
    select job,count(*) from emp group by job

7. 查询员工最高工资和最低工资的差距（DIFFERENCE）
    select max(sal)-min(sal) "DIFFERENCE" from emp

8. 查询公司的人数，以及在80,81,82,87年，每年雇用的人数，结果类似下面的格式
    total 1980 1981 1982 1987
    14 1 10 1 2
   
    select count(*) "total",
           sum(decode(to_char(hiredate,'yyyy'),1980,1,0)) "1980",
           sum(decode(to_char(hiredate,'yyyy'),1981,1,0)) "1981",
           sum(decode(to_char(hiredate,'yyyy'),1982,1,0)) "1982",
           sum(decode(to_char(hiredate,'yyyy'),1987,1,0)) "1987"
    from emp 
      
   -------------------------------------------------------------------------------
   
    select count(*) from emp where to_char(hiredate,'yyyy')='1980'
   
    select count(*) from emp where to_char(hiredate,'yyyy')='1981'
  
    select hiredate,decode(to_char(hiredate,'yyyy'),1980,1,0) from emp
    select  sum(decode(to_char(hiredate,'yyyy'),1981,1,0)) from emp
    select hiredate,decode(to_char(hiredate,'yyyy'),1982,1,0) from emp
    select hiredate,decode(to_char(hiredate,'yyyy'),1987,1,0) from emp