22.02.03 SQL 심화 공부 (집합연산자 & 계층형질의)

Standard SQL : 관계형 데이터베이스(RDBMS, Table, SQL)에서 원하는 정보를 유도하기 위한 기본연산집합

 비관계형 데이터베이스? = SQL이 아니다 NoSQL(MongoDB)

 

1. 관계형 데이터베이스 의 일반집합연산

카티션 프로덕트 - 가능한 모든 경우의 수 .. JOIN

일반집합연산을 SQL로 표현해보자(합집합, 교집합, 차집합, 카디션프로덕트(곱))

UNION, INTERSECT, EXCEPT, CROSS JOIN

2. 관계형 데이터베이스의 순수관계연산

SELECT : 특정 row만 조회

PROJECTION : 특정 col만 조회

JOIN : 두 테이블을 합쳐서 새로운 테이블을 만듦

DIVISION : 두 테이블에서 연관된 데이터만 출력

 

순수관계연산을 SQL로 표현하면

WHERE, SELECT, JOIN, 사용안함

조인 - 리본모양, 저 예시는 자연조인nC이다. 

순수관계연산 예시

집합 연산자 - 두 개 이상의 테이블에서 JOIN을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나

테이블에서 SELECT 한 컬럼의 수와 각 컬럼의 데이터타입이 테이블 간 상호 호환이 가능해야 한다.

합집합 - UNION, UNION ALL

UNION : 두 개의 테이블을 하나로 만드는 연산, 컬럼의 수와 데이터형식이 일치한 두 테이블이어야 하며

정렬이 들어간다. 그리고 중복된 데이터는 제거한다. 

UNION ALL : UNION과 비슷하나 UNION과 달리 중복제거/정렬을 하지 않는다.

UNION으로 두 학생 테이블 합집합하기

-- 아래 쿼리는 lecture_basic, lecture_special 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC lecture_basic;
DESC lecture_special;

-- 1. lecture_basic 테이블에서 학생번호(student_number)와 학생이름(student_name)을 조회하는 쿼리를 작성하세요.
SELECT student_number, student_name FROM lecture_basic;

-- 2. lecture_special 테이블에서 학생번호(student_number)와 학생이름(student_name)을 조회하는 쿼리를 작성하세요.
SELECT student_number, student_name FROM lecture_special;

-- 3. 위 2개의 쿼리에 대해서 집합 연산자를 이용하여 데이터 연결 및 정렬과 중복제거를 시행하는 쿼리를 작성하세요.
SELECT student_number, student_name FROM lecture_basic UNION SELECT student_number, student_name FROM lecture_special ORDER BY student_number;

UNION으로 치킨가게와 피자가게 합집합하기

-- 아래 쿼리는 chicken_store, pizza_store 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
-- DESC chicken_store;
-- DESC pizza_store;
SELECT * FROM chicken_store;
SELECT * FROM pizza_store;
-- 1. chicken_store에서 이용가능한 가게에 대해 가게이름만 출력하는 쿼리를 작성해보세요.
SELECT store_name FROM chicken_store;

-- 2. pizza_store에서 이용가능한 가게에 대해 가게이름만 출력하는 쿼리를 작성해보세요.
SELECT store_name FROM pizza_store WHERE available='Y';

-- 3. 위 2개의 쿼리에 대해서 집합 연산자를 이용하여 데이터 연결 및 정렬과 중복제거를 시행하는 쿼리를 작성하세요.
SELECT store_name FROM chicken_store UNION SELECT store_name FROM pizza_store WHERE available='Y' ORDER BY store_name;
SELECT store_name FROM chicken_store UNION SELECT store_name FROM pizza_store WHERE available='Y';

SELECT store_name FROM chicken_store UNION ALL SELECT store_name FROM pizza_store WHERE available='Y' ORDER BY store_name;

UNION ALL로 강의실 테이블 두개 UNION ALL 하기

-- 아래 쿼리는 lecture_basic, lecture_special 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC lecture_basic;
DESC lecture_special;

SELECT * FROM lecture_basic;
SELECT * FROM lecture_special;

