2021.07.02 수업일지

- SQL(Structured Query language) 다시보기

- 프로시저 : 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어

- SQL(Structured Query language) 본수업

- 함수

Q1. 사원번호가 홀수인 사원 검색

SELECT * FROM EMP
WHERE MOD(EMPNO,2)=1;

Q2. 사원번호가 짝수인 사원 검색

SELECT * FROM EMP
WHERE MOD(EMPNO,2)=0;

SELECT * FROM EMP
WHERE NOT MOD(EMPNO,2)=1;

Q3. 출력한 사원번호 목록을 내림차순으로 정리

SELECT * FROM EMP
WHERE MOD(EMPNO,2)=1
ORDER BY EMPNO DESC;

Q4. 테이블의 구조를 보고 싶을때

DESC EMP;

ㄴ DESC ; DESCRIBE : 특정 테이블에 어떤 컬럼이 있는지 조회하는 명령어

- UPPER LOWER 함수

- UPPER(소문자를 대문자로 변환시켜주는 함수)

SELECT UPPER('hello') FROM DUAL;

- 문자열을 키보드로 입력받는 다고 가정했을때 소문자로 변환하거나 대문자로 변환시켜서 검색하고 출력하는 경우를 생각해보자.

SELECT * FROM EMP
WHERE ENAME = UPPER('ford');

- 테이블에 입력되어 있는 값이 소문자와 대문자로 섞여 있는 경우 동등한 자료형으로 검색하기 위해서는?

SELECT * FROM EMP
WHERE UPPER(ENAME) = UPPER('ford');

- LENGTH함수 : 문자의 길이를 알려주는 함수

- 'HELLO ORACLE'의 문자의 길이를 출력한다면.

SELECT LENGTH('HELLO ORACLE') FROM DUAL;

- '대한 민국'의 문자의 길이를 출력한다면.

SELECT LENGTH('대한민국') FROM DUAL;

- 이름이 3 ~ 6자인 사원 검색

SELECT * FROM EMP
WHERE LENGTH(ENAME) >=3 OR LENGTH(ENAME) >=6;

SELECT * FROM EMP
WHERE LENGTH(ENAME) BETWEEN 3 AND 6;

※ 참고 : WHERE 절은 내용이 참일 경우에만 검색된다.

- INSTR : 문자열 검색 : 문자열(STRING)에서 지정된 문자열을 검색해서 그 위치를 리턴하는 함수이다. 위치는 지정된 문자열이 나타나는 제일 첫번째 위치를 리턴한다.

SELECT INSTR('WELCOME TO KOREA', 'O') FROM DUAL;

ㄴ 첫번째 글자부터 몇번째에 'O'가 있는지 검색해준다. 나머지는 왜 안나오는지는 나중에 알아보자.

- INSTR 함수를 이용해서 이름중에 A문자를 포함하는 사원의 이름 검색

SELECT ENAME FROM EMP
WHERE INSTR(ENAME,'A')!=0;

- SUBSTR : 부분 문자열 검출 ; 정해진 문자를 특정 자릿수만큼 잘라내는 기능을 하는 함수

- WELCOME TO KOREA에서 4번째 부터 6번째까지 문자를 출력한다면

SELECT SUBSTR('WELCOME TO KOREA', 4,6) FROM DUAL;

- WELCOME TO KOREA에서 4번째 부터 문자를 출력한다면

SELECT SUBSTR('WELCOME TO KOREA', 4) FROM DUAL;

- 입사연도를 두자리 출력

SELECT SUBSTR(HIREDATE, 1, 2) FROM EMP;

- 02월에 입사한 사원검색
1) LIKE 사용

SELECT * FROM EMP
WHERE HIREDATE LIKE '___02%';

2) SUBSTR 사용

SELECT * FROM EMP
WHERE SUBSTR(HIREDATE, 4, 2)='02';

- LPAD, RPAD 함수

- LPAD : 오른쪽 정렬 후 특정문자를 왼쪽에 삽입

SELECT LPAD('HELLO', 20, '#') FROM DUAL;
--선택  함수(문자, 글자갯수, 붙일문자) 어디서 듀얼에서;

- RPAD : 왼쪽 정렬 후 특정문자를 오른쪽에 삽입

