(1) Sub-Query : ( )로 꼭 묶을 것!!!
SELECT~
FROM~
WHERE ~~~~ (SELECT~
FROM~
WHERE);
(*서브쿼리는 메인 쿼리가 실행되기 이전에 실행된다)
* Sub Query를 사용할 수 있는 절들(ORDER BY절은 불가능!!)
1.WHERE절
2.HAVING절
3.FROM절
(2) 단일행 Sub-Query : 서브쿼리의 결과가 하나만 나오는 경우
문1 DALLS에서 근무하는 사원의 이름, 부서번호를 출력하시오.
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'DALLAS');
=============
(JOIN 사용)
SELECT ENAME, DEPTNO
FROM EMP NATURAL JOIN DEPT
WHERE LOC = (SELECT LOC
FROM DEPT
WHERE LOC = 'DALLAS');
**문2 직속상관이 KING인 사원의 이름과 급여를 출력하시오.
(틀림... 너무 복잡하게 생각...)
SELECT ENAME,SAL
FROM EMP
WHERE ???? = (SELECT E1.ENAME,E2.ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO AND E2.ENAME = 'KING');
(정답 : KING의 사원번호를 찾아서 MGR 번호와 맞춰보면 답이 나온다...)
SELECT ENAME,SAL
FROM EMP
WHERE MGR = (SELECT EMPNO
FROM EMP
WHERE ENAME = 'KING');
문3 평균 급여보다 높은 사원의 이름, 급여를 구하라. (평균급여를 구하기 위해서 서브쿼리 사용)
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT AVG(SAL)
FROM EMP);
문4 진현무 학생보다 키가 큰 학생들에 이름, 학번, 키를 출력하시오.
SELECT STU_NAME, STU_NO, STU_HEIGHT
FROM STUDENT
WHERE STU_HEIGHT > (SELECT STU_HEIGHT
FROM STUDENT
WHERE STU_NAME = '진현무');
SELECT STU_HEIGHT
FROM STUDENT
WHERE STU_NAME = '진현무'
**=SELF JOIN으로 풀기
SELECT S1.STU_NAME, S1.STU_NO, S1.STU_HEIGHT
FROM STUDENT S1, STUDENT S2
WHERE S1.STU_HEIGHT > S2. STU_HEIGHT
AND S2.STU_NAME = '진현무';
**문5 학과별 평균 신장 중 가장 높은 평균 신장, 학과를 출력하시오.
(틀림)
SELECT MAX(STU_HEIGHT),STU_DEPT
FROM STUDENT
GROUP BY STU_DEPT
HAVING AVG(STU_HEIGHT);
(정답)
SELECT STU_DEPT,AVG(STU_HEIGHT)
FROM STUDENT
GROUP BY STU_DEPT
HAVING AVG(STU_HEIGHT) = (SELECT MAX(AVG(STU_HEIGHT))
FROM STUDENT
GROUP BY STU_DEPT);
SELECT MAX(AVG(STU_HEIGHT))
FROM STUDENT
GROUP BY STU_DEPT;
문6 부서 평균 급여가 전체 직원 평균 급여보다 많은 부서의 이름, 부서 평균 급여를 출력하시오.
SELECT DNAME, ROUND(AVG(SAL))
FROM EMP NATURAL JOIN DEPT
GROUP BY DNAME
HAVING AVG(SAL) > (SELECT AVG(SAL)
FROM EMP;)
SELECT AVG(SAL)
FROM EMP;
문7 SCOTT 사원보다 많은 급여를 받는 사원 정보를 출력하시오.
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'SCOTT');
SELECT SAL
FROM EMP
WHERE ENAME = 'SCOTT';
문8 기계과 학생들 중 학년별 평균 신장이 160이상인 학년과 평균 신장을 출력하시오.
SELECT STU_GRADE, AVG(STU_HEIGHT)
FROM STUDENT
WHERE STU_DEPT = '기계'
GROUP BY STU_GRADE
HAVING AVG(STU_HEIGHT) > = 160;
** =다른방법 (FROM에 서브쿼리 적용)
SELECT STU_GRADE, AVG(STU_HEIGHT)
FROM (기계과 학년별 평균 신장)
WHERE AVG (STU_HEIGHT)>= 160
SELECT STU_GRADE, AVG(STU_HEIGHT)
FROM (SELECT STU_GRADE, AVG(STU_HEIGHT) AS AVG
FROM STUDENT
WHERE STU_DEPT = '기계'
GROUP BY STU_GRADE)
WHERE AVG(STU_HEIGHT) >= 160;
문9 CHICAGO에서 근무하지 않은 모든 사원의 정보를 출력하시오.
SELECT *
FROM EMP
WHERE DEPTNO != (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'CHICAGO');
SELECT DEPTNO
FROM DEPT
WHERE LOC = 'CHICAGO');
**문10 20번 부서의 최대급여보다 최대급여가 많은 부서의 부서번호, 부서이름, 최대급여를 검색하시오.
(ORA-00934: 그룹 함수는 허가되지 않습니다) ===> GROUP BY 위치가 틀렸고, HAVING절로 풀어야 됨
SELECT DEPTNO, DNAME, MAX(SAL)
FROM EMP NATURAL JOIN DEPT
WHERE MAX(SAL) > (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 20)
GROUP BY DEPTNO, DNAME;
(정답)
SELECT DEPTNO, DNAME, MAX(SAL)
FROM EMP NATURAL JOIN DEPT
GROUP BY DEPTNO, DNAME
HAVING MAX(SAL) > (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 20);
SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO= 20
문11 SALESMAN의 커미션 평균을 출력하시오.
SELECT AVG(COMM)
FROM EMP
WHERE JOB = 'SALESMAN';
** =SUB-QUERY
SELECT AVG(COMM)
FROM (SELECT NVL(COMM,0) AS COM
FROM EMP
WHERE JOB = 'SALESMAN');
(3) Multi Row Sub Query 다중형 서브쿼리 : 서브쿼리의 결과가 여러 행이 나오는 경우
-종류
1. IN : 메인쿼리의 비교조건이 서브쿼리의 결과 중에서 하나라도 일치하면 참 ('='과 같다)
2. ANY,SOME : 메인쿼리의 비교조건이 서브쿼리의 검색결과 하나 이상 일치하면 참 ( > ANY,SOME = 'MIN')
3.ALL : 메인쿼리의 비교조건이 서브쿼리의 검색결과 모든 값이 일치하면 참( > ALL = 'MAX')
문12 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원의 이름과 급여, 부서번호를 출력하라
(ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.) ===> 다중형 서브쿼리이므로 IN 사용!
①SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE SAL >= 3000);
(정답)SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE SAL >= 3000);
문13 10번 부서에 근무하는 사원의 이름과 10번 부서의 부서명을 출력하라
(1) JOIN 이용
SELECT E.ENAME, D.DNAME
FROM EMP NATURAL JOIN DEPT
WHERE DEPTNO = 10;
(2) 다중형 서브쿼리, JOIN 이용
SELECT E.ENMAE, D.DNAME
FROM EMP E, (SELECT DEPTNO, DNAME
FROM DEPT
WHERE DEPTNO = 10) D
WHERE E.DEPTNO = D.DEPTNO;
문14 부서별로 가장 급여를 많이 받는 사원의 사원번호, 사원이름, 급여, 부서번호를 출력하라.
(ORA-00934: 그룹 함수는 허가되지 않습니다) ===> WHERE절 그룹함수를 SAL로 고침
①SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE MAX(SAL) IN (SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
(ORA-00913: 값의 수가 너무 많습니다) ===> 서브쿼리 SELECT문에서 DEPTNO 삭제
②SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL IN (SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
(정답)SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
문15 각 부서별 최소 급여를 받는 사원의 이름과 급여를 구하라
SELECT ENAME, SAL
FROM EMP
WHERE SAL IN (SELECT MIN(SAL)
FROM EMP
GROUP BY DEPTNO);
SELECT MIN(SAL)
FROM EMP
GROUP BY DEPTNO;
문16 20번 부서 사원의 사원 직무와 같은 사원직무인 다른 부서의 사원 정보를 검색하라.
SELECT *
FROM EMP
WHERE DEPTNO != 20
AND JOB IN (SELECT DISTINCT JOB
FROM EMP
WHERE DEPTNO = 20);
SELECT JOB
FROM EMP
WHERE DEPTNO = 20
문17 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하시오
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
문18 직급이 매니저인 사람이 속한 부서명, 부서번호, 지역을 출력하시오
SELECT DISTINCT DNAME, DEPTNO, LOC
FROM DEPT NATURAL JOIN EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE JOB = 'MANAGER');
SELECT DEPTNO
FROM EMP
WHERE JOB = 'MANAGER';
문19 영업 사원이 받은 최소 급여보다 많이 받는 사원들의 이름,급여를 출력하시오
(부서번호 20은 제외할 것)
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ANY(SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN')
AND DEPTNO != 20;
===
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MIN(SAL)
FROM EMP
WHERE JOB = 'SALESMAN')
AND DEPTNO != 20;
SELECT MIN(SAL)
FROM EMP
WHERE JOB = 'SALESMAN';
문20 학과별 평균몸무게보다 몸무게가 많은 학과별 학생들의 학번, 이름, 학과명, 몸무게를 출력하시오.
(ORA-00913: 값의 수가 너무 많습니다) ==== > 서브쿼리 SELECT문에서 STU_DEPT 삭제
①SELECT STU_NO, STU_NAME,STU_DEPT,STU_WEIGHT
FROM STUDENT
GROUP BY STU_DEPT
HAVING STU_WEIGHT > (SELECT STU_DEPT, AVG(STU_WEIGHT)
FROM STUDENT
GROUP BY STU_DEPT);
(정답)SELECT STU_NO, STU_NAME, STU_DEPT,STU_WEIGHT
FROM STUDENT
WHERE STU_WEIGHT > ALL(SELECT STU_DEPT,AVG(STU_WEIGHT)
FROM STUDENT
GROUP BY STU_DEPT);
(SELF JOIN으로 풂)
SELECT STUDENT.STU_NO, STUDENT.STU_NAME, STUDENT.STU_DEPT, STU_WEIGHT
FROM STUDENT, (SELECT STU_DEPT, AVG(STU_WEIGHT) AVG_WEIGHT
FROM STUDENT
GROUP BY STU_DEPT) STUDENT_B
WHERE STUDENT.STU_DEPT = STUDENT_B.STU_DEPT
AND STUDENT.STU_WEIGHT > AVG_WEIGHT;
SELECT STU_DEPT, AVG(STU_WEIGHT)
FROM STUDENT
GROUP BY STU_DEPT
문21 급여가 모든 부서들의 평균 급여보다 많은 사원 정보를 검색하라. (ALL)
SELECT *
FROM EMP
WHERE SAL > ALL(SELECT AVG(SAL)
FROM EMP
GROUP BY DEPTNO);
문22 영업 사원들 보다 급여를 많이 받는 사원의 사원이름, 급여를 출력하시오. (ALL)
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
문23 컴퓨터 정보과 최소 신장보다 큰 학과의 학과명과 최소 신장을 출력하시오.
(ORA-00934: 그룹 함수는 허가되지 않습니다) ===> WHERE절에 그룹함수를 HAVING절로 !!
①SELECT STU_DEPT, MIN(STU_HEIGHT)
FROM STUDENT
WHERE MIN(STU_HEIGHT) > (SELECT MIN(STU_HEIGHT)
FROM STUDENT
WHERE STU_DEPT = '컴퓨터정보');
(정답)
SELECT STU_DEPT, MIN(STU_HEIGHT)
FROM STUDENT
GROUP BY STU_DEPT
HAVING MIN(STU_HEIGHT) > (SELECT MIN(STU_HEIGHT)
FROM STUDENT
WHERE STU_DEPT = '컴퓨터정보');
문24 101번 과목을 수강한 학생들의 정보를 구하라.
(부질의를 조인 질의로 표현 가능하다)
SELECT *
FROM STUDENT
WHERE STU_NO IN (SELECT STU_NO
FROM ENROL
WHERE SUB_NO = 101);
===============
(NATURAL JOIN을 사용하면 자동으로 중복되는 STU_NO를 잡아주니, 찾고자 하는 조건만 입력하면 OK)
SELECT *
FROM STUDENT NATURAL JOIN ENROL
WHERE SUB_NO = 101;
(4) SET연산자 : SELECT문에 작성
: 독립적인 여러 개의 쿼리를 하나로 묶는 역할
: 최종 결과는 하나의 쿼리가 수행된 것과 같다.
: 수학의 집합과 같은 개념
: SELECT 리스트의 컬럼의 개수, 순서, 타입이 같아야 함
1.UNION : QUERY 결과값의 합이며 중복 제거 (합집합)
SELECT * FROM EMP WHERE DEPTNO = 20
UNION
SELECT * FROM EMP WHERE DEPTNO = 10;
===
SELECT * FROM EMP WHERE DEPNTO IN (10,20)
2.UNION ALL : 중복 포함 (합집합)
SELECT * FROM EMP WHERE DEPTNO = 20
UNION ALL
SELECT * FROM EMP WHERE DEPTNO = 10;
3.INTERSECT :중복 값(교집합)
SELECT * FROM EMP WHERE DEPTNO = 20
INTERSECT
SELECT * FROM EMP WHERE DEPTNO = 10;
4.MINUS : 첫번째 결과값에서 두번째 결과값을 뺀 값
SELECT * FROM EMP WHERE DEPTNO = 20
MINUS
SELECT * FROM EMP WHERE DEPTNO = 10;
문25 전기전자과인 학생들과 컴퓨터 정보과인 학생들의 전체정보를 나타내시오.
SELECT *
FROM STUDNET
WHERE STU_DEPT = '전기전자'
UNION
SELECT *
FROM STUDNET
WHERE STU_DEPT = '컴퓨터정보';
문26 전체 학생 정보에서 1학년 학생 정보를 제외하고 전체정보를 출력하시오.
SELECT *
FROM STUDENT
MINUS
SELECT *
FROM STUDENT
WHERE STU_GRADE = 1;
문27 전체 학생 정보에서 기계과이면서 1학년생인 정보를 출력하시오.
SELECT *
FROM STUDENT
INTERSECT
SELECT *
FROM STUDENT
WHERE STU_DEPT = '기계'
AND STU_GRADE = 1;
문28 SALESMAN 이지만, 보너스를 받지 않는 사원의 정보를 모두 출력하시오 (SET)
SELECT *
FROM EMP
WHERE JOB = 'SALESMAN'
MINUS
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;
====
SELECT *
FROM EMP
WHERE JOB = 'SALESMAN'
INTERSECT
SELECT *
FROM EMP
WHERE COMM IS NULL;
(5) FROM 절의 부질의 : 부질의를 하나의 테이블로 생각하여 FROM절에 입력
: 테이블 명이 없으므로 별칭을 붙여 사용
: join 방식을 사용하는 것과 같다.
**문29 학생들의 학과별 평균 신장보다 큰 신장의 학생들 정보를 출력하라.
select stu_no, stu_name, a.stu_dept, stu_height, avg_height
from student a, (select stu_dept, round(avg(stu_height)) avg_height
from student
group by stu_dept) b
where a.stu_dept = b.stu_dept and stu_height > avg_height;
select sut_dept, avg(stu_height)
from student
group by stu_dept
**문30 점수가 각 학과 학생들의 평균 점수보다 높은 학생의 학번을 검색하라.
select a.stu_no,a.stu_dept
from student a, (select stu_dept,avg(enr_grade) avg_enr_grade
from student natural join enrol
group by stu_dept) b, enrol e
where a.stu_dept = b.stu_dept and a.stu_no=e.stu_no and
e.enr_grade > b.avg_enr_grade;
문31 컴퓨터정보과 학생들의 평균 점수를 구해 학생들의 학번과 이름, 평균 점수를 성적 순으로 검색하라.
select stu_no,stu_name,avg(enr_grade)
from student natural join enrol
where stu_dept = '컴퓨터정보'
group by stu_no,stu_name
order by 3;
(서브쿼리로 풀면?)
select stu_no, stu_name, avg(enr_grade)
from student natural join enrol
group by stu_no, stu_name
having avg(enr_grade) in (select avg(enr_grade)
from student natural join enrol
where stu_dept = '컴퓨터정보'
group by stu_no)
order by 3;
문32 사원번호, 사원이름을 부하직원수가 많은 순으로 검색하라.
select b.empno,b.ename,count(*)
from emp a join emp b on a.mgr = b.empno
group by b.empno, b.ename
order by 3 desc;