SQL 200 학습 시작! ②-(041~080)

prana·2023년 4월 8일
0

database

목록 보기
33/38
post-thumbnail

📕 초보자를 위한 SQL 200제 (PL/SQL)

2. 041~080

데이터 분석 함수로 순위 출력하기

041 RANK

  • 1등이 두 명이어서 2등이 출력되지 않고, 바로 3등이 출력
  • 직업별 월급 높은 순서대로 출력

042 DENSE_RANK

  • RANK와 DENSE_RANK 차이

043 데이터 분석 함수로 등급 출력하기 NTILE

1등급(0~25%)2등급(25%~50%)3등급(50~75%)4등급(75~100%)
  • NULLS LAST : 월급을 높은 것부터 출력되도록 정렬하는데, NULL을 맨 아래에 출력하겠다는 의미.

045 데이터 분석 함수로 데이터를 가로로 출력하기

  • 데이터를 가로로 출력 LISTAGG에 구분자로 콤마(,)를 이용하여 이름이 콤마로 구분됨

046 데이터 분석 함수로 바로 전 행과 다음 행 출력하기 (LAG, LEAD)

048 COLUMN을 ROW로 출력하기 (PIVOT)

SELECT *
  FROM (select job, sal from emp)
  PIVOT (sum(sal) for job in ('ANALYST', 'CLERK','MANAGER', 'SALESMAN'));
-- 더블쿼테이션으로 변경 
SELECT *
  FROM (select job, sal from emp)
  PIVOT (sum(sal) for job in ('ANALYST' as "ANALYST", 'CLERK' as "CLERK",
                                      'MANAGER' as "MANAGER", 'SALESMAN' as "SALESMAN"));
  • 👉 컬럼명 주목!

049 테이블의 열을 행으로 출력하는 방법

1. 테이블 생성
create table order2
( ename  varchar2(10),
  bicycle  number(10),
  camera   number(10),
  notebook  number(10) );

insert  into  order2  values('SMITH', 2,3,1);
insert  into  order2  values('ALLEN',1,2,3 );
insert  into  order2  values('KING',3,2,2 );

commit;
-------------------------------------------------
2. 조회
SELECT * 
    FROM ORDER2
    UNPIVOT (건수 FOR 아이템 IN (BICYCLE, CAMERA, NOTEBOOK));
  • 3)
  • 을 세로로 출력하면
  • NULL값도 출력되게 하려면 INCLUDE NULLS

050 데이터 분석 함수로 누적 데이터 출력하기 sum over

윈도우 기준윈도우 방식설명
ROWSUNBOUNDED PRECEDING맨 첫번째 행을 가리킵니다.
ROWSUNBOUNDED FOLLOWING맨 마지막 행을 가리킵니다.
ROWSCURRENT ROW현재 행을 가리킵니다.
  • BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW :제일 첫번째 행부터 현재 행까지의 값

051 데이터 분석 함수로 비율 출력 RATIO_TO_REPORT

-- 값이 동일하다고 알 수 있음.
SELECT EMPNO, ENAME, SAL, RATIO_TO_REPORT(SAL) OVER() AS 비율, 
            SAL/SUM(SAL) OVER () AS "비교 비율"
    FROM EMP
    WHERE DEPTNO = 20;

052 데이터 분석 함수로 집계 결과 출력 ROLLUP

  • 맨 아래쪽 전체 토탈 월급을 추가적으로 출력하는 쿼리.
  • JOB 컬럼의 데이터도 오름차순으로 정렬되어 출력하게 하기
  • 4번, 10번 줄에 값은 부서번호별 토탈 월급이 출력된다.
  • 나머지는 부서번호별 직업별 토탈 월급이 출력된다.
  • 13번 : 전체 토탈 월급이 출력

053 데이터를 집계한 결과를 추가적으로 전체 집계를 위쪽에 출력

  • GROUP BY CUBE(deptno, job)는 총 4가지 경우 집계 결과가 나온다.
