[SQL]서브쿼리문

서브 쿼리

 

1. 서브 쿼리는 비교 또는 조회대 상의 오른쪽에 놓이며 괄호 ( )로 묶어서 사용한다.

2. 특수한 경우를 제외하고(거의 없음) ORDER BY 절을 사용할 수 없다.

3. 왼쪽이 sal을 썼다면 오른쪽도 sal이 나와야 한다.

4. 비교 연산자만 사용한다.

 

 

--EMP 테이블이 존재할수도있으니 제거해준다.
drop table EMP;

--EMP 테이블 생성
create table EMP(
    EMPNO      number,
    ENAME      varchar2(20),
    JOB        varchar2(20),
    SAL        number,
    LOCAL      varchar2(20)
);

-- 속성 생성
insert into EMP values(1, '홍길동', '영업', 200, '대전');
insert into EMP values(2, '이순신', '경영', 500, '서울');
insert into EMP values(3, '안중근', '기획', 350, '경기');
insert into EMP values(4, '윤봉길', '기획', 400, '인천');
insert into EMP values(5, '코끼리', '영업', 350, '부산');
insert into EMP values(6, '호랑이', '경영', 300, '경기');
insert into EMP values(7, '강아지', '영업', 500, '인천');
insert into EMP values(8, '고양이', '기획', 450, '강원');
insert into EMP values(9, '두더지', '영업', 250, '대전');
insert into EMP values(10, '미어켓', '영업', 250, '대전');

문제 1) 직원의 평균 월급보다 높은 직원들은 누구인가

 

직원들의 평균을 구한 다음, 급여와 비교해서 평균보다 큰 사람들을 출력한다.

select *
    from EMP
    where sal > (
        select AVG(sal)
            from EMP);

문제 2) 홍길동과 다른 지역에 사는 사람은??

select *
    from EMP
    where LOCAL != (
        select LOCAL
            from EMP
            where ENAME = '홍길동');

 

문제 3) 홍길동과 다른 업무를 하는 사람은??

select *
    from EMP
    where JOB != (
        select JOB
            from EMP
            where ENAME = '홍길동');    
    

문제 4) 홍길동과 다른지역에 살면서 동일한 업무를 하는 사람은??

1) 다른 지역에 사는 사람 쿼리문 작성

select LOCAL
from EMP
where ENAME = '홍길동';

2) 동일한 업무 하는 사람 쿼리문 작성

select JOB
from EMP
where ENAME = '홍길동';

3) Main 쿼리문 작성 후 and 생성

select * 
    from EMP
    where A 
    and 
    B

 

4) 앞뒤로 만들어준 쿼리문을 넣어준다.

select * 
    from EMP
    where 
    local != (
        select LOCAL
        from EMP
        where ENAME = '홍길동'
    )
    and
    JOB = (
        select JOB
        from EMP
        where ENAME = '홍길동'
    );

 

LOC 테이블을 생성해줘서 지역에 번호를 주었다.

-- EMP 테이블에 LOCAL을 LOCALID로 해서 재생성해준다.
drop table EMP;

create table EMP(
    EMPNO      number,
    ENAME      varchar2(20),
    JOB        varchar2(20),
    SAL        number,
    LOCALID    number			-- 변경
);

-- 지역들을 number로 수정
insert into EMP values(1, '홍길동', '영업', 200, 4);
insert into EMP values(2, '이순신', '경영', 500, 1);
insert into EMP values(3, '안중근', '기획', 350, 2);
insert into EMP values(4, '윤봉길', '기획', 400, 4);
insert into EMP values(5, '코끼리', '영업', 350, 5);
insert into EMP values(6, '호랑이', '경영', 300, 2);
insert into EMP values(7, '강아지', '영업', 500, 3);
insert into EMP values(8, '고양이', '기획', 450, 5);
insert into EMP values(9, '두더지', '영업', 250, 1);
insert into EMP values(10, '미어켓', '영업', 250,3);

--LOC 테이블 생성
create table LOC(
   	LOCALID   number,
    	NAME    varchar2(20)
);

-- LOC 값을 넣어준다. EMP에 있는 지역과 맞춰준다.
insert into LOC values(1, '서울');
insert into LOC values(2, '경기');
insert into LOC values(3, '인천');
insert into LOC values(4, '대전');
insert into LOC values(5, '부산');

