create, alter, select table using subquery in oracle - 서브 쿼리를 이용한 테이블 생성

안녕하세요

이번 시간에는 오라클(oracle)에서 서브쿼리(subquery)를 활용하여 테이블을 생성하고

조회하고 삽입하는 예제를 올려봅니다.

잘 봐주세요~!



서브쿼리를 이용한 테이블 생성

CREATE TABLE 테이블명(컬럼명1, 컬럼명2, ...)
AS SUBQUERY

[1] 사원테이블에서 30번 부서에 근무하는 사원의 정보만 추출하여 EMP_30 테이블을 생성하세요
단 일은 사번, 이름, 업무, 입사일, 급여, 보너스를 포함시키세요

CREATE TABLE EMP_30(EMPNO, ENAME, JOB, HIREDATE, SAL, COMM)
AS
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, COMM
FROM EMP WHERE DEPTNO=30;

SELECT * FROM EMP_30;

[2] EMP에서 부서별로 인원수, 평균급여, 급여의 합, 최소급여, 최대급여를 포함하는 EMP_SUM 테이블을 생성하세요

CREATE TABLE EMP_SUM(DEPTNO, CNT, AVG_SAL, SUM_SAL, MIN_SAL, MAX_SAL)
AS
SELECT DEPTNO, COUNT(ENAME), ROUND(AVG(SAL),2), SUM(SAL), MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO;

SELECT * FROM EMP_SUM;

[3] EMP에서 사번, 이름, 부서번호, 업무만 포함하는 EMP_TEMP 테이블을 생성하는데, 데이터는 포함하지 않고 구조만 생성하세요

CREATE TABLE EMP_TEMP2(EMPNO, ENAME, DEPTNO, JOB)
AS
SELECT EMPNO, ENAME, DEPTNO, JOB FROM EMP WHERE 1=2; -- 조건을 말도 안되게 주면 구조만 생성
SELECT * FROM EMP_TEMP2;

테이블에 컬럼 추가 및 변경, 삭제

-컬럼 추가
ALTER TABLE 테이블명 ADD(추가할 컬럼정보 [DEFAULT 기본값])
-컬럼 변경
ALTER TABLE 테이블명 MODIFY(변경할 컬럼정보 [DEFAULT 기본값])
-컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN(삭제할 컬럼명)
-컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN 예전컬럼명 TO 새컬럼명

CREATE TABLE DEMO(
NO NUMBER);

SELECT * FROM DEMO;

[3]DEMO 테이블에 NAME 컬럼을 추가하세요 VARCHAR2(10) 유형으로
  DEMO 테이블에 POINT 컬럼을 추가하세요 NUMBER(3)
  ALTER TABLE DEMO ADD NAME VARCHAR2(10);
  ALTER TABLE DEMO ADD POINT NUMBER(3);

  DESC DEMO;

[4]DEMO 테이블에 생성된 POINT 컬럼의 자료형을 CHAR(3)으로 변경하세요
 ALTER TABLE DEMO MODIFY POINT CHAR(3);

[5]DEMO 테이블의 POINT 컬럼을 삭제하세요
ALTER TABLE DEMO DROP POINT;

ALTER TABLE DEMO ADD POINT NUMBER(3) NOT NULL;

INSERT INTO DEMO VALUES (1,'홍길동',100);

INTRO 컬럼을 추가하세요 VARCHAR2(100) NOT NULL로 한다면 => 오류가 발생하면서 추가가 안됨
NOT NULL인 열을 추가하기 위해서는 테이블이 비어있어야 함
ALTER TABLE DEMO ADD INTRO VARCHAR2(100);
DESC DEMO;

COMMIT;

NAME이라는 컬럼을 USERNAME이라고 변경
ALTER TABLE DEMO RENAME COLUMN NAME TO USERNAME;

제약조건 추가=> NO 컬럼에 PK 제약조건 주기
ALTER TABLE DEMO ADD CONSTRAINT DEMO_NO_PK PRIMARY KEY(NO);

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS
FROM user_constraints where table_name='DEMO';

INSERT INTO DEMO VALUES(2, '김길동', 300, '자기소개');

제약조건 비활성화
ALTER TABLE 테이블명 DISABLE CONSTRAINT 제약조건명 [CASCADE]
ALTER TABLE DEMO DISABLE CONSTRAINT DEMO_NO_PK; -- STATUS : DISABLEED로 변경

-- 프라이머리키 제약조건이 비 활성화 됨 2번이 계속 동일하게 들어감
ROLLBACK;

