오라클 DB에서 학습하기 위해 연습 계정인 scott / tiger 을 만들어 주어야 합니다.

 

① 오라클 설치 후 시작 메뉴 오라클 파일에 sqlplus.exe 를 실행해 줍니다.

 

② 설치할 때 만든 아이디와 비밀번호를 입력합니다.

(아이디는 대부분 system이고 비밀번호는 설치시 입력한 값입니다.)

 

③ scott 계정 푸는 방법

ALTER USER scott

IDENTIFIED BY tiger

ACCOUNT UNLOCK;

 

- 연결한 계정 바꾸는 방법

CONN scott/tiger;

 

※ 만약 계정이 존재하지 않는다면 아래와 같이 scott/tiger을 생성해 줘야 합니다. 

 

① 위의 ②와 같이 접속하여 scott/tiger 계정을 생성해 줍니다.

CREATE USER scott IDENTIFIED BY tiger

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp;

 

② 권한을 부여합니다.

GRANT connect, resource TO scott;

 

③ 생성된 계정으로 접속해 줍니다.

CONN scott/tiger;

 

첨부파일 : 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 실습 예제 Query 

 

아래는 실습 명령어들 모음입니다.

 

SELECT * FROM TAB;

 

DESC emp;

 

DESC dept;

 

SELECT * FROM emp;

 

SELECT empno, ename

FROM emp;

 

SELECT * FROM professor;

 

SELECT name, '교수님~배고파요~' 

FROM professor;

 

SELECT studno "학번", name AS "이름", profno 지도교수번호

FROM student;

 

SELECT empno 사원번호, ename 사원명, job 직업

FROM emp;

 

SELECT deptno 부서#, dname 부서명, loc 위치

FROM dept;

 

SELECT name || '님은' || position || '입니다'

FROM professor;

 

SELECT * FROM student;

 

SELECT name || '의 키는' || height || 'cm, 몸무게는' || weight || 'kg 입니다'

FROM student;

 

SELECT * FROM professor;

 

SELECT name || '(' || position || '), ' || name || '''' || position || ''''

FROM professor;

 

SELECT ename, sal, sal+100

FROM emp;

 

SELECT sal, sal+100/2, (sal+100)/2

FROM emp;

 

SELECT ename, sal, deptno

FROM emp

WHERE deptno = 10;

 

SELECT ename, sal

FROM emp

WHERE sal > 4000;

 

SELECT ename, empno, sal

FROM emp

WHERE ename = 'SCOTT';

 

SELECT * FROM professor;

 

SELECT name, pay, hiredate

FROM professor

WHERE hiredate = '2001-05-23';

 

SELECT name, height

FROM student

WHERE height >= 180;

 

SELECT name, weight

FROM student

WHERE weight BETWEEN 60 AND 80;

 

SELECT name, weight

FROM student

WHERE weight >= 60

AND weight <= 80;

 

SELECT name, deptno1

FROM student

WHERE deptno1 IN (101, 201);

 

SELECT name

FROM student

WHERE name LIKE '김%';

 

SELECT name, bonus

FROM professor;

 

SELECT name, bonus

FROM professor

WHERE bonus IS NULL;

 

SELECT name bonus

FROM professor

WHERE bonus IS NOT NULL;

 

SELECT name, grade, height

FROM student

WHERE grade = 4

AND height > 170;

 

SELECT name, grade, height, weight

FROM student

WHERE grade = 1

OR weight > 80;

 

SELECT name, grade, height, weight

FROM student

WHERE grade = 2

AND height > 180

AND weight > 70;

 

SELECT name, grade, height, weight

FROM student

WHERE grade = 2

AND (height > 180 OR weight > 70);

 

SELECT name

FROM professor

WHERE name >= '자'

AND name < '차'

ORDER BY 1;

 

SELECT name, height

FROM student

WHERE grade = 1;

 

SELECT name, height

FROM student

WHERE grade = 1

ORDER BY height;

 

SELECT name, height, weight

FROM student

WHERE grade = 1

ORDER BY height ASC, weight DESC;

 

SELECT name, birthday, height, weight

FROM student

WHERE grade = 1

ORDER BY 2;

 

SELECT name "이름", height "키"

FROM student

WHERE grade = 1

ORDER BY 이름;

 

SELECT * FROM professor;

 

SELECT INITCAP(ID)

FROM student

