SELECT COUNT(*) AS COUNT(*)
, COUNT(COMM) AS COUNT(COMM)
FROM EMP;
SELECT SUM(COMM) AS SUM(COMM)
, COUNT(COMM) AS COUNT(COMM)
FROM EMP;
SELECT AVG(COMM) AS AVG
, SUM(COMM) AS SUM
, COUNT(COMM) AS COUNT
FROM EMP;
SELECT MAX(SAL) AS MAX
, MIN(SAL) AS MIN
FROM EMP;
SELECT STDDEV(SAL) AS STDDEV
, VARIANCE(SAL) AS VARIANCE
FROM EMP;
SELECT DEPTNO AS DEPTNO
, AVG(NVL(SAL,0)) AS AVG
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO AS DEPTNO
, JOB AS JOB
, AVG(NVL(SAL,0)) AS AVG
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY 1,2;
SELECT DEPTNO AS DEPTNO
, JOB AS JOB
, AVG(NVL(SAL,0)) AS AVG
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO AS DEPTNO
, JOB AS JOB
, AVG(NVL(SAL,0)) AS AVG
FROM EMP
GROUP BY NO;
SELECT DEPTNO AS DEPTNO
, AVG(NVL(SAL,0)) AS AVG
FROM EMP
GROUP BY NO;
SELECT DEPTNO AS DEPTNO
, AVG(NVL(SAL,0)) AS AVG
FROM EMP
WHERE DEPTNO > 10
GROUP BY DEPTNO AVG(NVL(SAL,0)) > 2000;
SELECT DEPTNO AS DEPTNO
, POSITION AS POSITION
, COUNT(*) AS COUNT
, SUM(PAY) AS PAY
FROM PROFESSOR
GROUP BY POSITION, ROLLUP(DEPTNO);
SELECT DEPTNO AS DEPTNO
, POSITION AS POSITION
, COUNT(*) AS COUNT
, SUM(PAY) AS PAY
FROM PROFESSOR
GROUP BY DEPTNO, ROLLUP(POSITION);
SELECT DEPTNO AS DEPTNO
, POSITION AS POSITION
, SUM(PAY) AS PAY
FROM PROFESSOR2
GROUP BY DEPTNO, ROLLUP(POSITION);
SELECT DEPTNO AS DEPTNO
, JOB AS JOB
, ROUND(AVG(SAL),1) AS ROUND
, COUNT(*) AS COUNT
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;
SELECT GRADE AS GRADE
, DEPTNO1 AS DEPTNO1
, COUNT(*) AS COUNT
, SUM(HEIGHT) AS SUM
FROM STUDENT
GROUP BY GROUPING SETS(GRADE, DEPTNO1);
SELECT DEPTNO AS DEPTNO
, LISTAGG(ENAME, '->') WITHIN GROUP(ORDER BY HIREDATE) AS LISTAGG
FROM EMP
GROUP BY DEPTNO;
SELECT DECODE(DAY, 'SUN', DAYNO) AS SUN
, DECODE(DAY, 'MON', DAYNO) AS MON
, DECODE(DAY, 'TUE', DAYNO) AS TUE
, DECODE(DAY, 'WED', DAYNO) AS WED
, DECODE(DAY, 'THU', DAYNO) AS THU
, DECODE(DAY, 'FRI', DAYNO) AS FRI
, DECODE(DAY, 'SAT', DAYNO) AS SAT
FROM CAL
GROUP BY WEEKNO
ORDER BY WEEKNO;
SELECT MAX(DECODE(DAY, 'SUN', DAYNO)) AS SUN
, MAX(DECODE(DAY, 'MON', DAYNO)) AS MON
, MAX(DECODE(DAY, 'TUE', DAYNO)) AS TUE
, MAX(DECODE(DAY, 'WED', DAYNO)) AS WED
, MAX(DECODE(DAY, 'THU', DAYNO)) AS THU
, MAX(DECODE(DAY, 'FRI', DAYNO)) AS FRI
, MAX(DECODE(DAY, 'SAT', DAYNO)) AS SAT
FROM CAL;
SELECT MAX(DECODE(DAY, 'SUN', DAYNO)) AS SUN
, MAX(DECODE(DAY, 'MON', DAYNO)) AS MON
, MAX(DECODE(DAY, 'TUE', DAYNO)) AS TUE
, MAX(DECODE(DAY, 'WED', DAYNO)) AS WED
, MAX(DECODE(DAY, 'THU', DAYNO)) AS THU
, MAX(DECODE(DAY, 'FRI', DAYNO)) AS FRI
, MAX(DECODE(DAY, 'SAT', DAYNO)) AS SAT
FROM CAL
GROUP BY WEEKNO;
SELECT MAX(DECODE(DAY, 'SUN', DAYNO)) AS SUN
, MAX(DECODE(DAY, 'MON', DAYNO)) AS MON
, MAX(DECODE(DAY, 'TUE', DAYNO)) AS TUE
, MAX(DECODE(DAY, 'WED', DAYNO)) AS WED
, MAX(DECODE(DAY, 'THU', DAYNO)) AS THU
, MAX(DECODE(DAY, 'FRI', DAYNO)) AS FRI
, MAX(DECODE(DAY, 'SAT', DAYNO)) AS SAT
FROM CAL
GROUP BY WEEKNO
ORDER BY WEEKNO;
SELECT *
FROM (SELECT WEEKNO
, DAY
, DAYNO
FROM CAL)
PIVOT (MAX(DAYNO) FOR DAY IN 'SUN' AS SUN
, 'MON' AS MON
, 'TUE' AS TUE
, 'WED' AS WED
, 'THU' AS THU
, 'FRI' AS FRI
, 'SAT' AS SAT)
)
ORDER BY "WEEK";
SELECT DEPTNO
, DECODE(JOB, 'CLERK', '9') AS CLERK
, DECODE(JOB, 'MANAGER', '9') AS MANAGER
, DECODE(JOB, 'PRESIDENT', '9') AS PRESIDENT
, DECODE(JOB, 'ANALYST', '9') AS ANALYST
, DECODE(JOB, 'SALEMAN', '9') AS SALEMAN
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO
, COUNT(DECODE(JOB, 'CLERK', '9')) AS CLERK
, COUNT(DECODE(JOB, 'MANAGER', '9')) AS MANAGER
, COUNT(DECODE(JOB, 'PRESIDENT', '9')) AS PRESIDENT
, COUNT(DECODE(JOB, 'ANALYST', '9')) AS ANALYST
, COUNT(DECODE(JOB, 'SALEMAN', '9')) AS SALEMAN
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO
SELECT DEPTNO
, COUNT(DECODE(JOB, 'CLERK', '0')) AS CLERK
, COUNT(DECODE(JOB, 'MANAGER', '0')) AS MANAGER
, COUNT(DECODE(JOB, 'PRESIDENT', '0')) AS PRESIDENT
, COUNT(DECODE(JOB, 'ANALYST', '0')) AS ANALYST
, COUNT(DECODE(JOB, 'SALEMAN', '0')) AS SALEMAN
FROM EMP
OROUP BY DEPTNO
ORDER BY DEPTNO
SELECT *
FROM (SELECT DEPTNO
, JOB
, EMPNO
FROM EMP)
PIVOT (COUNT(EMPNO) FOR JOB IN 'CLERK' AS CLERK
, 'MANAGER' AS MANAGER
, 'PRESIDENT' AS PRESIDENT
, 'ANALYST' AS ANALYST
, 'SALESMAN' AS SALESMAN
)
ORDER BY DEPTNO;
SELECT *
FROM (SELECT DEPTNO
, JOB
, EMPNO
, SAL
FROM EMP)
PIVOT (COUNT(EMPNO) AS COUNT
,SUM(NVL(SAL,0)) AS SUM
FOR JOB IN 'CLERK' AS CLERK
, 'MANAGER' AS MANAGER
, 'PRESIDENT' AS PRESIDENT
, 'ANALYST' AS ANALYST
, 'SALESMAN' AS SALESMAN
)
ORDER BY DEPTNO;
CREATE TABLE UPIVOT
AS SELECT *
FROM (SELECT DEPTNO
, JOB
, EMPNO
FROM EMP)
PIVOT (COUNT(EMPNO) FOR JOB IN 'CLERK' AS CLERK
, 'MANAGER' AS MANAGER
, 'PRESIDENT' AS PRESIDENT
, 'ANALYST' AS ANALYST
, 'SALESMAN' AS SALESMAN
)
ORDER BY DEPTNO;
SELECT *
FROM UPIVOT
UNPIVOT (EMPNO FOR IN (CLERK, MANAGER, PRESIDENT, ANALYST, SALESMAN));
SELECT ENAEM
, HIREDATE
, SAL
, LAG(SAL,1,0) OVER (ORDER BY HIREDATE) AS LAG
FROM EMP;
SELECT ENAEM
, HIREDATE
, SAL
, LAG(SAL,3,2) OVER (ORDER BY HIREDATE) AS LAG
FROM EMP;
SELECT ENAEM
, HIREDATE
, SAL
, LEAD(SAL,2,1) OVER (ORDER BY HIREDATE) AS LEAD
FROM EMP;
SELECT RANK('SMITH') WITHN GROUP (ORDER BY ENAME) AS RANK
FROM EMP;
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
, RANK() OVER (ORDER BY SAL) AS RANK_ASC
, RANK() OVER (ORDER BY SAL DESC) AS RANK_DESC
FROM EMP;
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
, RANK() OVER (ORDER BY SAL DESC) AS RANK
FROM EMP
WHERE DEPTNO = 10;
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
, DEPTNO AS DEPTNO
, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK
FROM EMP;
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
, DEPTNO AS DEPTNO
, RANK() OVER (PARTITION BY DEPTNO, JOB ORDER BY SAL DESC) AS RANK
FROM EMP;
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
, RANK() OVER (ORDER BY SAL DESC) AS RANK
, DENSE_RANK() OVER (ORDER BY SAL DESC) AS DENSE_RANK
FROM EMP;
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
, RANK() OVER (ORDER BY SAL DESC) AS RANK
, DENSE_RANK() OVER (ORDER BY SAL DESC) AS DENSE_RANK
, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS ROW_NUMBER
FROM EMP;
SELECT DEPTNO AS DEPTNO
, SAL AS SAL
, EMPNO AS EMPNO
, ROW_NUMBER() OVER (PARTITION BY SAL ORDER BY SAL) AS ROW_NUMBER1
, RANK() OVER (PARTITION BY SAL ORDER BY SAL) AS RANK1
, DENSE_RANK() OVER (PARTITION BY SAL ORDER BY SAL) AS DENSE_RANK1
, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL, EMPNO) AS ROW_NUMBER2
, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL, EMPNO) AS RANK2
, DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL, EMPNO) AS DENSE_RANK2
FROM EMP
WHERE DEPTNO IN ('10', '20')
ORDER BY DEPTNO, SAL, EMPNO;
SELECT P_DATE AS P_DATE
, P_CODE AS P_CODE
, P_QTY AS P_QTY
, P_TOTAL AS P_TOTAL
, SUM(P_TOTAL) OVER(ORDER BY P_TOTAL) AS TOTAL
FROM PAAMAE
WHERE P_STORE = 1000;
SELECT P_DATE AS P_DATE
, P_CODE AS P_CODE
, P_QTY AS P_QTY
, P_TOTAL AS P_TOTAL
, SUM(P_TOTAL) OVER(PARTITION BY P_CODE ORDER BY P_TOTAL) AS TOTAL
FROM PAAMAE
WHERE P_STORE = 1000;
SELECT P_CODE AS P_CODE
, P_STORE AS P_STORE
, P_DATE AS P_DATE
, P_QTY AS P_QTY
, P_TOTAL AS P_TOTAL
, SUM(P_TOTAL) OVER(PARTITION BY P_CODE, P_STORE ORDER BY P_DATE) AS TOTAL
FROM PAAMAE;
SELECT P_CODE AS P_CODE
, SUM(SUM(P_QTY)) OVER() AS TOTAL_QTY
, SUM(SUM(P_TOTAL)) OVER AS TOTAL_PRICE
, P_STORE AS P_STORE
, P_QTY AS P_QTY
, P_TOTAL AS P_TOTAL
, ROUND((RATIO_TO_REPORT(SUM(P_QTY)) OVER()) * 100, 2) AS QTY_%
, ROUND((RATIO_TO_REPORT(SUM(P_TOTAL)) OVER()) * 100, 2) AS TOTAL_%
FROM PANMAE
WHERE P_CODE = 100
GROUP BY P_CODE, P_STROE, P_QTY, P_TOTAL;
SELECT P_STORE AS P_STORE
, P_DATE AS P_DATE
, P_CODE AS P_CODE
, P_QTY AS P_QTY
, LAG(P_QTY, 1) OVER(ORDER BY P_DATE) AS LAG1
, P_QTY - LAG(P_QTY, 1) OVER(ORDER BY P_DATE) AS LAG2
, P_TOTAL AS P_TOTAL
, LAG(P_TOTAL, 1) OVER(ORDER BY P_DATE) AS LAG3
, P_TOTAL - LAG(P_TOTAL, 1) OVER(ORDER BY P_DATE) AS LAG4
FROM PANMAE
WHERE P_STORE = 1000;
SELECT P_STORE AS P_STORE
, P_DATE AS P_DATE
, P_CODE AS P_CODE
, P_QTY AS P_QTY
, LAG(P_QTY, 1) OVER(PARTITION BY P_STOER ORDER BY P_DATE) AS LAG1
, P_QTY - LAG(P_QTY, 1) OVER(PARTITION BY P_STOER ORDER BY P_DATE) AS LAG2
, P_TOTAL AS P_TOTAL
, LAG(P_TOTAL, 1) OVER(PARTITION BY P_STOER ORDER BY P_DATE) AS LAG3
, P_TOTAL - LAG(P_TOTAL, 1) OVER(PARTITION BY P_STOER ORDER BY P_DATE) AS LAG4
FROM PANMAE;
--1번
--emp 테이블을 사용하여 사원 중에서 급여(sal)와 보너스(comm)를 합친 금액이 가장 많은 경우와 가장 적은 경우 ,
--평균 금액을 구하세요. 단 보너스가 없을 경우는 보너스를 0 으로 계산하고 출력 금액은 모두 소수점 첫째 자리까지만 나오게 하세요.
SELECT MAX(SAL + NVL(COMM, 0)) AS MAX
, MIN(SAL + NVL(COMM, 0)) AS MIN
, TRUNC(AVG(SAL + NVL(COMM, 0)), 1) AS AVG
FROM EMP;
--2번
--student 테이블의 birthday 컬럼을 참조해서 아래와 같이 월별로 생일자수를 출력하세요.
SELECT COUNT(*) || 'EA' AS TOTAL
, COUNT(DECODE(MON, '01', 1)) || 'EA' AS JAN
, COUNT(DECODE(MON, '02', 1)) || 'EA' AS FEB
, COUNT(DECODE(MON, '03', 1)) || 'EA' AS MAR
, COUNT(DECODE(MON, '04', 1)) || 'EA' AS APR
, COUNT(DECODE(MON, '05', 1)) || 'EA' AS MAY
, COUNT(DECODE(MON, '06', 1)) || 'EA' AS JUN
, COUNT(DECODE(MON, '07', 1)) || 'EA' AS JUL
, COUNT(DECODE(MON, '08', 1)) || 'EA' AS AUG
, COUNT(DECODE(MON, '09', 1)) || 'EA' AS SEP
, COUNT(DECODE(MON, '10', 1)) || 'EA' AS OCT
, COUNT(DECODE(MON, '11', 1)) || 'EA' AS NOV
, COUNT(DECODE(MON, '12', 1)) || 'EA' AS DEC
FROM(SELECT TO_CHAR(BIRTHDAY, 'MM ') MON
FROM STUDENT);
--3번
--Student 테이블의 tel 컬럼을 참고하여 아래와 같이 지역별 인원수를 출력하세요.
--단 02 ?서울 , 031 ? 경기 , 051 ? 부산 , 052 ? 울산 , 053 ? 대구 , 055 ? 경남으로 출력하세요
SELECT COUNT(*) AS TOTAL
, COUNT(DECODE(TEL, '02', 1)) AS SEOUL
, COUNT(DECODE(TEL, '031', 1)) AS GYEONGGI
, COUNT(DECODE(TEL, '051', 1)) AS BUSAN
, COUNT(DECODE(TEL, '052', 1)) AS ULSAN
, COUNT(DECODE(TEL, '053', 1)) AS DAEGU
, COUNT(DECODE(TEL, '055', 1)) AS GYEONGNAM
FROM(SELECT SUBSTR(TEL, 1, INSTR(TEL, ')')-1) TEL FROM student);
--4번
--먼저 emp 테이블에 아래의 두 건의 데이터를 입력 하신 후 작업하세요.
--Emp 테이블을 사용하여 아래의 화면과 같이 부서별로 직급별로 급여 합계 결과를 출력하세요.
SELECT DEPTNO AS DEPTNO
, SUM(DECODE(JOB, 'CLERK', SAL, 0)) AS CLERK
, SUM(DECODE(JOB, 'MANAGER', SAL, 0)) AS MANAGER
, SUM(DECODE(JOB, 'PRESIDENT', SAL, 0)) AS PRESIDENT
, SUM(DECODE(JOB, 'ANALYST', SAL, 0)) AS ANALYST
, SUM(DECODE(JOB, 'SALESMAN', SAL, 0)) AS SALESMAN
, SUM(SAL) AS TOTAL
FROM EMP
WHERE JOB IS NOT NULL
GROUP BY CUBE(DEPTNO)
ORDER BY DEPTNO;
--5번
--emp 테이블을 사용하여 직원들의 급여와 전체 급여의 누적 급여금액이 아래와 같도록 출력하세요.
--단 급여를 오름차순으로 정렬해서 출력하세요.
SELECT DEPTNO AS DEPTNO
, ENAME AS ENAME
, SAL AS SAL
, LAG(SAL, 1, 0) OVER (ORDER BY SAL) AS TOTAL
FROM EMP;
--6번
--fruit 테이블을 아래와 같은 형태로 출력하세요.
SELECT *
FROM FRUIT PIVOT (SUM(PRICE) FOR NAME IN( 'APPLE' APPLE, 'GRAPE' GRAPE, 'ORANGE' ORANGE ));
--7번
--student 테이블의 Tel 컬럼을 사용하여 아래와 같이 지역별 인원수와 전체대비 차지하는 비율을 출력하세요.
--(단 02 ?서울 , 031 ? 경기 , 051 ? 부산 , 052 ? 울산 , 053 ? 대구 , 055 ? 경남으로 출력하세요)
SELECT COUNT(*) || '명(' || COUNT(*) / 20 * 100 || '%)' AS 합계
, SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '02', 1)) || '명(' || SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '02', 1))/20*100|| '%)' AS 서울
, SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '031', 1)) || '명(' || SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '031', 1))/20*100|| '%)' AS 경기
, SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '051', 1)) || '명(' || SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '051', 1))/20*100|| '%)' AS 부산
, SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '052', 1, 0)) || '명(' || SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '052', 1,0))/20*100|| '%)' AS 울산
, SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '053', 1)) || '명(' || SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '053', 1))/20*100|| '%)' AS 대구
, SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '055', 1)) || '명(' || SUM(DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), '055', 1))/20*100|| '%)' AS 경남
FROM STUDENT;
--8번
--emp 테이블을 사용하여 아래와 같이 부서별로 급여 누적 합계가 나오도록 출력하세요.
--단 부서번호로 오름차순 출력하세요.
SELECT DEPTNO AS DEPTNO
, ENAME AS ENAME
, SAL AS SAL
, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS TOTAL
FROM EMP;
--9번
--emp 테이블을 사용하여 아래와 같이 각 사원의 급여액이 전체 직원 급여총액에서 몇 %의 비율을 차지하는지 출력하세요.
--단 급여 비중이 높은 사람이 먼저 출력되도록 하세요.
SELECT DEPTNO AS DEPTNO
, ENAME AS ENAME
, SAL AS SAL
, SUM(SAL) OVER() AS TOTAL_SAL
, ROUND(RATIO_TO_REPORT(SUM(SAL)) OVER() * 100, 2) AS RATIO
FROM EMP
GROUP BY DEPTNO, ENAME, SAL
ORDER BY SAL DESC;
--10번
--emp 테이블을 조회하여 아래와 같이 각 직원들의 급여가 해당 부서 합계금액에서 몇 %의 비중을 차지하는지를 출력하세요.
--단 부서번호를 기준으로 오름차순으로 출력하세요.
SELECT DEPTNO AS DEPTNO
, ENAME AS ENAME
, SAL AS SAL
, SUM(SAL) OVER(PARTITION BY DEPTNO) AS SUM_SAL
, ROUND(RATIO_TO_REPORT(SUM(SAL)) OVER(PARTITION BY DEPTNO) * 100, 2) AS RATIO
FROM EMP
GROUP BY DEPTNO, ENAME, SAL
ORDER BY DEPTNO;
--11번
-- loan 테이블을 사용하여 1000번 지점의 대출 내역을 출력하되 대출일자,대출코드,대출금액,일자별 누적대출금액을 아래와 같이 출력하세요.
SELECT L_DATE AS 대출일자
, L_CODE AS 대출종목코드
, L_QTY AS 대출건수
, L_TOTAL AS 대출총액
, SUM(L_TOTAL) OVER(ORDER BY L_DATE) AS 누적대출금액
FROM LOAN
WHERE L_STORE = 1000;
--12번
--loan 테이블을 사용하여 전체 지점의 대출코드 , 대출지점 , 대출날짜 , 대출건수 , 대출금액을 대출코드와 대출지점별로 누적 합계를 구하세요.
SELECT L_CODE AS 대출종목코드
, L_STORE AS 대출지점
, L_DATE AS 대출일자
, L_QTY AS 대출건수
, L_TOTAL AS 대출액
, SUM(L_TOTAL) OVER(PARTITION BY L_CODE, L_STORE ORDER BY L_DATE, L_TOTAL) AS 누적대출금액
FROM LOAN;
--13번
--loan 테이블을 조회하여 1000 번 지점의 대출 내역을 대출코드별로 합쳐서 대출일자, 대출코드, 대출건수 , 대출총액 , 코드별누적대출금액을 아래와 같이 출력하세요.
SELECT L_DATE AS 대출일자
, L_CODE AS 대출종목코드
, L_QTY AS 대출건수
, L_TOTAL AS 대출총액
, SUM(L_TOTAL) OVER(ORDER BY L_CODE, L_TOTAL) AS 누적대출금액
FROM LOAN
WHERE L_STORE = 1000;
--14번
--professor 테이블에서 각 교수들의 급여를 구하고 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력하세요
SELECT DEPTNO AS DEPTNO
, NAME AS NAME
, PAY AS PAY
, SUM(PAY) OVER() AS TOTAL_PAY
, ROUND(RATIO_TO_REPORT(SUM(PAY)) OVER() * 100, 2) AS RATIO
FROM PROFESSOR
GROUP BY DEPTNO, NAME, PAY
ORDER BY RATIO DESC;
--15번
--professor 테이블을 조회하여 학과번호 , 교수명 , 급여 , 학과별 급여 합계를 구하고 각 교수의 급여가 해당 학과별 급여 합계에서 차지하는 비율을 출력하세요.
SELECT DEPTNO AS DEPTNO
, NAME AS NAME
, PAY AS PAY
, SUM(PAY) OVER(PARTITION BY DEPTNO) AS TOTAL_DEPTNO
, ROUND(RATIO_TO_REPORT(PAY) OVER(PARTITION BY DEPTNO)*100, 2) AS RATION
FROM PROFESSOR;
--ORACLE 조인
SELECT A.COL1
, B.COL1
FROM TABLE1 A
, TABLE2 B
WHERE A.COL2 = B.COL2;
--ANSI 조인
SELECT A.COL1
, B.COL1
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.COL2 = B.COL2;
CREATE TABLE CAT_A ( NO NUMBER, NAME VARCHAR2(1));
CREATE TABLE CAT_B ( NO NUMBER, NAME VARCHAR2(1));
CREATE TABLE CAT_C ( NO NUMBER, NAME VARCHAR2(1));
INSERT INTO CAT_A VALUES (1,'A');
INSERT INTO CAT_A VALUES (2,'B');
INSERT INTO CAT_B VALUES (1,'C');
INSERT INTO CAT_B VALUES (2,'D');
INSERT INTO CAT_C VALUES (1,'E');
INSERT INTO CAT_C VALUES (2,'F');
SELECT * FROM CAT_A;
SELECT * FROM CAT_B;
SELECT * FROM CAT_C;
--2개 테이블 정상 조인
SELECT A.NAME AS A_NAME
, B.NAME AS B_NAME
FROM CAT_A A
, CAT_B B
WHERE A.NO = B.NO;
--2개 테이블 카디션 곱
SELECT A.NAME AS A_NAME
, B.NAME AS B_NAME
FROM CAT_A A
, CAT_B B;
--3개의 테이블 조인
SELECT A.NAME AS A_NAME
, B.NAME AS B_NAME
, C.NAME AS C_NAME
FROM CAT_A A
, CAT_B B
, CAT_C C
WHERE A.NO = B.NO
AND A.NO = C.NO;
--3개 테이블 조인 조건절 2개
SELECT A.NAME AS A_NAME
, B.NAME AS B_NAME
, C.NAME AS C_NAME
FROM CAT_A A
, CAT_B B
, CAT_C C
WHERE A.NO = B.NO;
--부서 번호가 10번인 사원 정보 조회
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, JOB AS JOB
, SAL AS SAL
FROM EMP
WHERE DEPTNO = 10;
--임의의 3건을 추출
SELECT LEVEL AS C1
FROM DUAL
CONNECT BY LEVEL<=3 ;
--카티션 곱을 사용하여 부서 번호 10번인 집합 3세트를 만듭니다. 조인 조건 누락 경우
SELECT *
FROM(SELECT EMPNO
, ENAME
, JOB
, SAL
FROM EMP
WHERE DEPTNO = 10)
,(SELECT LEVEL C1 FROM DUAL CONNECT BY LEVEL <= 3);
--등가조인
SELECT T1.EMPNO AS EMPNO
, T1.ENAME AS ENAME
, T2.DNAME AS DNAME
FROM EMP T1
, DEPT T2
WHERE T1.DEPTNO = T2.DEPTNO;
--ORACLE
SELECT T1.NAME AS STU_NAME
, T2.NAME AS PROF_NAME
FROM STUDENT T1
, PROFESSOR T2
WHERE T1.PROFNO = T2.PROFNO;
--ANSI
SELECT T1.NAME AS STU_NAME
, T2.NAME AS PROF_NAME
FROM STUDENT T1 INNER JOIN PROFESSOR T2
ON T1.PROFNO = T2.PROFNO;
--STUDENT,DEPARTMENT,PROFESSOR
--ORACLE
SELECT T1.NAME AS STU_NAME
, T2.DNAME AS DEPT_NAME
, T3.NAME AS PROF_NAME
FROM STUDENT T1
, DEPARTMENT T2
, PROFESSOR T3
WHERE T1.DEPTNO1 = T2.DEPTNO
AND T1.PROFNO = T3.PROFNO;
--ANSI
SELECT T1.NAME AS STU_NAME
, T2.DNAME AS DEPT_NAME
, T3.NAME AS PROF_NAME
FROM STUDENT T1 JOIN DEPARTMENT T2
ON T1.DEPTNO1 = T2.DEPTNO
JOIN PROFESSOR T3
ON T1.PROFNO = T3.PROFNO;
--STUDENT 조회하여 1전공이 101인 학생들의 이름과 지도교수 이름 출력
--ORACLE
SELECT T1.NAME AS STU_NAME
, T2.NAME AS PROF_NAME
FROM STUDENT T1
, PROFESSOR T2
WHERE T1.PROFNO = T2.PROFNO
AND DEPTNO1 = 101;
--ANSI
SELECT T1.NAME AS STU_NAME
, T2.NAME AS PROF_NAME
FROM STUDENT T1 JOIN PROFESSOR T2
ON T1.PROFNO = T2.PROFNO
AND DEPTNO1 = 101;
--비등가조인
--CUSTOMER과 GIFT 테이블
--ORACLE
SELECT T1.GNAME AS CUST_NAME
, TO_CHAR(T1.POINT, '999,999') AS POINT
, T2.GNAME AS GIFT_NAME
FROM CUSTOMER T1
, GIFT T2
WHERE T1.POINT >= T2.G_START
AND T1.POINT <= T2.G_END;
--ANSI
SELECT T1.GNAME AS CUST_NAME
, TO_CHAR(T1.POINT, '999,999') AS POINT
, T2.GNAME AS GIFT_NAME
FROM CUSTOMER T1 JOIN GIFT T2
ON T1.POINT >= T2.G_START
AND T1.POINT <= T2.G_END;
--성능향상
SELECT T1.GNAME AS CUST_NAME
, TO_CHAR(T1.POINT, '999,999') AS POINT
, T2.GNAME AS GIFT_NAME
FROM CUSTOMER T1 JOIN GIFT T2
ON T1.POINT >= T2.G_START
AND T1.POINT <= T2.G_END;
--학생 & 점수
--ORACLE
SELECT T1.NAME AS STU_NAME
, T2.TOTAL AS STU_TOTAL
, T3.GRADE AS GRADE
FROM STUDENT T1
, SCORE T2
, HAKJUM T3
WHERE T1.STUDNO = T2.STUDNO
AND T2.TOTAL >= T3.MIN_POINT
AND T2.TOTAL <= T3.MAX_POINT;
--ANSI
SELECT T1.NAME AS STU_NAME
, T2.TOTAL AS STU_TOTAL
, T3.GRADE AS GRADE
FROM STUDENT T1 JOIN SCORE T2
ON T1.STUDNO = T2.STUDNO
JOIN HAKJUM T3
ON (T2.TOTAL >= T3.MIN_POINT
AND T2.TOTAL <= T3.MAX_POINT);
--OUTER JOIN
--Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력
--단 지도교수가 결정되지 않은 학생의 명단도 함께 출력
--ORACLE
--없는 쪽에 (+) = 합쳐지는 쪽에 기호 붙임
SELECT S.NAME AS STU_NAME
, P.NAME AS PROF_NAME
FROM STUDENT S
, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+) ;
--ANSI
SELECT S.NAME AS 학생이름
, P.NAME AS 교수이름
FROM STUDENT S LEFT OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO ;
--Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력
--단 지도학생이 결정 안 된 교수 명단과 지도 교수가 결정 안된 학생 명단을 한꺼번에 출력하세요.
--ORACLE
SELECT S.NAME AS STU_NAME
, P.NAME AS PROF_NAME
FROM STUDENT S
, PROFESSOR P
WHERE S.PROFNO(+) = P.PROFNO
UNION
SELECT S.NAME AS STU_NAME
, P.NAME AS PROF_NAME
FROM STUDENT S
, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+) ;
--ANSI
SELECT S.NAME AS STU_NAME
, P.NAME AS PROF_NAME
FROM STUDENT S FULL OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO;
--ORACLE OUTER JOIN은 WEHRE 조건절에 아우터 조인되는 컬럼들에 대해서는 (+) 연산자를 붙여야한다.
--하나라도 빠지면 일반 조인과 같아짐
SELECT D.DEPTNO AS DEPTNO
, D.DNAME AS DNAME
, D.LOC AS LOC
, E.EMPNO AS EMPNO
, E.ENAME AS ENAME
, E.SAL AS SAL
FROM DEPT D
, EMP E
WHERE D.DEPTNO = E.DEPTNO(+)
AND E.DEPTNO(+) = 20
ORDER BY 1;
--ANSI JOIN의 경우, WHERE 조건절과 ON절 조건의 차이점 명확히 구분하여 사용하기
--SELF JOIN
--원하는 데이터가 하나의 테이블에 다 들어있는 경우
--하나의 테이블을 메모리상에서 별명을 두 개로 사용해서 가상으로 2개의 테이블로 만든 후 Join 작업을 수행
--ORACLE
SELECT E1.ENAME AS ENAME
, E2.ENAME AS MGR_ENAME
FROM EMP E1
, EMP E2
WHERE E1.MGR = E2.EMPNO;
--ANSI
SELECT E1.ENAME AS ENAME
, E2.ENAME AS MGR_ENAME
FROM EMP E1 JOIN EMP E2
ON E1.MGR = E2.EMPNO;
--1번
--학생 테이블 (student) 과 학과 테이블 (department) 테이블을 사용하여 학생이름, 1 전공학과번호(deptno1) , 1전공 학과 이름을 출력
--( ANSI Join 문법과 Oracle Join 문법 으로 각각 SQL 을 작성)
-- Oracle 문법
SELECT A.NAME AS STU_NAME
, B.DEPTNO AS DEPTNO1
, B.DNAME AS DEPT_NAME
FROM STUDENT A
, DEPARTMENT B
WHERE A.DEPTNO1 = B.DEPTNO;
-- ANSI 문법
SELECT A.NAME STU_NAME
, B.DEPTNO DEPTNO1
, B.DNAME DEPT_NAME
FROM STUDENT A
JOIN DEPARTMENT B
ON A.DEPTNO1 = B.DEPTNO;
--2번
--emp2 테이블과 p_grade 테이블을 조회하여 현재 직급이 있는 사원의 이름과 직급, 현재 연봉, 해당 직급의 연봉의 하한금액과 상한 금액을 출력
-- Oracle 문법
SELECT A.NAME AS NAME
, A.POSITION AS POSITION
, A.PAY AS PAY
, B.S_PAY AS LOW_PAY
, B.E_PAY AS HIGH_PAY
FROM EMP2 A
, P_GRADE B
WHERE LOWER(A.POSITION) = LOWER(B.POSITION);
-- ANSI 문법
SELECT A.NAME AS NAME
, A.POSITION AS POSITION
, A.PAY AS PAY
, B.S_PAY AS LOW PAY
, B.E_PAY AS HIGH PAY
FROM EMP2 A
JOIN P_GRADE B
ON LOWER(A.POSITION) = LOWER(B.POSITION);
--3번
--Emp2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재 직급 , 예상 직급 을 출력
--예상 직급은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미
--나이는 오늘(sysdate)을 기준으로 하되 trunc 로 소수점 이하는 절삭해서 계산
-- Oracle 문법
SELECT A.NAME AS NAME
, (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(A.BIRTHDAY, 'YYYY')) AS AGE
, A.POSITION AS CURR_POSITION
, DECODE(A.POSITION, 'BOSS', NULL, C.POSITION) AS BE_POSITION
FROM EMP2 A
, P_GRADE B
, P_GRADE C
WHERE LOWER(A.POSITION) = LOWER(B.POSITION(+))
AND (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(A.BIRTHDAY, 'YYYY')) >= C.S_AGE
AND (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(A.BIRTHDAY, 'YYYY')) <= C.E_AGE
ORDER BY AGE;
-- ANSI 문법
SELECT A.NAME AS NAME
, (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(A.BIRTHDAY, 'YYYY')) AS AGE
, A.POSITION AS CURR_POSITION
, DECODE(A.POSITION, 'BOSS', NULL, C.POSITION) AS BE_POSITION
FROM EMP2 A LEFT JOIN P_GRADE B
ON LOWER(A.POSITION) = LOWER(B.POSITION)
JOIN P_GRADE C
ON (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(A.BIRTHDAY, 'YYYY')) BETWEEN C.S_AGE AND C.E_AGE
ORDER BY AGE;
--4번
--customer 테이블과 gift 테이블을 Join하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때
--Notebook 을 선택할 수 있는 고객명과 포인트, 상품명을 출력
-- Oracle 문법
SELECT A.GNAME CUST_NAME
, A.POINT POINT
, B.GNAME GIFT_NAME
FROM CUSTORMER A
, GIFT B
WHERE A.POINT >= B.G_START
AND B.GNAME = 'NOTEBOOK';
-- ANSI 문법
SELECT A.GNAME CUST_NAME
, A.POINT POINT
, B.GNAME GIFT_NAME
FROM CUSTORMER A JOIN GIFT B
ON A.POINT >= B.G_START
AND B.GNAME = 'NOTEBOOK';
--5번
--professor 테이블에서 교수의 번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 출력
--단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력
--(Oracle Join 구문과 ANSI Join 구문으로 각각 SQL을 작성)
-- Oracle 문법
SELECT A.PROFNO
, A.NAME
, A.HIREDATE
, COUNT(NVL2(B.PROFNO, A.PROFNO, NULL)) COUNT
FROM PROFESSOR A
, PROFESSOR B
WHERE A.HIREDATE > B.HIREDATE(+)
GROUP BY A.PROFNO, A.NAME, A.HIREDATE
ORDER BY COUNT ASC;
-- ANSI 문법
SELECT A.PROFNO
, A.NAME
, A.HIREDATE
, COUNT(NVL2(B.PROFNO, A.PROFNO, NULL)) COUNT
FROM PROFESSOR A LEFT JOIN PROFESSOR B
ON A.HIREDATE > B.HIREDATE
GROUP BY A.PROFNO, A.NAME, A.HIREDATE
ORDER BY COUNT ASC;
--6번
--emp 테이블에서 사원번호, 사원이름, 입사일, 자신보다 먼저 입사한 사람 인원수를 출력하세요. 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력
--(Oracle Join 구문과 ANSI Join 구문으로 각각 SQL을 작성)
-- Oracle 문법
SELECT A.EMPNO
, A.ENAME
, A.HIREDATE
, COUNT(NVL2(B.EMPNO, A.EMPNO, NULL)) COUNT
FROM EMP A
, EMP B
WHERE A.HIREDATE > B.HIREDATE(+)
GROUP BY A.EMPNO,A.ENAME, A.HIREDATE
ORDER BY COUNT;
-- ANSI 문법
SELECT A.EMPNO
, A.ENAME
, A.HIREDATE
, COUNT(NVL2(B.EMPNO, A.EMPNO, NULL)) COUNT
FROM EMP A LEFT JOIN EMP B
ON A.HIREDATE > B.HIREDATE
GROUP BY A.EMPNO,A.ENAME, A.HIREDATE
ORDER BY COUNT;
댓글