-- 1. lecture_basic 테이블에서 강의이름(lecture_name)을 조회하는 쿼리를 작성하세요.
SELECT lecture_name FROM lecture_basic;

-- 2. lecture_special 테이블에서 강의이름(lecture_name)을 조회하는 쿼리를 작성하세요.
SELECT lecture_name FROm lecture_special;

-- 3. 위 2개의 쿼리에 대해서 집합 연산자를 이용하여 데이터를 연결하되 데이터의 중복제거를 시행하지 않는 쿼리를 작성하세요.

SELECT lecture_name FROM lecture_basic UNION ALL SELECT lecture_name FROm lecture_special ORDER BY lecture_name;

-- 아래 쿼리는 request_past, request_new 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC request_past;
DESC request_new;

-- 1. 각 테이블에서 이름(name)과 번호(number)를 조회하고, 집합연산자를 통해 중복을 제거하지 않고 데이터를 연결해보세요.
SELECT * FROM request_past;
SELECT * FROM request_new;
SELECT name, number FROM request_past UNION ALL SELECT name, number FROM request_new ORDER BY name;
-- ORDER BY 는 오름차순이 default

-- 2. 각 테이블에서 이름(name)과 번호(number)를 조회하고, 집합연산자를 통해 중복을 제거하여 데이터를 연결해보세요.
SELECT name, number FROM request_past UNION SELECT name, number FROM request_new ORDER BY name;

INTERSECT : 일반집합연산 중 교집합, 두 개의 테이블에 대해 겹치는 부분을 추출하는 연산, 추출 후에는 중복된 결과를 제거한다. -> Oracle과 Maria는 지원되지만, MySQL에서는 지원되지 않으므로 JOIN을 사용해야 한다!

INTERSECT로 강의 테이블 두 개 교집합보기

-- 아래 쿼리는 lecture_basic, lecture_special 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC lecture_basic;
DESC lecture_special;

SELECT * FROM lecture_basic;
SELECT * FROM lecture_special;

-- 1. lecture_basic 테이블에서 학생번호(student_number)와 학생이름(student_name)을 조회하는 쿼리를 작성하세요.
SELECT student_number, student_name FROm lecture_basic;

-- 2. lecture_special 테이블에서 학생번호(student_number)와 학생이름(student_name)을 조회하는 쿼리를 작성하세요.
SELECT student_number, student_name FROm lecture_special;

-- 3. 위 2개의 쿼리에 대해서 집합 연산자를 이용하여 공통적으로 존재하는 데이터를 조회하는 쿼리를 작성해주세요.
SELECT student_number, student_name FROm lecture_basic INTERSECT SELECT student_number, student_name FROm lecture_special ORDER BY sttudent_number;

-- 아래 쿼리는 student, lecture_special 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC student;
DESC lecture_special;

-- 1. student 테이블에서 이름(name)와 이메일(email)을 조회하는 쿼리를 작성하세요.
SELECT * FROM student;
SELECT * FROM lecture_special;

-- 2. lecture_special 테이블에서 이름(name)와 이메일(email)을 조회하는 쿼리를 작성하세요.
SELECT name,email FROM student;
SELECT name,email FROM lecture_special;

-- 3. 위 2개의 쿼리에 대해서 집합 연산자를 이용하여 공통적으로 존재하는 데이터를 조회하는 쿼리를 작성해주세요.
SELECT name,email FROM student INTERSECT SELECT name,email FROM lecture_special;

EXCEPT : 일반 집합연산에서 차집합 역할, 두 개의 테이블에서 겹치는 부분을 앞의 테이블에서 제외하여 추출하는 연산,

추출 후에는 중복된 결과를 제거한다.

Oracle에서는 지원되지만 MINUS로 지원하고, Maria는 10.3이상부터 EXCEPT를 지원, MySQL은 지원하지 않으므로 JOIN을 써야함.

EXCEPT로 학생과 강의목록 차집합 보기 column순서를 좀 다르게 해봤다

-- 아래 쿼리는 lecture_basic, lecture_special 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC lecture_basic;
DESC lecture_special;

-- 1. lecture_basic 테이블에서 학생번호(student_number)와 학생이름(student_name)을 조회하는 쿼리를 작성하세요.
SELECT student_number, student_name FROM lecture_basic;

