오라클 sql Practice4 [DML, AVG, <>, ^= ]

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

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

SELECT * FROM EMP;
SELECT * FROM DEPT;

CREATE TABLE MEMBER(ID NUMBER(5), NAME VARCHAR2(10));

INSERT INTO MEMBER(ID, NAME) VALUES(1000,'KIM');
INSERT INTO MEMBER(ID, NAME) VALUES(1001,'YI');
INSERT INTO MEMBER(ID, NAME) VALUES(1002,'KANG');

DELETE FROM MEMBER WHERE ID = 1002;
DROP TABLE MEMBER;

CREATE TABLE BOARD ( NO NUMBER(5), SUBJECT VARCHAR2(20), NAME VARCHAR2(10));

INSERT INTO BOARD (NO, SUBJECT, NAME) VALUES(1,'abc','KIM');
INSERT INTO BOARD (NO, SUBJECT, NAME) VALUES(2,'def','KIM');
INSERT INTO BOARD (NO, SUBJECT, NAME) VALUES(3,'GHI','YI');

SELECT *FROM MEMBER M, BOARD B WHERE M.NAME = B.NAME;
SELECT M.ID,M.NAME,B.NO,B.SUBJECT,B.NAME FROM MEMBER M, BOARD B WHERE M.NAME = B.NAME;

--OUTER JOIN: 관계가 없는 것 까지 포함해서 조인 결과를 나타냄
SELECT ID, M.NAME, NO, SUBJECT, B.NAME FROM MEMBER M, BOARD B WHERE M.NAME = B.NAME(+);

SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO = 10 OR DEPTNO = 20 ORDER BY ENAME;
SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO IN (10, 20) ORDER BY ENAME;
SELECT DEPTNO,COUNT(EMPNO),TRUNC(AVG(SAL),2), MIN(SAL),MAX(SAL),SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY SUM(SAL) DESC;
SELECT DEPTNO,TRUNC(AVG(SAL),2),SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) >= 3000;
SELECT ENAME,E.DEPTNO,DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND DNAME = 'RESEARCH';
SELECT ENAME, JOB, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND JOB = 'MANAGER';
SELECT ENAME,DNAME,LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND COMM IS NOT NULL AND SAL >= 1600;
SELECT ENAME,DNAME,LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND COMM > 0 AND SAL >= 1600;

SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');

SELECT* FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'MARTIN');

SELECT ENAME,SAL FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP WHERE DEPTNO =10);
SELECT EMPNO,ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);

SELECT ENAME,HIREDATE FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO
AND DNAME = (SELECT DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND ENAME = 'BLAKE')
AND ENAME ^= 'BLAKE';
SELECT ENAME,SAL FROM EMP WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING');
SELECT ENAME,SAL,LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO
AND SAL > (SELECT AVG(SAL) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO
GROUP BY LOC HAVING LOC = 'CHICAGO');
SELECT ENAME, SAL, LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO
AND SAL > (SELECT AVG(SAL) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND LOC = 'CHICAGO');
SELECT ENAME,SAL FROM EMP WHERE SAL < (SELECT AVG(SAL) FROM EMP);
SELECT DEPTNO, COUNT(EMPNO) FROM EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO) = (SELECT MAX(COOUNT(EMPNO)) FROM EMP GROUP BY DEPTNO);
SELECT DEPTNO AS 부서번호, COUNT(EMPNO) AS 사원수 FROM EMP GROUP BY DEPTNO;
SELECT EMPNO,ENAME FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10) AND DEPTNO <> 10;
SELECT DNAME, COUNT(EMPNO) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY DNAME HAVING COUNT(EMPNO) > 3;
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'JAMES');
SELECT ENAME,NVL2(COMM,COMM+SAL,SAL) AS 총급여 FROM EMP WHERE NVL2(COMM,SAL+COMM,SAL) > (SELECT AVG(SAL) FROM EMP);
SELECT ENAME,SAL+NVL(COMM,0) AS 총급여 FROM EMP WHERE SAL + NVL(COMM,0) > (SELECT AVG(SAL) FROM EMP);SQL