UNION (합집합)
A = {1,2,3}
B = {3,4,5}
A UNION ALL B
123 345
A UNION B
12345
예제1)
SQL> SELECT DEPTNO
2 FROM EMP
3 UNION
4 SELECT DEPTNO
5* FROM DEPT
6 ;
DEPTNO
----------
10
20
30
40
예제2)
SQL> SELECT DEPTNO
2 FROM EMP
3 UNION ALL
4 SELECT DEPTNO
5 FROM DEPT
6 ;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
10
20
30
40
18 개의 행이 선택되었습니다.
INTERSECT (교집합)
예제1)
SQL> SELECT DEPTNO
2 FROM DEPT
3 INTERSECT
4 SELECT DEPTNO
5 FROM EMP
6 ;
DEPTNO
----------
10
20
30
MINUS (차집합)
예제1)
1 SELECT DEPTNO
2 FROM DEPT
3 MINUS
4 SELECT DEPTNO
5* FROM EMP
6 ;
DEPTNO
----------
40
그룹함수
문제1) 커미션 컬럼의 수를 세보세요(널값 포함해서)
SQL> SELECT COUNT(COMM)
2 FROM EMP;
COUNT(COMM)
-----------
4
문제2) 최고급여와 최저급여, 평균급여 출력
MAX() MIN() AVG()
SQL> SELECT MAX(SAL),MIN(SAL),AVG(SAL)
2 FROM EMP
3 ;
MAX(SAL) MIN(SAL) AVG(SAL)
---------- ---------- ----------
5000 800 2073.21429
문제3) 최고급여액과 최저급여액의 차이를 구하시오.
SQL> SELECT MAX(SAL)-MIN(SAL)
2 FROM EMP
3 ;
MAX(SAL)-MIN(SAL)
-----------------
4200
문제4) 평균급여를 구하되 소수점 2째짜리까지 출력 (반올림)
SQL> SELECT ROUND(AVG(SAL),2)
2 FROM EMP
3 ;
ROUND(AVG(SAL),2)
-----------------
2073.21
STDDEV():표준편차
SQL> SELECT STDDEV(COMM), STDDEV(SAL) FROM EMP;
STDDEV(COMM) STDDEV(SAL)
------------ -----------
602.771377 1182.50322
문제1) 부서별 평균 급여
SQL> SELECT AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
AVG(SAL)
----------
2916.66667
2175
1566.66667
문제2) 평균 급여와 부서번호
SQL> SELECT DEPTNO,AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175
30 1566.66667
문제3) 20번부서이상 부서별 최고 급여액
SQL> SELECT MAX(SAL), DEPTNO
2 FROM EMP
3 WHERE DEPTNO>=20
4 GROUP BY DEPTNO
5 ;
MAX(SAL) DEPTNO
---------- ----------
3000 20
2850 30
SQL> SELECT MAX(SAL),DEPTNO <<===오름차순으로 출력된다.
2 FROM EMP
3 GROUP BY DEPTNO
4 HAVING DEPTNO>=20
5 ;
MAX(SAL) DEPTNO
---------- ----------
3000 20
2850 30
문제4) 업무별 급여합계외 평균급여
SQL> SELECT SUM(SAL),AVG(SAL)
2 FROM EMP
3 GROUP BY JOB
4 ;
SUM(SAL) AVG(SAL)
---------- ----------
6000 3000
4150 1037.5
8275 2758.33333
5000 5000
5600 1400
문제5) 업무별 급여 합계가 5000 이상이되는 것에 한해
급여합계와 최고 급여, 최저급여, 업무를 출력
SQL> SELECT MAX(SAL),SUM(SAL),MIN(SAL),JOB
2 FROM EMP
3 GROUP BY JOB
4 HAVING SUM(SAL) >= 5000
5 ;
MAX(SAL) SUM(SAL) MIN(SAL) JOB
---------- ---------- ---------- ---------
3000 6000 3000 ANALYST
2975 8275 2450 MANAGER
5000 5000 5000 PRESIDENT
1600 5600 1250 SALESMAN
문제1) 각 부서별 평균급여, 급여 합계를 구하되
평균 급여가 많은 순에서 적은 순으로 출력하시오.
SQL> SELECT AVG(SAL), SUM(SAL)
2 FROM EMP
3 GROUP BY DEPTNO
4 ORDER BY AVG(SAL) DESC
5 ;
AVG(SAL) SUM(SAL)
---------- ----------
2916.66667 8750
2175 10875
1566.66667 9400
문제2) 각 부서별 같은 업무믈 하는 사원으로 묶되
부서번호, 업무, 인원수를 출력
SQL> SELECT DEPTNO, JOB , COUNT(JOB)
2 FROM EMP
3 GROUP BY DEPTNO, JOB
4 ;
DEPTNO JOB COUNT(JOB)
---------- --------- ----------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 CLERK 2
20 ANALYST 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
9 개의 행이 선택되었습니다.
문제3) 같은 없무를 하는 사원의 수가 4명 이상인 경우에 한해
업무와 인원수를 출력하시오
SQL> SELECT JOB, COUNT(JOB)
2 FROM EMP
3 GROUP BY JOB
4 HAVING COUNT(JOB) >= 4
5 ;
JOB COUNT(JOB)
--------- ----------
CLERK 4
SALESMAN 4
ROLLUP
문제1) 부서별 부서번호, 급여 합계
SQL> SELECT DEPTNO, SUM(SAL)
2 FROM EMP
3 GROUP BY DEPTNO
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
문제2) 부서별 부서번호, 급여 합계, 총 급여 합계 출력
SQL> SELECT DEPTNO, SUM(SAL)
2 FROM EMP
3 GROUP BY ROLLUP(DEPTNO) * 기준 1개
4 ;
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025
문제3)
SQL> SELECT DEPTNO, JOB, SUM(SAL)
2 FROM EMP
3 GROUP BY ROLLUP(DEPTNO,JOB) *기준 2개
4 ;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
13 개의 행이 선택되었습니다.
CUBE
예제1)
1 SELECT DEPTNO, JOB, SUM(SAL)
2 FROM EMP
3* GROUP BY CUBE(DEPTNO,JOB)
4 ;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
18 개의 행이 선택되었습니다.
문제1) EMP 테이블에서 이름, 급여, 커미션, 총액(SAL+COMM)을 구하되
총액이 많은 순서로 출력
ENAME SAL COMM 총액
---------- ---------- ---------- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100(MARTIN 1250 1400 2650
ALLEN 1600 300 1900
WARD 1250 500 1750
TURNER 1500 0 1500
14 개의 행이 선택되었습니다.
문제2) 10번 부서의 모든 사원들에게 급여의 13%를 보너스로 지불하기로 하였다.
사원명, 급여, 보너스(급여의 13%), 부서번호를 출력하시오
SQL> SELECT ENAME, SAL, (SAL*0.13) AS "보너스" , DEPTNO
2 FROM EMP
3 ;
ENAME SAL 보너스 DEPTNO
---------- ---------- ---------- ----------
SMITH 800 104 20
ALLEN 1600 208 30
WARD 1250 162.5 30
JONES 2975 386.75 20
MARTIN 1250 162.5 30
BLAKE 2850 370.5 30
CLARK 2450 318.5 10
SCOTT 3000 390 20
KING 5000 650 10
TURNER 1500 195 30
ADAMS 1100 143 20
JAMES 950 123.5 30
FORD 3000 390 20
MILLER 1300 169 10
14 개의 행이 선택되었습니다.
문제3) 30번부서의 연봉을 셰산하여 사원명, 부서번호, 급여, 연봉 출력
(연봉 = 12달치 급여 + 급여의 150%)
SQL> SELECT ENAME, DEPTNO, SAL, ((SAL*12)+(SAL*1.5)) AS "연봉"
2 FROM EMP
3 WHERE DEPTNO = 30
4 ;
ENAME DEPTNO SAL 연봉
---------- ---------- ---------- ----------
ALLEN 30 1600 21600
WARD 30 1250 16875
MARTIN 30 1250 16875
BLAKE 30 2850 38475
TURNER 30 1500 20250
JAMES 30 950 12825
6 개의 행이 선택되었습니다.
문제4) 급여가 1500이상~ 3000이하의 사원은 급여의 15%를 회비로 지불하기로 하였다.
사원명, 급여, 회비(소수점 두자리까지 출력.반올림) 출력하시오
SQL> SELECT ENAME, SAL, ROUND((SAL*0.15),2) AS "회비"
2 FROM EMP
3 WHERE SAL BETWEEN 1500 AND 3000
4 ;
ENAME SAL 회비
---------- ---------- ----------
ALLEN 1600 240
JONES 2975 446.25
BLAKE 2850 427.5
CLARK 2450 367.5
SCOTT 3000 450
TURNER 1500 225
FORD 3000 450
7 개의 행이 선택되었습니다.
문제5) 급여가 2000 이상인 사원은 급여의 15%를 회비로 내기로 하였다.
사원명, 급여, 회비(소수점이하 절삭)를 출력하세요.
SQL> select ename, sal, trunc((sal*0.15),0)
2 from emp
3 where sal>=2000
4 ;
ENAME SAL TRUNC((SAL*0.15),0)
---------- ---------- -------------------
JONES 2975 446
BLAKE 2850 427
CLARK 2450 367
SCOTT 3000 450
KING 5000 750
FORD 3000 450
문제6) 부서번호가 20번인 사원의 시간당 임금을 계산하세요.
단, 1달의 근무일수는 12일.
1일의 근무시간은 5시간 입니다.
사원명, 급여, 시간당임금(소수점 첫번째 자리에서 반올림)을 출력하세요
SQL> select ename, sal,round((sal/12/5),2) "시간당임금"
2 from emp
3 where deptno=20
4 ;
ENAME SAL 시간당임금
---------- ---------- ----------
SMITH 800 13.33
JONES 2975 49.58
SCOTT 3000 50
ADAMS 1100 18.33
FORD 3000 50
문제7) 사원명, 급여, 실수령액을 출력하세요.
*실수력액= 급여-세금
*세금 = 급여액의 10%
SQL> select ename, sal, (sal-(sal*0.1)) "실수령액"
2 from emp
3 ;
ENAME SAL 실수령액
---------- ---------- ----------
SMITH 800 720
ALLEN 1600 1440
WARD 1250 1125
JONES 2975 2677.5
MARTIN 1250 1125
BLAKE 2850 2565
CLARK 2450 2205
SCOTT 3000 2700
KING 5000 4500
TURNER 1500 1350
ADAMS 1100 990
JAMES 950 855
FORD 3000 2700
MILLER 1300 1170