SELECT RPAD('HELLO', 20, '#') FROM DUAL;

※ 참고 테이블 생성

CREATE TABLE PRODUCT(
NAME NVARCHAR2(100), -- VARCHAR(N) ; N자리 가변문자열
CODE NCHAR(5)        -- 고정 문자열
);

ㄴ N은 꼭 붙이도록 하자. 아래 링크를 보도록 하자.

https://jckim-dev.tistory.com/6

[DB] SQL 다국어 데이터 깨짐 현상 해결

발단 현재 사용 중인 DB는 oracle이며, 이미 생성되어 있는 테이블에 중국어 데이터를 insert 할 작업이 잡혔다. 전달받은 중국어 데이터를 insert 후 정상적으로 데이터가 적재되었는지 확인하였으나

jckim-dev.tistory.com

INSERT INTO PRODUCT(NAME, CODE) VALUES('선풍기', 'N0001');
INSERT INTO PRODUCT(NAME, CODE) VALUES('에어컨', 'N2');
INSERT INTO PRODUCT(NAME, CODE) VALUES('에어컨', 'N'||LPAD(3,4,0));

ㄴ 제품 정보 삽입

SELECT * FROM PRODUCT;

ㄴ 출력

선풍기	N0001
에어컨	N2   
에어컨	N0003

ㄴ 결과

- LTRIM, RTRIM, TRIM 함수

- LTRIM : 문자열의 왼쪽 공백을 삭제

SELECT LTRIM('   HELLO    ') FROM DUAL;

- RTRIM : 문자열의 오른쪽 공백을 삭제

SELECT RTRIM('   HELLO    ') FROM DUAL;

- TRIM : 문자열의 앞 뒤 공백을 삭제

SELECT TRIM('   HELLO    ') FROM DUAL;

- 삭제됐는지 증명

SELECT LENGTH(LTRIM('   HELLO    ')) FROM DUAL;
SELECT LENGTH(RTRIM('   HELLO    ')) FROM DUAL;
SELECT LENGTH(TRIM('   HELLO    ')) FROM DUAL;

- 날짜 관련 함수 ***

- 현재 날자와 시간 ; SYSDATE ***

SELECT SYSDATE FROM DUAL;

- DAY + 1

SELECT SYSDATE+1 FROM DUAL;

- HOUR + 1

SELECT SYSDATE+1/24 FROM DUAL;

ㄴ 날짜는 연산이 가능하다.

- 입사 후 경과일을 계산해보자

SELECT ENAME, SYSDATE-HIREDATE FROM EMP;

- 입사 후 경과일을 반올림해서 계산해보자

SELECT ENAME, ROUND(SYSDATE-HIREDATE) FROM EMP;

- 입사한 달의 마지막 날

SELECT HIREDATE, LAST_DAY(HIREDATE) FROM EMP;

ㄴ 날짜에 관한 함수는 구글링해보기

- 형변환 함수 ****

- TO_DATE : 숫자, 문자를 날짜형으로 변환해주는 함수

SELECT SYSDATE-'2002/01/01' FROM DUAL;

ㄴ 이처럼 하면 안된다. 형식은 맞지만 문자열로 인식되기 때문이다.

SELECT SYSDATE-TO_DATE('2002/01/01') FROM DUAL;

ㄴ 이처럼 하면 된다.

SELECT SYSDATE-TO_DATE('2002/01/01', 'YYYY/MM/DD') FROM DUAL;

ㄴ 날짜 뒤에 형식(FORMAT)을 넣을 수 있다.

- TO_CHAR : 날짜, 숫자를 문자형으로 변환해주는 함수

SELECT SYSDATE FROM DUAL;

ㄴ 이를 원하는 형식으로 변환해보자.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

ㄴ ALIAS를 추가해보자.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') DAY FROM DUAL;

ㄴ 시분초를 추가해보자.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD, HH24:MI:SS') DAY FROM DUAL

- TO_NUMBER : 문자를 숫자로 변환 / 참고만 하도록 하자.

※ 참고

SELECT ENAME, SAL FROM EMP;

ㄴ SAL의 형식을 1,000와 같이 ,를 넣고 싶다면?