제약조건 활성화
ALTER TABLE DEMO ENABLE CONSTRAINT DEMO_NO_PK;



객체이름 변경(테이블명 변경)
RENAME OLD_NAME TO NEW NAME;

EMP_30 테이블명을 EMP_TEMP30으로 변경하세요
RENAME EMP_30 TO EMP_TEMP30;
SELECT * FROM EMP_TEMP30;

테이블 삭제
DROP TABLE 테이블명;

DROP TABLE EMP_30;
ROLLBACK;

DROP TABLE EMP2;

EMP와 동일한 구조의 테이블 EMP2를 만드세요(사번,이름,급여,입사일,부서번호)
(구조만 복사)

CREATE TABLE EMP2(EMPNO,ENAME,SAL,HIREDATE,DEPTNO)
AS
SELECT EMPNO,ENAME,SAL,HIREDATE,DEPTNO FROM EMP WHERE 1=2;

SELECT * FROM EMP2;

INSERT INTO EMP2(EMPNO, ENAME, SAL)
VALUES(1122, 'SCOTT', 3000);

INSERT INTO EMP2
VALUES(1123, 'JAMES', 4000, SYSDATE, 20);

서브쿼리를 이용한 INSERT문

INSERT INTO EMP2
SELECT EMPNO, ENAME, SAL, HIREDATE, DEPTNO
FROM EMP WHERE DEPTNO=10;

EMP2에서 7782번 사원의 부서번호를 20번 부서로 수정하고 급여는 500원을 인상해주세요
UPDATE EMP2 SET DEPTNO=20, SAL = SAL+500
WHERE EMPNO=7782;

[5] MEMBER에서 13/9/1 이후 등록한 고객들의 마일리지를 300점씩 올려주세요
UPDATE MEMBER SET MILEAGE = MILEAGE + 300
WHERE REG_DATE > '13/09/01';
ROLLBACK;

[6] 등록된 MEMBER중 이름에 '김' 자가 들어있는 모든 이름을 '최'로 변경하세요
UPDATE MEMBER SET NAME = REPLACE(NAME, '김', '최') WHERE NAME LIKE '김%';
SELECT * FROM MEMBER;
ROLLBACK;

[7] EMP2에서 SCOTT의 부서번호와 등록일을 MILLER와 동일한 부서번호, 등록일로 수정하세요

UPDATE EMP2 SET(DEPTNO, HIREDATE)
= (SELECT DEPTNO, HIREDATE FROM EMP WHERE ENAME='MILLER')
WHERE ENAME='SCOTT';

INSERT INTO EMP2(EMPNO, ENAME) VALUES (7788, 'SCOTT');

COMMIT;
SELECT * FROM EMP2;

UPDATE MEMBER SET ADDR=NAME, JOB=MILEAGE;
SELECT * FROM MEMBER;
ROLLBACK;

EMP에서 사번 7782번 사원을 삭제하세요
DELETE FROM EMP WHERE EMPNO=7782;
ROLLBACK;

EMP에서 입사일자가 83년인 사원의 정보를 삭제하세요
SELECT * FROM EMP;
DELETE FROM EMP
WHERE TO_CHAR(HIREDATE,'YY')='83';

오라클(Oracle) 서브쿼리를 활용해 테이블 조회해보자

안녕하세요

요즘 날씨가 갑자기 추어졌네요..

이제는 여름,겨울 2계절인것 같습니다.

모두 감기 조심하세요~!


이번 시간에는 오라클의 유용하게 쓰이는 서브쿼리 예제를 올려봅니다.

서브쿼리는 괄호 안에 있는 쿼리를 말합니다.


자 그럼 바로 예제 들어가겠습니다

ㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱ


사원 테이블에서 SCOTT의 급여보다 많은 사원의 사번, 이름, 업무, 급여를 출력하세요
SELECT SAL FROM EMP WHERE ENAME='SCOTT';

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL > 3000;

SELECT EMPNO, ENAME, JOB, SAL -- Main query
FROM EMP
WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SCOTT'); --Nested query

[1] EMP에서 사번이 7521인 사원과 업무가 같고, 급여가 7934인 사원보다 많은 사원의 사번, 이름, 업무, 급여를 출력하세요

SELECT JOB FROM EMP WHERE EMPNO=7521;

SELECT SAL FROM EMP WHERE EMPNO=7934;

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=7521)
AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7934);

--단일 행 서브쿼리
[2] EMP에서 사원들의 평균급여보다 적은 사원의 사번, 이름, 급여, 업무를 출력하세요