-- 2. lecture_special 테이블에서 학생번호(student_number)와 학생이름(student_name)을 조회하는 쿼리를 작성하세요.
SELECT student_name, student_number FROM lecture_special;

-- 3. 위 2개의 쿼리에 대해서 집합 연산자를 이용하여 lecture_special 테이블에는 존재하지만, lecture_basic에는 
-- 존재하지 않는 학생번호와 학생이름을 조회하는 쿼리를 작성하세요.
SELECT student_number, student_name FROM lecture_basic EXCEPT SELECT student_name, student_number FROM lecture_special;

A서점에만 존재하는 책 재고 조회하기

-- 아래 쿼리는 book_store_a, book_store_b 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC book_store_a;
DESC book_store_b;


SELECT * FROM book_store_a;
SELECT * FROM book_store_b;
-- 1. book_store_a 테이블에서 재고(stock)가 0 초과인 데이터의 책의이름(book_name)을 조회하는 쿼리를 작성하세요.
SELECT book_name FROM book_store_a WHERE stock>0;

-- 2. book_store_b 테이블에서 재고(stock)가 0 초과인 데이터의 책의이름(book_name)을 조회하는 쿼리를 작성하세요.
SELECT book_name FROM book_store_b WHERE stock>0;

-- 3. 위 2개의 쿼리에 대해서 집합 연산자를 이용하여 book_store_a에만 존재하는 데이터를 조회하세요.
SELECT book_name FROM book_store_a WHERE stock>0 EXCEPT SELECT book_name FROM book_store_b WHERE stock>0;

계층형 질의 - 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용하는 것

대표적인 데이터베이스 : ORACLE, SQL Server

계층형 데이터란

동일 테이블에 계층적으로 상위와 하위 데이터가 포함되어 있는 데이터

계층형 질의 Oracle 예시

START WITH : 최상위 데이터 기준(부모컬럼이 NULL이면 최상위니까,)

CONNECT BY : 상위 데이터와 하위데이터의 연결방식 결정(PRIOR이 붙은 게 상위 데이터다.)

Oracle 계층형 질의 예시 - LPAD(a,b) a문자열, b갯수만큼 왼쪽에 추가 .... 시각화 가능

CONNECT BY 키워드들(ROOT, ISLEAF, PATH) (Oracle)

SQL Server(Maria, My)의 계층형 질의 예시

CTE를 이용해 재귀호출을 하겠다(WITH RECURSIVE CTE 컬럼명)

 

-> 자세히 보자

1번째 순환 부모컬럼 IS NULL

2번쨰 순환

저 CTE 두개를 UNION ALL해서 그 다음 CTE Table을 만듦

3번쨰 순환

그걸 또 UNION ALL

4번쨰 순환

또 UNION ALL

이제 5번쨰, 6번쨰 해도 어차피 똑같은 CTE 테이블이 생길 것.모든 걸 다 순회했으니까

그래서 이렇게 가지고 있던 테이블을 가지고 레벨값을 지정한 테이블이 생겼다.

Oracle의 start with가 저 윗부분, connect by가 저 밑부분이다.

 

계층형질의 (SQL Server 예시) 멤버 ID와 관리자 ID 관리하기

SELECT * FROM MEMBER;

-- 계층형 질의, WITH RECURSIVE 를 이용하여, member_id, manager_id, lvl을 출력하되
-- member_id 오름차순, lvl 오름차순 정렬을 적용하여 쿼리를 작성하세요.

WITH RECURSIVE CTE(member_id, manager_id, lvl)
AS (
    SELECT member_id, manager_id, 0 AS lvl
/*     부모가 NULL */
    FROM MEMBER
    WHERE manager_id IS NULL 
    UNION ALL
    SELECT a.member_id, a.manager_id, b.lvl +1
/*     그 하위 데이터 */
    FROM MEMBER a
    JOIN CTE AS b
    ON a.manager_id = b.member_id
/*     CTE 테이블에 있는 애 = 하위데이터의 부모 */
)
SELECT member_id, manager_id, lvl
FROM CTE
ORDER BY member_id, lvl;

완성된 CTE 테이블