서브 쿼리 : 단일 행 서브 쿼리
* 서브 쿼리란?
단일 행 서브 쿼리 :
- 서브 쿼리가 하나의 컬럼에서 하나의 행을 검색한다.
다중 행 서브 쿼리
- 서브 쿼리가 하나의 컬럼에서 여러 개의 행을 검색한다.
다중 열 서브 쿼리 (IN)
- 서브 쿼리가 여러 개의 컬럼을 검색한다.
서브 쿼리는 WHERE절, HAVING절과 같이 조건 절에 주로 쓰이고 FROM절에 쓰이는 경우도 있다. 특
히 FROM절에 쓰인 서브 쿼리는 인라인 뷰(Inline View)라고 부른다.
단일 행 서브 쿼리
SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
FROM 테이블
WHERE 컬럼 단일_행_연산자 (SELECT 문장 : Sub query문)- 단일 행 연산자가 사용됨으로 반드시 서브 쿼리의 결과 값은 한 개만 검색돼야 한다.
- 단일 행 연산자 오른쪽에 기술한다.
(=, <, >, <=, >=, !=)
- WHERE절에 기술된 열의 숫자와 타입은 SELECT절과 1:1 대응 관계가 되어야 한다.
예제 1. 남궁연호보다 급여를 많이 받는 사원을 검색한다.
SELECT eno 사번, ename 이름
FROM emp
WHERE sal > (SELECT sal
FROM emp
WHERE ename = '남궁연호');
사번 이름
------- ----------
0001 안영희
1001 문시현
1002 김주란
1003 양선호
2003 정의찬
0120 김경현
예제 2. 김선유보다 급여를 많이 받는 사원을 검색한다.
SELECT eno 사번, ename 이름
FROM emp
WHERE sal > (SELECT sal
FROM emp
WHERE ename = '김선유');
WHERE sal > (SELECT sal
*
3행에 오류:
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.동명이인이 있을 수 있기 때문에 검색이 안된다.
* 예측하기 힘든 단일 행 서브 쿼리를 수정하는 방법
① '=' 연산자는 'IN' 연산자로 바꾼다.
② 부등호 ('<', '>', '<=', '>=')는 any, all 연산자를 추가한다.
(< 일때, any는 항목에 가장 큰값보다 작으면 가능, all은 가장 작은값보다 작으면 가능)
③ Max(), Min()과 같은 그룹 함수를 사용 한다.
예제 3. 문시현과 부서가 다르고 동일한 업무를 하는 사원의 정보를 검색한다.
SELECT eno 사번, ename 이름, dno 부서번호, job 업무
FROM emp
WHERE dno != (SELECT dno FROM emp WHERE ename = '문시현')
AND job = (SELECT job FROM emp WHERE ename = '문시현');
사번 이름 부서번호 업무
------- ---------- --------- -------
1002 김주란 20 모델링
1003 양선호 30 모델링
예제 4. 부산에서 근무하는 사원의 정보를 검색한다.
SELECT eno 사번, ename 이름
FROM emp
WHERE dno = (SELECT dno
FROM dept
WHERE loc = '부산');
사번 이름
------- ----------
1002 김주란
2002 제갈민
3002 권아현
0120 김경현
SELECT eno 사번, ename 이름
FROM emp e, dept d
WHERE e.dno = d.dno
AND loc = '부산';
사번 이름
------- ----------
1002 김주란
2002 제갈민
3002 권아현
0120 김경현
실습
1. 김혁윤 보다 평점이 우수한 학생의 학번과 이름을 검색한다.
SELECT sno 학번, sname 이름, avr 평점
FROM student
WHERE avr > (SELECT avr
FROM student
WHERE sname = '김혁윤');
학번 이름 평점
------- ---------- ----------
933904 임영현 3.98
924501 권석복 4
915601 강태용 3.9
925603 최지현 3.89
945601 심정용 3.9
918203 장운영 3.89
918205 독고낭도 3.99
학번 이름 평점
------- ---------- ----------
938202 신섭인 3.93
938204 최현 3.92
2. 권현와 동일한 학년 학생 중에 평점이 강은혜와 동일한 학생을 검색한다.
SELECT syear 학년, sno 학번, sname 이름, avr 평점
FROM student
WHERE syear = (SELECT syear FROM student WHERE sname = '권현')
AND avr = (SELECT avr FROM student WHERE sname = '강은혜');
학년 학번 이름 평점
---------- ------- ---------- ----------
1 925309 오우재 2.99
1 945605 오경운 2.99
3. 이학수학 과목과 동일한 학점수인 과목을 검색한다.
SELECT st_num 학점, cno 과목번호, cname 과목명
FROM course
WHERE st_num = (SELECT st_num FROM course WHERE cname = '이학수학');
학점 과목번호 과목명
---------- --------- ------------
2 1211 일반화학실험
2 1214 무기화학
2 1216 환경화학
2 1218 생화학
2 1227 이학수학
2 1228 위상수학
2 2365 전자기학
학점 과목번호 과목명
---------- --------- ------------
2 2366 물리실험
2 2369 무기화학실험
2 1711 유전학실험
2 1245 실험물리학
2 2112 영양학실험
2 2313 분류학실험
4. 타 학과에 송강 교수와 동일한 지위의 교수 명단을 검색한다.
SELECT pno 교수번호, pname 교수이름, orders 지위, section 학과
FROM professor
WHERE section != (SELECT section FROM professor WHERE pname = '송강')
AND orders = (SELECT orders FROM professor WHERE pname = '송강');
교수번호 교수이름 지위 학과
--------- ---------- ------- -------
1010 이규진 정교수 물리
1009 이준영 정교수 물리
1021 왕사진 정교수 생물
1029 주동평 정교수 생물
1030 김동평 정교수 유공
1033 박삭광 정교수 유공
1035 장관용 정교수 식영
5. 제갈민의 입사일보다 나중에 부임한 교수의 명단을 검색한다.
SELECT pno 교수번호, pname 교수이름, hiredate 부임일자
FROM professor
WHERE hiredate > (SELECT hdate FROM emp WHERE ename = '제갈민');
교수번호 교수이름 부임일자
--------- ---------- ----------
1001 송강 2002/08/12
1006 장청아 2003/05/20
1007 이초아 2010/07/06
1008 문규식 2005/02/11
1010 이규진 1998/10/07
1013 하영진 1999/04/19
1012 이영준 2000/05/18
교수번호 교수이름 부임일자
--------- ---------- ----------
1016 호연작 2009/10/21
1018 김응전 1999/02/18
1021 왕사진 1997/02/17
1027 임충원 2001/06/03
1029 주동평 2006/01/26
1030 김동평 2002/02/15
1017 최무송 2011/01/24
교수번호 교수이름 부임일자
--------- ---------- ----------
1033 박삭광 2005/07/14
1034 최해연 1998/07/02
1038 하영진 2004/03/02
6. 강태용보다 일반 화학 과목의 학점이 더 낮은 학생의 명단을 학점과 검색한다.
// 성적으로 검색
SELECT s.sno 학번, sname 이름, result 성적, cname 과목이름
FROM student s, course c, score r
WHERE s.sno = r.sno
AND r.cno = c.cno
AND cname = '일반화학'
AND result < (SELECT result
FROM student s, score r, course c
WHERE s.sno = r.sno
AND r.cno = c.cno
AND sname='강태용'
AND cname='일반화학');
학번 이름 성적 과목이름
------- ---------- ---------- ------------
913902 황진혜 70 일반화학
913903 정도정 57 일반화학
913904 이호란 27 일반화학
914502 문수보 60 일반화학
915301 정동상 44 일반화학
915305 최정희 53 일반화학
915604 정성현 54 일반화학
// 학점으로 검색
SELECT s.sno 학번, s.sname 이름, result 성적, sc.grade 학점, cname 과목이름
FROM student s, score r, course c, scgrade sc
WHERE s.sno=r.sno
AND r.cno=c.cno
AND result between loscore and hiscore
AND grade > (SELECT grade
FROM student s, score r, course c, scgrade sc
WHERE s.sno=r.sno
AND r.cno=c.cno
AND result between loscore and hiscore
AND sname='강태용'
AND cname='일반화학')
AND cname='일반화학';
학번 이름 성적 학점 과목이름
------- ---------- ---------- ----- ------------
913903 정도정 57 C 일반화학
913904 이호란 27 F 일반화학
914502 문수보 60 C 일반화학
915301 정동상 44 D 일반화학
915305 최정희 53 D 일반화학
915604 정성현 54 D 일반화학
918201 이경철 62 C 일반화학
SELECT s.sno, sname, cname, grade
FROM student s, course c, score r, scgrade g
WHERE s.sno=r.sno AND r.cno=c.cno AND result BETWEEN loscore AND hiscore
AND cname = '일반화학'
AND grade > ( SELECT grade
FROM student s, course c, score r, scgrade g
WHERE s.sno=r.sno AND r.cno=c.cno
AND result BETWEEN loscore AND hiscore
AND sname = '강태용'
AND cname = '일반화학');
SNO SNAME CNAME GRADE
------- ---------- ------------ -------
913903 정도정 일반화학 C
913904 이호란 일반화학 F
914502 문수보 일반화학 C
915301 정동상 일반화학 D
915305 최정희 일반화학 D
915604 정성현 일반화학 D
918201 이경철 일반화학 C
'SQL 공부 기록' 카테고리의 다른 글
| SQL 12 - 집합 연산자 (0) | 2021.11.12 |
|---|---|
| SQL 11 - 서브 쿼리 : 다중 행, 다중 열 서브 쿼리 (0) | 2021.11.09 |
| SQL 9 - 조인 : 자기 참조 조인(Self join)과 외부조인(Outer join) (0) | 2021.11.04 |
| SQL 8 - 조인 : 등가 조인(Equi join)과 비등가 조인(Non Equi-join) (0) | 2021.11.01 |
| SQL 7 - BETWEEN과 IN 연산자 (0) | 2021.10.28 |