SELECT EMPNO, ENAME, SAL, JOB
FROM EMP
WHERE SAL < (SELECT AVG(SAL)FROM EMP);

[3] 사원 테이블에서 사원의 급여가 20번 부서의 최소 급여 보다 많은 부서의 부서번호와 최소급여를 출력하세요

SELECT DEPTNO, MIN(SAL) FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20);

다중 행 서브 쿼리
- 하나 이상의 행을 반환하는 서브쿼리
- 다중행 서브쿼리는 다중행 연산자를 사용해야 한다.
- 다중행 연산자는 하나 이상의 값을 요구한다.

#다중행 연산자
IN 연산자
ANY 연산자
ALL 연산자
EXIST 연산자

--다중행, 다중열 일 때는 IN 이 들어가야함
[4] 업무별로 최대 급여를 받는 사원의 사번과 이름, 급여를 출력하세요
SELECT JOB, EMPNO, ENAME, SAL
FROM EMP
WHERE (JOB, SAL) IN
(SELECT JOB, MAX(SAL)
FROM EMP
GROUP BY JOB);

ANY 연산자
- 서브쿼리 결과 값 중 어느 하나 값이라도 만족되면 결과값을 반환한다.

SELECT ENAME, SAL FROM EMP
WHERE DEPTNO<>20
--AND SAL > ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN');
AND SAL > (SELECT MIN(SAL) FROM EMP WHERE JOB = 'SALESMAN');

> ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN'); 의 의미
-> SAL > (SELECT MIN(SAL) FROM EMP WHERE JOB = 'SALESMAN')와 동일한 의미

SELECT DEPTNO, ENAME, SAL FROM EMP
WHERE DEPTNO<>20
AND SAL < ANY (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');
==>SAL<(SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN')과 동일

#ALL 연산자
- 서브쿼리 결과값 중 모든 결과값이 만족되어야만 결과값을 반환한다.

SELECT ENAME, SAL FROM EMP
WHERE DEPTNO<>20
AND SAL>ALL(SELECT SAL FROM EMP WHERE JOB='SALESMAN'); -- 최대값보다 크냐  부등호가 < 이면 최소값보다 작냐



# EXISTS 연산자
-EXISTS 연산자를 사용하면, 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만을 결과로 반환해준다
-SUBQUERY에서 적어도 1개의 행을 RETURN 하면 논리식은 참이고 그렇지 않으면 거짓입니다.

[5] 사원을 관리할 수 있는 사원의 정보를 보여주세요
SELECT ENAME, JOB FROM EMP E -- 여기서 E는 관리자를 의미(관리자의 이름과 업무를 보여주자)
WHERE EXISTS (SELECT EMPNO FROM EMP WHERE E.EMPNO=MGR); -- EMP의 사번 = MGT(관리자 사번) -> 관리자냐를 물어보는 것임

#다중 열 서브쿼리
-서브쿼리 결과값이 두 개 이상의 컬럼을 반환하는 경우

[6] EMP에서 급여와 보너스가 30번 부서의 있는 사원의 급여, 보너스와 일치하는 사원의 이름, 부서번호를 출력하세요
SELECT ENAME, DEPTNO, SAL, COMM
FROM EMP
WHERE (SAL, COMM)
IN (SELECT SAL, COMM FROM EMP WHERE DEPTNO=30 AND COMM IS NOT NULL);

[7] 업무별로 최소 급여를 받는 사원의 사번, 이름, 업무, 급여를 출력하세요. 단 업무별로 정렬하세요
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE (JOB, SAL) IN
(SELECT JOB, MIN(SAL) FROM EMP GROUP BY JOB)
ORDER BY JOB;

[실습]
84] 고객 테이블에 있는 고객 정보 중 마일리지가 가장 높은 금액의 고객 정보를 보여주세요.
SELECT NAME, JOB, MILEAGE FROM MEMBER
WHERE MILEAGE = (SELECT MAX(MILEAGE) FROM MEMBER);

85] 상품 테이블에 있는 전체 상품 정보 중 상품의 판매가격이 판매가격의 평균보다 큰  상품의 목록을 보여주세요.
 단, 평균을 구할 때와 결과를 보여줄 때의 판매 가격이 50만원을 넘어가는 상품은 제외시키세요.
   
SELECT PRODUCTS_NAME, OUTPUT_PRICE
FROM PRODUCTS
WHERE OUTPUT_PRICE > (SELECT AVG(OUTPUT_PRICE)FROM PRODUCTS
WHERE OUTPUT_PRICE <=500000)
AND OUTPUT_PRICE<=500000;