WHERE deptno1 = 201;

 

SELECT INITCAP('Pretty girl')

FROM DUAL;

 

SELECT name 이름, id, LOWER(id) 소문자, UPPER(id) 대문자

FROM student

WHERE deptno1 = 201;

 

SELECT name 이름, id, LENGTH(id) 글자수

FROM student

WHERE LENGTH(id) > 9;

 

SELECT name 이름, LENGTH(name), LENGTHB(name)

FROM student

WHERE deptno1 = 201;

 

SELECT CONCAT(name, position)

FROM professor

WHERE deptno = 101;

 

SELECT SUBSTR('ABCDE', 2, 3)

FROM dual;

 

SELECT SUBSTR('ABCDE', -2, 3)

FROM dual;

 

SELECT name, SUBSTR(jumin, 1, 6) "생년월일"

FROM student

WHERE deptno1 = 101;

 

SELECT name, SUBSTR(jumin, 1, 6) "생년월일"

FROM student

WHERE SUBSTR(jumin, 3, 2) = '08';

 

SELECT SUBSTR(name, 1, 2)

FROM student

WHERE deptno1 = 101;

 

SELECT SUBSTRB(name, 1, 2)

FROM student

WHERE deptno1 = 101;

 

SELECT INSTR('A*B*C*', '*', 1, 2)

FROM dual;

 

SELECT name, tel, SUBSTR( tel, 1,INSTR(TEL, ')', 1, 1) - 1 ) AS 지역번호

FROM student

WHERE deptno1 = 101;

 

SELECT id, LPAD(id, 10, '$') "LPAD"

FROM student

WHERE deptno1 = 101;

 

SELECT LPAD(dname, 10, '1234') "LPAD 연습"

FROM dept2;

 

SELECT RPAD(dname, 10, SUBSTR('1234567890 ', LENGTHB(dname)+1, 10)) "LPAD 연습"

FROM dept2;

 

SELECT LTRIM(dname, '영')

FROM dept2;

 

SELECT REPLACE(name, substr(name, 1, 1), '#')

FROM student

WHERE deptno1 = 101;

 

SELECT REPLACE(name, substr(name, 2, 1), '#')

FROM student

WHERE deptno1 = 101;

 

SELECT REPLACE(jumin, substr(jumin, 7, 7), '*******')

FROM student

WHERE deptno1 = 101;

 

SELECT name, tel, REPLACE(tel, substr(tel, INSTR(TEL, ')', 1, 1)+1, 3), '###') AS REPLACE

FROM student

WHERE deptno1 = 102;

 

SELECT TRUNC(12.345) TRUNC1, TRUNC(12.345, 2) TRUNC2, TRUNC(12.345, -1) TRUNC3

FROM dual;

 

SELECT MOD(12, 10) "Mod", CEIL(12.345) "Ceil", FLOOR(12.345) "Flor"

FROM dual;

 

SELECT POWER(3, 2)

FROM dual;

 

SELECT SYSDATE

FROM dual;

 

SELECT MONTHS_BETWEEN('2012-03-01', '2012-01-01')

FROM dual;

 

SELECT name, SYSDATE, hiredate,

        ROUND(MONTHS_BETWEEN(SYSDATE, hiredate), 2) Date_1,

        ROUND(((SYSDATE - hiredate) / 31), 2) Date_2

FROM professor

WHERE deptno = 101;

 

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3)

FROM dual;

 

SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일')

FROM dual;

 

SELECT SYSDATE, ROUND(SYSDATE), TRUNC(SYSDATE)

FROM dual;

 

SELECT 1 + '1' FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'YYYY') "YYYY",

        TO_CHAR(SYSDATE, 'RRRR') "RRRR",

        TO_CHAR(SYSDATE, 'YY') "YY",

        TO_CHAR(SYSDATE, 'YEAR') "YEAR"

FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'MM') "MM",

        TO_CHAR(SYSDATE, 'MON') "MON",

        TO_CHAR(SYSDATE, 'MONTH') "MONTH"

FROM dual;

 

SELECT name, TO_CHAR((pay * 12) + bonus, '99,999') "연봉"

FROM professor

WHERE deptno = 101;

 

SELECT TO_NUMBER('1')

FROM dual;

 

SELECT name, TO_DATE(hiredate, 'YYYY-MM-DD') "입사일", TO_CHAR((pay * 12), '99,999') "연봉", To_CHAR((pay*12)*1.1, '99,999') "인상후"

