다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Presentation on theme: "다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL"— Presentation transcript:

1 다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL
서진수 저

2 5장. Sub Query 를 배웁니다 1

3 5. Sub Query 1. Sub Query 란? Main Query , Outer Query Sub Query ,
SELECT select_list FROM table 또는 View WHERE 조건 연산자 ( SELECT select_list FROM table WHERE 조건 ) ; Sub Query , INNER Query 2

4 5. Sub Query Emp 테이블에서 ‘SCOTT’ 보다 급여를 많이 받는 사람의 이름과 급여를 출력하세요. 3
SCOTT>SELECT ename , sal 2 FROM emp 3 WHERE sal > ( SELECT sal FROM emp WHERE ename='SCOTT') ; 3

5 5. Sub Query -Sub Query 작성 시 주의 사항
Sub Query 부분은 Where 절에 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 합니다. 특별한 경우 (Top-n 분석 등)를 제외하고는 Sub Query 절에 Order by 절이 올 수 없습니다. - 단일 행 Sub Query 와 다중 행 Sub Query 에 따라 연산자를 잘 선택해야 합니다. 4

6 5. Sub Query 2. Sub Query의 종류 5

7 5. Sub Query 1) 단일 행 Sub Query 6 연산자 의 미 = 같다 (Equal to) <>
의 미 = 같다 (Equal to) <> 같지 않다 (Not Equal to) > 크다 (Greater Than) >= 크거나 같다 (Greater Than or Equal to) < 작다 (Less Than) <= 작거나 같다 (Less Than or Equal to) 6

8 5. Sub Query 단일 행 Sub Query 연습문제 1:
Student 테이블과 department 테이블을 사용하여 이윤나 학생과 1 전공(deptno1)이 동일한 학생들의 이름과 1전공 이름을 출력하세요. 7

9 Select s. name, d. dname From student s, department d Where s
Select s.name, d.dname From student s, department d Where s.deptno1 = d.deptno And s.deptno1 = (select deptno1 from student where name=‘이윤나’);

10 5. Sub Query (2) 단일 행 Sub Query 연습문제 2:
Professor 테이블에서 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요. 8

11 Select p. name, p. hiredate, d
Select p.name, p.hiredate, d.dname From professor p, department d Where p.deptno = d.deptno And hiredate > (select hiredate from professor where name = ‘송도권’);

12 5. Sub Query (3) 단일 행 Sub Query 연습 문제 3:
Student 테이블에서 1 전공(deptno1)이 101번 인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를 출력하세요. 9

13 Select name, weight From student Where weight > (select avg(weight) from student where deptno1=101);

14 5. Sub Query (4) 단일 행 Sub Query 예 4:
Professor 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게 받는 교수의 이름과 급여, 입사일을 출력하세요. 10

15 Select name, pay, hiredate From professor Where hiredate = (Select hiredate From professor where name=‘심슨’) And pay < (select pay from professor where name=‘조인형’);

16 5. Sub Query 2) 다중 행 Sub Query 11 연산자 의 미 IN 같은 값을 찾음 >ANY 최소값을 반환함
의 미 IN 같은 값을 찾음 >ANY 최소값을 반환함 <ANY 최대값을 반환함 <ALL >ALL EXIST Sub Query 의 값이 있을 경우 반환함 11

17 5. Sub Query (1) 다중 행 Sub Query 예 1:
Emp2 테이블과 Dept2 테이블을 참조하여 근무지역(dept2 테이블의 area 컬럼)이 서울 지사인 모든 사원들의 사번과 이름, 부서번호를 출력하세요. SCOTT> SELECT empno, name, deptno 2 FROM emp2 3 WHERE deptno IN (SELECT dcode FROM dept2 WHERE area='서울지사') ; 12

18 5. Sub Query (2) 다중 행 Sub Query 연습문제 1:
Emp2 테이블을 사용하여 전체 직원 중 과장 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급 , 연봉을 출력하세요. 단 연봉 출력 형식은 아래와 같이 천 단위 구분기호와 원 표시를 하세요. 13

19 Select name, position, to_char(pay, ‘999,999,999’)||’ 원’ From emp2 Where pay > any (select pay from emp2 where position=‘과장’) ;

20 5. Sub Query (3) 다중 행 Sub Query 연습문제 2:
Student 테이블을 조회하여 전체 학생 중에서 체중이 4학년 학생들의 체중에서 가장 적게 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요 14

21 Select name, grade, weight From student Where weight < all (select weight from student where grade=4);

22 5. Sub Query 3) 다중 컬럼 Sub Query (1) 다중 컬럼 Sub Query 예 1 :
Student 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요. 15

23 5. Sub Query 16 SCOTT> SELECT grade "학년" ,name "이름" , height "키"
2 FROM student 3 WHERE (grade,height) IN (SELECT grade, MAX(height) FROM student GROUP BY grade ) 6 ORDER BY 1 ; 16

24 5. Sub Query (2) 다중 컬럼 Sub Query 연습문제 1 :
Professor 테이블을 조회하여 각 학과별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요. (학과이름순으로 오름차순 정렬하세요) 17

25 Select p. profno, p. name, p. hiredate, d
Select p.profno, p.name, p.hiredate, d.dname From professor p, department d Where p.deptno=d.deptno And (p.deptno, p.hiredate) in (select deptno, min(hiredate) from professor group by deptno) Order by 4;

26 5. Sub Query (3) 다중 컬럼 Sub Query 연습문제 2:
Emp2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름과 직급, 연봉을 출력하세요. 연봉순으로 오름차순 정렬하세요. 18

27 Select name, position, pay From emp2 Where (position, pay) in (select position, max(pay) from emp2 group by position) Order by 3;

28 5. Sub Query (4) 다중 컬럼 Sub Query 연습문제 3:
Emp2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요 19

29 Select d,dname, e. name, e. pay From emp2 e, dept2 d Where e. deptno=d
Select d,dname, e.name, e.pay From emp2 e, dept2 d Where e.deptno=d.dcode And e.pay < all (select avg(pay) from emp2 group by deptno) Order by 3;

30 5. Sub Query 4) 상호 연관 Sub Query 상호 연관 Sub Query 예 1:
Emp2 테이블을 조회해서 직원 들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의 이름과 직급, 현재 연봉을 출력하세요. SCOTT>SELECT name "사원이름", position "직급" , pay "급여" 3 FROM emp2 a 4 WHERE pay >= ( SELECT AVG(pay) FROM emp2 b WHERE a.position=b.position) ; 20

31 3. Scalar Sub Query (스칼라 서브쿼리)
21

32 5. Sub Query - Scalar Sub Query 예 : emp2 테이블과 dept2 테이블을
emp2 테이블과 dept2 테이블을 조회하여 사원들의 이름과 부서이름을 출력하세요. 22