86] 상품 테이블에 있는 판매가격에서 평균가격 이상의 상품 목록을 구하되 / <-- 여기 먼저 구하자
평균을 구할 때 판매가격이 최대인 상품을 제외하고 평균을 구하세요.
--서브쿼리 안에 서브쿼리가 들어간다

SELECT * FROM PRODUCTS
WHERE OUTPUT_PRICE >=
(SELECT AVG(OUTPUT_PRICE) FROM PRODUCTS
WHERE OUTPUT_PRICE <> (SELECT MAX(OUTPUT_PRICE)FROM PRODUCTS));

87] 상품 카테고리 테이블에서 카테고리 이름에 컴퓨터라는 단어가 포함된 카테고리에 속하는 상품 목록을 보여주세요.

SELECT * FROM PRODUCTS WHERE CATEGORY_FK IN-- SELECT * FROM PRODUCTS WHERE CATEGORY_FK = -> '=' 단일 행 연산자 일때, 'IN' 다중 행
(SELECT CATEGORY_CODE FROM CATEGORY WHERE category_name LIKE '%컴퓨터%');

88] 고객 테이블에 있는 고객정보 중 직업의 종류별로 가장 나이가 많은 사람의 정보를 화면에 보여주세요.

SELECT NAME, JOB, AGE FROM MEMBER WHERE (JOB, AGE) IN -- 다중행 IN
(SELECT JOB, MAX(AGE) -- 다중행
FROM MEMBER GROUP BY JOB);

DELETE문에서 사용
(DELETE문은 직접 실행해보시고 ROLLBACK으로 취소해보세요)

89] 상품 테이블에서 공급가가 가장 큰 상품은 삭제 시키는 SQL 문을 작성하세요

DELETE FROM PRODUCTS WHERE INPUT_PRICE =
(SELECT MAX(INPUT_PRICE) FROM PRODUCTS);

ROLLBACK;

90] 상품 테이블에서 상품 목록을 공급업체(EP_CODE_FK)별로 정리한 뒤
각 공급업체 별로 최소 공급가(INPUT_PRICE)를 가진 상품을 삭제하세요

SELECT * FROM PRODUCTS;
DELETE FROM PRODUCTS WHERE (EP_CODE_FK, INPUT_PRICE) IN
(SELECT EP_CODE_FK, MIN(INPUT_PRICE) FROM PRODUCTS GROUP BY EP_CODE_FK);
ROLLBACK;

실무에서도 중요한 오라클 조인(Join) 개념과 예제

안녕하세요

이번 시간에는 오라클에서 중요한 개념 중 하나인

조인에 대해 개념과 예제를 보여주는 시간을 가지겠습니다


먼저 오라클에서 조인(JOIN)이란..

'두 개 이상의 테이블이나 데이터 베이스를 서로 연결하여 데이터를 검색할 때 사용하는 방법으로 서로 연관있는 테이블을 이용하여 두 개의 테이블이 마치 하나의 테이블인 것처럼 보여주는 것'

일반적으로 PRIMARY KEY(PK)와 FOREIGN KEY (FK)를 사용하여 JOIN 하는 경우가 대부분이지만 논리적인 값들의 연관으로 JOIN하는 경우도 있습니다.


조인(JOIN) 구문 형태

==================================
SELECT table1.colum1[, table2.column2 ...]
FROM  table1, table2
WHERE table1.colum1=table2.column2;

또는 join절을 이용한 명시적 조인

SELECT table1.colum1[,table2.colum2...]
FROM table1 JOIN table2
ON  table1.column1 = talbe2.column2;
===================================


조인의 형태

--------------------------------------------------------------------
  1) EQUI JOIN : 조인조건이 정확히 일치하는 경우에 사용(pk와 fk를 사용하여)
  2) NON-EQUI JOIN : 조인 조건이 정확히 일치하지 않는 경우에 사용(등급,학점)
  3) OUTER JOIN : 조인 조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력
  4) SELF JOIN : 하나의 테이블에서 행들을 조인하고자 할 때 사용.

   그외
   5) Cartesian Product : 모든 가능한 행들의 조인
   6) Set Operator    : 여러 개의 select문장을 연결하여 작성한다.
--------------------------------------------------------------------


예제

dept와 emp의 equi join

SELECT D.DNAME, D.DEPTNO, E.ENAME, E.JOB
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO
ORDER BY D.DEPTNO ASC;