FROM professor

WHERE TO_CHAR(hiredate, 'YYYY') < 1990;

 

SELECT name, pay, bonus, (pay * 12 + NVL(bonus, 0 )) "연봉"

FROM professor

WHERE deptno = 101;

 

SELECT name, deptno, DECODE(deptno, 101, '컴퓨터공학과') "학과명"

FROM professor;

 

SELECT name, deptno, DECODE(deptno, 101, '컴퓨터공학과', '기타학과') "학과명"

FROM professor;

 

SELECT name, deptno, DECODE(deptno, 101, '컴퓨터공학과',

                                    102, '멀티미디어공학과',

                                    103, '소프트웨어공학과',

                                         '기타학과') "학과명"

FROM professor;

 

SELECT name, jumin, DECODE(substr(jumin, 7, 1), 1, '남자', '여자')

FROM student

WHERE deptno1 = 101;

 

SELECT name, tel, CASE(substr(tel, 1, INSTR(tel, ')') -1)) WHEN '02' THEN '서울'

                                                           WHEN '031' THEN '경기'

                                                           WHEN '031' THEN '부산'

                                                           WHEN '031' THEN '울산'

                                                           WHEN '031' THEN '경남'

                                                           ELSE '기타'

                                                           END "지역명"

FROM student;       

 

SELECT deptno, position, AVG(NVL(pay, 0)) "평균급여"

FROM professor

GROUP BY deptno, position;

 

SELECT deptno, position, AVG(NVL(pay, 0)) "평균급여"

FROM professor

GROUP BY deptno, position

ORDER BY deptno;

 

SELECT deptno, AVG(NVL(pay, 0))

FROM professor

GROUP BY deptno

HAVING AVG(pay) > 450;

 

SELECT deptno, position, COUNT(*), SUM(pay)

FROM professor

GROUP BY ROLLUP(deptno, position);

 

SELECT *

FROM (SELECT week "주", day, num_day

      FROM cal)

PIVOT (MAX(num_day)

       FOR day IN('일' as "일",

                  '월' as "월",

                  '화' as "화",

                  '수' as "수",

                  '목' as "목",

                  '금' as "금",

                  '토' as "토")

      )

ORDER BY "주";

 

SELECT MAX(DECODE(day, '일', num_day)) AS SUN,

       MAX(DECODE(day, '월', num_day)) AS MON,

       MAX(DECODE(day, '화', num_day)) AS TUE,

       MAX(DECODE(day, '수', num_day)) AS WED,

       MAX(DECODE(day, '목', num_day)) AS THU,

       MAX(DECODE(day, '금', num_day)) AS FRI,

       MAX(DECODE(day, '토', num_day)) AS SAT

FROM cal

GROUP BY week

ORDER BY week;

 

SELECT *

FROM (SELECT deptno, job, empno, sal

      FROM emp)

PIVOT

( COUNT(empno)

  FOR job IN('CLERK' as "CLERK",

             'MANAGER' as "MANAGER",

             'PRESIDENT' as "PRESIDENT",

             'ANALYST' as "ANALYST",

             'SALESMAN' as "SALESMAN")

)

ORDER BY deptno;

 

SELECT *

FROM (SELECT deptno, job, empno, sal

      FROM emp)

PIVOT ( COUNT(empno) AS CNT,

        SUM(NVL(sal,0)) AS S_SAL FOR job IN('CLERK' as "C",

                                            'MANAGER' as "M",

                                            'PRESIDENT' as "P",

                                            'ANALYST' as "A",

                                            'SALESMAN' as "S")

      )

ORDER BY deptno;

 

CREATE TABLE t_unpivot AS

(SELECT * FROM (SELECT deptno, job, empno

                FROM emp)

PIVOT

(  COUNT (empno)

    FOR job IN('CLERK' as "CLERK",

             'MANAGER' as "MANAGER",

             'PRESIDENT' as "PRESIDENT",

             'ANALYST' as "ANALYST",

             'SALESMAN' as "SALESMAN")

)

);

 

SELECT * FROM t_unpivot;

 

SELECT * 

FROM t_unpivot

UNPIVOT(

empno FOR job IN(CLERK, MANAGER, PRESIDENT, ANALYST, SALESMAN)

);

 

