🐷전동준(Jeon DongJun)

Go with the flow

[Oracle]오라클 강의내용 복습 2일차

Oracle 2️⃣

  • 정처기 필기 가채점 상 합격이여서 기분좋은 상태로 강의를 들은 하루였다.😋
  • SQLD 자격을 취득해서 관련 강의들은 듣기 매우 수월했따!


함수

LPAD / RPAD

  • 왼쪽정렬/오른쪽정렬

  • 정렬 후 두번째 파라미터의 숫자만큼 공간을 만들고, 세번째 적은 파라미터로 백을 대체한다.

  • 예시1

    • LPAD 왼쪽정렬

    • SELECT lpad(ename, 7, '*') FROM emp;
    • image

  • 예시2

    • RPAD 오른쪽정렬

    • SELECT rpad(ename,10,'*') FROM emp;
    • image

LTRIM / RTRIM

  • 문자열을 잘라내기(다듬기)

  • 첫 번째 파라미터에는 다듬을 문자열을, 두번째 파라미터는 지울 값을 적음

  • 실행 후 공백에서 다듬기를 멈춘다.

  • 예시1

    • SELECT ltrim('xyxzyygoodjeon 0513', 'xyz') FROM dual;
    • image

  • 예시2

    • SELECT rtrim('goodjeon5 0513', '0123456789') FROM dual;

    • image

    • 공백도 잘라주고 싶다면, 두 번째 파라미터 공백도 추가하면 된다.

    • image

TRIM

  • 문자열로부터 문자를 하나만 제거하는 것

  • 양쪽에서 제거할 문자중 하나만 제거한다.

  • 예시

    • SELECT trim('x' FROM 'xyxzyyTech6 327') FROM dual;
    • image

  • 첫번째 파라미터에 한 개 이상의 값을 적으면 에러가 난다.

SUBSTR

  • 문자열에서 원하는 위치의 값만 가져오는 것

  • 첫 번째 파라미터에는 문자열, 두 번째에는 시작 위치, 세 번째 파라미터는 가져올 길이를 적어줘야한다.)

  • 시작위치를 양수로 잡으면 왼쪽부터 오른쪽, 음수로 잡으면 오른쪽(끝)부터 왼쪽(시작)방향으로 간다.

  • 또한, 오라클에서는 인덱스가 0부터가 아닌 1부터 시작한다.

  • 예시

    • 사원이름에서 두 번째 글자만 가져오는 것

    • SELECT substr(ename,2,1) FROM emp;

    • image

    • 뒤에서 두번째 부터 마지막 까지 찾아오기

    • SELECT substr(ename, -2) FROM emp;
    • image

INSTR

  • 인덱스 값을 찾아오는 것

  • 첫 번째 파라미터에는 문자열, 두 번째는 인덱스를 찾을 문자, 세 번째는 시작위치, 네 번째는 찾을 개수를 적어주면 된다.

  • 시작위치의 양수, 음수에 따른 방향은 SUBSTR과 같다.

  • 인덱스 값은 무조건 왼쪽 기준으로 생각하면된다.

  • 예시1

    • 사원 이름에서, S값의 인덱스를 찾을 것이고, 첫번 째 값을 찾을 것

    • SELECT ename, instr(ename, 'S', 1, 1) FROM emp;

    • image

    • SMITH는 S를 가지고 있고, 처음에 위치해 있어서 1을 반환했고, JONES는 5번째에 S를 가지고 있어 5를 반환한다.

  • 예시2

    • 뒤에서 부터 a를 찾을 것인데, 두 번째 a의 인덱스 값을 찾을 것이다.

    • SELECT instr('adcccva', 'a', -1, 2) FROM dual;

    • image

    • 오른쪽에서부터 보면 두 번째 a가 문자열의 가장 첫번째에 위치하고 있기 때문에 1을 반환한다.

LENGTH / LENGTHB

  • 주어진 컬럼의 문자의 길이를 반환한다(NUMBER / BITE)
  • 컬럼의 타입이 CHAR인 경우, 데이터와 상관 없이 컬럼의 전체 길이를 반환한다.
  • UTF-8 일떄는 한 문자가 3바이트다.

