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

prana·2023년 4월 8일

database

목록 보기
34/38
post-thumbnail

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

1. 001~040

001

  • 가독성을 높이기 위해 SQL은 대문자로 작성하고, 컬럼명과 테이블명은 소문자로 작성하기를 권장한다.
  • 가급적 select절과 from절을 각각 별도의 라인에 작성
  • from 절 작성 시에는 2칸~3칸 정도 공백을 두어 들여쓰기 해주면 가독성이 좋아진다.

002

  • select dept.*, deptno from dept; 추가 컬럼이 붙는 경우 별표 앞에 테이블명.을 붙여야 한다.

003

  • "" 더블 쿼테이션 마크가 붙는 경우
      1. 대소문자를 구분하여 출력할 때
      1. 공백 문자를 출력
      1. 특수문자를 출력할 때($, _, #만 가능)

004

  • 문자열 합치기

005

  • 날짜 데이터 검색의 경우 현재 접속한 세션의 날짜 형식에 맞춰 작성해줘야 한다.

009

  • Null : 데이터가 없는 상태, 즉 데이터가 할당되지 않은 상태 또는 알 수 없는 값.

011

  • 비교 연산자 배우기

012 - LIKE

  • % : 0개 이상의 임의 문자와 일치
  • _ : 하나의 문자와 일치

013

  • 알 수 없는 값이기에 이퀄 연산자로는 비교할 수 없다. null값을 검색하기 위해서는 is null

🌟015 - 논리 연산자

  • TRUE AND NULL = NULL

  • AND 연산자 진리 연산표

ANDTRUEFALSENULL
TRUETRUEFALSENULL
FALSEFALSEFALSEFALSE
NULLNULLFALSENULL
  • OR 연산자
ORTRUEFALSENULL
TRUETRUETRUETRUE
FALSETRUEFALSENULL
NULLTRUENULLNULL
  • NOT
NOTTRUEFALSENULL
TRUEFALSETRUENULL

016

  • inicap 함수는 첫 번째 철자만 대문자 출력, 나머지는 소문자로 출력

017 substr

SELECT SUBSTR('SMITH', 1, 3)
    FROM DUAL;  
-- DUAL : 간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용하는 테이블

018 INSTR

019 replace

  • - 월급의 숫자 0부터 3까지 *별표처리
  • 이름의 두 번째 자리의 한글을 * 별표처리

021

022 trim, rtrim, ltrim

insert into emp(empno,ename,sal,job,deptno) values(8291, 'JACK  ', 3000, 'SALESMAN', 30);
commit; -- 공백 데이터 넣기

026 MONTHS_BETWEEN

  • MONTHS_BETWEEN(최신날짜, 예전날짜)

027 ADD_MONTHS

  • interval '100' month

028 돌아오는 특정 요일 날짜 출력

029 특정 날짜가 있는 달의 마지막 날짜 출력 LAST_DAY

030 문자형으로 데이터 변환

형식포맷형식포맷
연도RRRR, YYYY, RR, YY주WW, IW, W
월MM, MON시간HH, HH24
일DD분MI
요일DAY, DY초SS
  • extract
  • T0_CHAR(sal*200, 'L999,999,999') -> ￦ 원화 단위 사용 가능

031 날짜형

  • 현재 내가 접속한 세션의 날짜 형식 변경 
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/RR';
# 지금 접속한 세션에서만 유효. 로그아웃하고 다시 새로 접속하면 파라미터 설정 값은 사라진다.
  • 일관되게 날짜 검색하기 
SELECT ename, hiredate
	FROM emp
    WHERE hiredate = TO_DATE('81/11/17' 'RR/MM/DD');
# 다시 날짜 포맷 변경 
ALTER SESSION SET NLS_DATE_FORMAT='RR/MM/DD';

🌟SET AUTOT ON 명령어

032 3)

SET AUTOT ON : SQL을 실행할 때 출력되는 결과와 실행계획을 한 번에 보여달라는 SQLPLUS 명령어
실행계획 : 오라클이 SQL을 실행할 때 어떠한 방법으로 데이터를 검색하겠다는 계획서

  • SET AUTOT ON
  • SELECT ENAME, SAL
    FROM EMP32
    WHERE SAL = 3000;
  • TO_NUBER()로 형변환된 것을 알 수 있다.

033 4)

-- 033 NVL2
-- NULL이 아닌 사원들은 sal+comm 을 출력, NULL인 사원들은 sal을 출력
SELECT ENAME, SAL, COMM, NVL2(COMM, SAL+COMM, SAL)
    FROM EMP
    WHERE JOB IN ('SALESMAN', 'ANALYSIST');

if문을 SQL로 표현하기

034 if문을 SQL로 표현하기 ① - DECODE

  • default 값은 생략 가능하다.

035 ② CASE문

🌟 SQL 실행 순서

SELECT job, MAX(SAL)      --- 4 (실행순서)
	FROM emp 			  --- 1
    WHERE job='SALESMAN'  --- 2 
    GROUP BY job;		  --- 3

037 최소값

  • ORDER BY 절은 항상 맨 마지막에 작성하고 실행 또한 맨 마지막에 실행된다.
  • 그룹함수의 특징은 WHERE 절의 조건이 거짓이어도 결과를 항상 출력한다는 것이다.
    -

038 AVG

  • 그룹함수는 NULL값을 무시한다. NULL을 제외한 나머지 데이터의 값을 나누어 평균값을 출력한다.
  • NVL함수를 사용하면 합계를 수행할 때 0이 연산에 포함되어, 시간이 더 걸리게 된다.
    (EX: SUM(NVL(comm,0))

039 sum

🌟 having 절

SELECT job, SUM(SAL)				--- 5
	FROM emp						--- 1
    WHERE job != 'SALESMAN'			--- 2
    GROUP BY job					--- 3
    HAVING sum(sal) >= 4000			--- 4
    ORDER BY sum(sal) DESC;			--- 6

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' 조건은 메인 쿼리이다. 서브 쿼리는 괄호 안이 서브 쿼리이다.

3. 081~120

4. 121~160

5. 161~200

profile
prana
이전 포스트

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

다음 포스트

SQL 200 학습 시작! ③-(081~120)

0개의 댓글