오라클 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;
'IT > 데이터베이스(Database)' 카테고리의 다른 글
| Oracle Scott 계정 기본 테이블 생성 Query (2) | 2021.07.06 |
|---|---|
| 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 실습 예제 Query (0) | 2021.07.06 |
| Oracle 실습 예제 (0) | 2021.07.06 |
| MySQL(Maria DB) 기본 명령어 정리 (0) | 2021.07.06 |
| MariaDB : Access denied for user ‘root’@’localhost’ 해결방법 (0) | 2021.07.06 |