오라클 sql Practice3 [TO_CHAR, SUM, MAX, NVL]

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

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

SELECT SYSDATE FROM DUAL;

SELECT HIREDATE FROM EMP;

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간 FROM DUAL;

SELECT SYSDATE,TO_CHAR(SYSDATE,'MM') AS MM,
TO_CHAR(SYSDATE,'MON') AS MON,
TO_CHAR(SYSDATE,'MONTH') AS MONTH,
TO_CHAR(SYSDATE,'DD') AS DD,
TO_CHAR(SYSDATE,'DY') AS DY,
TO_CHAR(SYSDATE,'DAY') AS DAY FROM DUAL;

SELECT SYSDATE,TO_CHAR(SYSDATE,'MM') AS MM,
TO_CHAR(SYSDATE,'DD') AS DD,
TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=KOREAN') AS DY_KOR,
TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=JAPANESE') AS DY_JAN,
TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=ENGLISH') AS DY_ENG,
TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=KOREAN') AS DAY_KOR,
TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=JAPANESE') AS DAY_JAN,
TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=ENGLISH') AS DAY_ENG FROM DUAL;

SELECT ENAME,TO_CHAR(SAL,'$999,999') FROM EMP;
SELECT ENAME,TO_CHAR(SAL,'L0,999') FROM EMP;

SELECT TO_CHAR(SAL,'9,999.00') FROM EMP;
SELECT TO_CHAR(SAL,'9,999.99') FROM EMP;

SELECT 1300-'1500' FROM DUAL;
SELECT '1300'-'1500' FROM DUAL;
SELECT '1,300'-'1,500' FROM DUAL;

SELECT TO_NUMBER('1,300','999,999') - TO_NUMBER('1,500','999,999') FROM DUAL;

SELECT SYSDATE-10 FROM DUAL;

SELECT TO_DATE('2018-07-14','YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('20180714','YYYY-MM-DD') FROM DUAL;

SELECT * FROM EMP WHERE HIREDATE > TO_DATE('1981/06/01','YYYY/MM/DD');
SELECT * FROM EMP WHERE HIREDATE > '1981/06/01'; --자동으로 날짜형 데이터로 형 변환이 이루어짐

DESC EMP;

SELECT EMPNO,ENAME,SAL,COMM,SAL+COMM,NVL(COMM,0),SAL+NVL(COMM,0) FROM EMP;

SELECT EMPNO,ENAME,SAL,COMM,NVL2(COMM,'O','X'),NVL2(COMM,SAL*12+COMM,SAL*12) AS ANNSAL FROM EMP;

--다중행함수 = 집계함수 = 그룹함수
SELECT SUM(SAL),COUNT(SAL),MAX(SAL),MIN(SAL),TRUNC(AVG(SAL),2) FROM EMP;

SELECT COUNT(*) FROM EMP;
SELECT COUNT(EMPNO) FROM EMP;
SELECT COUNT(DISTINCT DEPTNO) FROM EMP;

SELECT * FROM DEPT;

SELECT COUNT(*) FROM EMP WHERE DEPTNO=30;

SELECT COUNT(COMM) FROM EMP;
SELECT COUNT(COMM) FROM EMP WHERE COMM IS NOT NULL;

SELECT MAX(SAL) FROM EMP WHERE DEPTNO =10;

SELECT MAX(HIREDATE) FROM EMP WHERE DEPTNO=20;

SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30;
SELECT TRUNC(AVG(SAL)) FROM EMP WHERE DEPTNO = 30;

SELECT DEPTNO,TRUNC(AVG(SAL),2) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO ASC;
-- ORDER BY는 항상 맨뒤
/*
1.FROM
2.WHERE
3.GROUP BY
4.ORDER BY
5.SELECT
*/

SELECT *FROM EMP,DEPT ORDER BY EMPNO;

--VARCHAR(10) = 가변형 문자열 최대 글자 길이 10
CREATE TABLE EMP_T1(EMPNO NUMBER(4),ENAME VARCHAR2(10));
INSERT INTO EMP_T1(EMPNO,ENAME) VALUES(111,'KIM');
INSERT INTO EMP_T1(EMPNO,ENAME) VALUES(2222,'YI');

CREATE TABLE DEPT_T1(DEPTNO NUMBER(4),LOC VARCHAR2(10));
INSERT INTO DEPT_T1(DEPTNO,LOC) VALUES(10,'LA');
INSERT INTO DEPT_T1(DEPTNO,LOC) VALUES(20,'NEW YORK');

SELECT * FROM EMP_T1,DEPT_T1;

SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT. DEPTNO ORDER BY EMPNO;

SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;
--등가 조인(=INNER JOIN)

SELECT EMPNO,ENAME,D.DEPTNO,DNAME,LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

SELECT E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME,D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND SAL >= 3000;

SELECT EMPNO,DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO =10;

SELECT DEPTNO,JOB,AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO,JOB;

SELECT DEPTNO,TRUNC(AVG(SAL),2) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >=2000;
--DEPTNO(부서번호)로 그룹화 한 후 각 부서 별 평균 급여가 2000이상

SELECT DEPTNO,MAX(SAL),MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) >=2900;

SELECT TO_CHAR(SAL,'$999,999') FROM EMP WHERE DEPTNO=10;
SELECT COUNT(EMPNO),COUNT(COMM),AVG(COMM),COUNT(DISTINCT DEPTNO) FROM EMP;
SELECT DEPTNO,COUNT(EMPNO) FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO) > 4;
SELECT DEPTNO,TRUNC(AVG(SAL),2) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) >= 2000;
SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL) > 3000;
SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE '%A%';

SELECT ENAME,DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND JOB = 'CLERK';
SELECT EMPNO,DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND ENAME = 'SMITH';