오라클 sql Practice7 [JOIN]

SQL 처음 공부했을 때 썼던 코드 복습하는 느낌으로 기록합니다.

연습 Table은 오라클에서 처음 제공하는 emp, dept 를 이용하였습니다.

CREATE TABLE DEPT_FK1 AS SELECT * FROM DEPT;
SELECT * FROM DEPT_FK1;

-- DESC DEPT_FK1 과 DESC DEPT 의 결과값이 다름. NOT NULL 제약 조건이 다르게 생성된다.
DESC DEPT_FK1;
DESC DEPT;

CREATE TABLE EMP_FK1 AS SELECT* FROM EMP;

ALTER TABLE DEPT_FK1 MODIFY(DEPTNO CONSTRAINT TBLDTFK1_DPTNO_PK PRIMARY KEY);
ALTER TABLE EMP_FK1 MODIFY(DEPTNO CONSTRAINT TBLEMPFK1_DPTNO_FK REFERENCES DEPT_FK1(DEPTNO));

DESC EMP_FK1;
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT_FK1' OR TABLE_NAME = 'EMP_FK1';

--제약조건은 수정 불가능 오류나옴. 삭제하고 새롭게 설정해야됨.
ALTER TABLE EMP_FK1 MODIFY(DEPTNO CONSTRAINT TBLEMPFK1_DPTNO_FK REFERENCES DEPT_FK1(DEPTNO) ON DELETE CASCADE);

ALTER TABLE EMP_FK1 DROP CONSTRAINT TBLEMPFK1_DPTNO_FK;

DELETE FROM DEPT_FK1 WHERE DEPTNO = 10;
SELECT * FROM DEPT_FK1;
SELECT * FROM EMP_FK1;
ROLLBACK;

ALTER TABLE EMP_FK1 MODIFY(DEPTNO CONSTRAINT TBLEMPFK1_DPTNO_FK 
REFERENCES DEPT_FK1(DEPTNO) ON DELETE SET NULL);

DELETE FROM DEPT_FK1 WHERE DEPTNO = 10;
--------------------------------------------EX
CREATE TABLE DEPT_CONST ( 
    DEPTNO NUMBER(2) CONSTRAINT DEPTCONST_DEPTNO_PK PRIMARY KEY,
    DNAME VARCHAR(14) CONSTRAINT DEPTCONST_DNAME_UNQ UNIQUE,
    LOC VARCHAR(13) CONSTRAINT DEPTCONST_LOC_NN NOT NULL
    );
    
CREATE TABLE EMP_CONST(
    EMPNO NUMBER(4) CONSTRAINT EMPCOST_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR(10) CONSTRAINT EMPCONST_ENAME_NN NOT NULL,
    JOB VARCHAR(9),
    TEL VARCHAR(20) CONSTRAINT EMPCONST_TEL_UNQ UNIQUE,
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTO NUMBER(2) CONSTRAINT EMPCONST_DEPTNO_FK REFERENCES DEPT_CONST(DEPTNO)
    );
    
DESC DEPT_CONST;
DESC EMP_CONST;
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS 
WHERE TABLE_NAME = 'EMP_CONST';
-------------------------------------------------1번
SELECT SUBSTR(ENAME,1,3) FROM EMP WHERE ENAME LIKE '%A%';
-------------------------------------------------2번
SELECT INITCAP(ENAME) FROM EMP WHERE ENAME LIKE 'J%' OR ENAME LIKE 'A%';
-------------------------------------------------3번
SELECT LOWER(ENAME) FROM EMP WHERE LENGTH(ENAME) >= 6;

CREATE TABLE TABLE_CHECK (
    LOGIN_ID VARCHAR2(20) CONSTRAINT TBLCK_LOGINID_PK PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLCK_LOGINPW_CK CHECK(LENGTH(LOGIN_PWD) > 3),
    TEL VARCHAR2(20)
    );

-- 4글자 이상이 필요하다. 오류.
INSERT INTO TABLE_CHECK VALUES('TEST_ID','123','010-1111-2222');

INSERT INTO TABLE_CHECK VALUES('TEST_ID','1234','010-1111-2222');
SELECT * FROM TABLE_CHECK;

SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TABLE_CHECK';

