SELF JOIN, 서브쿼리 21. 03. 10.

-- 교수 중에서 담당 학생이 없는 교수 목록, 교수 번호, 교수 이름

SELECT DISTINCT P.PROFNO, P.NAME
FROM PROFESSOR P
LEFT JOIN STUDENT S
ON P.PROFNO = S.PROFNO
WHERE S.PROFNO IS NULL;

SELF JOIN

- 하나의 테이블 내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용
- 조인 대상 테이블이 자신 하나라는 것 외에는 EQUI JOIN과 동일

• WHERE 절을 사용한 SELF JOIN
- 한 테이블에서 두 개의 칼럼을 연결하여 EQUI JOIN
- FROM 절에서 하나의 테이블에 테이블 별명 지정

• JOIN~ ON 절을 사용한 SELF JOIN

-- 부서 테이블에서 SELF JOIN을 이용하여 부서 이름과 상위부서의 이름을 출력하여라.
-- 몇 개 출력될까? COLLEGE 값이 널이 아닌 값만 출력

SELECT D1.DNAME || '의 상위 부서는 ' || NVL(D2.DNAME, '없습니다.')
FROM DEPARTMENT D1, DEPARTMENT D2
WHERE D1.COLLEGE = D2.DEPTNO(+);

-- ANSI 조인 형태로 변경

SELECT D1.DNAME || '의 상위 부서는 ' || NVL(D2.DNAME, '없습니다.')
FROM DEPARTMENT D1
LEFT JOIN DEPARTMENT D2
ON D1.COLLEGE = D2.DEPTNO;

- '부서 번호가 201 이상인'을 추가하면 WHERE 절에 AND 하고 D1.DEPTNO >= 201 하면 됨.

서브쿼리

- 하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL 명령문을 하나의 SQL 명령문으로 연결하여 처리하는 방법

단일행 서브쿼리

- 서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문

- 메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우, 단일 행 비교 연산자 : = , >, >=, <, <>, <= 중 하나만 사용해야 함.

- 서브쿼리의 결과로 하나의 행만이 출력되어야 함.

• 방법1. 서브쿼리의 조건절에서 기본키나 고유키를 = 비교하는 방식
• 방법2. 서브쿼리의 SELECT 절에서 전체 집합을 대상으로 그룹 함수 사용

-- 사용자 아이디가 jun123인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하여라

SELECT STUDNO, NAME, GRADE
FROM STUDENT
WHERE GRADE =
(
    SELECT GRADE
    FROM STUDENT
    WHERE USERID = 'jun123'
);

-- 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과 번호, 몸무게를 출력

SELECT NAME, DEPTNO, WEIGHT
FROM STUDENT
WHERE WEIGHT <
(
    SELECT AVG(WEIGHT)
    FROM STUDENT
    WHERE DEPTNO = 101
)
ORDER BY 2;

-- 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름, 학년, 키를 출력

SELECT NAME, GRADE, HEIGHT
FROM STUDENT
WHERE GRADE =
(
    SELECT GRADE
    FROM STUDENT
    WHERE STUDNO = 20101
)
AND HEIGHT >
(
    SELECT HEIGHT
    FROM STUDENT
    WHERE STUDNO = 20101
);

다중 행 서브쿼리

- 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리

- 메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우, 다중 행 비교 연산자 사용 : IN, ANY, ALL, EXISTS

- 다중 행 비교 연산자는 단일 행 비교 연산자와 결합하여 사용 가능

-- 정보미디어학부 (부서 번호:100)에 소속된 모든 학생의 학번, 이름, 학과 번호를 출력하여라

SELECT STUDNO, NAME, DEPTNO
FROM STUDENT
WHERE DEPTNO IN
(
    SELECT DEPTNO
    FROM DEPARTMENT
    WHERE COLLEGE =
    (
        SELECT DEPTNO
        FROM DEPARTMENT
        WHERE DNAME = '정보미디어학부'
    )
);

ANY 연산자를 이용한 다중 행 서브쿼리

- >, < 등과 같은 범위 비교도 가능

