Oracle/ 서브 쿼리 - 단일행 서브쿼리

! 쿼리 ; 정보요청

<서브 쿼리 - 단일행 서브쿼리>
; 서브 쿼리는 두 개의 쿼리를 결합하여 하나의 문장으로 표현하는 것

1) 단일 행 서브쿼리
 ; 서브쿼리가 하나의 컬럼에서 하나의 행을 검색
 
2) 다중 행 서브쿼리
 ; 서브쿼리가 하나의 컬럼에서 여러 개의 행을 검색
 
3) 다중 열 서브쿼리
 ; 서브쿼리가 여러 개의 컬럼을 검색

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

 

SELECT 컬럼, ...
FROM 테이블
WHERE 컬럼 <단일 행 연산자> (SELECT 문: Sub Query);

 

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

 

 

--김연아보다 급여를 많이 받는 사원을 검색한다
--1) 김연아의 급여를 검색한다
--2) 김연아의 급여와 비교하여 더 많이 받는 사원을 검색한다
SELECT eno, ename
FROM emp
WHERE sal > (SELECT sal
             FROM emp
             WHERE ename='김연아');
             
--노육과 평점이 동일한 학생의 정보를 검색하라
--노육이 3명이라서 단일 행 연산자를 사용할 수가 없기 때문에 Error 가 발생했다
SELECT sno, sname, avr
FROM student
WHERE avr = (SELECT avr
             FROM student
             WHERE sname='노육');
             
SELECT sno, sname, avr
FROM student
WHERE sname='노육';

 

 

 

예측하기 힘든 단일 행 서브쿼리를 수정하는 방법
1) '=' 연산자는 'IN'연산자로 수정
2) 부등호(<, >, <=, >=)는 any, all 연산자를 추가
3) Max, Min 그룹 함수를 사용

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

--김연아와 부서가 다르고 동일한 업무를 하는 사원의 정보를 검색하라
SELECT eno, ename, dno, job
FROM emp
WHERE dno!=(SELECT dno
            FROM emp
            WHERE ename='김연아')
AND job=(SELECT job
         FROM emp
         WHERE ename='김연아');
         
         
         
--부서 중 가장 급여를 많이 받는 부서를 검색하라
--1) 부서중 평균 최대급여 계산
--2) 일치하는 부서를 출력
SELECT dno, ROUND(AVG(sal), 2)
FROM emp
GROUP BY dno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
                   FROM emp
                   GROUP BY dno);

--부산에서 근무하는 사원의 정보를 검색한다
SELECT eno, ename
FROM emp
WHERE dno = (SELECT dno
             FROM dept
             WHERE loc='부산');
             
SELECT eno, ename
FROM emp e
JOIN dept d ON e.dno=d.dno
WHERE loc='부산';

 

 

 

 

 

 

 

<Exercise>

--1. 관우보다 평점이 우수한 학생의 학번과 이름을 검색하라
SELECT sno, sname, avr
FROM student
WHERE avr > (SELECT avr
            FROM student
            WHERE sname = '관우');

--2. 관우와 동일한 학년중에 평점이 사마감과 동일한 학생을
--  검색하라
SELECT syear, sno, sname, avr
FROM student
WHERE avr = ( SELECT avr
                FROM student
                WHERE sname = '사마감')
AND syear = ( SELECT syear
                FROM student
                WHERE sname ='관우');

--3. 관우보다 일반 화학과목의 학점이 더 낮은 학생의 
--  명단을 학점과 검색하라
SELECT score.sno, sname, result
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno = course.cno
WHERE result < (SELECT result
                        FROM student
                        JOIN score ON student.sno = score.sno
                        JOIN course ON score.cno = course.cno
                        WHERE sname='관우'
                        AND cname = '일반화학'
                        )
AND cname = '일반화학';

--4. 인원수가 가장 많은 학과를 검색하라
SELECT major, COUNT(sno)
FROM student
GROUP BY major
HAVING COUNT(sno) = (SELECT MAX(COUNT(sno))
                   FROM student
                   GROUP BY major);


--5. 학생중 기말고사 성적이 가장 낮은 학생의 정보를 검색하라
SELECT score.sno, sname, result
FROM student
JOIN score ON student.sno = score.sno
WHERE result = (SELECT MIN(result)
                      FROM score);