<33, 34> 단일행 서브쿼리

[서브 쿼리 - 단일 행 서브쿼리]
; 서브 쿼리는 두 개의 쿼리를 결합하여 하나의 문장으로
표현하는 것이다
1) 단일 행 서브쿼리
 ; 서브쿼리가 하나의 컬럼에서 하나의 행을 검색한다
2) 다중 행 서브쿼리
 ; 서브쿼리가 하나의 컬럼에서 여러 개의 행을 검색한다
3) 다중 열 서브쿼리
 ; 서브쿼리가 여러 개의 컬럼을 검색한다

4) 서브 쿼리는 WHERE 절, HAVING 절과 같이 조건절에 주로
 쓰인다. FROM 절에 쓰이는 경우도 있다.
 FROM 절에 서브쿼리를 쓰는 경우를 인라인뷰(Inline View)
 라고 한다

SELECT 컬럼, ...
FROM 테이블
WHERE 컬럼 <단일 행 연산자> (SELECT 문: Sub Query);
1) 단일 행 연산자가 사용됨으로 반드시 서브쿼리의 결과
 값은 1개만 검색돼야 한다
2) 서브 쿼리는 반드시 괄호로 묶는다
3) 서브 쿼리는 메인 쿼리 실행 전에 실행된다
4) 서브 쿼리의 검색된 결과값은 메인 쿼리에 사용된다
5) 단일 행 연산자 오른쪽에 기술한다
   (=, <, >, <=, >=, !=)
6) WHERE 절에 기술된 열의 숫자와 타입은
  SELECT 절과 1:1 대응관계가 되어야 한다.

1)김연아보다 급여를 많이 받는 // 사원을 검색한다
--1) 김연아의 급여를 검색한다
--2) 김연아의 급여와 비교하여 더 많이 받는 사원을 검색한다

SELECT eno, ename "김연아 급여 초과"
FROM emp
WHERE sal > (SELECT sal 
              FROM emp 
              WHERE ename='김연아'); -- 서브쿼리


             
2)노육과 평점이 동일한 학생의 정보를 검색하라
--노육이 3명이라서 단일 행 연산자를 사용할 수가 없다
--그래서 Error 가 발생했다

SELECT sno, sname, avr
FROM student
WHERE avr=(SELECT avr
            FROM student
            WHERE sname='노육');

--다중행 서브쿼리 : 결과값이 여러개 행이다.
SELECT sno, sname, avr
FROM student
WHERE avr IN (SELECT avr
              FROM student
              WHERE sname='노육');


예측하기 힘든 단일 행 서브쿼리를 수정하는 방법
1) '=' 연산자는 'IN'연산자로 바꾼다    -- 다중행 서브쿼리로 전환
2) 부등호(<, >, <=, >=)는 any, all    -- 다중행 서브쿼리로 전환
  연산자를 추가한다
3) Max, Min 그룹 함수를 사용한다       -- 여러개 중에 1개만 선택



3) 김연아와 부서가 다르고 동일한 업무를 하는 사원의 정보를 검색하라
-- 김연아와 부서가 다르다 (sub query)
-- 김연아와 동일한 업무를 한다 (sub)
-- 위 조건의 사원정보를 검색한다 (main)

SELECT eno, ename, dno, job
FROM emp
WHERE dno != (SELECT dno FROM emp WHERE ename='김연아')
AND job = (SELECT job FROM emp WHERE ename='김연아');


         
4) 부서 중 가장 급여를 많이 받는 부서를 검색하라
--1) 부서중 평균 최대급여 계산
--2) 일치하는 부서를 출력

SELECT dno "급여 최대 부서", ROUND (AVG(sal))
FROM emp
GROUP BY dno
HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY dno);
                          
SELECT MAX(AVG(sal))
FROM emp
GROUP BY dno;


5) 부산에서 근무하는 사원의 정보를 검색한다
 -- 부산에 근무하는 부서번호
 -- 해당 부서번호와 일치하는 사원의 정보 검색
 
 SELECT eno, ename "부산근무사원"
 FROM emp
 WHERE dno = (SELECT dno FROM dept WHERE loc='부산');
         

SELECT eno, ename, loc
FROM emp e, dept d
WHERE e.dno=d.dno
AND loc='부산';

 

연습문제

<서브 쿼리를 사용하세요>
1) 관우보다 평점이 우수한 학생의 학번과 이름을 검색하세요

SELECT sno, sname, avr
FROM student
WHERE avr > (SELECT avr FROM student WHERE sname='관우');


2) 관우와 동일한 학년 학생 중에 평점이 사마감과 동일한 학생을 검색하세요
-- 평점이 검색 
    --->사마감이랑 동일
-- 관우와 동일학년

SELECT *
FROM student
WHERE avr IN (SELECT avr FROM student WHERE sname ='사마감')
AND syear = (SELECT syear FROM student WHERE sname ='관우');


--SELECT sno 학번, syear 학년, sname 이름
--FROM student
--WHERE syear = (SELECT syear
--               FROM student
--               WHERE sname = '관우') 
--AND avr IN ( SELECT avr
--             FROM student
--             WHERE sname='사마감');

3) 관우보다 일반 화학과목의 학점이 더 낮은 학생의 명단을 학점과 검색하세요

-- 화학과목 학생명단
--

SELECT sno, sname, cname, result 점수, avr 학점
--FROM student s , course c, score r
FROM student
NATURAL JOIN course
NATURAL JOIN score
WHERE cname='일반화학' 
AND result < (SELECT result FROM score WHERE cname='관우');


4) 인원수가 가장 많은 학과를 검색하세요

SELECT major 학과, count(*) 인원수
FROM student
GROUP BY major
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                    FROM student
                    GROUP BY major) ;

5) 학생 중 기말고사 성적이 가장 낮은 학생의 정보를 검색하세요
SELECT sno, sname, MIN(result)
FROM student 
NATURAL JOIN score
GROUP BY sno, sname;      -- 그룹함수니까 GROUP BY를 써 줘야 한다...