명시적 JOIN절 사용
SELECT DNAME, D.DEPTNO, ENAME, LOC
FROM DEPT D JOIN EMP E
ON D.DEPTNO=E.DEPTNO
ORDER BY 1;

where절을 쓴거와 join절을 쓴것의 결과는 위와 똑같습니다.

[1]SALESMAN의 사원번호, 이름, 급여, 부서명과 근무지를 출력하세요
SELECT E.EMPNO, E.ENAME, E.SAL, E.JOB, D.DNAME, D.LOC
FROM EMP E JOIN DEPT D
ON D.DEPTNO=E.DEPTNO AND E.JOB='SALESMAN';

[2]카테고리 테이블과 상품 테이블을 JOIN하여 카테고리명, 카테고리 코드, 상품명, 상품 판매가, 제조사(COMPANY)를 출력하세요
SELECT C.CATEGORY_NAME, C.CATEGORY_CODE, P.PRODUCTS_NAME, P.OUTPUT_PRICE, P.COMPANY
FROM CATEGORY C JOIN PRODUCTS P
ON C.CATEGORY_CODE = P.CATEGORY_FK
ORDER BY 2;

위의 'C', 'P' 빼도 실행에는 문제가 없습니다.


[3]상품의 카테고리, 상품명, 공급가, 판매가를 출력하되, 카테고리가 'TV'인 것은 제외시키세요
또한 상품의 판매가가 저렴한 순으로 정렬하세요
SELECT C.CATEGORY_NAME, P.PRODUCTS_NAME, INPUT_PRICE, OUTPUT_PRICE
FROM CATEGORY C, PRODUCTS P
WHERE C.CATEGORY_CODE=P.CATEGORY_FK
AND CATEGORY_NAME <>'TV'
ORDER BY OUTPUT_PRICE;

#NON-EQUI JOIN
조인 조건이 EQUAL(=)이 아닌 다른 연산 기호로 만들어지는 경우

EMP, SALGRADE 테이블을 살펴보자

SELECT * FROM SALGRADE;

각 사원의 사원명, 급여, 급여 등급을 출력하세요
SELECT ENAME, SAL, GRADE, LOSAL, HISAL
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

[4] 공급업체 테이블과 상품 테이블을 조인하여 공급업체명, 공급업체 코드, 상품명, 공급가를 출력하세요 (EQUI JOIN)

SELECT * FROM supply_comp; -- PK==>EP_CODE
SELECT * FROM PRODUCTS; -- 상품 테이블

SELECT EP_NAME, EP_CODE, PRODUCTS_NAME, INPUT_PRICE
FROM SUPPLY_COMP C JOIN PRODUCTS P
ON C.EP_CODE = P.EP_CODE_FK
ORDER BY 1;

[5] 공급업체 테이블과 상품 테이블을 조인하여 공급업체명, 상품명, 공급가를 표시하되 상품의 공급가가 100000원 이상의 상품 정보만 표시하세요
단, 공급업체는 공급업체B만 표시되도록 하세요
SELECT EP_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM SUPPLY_COMP S JOIN PRODUCTS P
ON S.EP_CODE = P.EP_CODE_FK AND INPUT_PRICE>=100000
WHERE EP_NAME='공급업체B';

-- 잘못된 데이터 아래
SELECT EP_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM SUPPLY_COMP S JOIN PRODUCTS P
ON P.INPUT_PRICE>=100000 AND EP_NAME='공급업체B';

두 테이블이 서로 EQUAL하지 않기 때문에 해당 정보를 독립적으로 보여준다.
그냥 공급업체B와 10만원이 넘는 상품을 결합하여 잘못된 데이터를 출력

#OUTER JOIN
두 개 이상의 테이블을 EQUI 조인하는 경우, 한쪽 테이블에 일치하는 행이 없으면 다른 쪽 테이블을 NULL로 하여 그 값을 보여준다.

SELECT * FROM DEPT;

EQUI 조인의 예
SELECT D.DEPTNO, DNAME, ENAME, SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO ORDER BY 1;

SELECT D.DEPTNO, DNAME, ENAME, SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO(+)
ORDER BY 1;

명시적 JOIN 절을 이용한 OUTER 조인
오라클 9i 버전부터는 ANSI/ISO 표준 SQL인
LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN을 지원한다.

OUTER JOIN을 해보자.

SELECT D.DEPTNO, DNAME, ENAME
FROM DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO ORDER BY 1 DESC;