SELECT ENAME, TO_CHAR(SAL, '999,999') FROM EMP;

ㄴ 9은 일반적인 숫자를 나타낸다.
ㄴ 화폐 단위를 붙이고 싶다면?

SELECT ENAME, TO_CHAR(SAL, 'L999,999') FROM EMP;

ㄴ L은 지역 통화 단위를 붙인다. 한국은 \ 미국은 $ ETC.

※ 참고

- 변환형 함수
TO_CHAR : 숫자나 날짜를 문자열로 변환
TO_NUMBER : 문자를 숫자로 변환
TO_DATE : 문자를 날짜로 변환

- TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소
9 : 일반적인 숫자를 나타냄
0 : 앞의 빈자리를 0으로 채움
$ : dollar를 표시함
L : 지역 통화 단위(ex )
. : 소숫점을 표시함
, : 천단위를 표시함

- TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소
SCC : 세기를 표시 S는 기원전(BC)
YEAR : 연도를 알파벳으로 spelling
YYYY : 4자리 연도로 표시
YY : 끝의 2자리 연도로 표시
MONTH : 월을 알파벳으로 spelling
MON : 월의 알파벳 약어
MM : 월을 2자리 숫자로 표시
DAY : 일에 해당하는 요일
DY : 일에 해당하는 요일의 약어
DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시
HH , HH24 : (1-12) , (0-23)중의 시간을 표시
MI : 분을 표시
SS : 초를 표시
AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시

TO_CHAR(문자값,‘형식’)
숫자를 문자로 변환 : TO_CHAR(350000,'$999,999')→ $350,000
숫자를 날짜로 변환 : TO_CHAR(SYSDATE,'YY/MM/DD')→ 95/05/25
TO_DATE(문자값, ‘형식’) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY')→10-SEP-92
TO_NUMBER(문자값) : TO_NUMBER('1234')→ 1234

- NVL함수 : NULL값을 특정값으로 변환

SELECT SAL, COMM, SAL*12+NVL(COMM,0) ANNUAL FROM EMP;

- 커미션 받는 사원과 안받는 사원 각각 검색

SELECT ENAME, COMM FROM EMP
WHERE NVL(COMM,0)!=0;

SELECT ENAME, COMM FROM EMP
WHERE NVL(COMM,0)=0;

SELECT * FROM EMP
WHERE COMM IS NULL OR COMM =0;

- 대표의 사번이 없을때 NULL값을 CEO로 변환한다면

SELECT NVL(TO_CHAR(MGR, '9999'), 'CEO') FROM EMP;

- DECODE 함수 ; JAVA의 IF문과 같은 함수 ; 같은 연산자만 사용가능하고 나머지는 불가능하다.

SELECT ENAME, DEPTNO,
    DECODE(DEPTNO, 10, '영업부',
                    20, '총무부',
                    30, '인사부',
                    40, '관리부') DNAME
FROM EMP;

ㄴ DECODE(콜론명, 콜론데이터, 삽입명) 가 콜론데이터를 확인하고 참이면 삽입명을 만드는 프로세스이다.

SELECT ENAME, DEPTNO,
    DECODE(JOB, 'CLERK', SAL*1.1,
                    'SALESMAN', SAL*1.2,
                    'MANAGER', SAL*0.9,
                         SAL) UPSAL
FROM EMP;

ㄴ 계산도 가능하다.

- CASE 함수 ; 연산자를 사용해서 조건을 붙일 수 있다.

SELECT ENAME, DEPTNO,
    CASE WHEN DEPTNO=10 THEN '영업부'
    WHEN DEPTNO=20 THEN '관리부'
    WHEN DEPTNO=30 THEN '총무부'
    WHEN DEPTNO=40 THEN '인사부'
    END AS DNAME
FROM EMP;

※ 참고

SELECT *, SYSDATE FROM EMP; -- 안된다.
SELECT EMP.*, SYSDATE FROM EMP; --된다.

Q. 문제 급여가 1000이상이면 30% 2000이상이면 20% 3000이상이면 10% 나머지는 50% 인상된 급여와 이름를 출력하시오.

나)

