[SQL] SELECT-2 , JOIN

📖 SELECT-2

SELECT [PREDICATE][테이블명.]속성명 [AS 별칭], [테이블명.]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW 함수 OVER (PARTITION BY 속성명1, 속성명2, ...
ORDER BY 속성명3, 속성명4, ...) [AS 별칭]]

[GROUP BY속성명, 속성명, ...]

[HAVING BY 조건]

WINDOW 함수

• PARTITION BY : WINDOW 함수가 적용될 번위로 사용할 속성을 지정한다.
• ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정한다.

그룹 함수
COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIANCE, ROLLUP, CUBE
WINDOW 함수
ROW_NUMBER, RANK, DENSE_RANK

---

🍀 WINDOW 함수 이용 검색

💡 예제1 <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오. (단 순서는 내림차순이며 속성명은 'NO'로 할것)

SELECT 상여내역, 상여금,
	ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

💡 예제2 <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 순위를 구하시오. (단, 순서는 내림차순이며, 속성명은 '상여금순위'로 하고, RANK() 함수를 이용할 것)

SELECT 상여내역, 상여금,
RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금 순위
FROM 상여금;

---

🍀 그룹 지정 검색

💡 예제1 <상여금> 테이블에서 '부서'별 '상여금'의 평균을 구하시오.

SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

💡 예제2 <상여금> 테이블에서 부서별 튜플 수를 검색하시오.

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
GROUP BY 부서;

💡 예제3 <상여금> 테이블에서 '상여금'이 100 이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오.

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >100
GROUP BY 부서
HAVING COUNT(*) >=2;

💡 예제4 <상여금> 테이블의 '부서','상여내역',그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 '상여금합계'로 하고, ROLLUP 함수를 사용할 것

SELECT 부서,상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY ROLLUP(부서,상여내역);

💡 예제5 <상여금> 테이블의 '부서','상여내역', 그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 '상여금합계'로 하고, CUBE 함수를 사용할 것)

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY CUBE(부서,상여내역);

---

🍀 집합 연산자를 이용한 통합 질의

SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNIONALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [DESC|ASC]];

집합 연산자의 종류
+UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력하고, 중복된 행은 한 번만 출력한다.
+UNIONALL : 두 SELECT문의 조회 결과를 통합하여 모두 출력하고, 중복된 행도 그대로 출력한다.
+INTERSECT : 두 SELECT문의 조회 결과 중 공통된 행만 출력한다.
+EXCEPT : 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력한다.

💡 예제1 <사원> 테이블과 <직원> 테이블을 통합하는 질의문을 작성하시오. (단, 같은 레코드가 중복되어 나오지 않게 하시오)

SELECT *
FROM 사원
UNION
SELECT *
FROM 직원;

💡 예제2 <사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합하는 질의문을 작성하시오.

SELECT *
FROM 사원
INTERSECT
SELECT *
FROM 직원;

---

📖 JOIN

🍀 INNER JOIN

INNER JOIN은 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분된다.
조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.

EQUI JOIN

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;

💡 예제1 <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL문을 작성하시오.

SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생, 학과
WHERE 학생.학과코드 = 학과.학과코드;

NON-EQUI JOIN
NON-EQUI JOIN은 JOIN조건에 '=' 조건이 아닌 나머지 비교 연산자, 즉 >, <, <=, >=, <> 연산자를 사용하는 JOIN 방법이다.

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);

💡 예제2 <학생> 테이블과 <성적등급> 테이블을 JOIN하여 각 학생의 '학번', '이름', '성적', '등급'을 출력하는 SQL문을 작성하시오.

SELECT 학번, 이름, 성적, 등급
FROM 학생, 성적등급
WHERE 학생.성적 BETWEEN 성적등급.최저 AND 성적등급.최고;

---

🍀 OUTER JOIN

OUTER JOIN은 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법이다.

LEFT OUTER JOIN : INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

RIGHT OUTER JOIN : INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

FULL OUTER JOIN : LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐놓은 것이다.

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

💡 예제1 <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL문을 작성하시오. 이때, '학과코드'가 입력되지 않은 학생도 출력하시오.

SELECT 학번, 이름, 학과코드, 학과명
FROM 학생 LEFT OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;

💡 예제2 <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL문을 작성하시오. 이때, '학과코드'가 입력안 된 학생이나 학생이 없는 '학과코드'도 모두 출력하시오.

SELECT 학번, 이름, 학과.학과코드, 학과명
FROM 학생 FULL OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;

---

🍀 SELF JOIN

SELF JOIN은 같은 테이블에서 2개의 속성을 연결하여 EQUI JOIN을 하는 JOIN 방법이다.

SELECT [별칭1.]속성명, [별칭2.]속성명, ...
FROM 테이블명1 [AS] 별칭1 JOIN 테이블명1 [AS] 별칭2
ON 별칭1.속성명 = 별칭2.속성명;

💡 예제 <학생> 테이블을 SELF JOIN하여 선배가 있는 학생과 선배의 '이름'을 표시하는 SQL문을 작성하시오.

SELECT A.학번, A.이름, B.이름 AS 선배
FROM 학생 A JOIN 학생 B
ON A.선배 = B.학번;