오라클 sql Practice5 [DML, SubQuery, UNION, CASE, TCL]

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

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

SELECT ENAME AS "사원명", DEPTNO AS "부서번호",
DECODE(DEPTNO,10,'Accounting',20,'Research',30,'Sale') AS "부서명" FROM EMP;

SELECT SUM(DECODE(DEPTNO,10,1,0)) FROM EMP;
SELECT DECODE(DEPTNO,10,1) FROM EMP; -- 지정 안해주면 NULL 나옴

SELECT COUNT(DEPTNO) FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING DEPTNO = 10);

SELECT SUM(DECODE(DEPTNO,10,1,0)) FROM EMP
UNION
SELECT SUM(DECODE(DEPTNO,20,1,0)) FROM EMP
UNION
SELECT SUM(DECODE(DEPTNO,30,1,0)) FROM EMP; -- 중복 제거됨

SELECT SUM(DECODE(DEPTNO,10,1,0)) FROM EMP
UNION ALL
SELECT SUM(DECODE(DEPTNO,20,1,0)) FROM EMP
UNION ALL
SELECT SUM(DECODE(DEPTNO,30,1,0)) FROM EMP; -- 중복 값 출력됨

SELECT EMPNO, ENAME ,JOB, SAL,
        CASE JOB
        WHEN 'MANAGER' THEN SAL*1.1
        WHEN 'SALESMAN' THEN SAL*1.05
        WHEN 'ANALYST' THEN SAL
        ELSE SAL*1.03
        END AS UPSAL 
FROM EMP;
        
SELECT SUM(COMM) FROM EMP;

SELECT EMPNO,ENAME,COMM,
    CASE
        WHEN COMM IS NULL THEN '해당사항없음'
        WHEN COMM = 0 THEN '수당없음'
        WHEN COMM > 0 THEN '수당: '||COMM
    END AS COMM_TEXT
FROM EMP;

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

INSERT INTO DEPT_TEMP(DEPTNO,DNAME, LOC) VALUES ( 50,'DATABASE','SEOUL');

INSERT INTO DEPT_TEMP VALUES(60,'NETWORK','BUSAN');

INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC) VALUES(70,'WEB',NULL);    

INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC) VALUES(80,'MOBILE','');

INSERT INTO DEPT_TEMP(DEPTNO,LOC) VALUES(90,'INCHEON');

CREATE TABLE EMP_TEMP AS SELECT * FROM EMP WHERE 1<>1;
SELECT * FROM EMP_TEMP;
INSERT INTO EMP_TEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
        VALUES(9999,'홍길동','PRESIDENT',NULL,'2001/01/01',5000,1000,10);
INSERT INTO EMP_TEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
        VALUES(1111,'성춘향','MANAGER',9999,'2001-01-02',4000,NULL,20); --날짜형 데이터 구분 /,- 해도 /로 강제 변환되서 나온다.

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

UPDATE DEPT_TEMP2 SET LOC = 'SEOUL'; -- UPDATE 는 WHERE가 필수
ROLLBACK;

UPDATE DEPT_TEMP2 SET DNAME = 'DATABASE', LOC = 'SEOUL' WHERE DEPTNO = 40;

CREATE TABLE EMP_TEMP2 AS SELECT * FROM EMP;]
SELECT * FROM EMP_TEMP2;

DELETE FROM EMP_TEMP2 WHERE JOB='MANAGER';
ROLLBACK;

DELETE FROM EMP_TEMP2;

SELECT DEPTNO, COUNT(EMPNO),SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO) >=6;
SELECT JOB,SUM(NVL2(COMM,SAL+COMM,SAL)) AS "급여(수당포함)합계" FROM EMP WHERE NVL2(COMM,SAL+COMM,SAL) > 900 GROUP BY JOB;
SELECT LOC,COUNT(EMPNO) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY LOC HAVING COUNT(EMPNO) <= 5;

SELECT MGR,COUNT(EMPNO) FROM EMP GROUP BY MGR;
SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY MGR;

--EMP 테이블에서 가장 많은 부하사원을 갖는 사원의 사원 번호를 출력
SELECT MGR FROM EMP GROUP BY MGR HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY MGR);
SELECT EMPNO FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP GROUP BY MGR HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY MGR));

SELECT * FROM EMP E1, EMP E2 WHERE E1.MGR = E2.EMPNO;

--상사 이름
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME FROM EMP E1,EMP E2 WHERE E1.MGR = E2.EMPNO(+) ORDER BY E1.EMPNO;

SELECT E1.ENAME,E1.SAL FROM EMP E1, EMP E2 WHERE E1.MGR = E2.EMPNO(+) AND E2.ENAME ='KING';
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE EMPNO = 7566);

CREATE TABLE DEPT_TCL AS SELECT * FROM DEPT;
INSERT INTO DEPT_TCL VALUES ( 50, 'DATABASE','SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

SELECT * FROM DEPT_TCL;
ROLLBACK;

INSERT INTO DEPT_TCL VALUES(50,'NETWORK','SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 20;
DELETE FROM DEPT_TCL WHERE DEPTNO = 40;

COMMIT;
ROLLBACK; -- 커밋 후에는 롤백안됨

SELECT INITCAP(ENAME) FROM EMP WHERE ENAME LIKE 'J%' OR ENAME LIKE 'A%';
SELECT LOWER(ENAME) FROM EMP WHERE LENGTH(ENAME) >= 6;
SELECT RPAD(ENAME,2) FROM EMP WHERE LENGTH(ENAME) > = 6;
SELECT ENAME,HIREDATE FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'BLAKE') AND ENAME != 'BLAKE';
SELECT EMPNO,ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
SELECT DNAME, COUNT(EMPNO) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY DNAME HAVING COUNT(ENAME) >= 3;
SELECT EMPNO,ENAME,NVL2(COMM,SAL+COMM,SAL) AS "총 급여",COMM FROM EMP 
WHERE NVL2(COMM,COMM+SAL,SAL) > (SELECT AVG(SAL) FROM EMP);