TO_CHAR(날짜, 형식): 날짜를 형식의 문자열로 변환 TO_DATE(문자열,형식): 문자열을 형식의 날짜로 변환
예제
--2. EMP 테이브 모든 정보 확인
SELECT * FROM EMP;
--3.EMP테이블의 사원번호, 사원명 확인
SELECT EMPNO,ENAME
FROM EMP;
--4.EMP 테이블에서 사원이 있는 부서번호 확인
SELECT DEPTNO
FROM EMP;
--5. EMP 테이블에서 사원이 있는 부서번호 확인(중복제거)
SELECT DISTINCT DEPTNO
FROM EMP;
--6.사원이 없는 부서번호 확인
--#1. (전체부서에서) - (사원있는 부서)
SELECT DEPTNO
FROM DEPT
MINUS
SELECT DEPTNO
FROM EMP;
--#2.NOT IN 활용하기
SELECT DEPTNO
FROM DEPT
WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
--#3.직무별, 부서번호별 중복된 값을 제거하고 출력
SELECT DISTINCT JOB ,DEPTNO
FROM EMP;
--7.급여순으로 사원정보 출력
SELECT *
FROM EMP
ORDER BY SAL;
--8. 입사일 순으로 사원번호, 사원명 검색
SELECT EMPNO,ENAME
FROM EMP
ORDER BY HIREDATE;
--9. 사원명 순으로 사원번호, 사원명 검색
SELECT EMPNO, ENAME
FROM EMP
ORDER BY ENAME;
--10.최근 입사한 사람순으로 사원정보 검색
SELECT *
FROM EMP
ORDER BY HIREDATE DESC;
--11.급여 최고 많은 사람부터 적은 순으로 (급여 역순) 사원정보 검색
SELECT *
FROM EMP
ORDER BY SAL DESC;
--12. NULL은 정렬시키면 먼저?또는 늦게?
--NULL은 정렬시키면 제일 늦게나옴 역순으로 하면 제일 먼저 나오게 됨
--관리자번호 순으로 사원 정보 검색
SELECT *
FROM EMP
ORDER BY MGR DESC;
--13.연봉이 제일 많은 순으로 사원번호, 사원명, 급여, 보너스, 연봉(SAL*12 +COMM)
-- =>보너스가 NULL이면 0으로 처리
SELECT EMPNO,ENAME,SAL,COMM, SAL *12 +NVL(COMM,0) AS 연봉
FROM EMP
ORDER BY 연봉 DESC;
--14.사원정보를 검색하되 부서번호 순, 사원번호순으로 검색
SELECT *
FROM EMP
ORDER BY DEPTNO,EMPNO;
--15.ORDER BY와 SELECT에 나오는 속성은 관련이 없다
SELECT EMPNO,ENAME
FROM EMP
ORDER BY EMPNO ASC;
--16.사원명과 급여를 출력하되 급여는 'SALARY'라고 제목을 변경해서 출력하기
SELECT ENAME,SAL AS SALARY
FROM EMP;
--17. 사원명과 급여를 출력하되 급여는 'S a l a r y'라고 제목을 변경해서 출력하기
SELECT ENAME, SAL AS "S a l a r y"
FROM EMP;
--18.사원명과 급여를 출력하되 급여는 'Sal#'라고 제목을 변경해서 출력하기
SELECT ENAME, SAL AS "Sal#" FROM EMP;
--19.부서번호가 10번인 사원의 사원번호, 사원명, 부서번호 출력
SELECT EMPNO,ENAME,DEPTNO
FROM EMP
WHERE DEPTNO =10;
--20.사원번호, 급여, 급여의 10%인상된 금액
SELECT EMPNO,SAL, SAL *1.1 AS "10%인상된 급여"
FROM EMP;
--21.사원번호, 급여, 보너스, 급여 +보너스 별칭 =>급여 +보너스
SELECT EMPNO,SAL, COMM, SAL+NVL(COMM,0) AS "급여+보너스"
FROM EMP;
--22.사원번호, 급여에 100을 더한 금액의 두배 출력
SELECT EMPNO, 2*(SAL+100)
FROM EMP;
--23. 부서번호가 20번 미만인 사원들의 정보
SELECT *
FROM EMP
WHERE DEPTNO <20;
--24. 사원번호가 7934인 사원정보
SELECT *
FROM EMP
WHERE EMPNO = 7934;
--25.사원명이 BLAKE인 사원의 번호와 사원명
SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME = 'BLAKE';
--26.입사일인 82/01/23인 사원의 정보
SELECT *
FROM EMP
WHERE HIREDATE = '82/01/23';
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE, 'RR/MM/DD')= '82/01/23';
SELECT *
FROM EMP
WHERE TO_DATE('82/01/23', 'RR/MM/DD') = HIREDATE;
--27.현재 데이터베이스의 날짜 타입을 확인
SELECT VALUE FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';
--28. 부서번호 10번이고 급여가 3000미만인 사원정보
SELECT *
FROM EMP
WHERE DEPTNO =10 AND SAL < 3000;
--29 부서번호 10번이거나 급여가 3000미만인 사원정보
SELECT *
FROM EMP
WHERE DEPTNO =10 OR SAL <3000;
--30.직무가 SALESMAN이 아닌 사원정보
SELECT *
FROM EMP
WHERE JOB != 'SALESMAN';
--31.부서번호가 10,20,30인 사원번호, 사원명
SELECT EMPNO,ENAME
FROM EMP
WHERE DEPTNO IN (10,20,30);
--32. 이름이 A로 시작하는 사원명과 급여
SELECT ENAME,SAL
FROM EMP
WHERE ENAME LIKE 'A%';
--33.이름이 T로 끝나는 사원명과 부서번호
SELECT ENAME, DEPTNO
FROM EMP
WHERE ENAME LIKE '%T';
--34.사원명 6글자 중 5자가 MILLE이고 마지막 글자를 모르는 경우 사원정보 검색
SELECT *
FROM EMP WHERE ENAME LIKE 'MILLE_';
--35.급여가 2000에서 3000사이인 사원정보 (BETWEEN A AND B)또는 >= OR/AND <= 사용
SELECT *
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;
SELECT *
FROM EMP
WHERE SAL >= 2000 AND SAL<= 3000;
--36.보너스가 NULL인 사원정보
SELECT *
FROM EMP
WHERE COMM IS NULL;
--37.보너스가 NULL이 아닌 사원의 번호와 사원명과 급여를 출력하되
--EX)SMITH의 급여는 800입니다.식으로 출력하시오
SELECT ENAME || '의 급여는' || SAL || '입니다'
FROM EMP
WHERE COMM IS NOT NULL;
--38.연산자의 우선순위는 AND, OR, NOT, (), 비교 연산자, 산술(+-/*), 관계(>,<,>=,<=),논리
() -> 비교연산자 -> NOT ,AND, OR ;
--39.직무가 PRESIDENT이고 급여가 2000이상이거나 직무가 SALESMAN인 사원정보
SELECT *
FROM EMP
WHERE JOB ='PRESIDENT' AND SAL>=2000 OR JOB='SALESMAN';
--40. 직무는 PRESIDENT이거나 SLAESMAN이고 둘다 급여는 2000이상인 사원정보
SELECT *
FROM EMP
WHERE (JOB ='PRESIDENT' OR JOB='SALESMAN') AND SAL>=2000;
--41.사원테이블의 부서번호와 부서테이블의 부서번호를 UNION 해서 출력(UNION은 중복제거)
SELECT DEPTNO
FROM EMP
UNION
SELECT DEPTNO
FROM DEPT;
--42.사원테이블의 부서번호와 부서테이블의 부서번호를 UNION 해서 출력(UNION ALL은 중복제거)
SELECT DEPTNO
FROM EMP
UNION ALL
SELECT DEPTNO
FROM DEPT;
--43.DEPT테이블과 EMP 테이블 모두에 존재하는 부서번호(INTERESCT-교집합)
SELECT DEPTNO
FROM DEPT
INTERSECT
SELECT DEPTNO
FROM EMP;
--44.KOREA 문자열에서 REA 추출하기(SUBSTR(문자열,시작위치,갯수)
SELECT SUBSTR('KOREA',3,3) FROM DUAL;
--45. KOREA 문자열의 글자수 구하기-LENGTH(문자열)
SELECT LENGTH('KOREA') FROM DUAL;
--46. 456.789 소수이하 없애기-TRUNC(숫자, 만들자릿수) -0: 소수이하 없이, 1: 소수이하 한자리
SELECT TRUNC(456.789) FROM DUAL;
--47. 456.789 소수이하 두번째 자리에서 반올림해서 소수이하 한자리 만들기-ROUND(숫자, 만들자릿수)
SELECT ROUND(456.789,1) FROM DUAL;
--48 -45.7을 양수로 변환 - ABS(숫자)
SELECT ABS(-45.7) FROM DUAL;
--49 오늘 날짜 출력
SELECT SYSDATE FROM DUAL;
--50 사원들의 입사일과 입사일로부터 한달이 지난 날짜-ADD_MONTHS(날짜, 숫자)
SELECT HIREDATE, ADD_MONTHS(HIREDATE,1) FROM EMP;
--51 입사일의 마지막 날짜
SELECT HIREDATE,LAST_DAY(HIREDATE)
FROM DUAL;
--52 부서 갯수
SELECT COUNT(*)
FROM DEPT;
--53 사원의 수
SELECT COUNT(*)
FROM EMP;
--54 최고 급여, 최소 급여, 평균 급여
SELECT MAX(SAL),MIN(SAL),ROUND(AVG(SAL),2)
FROM EMP;
--55.부서별 최고급여, 최소 급여, 평균 급여, 급여 합계
SELECT MAX(SAL), MIN(SAL), AVG(SAL), SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;
--56.부서별 최고급여, 최소급여, 평균급여, 급여 합계를 구하되 부서별 인원수가 2명 이상인 부서만 출력(모았을때 결과가 필요하면 HAVING)
SELECT MAX(SAL), MIN(SAL), AVG(SAL), SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=2;
--57.부서별 최고 급여, 최소 급여, 평균 급여,급여 합계를 구하되
--사원의 급여가 2000이상인 사원에 대해서만 검색
SELECT MAX(SAL), MIN(SAL), AVG(SAL), SUM(SAL)
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO;
--58.직무가 ANALYST이면 급여의 10%인상된 금액, CLERK이면 15%인상된 금액, MANAGER이면 20%인상된 금액
--그 외는 급여 그대로 출력하기 DECODE(대상, 조건 1ㄱ밧, 값1, 조건2값, 값2, 조건3값, 값3...해당 되는 것 없을 때 값)
SELECT JOB,SAL,DECODE(JOB,'ANALYST',SAL*1.1, 'CLERK', SAL*1.15, 'MANAGER', SAL*1.2,SAL) SALARY
FROM EMP;
--59.부서별, 직무별 인원과 평균 급여액의 12개월치
SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), COUNT(*) , AVG(SAL)*12
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
--60.부서별, 직무별 인원과 평균 급여액의 12개월치와 전체 집계 정보 포함
SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), COUNT(*) , AVG(SAL)*12
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);
--61 전체 평균 급여보다 더 많이 받는 사원의 정보
SELECT *
FROM EMP
WHERE SAL > ( SELECT AVG(SAL) FROM EMP);
--62 SCOTT의 부서에 속하는 사원들의 정보
SELECT *
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT');
--63 SCOTT의 부서번호와 부서명, 부서위치
SELECT DEPTNO, DNAME, LOC
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT');
--64.SCOTT의 사원번호, 사원명,부서번호, 부서명, 부서위치 출력
SELECT EMPNO, ENAME, D.DEPTNO, DNAME, LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND ENAME ='SCOTT';
--65. 급여 제일 많은 3명의 정보 출력하기(ROWNUM 사용, ORDER BY 사용)INLINE VIEW사용해야함
SELECT *
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <=3
ORDER BY SAL DESC ; --X 위의 세개를 뽑은 후 정렬
SELECT *
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <=3; --급여 역순으로 정렬한 후 위의 세개 선택
--66. 모든 부서정보는 다 나오고(즉, 해당 부서에 소속된 사원이 없더라도) 해당 부서의 사원번호, 사원명, 급여 출력하기
SELECT D.*, EMPNO, ENAME, SAL
FROM DEPT D LEFT OUTER JOIN EMP E
ON (D.DEPTNO =E.DEPTNO);
-- SQL-99 이전 방식
SELECT D.*, EMPNO, ENAME, SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO =E.DEPTNO(+);
-- (+)없는 쪽이 기준이 되고 (+) 나오는 쪽에 짝이 없는 애들을 NULL로 채워라.
--67. 사원명이 SMITH인 사원이 속한 부서의 급여 평균 구하기
SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME ='SMITH');
--68. 각 부서별 최고 급여 받는 사원정보 출력
SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO;