---
1deptno, job부서 번호별 직업별 토탈 월급
2deptno부서 번호별 토탈 월급
3job직업별 토탈 월급
4()전체 토탈 월급

054 데이터 분석 함수로 집계 결과 출력 GROUPING SETS

  • 집계할 컬럼을 직접 선택하여 추가 집계하는 방법
  • 부서 번호와 직업, 부서 번호별 토탈 월급과 직업별 토탈 월급, 전체 토탈 월급 출력

🌟 TOP-N Query

057 출력되는 행 제한하기

  • WITH TIES 옵션 : 여러 행이 N번째 행의 값과 동일하다면, 같이 출력 
  • 2) with ties n번째 값과 동일하다면 같이 출력
SELECT EMPNO, ENAME, JOB, SAL
   FROM EMP
   ORDER BY SAL DESC FETCH FIRST 2 ROWS WITH TIES;
  • OFFSET 옵션 사용하기
  • OFFSET과 FETCH 믹스 
-- 057 4)  OFFSET과 FETCH  믹스
SELECT EMPNO, ENAME, JOB, SAL
    FROM EMP
    ORDER BY SAL DESC OFFSET 9 ROWS
    FETCH FIRST 2 ROWS ONLY;
---OFFSET 9로 출력된 5개의 행 중에서 2개의 행만 출력되고 있음

여러 테이블의 데이터를 조인해서 출력하기

058 EQUI JOIN 이퀄

  • 서로 다른 테이블에 있는 컬럼들의 데이터를 하나의 결과로 조인해서 출력
  • 조인을 주지 않았을 때
  • 👉 두 테이블의 겹치는 컬럼일 경우, 열 이름 앞에 테이블명을 접두어로 붙여서 사용한다. 
-- 별칭 사용
SELECT E.ENAME, D.LOC, E.JOB
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'ANALYST';

059 NON EQUI JOIN 

SELECT E.ENAME, E.SAL, S.GRADE
    FROM EMP E, salgrade S
    WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
-- 두 테이블 사이에 동일한 컬럼은 없지만, 비슷한 컬럼 연결

060 

-- 060 OUTER JOIN 
SELECT E.ENAME, D.LOC
    FROM EMP E, DEPT D
    WHERE E.DEPTNO (+) = D.DEPTNO;
  • 기존 EQUI JOIN 문법에 OUTER 조인 사인(+)만 추가한 것!

061 SELF JOIN

  • 기존 테이블 조회로 확인

062 여러 테이블의 데이터를 조인해서 출력하기 ON절

오라클 조인과 ANSI/ISO SQL:1999 standards

오라클 조인 : EQUI JOIN / NON EQUI JOIN / OUTER JOIN / SELF JOIN

ANSI/ISO SQL:1999 standards : ON절을 사용한 JOIN / LEFT/RIGHT/FULL OUTER JOIN / USING 절을 사용한 JOIN / NATURAL JOIN / CROSS JOIN

  • ANSI/ISO SQL:1999 standards 조인 작성법
  • 작성 sql의 차이만 있을 뿐 성능의 차이는 없다. 일반적으로는 오라클 조인 작성법이 많이 쓰인다.
-오라클 EQUI JOINON절을 사용한 조인
테이블 1개SELECT E.ENAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO;		SELECT E.ENAME, D.LOC

FROM EMP E JOIN DEPT D

ON (E.DEPTNO = D.DEPTNO);
테이블 여러개SELECT E.E.NAME, D.LOC

FROM EMP E, DEPT D, SALGRADE S

WEHRE E.DEPTNO = D.DEPTNO

AND E.SAL BETWEEN S.LOSAL AND S.HISAL;		SELECT E.ENAME, D.LOC, S.GRADE

FROM EMP E

JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)

JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);
  • 여러 개의 테이블을 조인할 때는 연결고리가 되는 조인 조건을 테이블의 개수에서 하나를 차감해서 기술
  • (조인 조건의 개수 = 테이블 개수 -1) :테이블이 3개이므로 위에서 조건을 2개 작성함