ROUND / TRUNC

  • ROUND는 반올림

  • TRUNC는 버리기

  • 첫 파라미터에는 실수, 두 번째는 반올림하거나 버릴 자리 수를 적어줌

  • 예시1

    • 소수점 자리를 다 반올림한 경우

    • SELECT round(123.456) FROM dual;
    • image

  • 예시2

    • 소수점 첫 번째 자리 기준으로 반올림

    • SELECT round(123.456, 1) FROM dual;
    • image

  • 예시3

    • 소수점 첫 번째 자리 기준으로 밑은 버리기

    • SELECT trunc(123.456, 1) FROM dual;
    • image

  • 예시4

    • 1의 자리 수 버리기

    • SELECT trunc(123.456, -1) FROM dual;
    • image

CEIL / FLOOR()

  • 올림 / 버림

  • 예시1

    • SELECT ceil(123.456) FROM dual;
    • image

  • 예시2

    • 인수의 개수가 부적합할 때

    • SELECT floor(123.456, 1) FROM dual;
    • image

ADD_MONTH(날짜, 더하려는 개월)

  • 말그대로 선택한 날짜부터 개월수를 날짜를 더해서 값을 반환하는 것

  • 예시

    • 입사 뒤 10주년을 구해보자.

    • SELECT ename, hiredate, ADD_MONTHS(hiredate, 120) FROM emp;
    • image

MONTHS_BETWEEN(날짜1, 2)

  • 지정한 두 날짜의 개월수를 반환

  • 예시

    • SELECT ename, job, hiredate, trunc(MONTHS_BETWEEN('2000/01/01', hiredate)/12) 
FROM emp WHERE MONTHS_BETWEEN('2000/01/01', hiredate) > 120;
    • image

TO_NUMBR / TO_CHAR / TO_DATE

  • NUMBER <-> CHAR <-> DATE

  • 이런 식으로 무조건 형태를 거쳐야한다. DATE에서 NUMBER로 건너뛰기 안된다.

  • TO_CHAR number 표현 예시

    • SELECT TO_char(1234, '99999') AS tochar1, to_char(1234, '00000') AS tochar2, to_char(1234,'l9999') AS tochar3, 
TO_char(1234, '9,999') AS tochar4 FROM dual;

    • image

    • 99999 단순한 자리 채우기, 00000은 빈자리를 0으로 채우기, l9999는 l자리에 원화표시 넣기, 9,999는 원하는 자리에 콤마를 넣어준 것

  • TO_CHAR date 표현 예시

    • to_char를 사용해 날짜를 원하는 형식의 문자열로 바꿀 수 있다.

    • SELECT
to_char(sysdate, 'HH24:MI:SS') AS q1,
to_char(sysdate, 'MON DY, YYYY') AS q2,
to_char(sysdate, 'YYYY-FMMM-DD DAY') AS q3,
to_char(sysdate, 'YYYY-MM-DD') AS q4,
to_char(sysdate, 'YEAR, Q') AS q5
FROM dual;
    • image

  • TO_DATE 예시

    • 숫자를 날짜형식으로 바꿔줌

    • SELECT to_date('20220307', 'YYYYMMDD') FROM dual;

    • image

    • 숫자를 날짜로 바꾸고 문자로 바꾸기

    • SELECT to_char(to_date('20220307', 'yyyymmdd'), 'yyyy, mon') FROM dual;

    • image

    • SELECT to_char(to_date('220307 101201', 'yymmdd hh24miss'), 'yy-mm-dd am fmhh:mi:ss') FROM dual;
    • image

  • TO_NUMBER 예시

    • 입사일 데이터에서 년도와 월을 가져오자.

    • DBeaver 기준이 아닌 cmd창 기준의 날짜로 잘라줘야한다.

    • DBeaver에서 HIREDATE를 그냥 조회하면 1980-12-17 00:00:00.000 이런 형식이다.

    • CMD에서 확인하면 80/12/17 이런 형식이다.

    • SELECT ename, to_number(substr(hiredate, 1, 2)), to_number(substr(HIREDATE, 4, 2)) FROM emp;
    • image

DECODE

  • 조건에 맞는다면 원하는 값으로 바꿔주고, 아니라면 NULL을 반환

  • 첫번째 파라미터에는 비교할 값, 두 번째에는 속성값(조건), 세 번째에는 반환할 값

  • 예시

    • 사원 테이블에서 직업이 매니져인 사람은 0으로

    • SELECT ename, job, decode(job, 'MANAGER', '0') FROM emp;
    • image

