★★서브쿼리문(하위 질의문)
-★WHERE, HAVING절 하위질의문
-FROM절 하위질의문 => (n-tier)
문> 사원의 평균급여보다 많이 받는 직원의 내역을 출력하라.
SELECT AVG(salary) FROM employees
SELECT last_name, salary FROM employees
WHERE salary >6461.831775700934579439252336448598130841; //두번 질의하는 것의 번거로움
서브쿼리 작성 순서
-
서브쿼리문 먼저 작성 ( 먼저 테스트를 해봐야 함.)
-
메인쿼리문 작성
SELECT last_name, salary FROM employees //메인쿼리
WHERE salary > (SELECT AVG(salary) FROM employees) //서브쿼리
퀴즈hr> 'Chen' 사원보다 salary를 많이 받는 사원의 목록을 출력하시오. (chen = 8200)
SELECT last_name, salary FROM employees
WHERE salary >(SELECT salary FROM employees WHERE last_name = 'Chen');
<mision kosta202>
-
'정의찬'과 부서(dept)가 다르지만 동일한 업무(job)를 수행하는 사원 목록을 출력하라
-
'관우'보다 일반화학과목의 학점이 낮은 학생의 명단을 출력하라.
->SELECT e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.dno = d.dno AND e.job = (SELECT job FROM emp WHERE ename = '정의찬')
AND d.dno != (SELECT dno FROM emp WHERE ename = '정의찬');
->SELECT s.sname, s.major, c.cname, o.result, g.grade
FROM student s, score o, scgrade g, course c
WHERE s.sno = o.sno
AND c.cno = o.cno AND c.cname = '일반화학'
AND result BETWEEN loscore AND hiscore
AND grade > (SELECT grade
FROM student s, score o, scgrade g, course c
WHERE s.sno = o.sno
AND c.cno = o.cno AND c.cname = '일반화학' AND s.sname = '관우'
AND result BETWEEN loscore AND hiscore);
**HAVING절 서브쿼리(일부그룹만 추출 => having)
문제> 부서 중 가장 급여를 많이 받는 부서를 검색하라.
SELECT MAX(AVG(sal)) FROM emp
GROUP BY dno // 부서별로 가장 많이 받는 부서
SELECT dno FROM emp
GROUP BY dno
HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp
GROUP BY dno)
//평균급여가 300달러인 부서를 출력, 부서를 하나의 단위로 봄
퀴즈 kosta202> 학생 인원수가 가장 많은 학과를 검색하라.
=>SELECT major FROM student
GROUP BY major
Having count(*) = (SELECT Max(count(*))
FROM student
GROUP BY major);
<mission kosta202>
-
학생중 기말고사 평균성적이 가장 낮은 학생의 정보를 검색하라.
-
화학과 1학년 학생중에 평점이 평균이하인 학생을 검색하라.
->SELECT s.sno, s.sname
FROM student s, score c
WHERE s.sno = c.sno
GROUP BY s.sno, s.sname
HAVING AVG(result) = ( SELECT MIN(AVG(result)) FROM score GROUP BY sno);
->SELECT *
FROM student
WHERE major ='화학' AND syear = 1
AND avr <= (SELECT AVG(avr)
FROM student
WHERE major = '화학'
AND syear = 1);
**다중컬럼, 다중로우
문제hr> 직무(job_id)별 최대급여자의 사원내역을 출력하라.
->SELECT MAX(salary),job_id FROM employees
GROUP BY job_id
-> SELECT employee_id, last_name, salary, job_id
FROM employees
WHERE (salary, job_id) IN (SELECT MAX(salary), job_id FROM employees
GROUP BY job_id); //동일한 max금액을 가진 job_id도 다 나오게 됨. max값만 던져서 생기는 오류, 그래서 salary와 job_id라는 조건 2개를 함께 적어줘야 함.
퀴즈 kosta202> 01번(부서번호) 부서원들과 보너스(comm)가 같은 사원을 검색하라.
=>SELECT *
FROM emp
WHERE comm IN (SELECT comm FROM emp WHERE dno=1);
WHERE 칼럼 = (SELECT ~) => 단일로우
WHERE 칼럼 IN (SELECT ~) => 다중로우
IN : 검색된 값 중에 하나만 일치하면 참 (개별적인 값)
ANY : 검색된 값 중에 조건에 맞는 것이 하나 이상 있으면 참
ALL : 검색된 값 중에 조건에 모두 일치해야 함
*칼럼 > max same 컬럼 > ALL(서브쿼리) : 가장 큰 값 보다 크다.
*칼럼 < min same 컬럼 < ALL(서브쿼리) : 가장 작은 값 보다 작다.
*칼럼 > min same 컬럼 > ANY(서브쿼리) : 가장 작은 값 보다 크다.
*칼럼 < max same 컬럼 < ANY서브쿼리) : 가장 큰 값 보다는 작다.
문제 kosta202> 10번 부서에 가장 작은 급여자보다 작게 받는 급여자를 출력하라.
SELECT * FROM emp
WHERE sal < (SELECT min(sal) FROM emp
WHERE dno = 10);
SELECT * FROM emp
WHERE sal < ALL(SELECT sal FROM emp
WHERE dno = 10);
<mission hr>
-
부서번호 30번 최대급여자보다 급여가 높은 사원을 출력하라.
-
부서번호 30번 최대급여자보다 급여가 작은 사원을 출력하라.
SELECT * FROM employees
WHERE salary > ALL(SELECT salary FROM employees
WHERE department_id = 30);
SELECT * FROM employees
WHERE salary < ANY(SELECT salary FROM employees
WHERE department_id = 30);
<mission kosta202>
-
'손하늘'과 동일한 관리자(mgr)의 관리를 받으면서 업무도 같은 사람을 검색하라.
-
화학과 학생과 평점이 동일한 학생을 검색하라.
-
화학과 학생과 같은 학년에서 평점이 동일한 학생을 검색하라.
-
기말고사 평균 성적이 '핵화학' 과목 평균 성적보다 우수한 과목번호, 과목명, 담당교수를 검색하라.
->SELECT * FROM emp
WHERE (mgr,job) IN (SELECT mgr,job FROM emp WHERE ename = '손하늘'); //상사가 2명이상 있을 수도 있으니까 IN사용
->SELECT * FROM student
WHERE avr IN (SELECT avr FROM student
WHERE major = '화학');
->SELECT * FROM student
WHERE (syear,avr) IN (SELECT syear,avr FROM student
WHERE major = '화학');
->SELECT c.cno, c.cname, p.pname, AVG(result)
FROM course c, professor p, score s
WHERE p.pno = c.pno AND c.cno = s.cno
GROUP BY c.cno, c.cname, p.pname
HAVING AVG(result) > (SELECT avg(result)
FROM course c, professor p, score s
WHERE p.pno = c.pno AND c.cno = s.cno AND c.cname = '핵화학');
//조인을 하든가 서브쿼리를 쓰든가 선택의 자유
**FROM절 서브쿼리(Top-N SQL) (서브쿼리 안에 서브쿼리)
문제> 입사순서 5명을 출력하라.
SELECT employee_id, last_name, hire_date
FROM employees
ORDER BY hire_date
SELECT ROWNUM, alias.*
FROM (SELECT employee_id, last_name, hire_date
FROM employees
ORDER BY hire_date
)alias
WHERE ROWNUM <=5;
퀴즈 hr> 급여를 많이 받는 순서 3명의 사원정보를 출력하라.
->SELECT ROWNUM, alias.*
FROM (SELECT * FROM employees
ORDER BY salary)alias
WHERE ROWNUM <=3;
**주의 사항
CREATE TABLE board(
seq number PRIMARY KEY,
title VARCHAR2(50),
writer VARCHAR2(50),
contents VARCHAR2(200),
regdate date,
hitcount number
)
INSERT INTO board VALUES( 1, 'a1', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 2, 'a2', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 3, 'a3', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 4, 'a4', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 5, 'a5', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 6, 'a6', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 7, 'a7', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 8, 'a8', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 9, 'a9', 'a', 'a', sysdate, 0);
INSERT INTO board VALUES( 10, 'a10', 'a', 'a', sysdate, 0);
SELECT * FROM board
ORDER BY seq DESC
SELECT * FROM
(SELECT * FROM board
ORDER BY seq DESC
)
WHERE ROWNUM BETWEEN 5 AND 8; //첫번째가 포함이 안됨.-> 출력이 안됨 계속해서 null, ====>ROWNUM은 항상 1부터 시작해서 값이 출력이 안됨.
=> 문제해결(페이징처리)
SELECT ROWNUM as ROW_NUM, temp,*
FROM (SELECT * FROM board
ORDER BY seq DESC
) temp
SELECT * FROM(
SELECT ROWNUM as ROW_NUM, temp.*
FROM (SELECT * FROM board
ORDER BY seq DESC
) temp
) WHERE ROW_NUM BETWEEN 5 AND 8;
<mission kosta202>
-
4.5환산평점이 가장 높은 3명의 학생을 검색하라.
-
화학과 학생 중에 기말고사 유기화학 점수가 최하위 학생 3명을 검색하라.
-
기말고사 과목별 평균이 높은 3과목을 검색하라.
->SELECT sno, sname,sex, syear, major, (avr*4.5)/4.0
FROM student ORDER BY (avr*4.5)/4.0 DESC;
**ORDER BY 3번째꺼 DESC라고 써도 됨.
SELECT ROWNUM, alias.*
FROM (SELECT sno, sname,sex, syear, major, (avr*4.5)/4.0
FROM student ORDER BY (avr*4.5)/4.0 DESC
)alias
WHERE ROWNUM <=3;
->SELECT s.sname,s.major,c.cname, o.result
FROM student s,course c, score o
WHERE c.cno = o.cno AND s.sno = o.sno
AND cname = '유기화학' AND s.major ='화학'
ORDER BY result;
SELECT ROWNUM, alias.*
FROM (SELECT s.sname,s.major,c.cname, o.result
FROM student s,course c, score o
WHERE c.cno = o.cno AND s.sno = o.sno
AND cname = '유기화학' AND s.major ='화학'
ORDER BY result
)alias
WHERE ROWNUM <=3;
->SELECT c.cname, avg(result)
FROM course c, score o
WHERE c.cno = o.cno
GROUP BY c.cname
ORDER BY avg(result) DESC;
SELECT ROWNUM, alias.*
FROM (SELECT c.cname, avg(result)
FROM course c, score o
WHERE c.cno = o.cno
GROUP BY c.cname
ORDER BY avg(result) DESC
)alias
WHERE ROWNUM <=3;
'FULLSTACK > DB' 카테고리의 다른 글
| DB 6차시 - 제어문(조건문), 예외처리, 커서, 프로시저, 함수 (0) | 2020.11.13 |
|---|---|
| DB 5차시 - 인덱스, 뷰, 시퀀스, PL/SQL (0) | 2020.11.13 |
| DB 3차시 - 조인(SELF, OUTER) (0) | 2020.11.13 |
| DB 2차시 - 그룹함수, 다양한 함수들, DDL, DML (0) | 2020.11.13 |
| DB 1차시 - DB개념, 연산자 (0) | 2020.11.13 |