[ex18] subquery

ex18_subquery

 

Main Query
    - 여태까지의 SQL
    - 일반적인 SQL
    - 하나의 문장안에 하나의 select(insert, update, delete)로 구성된 쿼리
    
    
    Sub Query, 서브 쿼리, 부속 질의
    - 하나의 문장안에(select, insert, update, delete)안에 또 다른 문장(select)이 들어있는 쿼리
    - 하나의 select문 안에 들어있는 또 다른 select문
    - 삽입 위치 > 거의 대다수의 절(select, from절, where절 :: group by절, having절, order by절)
    

-- tblCountry. 인구수가 가장 많은 나라의 이름? > 중국
select * from tblCountry;

update tblCountry set population = 120560 where name = '중국';

select max(population) from tblCountry; -- 인구수가 가장 많은 > 132726
select name from tblCountry where population = 120560; -- 중국


-- 장점
-- 1. 2개의 SQL > 1개의 SQL
-- 2. 변화에 강하다.(인구수 변동) > 개발자 편의성 향상
select name from tblCountry 
    where population = (select max(population) from tblCountry); --쿼리 자체를 숫자 하나로 봄

---

서브 쿼리 삽입 위치
1. 조건절
2. 컬럼리스트
3. from절


1. 조건절
: 비교 대상(비교값) 역할 > 값
: where절, having절, case문

a. 반환값이 1행 1열 > 단일값 반환 > 값 1개로 취급
b. 반환값이 N행 1열 > 다중값(같은 성질의 여러개의 데이터) 반환 > 값 N개로 취급
c. 반환값이 1행 N열 > 다중값(서로 다른 성질의 여러개의 데이터) 반환 > 
d. 반환값이 N행 N열 > 다중값 반환

 

-- a. 반환값이 1행 1열 > 단일값 반환 > 값 1개로 취급
select * from tblInsa
    where basicpay >= (select avg(basicpay) from tblInsa);


-- b. 반환값이 N행 1열 > 다중값(같은 성질의 여러개의 데이터) 반환 > 값 N개로 취급
-- 급여가 260만원 이상 받는 직원이 근무하는 부서 직원 명단을 가져오시오. > 기획부 + 총무부
-- ORA-01427: single-row subquery returns more than one row
select * from tblInsa
    --where 부서 = 기획부/총무부
    --where buseo = (select buseo from tblInsa where basicpay >= 2600000);
    
    --where buseo = '기획부' or buseo = '총무부'
    --where buseo in('기획부', '총무부') --같은 성질의 N개 데이터 > 열거형
    where buseo in (select buseo from tblInsa where basicpay >= 2600000);


-- '홍길동'과 같은 지역 + 같은 직위 > 소속 부서 직원 명단
select * from tblInsa where name = '홍길동'; -- 서울, 부장
select * from tblInsa where city = '서울' and jikwi = '부장'; -- 기획부, 영업부

select city from tblInsa where city = '홍길동'; --서울
select jikwi from tblInsa where name = '홍길동'; --부장

select buseo from tblInsa 
    where city = (select city from tblInsa where name = '홍길동') 
        and jikwi = (select jikwi from tblInsa where name = '홍길동')
            and name <> '홍길동';

select * from tblInsa
    where buseo in ('기획부', '영업부'); --23명

select * from tblInsa
    where buseo in (select buseo from tblInsa 
    where city = (select city from tblInsa where name = '홍길동') 
        and jikwi = (select jikwi from tblInsa where name = '홍길동')
            and name <> '홍길동');




-- c. 반환값이 1행 N열 > 다중값(서로 다른 성질의 여러개의 데이터) 반환 > N:N 비교
-- '홍길동'과 같은 지역 + 같은 직위 > 어떤 직원들?
select city from tblInsa where name = '홍길동'; --어느지역인지
select jikwi from tblInsa where name = '홍길동'; --어느직급인지

select * from tblInsa where city = '서울' and jikwi = '부장';