CASE WHEN

  • CASE WHEN 조건 THEN 참일 때 반환값 [, WHEN THEN ...] [ELSE 거짓일 때 반환값] END ELSE 지정하지 않으면 거짓일 때 NULL 반환

  • 예시

    • 월급이 1000이하면 초급, 2000이하면 중급, 그게아니라면 고급으로 나타내라.

    • SELECT ename, sal, CASE WHEN sal <= 1000 THEN '초급' WHEN sal <= 2000 THEN '중급' ELSE '고급' END FROM emp;

    • image

    • 사원 테이블에서 직업이 MANAGER인 사원은 1, SALESMAN 인 사원은 2, 아니면 3 을 출력하자.

    • SELECT ename, job, CASE WHEN job = 'MANAGER' THEN 1 WHEN job = 'SALESMAN' THEN 2 ELSE 3 END FROM emp;
    • image

집계함수

  • 평균, 최소값, 최대값 등을 구하는 함수

  • count, max, min, sum, avg 등등…

  • 기본적으로 연산에서 NULL을 제외한다.

  • 그래서 NVL(대상, 대체값) 을 사용해야한다.

  • 예시

    • SELECT count(comm), count(nvl(comm, 0)) FROM emp;

    • image

    • 단순히 커미션이 널이 아닌애들만 카운트하는 count(comm)과 NULL값을 0으로 대체하는 count(nvl(comm, 0))으로 했을 때의 차이를 보여준다.

    • 사원의 연봉을 출력해보자. (comm의 null값을 생각해야 함)

    • SELECT ename, sal * 12 + nvl(comm, 0) FROM emp;
    • image

그룹함수

  • rollup, cube, grouping sets

  • 일반적으로 집계함수와 같이 사용

  • rollup : 순서에 맞게 중간 집계 추가

    • SELECT A, B, 집계함수 FROM 테이블 GROUP BY ROLLUP(A, B);
    • (A,), (A,B) , 전체에 대한 집계가 나옴

  • cube : 모든 중간 집계 추가

    • SELECT A, B, 집계함수 FROM 테이블 GROUP BY CUBE(A, B);
    • 표현되는 것은 ROLLUP과 반대로 전체집계부터 나옴
    • 전체, (A,), (,B), (A,B) 에 대한 집곅 나옴

  • grouping sets : 그룹함수의 결과에 원하는 결과를 추가

  • ROLLUP 예시

    • SELECT job, deptno, avg(sal) FROM emp GROUP BY rollup(job, deptno);

    • image

    • job에 대한 집계와, job-deptno에 대한 집계, 전체 집계 이런 식으로 나온다.

  • CUBE 예시

    • SELECT job, deptno, avg(sal) FROM emp GROUP BY cube(job, deptno);

    • image

    • 전체 집계, B에대한 집계, A에 대한 집계 , AB에 대한 집계 식으로 나온다.

  • GROUPING SETS 예시

    • SELECT JOB, DEPTNO, AVG(SAL) FROM EMP GROUP BY GROUPING SETS(ROLLUP(JOB, DEPTNO), DEPTNO);

    • image

    • DEPTNO에 대한 AVG(SAL)을 추가해준 것

ROWID / ROWNUM

  • 식별자로 사용하며, 수정이 불가하다.

  • 예시

    • NO라는 넘버타입의 컬럼을 만들어 줌

    • CREATE TABLE RT(NO NUMBER);
    
INSERT INTO RT VALUES(1111);
INSERT INTO RT VALUES(2222);
INSERT INTO RT VALUES(3333);
SELECT ROWID, ROWNUM, NO FROM RT;

    • image

    • ROWID와 ROWNUM을 따로 만들어 준 적이 없는데 자동으로 만들어졌다.

    • 그 뜻은 컴퓨터가 내부적으로 한 줄(ROW)를 저렇게 관리하고 있다는 것을 알 수 있다. 특히, ROWNUM은 엑셀의 행번호를 생각하면 된다. 그리고 ROWNUM은 그 테이블의 상황마다 유동적으로 바뀐다.

    • DELETE FROM RT WHERE NO = 3333;
DELETE FROM RT WHERE NO = 1111;
SELECT ROWID, ROWNUM, NO FROM RT;
    • image

TOP N QUERY

  • RANK 함수를 지원하지 않는 DBMS도 있을 수도 있기 때문에 사용할 줄 알아야한다.

  • 예시

    • 월급 1위부터 4위까지 출력해보자.

    • SELECT ENAME, SAL, ROWNUM FROM EMP;

    • image

    • SELECT ENAME, SAL, ROWNUM FROM EMP ORDER BY SAL DESC;

    • image

    • ROWNUM이 뒤죽박죽이다. ROWNUM은 INSERT 순서에 따라 매겨지기 때문이다. 따라서 가상테이블을 만들어 정렬해야한다.

    • SELECT ENAME, SAL, ROWNUM FROM 
(SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC);

    • image

    • 이러면 ROWNUM이 정리되는 것을 확인할 수 있다.

    • SELECT ENAME, SAL, ROWNUM FROM 
(SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <= 4;

    • image

    • 이렇게 월급이 많은 1~4위를 구할 수 있다.

    • 만약 월급 순위 3~5위를 구하고 싶다면,

    • SELECT ENAME, SAL, RN
FROM (SELECT ENAME, SAL, ROWNUM  AS RN FROM 
(SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC))
WHERE RN >=3 AND RN <=5;
    • 이런 식으로도 가능하다. 일반적인 ROWNUM=4 이런 식으로 하면 오라클 자체적으로 다루고 있는 ROWNUM이 안잡히기 때문에 서브쿼리를 이용해서 가상테이블을 한번 더 만들어준다. RN이라는 별칭을 붙여 컬럼을 한번 더 만들어 원하는 값을 접근할 수 있게 만들어 준 것

RANK / DENSE_RANK / ROW_NUMBER

  • RANK는 동일 순위를 표현하고 다음 순위를 나타낼 때 그 동일순위의 인간 수를 반영해서 다음 순위를 나타냄

  • DENSE_RANK는 동일 순위의 인간 수를 반영하지 않고 바로 다음 순위를 나타낸다.

  • ROW_NUMBER는 동일 순위를 표현하지 않고 그냥 무조건 순위를 매긴다.

  • 예시를 보고 이해하는게 빠를 것 같다.

    • SELECT ENAME, SAL, 
RANK() OVER(ORDER BY SAL DESC) AS RANK,
DENSE_RANK() OVER(ORDER BY SAL DESC) AS DESNE,
ROW_NUMBER() OVER(ORDER BY SAL DESC) AS ROWNB
FROM EMP;

    • image

    • 동일 순위 내에서 순서를 따지는 것은 ENAME의 순서를 반영한 것 같다.

중간 퀴즈

--1 사원 테이블에서 사원의 이름을 첫글자는 대문자로, 나머지는 소문자로 출력하자.
SELECT SUBSTR(ENAME,1,1)||SUBSTR(LOWER(ENAME),2) AS GOOD
FROM EMP;

--2 사원 테이블에서 사원의 이름을 출력, 이름의 두 번째 글자부터 네 번째 글자까지 추력
SELECT ENAME, SUBSTR(ENAME, 2,3)
FROM EMP;

--3 사원테이블에서 사원 이름과 근무일수(고용일 ~ 현재 날짜)를 출력 (한달은 30일)
SELECT ENAME, (MONTHS_BETWEEN('2022-03-07', HIREDATE)*30)
FROM EMP;


--4 사원 테이블에서 각 사원 이름의 철자 개수를 출력
SELECT ENAME, LENGTH(ENAME) FROM EMP;

--5 사원 테이블에서 사원의 이름이 M으로 시작하는 사원들의 이름을 출력
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME, 1,1) = 'M';
  • 1번 문제는 INITCAP() 함수를 사용해도 된다.
  • 3번 문제는 현재 날짜에 SYSDATE를 사용해도 된다.
  • 5번 문제는 WHERE절에서 ENAME LIKE 'M%'; 를 사용해도 된다.

SUBQUERY

  • DML이나 다른 SUBQUERY안에 들어가는 SELECT 구문이다.
  • NESTED SELECT 라고도 불린다!
  • 종류
    • SINGLE ROW
    • MULTI ROW
    • MULTI COLUMN
    • INLINE VIEW 등등등~~~

SINGLE ROW SUBQUERY

  • 결과가 1개의 ROW

  • 예시

    • 월급이 JONES보다 큰 경우에 사원이름과 월급을 조회

    • SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');
    • image

MULTI ROW SUBQUERY

  • 결과가 여러 개의 ROW

  • 예시

    • 부하 직원이 없는 사원의 사번과 이름 조회

    • SELECT MGR FROM EMP;

    • image

    • 매니저 번호에 사원번호가 있으면 부하직원이 있다는 뜻

    • SELECT EMPNO, ENAME
