HighLight in your life~

Oracle Database 10g SQL Fundamentals I - Practice 8

1.
CREATE TABLE MY_EMPLOYEE
(ID NUMBER(4)
 , LAST_NAME VARCHAR2(25)
 , FIRST_NAME VARCHAR2(25)
 , USER_ID VARCHAR2(8)
 , SALARY NUMBER(9, 2)
)
;

2.
DESC MY_EMPLOYEE

3.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(1, 'Patel', 'Ralph', 'rpatel', 895)
;

4.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(2, 'Dancs', 'Betty', 'bdancs', 860)
;

5.
SELECT * FROM MY_EMPLOYEE;

6.
INSERT INTO MY_EMPLOYEE
VALUES(&P_ID
, '&&P_LAST_NAME'
, '&&P_FIRST_NAME'
, LOWER(SUBSTR('&P_FIRST_NAME', 1, 1) ||
SUBSTR('&P_LAST_NAME', 1, 7)), &P_SALARY);

7.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(3, 'Biri', 'Ben', 'bbiri', 1100)
;

INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(4, 'Newman', 'Chad', 'cnewman', 750)
;

8.
SELECT * FROM MY_EMPLOYEE;

9.
COMMIT

10.
UPDATE MY_EMPLOYEE
SET LAST_NAME = 'Drexler'
WHERE ID = 3;

11.
UPDATE MY_EMPLOYEE
SET SALARY = 1000
WHERE SALARY < 900;

12.
DELETE FROM MY_EMPLOYEE WHERE LAST_NAME='Dancs';

15. COMMIT

17.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(5, 'Ropeburn', 'Audrey', 'aropebur', 1550)
;

18~23
SAVEPOINT STEP_18;
DELETE FROM MY_EMPLOYEE;
SELECT * FROM MY_EMPLOYEE;
ROLLBACK TO STEP_18;
SELECT * FROM EMPLOYEE;
COMMIT;

Oracle Database 10g SQL Fundamentals I - Practice 10

1.
CREATE OR REPLACE VIEW EMPLOYEES_VU
AS SELECT EMPLOYEE_ID, LAST_NAME AS EMPLOYEE, DEPARTMENT_ID
FROM EMPLOYEES;

2.
SELECT * FROM EMPLOYEES_VU;

3.
SELECT EMPLOYEE, DEPARTMENT_ID FROM EMPLOYEES_VU;

4.
CREATE OR REPLACE VIEW DEPT50
AS
SELECT EMPLOYEE_ID AS EMPNO, LAST_NAME AS EMPLOYEE, DEPARTMENT_ID AS DEPTNO
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;
-- WITH CHECK OPTION CONSTRAINT EMP_DEPT_50;

5.
SELECT * FROM DEPT50;

6.
UPDATE DEPT50
SET DEPTNO = 80
WHERE EMPNO = 143
;

7.
CREATE SEQUENCE DEPT_ID_SEQ
START WITH 200
INCREMENT BY 10
MAXVALUE 1000
;

8. ???

9.
CREATE INDEX DEPT_DNAME_IDX ON DEPT(DNAME);

10.
CREATE SYNONYM EMP01 FOR EMPLOYEES;

1.
CREATE TABLE DEPT_TEST(
ID NUMBER(7),
NAME VARCHAR2(25),
CONSTRAINT DEPT_TEST_ID_PK PRIMARY KEY (ID)
)
;

2.
INSER INTO DEPT_TEST
 SELECT DEPARTMENT_ID, DEPARTMENT_NAME
 FROM DEPARTMENTS
;

3.
CREATE TABLE EMP_TEST(
ID NUMBER(7),
LAST_NAME VARCHAR2(25),
FIRST_NAME VARCHAR2(25),
DEPT_ID NUMBER(7),
CONSTRAINT EMP_DEPT_TEST_FK FOREIGN KEY(DEPT_ID) REFERENCES DEPT_TEST(ID)
)
;

4.
CREATE TABLE EMP_TEST2
AS SELECT EMPLOYEE_ID ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID DEPT_ID
FROM EMPLOYEES WHERE 1 = 2;

5.
DROP TABLE EMP_TEST2;

Set Operators

- UNION ALL : 합집합 (중복 허용)
- UNION : 합집합 (중복 제거)
- INTERSECT : 교집합 (중복 제거)
- MINUS : 차집합 (중복 제거)

A: 1 1 1 2 2 3

B: 2 3 3 4 4

A UNION ALL B: 1 1 1 2 2 3 2 3 3 4 4

A UNION B: 1 2 3 4

A INTERSECT B: 2 3

A MINUS B: 1

Oracle Database 10g SQL Fundamentals I - Practice 7

ar(11월9일 4차시)

