设为首页收藏本站

Botang唐波's Oracle Station

查看: 1217|回复: 0

课程第11次续

[复制链接]

745

主题

1118

帖子

8120

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8120
发表于 2016-4-21 09:21:57 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2016-4-21 09:42 编辑

2016-04-20.txt续:

2016-04-20.sql:
  1. select
  2. ( to_date('1995-09-01','YYYY-MM-DD') -
  3.   to_date('1994-01-11','YYYY-MM-DD'))/30
  4.    from dual;
  5.    
  6.    
  7. select
  8. ( to_date('1995-09-01','YYYY-MM-DD') -
  9.   to_date('1994-01-11','YYYY-MM-DD'))/31
  10.    from dual;
  11.    
  12.    
  13.    select  months_between(to_date('1995-09-01','YYYY-MM-DD'),
  14.    to_date('1994-01-11','YYYY-MM-DD'))
  15.    from dual;
  16.    
  17.    select  add_months(to_date('1995-09-01','YYYY-MM-DD'), 6)
  18.     from dual;
  19.    
  20.     select  next_day(sysdate,4) from dual;
  21.    
  22.      SELECT to_char(ROUND((salary/7), 2),'999,999,999.99'
  23.      
  24.       from employees;
  25.    
  26.    
  27.    SELECT         TO_CHAR(ROUND((salary/7), 2),'99G999D99',
  28.    'NLS_NUMERIC_CHARACTERS = ''.,'' '
  29.            )
  30.         "Formatted Salary"
  31. FROM employees;
  32. -----


  33. SELECT last_name, job_id, salary,
  34.        CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
  35.                    WHEN 'ST_CLERK' THEN  1.15*salary
  36.                    WHEN 'SA_REP'   THEN  1.20*salary
  37.        ELSE      salary END     "REVISED_SALARY"
  38. FROM   employees;


  39. SELECT last_name, job_id, salary,
  40.         decode (job_id, 'IT_PROG' , 1.10*salary,
  41.                     'ST_CLERK' ,1.15*salary,
  42.                     'SA_REP'  ,1.20*salary,salary)
  43.          "REVISED_SALARY"
  44. FROM   employees;



  45. SELECT last_name, job_id, salary,
  46.        CASE WHEN  salary<=3000  THEN  .10*salary
  47.                    WHEN salary>3001 and salary<=10000 THEN  .15*salary
  48.                    WHEN salary>10001   THEN  .20*salary
  49.        ELSE      salary END     "TAX"
  50. FROM   employees;


  51. select  last_name ,salary  from employees where job_id='IT_PROG';


  52. SELECT last_name, job_id, salary,
  53.        CASE  when last_name like 'P%' then 2*salary         
  54.               WHEN job_id= 'IT_PROG'  THEN  1.10*salary                  
  55.                    WHEN job_id='ST_CLERK' THEN  1.15*salary
  56.                    WHEN job_id='SA_REP'   THEN  1.20*salary
  57.        ELSE      salary END     "REVISED_SALARY"
  58. FROM   employees;

  59. ----
  60. select  * from employees;

  61. ---

  62. select  count(*) Y2002   from employees where to_char(hire_date,'YYYY')=2002
  63. union all
  64. select  count(*)   Y2003  from employees where to_char(hire_date,'YYYY')=2003
  65. union all
  66. select  count(*)    Y2004 from employees where to_char(hire_date,'YYYY')=2004;

  67. ---


  68. select  sum(decode(   to_char(hire_date,'YYYY') , 2002,1,0))    Y2002,
  69.              sum(decode(   to_char(hire_date,'YYYY') , 2003,1,0))      Y2003,
  70.              sum(decode(   to_char(hire_date,'YYYY') , 2004,1,0))       Y2004
  71.            from  employees;



  72. ----

  73. SELECT   department_id, manager_id,  AVG(salary)
  74. FROM     employees
  75. GROUP BY department_id,manager_id
  76. ;

  77. SELECT   department_id, manager_id,  AVG(salary)
  78. FROM     employees
  79. GROUP BY rollup(department_id,manager_id)
  80. ;

  81. -----

  82. SELECT   department_id, AVG(salary)
  83. FROM     employees
  84. where department_id>20
  85. GROUP BY department_id
  86. having  AVG(salary) > 8000;

  87. --ERROR
  88. SELECT   department_id  d , AVG(salary)
  89. FROM     employees d
  90. GROUP BY  department_id
  91. having  AVG(salary) > 8000  and department_id>20;

  92. ---
  93. ---





复制代码


2016-04-20.txt.zip

3.9 KB, 下载次数: 996

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-12-13 12:30 , Processed in 0.114293 second(s), 35 queries .

快速回复 返回顶部 返回列表