FROM EMP
WHERE EMPNO NOT IN (SELECT NVL(MGR, 0) FROM EMP);

    • image

    • NOT IN을 사용해서 다중 값 서브쿼리와 EMPNO를 비교할 수 있다.

MULTI COLUMN SUBQUERY

  • 결과가 여러 개의 COLUMN

  • 예시

    • 조건1 : 직업이 ‘SALESMAN’인 사원과 같은 부서에서 근무

    • 조건2 : 직업이 ‘SALESMAN’인 사원과 같은 월급을 받는 사원들의 이름, 월급, 부서번호를 조회

    • SELECT ENAME, SAL, DEPTNO 
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE JOB = 'SALESMAN');
    • image

INLINE VIEW

  • 결과가 가상테이블로 사용되는 경우

  • 예시

    • 자신의 부서 평균 월급보다 더 많이 받는 사원들의 이름, 월급, 부서번호, 부서별 평균 월급을 조회

    • SELECT E.ENAME, E.SAL, MYDEPT.DEPTNO, MYAVG
FROM EMP E,
	(SELECT DEPTNO, AVG(SAL) AS MYAVG FROM EMP GROUP BY DEPTNO) MYDEPT
WHERE E.DEPTNO = MYDEPT.DEPTNO
AND E.SAL > MYDEPT.MYAVG;

    • 집계함수를 컬럼명으로 쓸 수 없기 때문에 MYAVG로 ALIAS를 잡아줌

    • image

중간 QUIZ

--1. 'CHICAGO'에서 근무하는 사원들과 같은 부서에서 근무하는 사원의 이름과 월급을 출력
-- 내꺼
SELECT ENAME, SAL 
FROM  EMP E, 
(SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO') CHICAGO
WHERE E.DEPTNO = CHICAGO.DEPTNO;



--2. 관리자의 이름이 'KING'인 사원의 이름과 월급을 출력
-- 내꺼
SELECT ENAME, SAL
FROM EMP E, (SELECT EMPNO FROM EMP WHERE ENAME='KING') KING 
WHERE E.MGR = KING.EMPNO;

--강사님꺼
SELECT ENAME, SAL
FROM EMP
WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME='KING');

--3. 전체 사원 중, 20번 부서의 사원 중 가장 많은 월급을 받는 사원보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력
-- 내꺼
SELECT ENAME, SAL
FROM EMP E, (SELECT MAX(SAL) AS MAXSAL FROM EMP GROUP BY DEPTNO HAVING DEPTNO = 20) GOOD
WHERE E.SAL > GOOD.MAXSAL;

-- 강사님꺼
SELECT ENAME, SAL
FROM EMP
WHERE SAL >
(SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 20);



--4. 전체 사원 중, 직업이 'SALESMAN'인 사원 중 가장 많은 월급을 받는 사원보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하되, 
--MAX()함수 사용 X (ANY, ALL 연산자)
-- 내꺼
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');

-- 강사님꺼
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL
	(SELECT SAL
	FROM EMP
	WHERE JOB = 'SALESMAN');



--5. 'BLAKE'가 근무하는 부서의 위치(LOC)를 출력
--내꺼
SELECT LOC
FROM DEPT D, (SELECT DEPTNO FROM EMP WHERE ENAME='BLAKE') BLAKE
WHERE D.DEPTNO = BLAKE.DEPTNO;

--강사님꺼
SELECT LOC
FROM DEPT
WHERE DEPTNO =
(SELECT DEPTNO 
FROM EMP 
WHERE ENAME = 'BLAKE');


--6. 이름에 'S'가 들어가는 사원과 동일한 부서에서 근무하는 사원 중, 자신의 월급이 전체 사원의 평균 월급보다 많은 사원들의 사원번호, 이름, 월급을 출력
--내꺼
SELECT DISTINCT E.EMPNO, E.ENAME, E.SAL
FROM EMP E,(SELECT DEPTNO,SAL FROM EMP WHERE ENAME LIKE '%S%') S, (SELECT AVG(SAL) AVGSAL FROM EMP) AVGSAL 
WHERE E.DEPTNO = S.DEPTNO AND AVGSAL.AVGSAL < E.SAL;