Set Operators

  - UNION ALL : 합집합 (중복 허용)
  - UNION     : 합집합 (중복 제거)
  - INTERSECT : 교집합 (중복 제거)
  - MINUS     : 차집합 (중복 제거)

1.
SELECT DEPARTMENT_ID
FROM   DEPARTMENTS
MINUS
SELECT DEPARTMENT_ID
FROM   EMPLOYEES
WHERE  JOB_ID = 'ST_CLERK';

2.
SELECT COUNTRY_ID,COUNTRY_NAME
FROM   COUNTRIES
MINUS
SELECT C.COUNTRY_ID,C.COUNTRY_NAME
FROM COUNTRIES C, LOCATIONS L, DEPARTMENTS D
WHERE C.COUNTRY_ID = L.COUNTRY_ID
AND L.LOCATION_ID = D.LOCATION_ID
;

3.
COLUMN DUMMY NOPRINT
SELECT JOB_ID, DEPARTMENT_ID, 'X' DUMMY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10
UNION
SELECT JOB_ID, DEPARTMENT_ID, 'Y' DUMMY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
UNION
SELECT JOB_ID, DEPARTMENT_ID, 'Y' DUMMY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
ORDER BY DUMMY
;
COLUMN DUMMY PRINT

4.
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
INTERSECT
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY
;

5.
1)
SELECT LAST_NAME, DEPARTMENT_ID, TO_CHAR(NULL)
FROM EMPLOYEES
UNION ALL
SELECT TO_CHAR(NULL), DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
;

2)
SELECT LAST_NAME, DEPARTMENT_ID, TO_CHAR(NULL)
FROM EMPLOYEES
UNION
SELECT TO_CHAR(NULL), DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
;

Oracle Database 10g SQL Fundamentals I - Practice 6

1.
SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME IN ('Zlotkey'))
AND LAST_NAME <> 'Zlotkey';

2.
SELECT EMPLOYEE_ID, LAST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES)
ORDER BY SALARY ASC;

3.
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE '%u%');

4.
SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID = 1700);

5.
SELECT LAST_NAME, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID = (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE LAST_NAME = 'King');

6.
SELECT DEPARTMENT_ID, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE
DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = 'Executive')

7.
SELECT EMPLOYEE_ID, LAST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES)
AND DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE '%u%')
ORDER BY SALARY ASC;

Oracle Database 10g SQL Fundamentals I- Les05_v(Displaying Data from Multiple Tables) Practice 5

1.

SELECT L.LOCATION_ID, L.STREET_ADDRESS, L.CITY, L.STATE_PROVINCE, C.COUNTRY_NAME
FROM LOCATIONS L, COUNTRIES C
WHERE L.COUNTRY_ID = C.COUNTRY_ID
ORDER BY L.LOCATION_ID
;

2.
SELECT E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
;

3.

SELECT E.LAST_NAME, E.JOB_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND L.CITY ='Toronto'
;

4.

SELECT A1.LAST_NAME AS Employee, A1.EMPLOYEE_ID AS EMP#,  B1.LAST_NAME AS Manager, A1.MANAGER_ID AS Mgr#
FROM EMPLOYEES A1, EMPLOYEES B1
WHERE A1.MANAGER_ID = B1.EMPLOYEE_ID
ORDER BY A1.EMPLOYEE_ID
;

5.

SELECT A1.LAST_NAME AS Employee, A1.EMPLOYEE_ID AS EMP#,  B1.LAST_NAME AS Manager, A1.MANAGER_ID AS Mgr#
FROM EMPLOYEES A1, EMPLOYEES B1
WHERE A1.MANAGER_ID = B1.EMPLOYEE_ID(+)
ORDER BY A1.EMPLOYEE_ID

6.

SELECT A1.LAST_NAME, A2.LAST_NAME
FROM EMPLOYEES A1, EMPLOYEES A2
WHERE A1.DEPARTMENT_ID = A2.DEPARTMENT_ID
AND A1.EMPLOYEE_ID <> A2.EMPLOYEE_ID
ORDER BY A1.DEPARTMENT_ID, A1.EMPLOYEE_ID DESC
;

7.

SELECT E.LAST_NAME, E.JOB_ID, D.DEPARTMENT_NAME, E.SALARY, J.GRADE_LEVEL
FROM EMPLOYEES E, DEPARTMENTS D, JOB_GRADES J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL
;

8.

SELECT A1.LAST_NAME, A1.HIRE_DATE
FROM EMPLOYEES A1, EMPLOYEES A2
WHERE A1.HIRE_DATE > A2.HIRE_DATE
AND A2.LAST_NAME = 'Davies'
;

9.