- ANY 연산자를 이용한 서브쿼리의 예

-- 모든 학생 중에서 4학년 학생 중에서 키가 제일 작은 학생보다 키가 큰 학생의
-- 학번, 이름, 키를 출력하여라

SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > ANY (
    SELECT HEIGHT
    FROM STUDENT
    WHERE GRADE = 4
);

- 단일 행 서브쿼리로 바꾸면

SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT >
(
    SELECT MIN(HEIGHT)
    FROM STUDENT
    WHERE GRADE = 4
);

ALL 연산자를 이용한 다중 행 서브쿼리

- ANY 연산자와 차이점

• > ANY : 서브쿼리 결과 중에서 최소 값보다 크면 메인쿼리의 비교 조건이 참. ( 부등호가 <면 '최대 값보다 크면')

• > ALL : 서브쿼리의 결과 중에서 최대 값보다 크면 메인쿼리의 비교 조건이 참

EXISTS 연산자를 이용한 다중 행 서브쿼리

- 서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자
- 서브쿼리에서 검색된 결과가 존재하지 않으면 메인쿼리의 조건절은 거짓
• '선택된 레코드가 없습니다'라는 메시지 출력

NOT EXISTS
• EXISTS와 상반되는 연산자

-- 보직수당을 받는 교수가 한 명이라도 있으면 모든 교수의
-- 교수 번호, 이름, 보직수당 그리고 급여와 보직수당의 합을 출력하여라

SELECT PROFNO, NAME, COMM, SAL+COMM
FROM PROFESSOR
WHERE EXISTS
(
    SELECT *
    FROM PROFESSOR
    WHERE COMM IS NOT NULL
);

-- 학생 중에서 goodstudent이라는 사용자 아이디가 없으면 1을 출력하여라

SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
    SELECT USERID
    FROM STUDENT
    WHERE USERID = 'goodstudent'
);

다중 칼럼 서브 쿼리

- 서브쿼리에서 여러 개의 칼럽 값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리

- 메인쿼리의 조건절에서도 서브쿼리의 칼럼 수만큼 지정해야 함

• PAIRWISE : 칼럼을 쌍으로 묶어서 동시에 비교하는 방식
• UNPAIRWISE : 칼럼별로 나누어서 비교한 후, AND 연산을 하는 방식

-- PAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라.

SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE (GRADE, WEIGHT) IN
    (
    SELECT GRADE, MIN(WEIGHT)
    FROM STUDENT
    GROUP BY GRADE
    )
ORDER BY 2;

-- UNPAIRWISE 비교방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라.

SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE GRADE IN
    (
    SELECT GRADE
    FROM STUDENT
    GROUP BY GRADE
    )
AND WEIGHT IN
    (
    SELECT MIN(WEIGHT)
    FROM STUDENT
    GROUP BY GRADE
    )
ORDER BY 2;

상호연관 서브쿼리

- 메인쿼리절과 서브커리간에 검색 결과를 교환하는 서브쿼리

- 메인쿼리와 서브쿼리간의 결과를 교환하기 위해 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결

• 행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리 성능이 저하될 수 있음.

-- 각 학과 학생의 평균키보다 키가 큰 학생의 이름, 학과번호, 키를 출력하여라

SELECT NAME, DEPTNO, HEIGHT
FROM STUDENT S1
WHERE HEIGHT >
(
    SELECT AVG(HEIGHT)
    FROM STUDENT S2
    WHERE S1.DEPTNO = S2.DEPTNO -- 이 안에서 S1을 모르기 때문에 어려움.
);

1. 메인쿼리에서 학생 테이블의 학과 번호를 읽어서 서브쿼리로 전달

2. 메인쿼리에서 전달받은 학과 번호로 서브쿼리에서 학과의 평균 키를 계산

3. 메인쿼리에서 해당 학과의 평균 키보다 큰 학생의 이름, 학과 번호, 키를 출력

<SCOTT 계정에서 실습>

-- Blake와 같은 부서에 있는 모든 사원에 대해서 사원 이름과 입사일을 디스플레이하라.