:LEFT OUTER JOIN : 왼쪽을 테이블을 기준으로 하여 출력
:RIGHT OUTER JOIN : 오른쪽 테이블을 기준으로 하여 출력
:FULL OUTER JOIN : 양쪽 테이블에 다 아웃터 조인을 건다.

SELECT D.DEPTNO, DNAME, ENAME
FROM DEPT D RIGHT OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO ORDER BY 1 DESC;

SELECT D.DEPTNO, DNAME, ENAME
FROM DEPT D FULL OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO ORDER BY 1 DESC;

[6] 상품 테이블의 모든 상품을 공급업체, 공급업체코드, 상품명, 공급가, 판매가 순서로 출력하되 일치하지 않는 공급업체도 출력되도록 하세요 (일치하지 않는 공급업체도.. <= outer join 활용
SUPPLY_COMP, PRODUCTS

SELECT * FROM SUPPLY_COMP;

SELECT * FROM PRODUCTS;

조인 활용 예제

SELECT EP_NAME, EP_CODE, PRODUCTS_NAME, INPUT_PRICE, OUTPUT_PRICE
FROM SUPPLY_COMP S JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;

아래 나머지 조인은 직접 실행시켜보세요~!

SELECT S.EP_NAME, S.EP_CODE, P.PRODUCTS_NAME, P.INPUT_PRICE, P.OUTPUT_PRICE
FROM SUPPLY_COMP S LEFT OUTER JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;

SELECT S.EP_NAME, S.EP_CODE, P.PRODUCTS_NAME, P.INPUT_PRICE, P.OUTPUT_PRICE
FROM SUPPLY_COMP S RIGHT OUTER JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;

SELECT S.EP_NAME, S.EP_CODE, P.PRODUCTS_NAME, P.INPUT_PRICE, P.OUTPUT_PRICE
FROM SUPPLY_COMP S FULL OUTER JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;


[7] 상품의 공급업체명, 카테고리명, 상품명, 판매가를 출력하세요
단, 공급업체나 상품 카테고리가 없는 상품도 출력되도록 합니다.
(category, supply_comp, products)

SELECT EP_NAME, CATEGORY_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM CATEGORY C JOIN PRODUCTS P
ON C.CATEGORY_CODE = P.CATEGORY_FK
JOIN SUPPLY_COMP S
ON P.EP_CODE_FK=S.EP_CODE
ORDER BY 1;

SELECT EP_NAME, CATEGORY_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM CATEGORY C RIGHT OUTER JOIN PRODUCTS P
ON C.CATEGORY_CODE = P.CATEGORY_FK
LEFT OUTER JOIN SUPPLY_COMP S
ON P.EP_CODE_FK=S.EP_CODE
ORDER BY 1;

#SELF JOIN
:테이블 자체적으로 조인할 경우, 가령 각 사원명과 각 사원의 관리자명을 함께 출력하세요

SELECT EMPNO, ENAME"사번" , MGR"관리자 사번" FROM EMP;

SELECT E.EMPNO, E.ENAME, M.ENAME
FROM EMP E JOIN EMP M
ON E.MGR=M.EMPNO ORDER BY 1;

[8] emp 테이블에서 NEW YORK에서 근무하고 있는 사원에 대하여 이름, 업무, 급여, 부서명을 출력하는 SELECT 문을 작성하세요
SELECT ENAME, JOB, SAL, DNAME, D.DEPTNO, D.LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO AND D.LOC='NEW YORK';

[9] EMP에서 보너스를 받는 사원의 이름, 부서명, 보너스, 근무지를 출력하세요
SELECT ENAME, D.DEPTNO, SAL, D.LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO AND COMM>0;

조인절 안쓴 버전
SELECT ENAME, DNAME, COMM, LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND COMM IS NOT NULL; --<>NULL [X]

[10]EMP에서 이름에 'L'자를 갖는 사원의 이름, 업무, 부서명, 급여를 출력하세요
SELECT ENAME, JOB, DNAME, SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO AND (ENAME) LIKE '%L%';

[11]EMP 테이블에서 '누구의 관리자는 누구이다'는 내용을 출력하세요
단, 관리자를 갖지 않는 KING도 포함되도록 출력하세요
Ex) SMITH의 관리자는 FORD입니다. KING의 관리자는 NULL입니다.

SELECT E.ENAME||'의 관리자는'||NVL(M.ENAME,'NONE')||'입니다'"종속관계"
FROM EMP E LEFT OUTER JOIN EMP M
ON E.MGR = M.EMPNO;