등가 조인을 사용해서 서울에 사는 직원을 출력

select EMP.LOCALID, EMPNO, ENAME, JOB, SAL, NAME  
from EMP, LOC
where LOC.LOCALID=EMP.LOCALID 
----------------------------등가조인 끝
and LOC.NAME='서울'
order by EMP.LOCALID;

등가조인을 사용하지 않고 자연 조인을 사용해서 출력

select *
    from EMP natural join LOC
    where LOC.NAME ='서울'
    order by LOCALID;

---

단일행 서브 쿼리 (실행결과가 하나인 서브 쿼리)

 

위의 마지막 테이블을 그대로 사용한다.

 

EMP와 LOC 테이블에서 '서울'인 1을 뽑아내고 EMP의 SAL속성에서 평균보다 큰 값만 출력한다.

select *
    from EMP E, LOC L
    where E.LOCALID = L.LOCALID		-- 등가 조인
        and E.LOCALID = 1
        and E.SAL > (select AVG(SAL)
                from EMP);

등가 조인을 없애면 교차 조인으로 들어가 버리기 때문에 등가 조인을 없애면 안 된다.

 

1. 평균 급여보다 작은 급여를 받는 사원

select *
    from EMP
    where SAL <=(
            select AVG(SAL)
                from EMP);

2. 평균 급여보다 작은 급여를 받는 영업부 사원

select *
    from EMP
    where JOB = '영업'
    and SAL <=(
            select AVG(SAL)
                from EMP);

3. 교차 조인을 사용하여 평균 연봉보다 적게 받고 경기에 사는 직원

select distinct * 
	from EMP, LOC
	where EMP.LOCALID = LOC.LOCALID
	and EMP.LOCALID = 2
	and SAL <= (
    		select AVG(SAL) 
        	from EMP);

---

다중행 서브 쿼리

 

(필드명) > all : max보다 큰 것,

(필드명) < any : max보다 작은 것 이면 무조건 max 선택

 

그 외는 min이다. (필드명) < all, (필드명) > any 같은 경우

 

예시

drop table tab04;

create table tab04(
    SAL     number
);

insert into tab04 values(&a);
-- sal 값을 50, 100, 150, 200, 250, 300, 350을 넣어준다.

select * 
    from tab04
    where sal > all(100, 200, 300);     --3개중에서 max인 300보다 큰것만 출력
    
select * 
    from tab04
    where sal < any(100, 200, 300);     --3개중에서 max인 300보다 작은것만 출력

select * 
    from tab04
    where sal < all(100, 200, 300);     --3개중에서 min인 100보다 작은것만 출력

select * 
    from tab04
    where sal > any(100, 200, 300);     --3개중에서 min인 100보다 큰것만 출력

---

학과는 3개의 학과 생물학과, 화학과, 물리과

학생들은 국어, 수학, 영어에 대한 점수를 가지고 있다.

화학과 학생들의 총점과 동일한 학생들을 전부 출력

270, 280, 290점이 있는 다른 과 학생들을 뽑고 싶다.

--STU 테이블 생성
create table STU(
    NAME    varchar2(20),
    CLAID   number,
    LANG    number,
    MATH    number,
    ENG     number
);

-- 속성 생성
insert into STU values('홍길동', 1, 90, 90, 90);
insert into STU values('고길동', 1, 85, 95, 60);
insert into STU values('김둘리', 1, 100, 90, 70);
insert into STU values('이도넛', 1, 80, 70, 90);
insert into STU values('마이콜', 1, 50, 80, 100);
insert into STU values('고희동', 1, 95, 95, 95);

insert into STU values('김첨지', 2, 85, 100, 100);
insert into STU values('한지만', 2, 95, 85, 90);
insert into STU values('설렁탕', 2, 95, 100, 90);
insert into STU values('뚝배기', 2, 75, 80, 90);
insert into STU values('김인정', 2, 55, 70, 90);
insert into STU values('백이용', 2, 95, 100, 70);

insert into STU values('우투리', 3, 95, 80, 90);
insert into STU values('바둑이', 3, 85, 100, 60);
insert into STU values('따릉이', 3, 90, 95, 90);
insert into STU values('부릉이', 3, 80, 95, 60);
insert into STU values('오순찌', 3, 95, 85, 85);
insert into STU values('심청이', 3, 100, 100, 100);