SELECT ENAME, SAL,
    CASE 
    WHEN SAL BETWEEN 1000 AND 1999 THEN SAL*1.3
    WHEN SAL BETWEEN 2000 AND 2999 THEN SAL*1.2
    WHEN SAL>=3000 THEN SAL*1.1
    ELSE SAL*1.5
    END AS UPSAL
FROM EMP;

선생님)

SELECT ENAME, SAL,
    CASE 
    WHEN SAL >= 3000 THEN SAL*1.1
    WHEN SAL >= 2000 THEN SAL*1.2
    WHEN SAL >= 1000 THEN SAL*1.3
    ELSE SAL*1.5
    END AS UPSAL
FROM EMP;

- 그룹함수 : MAX, MIN, SUM, AVG, COUNT 5개

- 급여의 최대값, 최소값, 합계, 평균, 갯수를 출력한다면?

SELECT MAX(SAL), MIN(SAL), SUM(SAL), AVG(SAL), COUNT(SAL)
FROM EMP;

- 급여의 최대값, 최소값을 구하고 총합계를 카운터함수로 나누어 평균을 구하면

SELECT MAX(SAL), MIN(SAL), ROUND(SUM(SAL)/COUNT(SAL)) AVG
FROM EMP;

- 성과급을 구하는데 NULL값이 있을경우

SELECT MAX(COMM), MIN(COMM), ROUND(SUM(COMM)/COUNT(*)) AVG12
,AVG(COMM) AVG4, COUNT(*) 레코드수
FROM EMP;

ㄴ 이와 같을때는 NULL값을 제외하고 계산하게 된다.
ㄴ COUNT(*)는 레코드의 수를 나타낸다.
ㄴ NVL을 이용해도 된다.

SELECT MAX(COMM), MIN(COMM), ROUND(SUM(COMM)/COUNT(*)) AVG12
,AVG(NVL(COMM,0)) AVG12, COUNT(*) 레코드수
FROM EMP;

- 문제1) 부서번호가 30인 사원 중에서 커미션을 받는 사원수

나)

SELECT COUNT(COMM) GETCOMM
FROM EMP
WHERE DEPTNO = 30 AND NVL(COMM,0)!=0;

선생님)

SELECT COUNT(*)FROM EMP
WHERE DEPTNO = 30 AND NVL(COMM,0)!=0;

- 문제2) 직급(JOB) 개수 출력.

SELECT COUNT(DISTINCT JOB)
FROM EMP;

- GROUP BY 함수

- 직급별 최고 연봉

SELECT MAX(SAL) FROM EMP
GROUP BY JOB;

ㄴ 직급을 붙이고 싶다면

SELECT MAX(SAL), JOB FROM EMP
GROUP BY JOB;

ㄴ 위와 같이 테이블 컬럼의 갯수가 일치해야 예로 5:5이어야 출력이 된다.

- 부서별 최고 금액

SELECT MAX(SAL), DEPTNO FROM EMP
GROUP BY DEPTNO;

ㄴ 부서 오름차순으로 한다면

SELECT MAX(SAL), DEPTNO FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

- 문) 부서별로 전체 사원수와 커미션을 받는(0포함) 사원수 출력

SELECT DEPTNO, COUNT(*), COUNT(COMM)
FROM EMP
GROUP BY DEPTNO;

- 문) 각 부서 내에서 직무별 최대 급여

SELECT DEPTNO,JOB, MAX(SAL)
FROM EMP
GROUP BY DEPTNO, JOB -- 순서바뀌면 안됨
ORDER BY DEPTNO;

ㄴ 부서안에서 직급별로 묶는다.

- 부서별 급여 평균

SELECT DEPTNO, ROUND(AVG(SAL),0)
FROM EMP
GROUP BY DEPTNO;

- 부서별 평균 급여 중 2000이상

SELECT DEPTNO, ROUND(AVG(SAL),0)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000;

ㄴ WHERE 그룹전 일반조건
ㄴ HAVING 구룹후 조건

- 부서별 평균 급여 중 2000이상이며 부서를 오름차순으로 정렬

SELECT DEPTNO, ROUND(AVG(SAL),0)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000
ORDER BY DEPTNO;