--강사님꺼
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN 
(SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%S%')
AND SAL > (SELECT AVG(SAL) FROM EMP);


--7. 사원번호가 7369인 사원과 같은 직업이고, 사원번호가 7876인 사원보다 많이 받는 사원의 이름과 직업을 출력
-- 내꺼 1
SELECT E.ENAME, E.JOB
FROM EMP E ,(SELECT EMPNO, JOB FROM EMP WHERE EMPNO = 7369) CLERK, (SELECT ENAME, EMPNO, SAL FROM EMP WHERE EMPNO = 7876) NO7876
WHERE NO7876.SAL < E.SAL AND CLERK.JOB = E.JOB;

-- 내꺼 2
SELECT ENAME, JOB FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7369) AND SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7876);


-- 강사님꺼
SELECT ENAME, JOB
FROM EMP
WHERE JOB = 
	(SELECT JOB
	FROM EMP
	WHERE EMPNO = 7369)
AND SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7876);
  • ANY, ALL 참조홈페이지 : https://gent.tistory.com/287
  • 나는 순수한 서브쿼리라기 보다는 조인을 많이 사용했던 것 같다.


JOIN

INNER

  • 동일한 값이 있는 컬럼끼리 조인
  • 두 테이블의 교집합이라고 생각하면 된다.
  • 적는 형식
    • SELECT 컬럼 FROM 테이블A INNER JOIN 테이블B ON 테이블A.컬럼 = 테이블B.컬럼
    • SELECT 컬럼 FROM 테이블A, 테이블B WHERE 테이블A.컬럼 = 테이블B.컬럼
    • SELECT 컬럼 FROM 테이블A INNER JOIN 테이블B USING(컬럼)
      • 컬럼명이 아예 같다면 ON대신 USING을 사용해도 된다!

CROSS

  • CARTESIAN PRODUCTS라 해서 모든 경우의 수를 다 출력해준다.
  • 적는 형식
    • SELECT 컬럼 FROM 테이블A CROSS JOIN 테이블B
    • SELECT 컬럼 FROM 테이블A CROSS JOIN 테이블B

OUTER

  • 기준 테이블의 정을 정하고 테이블의 교집합 부분과 기준 테이블의 값까지 보여주는 것
  • LEFT OUTER JOIN
    • 왼쪽 테이블을 기준으로 조인
    • 적는 형식
      • SELECT 컬럼 FROM 테이블A LEFT OUTER JOIN 테이블B ON 테이블A.컬럼 = 테이블B.컬럼
        • ANSI 표준
      • SELECT 컬럼 FROM 테이블A, 테이블B WHERE 테이블A.컬럼 = 테이블B.컬럼(+)
        • 오라클에서는 LEFT OUTER JOIN할 때 오른쪽 테이블 컬럼 부분에 (+)를 적어주면 된다.
  • RIGHT OUTER JOIN
    • 오른쪽 테이블을 기준으로 조인
    • 적는 형식
      • SELECT 컬럼 FROM 테이블A RIGHT OUTER JOIN 테이블B ON 테이블A.컬럼 = 테이블B.컬럼
        • ANSI 표준
      • SELECT 컬럼 FROM 테이블A, 테이블B WHERE 테이블A.컬럼(+) = 테이블B.컬럼
        • 오라클에서는 RIGHT OUTER JOIN할 때 왼쪽 테이블 컬럼 부분에 (+)를 적어주면 된다.
  • FULL OUTER JOIN
    • 모든 값들을 출력해줌
    • 적는 형식
      • SELECT 컬럼 FROM 테이블A FULL OUTER JOIN 테이블B ON 테이블A.컬럼 = 테이블B.컬럼
      • (+) 형식은 쓰이지 않음

NON EQUI

  • JOIN 할 두 테이블에 일치하는 값이 있는 컬럼이 없을 때

  • 예제

    • 사원(EMP)테이블

    • SELECT * FROM EMP;

    • image

    • 월급(SALGRADE)테이블

    • SELECT * FROM SALGRADE;

    • image

    • 사원들의 월급 등급을 합쳐서 보고싶음

    • SELECT ENAME, SAL, GRADE
FROM EMP E JOIN SALGRADE SG ON (E.SAL BETWEEN SG.LOSAL AND SG.HISAL);
    • image

SELF JOIN

  • JOIN 할 두 테이블에 일치하는 값이 있는 컬럼이 없을 때

  • 자기 자신을 조인함

  • 예제

    • 사원의 관리자 이름과 사번을 가져와보자.

    • SELECT 사원.ENAME, 사원.EMPNO, 관리자.ENAME, 관리자.EMPNO