SELECT MAX(pay + NVL(bonus, 0)) "MAX", MIN(pay + NVL(bonus, 0)) "MIN", AVG(pay + NVL(bonus, 0)) "AVG"

FROM professor;

 

SELECT * FROM student;

 

SELECT COUNT(*)"합계",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'01',0)) "1월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'02',0)) "2월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'03',0)) "3월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'04',0)) "4월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'05',0)) "5월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'06',0)) "6월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'07',0)) "7월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'08',0)) "8월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'09',0)) "9월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'10',0)) "10월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'11',0)) "11월",

       COUNT(DECODE(TO_CHAR(birthday, 'MM'),'12',0)) "12월"

FROM student;

 

SELECT substr(tel, 1, INSTR(TEL, ')', 1, 1)-1 ) from student;

 

SELECT  COUNT(*)"합계",

        COUNT(DECODE( substr(tel, 1, INSTR(TEL, ')', 1, 1)-1 ) ,'02',0)) "서울",

        COUNT(DECODE( substr(tel, 1, INSTR(TEL, ')', 1, 1)-1 ) ,'031',0)) "경기",

        COUNT(DECODE( substr(tel, 1, INSTR(TEL, ')', 1, 1)-1 ) ,'051',0)) "부산",

        COUNT(DECODE( substr(tel, 1, INSTR(TEL, ')', 1, 1)-1 ) ,'052',0)) "울산",

        COUNT(DECODE( substr(tel, 1, INSTR(TEL, ')', 1, 1)-1 ) ,'053',0)) "대구",

        COUNT(DECODE( substr(tel, 1, INSTR(TEL, ')', 1, 1)-1 ) ,'055',0)) "경남"

FROM student;

 

insert into emp (empno , deptno , ename , sal)

values (1000,10,'홍길동',3600) ;

 

insert into emp (empno , deptno , ename , sal)

values (2000,30,'일지매',3000);

 

commit;

 

SELECT * FROM (SELECT deptno, job, empno, sal

                FROM emp)

PIVOT

(

    COUNT(empno) as CNT,

    SUM(NVL(sal,0)) as S_SAL FOR job IN('CLERK' as "CLERK",

             'MANAGER' as "MANAGER",

             'PRESIDENT' as "PRESIDENT",

             'ANALYST' as "ANALYST",

             'SALESMAN' as "SALESMAN")

             )

ORDER BY deptno;      

 

SELECT deptno,

SUM(DECODE(job, 'CLERK', sal, 0)) "CLERK",

SUM(DECODE(job, 'MANAGER', sal, 0)) "MANAGER",

SUM(DECODE(job, 'PRESIDENT', sal, 0)) "PRESIDENT",

SUM(DECODE(job, 'ANALYST', sal, 0)) "ANALYST",

SUM(DECODE(job, 'SALESMAN', sal, 0)) "SALESMAN",

SUM(NVL2(job, sal, 0)) "합계"

FROM emp

GROUP BY ROLLUP(deptno);

 

SELECT profno, name, pay,

       RANK() OVER (ORDER BY pay) AS RANK,

       RANK() OVER (ORDER BY pay DESC) AS RANK_DESC

FROM professor;

 

SELECT profno, name, pay,

       RANK() OVER (ORDER BY pay) AS RANK,

       RANK() OVER (ORDER BY pay DESC) AS RANK_DESC

FROM professor;

 

SELECT s.name "학생이름", s.deptno1 "학과번호", d.dname "학과이름"

FROM student s, department d

WHERE s.deptno1 = d.deptno;

 

SELECT * from student;

 

SELECT s.name "학생이름", p.profno "교수번호", p.name "교수이름"

FROM student s, professor p

WHERE s.profno = p.profno;

 

SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"

FROM student s, professor p, department d

WHERE s.deptno1 = d.deptno

AND s.profno = p.profno;

 

SELECT * FROM gift;

 

SELECT go.gname "고객명", go.point "POINT", gi.gname "상품명"

FROM gogak go, gift gi

WHERE go.point BETWEEN gi.g_start AND gi.g_end;

 

SELECT ename, sal

FROM emp

WHERE sal >(SELECT sal

            FROM emp

            WHERE ename = 'SCOTT');

 

SELECT s.name "학생이름", d.dname

FROM student s, department d

WHERE s.deptno1 = d.deptno

AND s.deptno1 = (SELECT deptno1

                FROM student

                WHERE name = '이윤나');

 