-- 문) JOB이 SALESMAN이 아닌 사원 중에서 JOB 별로 총 급여가 5000이상인 JOB의 급여 합계를 출력 하시오. 단 급여합계 오름정렬 하시오.

SELECT JOB, SAL+NVL(COMM,0) TOTSAL
FROM EMP
WHERE JOB!='SALESMAN'
GROUP BY JOB, SAL, COMM
HAVING SAL+NVL(COMM,0)>= 5000;

ㄴ 문제를 잘못이해함

선생님)

SELECT JOB, SUM(SAL)
FROM EMP
WHERE JOB!='SALESMAN'
GROUP BY JOB
HAVING SUM(SAL)>=5000
ORDER BY SUM(SAL);

- SELECT 문 순서

- FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
- ROWNUM : 행번호 출력

SELECT ENAME, SAL*12 ANNUAL
FROM EMP
WHERE ANNUAL>=15000; -- ALIAS는 WHERE에서 쓸 수 없음
-- SELECT 이전에 ALIAS는 설정 전이기 때문에 WHERE에서 쓸 수 없다.
-- ANNUAL이 SAL*12로 명명이 되기 전이기 때문이다.

SELECT ROWNUM RN, ENAME, SAL
FROM EMP
ORDER BY SAL;

ㄴ 이렇게 하면 셀렉트 순에 따라서 다음과 같이 나온다.

1	SMITH	800
10	JAMES	950
3	WARD	1250
5	MARTIN	1250
12	MILLER	1300
9	TURNER	1500
2	ALLEN	1600
7	CLARK	2450
6	BLAKE	2850
4	JONES	2975
11	FORD	3000
8	KING	5000

ㄴ ORDER BY가 가장 나중에 적용되기 때문이다.
ㄴ 이와 관련되서 해결되는 방법은 나중에 알아보도록 하자.

- JOIN : 두 개 이상의 테이블을 결합하여 "새로운 테이블"을 생성, DB의 꽃

1. 내부(INNER)조인 : 일치하는 행끼리 조인

1_1. 동일(EQUI) 조인 : '=' 연산자 사용(90% 사용)

SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

ㄴ 조인은 되지만 중복되는 행도 있고 부자연스럽다.

SELECT ENAME, DEPTNO, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

ㄴ "column ambiguously defined" EMP, DEPT중 어떤 DEPTNO를
ㄴ 출력하라는 건지 정확하지 않기때문에 오류가 발생

SELECT ENAME, DEPT.DEPTNO, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

ㄴ 이처럼 명확하게 어떤 테이블의 데이터를 기준으로 할건지 정해야한다.

SELECT EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP E, DEPT D -- AS를 붙이지 않는다.
WHERE E.DEPTNO=D.DEPTNO; -- ALIAS후 별칭으로 지칭할 수 있다.

ㄴ 하지만 위 명명법은 오라클 조인이다. 표준 SQL 조인이 아니다.

- 표준 SQL 조인

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;

ㄴ INNER 생략도 가능하다.

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO -- 조인 조건이 먼저온다.
WHERE SAL>=1000; -- 일반 조건

ㄴ WHERE 조건은 JOIN 조건이 먼저 온 후에 쓴다.

1_2. 비동일(NON-EQUI) 조인 : >=, <=, >, <

SELECT * FROM SALGRADE; -- 호봉별 급여구간 테이블

ㄴ 위 테이블을 이용해서 NON-EQUI 조인을 설명해보자.

SELECT *
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

ㄴ 필요한 값만 출력해보자.

SELECT E.ENAME, E.SAL, S.GRADE||'호봉' GRADE
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

ㄴ 결과는 다음과 같다.

SMITH	800	1호봉
JAMES	950	1호봉
WARD	1250	2호봉
MARTIN	1250	2호봉
MILLER	1300	2호봉
TURNER	1500	3호봉
ALLEN	1600	3호봉
CLARK	2450	4호봉
BLAKE	2850	4호봉
JONES	2975	4호봉
FORD	3000	4호봉
KING	5000	5호봉

- 세개 이상의 테이블을 조인할때 오라클과 표준의 차이

-- 오라클 조인
SELECT E.ENAME, E.SAL, D.DNAME, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT E.ENAME, E.SAL, D.DNAME, S.GRADE
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO -- 1차 조인
JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL; --2차 조인

