课程第11次续

botang · 发表于 2016-4-21 09:21:57

本帖最后由 botang 于 2016-4-21 09:42 编辑

2016-04-20.txt续：

2016-04-20.sql：

select
( to_date('1995-09-01','YYYY-MM-DD') -
to_date('1994-01-11','YYYY-MM-DD'))/30
from dual;
select
( to_date('1995-09-01','YYYY-MM-DD') -
to_date('1994-01-11','YYYY-MM-DD'))/31
from dual;
select months_between(to_date('1995-09-01','YYYY-MM-DD'),
to_date('1994-01-11','YYYY-MM-DD'))
from dual;
select add_months(to_date('1995-09-01','YYYY-MM-DD'), 6)
from dual;
select next_day(sysdate,4) from dual;
SELECT to_char(ROUND((salary/7), 2),'999,999,999.99'
from employees;
SELECT TO_CHAR(ROUND((salary/7), 2),'99G999D99',
'NLS_NUMERIC_CHARACTERS = ''.,'' '
)
"Formatted Salary"
FROM employees;
-----
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
SELECT last_name, job_id, salary,
decode (job_id, 'IT_PROG' , 1.10*salary,
'ST_CLERK' ,1.15*salary,
'SA_REP' ,1.20*salary,salary)
"REVISED_SALARY"
FROM employees;
SELECT last_name, job_id, salary,
CASE WHEN salary<=3000 THEN .10*salary
WHEN salary>3001 and salary<=10000 THEN .15*salary
WHEN salary>10001 THEN .20*salary
ELSE salary END "TAX"
FROM employees;
select last_name ,salary from employees where job_id='IT_PROG';
SELECT last_name, job_id, salary,
CASE when last_name like 'P%' then 2*salary
WHEN job_id= 'IT_PROG' THEN 1.10*salary
WHEN job_id='ST_CLERK' THEN 1.15*salary
WHEN job_id='SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
----
select * from employees;
---
select count(*) Y2002 from employees where to_char(hire_date,'YYYY')=2002
union all
select count(*) Y2003 from employees where to_char(hire_date,'YYYY')=2003
union all
select count(*) Y2004 from employees where to_char(hire_date,'YYYY')=2004;
---
select sum(decode( to_char(hire_date,'YYYY') , 2002,1,0)) Y2002,
sum(decode( to_char(hire_date,'YYYY') , 2003,1,0)) Y2003,
sum(decode( to_char(hire_date,'YYYY') , 2004,1,0)) Y2004
from employees;
----
SELECT department_id, manager_id, AVG(salary)
FROM employees
GROUP BY department_id,manager_id
;
SELECT department_id, manager_id, AVG(salary)
FROM employees
GROUP BY rollup(department_id,manager_id)
;
-----
SELECT department_id, AVG(salary)
FROM employees
where department_id>20
GROUP BY department_id
having AVG(salary) > 8000;
--ERROR
SELECT department_id d , AVG(salary)
FROM employees d
GROUP BY department_id
having AVG(salary) > 8000 and department_id>20;
---
---

复制代码