1981 년 5월 31일 이후 입사자 중
커미션이 NULL이거나 0인 사원의
커미션은 500으로, 그렇지 않으면 COMM을 출력하시오.
SELECT
CASE
WHEN
NVL(COMM,0) = 0
THEN
500
ELSE
COMM
END as COMM
FROM
EMP
WHERE
HIREDATE >= TO_DATE('1981/05/31');- 원본 컬럼은 변경하면 안된다.
1980년도에 입사한 사원의 수를 ‘입사1980’ 컬럼으로 뽑아주시고,
그외 인원들을 ‘그외’ 컬럼으로 뽑아주세요
SELECT
(SELECT
COUNT(EMPNO)
FROM
EMP
WHERE
HIREDATE >= TO_DATE('1980/01/01')
AND
HIREDATE <= TO_DATE('1981/01/01')
) as 입사1980,
(SELECT
COUNT(EMPNO)
FROM
EMP
WHERE
HIREDATE < TO_DATE('1980/01/01')
OR
HIREDATE > TO_DATE('1981/01/01')
) as 그외
FROM
DUAL;- 날짜 비교는
**00:00:00**까지 하므로,**1981/12/31**까지로 계산하면 안됨. - 딱 한 라인의 튜플만 뽑으면 되므로, ‘
**DUAL**’을 사용해야한다.
가장 높은 급여를 받는 사원보다 입사일이 늦은
사원의 이름, 입사일을 출력
SELECT
ENAME,
HIREDATE
FROM
EMP
WHERE
HIREDATE > (
SELECT
HIREDATE
FROM
EMP
WHERE
SAL = (
SELECT
MAX(SAL)
FROM
EMP
)
);- 서브쿼리는
**Bottom-Up**방식으로 쌓아올리는 것이 좋다.
FORD 보다 입사일이 늦은 사원 중
급여가 가장 높은 사원의 이름과 급여를 출력
-- FORD 보다 입사일이 늦은 사원 중
-- 급여가 가장 높은 사원의 이름과 급여를 출력
SELECT
ENAME,
SAL
FROM
(SELECT
ENAME,
SAL
FROM
((SELECT
ENAME,
SAL
FROM
EMP
WHERE
HIREDATE > (
SELECT
HIREDATE
FROM
EMP
WHERE
ENAME = 'FORD'
)))
ORDER BY
SAL DESC)
WHERE
ROWNUM <= 1;
-- 이 쿼리의 오류는 무엇일까?
-- 최종 메인 쿼리에서 'FORD'의 입사일과 관련이 없는 결과 데이터가 나온다.
SELECT
ENAME,
SAL
FROM
EMP
WHERE
SAL = (SELECT
MAX(SAL)
FROM
EMP
WHERE
HIAREDATE > (SELECT
HIREDATE
FROM
EMP
WHERE
ENAME = 'FORD'));20번 부서의 최고 급여보다 많은 급여를 받는 사원의
사원번호, 사원명, 급여를 출력
-- 20번 부서의 최고 급여보다 많은 사원의 사원번호,사원명,급여를 출력
SELECT
EMPNO,
ENAME,
SAL
FROM
EMP
WHERE
SAL > (
SELECT
MAX(SAL)
FROM
EMP
WHERE
DEPTNO = 20
);EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원 번호 출력
( 어제 가장 어려웠던 쿼리. 이건 지속적으로 복습할 필요가 있다. )
-- EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호 출력
SELECT
MGR
FROM
EMP
GROUP BY
MGR
HAVING
COUNT(MGR) = (
SELECT
MAX(COUNT(MGR))
FROM
EMP
GROUP BY
MGR);TOP N Query - using ROWNUM
- 상위 N개의 데이터를 추출하는 쿼리
1) Oracle
**ROWNUM**Pseudo Column**ROWNUM**으로 Top N 쿼리를 만드려면 인라인 뷰 사용
-- WHERE 에서 RNUM 사용하려면?
-- FROM 절에서부터 가져온 새로운 테이블의 ROWNUM을 참조할 수 있어야 한다.
-- 그렇기에, 새로 생성된 테이블의 ROWNUM을 참조하기 위해
-- SELECT 문으로 래핑해주어야 한다.
-- TOP ROWNUM은 1부터만 시작해야한다.
-- 내부 인라인 뷰의 ROWNUM 은 1이 아닌 시점부터 시작 가능하다.
SELECT
ENAME,
SAL
FROM(
SELECT
ENAME,
SAL,
ROWNUM as RNUM
FROM
(SELECT
ENAME,
SAL
FROM
EMP
ORDER BY
SAL DESC))
WHERE
RNUM = 2;- 서브쿼리로 할 수 있는 것은
‘가장 많은’,‘가장 적은’ 처음부터 몇위까지,
맨 하위 10명까지.. 라는 쿼리는 ROWNUM ( FROM 문 안의 ROWNUM )을 사용해주어야 구할 수 있다.
추가 - ROW_NUMBER() OVER()
-- 추가 ( Optional )
-- ROW_NUMBER() OVER as RNUM
-- 오라클에서는 누계함수가 막강하다.
SELECT
ROW_NUMBER() OVER(ORDER BY e.sal) AS RNUM,
e.*
FROM
EMP e
ORDER BY e.SAL;
-- JOB으로 그룹화하고, SAL과 ENAME으로 정렬하라.
SELECT
ROW_NUMBER() OVER(PARTITION BY job ORDER BY e.sal, e.ename) as snum,
e.*
FROM
EMP e;오라클 누계 함수, 오라클 통계 함수
Oracle Index Hint
- 오라클은 DBA가 엄청 좋아하는 DB
- 오라클이 제공하는 엄청난 기능들
REGEXP_LIKE
- Regulation LIKE
- ‘이름 전체에 숫자가 들어간 사람들 다 뽑아’
- 정규 표현식
- ^ : 시작
- $ : 끝
- REGEXP_LIKE(str,‘[0-9]’)
- ^A.+S’ ⇒
**정규표현식**예시- ^ 로 시작, $ 로 끝
- A.+S
정규표현식매우 중요, 전 세게에서 이 기호는 항상 쓰임 ( 전 세계 통용되는 문법 )
입사일이 두 번째로 빠른 사람의 부서명과 이름, 입사일 출력
-- 입사일이 두 번째로 빠른 사람의 부서명과 이름, 입사일
SELECT
dname,
ename,
hiredate
FROM
(SELECT
ROWNUM as rnum,
ee.*
FROM (
SELECT
d.dname,
e.ename,
e.hiredate
FROM
EMP e
JOIN
DEPT d
ON
e.deptno = d.deptno
ORDER BY
hiredate ASC) ee)
WHERE
rnum = 2;**ROWNUM**은 테이블에 INSERT 된 순서를 나타낸다.**Inline-VIew**에서 INSERT된 순서 또한**ROWNUM**의 순서로 나타낼 수 있다.
-- DALLAS에 위치한 부서에
-- 최대 급여를 받는 사원에서
-- 최소 급여를 받는 사람의 급여를 뺀 급여값 출력
SELECT
MAX(SAL) - MIN(SAL) as result
FROM
EMP e
JOIN
DEPT d
ON
e.deptno = d.deptno
WHERE
d.loc = 'DALLAS';CASE Tool
- 프로그램을 개발하는데 사용되는 자동화 도구
- IDE를 통해 클래스다이어그램, ER Diagram ( ERD Cloud )
- 모델링에 주로 사용
- CASE 툴 써봤나요? →
ERD Cloud써서 모델링 하면 됨.
ER - Diagram
논리적 모델 + 물리적 모델
- Logical ⇒ SW적
- Physical ⇒ 컴퓨터에 만들어질 수 있는 상태
개념적 모델
논리적 모델
물리적 모델
- 논리, 물리 모델 동시 설계함.
식별 관계와 비식별 관계
실선 ⇒ FK 가 나의 PK ( 남에게서 가져온 PK가 나의 PK라면 )
- 종속적인 관계 ( 식별자 )
- FK가 아래 가있으면,
슈퍼타입과 서브타입
- 공통과 각각의 부분으로 나눈다.
- 사원 ← 정규직, 임시직 ( 반달 모양의 X )