select * from tblInsa 
    where city = (select city from tblInsa where name = '홍길동') 
        and jikwi = (select jikwi from tblInsa where name = '홍길동');

-- 한꺼번에 나열 
select * from tblInsa
    where (city, jikwi) = (select city, jikwi from tblInsa where name = '홍길동'); -- 2:2비교


select * from tblAddressBook;   --기예주(성별, 나이, 직업, 키 같은사람)

select * from tblAddressBook
    where (gender, age, job, height) = (select gender, age, job, height 
                                                from tblAddressBook where name = '기예주');




-- d. 반환값이 N행 N열 > 다중값 반환
-- 급여가 260만 이상 받는 직원과 > 같은 부서 + 같은 지역에 있는 모든 직원?
select * from tblInsa where basicpay >= 2600000;

-- 서울 + 기획부 && 경남 + 총무부
select city, buseo from tblInsa where basicpay >= 2600000;

select * from tblInsa
    where (city, buseo) in (select city, buseo from tblInsa where basicpay >= 2600000);



--having절
select 
    buseo,
    avg(basicpay)
from tblInsa
    group by buseo
        having avg(basicpay) >= (select avg(basicpay) from tblInsa where buseo = '총무부');

 

---

2. 컬럼리스트
 : 단일 데이터(값) > 원자값 > 관계형 데이터베이스 > 표 > 속성(컬럼)의 값은 원자값이어야 한다.
 a. 컬럼명
 b. 상수
 c. 연산
 d. 함수

 컬럼리스트에서 서브쿼리를 사용하기
 - 서브쿼리의 결과값이 반드시 1행 1열이어야 한다. > 스칼라 쿼리
 - 정적쿼리 > 모든 행에 동일한 값을 반환 > 사용 빈도 적음
 - 상관 서브 쿼리 > ?? > 사용 빈도 높음

 

select
    name,
    (select sysdate from dual),
    sysdate
    from tblInsa;

select
    name,
    (select basicpay from tblInsa where name = '홍길동')
from tblInsa;


-- 
select 
    name, buseo, basicpay,
    avg(basicpay)
from tblInsa;

select
    name, buseo, basicpay,
    (select round(avg(basicpay)) from tblInsa) as "평균급여"
from tblInsa;

select avg(basicpay) from tblInsa;



-- 상관 서브 쿼리(*중요*)

select
    name, buseo, basicpay,
    (select round(avg(basicpay)) from tblInsa b where b.buseo = a.buseo) as "소속 부서 평균 급여"
from tblInsa a; --테이블 별칭 as 안붙이고


-- 식별자 정리
select * from tblInsa;

-- 현재 접속중인 계정은 생략 가능하다.
select * from hr.tblInsa; -- 계정명(스키마).테이블명 (FM)

select name, buseo, jikwi from hr.tblInsa;

select tblInsa.name, tblInsa.buseo, tblInsa.jikwi from hr.tblInsa;

select hr.tblInsa.name, hr.tblInsa.buseo, hr.tblInsa.jikwi from hr.tblInsa; -- Full


select *, sysdate from tblInsa;

-- 와일드카드(*)와 다른 컬럼을 동시에 가져오는 방법 > 테이블명
select tblInsa.*, sysdate from tblInsa;

-- 컬럼 별칭 > 유효한 이름을 만들기 위해ㅓㅅ
-- 테이블 별칭 > 최대한 줄여서 > 보통 알파벳 1글자로 적는다.

-- SQL의 별칭(Alis) > 별명(X), 개명(O) > 별칭 선언이후의 단계에서는 원래 곗ㅇ

select 
    i.*, sysdate  --2.
    from tblInsa i;     --1. 테이블 별칭



select
    name, buseo, basicpay,
    (select round(avg(basicpay)) from tblInsa where buseo = i.buseo) as "소속 부서 평균 급여"
from tblInsa i;




drop table tblMen;
drop table tblWomen;

select * from tblMen;
select * from tblWomen;

