[TIL]SQLD: SQL의 활용1(집합 연산자, 서브쿼리와 뷰)+ Speak + 매카시 논문

10. SQL의 활용1

--10-1. 집합 연산자
-- 1) 집합 연산자 개념과 종류: SET OPERATOR는 SELECT를 통해 얻은 결과 간에 (JOIN없이))집합 연산을 수행 하는 연산자
                       --UNION(합집합, 중복 허용x), UNION ALL(합집합, 중복 허용, 정렬x), INTERSECT(교집합), MINUS(차집합)
--  !주의사항: 두 집합의 '칼럼 수, 순서, 데이터 타입'이 일치해야 한다. 사이즈는 달라도 된다. 
--  ex) 
SELECT empno, ename, job, deptno FROM emp;
SELECT deptno, dname FROM dept;

SELECT job FROM EMP WHERE deptno = 10
MINUS
SELECT job FROM EMP WHERE deptno = 20;

--10-2. 서브쿼리
-- 1) 서브쿼리 기본: 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문. 보다 복잡한 조건 처리하기 위해.
--  ①개념: 서브쿼리는 메인쿼리에 포함된 형태이며 항상 메인쿼리 레벨로 결과 집합이 생성. 메인쿼리에서 컬럼 받을 수만 있고 줄 수 없다. 
--    •!주의사항: 서브쿼리는 소괄호 () 로 감싸 사용. ORDER BY 사용x

-- 2) 동작 방식에 따른 서브쿼리: 연관/비연관 서브쿼리 → '서브쿼리가 메인쿼리의 값을 사용 O/X'
--  ①연관 서브쿼리: 서브쿼리 의 값이 결정되는데 메인쿼리에 의존
SELECT A.EMPNO
     , A.ENAME
	 	, A.DEPTNO
		, A.SAL 
FROM EMP A
WHERE A.SAL >= (
				SELECT AVG(X.SAL)
				FROM EMP X
				WHERE X.DEPTNO = A.DEPTNO
				GROUP BY X.DEPTNO );
--   !EXISTS 서브쿼리 : 서브쿼리 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건
SELECT DISTINCT country_name
FROM nation n
WHERE EXISTS (
    SELECT 1 -- * 동일
    FROM participant p
    WHERE p.nation_id = n.nation_id
);

--  ②비연관 서브쿼리: 메인 쿼리의 어떤 것도 참조하지 않고 단독으로 사용. 서브 쿼리 우선 실행. 
SELECT first_name, height
FROM PARTICIPANT
WHERE height > (SELECT AVG(height) FROM PARTICIPANT); --> SELECT...는 메인 쿼리와 별도로 먼저 실행

-- 3) 반환 형태에 따른 서브쿼리: 몇 개의 행 혹은 컬럼을 메인쿼리에 반환하는가?
   --단일행 서브쿼리(실행 결과가 항상 1건 이하), 다중행 서브쿼리(실행 결과 여러 건), 다중칼럼 서브쿼리
--  ①단일행 서브쿼리 → 일행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용
SELECT A.EMPNO, A.ENAME, A.SAL 
FROM EMP A
WHERE A.SAL >= (SELECT AVG(K.SAL) AS MAX_SAL FROM EMP K) --> >= 등 사용_단일행
ORDER BY A.SAL ;

--  ②다중행 서브쿼리 → 다중행 비교 연산자(IN, ALL,  ANY, EXISTS 등)와 함께 사용
--   ex) DNAME이 'ACCOUNTING' 혹은 'SALES'인 DEPTNO에 소속되어 있는 직원들의 정보를 출력

SELECT A.EMPNO
     , A.ENAME
		 , A.DEPTNO 
	FROM EMP A
 WHERE A.DEPTNO IN
                 (
									SELECT K.DEPTNO 
										FROM DEPT K
                    WHERE K.DNAME IN ('ACCOUNTING', 'SALES')
                 )
ORDER BY A.DEPTNO ;

--error 발생
SELECT A.EMPNO
     , A.ENAME
		 , A.DEPTNO 
	FROM EMP A
WHERE A.DEPTNO = (    --> 이 이하가 다중행이므로 = 불가  
									SELECT K.DEPTNO 
										FROM DEPT K
									 WHERE K.DNAME IN ('ACCOUNTING', 'SALES')
								 )
ORDER BY A.DEPTNO ;

--   ③다중칼럼 서브쿼리 → 메인쿼리의 조건 과 동시에 비교
--    ex)부서별로 가장 높은 급여를 받는 사원을 찾는 SQL 문
SELECT deptno, dname, empno, ename, sal
FROM (
    SELECT e.deptno,
           d.dname,
           e.empno,
           e.ename,
           e.sal,
           RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS sal_rank
    FROM emp e
    JOIN dept d ON e.deptno = d.deptno
)
WHERE sal_rank = 1;

--10-3. 기타 서브쿼리와 뷰
-- 1) 위치별 서브쿼리
  --SELECT(절에 위치, '스칼라 서브쿼리', 'e1 Row - 1 Column'), FROM(인라인뷰 서브쿼리), WHERE(가장 일반적), HAVING, INSERT문의 VALUES, UPDATE문의 SET


-- 2) 뷰(View): (실제 데이터를 가진)테이블에 있는 데이터를 가져다 보여주는 가상의 테이블. 원하는 정보를 필터링한 결과. 뷰 정의(View Definition, SELECT문으로 구성)만을 가짐. 
  --뷰 사용의 장점: 독립성(테이블 구조 변경해도 영향x), 편리성(복잡한 질의는 따로), 보안성(숨기는 정보는 뷰에선 생략 가능)  
--  ex)
CREATE VIEW V_DEPT_EMP AS --> 이름은 ‘V_’로 시작하는 게 좋다
SELECT E.EMPNO,
       E.ENAME,
       E.JOB,
       E.SAL,
       D.DNAME
FROM DEPT D,
     EMP E
WHERE D.DEPTNO = E.DEPTNO;
--
SELECT ENAME,
       JOB
FROM V_DEPT_EMP --> 인라인 뷰, 앞선 뷰 덕분에 간단해짐 
WHERE EMPNO IN (7698, 7788);