CREATE TABLE TABLE_DEFAULT(
    LOGIN_ID VARCHAR2(20) CONSTRAINT TBLDF_LOGINID_PK PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20) DEFAULT '1234',
    TEL VARCHAR2(20)
    );
    
INSERT INTO TABLE_DEFAULT VALUES ('TEST_ID',NULL,'010-1111-2222');
INSERT INTO TABLE_DEFAULT(LOGIN_ID, TEL) VALUES ( 'TEST-ID2','010-1111-3333');

SELECT * FROM TABLE_DEFAULT;
-------------------------------------------------4번
SELECT E1.ENAME AS 사원이름, E1.EMPNO AS 사원번호, E2.ENAME AS 관리자이름, E2.EMPNO AS 관리번호
FROM EMP E1, EMP E2 WHERE E1.MGR = E2.EMPNO;

-- 조인 새로운 방식(WHERE  안써도됨)
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, E.DEPTNO, DNAME, LOC
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,E.DEPTNO,DNAME,LOC
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO) WHERE SAL <= 3000;

-- KING 은 상사가 없다.
SELECT E1.ENAME AS 사원이름, E1.EMPNO AS 사원번호, E2.ENAME AS 관리자이름, E2.EMPNO AS 관리자번호
FROM EMP E1 JOIN EMP E2 ON(E1.MGR = E2.EMPNO);

-- KING의 NULL이 추가되서 나옴.
SELECT E1.ENAME AS 사원이름, E1.EMPNO AS 사원번호, E2.ENAME AS 관리자이름, E2.EMPNO AS 관리자번호
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO);
SELECT E1.ENAME AS 사원이름, E1.EMPNO AS 사원번호, E2.ENAME AS 관리자이름, E2.EMPNO AS 관리자번호
FROM EMP E1 RIGHT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO);
SELECT E1.ENAME AS 사원이름, E1.EMPNO AS 사원번호, E2.ENAME AS 관리자이름, E2.EMPNO AS 관리자번호
FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO);
-- LEFT OUTER JOIN
-- 등가조인을 먼저 한 후 OUTER JOIN 을 함.
-- LEFT는 테이블 조인시 테이블 명을 명시 할 때 처음으로 명시한 테이블(왼쪽)을 의미한다.
-- 왼쪽 테이블의 모든 컬럼은 출력을 함.
-- 공통된 값이 없을 경우 NULL 값으로 채워지고 조인이 마무리 됨.
SELECT * FROM DEPT;
-------------------------------------------------5-1번
SELECT EMPNO,ENAME,E.DEPTNO,DNAME FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);
-------------------------------------------------5-2번
SELECT EMPNO, ENAME, DNAME FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO) WHERE ENAME = 'SMITH';
-------------------------------------------------6번
SELECT E1.ENAME,E1.HIREDATE,E2.ENAME,E2.HIREDATE FROM EMP E1 JOIN EMP E2 ON(E1.MGR = E2.EMPNO)
WHERE E1.HIREDATE < E2.HIREDATE;
-------------------------------------------------7번
SELECT EMPNO,ENAME,SAL,DNAME FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
WHERE SAL > (SELECT AVG(SAL) FROM EMP) ORDER BY SAL DESC;
-------------------------------------------------8번
SELECT ENAME, SAL FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
WHERE DNAME = 'SALES' GROUP BY DNAME);
-------------------------------------------------9번
SELECT ENAME, DNAME FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO) 
WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING');
-------------------------------------------------10번
SELECT ENAME, SAL ,LOC FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) 
WHERE SAL > (SELECT AVG(SAL) FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
WHERE LOC = 'CHICAGO' GROUP BY LOC);
-------------------------------------------------11번
SELECT ENAME FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP WHERE COMM IS NULL OR COMM = 0);
-------------------------------------------------12번
SELECT MGR FROM EMP GROUP BY MGR HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY MGR);
-------------------------------------------------13번
SELECT DEPTNO , COUNT(EMPNO) FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO) =
(SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY DEPTNO);
-------------------------------------------------14번
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7499)
AND SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7782);
-------------------------------------------------15번
SELECT EMPNO,ENAME,JOB,DNAME FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
WHERE (JOB,SAL) IN (SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB) ORDER BY JOB DESC;
-------------------------------------------------16번
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);SQL