CREATE TABLE tblmen
(
   name varchar2(30) primary key,
   age number not null,
   height number null,
   weight number null,
   couple varchar2(30) null
);

CREATE TABLE tblwomen
(
   name varchar2(30) primary key,
   age number not null,
   height number null,
   weight number null,
   couple varchar2(30) null
);


INSERT INTO tblmen VALUES ('홍길동', 25, 180, 70, '장도연');
INSERT INTO tblmen VALUES ('아무개', 22, 175, NULL, '이세영');
INSERT INTO tblmen VALUES ('하하하', 27, NULL, 80, NULL);
INSERT INTO tblmen VALUES ('무명씨', 21, 177, 72, NULL);
INSERT INTO tblmen VALUES ('유재석', 29, NULL, NULL, '김숙');
INSERT INTO tblmen VALUES ('박명수', 30, 170, NULL, '김지민');
INSERT INTO tblmen VALUES ('정준하', 31, 183, NULL, '신보라');
INSERT INTO tblmen VALUES ('정형돈', 28, NULL, 92, NULL);
INSERT INTO tblmen VALUES ('양세형', 22, 166, 55, '김민경');
INSERT INTO tblmen VALUES ('조세호', 24, 165, 58, '오나미');

INSERT INTO tblwomen VALUES ('박나래', 23, 150, 55, NULL);
INSERT INTO tblwomen VALUES ('장도연', 28, 177, 65, '홍길동');
INSERT INTO tblwomen VALUES ('김지민', 30, 160, NULL, '박명수');
INSERT INTO tblwomen VALUES ('김숙', 34, 158, NULL, '유재석');
INSERT INTO tblwomen VALUES ('오나미', 27, NULL, NULL, '조세호');
INSERT INTO tblwomen VALUES ('김민경', 22, 169, 88, '양세형');
INSERT INTO tblwomen VALUES ('홍현희', 20, 158, 75, NULL);
INSERT INTO tblwomen VALUES ('신보라', 26, 170, 60, '정준하');
INSERT INTO tblwomen VALUES ('이세영', 28, 163, NULL, '아무개');
INSERT INTO tblwomen VALUES ('신봉선', 27, 162, NULL, NULL);


COMMIT;


-- tblMen <- (연인) -> tblWomen; 
select * from tblMen;
select * from tblWomen;

-- 남자 명단(이름, 나이) 출력 > 여자친구가 있으면 여자친구(이름,나이)를 같이 출력하시오.

select 
    name, age, couple,
    (select age from tblWomen where name = tblMen.couple)
from tblMen;

---

3. from절
 : 서브쿼리의 결과셋을 하나의 테이블이라고 생각하고, 또 다른 select를 실행~
 : 구문을 단순화하기 위해서 사용

select
    * 
from (select * from tblInsa); --1. 


select
    * --메인쿼리의 모든 컬럼(name, buseo, jikwi)
from (select name, buseo, jikwi from tblInsa);


select
    --name, ssn --from절에 ssn 컬럼 없음.
    --name
    이름 --컬럼 이름도 주의
from (select name as 이름, buseo, jikwi from tblInsa);


select
    name, len
from (select name, length(name) as len from tblInsa);



-- ORA-00918: column ambiguously defined > 동일한 컬럼명이 2개 이상 발견
select 
    *
from (select name, age, couple
        , (select age from tblWomen where name = tblMen.couple) as age2 from tblMen);





-- employees. 'Munich'에 위치한 부서에 소속된 직원 명단?
select * from employees; --department_id 소속 부서
select * from departments; --location_id 위치 정보
select * from locations;


select location_id from locations
    where city = 'Munich';

select department_id from departments
    where location_id = (select location_id from locations
                            where city = 'Munich');
--2번 중첩
--ORA-01427: single-row subquery returns more than one row
select * from employees
    where department_id in (select department_id from departments
                                where location_id = (select location_id from locations
                                                        where city = 'Seattle'));