SELECT ENAME, HIREDATE, DEPTNO
FROM EMP
WHERE DEPTNO =
(
    SELECT DEPTNO
    FROM EMP
    WHERE INITCAP(ENAME) = 'Blake'
);

-- 평균 급여 이상을 받는 모든 사원에 대해서 사원번호와 이름을 출력.
-- 단 출력은 급여내림차순 정렬하라.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL >
(
    SELECT AVG(SAL)
    FROM EMP
)
ORDER BY SAL DESC;

-- 부서번호와 급여가 보너스를 받는 어떤 사원의 부서번호와
-- 급여에 일치하는 사원의 이름, 부서번호 그리고 급여를 디스플레이하라.

SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE (DEPTNO, SAL) IN
(
    SELECT DEPTNO, SAL
    FROM EMP
    WHERE COMM IS NOT NULL
);

Scalar Subquery

- 유효한 수식이 쓰일 수 있는 모든 곳에서 사용 가능

- 오직 하나의 값만 반환

- 대량의 데이터의 경우 성능 저하 가능

1. Select List에서의 Scalar Subquery

2. Where 절에서의 Scalar Subquery

3. Order By 절에서의 Scalar Subquery

4. CASE 수식에서의 Scalar Subquery - 많이 씀

5. 함수에서의 Scalar Subquery

데이타 조작어 (DML: Data Manipulation Language)

- 테이블에 새로운 데이터를 입력하거나 기존 데이터를 수정 또는 삭제하기 위한 명령어

INSERT, UPDATE, DELETE, MERGE

- 트랜잭션 : 여러 개의 명령문을 하나의 논리적인 작업단위로 처리하는 기능

COMMIT, ROLLBACK

※ DDL (데이터 정의어, Data Definition Language) : 데이터베이스를 정의하는 언어이며, 데이터리를 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할.
- 종류 : CREATE, ALTER, DROP, TRUNCATE
- SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용.

단일 행 입력

- INTO 절에 칼럼을 명시하지 않으면 테이블 생성시 정의한 칼럼 순서와 동일한 순서로 입력

-- 묵시적인 방법을 이용하여 부서테이블의 부서번호와 부서이름을 입력하고 나머지칼럼은 NULL을 입력하여라

INSERT INTO DEPARTMENT(DEPTNO, DNAME)
VALUES (300, '생명공학부');

-- 명시적인 방법을 이용하여 부서테이블의 부서번호와 부서이름을 입력하고 나머지칼럼은 NULL을 입력하여라

INSERT INTO DEPARTMENT
VALUES (301, '환경보건학과', '', NULL);

- 열이 네 개라 네 개 다 써줘야 함.

-- 9902 최윤식 조교수 2006/01/01 102학과 교수데이터 입력하시오.
INSERT INTO PROFESSOR(PROFNO, NAME, POSITION, HIREDATE, DEPTNO)
VALUES(9902, '최윤식', '조교수', TO_DATE('2006/01/01', 'YYYY/MM/DD'), 102);

SELECT * FROM PROFESSOR;

-- 9910, 백미선, white 전임강사, 200 sysdate 10, 101
INSERT INTO PROFESSOR
VALUES(9910, '백미선', 'white', '전임강사', 200, TRUNC(SYSDATE), 10, 101);

SELECT TO_CHAR(HIREDATE, 'YY/MM/DD HH24:MI:SS') FROM PROFESSOR;

- 날짜 데이터는 TO_DATE 함수 쓰는 버릇 들이기

- SYSDATE는 초까지 나오기 때문에 TRUNC(버림) 해줘야 함

다중 행 입력

- INSERT 명령문에서 서브쿼리 절을 이용

• unconditional INSERT ALL
• conditional INSERT ALL
• conditional FIRST INSERT
• pivoting INSERT

-- 테이블 복사
CREATE TABLE T_STUDENT
AS SELECT * FROM STUDENT;

-- 테이블 스키마만 복사
CREATE TABLE T_STUDENT
AS SELECT * FROM STUDENT WHERE 1=0;

INSERT INTO T_STUDENT
SELECT * FROM STUDENT;