1_3. 자가(SELF) 조인 : 자기 자신의 테이블간의 조인

- 원본테이블과 복제테이블을 만든다. 이 복제테이블과 원본을 조인한다. 원본 테이블에서의 상관 컬럼과 복제 테이블의 사원 테이블을 비교해서 매치 시키면

- EMP 테이블의 직원의 상관 이름을 붙여주고 싶다면?

SELECT *
FROM EMP E JOIN EMP M
ON E.MGR=M.EMPNO;

ㄴ 필요한 데이터만 추려보자.

SELECT E.ENAME||'의 상관은 '||M.ENAME MGR
FROM EMP E JOIN EMP M
ON E.MGR=M.EMPNO;

FORD의 상관은 JONES
JAMES의 상관은 BLAKE
TURNER의 상관은 BLAKE
MARTIN의 상관은 BLAKE
WARD의 상관은 BLAKE
ALLEN의 상관은 BLAKE
MILLER의 상관은 CLARK
CLARK의 상관은 KING
BLAKE의 상관은 KING
JONES의 상관은 KING
SMITH의 상관은 FORD

ㄴ 대표는 상관이 없어서 누락되었다.
ㄴ 어떻게 해결해야할까. 외부조인 2_4로 가자

2. 외부(OUTER)조인 : 일치하지 않는 행까지 조인

2_1. LEFT OUTER JOIN : 왼쪽 테이블을 값에 NULL추가

2_2. RIGHT OUTER JOIN : 오른쪽 테이블 값에 NULL추가

2_3. FULL OUTER JOIN : 양쪽 테이블 값에 NULL추가

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;
-- 오른쪽 기준으로 왼쪽에 데이터가 없으면 RIGHT

SMITH	20	RESEARCH
ALLEN	30	SALES
WARD	30	SALES
JONES	20	RESEARCH
MARTIN	30	SALES
BLAKE	30	SALES
CLARK	10	ACCOUNTING
KING	10	ACCOUNTING
TURNER	30	SALES
JAMES	30	SALES
FORD	20	RESEARCH
MILLER	10	ACCOUNTING
	40	OPERATIONS

ㄴ 밀러 하단에 아무것도 안보이지만 RIGHT JOIN으로 인해 NULL이 추가된것이다.
ㄴ 누락된 40번 부서를 호출가능하게 됐다.

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM DEPT D LEFT JOIN EMP E
ON E.DEPTNO=D.DEPTNO;
-- 왼쪽 기준으로 오른쪽에 데이터가 없으면 LEFT

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM DEPT D FULL JOIN EMP E
ON E.DEPTNO=D.DEPTNO;

ㄴ 여기서는 RIGHT의 결과와 같으니 참고하자. FROM에서의 테이블 위치가 RIGHT인지 LEFT인지 결정한다.

※ 참고

-- 오라클
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM DEPT D, EMP E
WHERE E.DEPTNO(+)=D.DEPTNO;
-- 남는 데이터가 데이터가 없는쪽에 (+)을 붙이면 된다.

SMITH	20	RESEARCH
ALLEN	30	SALES
WARD	30	SALES
JONES	20	RESEARCH
MARTIN	30	SALES
BLAKE	30	SALES
CLARK	10	ACCOUNTING
KING	10	ACCOUNTING
TURNER	30	SALES
JAMES	30	SALES
FORD	20	RESEARCH
MILLER	10	ACCOUNTING
	40	OPERATIONS

2_4 자가 조인

- 표준 조인

SELECT E.ENAME||'의 상관은 '||M.ENAME MGR
FROM EMP E LEFT JOIN EMP M
ON E.MGR=M.EMPNO;

- 오라클 조인

SELECT E.ENAME||'의 상관은 '||M.ENAME MGR
FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO(+);

ㄴ 오라클 조인과 표준 조인을 잘 알아두도록 하자.

-- 문제1) 사원이름, 부서번호, 부서명 출력

SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;

-- 문제2) 부서번호가 30번인 사원의 이름, 직급, 부서이름, 부서위치 출력

SELECT E.ENAME, E.JOB, E.DEPTNO, D.LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE E.DEPTNO=30;