SELECT A1.LAST_NAME, A1.HIRE_DATE
FROM EMPLOYEES A1, EMPLOYEES A2
WHERE A1.MANAGER_ID = A2.EMPLOYEE_ID
AND A1.HIRE_DATE < A2.HIRE_DATE
;

1. 9i 함수 메뉴얼

* http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions2a.htm#80856

2. 10g 함수 메뉴얼
* http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

# 가공 --> 연산 - 산술
                - 연결
                - 논리
                - ...
       --> 함수 - 단일행 함수 : lower ... : 3장
                - 복수행 함수 : avg   ... : 4장
                - 사용자 정의 함수        : PL/SQL

# 3-3 : argument? 아규먼트, 인자, 인수, 매개변수, 파라미터, ...

  col ip format a20
  select  user, sysdate, userenv('sid'), SYS_CONTEXT ('USERENV', 'IP_ADDRESS') as ip          from dual;
  select ename, lower(ename), instr(ename, 'E'), lower(substr(ename, 1, 2)), substr(ename, 3) from emp;

----------------------
 Character
----------------------

# 3-11

 - substr

   select ename, substr(ename, 2, 3), substr(ename, 2), substr(ename, -2, 1) from emp;

   select ename, lpad(ename, 10), ename                               from emp;
   select ename, substr(lpad(ename, 10), 10, 1), substr(ename, -1, 1) from emp;

 - length

   select ename, length(ename) from emp;

   select * from emp where length(ename) = 5;         --> 이름이 5자인 사원
   select * from emp where ename like '_____';        --> 이름이 5자인 사원

 - instr

   select ename, instr(ename, 'A') from emp;

   select * from emp where instr(ename, 'A') > 0;     --> 이름에 A가 포함된 사원
   select * from emp where ename like '%A%';          --> 이름에 A가 포함된 사원

 - rpad, lpad

   col stars format a60

   select empno, ename, sal, sal from emp;
   select empno, ename, sal, round(sal/100) from emp;
   select empno, ename, sal, '*', round(sal/100) from emp;
   select empno, ename, sal, rpad('*', round(sal/100)) as stars from emp;
   select empno, ename, sal, rpad('*', round(sal/100), '*') as stars from emp;

   col ename2 justify r
   col ename2 format a10
   select empno, ename, lpad(ename, 10) as ename2, sal from emp;

 - ltrim, rtrim vs trim

   select ltrim('SOS', 'S')            from dual;
   select trim(leading 'S' from 'SOS') from dual;

   select rtrim('SOS', 'S')             from dual;
   select trim(trailing 'S' from 'SOS') from dual;

   select rtrim(ltrim('SOS', 'S'), 'S') from dual;
   select trim(both 'S' from 'SOS')     from dual;

         --------

   select ltrim('ABBACAB', 'AB')            from dual;    --> http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions68a.htm#77877
   select trim(leading 'AB' from 'ABBACAB') from dual;    --> ORA-30001: trim set should have only one character

 - reverse

   select ename, reverse(ename) from emp;

문제. 숫자로만 이루어진 행을 찾으세요.

  drop table t1 purge;
  create table t1 (col1 varchar2(30));
  insert into t1 values ('23FA2');
  insert into t1 values ('564766');
  insert into t1 values ('40953');
  insert into t1 values ('P3312');
  insert into t1 values ('48756');
  insert into t1 values ('8876M');
  insert into t1 values ('...');

  commit;

  grant select on t1 to public;

  select * from t1;

  해답 1.

    select upper(col1), lower(col1) from t1;
    select upper(col1), lower(col1) from t1 where upper(col1) = lower(col1);

  해답 2.

    select col1, ltrim(col1, 0123456789)   from t1; 
    select col1, ltrim(col1, 1234567890)   from t1;

    select col1, ltrim(col1, '0123456789') from t1;
    select col1, ltrim(col1, '0123456789') from t1 where ltrim(col1, '0123456789') is null;

문제. 연속된 숫자 5개가 포함된 행 찾기

  drop table t2 purge;
  create table t2 (col1 varchar2(40));

  insert into t2 values('이것은 12321이다');
  insert into t2 values('중요한 것은 12가 123보다 크지 않다는 것이다');
  insert into t2 values('3 곱하기 2는 6이다. 6 나누기 2는 3이다.');
  insert into t2 values('전체의 합은 76871이다');
  ...

  commit;

  grant select on t2 to public;

  select * from t2;

  해답.

    select col1, translate(col1, '0123456789', '----------') from t2;
    select col1 from t2 where translate(col1, '0123456789', '----------') like '%-----%';

    cf.http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions150a.htm#79574

----------------------
 Number
----------------------