FROM EMP 사원, EMP 관리자
WHERE 사원.MGR = 관리자.EMPNO;

    • image

    • 관리자가 없는 ‘KING’의 정보까지 가져오고 싶으면 LEFT OUTER JOIN을 해야함

    • 오라클에서는 조인 당하는 쪽에 (+)를 적어주며 된다.

    • SELECT 사원.ENAME, 사원.EMPNO, 관리자.ENAME, 관리자.EMPNO
FROM EMP 사원, EMP 관리자
WHERE 사원.MGR = 관리자.EMPNO(+);

    • image

    • 그러면 관리자가 없는 사원의 정보도 나온다.

    • ANSI 표준 으로 적어줘야한다면

    • SELECT 사원.ENAME, 사원.EMPNO, 관리자.ENAME, 관리자.EMPNO
FROM EMP 사원 LEFT OUTER JOIN EMP 관리자 ON (사원.MGR = 관리자.EMPNO);
    • 이렇게 적어주면 된다.

중간 QUIZ

--QUIZ
--1 사원들의 이름, 부서번호, 부서이름을 출력하자.
--내꺼
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

-- 강사님꺼
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

SELECT ENAME, DEPTNO, DNAME
FROM EMP JOIN DEPT USING(DEPTNO);




--2 ‘DALLAS’에서 근무하는 사원의 이름, 직업, 부서번호, 부서이름을 출력하자.
-- 내꺼
SELECT E.ENAME, E.JOB, E.DEPTNO, D.DNAME
FROM EMP E, (SELECT * FROM DEPT WHERE LOC = 'DALLAS') D
WHERE E.DEPTNO = D.DEPTNO;

-- 강사님꺼
SELECT E.ENAME, E.JOB, D.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE D.LOC = 'DALLAS';

SELECT ENAME, JOB, DEPTNO, DNAME
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE LOC = 'DALLAS';




--3 이름에 ‘A’가 들어가는 사원들의 이름과 부서이름을 출력하자.
-- 내꺼
SELECT E.ENAME, D.DNAME
FROM (SELECT * FROM EMP WHERE ENAME LIKE '%A%') E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

--강사님꺼
SELECT E.ENAME, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.ENAME LIKE '%A%';

SELECT ENAME, DNAME
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE ENAME LIKE '%A%';


--4 사원의 이름과 부서이름, 월급을 출력하되, 월급이 3000 이상인 사원들만 출력하자.
-- 내꺼
SELECT E.ENAME, D.DNAME, E.SAL
FROM (SELECT * FROM EMP WHERE SAL >= 3000) E INNER JOIN DEPT D
USING(DEPTNO);

--강사님꺼
SELECT E.ENAME, D.DNAME, E.SAL
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
AND E.SAL >= 3000;

SELECT ENAME, DNAME, SAL
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE SAL >= 3000;



--5 사원테이블과 급여테이블(SALGRADE)에서 커미션이 책정된 사원들의 사원번호, 이름, 연봉, 연봉+커미션, 급여등급(GRADE)을 출력하자.
-- 내꺼
SELECT E.EMPNO, E.ENAME, E.SAL*12, E.SAL*12+COMM, S.GRADE
FROM (SELECT * FROM EMP WHERE COMM IS NOT NULL) E JOIN SALGRADE S
ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

--강사님꺼
SELECT EMPNO, ENAME, SAL*12, SAL*12+NVL(COMM,0), SG.GRADE
FROM EMP E JOIN SALGRADE SG ON(E.SAL BETWEEN SG.LOSAL AND SG.HISAL)
WHERE COMM IS NOT NULL;

SELECT E.EMPNO, E.ENAME, E.SAL*12, E.SAL*12+NVL(E.COMM,0), SG.GRADE
FROM EMP E, SALGRADE SG
WHERE E.SAL BETWEEN SG.LOSAL AND SG.HISAL
AND E.COMM IS NOT NULL;




--6 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하자.
-- 내꺼
SELECT E.DEPTNO, D.DNAME, E.ENAME, E.SAL, S.GRADE
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO 
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

--강사님꺼
SELECT DEPTNO, DNAME, ENAME, SAL, GRADE
FROM DEPT JOIN EMP USING(DEPTNO) JOIN SALGRADE ON (SAL BETWEEN LOSAL AND HISAL)
WHERE DEPTNO = 10;