- DOL
CREATE, ALTER, DROP >> 객체(TABLE, USER, VIEW, INDEX .. ) 제어

- DML
INSERT, UPDATE, DELETE >> 데이터 제어

- DCL
ROLLBACK COMMIT >> 데이터 제어

다중 행 입력 - INSERT ALL

- 서브쿼리의 결과 집합을 조건 없이 여러 테이블에 동시에 입력

- 서브쿼리의 컬럼 이름과 데이터가 입력되는 테이블의 칼럼이 반드시 동일해야 함

ALL : 서브쿼리의 결과 집합을 해당하는 INSERT 절에 모두 입력

FIRST : 서브쿼리의 결과 집합을 해당하는 첫번째 INSERT 절에 입력

subquery : 입력 데이터 집합을 정의하기 위한 서브쿼리

-- 학생테이블에서 2학년 이상의 학생을 검색하여 height_info 테이블에는 학번, 이름, 키,
-- weight_info 테이블에는 학번, 이름, 몸무게를 각각 입력하여라

INSERT ALL
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT *
FROM STUDENT
WHERE GRADE >=2;

- SELECT : 결과값의 타입? 결과 집합(표)
- INSERT, UPDATE, DELETE : 결과값의 타입? 반영 행의 개수

다중 행 입력 - Conditional INSERT ALL

- 서브쿼리의 결과 집합에 대해 WHEN 조건절에서 지정한 조건을 만족하는 행을 해당되는 테이블에 각각 입력

- 서브쿼리에서 검색된 행을 만족하는 조건이 여러 개일 경우 해당 테이블에 모두 입력

- ALL : WHEN~THEN~ELSE의 조건을 만족하는 서브쿼리의 모든 검색 결과를 입력하기 위한 옵션

- WHEN 조건절 THEN : 서브쿼리의 결과 집합에 대한 비교 조건

-- 학생테이블에서 2학년 이상의 학생을 검색하여
-- height_info 테이블에는 키가 170보다 큰 학생의 학번, 이름, 키를 입력하고
-- weight_info 테이블에는 몸무게가 70보다 큰 학생의 학번, 이름, 몸무게를 각각 입력하여라.

INSERT ALL
WHEN HEIGHT > 170
    THEN INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
WHEN WEIGHT > 70
    THEN INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT *
FROM STUDENT
WHERE GRADE >= 2;

다중 행 입력 - Conditional-First INSERT

-- 학생테이블에서 2학년 이상의 학생을 검색하여
-- height_info 테이블에는 키가 170보다 큰 학생의 학번, 이름, 키를 입력하고
-- weight_info 테이블에는 몸무게가 70보다 큰 학생의 학번, 이름, 몸무게를 각각 입력하여라.
-- 단, 키가 170보다 작고, 몸무게가 70보다 큰 학생은 WEIGHT_INFO 테이블만 입력한다.

INSERT FIRST
WHEN HEIGHT > 170
    THEN INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
WHEN WEIGHT > 70
    THEN INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT *
FROM STUDENT
WHERE GRADE >= 2;

저작자표시

'데이터베이스' 카테고리의 다른 글

계층적 질의문 21. 03. 12. (0)	2021.03.14
무결성 제약조건 이어서, 인덱스, 뷰, 사용자 권한 제어 21. 03. 12. (0)	2021.03.14
21. 03. 11. (0)	2021.03.11
21. 03. 09. 문자 조작 함수 이어서, 여러 함수들, GROUP BY, HAVING, JOIN (0)	2021.03.09
21. 03. 08 데이터 타입, ROWNUM, 문자 조작 함수 (0)	2021.03.08

삐약삐약

SELF JOIN, 서브쿼리 21. 03. 10.

데이타 조작어 (DML: Data Manipulation Language)

'데이터베이스' 카테고리의 다른 글

티스토리툴바

SELF JOIN, 서브쿼리 21. 03. 10.

데이타 조작어 (DML: Data Manipulation Language)

'데이터베이스' 카테고리의 다른 글

'데이터베이스' Related Articles

티스토리툴바