# 3-13

  select ceil(2.4), floor(2.4) 
  from dual;

  select round(45.995, 2),     -- 46 or 46.00
         round(45.995, -2),    -- 0
         round(55.995, -2),   
         round(78345.995, -2)  
  from dual;

  select to_char(round(45.995, 2), '999,999.99') as ret
  from dual;

  select dbms_random.value(1000, 2000) as ret from dual connect by level <= 100;
  select round(dbms_random.value(1000, 2000), -2) as ret from dual connect by level <= 100;

  select ret, count(*)
  from (select round(dbms_random.value(1000, 2000), -2) as ret from dual connect by level <= 100)
  group by ret
  order by ret;

  select trunc(45.995, 2),
         trunc(45.995, -2),
         trunc(55.995, -2),
         trunc(78345.995, -2)
  from dual;

  select empno,
         mod(empno, 2),
         mod(empno, 3),
         mod(empno, 5)
  from emp;

  select * from emp where mod(empno, 2) = 0;
  select * from emp where mod(empno, 3) = 0;
  select * from emp where mod(empno, 5) = 0;

# 3-14 : Dual

  @desc dual
  select * from dual;

  col dummy format a10
  select dummy from sys.dual;

  select empno, ename, 37*1.5+23545 from emp;
  select 37*1.5+23545 from emp;
  select 37*1.5+23545 from dept;

  select 37*1.5+23545 from dual;

----------------------
 Date
----------------------

# 3-17

  alter session set nls_date_format = 'DD-MON-RR';
  select sysdate from dual;
  select * from emp;
 
  alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
  select sysdate from dual;
  select * from emp;

# 3-20

  select sysdate, sysdate + 1            from dual;
  select sysdate, sysdate - 3            from dual;
  select sysdate, sysdate + 5/24         from dual;
  select sysdate, sysdate + 5/(24*60)    from dual;
  select sysdate, sysdate + 5/(24*60*60) from dual;

  alter session set nls_date_format = 'DD-MON-RR';
  select empno, ename, sysdate, hiredate, ceil((sysdate - hiredate)/7) as weeks from emp;

  select sysdate, sysdate + 100, sysdate + 1000, sysdate + 10000 from dual;

  select sysdate - to_date('01-JAN-93') from dual;

  select sysdate * 3 from dual;              --> 에러 : ORA-00932: inconsistent datatypes: expected NUMBER got DATE
  select sysdate + (sysdate + 1) from dual;  --> 에러 : ORA-00975: date + date not allowed

# 3-22

  select empno, ename, sysdate, hiredate,
         months_between(sysdate, hiredate) as Month_Between1,
         ceil(months_between(sysdate, hiredate)) as Month_Between2,
         add_months(hiredate, 5)
  from emp;

  select next_day(sysdate, 'FRI') from dual;
  select next_day(sysdate, 'Mon') from dual;

  select last_day('13-FEB-11') from dual; 
  select last_day('17-FEB-12') from dual; 

# 3-24

  Day   :  1 ~ 15 : 탈락
          16 ~ 31 : 반올림

  Month :  1 ~  6 : 탈락
           7 ~ 12 : 반올림

  select empno, ename, hiredate, trunc(hiredate, 'month'), trunc(hiredate, 'year') from emp order by hiredate;
 
  select empno, ename, hiredate, trunc(hiredate, 'month'), trunc(hiredate, 'year')   --> 81년 2월에 입사한 사원
  from emp
  where trunc(hiredate, 'month') = '01-FEB-81';

  select empno, ename, hiredate, trunc(hiredate, 'month'), trunc(hiredate, 'year')   --> 82년에 입사한 사원
  from emp
  where trunc(hiredate, 'year') = '01-JAN-82';

  --> Hashing?

(9) Access Predicate와 Filter Predicate
 - 10gR2부터 Autotrace 명령을 통해서 Predicate 정보를 출력해 볼 수 있다.
 - 10gR2 이전 버전에서는 explain plan 명령을 통해 실행계획을 수집하고서 dbms_xplan 패키지(?

@/rdbms/admin/utlxpls.sql 참조)를 통해 실행계획을 출력해 볼 수 있다. 이는 오라클 9i부터 plan_table에
 추가된 access_predicate와 filter_predicate 컬럼으로부터 가져온 값이다.

create index emp_x01 on emp(deptno, job, sal, ename, mgr, comm);

set autotrace traceonly explain

select /*+ ordered use_nl(e) index(e emp_x01) */ *
from dept d, emp e
where d.loc = 'CHICAGO'
and e.deptno = d.deptno
and e.job like 'SALE%'
and e.job between 'A' and 'Z'
and e.sal >= 1000
and e.ename like '%A%'
and trim(e.ename) = 'ALLEN'
and e.comm >= 300
and to_char(e.hiredate, 'yyyymmdd') like '198102%'
;