063 USING절

  • USING 절에는 조인 조건 대신 두 테이블을 연결할 때 사용할 컬럼인 DEPTNO만 기술할 수 있다.
  • 앞에 테이블명이나 별칭 사용 불가
-오라클 EQUI JOINON절을 사용한 조인
TABLE 1개SELECT E.ENAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO;		SELECT E.ENAME, D.LOC

FROM EMP E JOIN DEPT D

USING (DEPTNO);
TABLE 여러개SELECT E.ENAME, D.LOC

FROM EMP E, DEPT D, SALGRADE S

WHERE E.DEPTNO = D.DEPTNO

AND E.SAL BETWEEN S.LOSAL AND S.HISAL;		SELECT E.ENAME, D.LOC, S.GRADE

FROM EMP E

JOIN DEPT D USING (DEPTNO)

JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

064 NATURAL JOIN

  • 조인조건을 주지 않고 EQUI 조인을 하는 방법 

  • SELECT E.ENAME AS 이름, E.JOB AS 직업, E.SAL AS 월급, D.LOC AS 부서위치
    FROM EMP E NATURAL JOIN DEPT D
    WHERE E.JOB = 'SALESMAN'; 
    ```

  • 조인 조건을 명시적으로 작성하지 않아도, FROM 절에 EMP와 DEPT 사이에 NATURAL JOIN 하겠다고 기술하면, 조인이 되는 쿼리

  • 두 테이블에 둘 다 존재하는 동일한 컬럼을 기반으로 암시적인 조인을 수행

  • 동일한 컬럼인 DEPTNO를 오라클이 알아서 찾아 이를 이용하여 조인을 수행한다.

  • DEPTNO는 별칭없이 수행해야 한다.

  • -> E.DEPTNO=30이 아니라, DEPTNO=30 으로 수정이 필요하다.

065 LEFT/RIGHT OUTER JOIN

  • EQUI JOIN으로 조인이 안 되는 데이터를 출력하는 방법

066 FULL OUTER JOIN 

SELECT E.ENAME AS 이름, E.JOB AS 직업, E.SAL AS 월급, D.LOC AS 부서위치
    FROM EMP E FULL OUTER JOIN DEPT D
    ON (E.DEPTNO = D.DEPTNO);
  • 오라클 조인 작성법으로 양쪽에 (+) 조인 사인을 넣으면 에러가 난다.

FULL OUTER JOIN 을 사용하지 않고 동일한 결과 출력(UNION)

067 집합 연산자로 데이터를 위아래로 연결하기 (UNION ALL)

  • 여러 개의 쿼리 결과 데이터를 위아래로 하나의 결과로 출력하는 방법

🚨 집합 연산자 작성 시 주의사항

  • UNION ALL 위쪽 쿼리 컬럼의 개수가 동일해야 한다.
  • UNION ALL 위쪽 쿼리와 아래쪽 쿼리 컬럼의 데이터 타입이 동일해야 한다.
  • 결과로 출력되는 컬럼명은 위쪽 쿼리의 컬럼명으로 출력된다.
  • ORDER BY 절은 제일 아래쪽 쿼리에만 작성할 수 있다. 
---------------------------    
CREATE TABLE A (COL1 NUMBER(10) );
INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);
INSERT INTO A VALUES(4);
INSERT INTO A VALUES(5);
commit;


CREATE TABLE B (COL1 NUMBER(10));
INSERT INTO A VALUES(3);
INSERT INTO A VALUES(4);
INSERT INTO A VALUES(5);
INSERT INTO A VALUES(6);
INSERT INTO A VALUES(7);
commit;

SELECT COL1 FROM A
UNION ALL
SELECT COL1 FROM B;

068 집합 연산자로 데이터를 위아래로 연결 UNION

😲 UNION 연산자가 UNION ALL과 다른 점

  • 중복된 데이터를 하나의 고유한 값으로 출력
  • 첫 번째 컬럼의 데이터를 기준으로 내림차순으로 정렬하여 출력
CREATE TABLE C (COL1 NUMBER(10) );
INSERT INTO C VALUES(1);
INSERT INTO C VALUES(2);
INSERT INTO C VALUES(3);
INSERT INTO C VALUES(4);
INSERT INTO C VALUES(5);
COMMIT;

CREATE TABLE D (COL1 NUMBER(10) );
INSERT INTO D VALUES(3);
INSERT INTO D VALUES(4);
INSERT INTO D VALUES(5);
INSERT INTO D VALUES(6);
INSERT INTO D VALUES(7);
COMMIT;

SELECT COL1 FROM C
UNION 
SELECT COL1 FROM D;
  • 중복된 데이터가 제거되고, 결과 데이터가 내림차순으로 정렬되어 출력된다.

069 집합 연산자로 데이터의 교집합을 출력 INTERSECT 

----------------------------------
CREATE TABLE E (COL1 NUMBER(10) );
INSERT INTO E VALUES(1);
INSERT INTO E VALUES(2);
INSERT INTO E VALUES(3);
INSERT INTO E VALUES(4);
INSERT INTO E VALUES(5);
COMMIT;

CREATE TABLE F (COL1 NUMBER(10) );
INSERT INTO F VALUES(3);
INSERT INTO F VALUES(4);
INSERT INTO F VALUES(5);
INSERT INTO F VALUES(6);
INSERT INTO F VALUES(7);
COMMIT;

SELECT COL1 FROM E
INTERSECT
SELECT COL1 FROM F;
------------------------------------

070 집합 연산자로 데이터의 차이를 출력하기 MINUS

--- 
CREATE TABLE G (COL1 NUMBER(10) );
INSERT INTO G VALUES(1);
INSERT INTO G VALUES(2);
INSERT INTO G VALUES(3);
INSERT INTO G VALUES(4);
INSERT INTO G VALUES(5);
COMMIT;

CREATE TABLE H (COL1 NUMBER(10) );
INSERT INTO H VALUES(3);
INSERT INTO H VALUES(4);
INSERT INTO H VALUES(5);
INSERT INTO H VALUES(6);
INSERT INTO H VALUES(7);
COMMIT;


SELECT COL1 FROM G
MINUS
SELECT COL1 FROM H;

서브쿼리

071 서브쿼리 

---071 서브쿼리 (단일행 서브쿼리)
--- JONES보다 더 많은 월급을 받는 사원들의 이름과 월급 출력
SELECT ENAME, SAL
    FROM EMP
    WHERE SAL > (SELECT SAL
                    FROM EMP 
                    WHERE ENAME='JONES');
  • SCOTT의 값도 같이 나온다. 이를 방지하려면 메인 쿼리에 조건을 주면 된다.
  • 👉AND ENAME !='SCOTT' 조건은 메인 쿼리이다. 서브 쿼리는 괄호 안이 서브 쿼리이다.

072 서브쿼리 사용하기 2 - 다중 행 서브쿼리

  • 종류설명
    단일 행 서브쿼리서브쿼리에서 메인 쿼리로 하나의 값이 반환됨
    다중 행 서브쿼리서브 쿼리에서 메인 쿼리로 여러 개의 값이 반환됨
    다중 컬럼 서브 쿼리서브 쿼리에서 메인 쿼리로 여러 개의 컬럼 값이 반환됨
  • 종류연산자
    단일 행 서브 쿼리=, !=, >, <, >=, <=
    다중 행 서브쿼리in, not in, >any, <ant, >all, <all
  • 연산자설명
    in리스트의 값과 동일
    Not in리스트의 값과 동일하지 않다
    >all리스트의 가장 큰 값보다 크다
    >any리스트에서 가장 작은 값보다 크다
    <all리스트에서 가장 작은 값보다 작다
    <any리스트에서 가장 큰 값보다 작다.

073 서브쿼리 사용하기 not in

-- 073 not in
select ename, sal, job
    from emp 
    where empno not in (select mgr
                        from emp
                        where mgr is not null);
-- 서브쿼리문이 먼저 실행되었다고 가정 
select ename, sal, job
        from emp
        where empno not in (7839, 7698, 7902, 7566, 7788, 7782);

074 서브쿼리 사용하기 4 - exists 와 not exists
-- 특정 테이블의 데이터가 다른 테이블에도 존재하는지 여부를 확인하는 방법

--074 exists not exists
select * 
    from dept d
    where exists (select * 
                    from emp e 
                    where e.deptno = d.deptno);

075 HAVING 절의 서브쿼리

🌟SELECT 문에서 서브 쿼리문을 사용할 수 있는 절

SELECT문의 6가지 절서브쿼리 사용 여부서브 쿼리 이름
SELECT가능스칼라(SCALAR) 서브 쿼리
FROM가능IN LINE VIEW
WHERE가능서브 쿼리
🚨GROUP BY불가능
HAVING가능서브 쿼리
ORDER BY가능스칼라 서브 쿼리
  • 그룹 바이 절만 빼고 전부 서브 쿼리를 사용

076 FROM절의 서브 쿼리

  • 이름과 월급과 순위를 출력하는 데 순위가 1인 사원만 출력

  • FROM 절의 서브쿼리를 IN LINE VIEW라고 한다.

  • 서브쿼리의 별칭을 V로 사용했다.

077 SELECT 절의 서브 쿼리

  • 스칼라 서브 쿼리는 출력되는 행의 수만큼 반복되어 실행된다.

👉서브 쿼리 캐싱

  • 최대와 최소 월급을 메모리에 올려 놓고, 두 번째 행부터는 메모리에 올려놓은 데이터를 출력시킨다.

078 insert 데이터 입력하기

테이블에 null 값을 입력하는 방법

null입력 방법값insert문 예제
암시적으로 입력INSERT INTO EMP(empno, ename, sal) VALUES(2912, 'JANE', 4500);
명시적으로 입력nullINSERT INTO EMP(empno, ename, sal, job) VALUES (8381, 'JACK', NULL, NULL);
명시적으로 입력''INSERT INTO EMP(empno, ename, sal, job) VALUES(8381, 'JACK', '', '');

DML (Data Manipulation Language)

INSERT - 데이터 입력
UPDATE - 데이터 수정
DELETE - 데이터 삭제
MERGE - 데이터, 입력, 수정, 삭제를 한 번에 진행

079 데이터 수정 UPDATE

UPDATE문가능 여부
UPDATE서브 쿼리 가능
SET서브 쿼리 가능
WHERE서브 쿼리 가능

080 - 데이터 삭제

TRUNCATE TABLE emp; : 모든 데이터를 한 번에 삭제 , 데이터 삭제 후에는 취소가 불가능 하여 DELETE보다 삭제되는 속도가 빠르다. 데이터를 모두 지우고, 테이블 구조만 남겨두는 것

DROP TABLE emp; : 테이블 전체를 한 번에 삭제하는 명령어, 삭제 후 취소(Rollback)은 불가능 하지만, 플래쉬백(Flashback)으로 테이블을 복구할 수는 있다.

delete table emp; (where절을 사용하지 않으면 전체가 삭제)

  • DML문이고, TRUNCATE와 DROP은 DDL문이다. DDL: Data Definition Language약자.
  • DDL문은 수행되면서 암시적인 commit이 발생한다.
DELETETRUNCATEDROP
데이터삭제삭제삭제
저장 공간남김삭제삭제
저장 구조남김남김삭제
취소 여부가능불가능불가능
플래쉬 백 여부가능불가능가능

DDL 문의 종류

종류설명
CREATE객체를 생성
ALTER객체를 수정
DROP객체를 삭제
TRUNCATE객체를 삭제
RENAME객체의 이름을 변경

3. 081~120

4. 121~160

5. 161~200

profile
prana
이전 포스트

[MSSQL] 데이터 유형(타입)

다음 포스트

SQL 200 학습 시작! ①-(001~040)

0개의 댓글

관련 채용 정보