SQL> SELECT EXTRACT ( YEAR FROM SYSDATE) FROM DUAL;
EXTRACT(YEARFROMSYSDATE)
------------------------
2010
SQL> select hire_date,
2 hire_date + to_yminterval('01-02') as hire_date_yminterval
3 from employees
4 where department_id =20;
HIRE_DAT HIRE_DAT
-------- --------
96/02/17 97/04/17
97/08/17 98/10/17
SQL> select employee_id, manager_id, department_id
2 from employees
3 where (manager_id, department_id) in
4 ( select manager_id, department_id
5 from employees
6 where employee_id in (199,174))
7 ;
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
198 124 50
199 124 50
141 124 50
142 124 50
143 124 50
144 124 50
196 124 50
197 124 50
174 149 80
175 149 80
176 149 80
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
177 149 80
179 149 80
13 개의 행이 선택되었습니다.
SQL> select employee_id, last_name,
2 ( case
3 when department_id =
4 ( select department_id
5 from departments
6 where location_id = 1800 )
7 then 'Canada' else 'usa' end ) location
9 from employees;
EMPLOYEE_ID LAST_NAME LOCATIO
----------- ------------------------- -------
198 OConnell usa
199 Grant usa
200 Whalen usa
201 Hartstein Canada
202 Fay Canada
...
자기부서의 평균보다 많이 받는 직원들의 집합
SQL> select last_name, salary, department_id
2 from employees outer
3 where salary > ( select avg(salary)
4 from employees
5 where department_id = outer.department_id);
LAST_NAME SALARY DEPARTMENT_ID
------------------------- ---------- -------------
Hartstein 13000 20
Higgins 12000 110
King 24000 90
Hunold 9000 60
Ernst 6000 60
Greenberg 12000 100
Faviet 9000 100
...
직업을 두번 이상 바꾼 직원들의 집합
SQL> select e.employee_id, last_name, e.job_id
2 from employees e
3 where 2 <= (select count(*)
4 from job_history
5 where employee_id = e.employee_id);
EMPLOYEE_ID LAST_NAME JOB_ID
----------- ------------------------- ----------
200 Whalen AD_ASST
101 Kochhar AD_VP
176 Taylor SA_REP
SQL> select employee_id, last_name, job_id, manager_id
2 from employees
3 start with employee_id = 101
4 connect by prior manager_id = employee_id;
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 100
100 King AD_PRES
무작정 컬럼 많이 만들기.
SQL> SELECT LEVEL FROM DUAL
2 CONNECT BY LEVEL <= 10000;
LEVEL 사용예
SQL> SELECT LPAD(LAST_NAME,LENGTH(LAST_NAME)+(LEVEL*2) -2,'-')
2 AS ORG_CHART
3 FROM EMPLOYEES
4 START WITH LAST_NAME='King'
5 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;
가지치기
SQL> SELECT COUNT(*) FROM EMPLOYEES
2 START WITH MANAGER_ID IS NULL
3 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
4 AND LAST_NAME != 'Higgins'
5 ;
SQL 스크립트
SQL> select 'create table '||table_name||'_test as select * from '||table_name||' where 1 = 2;' as "Create Table Script" from user_tables;
format
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET PAGESIZE 0
SQL> SPOOL DROPEM.SQL
--SQL Query
SQL> spool off
SQL> set feedback on
SQL> set pagesize 24
SQL> set echo on