-- 화학과 학생들의 총점만 출력해주었다.
select NAME, CLAID,(LANG+MATH+ENG)
    from STU
    where CLAID = 2;
    
-- 전체 학생들의 총점을 출력하였다.
-- 밑에 쿼리문이 제대로 출력되는지 비교 후 확인하기 위해서 출력하였다.
select NAME, CLAID,(LANG+MATH+ENG)
    from STU;

-- 화학과 학생들을 뺀 나머지 학생들중에서 화학과 학생보다 총점이 높은 학생을 구해준다.
select NAME, LANG, MATH, ENG, CLAID, (LANG+MATH+ENG) TOTAL
    from STU
    where NOT CLAID = 2
    and (LANG+MATH+ENG) > all(
        select (LANG+MATH+ENG) TOTAL
            from STU
                where CLAID = 2);

심청이만 나온다면 정상으로 작동

 

STU 테이블에 학년 필드를 추가해준다.

 

화학과 학생들과 같은 학년에서 총점도 동일한 학생들 출력 

 

필드 조건 : 1) 학년을 맨 끝에 화학과는 전부 1학년, 2) 나머지 과들은 1, 2학년 섞여있음

-- STU 테이블을 재생성하기위해 삭제해준다.
drop table STU;

create table STU(
    NAME    varchar2(20),
    CLAID   number,
    LANG    number,
    MATH    number,
    ENG     number,
    GRAD    number
);

-- 학년을 추가해준 필드
insert into STU values('홍길동', 1, 90, 90, 90, 1);
insert into STU values('고길동', 1, 85, 95, 60, 2);
insert into STU values('김둘리', 1, 100, 90, 70, 2);
insert into STU values('이도넛', 1, 80, 70, 90, 1);
insert into STU values('마이콜', 1, 50, 80, 100, 1);
insert into STU values('고희동', 1, 95, 95, 95, 2);

insert into STU values('김첨지', 2, 85, 100, 100, 1);
insert into STU values('한지만', 2, 95, 85, 90, 1);
insert into STU values('설렁탕', 2, 95, 100, 90, 1);
insert into STU values('뚝배기', 2, 75, 80, 90, 1);
insert into STU values('김인정', 2, 55, 70, 90, 1);
insert into STU values('백이용', 2, 95, 100, 70, 1);

insert into STU values('우투리', 3, 95, 80, 90, 1);
insert into STU values('바둑이', 3, 85, 100, 60, 1);
insert into STU values('따릉이', 3, 90, 95, 90, 2);
insert into STU values('부릉이', 3, 80, 95, 60, 2);
insert into STU values('오순찌', 3, 95, 85, 85, 1);
insert into STU values('심청이', 3, 100, 100, 100, 1);

select *
    from STU
    where not CLAID=2			-- 화학과목을 가진 학생들을 빼준다.
    and GRAD = (				-- 학년이 화학과 학생들과 같은 학생들 뽑아준다.
        select distinct GRAD
        from STU
        where CLAID = 2
    )
    and LANG+ENG+MATH in (	    -- 총합이 화학과 학생들과 같은 학생들을 출력
        select LANG+ENG+MATH SCORE
        from STU
        where CLAID=2
    );

 

---

WITH절

 

with로 테이블의 이름을 정의해주면 가져와서 쓸 수 있다.

정의해준 이름들은 with가 끝나는 ; (세미콜론) 까지만 쓸 수 있다.

끝난 뒤에는 쓸 수 없다.

create table tab05(
    sal     number
);

insert into tab05 values(10);
insert into tab05 values(20);
insert into tab05 values(30);
insert into tab05 values(40);
insert into tab05 values(50);

select * from tab05;

select * from (
    select * from tab05
    where sal > 30) t1;
    
-- with를 통해 view 화 시킨다.
-- with부터 명령문이 끝나는 ;까지만 유효범위이다.
-- ; 끝나고 부터는 t1, t2는 쓸 수 없다.
with
t1 as (
    select * from tab05
    where sal >= 30),
t2 as (
	select * from tab05
    where sal < 30)
select * from t1, t2;