SELECT D.DEPTNO, D.DNAME, E.ENAME, E.SAL, SG.GRADE
FROM DEPT D, EMP E, SALGRADE SG
WHERE D.DEPTNO  = E.DEPTNO
AND E.SAL BETWEEN SG.LOSAL AND SG.HISAL
AND E.DEPTNO = 10;



--7 부서번호가 10번이거나 20번인 사원들의 부서번호, 부서이름, 사원이름, 급여등급을 출력하되, 부서번호가 낮은 번호부터, 월급이 높은 순으로 출력하자.
-- 내꺼
SELECT E.DEPTNO, D.DNAME, E.ENAME, S.GRADE
FROM (SELECT * FROM EMP WHERE DEPTNO = 10 OR DEPTNO = 20) E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
ORDER BY E.DEPTNO ASC, E.SAL DESC;

--강사님꺼
SELECT DEPTNO, DNAME, ENAME, GRADE
FROM DEPT JOIN EMP USING(DEPTNO) JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
WHERE DEPTNO IN (10, 20)
ORDER BY DEPTNO, SAL DESC;


--8 사원번호와 이름, 관리자의 사원번호와 관리자이름을 출력하자.
-- 내꺼
SELECT 사원.EMPNO, 사원.ENAME, 관리자.EMPNO, 관리자.ENAME
FROM EMP 사원, EMP 관리자
WHERE 사원.MGR = 관리자.EMPNO(+);

--강사님꺼
SELECT 사원.ENAME, 사원.EMPNO, 관리자.EMPNO, 관리자.ENAME
FROM EMP 사원 JOIN EMP 관리자 ON (사원.MGR = 관리자.EMPNO);



--9 부서이름, 위치, 각 부서의 사원수, 평균 월급을 출력하자.
-- 내꺼
SELECT D.DNAME, D.LOC, COUNT(D.DNAME), AVG(E.SAL)
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME, D.LOC;

--강사님꺼
SELECT DNAME, LOC, COUNT(*), AVG(SAL)
FROM EMP JOIN DEPT USING(DEPTNO)
GROUP BY DNAME, LOC;
  • 내가 풀 땐 주로 ANSI 표준이 아닌 ORACLE식으로 풀었고, 강사님은 모두 설명해주셨다.
  • 조인하고 GROUP BY 를 해줄 때는, SELECT절에서 명시한 컬럼들을 다 적어줘야 한다.


ERD

Baker

  • image

    사진 출처 : https://dataonair.or.kr/db-tech-reference/d-guide/da-guide/?mod=document&uid=278

  • 논리 ERD와 물리 ERD가 같이 나온다.
    • 논리 : 의미명(한글이나 알아보기 쉬운 단어로 표현)
    • 물리 : 실제 컬럼명
  • 속성 구분
    • # : primary key
    • * : not null
    • 0 : null
  • A : B 관계
    • 1 : 1
      • A에 존재하는 데이터 1개 <-> B에 존재하는 데이터 1개
    • 1 : N
      • A에 존재하는 데이터 1개 <-> B에 존재하는 데이터 N개
    • N : M
      • A에 존재하는 데이터 1개 <-> B에 존재하는 데이터 M개
      • A에 존재하는 데이터 N개 <-> B에 존재하는 데이터 1개
  • 관계선
    • 1 : 실선
    • 0 or 1 : 점선
    • 0 or more : 점선에 까치발
    • 1 or more : 실선에 까치발

I/E

  • image

    사진 출처 : https://dataonair.or.kr/db-tech-reference/d-guide/da-guide/?mod=document&uid=278

  • 사각형 구조
    • 첫 칸에는 Primary Key를 넣어줌
      • 만약 첫 칸에 Key가 여러 개라면 복합키
    • 그 밑에 여타 키를 넣어줌
  • A - B 관계
    • A가 없으면 B가 존재할 수 없다.(종속)
      • 실선
    • A가 없어도 B가 존재할 수 있다.(독립)
      • 점선
  • 관계선
    • 0 or 1 : 0과 짝대기( )가 들어간 실선이나 점선
    • 1 : 짝대기( )가 들어간 실선, 점선
    • 0 or more : 0과 짝대기( )와 까치발이 있는 실선 혹은 점선
    • 1 or more : 짝대기( )와 까치발이 있는 실선 혹은 점선

2022

[web]jQuery 복습 3

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 2

13 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 1

14 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리4

5 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리3

10 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리2

7 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리1

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]CSS 기초 정리

11 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]HTML 기초 정리

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[Pandas]pandas 연습

3 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑

2021

[Python기초]module

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