SELECT p.name "교수명", p.hiredate "입사일", d.dname "학과명"

FROM professor p, department d

WHERE p.deptno = d.deptno

AND p.hiredate > (SELECT hiredate

                  FROM professor

                  WHERE name = '송도권');

 

SELECT name "이름", position "직급", TO_CHAR(pay, '999,999,999')|| ' 원' "연봉"

FROM emp2

WHERE pay > ANY (SELECT pay

                 FROM emp2

                 WHERE position = '과장');

                 

                 

SELECT name "이름", position "직급", TO_CHAR(pay, '999,999,999')|| ' 원' "연봉"

FROM emp2

WHERE pay < ANY (SELECT pay

                 FROM emp2

                 WHERE position = '과장');

 

SELECT name "이름", grade "학년", weight "몸무게"

FROM student

WHERE weight < ALL(SELECT weight

                   FROM student

                   WHERE grade = '4');

 

SELECT grade "학년", name "이름", height "키"

FROM student

WHERE (grade, height) IN (SELECT grade, MAX(height)

                 FROM student

                 GROUP BY grade)

ORDER BY 1;

 

SELECT p.PROFNO "교수번호", p.name "교수명", p.hiredate "입사일", d.dname "학과명"

FROM professor p, department d

WHERE p.deptno = d.deptno

AND (p.deptno, p.hiredate) IN (SELECT deptno, MIN(hiredate)

                                FROM professor

                                GROUP BY deptno)

ORDER BY 4;

 

SELECT name "사원이름", position "직급", pay "급여"

FROM emp2 a

WHERE pay >= (SELECT AVG(pay)

              FROM emp2 b

              WHERE a.position = b.position);

              

INSERT INTO dept2(dcode, dname, pdept)

VALUES(9002, '특판3팀', '영업부');

 

INSERT INTO professor(profno, name, id, position, pay, hiredate)

VALUES (5001, '김설희', 'Love_me', '정교수', 510, '2011-11-14');

 

CREATE TABLE professor2

AS SELECT * FROM professor;

 

INSERT INTO professor2

SELECT * FROM professor;

 

INSERT ALL

INTO p_01(no, name)

VALUES(1, 'AAA')

INTO p_02(no, name)

VALUES(2, 'BBB')

SELECT * FROM dual;

 

INSERT ALL

WHEN profno BETWEEN 1000 AND 1999 THEN

INTO p_01 VALUES(profno, name)

WHEN profno BETWEEN 2000 AND 2999 THEN

INTO p_02 VALUES(profno, name)

SELECT profno, name

FROM professor;

 

SELECT COUNT(*) FROM p_01;

 

CREATE TABLE scott.test01(

no NUMBER, name VARCHAR2(20), addr VARCHAR2(20));

 

BEGIN

FOR i IN 1..500000 LOOP

INSERT INTO scott.test01

VALUES(i, DBMS_RANDOM.STRING('A', 19), 

          DBMS_RANDOM.STRING('Q', 19));

END LOOP;

COMMIT;

END;

/

 

SELECT COUNT(*) FROM SCOTT.TEST01;

 

ANALYZE TABLE scott.test01 COMPUTE STATISTICS;

 

Select * FROM pt_01;

 

Select * FROM pt_02;

 

Select * FROM P_total;

 

MERGE INTO p_total total

USING pt_01 p01

ON (total.판매번호 = p01.판매번호)

WHEN MATCHED THEN

UPDATE SET total.제품번호 = p01.제품번호

WHEN NOT MATCHED THEN

INSERT VALUES(p01.판매번호, p01.제품번호, p01.수량, p01.금액);

 

MERGE INTO p_total total

USING pt_02 p02

ON (total.판매번호 = p02.판매번호)

WHEN MATCHED THEN

UPDATE SET total.제품번호 = p02.제품번호

WHEN NOT MATCHED THEN

INSERT VALUES(p02.판매번호, p02.제품번호, p02.수량, p02.금액);

 

SELECT * FROM p_total;

 

CREATE TABLE dept5

AS  SELECT *

    FROM dept2

    WHERE 1 = 2;

 

SELECT * FROM dept5;

 

CREATE TABLE vt001

( no1 number,

  no2 number,

  no3 number GENERATED ALWAYS AS (no1 + no2) VIRTUAL);

 

SELECT * FROM vt001;