교육 21일차

권재현·2021년 4월 13일

교육

목록 보기

15/49

20일차에 준 문제 2번 정답

--급여 등급이 2등급인 사원들/ 중 부서별 급여 1위를 구하시오.  
SELECT *
FROM
         ( SELECT E.ENAME, E.SAL,D.DNAME,SG.GRADE,
           RANK() OVER( PARTITION BY DNAME ORDER BY SAL DESC) AS RNK
                	 FROM EMP E INNER JOIN DEPT D 
                		ON E.DEPTNO = D.DEPTNO
                  			  INNER JOIN SALGRADE SG
                   					 ON SG.GRADE = 2
                   		 AND E.SAL BETWEEN LOSAL AND HISAL)
WHERE RNK =1
;

서브쿼리만 출력하면 급여 1위가 아니게 출력되서 한번 더 가공된 서브쿼리를 넣고 WHERE에 1등만 나올 수 있게 RNK =1 을 써준다.

20일 차 복습

1.집계함수 - 데이터를 수집하여 가공하는 것

A. count(값)- row의 수를 센다.

값을 지정할 경우, NULL 인 것은 세지 않는다.

B. sum(값) - 값의 합계

C. avg(값) - 값의 평균

D. max(값)- 값의 최대값

F. min(값)- 값의 최소값

그룹화 - 기준점을 만들고 기준점 별 집계
- group by 값...
기본 형태
- WHERE>
  - GROUB BY 값...
- ORDER BY >

※ 집계함수 사용 시 주의 사항

SELECT에 올 수 있는 것은 GROUP BY에 사용된 컬럼과 집계함수를 포함한 함수 뿐이다

--NULL은 세지 않는다.
SELECT  COUNT(ENAME)
FROM EMP

;

--SUM은 수치로 가능한 것만 가져올 수 있음
SELECT SUM(SAL)
FROM EMP
;

SELECT AVG(SAL)
FROM EMP
;

SELECT MAX(SAL)
FROM EMP
;

SELECT MIN(SAL)
FROM EMP
;

집계함수 발생 순서

SELECT : GROUB BY 에 온 것과 집계함수 및 다른 함수 사용

FROM : 집계 함수 사용 X

WHERE : 집계 함수 사용 X

★GROUP BY : 그룹화 + 압축(집계)

★HAVING : 집계함수사용 조건처리

ORDER BY : GROUB BY에 온것, 집계함수, SELECT에 지정한 것

★: 선택적

실행순서 FROM - WHERE - GROUB BY - HAVING - SELECT - ORDER BY

예제

--GROUB BY
--부서별,업무별 급여 평균을 구하시오.
SELECT DEPTNO,JOB,AVG(SAL),COUNT(*)
FROM EMP
--WHERE JOB = 'CLERK'
GROUP BY DEPTNO, JOB
ORDER BY AVG(SAL) ASC
;

--직원 수 가 3명이상인 업무별 직원 수를 구하시오.
SELECT  JOB,CNT
FROM      
        (
        SELECT JOB,COUNT(*) AS CNT
        FROM EMP
        GROUP BY JOB
        )
WHERE CNT > =3    
;

SELECT JOB,COUNT(*) AS CNT
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >= 3  
;

2. 테이블

자료형
- NUMBER : 숫자
- VARCHAR2 : 문자 -4000 BYTE 까지 가능
- DATE : 날짜

숫자 : 1 BYTE, 영어 : 2 BYTE, 한글 : 4 BYTE, 공백 : 1 BYTE

테이블 생성, 수정, 삭제 -DDL(정의어)

CREATE : 생성
ALTER : 수정
DROP : 삭제

INSERT : 데이터 추가

기본형태
- INSERT into 테이블 명( 컬럼 명,.....)
- VALUES(값,......)
컬럼명은 테이블에 존재하는 것, 필수항목 중 기본 값 없는것은 다 있어야 함.
값,...은 지정한 컬럼과 개수, 위치,타입이 동일해야 함.

transaction : 작업을 데이터 안전성 확보를 위해 임시저장

commit : 현재까지의 작업을 저장.
rollback : 마지막 commit 시점으로 돌아가기

UPDATE : 수정

기본형태

UPDATE 테이블명 set 컬러명 = 값,.....--> 직접적인 값, 컬러명(기존의 값을 불러옴)

where 조건(선택적 영역) ※ where 없는 경우 모든 row에 적용

DELETE : ROW를 삭제

기본 형태

DELETE FROM 테이블명

★WHERE 조건 ( 없을 경우 모든 ROW 삭제)

;

★: 선택적

사용 시 꼭 확인 하고 사용하자

햄버거 문제

--데이터 추가
INSERT INTO HAMBUGER(PNO,PNAME,PRICE,STOCK,PDATE,ETC)
VALUES(5,'쉑쉑',2000,5,'2021-04-01','비싸다')
;

----등록일자별 /재고수량 합계를 구하시오
SELECT PDATE,SUM(STOCK) AS HSUM
FROM HAMBUGER
GROUP BY PDATE
HAVING SUM(STOCK) < 50
;

HAVING은 실습 겸 추가한 조건 , 집계함수 처리조건 명심할 것

마지막 문제

--전체 급여 평균(AVG)이하로 급여를 받는 사원들의 급여를  30%인상하여
--인상된 급여기준으로(파티션) 전체 급여 순위를 구하시오. 급여 1.3

--급여평균 VIEW
SELECT AVG(SAL)
FROM EMP
;
--전체 급여 평균 이하 받는 사원 급여 30% 인상 VIEW
 SELECT E.ENAME,E.SAL,
                CASE WHEN E.SAL < A.ASAL
                    THEN E.SAL * 1.3
                    ELSE E.SAL
                END  AS ESAL
FROM EMP E INNER JOIN ( SELECT FLOOR(AVG(SAL)) AS ASAL
            						FROM EMP) A
                                    ON 1=1 


--인상된 급여기준으로 전체 급여 순위 VIEW
SELECT    E.ENAME,E.SAL, E.ESAL,
        RANK() OVER( ORDER BY ESAL DESC) AS RNK
FROM        (  SELECT E.ENAME,E.SAL,
                CASE WHEN E.SAL < A.ASAL
                    THEN E.SAL * 1.3
                    ELSE E.SAL
                END  AS ESAL
                    FROM EMP E INNER JOIN ( SELECT FLOOR(AVG(SAL)) AS ASAL
                                    FROM EMP) A
                                    ON 1=1 ) E
;