-- 문제3) COMM을 받는 사원의 이름, 커미션, 부서이름, 부서위치 출력

SELECT E.ENAME, E.COMM, D.DNAME, D.LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE NVL(E.COMM,0)!=0;

-- 문제4) 이름에 A가 들어가는 사원의 이름, 부서이름 출력

SELECT E.ENAME, D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.ENAME LIKE '%A%';

-- 문제5) 사원이름, 직급, 부서명, 호봉 출력

SELECT * FROM SALGRADE;

SELECT E.ENAME, E.JOB, D.DNAME, S.GRADE
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN SALGRADE S
ON E.SAL BETWEEN LOSAL AND HISAL;

-- 문제6) 사원이름, 부서번호와 해당 사원과
-- 같은 부서에 근무하는 사원이름, 부서번호 출력(셀프조인)
-- 셀프 조인 사용
-- 자신 부서번호   동료 부서번호
-- 스미스 20      존스 20
-- 스미스 20      포드 20
-- 자기 자신과 동료인 경우는 제외

SELECT E.ENAME 자신, E.DEPTNO 부서번호, C.ENAME 동료, C.DEPTNO 부서번호
FROM EMP C JOIN EMP E
ON E.DEPTNO = C.DEPTNO
WHERE E.ENAME!=C.ENAME;

저작자표시

'ICIA 수업일지' 카테고리의 다른 글

2021.07.06 수업일지 (0)	2021.07.10
2021.07.05 수업일지 (0)	2021.07.10
2021.07.01 수업일지 (0)	2021.07.03
2021.06.30 수업일지 (0)	2021.07.03
2021.06.29 수업일지 (0)	2021.07.03

나의 웹 공부 이야기

2021.07.02 수업일지

- SQL(Structured Query language) 다시보기

- SQL(Structured Query language) 본수업

- 함수

- UPPER LOWER 함수

- LENGTH함수 : 문자의 길이를 알려주는 함수

- SUBSTR : 부분 문자열 검출 ; 정해진 문자를 특정 자릿수만큼 잘라내는 기능을 하는 함수

- LPAD, RPAD 함수

- LTRIM, RTRIM, TRIM 함수

- 날짜 관련 함수 ***

- 형변환 함수 ****

- DECODE 함수 ; JAVA의 IF문과 같은 함수 ; 같은 연산자만 사용가능하고 나머지는 불가능하다.

- CASE 함수 ; 연산자를 사용해서 조건을 붙일 수 있다.

- 그룹함수 : MAX, MIN, SUM, AVG, COUNT 5개

- GROUP BY 함수

- SELECT 문 순서

- JOIN : 두 개 이상의 테이블을 결합하여 "새로운 테이블"을 생성, DB의 꽃

1. 내부(INNER)조인 : 일치하는 행끼리 조인

2. 외부(OUTER)조인 : 일치하지 않는 행까지 조인

'ICIA 수업일지' 카테고리의 다른 글

티스토리툴바

2021.07.02 수업일지

- SQL(Structured Query language) 다시보기

- SQL(Structured Query language) 본수업

- 함수

- UPPER LOWER 함수

- LENGTH함수 : 문자의 길이를 알려주는 함수

- SUBSTR : 부분 문자열 검출 ; 정해진 문자를 특정 자릿수만큼 잘라내는 기능을 하는 함수

- LPAD, RPAD 함수

- LTRIM, RTRIM, TRIM 함수

- 날짜 관련 함수 ***

- 형변환 함수 ****

- DECODE 함수 ; JAVA의 IF문과 같은 함수 ; 같은 연산자만 사용가능하고 나머지는 불가능하다.

- CASE 함수 ; 연산자를 사용해서 조건을 붙일 수 있다.

- 그룹함수 : MAX, MIN, SUM, AVG, COUNT 5개

- GROUP BY 함수

- SELECT 문 순서

- JOIN : 두 개 이상의 테이블을 결합하여 "새로운 테이블"을 생성, DB의 꽃

1. 내부(INNER)조인 : 일치하는 행끼리 조인

2. 외부(OUTER)조인 : 일치하지 않는 행까지 조인

'ICIA 수업일지' 카테고리의 다른 글

관련글

티스토리툴바