[SQL] 서브쿼리(단일행, 다중행, 스칼라), 인라인뷰, rownum

728x90

서브쿼리

하나의 질의를 다른 질의 내부에 두는 방법으로 두 개의 질의를 조합하여 해결
nancy의 급여보다 많은 급여를 받는 사원은 누구?
1. nancy급여
2. 1보다 많은 급여를 받는 사원
다른 select 문장의 절에 내장된 select 문장
테이블 자체의 데이터에 의존하는 조건
테이블의 행을 검색할 필요가 있을 때 유용

서브쿼리는 ( )안에 있어야 함
비교 연산자의 오른쪽에 있어야 함
서브쿼리절을 먼저 해석
select, from, where, having, order by, insert문의 values, update문의 set, create table문의 as

단일 행 서브쿼리

내부 select 문장으로부터 하나의 행을 리턴
=, >, >=, <, <=, <>(같지 않다)

다중 행 서브쿼리

서브쿼리 결과가 2개 행 이상일 경우

IN연산자: 일치하는 데이터만 찾는 경우

select first_name, department_id, job_id
from employees
where department_id, in(select department_id
						from employees
                        where first_name = 'David');

실습

-- 서브쿼리
-- 서브쿼리 사용방법 ()안에 명시함, 서브쿼리절의 리턴행이 1줄 이하여야 합니다.
-- 서브쿼리 절에는 비교할 대상이 '하나' 반드시 들어가야 합니다.
-- (서브쿼리절을 먼저 해석하면 됩니다.)

-- nancy의 급여
select salary from employees where first_name = 'Nancy';

select *
from employees
where salary >= (select salary from employees where first_name = 'Nancy');

-employee_id가 104번인 사람과 job_id가 동일한 사람을 검색하는 문장
select job_id
from employees
where employee_id = 104;

select *
from employees
where job_id = ( select job_id
                          from employees
                            where employee_id = 104 );
------------------------------------------------------------------------------
-- 서브쿼리의 결과가 여러개라면, 단일행 연산자를 사용할 수 없음
-- 이런경우는 다중 서브쿼리를 이용하여 처리해야 함

select employee_id
from employees
where job_id = 'IT_PROG';

select *
from employees
where employee_id >= (select employee_id
                                    from employees
                                    where job_id = 'IT_PROG');


--------다중행 서브쿼리 in, any, all
select salary
from employees
where first_name =  'David';

-- in
select *
from employees
where salary in (select salary from employees where first_name =  'David');

-- 4800, 6800, 9500
-- any - 최소값보다 큰 or 최대값보다 작은
select *
from employees
where salary > any (select salary from employees where first_name = 'David');

-- all - 최대값보다 큰 or 최소값보다 작은
select *
from employees
where salary < all (select salary from employees where first_name = 'David');

-- first_name - steven의 급여보다 작은 사람
-- 어느 스티븐보다 작아도 되니까 24000보다 작으면 됨
select salary
from employees
where first_name = 'Steven'; -- 두 Steven 급여

select *
from employees
where salary < any (select salary from employees where first_name = 'Steven');

**스칼라 서브쿼리 *select 절**

select 절에 사용하는 서브쿼리
조인을 수행할 시 조인할 생의 수를 줄여 성능 향상
특정 테이블의 1컬럼만 가져오고 싶을 때!!
서브쿼리이기 때문에 1row만 가져옴

두 구문을 각각 실행 - F10 실행
다른 레벨이면 아래부터 ↑
같은 레벨이면 위부터 ↓

**★★★ 인라인 뷰 *from 절 ★★★**

from 절에 또 다른 select 구문이 나온 것
서브쿼리를 from절에 사용해 하나의 테이블 또는 뷰처럼 사용
응용: join테이블의 위치로 인라인 뷰 삽입가능 or 스칼라 쿼리와 혼합해서 사용가능

rownum

정의

select 한 데이터에 일련번호 붙이는 것
원하는 만큼의 행만 가져오고 싶을 때, 행의 개수를 제한하는 용도
ex) 전체 행에서 TOP3, 게시판 페이징 등

select rownum
from employees
where rownum between 1 and 10;
-- 혹은 where rownum >= 1 and rownum <= 10;

특정 rownum

rownum은 반환되는 쿼리 결과의 임시 행번호이기 때문에 반드시 1부터 나와야 함
1번 이외의 숫자 이상을 출력, 페이징이나 특정 행을 추출할 때,
rownum이 포함된 select문 밖에 한번 더 select로 감싸줘야 함

select rownum as rn, first_name
from employees;

↓

select *
from (select rownum as rn, first_name
	  from employees)
where rn >= 5 and rn <= 10;

rownum 정렬

rownum은 order by 이전에 적용
둘 다 사용하면 순서 뒤죽박죽 됨
rownum: 쿼리 결과 데이터에 숫자를 붙임으로 결과 집합을 결정하는데 관여
order by: 결과 집합을 결정하는 요소는 아님. 순서만 바꿈
보통 테이블 정렬 → 바깥을 select로 한번 더 감싸서 rownum으로 일련번호 붙임
출처: https://turing0809.tistory.com/48

실습

-- 스칼라 서브쿼리 (select구문에 서브쿼리가 오는 것, left outer join이랑 같은 결과)
-- join
select first_name, department_name
from employees e
left outer join departments d on e.department_id = d.department_id
order by first_name asc;
-- scalar
select first_name,
            (select department_name from departments d where d.department_id = e.department_id) as department_name
from employees e
order by first_name asc;

select first_name,
            (select department_name from departments d where d.department_id = e.department_id) as department_name,
            (select location_id from departments d where d.department_id = e.department_id) as location_id -- 두개 가져오고 싶으면 두번 써줘야 하는데 남발은 금물
from employees e
order by first_name asc;


-- 각 부서의 매니저장의 이름
-- left join
select d.*,
           e.first_name
from departments d
left outer join employees e
on d.manager_id = e.employee_id
order by d.manager_id;
-- scalar
select d.*,
           ( select e.first_name from employees e where e.employee_id = d.manager_id ) as first_name
           -- 특정 컬럼 하나 first_name을 스칼라쿼리로 가져옴
from departments d
order by d.manager_id;

-----------------------------------------------------------------------------------------------------------------------------
-- left join
select e1.*,
          d.department_name as 직원의부서,
          e2.first_name as 매니저이름
from employees e1
left outer join employees e2 on e1.manager_id = e2.employee_id
left outer join departments d on e1.department_id = d.department_id;
-- scalar
select e1.*,
        (select d.department_name from departments d  where e1.department_id = d.department_id) as 직원의부서,
        (select e2.first_name from employees e2 where e1.manager_id = e2.employee_id) as 매니저이름
from employees e1;

-----------------------------------------------------------------------------------------------------------------------------
-- 부서별 사원수 (join or scalar)
select count(*) from employees group by department_id;

select d.*,
            (select count(*) from employees e where d.department_id = e.department_id group by department_id) as 사원수
from departments d;

-----------------------------------------------------------------------------------------------------------------------------
-- 인라인뷰
select *
from (select *
        from employees);
    
    
-- order를 거치면서 순서가 섞임
select rownum, first_name, job_id, salary
from employees
order by salary;

-- 
select rownum,
            a.*
from ( select first_name, job_id, salary
            from employees
            order by salary) a
where rownum <= 10;

-- rownum 이 중간 카운트 불가
select rownum,
            a.*
from ( select first_name, job_id, salary
            from employees
            order by salary) a
where rownum > 10 and rownum <= 20;


-- 3중 인라인 뷰
select * --  필요한거 뽑기
from (select rownum as rn,
                    a.*
        from ( select *
                    from employees
                    order by salary desc ) a) -- 안에서 정렬, () 테이블이 들어갈 위치에는 언제든지 서브쿼리 가능
where rn > 10 and rn <= 20;

인라인 뷰 응용

(join테이블의 위치로 인라인뷰 삽입가능 or 스칼라 쿼리와 혼합해서 사용가능)

-- 인라인 뷰 응용 (join테이블의 위치로 인라인뷰 삽입가능 or 스칼라 쿼리와 혼합해서 사용가능)
-- departments 테이블에서 manager_id가 null이 아닌 데이터를, employees에 조인

 -- 1. manager_id가 null이 아닌 데이터
select *
from departments
where manager_id is not null;

-- 2. employees E와 join뒤의 () D가 연결
select *
from employees E
left outer join (select * from departments where manager_id is not null)  D;

-- 3. E테이블의 department_id와 D테이블의 department_id 연결
select *
from employees E
left outer join (select * from departments where manager_id is not null)  D
on E.department_id = D.department_id;

-- salary가 10000이상인 직원의 정보 전부, 부서명, 부서의 주소, job_title을 출력, salary 기준으로 내림차순
select e.*, -- 그냥 join만 사용했을 경우
            d.department_name,
            l. street_address,
            j.job_title
from employees e
left outer join departments d on e.department_id = d.department_id
left outer join locations l on d.location_id = l.location_id
left outer join jobs j on e.job_id = j.job_id
where salary >= 10000;

-- 인라인뷰에 들어갈 구문 2번까지
select a.*
from ( select *
        from employees e
        left outer join departments d
        on e.department_id = d.department_id
        where salary >= 10000 ) A;
        
select a.*, 
           (select street_address from locations l where l.location_id = A.location_id) as street_address,
           (select job_title from jobs j where j.job_id = a.job_id ) as job_title
from ( select *
        from employees e
        left outer join departments d
        on e.department_id = d.department_id
        where salary >= 10000 ) A;

문제

문제 1 ~ 11

문제 1.

-- EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들을 데이터를 출력 하세요 AVG( 컬럼 ) 사용
-- EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들을 수를 출력하세요
-- EMPLOYEES 테이블에서 job_id 가 IT_PFOG 인 사원들의 평균급여보다 높은 사원들을 데이터를 출력하세요

-- 문제1
-- 급여 평균: 6461원
select avg(salary) from employees;
-- 급여가 평균보다 높은 사원
select * from employees where salary >= (select avg(salary) from employees);

-- select에 사원수만 출력
select count(*) as 사원수 from employees where salary > (select avg(salary) from employees);

-- job_id가 it_prog인 사원
select * from employees where job_id = 'IT_PROG';
-- it_prog 사원의 평균급여: 5760원, from employees where job_id = 'IT PROG'로 조건만 걸어줘도 됨
select avg(salary) from (select * from employees where job_id = 'IT_PROG');
-- 평균급여보다 높은 급여를 받는 사원
select * from employees where salary >= (select avg(salary) from employees where job_id = 'IT_PROG');

문제 2.

-- DEPARTMENTS 테이블에서 manager_id 가 100 인 사람의 department_id 와 EMPLOYEES 테이블에서 department_id 가 일치하는 모든 사원의 정보 를 검색하세요

-- 문제2
-- manager_id가 100인 사람
select * from departments where manager_id = 100;

-- employees, departments 테이블 join으로 연결, 두 테이블의 컬럼이 다 나옴
select *
from employees e
inner join (select * from departments where manager_id = 100) d
on e.department_id = d.department_id;

-- 선생님 풀이
-- employees 테이블에서 department_id 조건이 100인 사람을 구함, employees 테이블의 컬럼만 나옴
select *
from employees 
where department_id = (select department_id from departments where manager_id = 100);

문제 3.
-- EMPLOYEES 테이블에서 “ 의 manager_id 보다 높은 manager_id 를 갖는 모든 사원의 데이터를 출력하세요
-- EMPLOYEES 테이블에서 “James”(2 명 들의 manager_id 와 갖는 모든 사원의 데이터를 출력하세요

-- 문제3
-- 이름이 Pat인 사람을 구하고 inner join으로 연결, 두 테이블의 컬럼 다 나옴
select *
from employees e 
inner join(select * from employees where first_name = 'Pat') a
on e.manager_id > a.manager_id;

-- 선생님 풀이
-- manager_id가 조건보다 높은 사원들의 데이터 추출, employees 테이블의 컬럼만 나옴
select *
from employees
where manager_id > (select manager_id from employees where first_name = 'Pat');

-- 이름이 James인 사람을 구하고 inner join으로 연결, 두 테이블의 컬럼 다 나옴
select *
from employees e 
inner join (select * from employees a where first_name = 'James') a
on e.manager_id = a.manager_id;

-- 선생님 풀이
-- manager_id가 조건과 같은 사원들의 데이터 추출, 조건이 숫자가 아니므로 IN연산자 사용
-- employees 테이블의 컬럼만 나옴
select *
from employees
where manager_id in (select manager_id from employees where first_name = 'James');

문제 4.
-- EMPLOYEES 테이블 에서 first_name 기준으로 내림차순 정렬하고 , 41~50 번째 데이터의 행 번호 , 이름을 출력하세요

-- 문제4
-- first_name 기준 내림차순
select *
from employees
order by first_name desc;

-- 3중 쿼리 이용, rownum은 첫번째 행부터 조회 가능
select rnum, first_name
from (select rownum as rnum, first_name
        from (select *
                from employees
                order by first_name desc)
        )
where rnum between 41 and 50;

-- 선생님 답
select *
from(select rownum as rnum,
                             a.* -- 인라인뷰는 새로운 컬럼으로 만든 것을 다시 바깥에서 필요할때 쓰기 위해서
        from (select *
                from employees
                order by first_name desc) a
) 
where rnum between 41 and 50;

문제 5.
-- EMPLOYEES 테이블에서 hire_date 기준으로 오름차순 정렬하고 , 31~40 번째 데이터의 행 번호 , 사원 id, 이름 , 번호 , 입사일을 출력하세요

-- 문제 5
-- hire_date기준 오름차순
select * from employees order by hire_date asc;

 -- 행번호 추가해서 정렬
select rownum as 행번호,
            employee_id as 사원id,
            first_name as 이름,
            phone_number as 번호,
            hire_date as 입사일
from (select * from employees order by hire_date asc);

-- 특정 행 꺼내기 위해 select문으로 감싸주기
select *
from (select rownum as 행번호,
            employee_id as 사원id,
            first_name as 이름,
            phone_number as 번호,
            hire_date as 입사일
            from (select *
                    from employees
                    order by hire_date asc))
where 행번호 between 31 and 40;

-- 선생님 풀이
select *
from(select rownum rn, 
            employee_id,  
            first_name, 
            phone_number, 
            hire_date
    from(select * 
         from employees 
         order by hire_date asc)
    )     
where rn > 30 and rn <= 40;

문제 6.
-- employees 테이블 departments 테이블을 left 조인하세요
조건) 직원아이디 , 이름 성 , 이름 ), 부서아이디 , 부서명 만 출력합니다
조건) 직원아이디 기준 오름차순 정렬

-- 문제6
-- 두 테이블 조인
select *
from employees e
left outer join departments d
on e.department_id = d.department_id;

select e.employee_id,
            e.first_name,
            e.last_name,
            e.department_id,
            d.department_name
from employees e
left outer join departments d on e.department_id = d.department_id
order by employee_id asc;

문제 7.
-- 문제 6 의 결과를 스칼라 쿼리 로 동일하게 조회하세요

-- 문제7 (문제6을 스칼라 쿼리로 작성)
-- departments 테이블 사용하는 컬럼 따로 꺼내서 select에 넣어주기
select e.employee_id,
            e.first_name,
            e.last_name,
            e.department_id,
            (select department_name from departments d where e.department_id = d.department_id)
from employees e
order by department_id asc;

문제 8.
-- departments 테이블 locations 테이블을 left 조인하세요
조건) 부서아이디 , 부서이름 , 매니저아이디 , 로케이션아이디 , 스트릿 어드레스 , 포스트 코드 , 시티 만 출력합니다
조건) 부서아이디 기준 오름차순 정렬

-- 문제8
--  두 테이블 조인
select *
from departments d
left outer join locations l
on d.location_id = l.location_id;

-- rownum 사용안해서 굳이 select로 감싸주지 않아도 됨
select *
from (select d.department_id,
                    d.department_name,
                    d.manager_id,
                    d.location_id,
                    l.street_address,
                    l.postal_code,
                    l.city
            from departments d
            left outer join locations l
            on d.location_id = l.location_id)
order by department_id asc;

--선생님 답
select 
    d.department_id,
    d.department_name,
    d.manager_id,
    d.location_id,
    l.street_address,
    l.postal_code,
    l.city
from departments d left outer join locations l on d.location_id = l.location_id
order by department_id asc;

문제 9.
-- 문제 8 의 결과를 스칼라 쿼리 로 동일하게 조회하세요

-- 문제9 (문제8을 스칼라 쿼리로 작성)
-- 한번에 한 컬럼씩 뽑아야 함 (3컬럼을 뽑아야하니까 효율적이지못함)
select 
    department_id,
    department_name,
    manager_id,
    location_id,
    (select street_address from locations l where l.location_id = d.location_id) as street_address,
    (select postal_code from locations l where l.location_id = d.location_id) as postal_code,
    (select city from locations l where l.location_id = d.location_id) as city
from departments d
order by department_id asc;

문제 10.
-- locations 테이블 countries 테이블을 left 조인하세요
조건) 로케이션아이디 , 주소 , 시티 , country_id , country_name 만 출력합니다
조건) country_name 기준 오름차순 정렬

-- 문제10
-- 두 테이블 조인
select *
from locations l
left outer join countries c
on l.country_id = c.country_id;

-- rownum 사용안해서 굳이 select로 감싸주지 않아도 됨
select *
from (select l.location_id,
                    l.street_address,
                    l.city,
                    l.country_id,
                    c.country_name
            from locations l
            left outer join countries c
            on l.country_id = c.country_id)
order by country_name asc;

--선생님 답
select
    l.location_id,
    l.street_address,
    l.city,
    l.country_id,
    c.country_name
from locations l 
left outer join countries c on l.country_id = c.country_id
order by c.country_name asc;

문제 11.
-- 문제10 의 결과를 스칼라 쿼리 로 동일하게 조회하세요

-- 문제11 (문제10를 스칼라 쿼리로 작성)
-- countries 테이블 사용하는 컬럼 따로 꺼내서 select에 넣어주기
select
    location_id,
    street_address,
    city,
    country_id,
    (select country_name from countries c where c.country_id = l.country_id) as country_name
from locations l
order by country_name asc;

문제 12 ~ 15

문제 12.
-- employees 테이블 , departments 테이블을 left 조인 hire_date 를 오름차순 기준으로 1 10 번째 데이터만 출력합니다
조건) rownum 을 적용하여 번호 , 직원아이디 , 이름 , 전화번호 , 입사일 , 부서아이디 , 부서이름 을 출력합니다
조건) hire_date 를 기준으로 오름차순 정렬 되어야 합니다 . rownum 이 틀어지면 안됩니다

select  employee_id as 직원아이디,
        first_name as 이름,
        phone_number as 전화번호,
        hire_date as 입사일,
        d.department_id as 부서아이디,
        d.department_name as 부서이름
from employees e
inner join departments d  -- emplpoyees 테이블과 department 테이블을 inner join
on e.department_id = d.department_id -- pk, fk인 depatment_id로 연결
order by hire_date asc; -- hire_date로 오름차순 *rownum틀어지면 안되니까 다음 select ~ from으로 넘김


select rownum rn, -- rownum 생성하고 앞에 적었던 부분 별칭을 a로 설정
		a.*
    from(select employee_id as 직원아이디,
                first_name as 이름,
                phone_number as 전화번호,
                hire_date as 입사일,
                d.department_id as 부서아이디,
                d.department_name as 부서이름
        from employees e
        inner join departments d
        on e.department_id = d.department_id
        order by hire_date asc) a;
        
        
select *
from (select rownum rn,
		a.*
    from(select employee_id as 직원아이디,
                first_name as 이름,
                phone_number as 전화번호,
                hire_date as 입사일,
                d.department_id as 부서아이디,
                d.department_name as 부서이름
        from employees e
        inner join departments d
        on e.department_id = d.department_id
        order by hire_date asc) a
)
where rn > 0 and rn <= 10; -- rownum이 1~10까지의 범위 조건

문제 13.
-- EMPLOYEES 과 DEPARTMENTS 테이블에서 JOB_ID 가 SA_MAN 사원의 정보의 LAST_NAME, JOB_ID,
DEPARTMENT_ID,DEPARTMENT_NAME 을 출력하세요

-- 선생님 풀이와 내 풀이가 결과는 같으나 체계적으로 사용한 것과 그렇지 않은 것의 차이가 있음

수정하며 유지, 보수할 때를 고려하기

-- 내 풀이
-- 모든 조건을 한번에 적음 (order, rownum 없어서)
select * -- select * from 없어도 결과는 같음
from (select last_name, job_id, d.department_id, department_name
            from employees e
            left outer join departments d
            on e.department_id = d.department_id
            where job_id = 'SA_MAN');
            
            
-- 선생님 풀이
select last_name, job_id, department_id
        from employees
        where job_id = 'SA_MAN'; -- employees 테이블에서 sa_man조건에 맞는 데이터를 뽑음


select *
from (select last_name, job_id, department_id
        from employees
        where job_id = 'SA_MAN') a -- select~from 절에 썼던 내용을 별칭 a로 담기
       

select a.last_name,
       a.job_id,
       a.department_id, -- 3컬럼은 a에서 나타냈기 때문에 a.으로 적고
       d.department_name -- department_name 컬럼은 d에만 있기 때문에 d.으로 구분해줌 
from (select last_name, job_id, department_id
        from employees
        where job_id = 'SA_MAN') a  -- select~from 절에 앞에서 썼던 내용을 별칭 a로 담기
left join departments d
on a.department_id = d.department_id; -- left join으로 departments 테이블과 연결

문제 14
-- DEPARTMENT 테이블에서 각 부서의 ID, NAME, MANAGER_ID 와 부서에 속한 인원수(부서별 인원수)를 출력하세요
-- 인원수 기준 내림차순 정렬하세요
-- 사람이 없는 부서는 출력하지 뽑지 않습니다

-- 내 풀이 (오답)
-- 진짜 인원수별로 내림차순만 나타냄;;
-- is not null 사용하지 않음
select count(*) as 인원수 -- count로 인원수 표시
from departments d
left join employees e -- departments 테이블과 employees 테이블을 left join
on d.department_id = e.department_id -- pk, fk인 department_id로 연결
group by d.department_id -- 부서별로 묶어야해서 department_id(두 개니까 d.또는e.으로 어떤 테이블인지 표시 필수)로 group by
order by 인원수 desc; -- 마지막으로 인원수별로 내림차순


-- 스칼라 쿼리 이용
select d. department_id,
       d. department_name,
       d. manager_id, -- 3컬럼은 departments 테이블에 있으니까 d.붙여줌
       (select count(*)
        from employees e
        where e.department_id = d.department_id
        group by d.department_id) as 인원수
from departments d
where manager_id is not null
order by 인원수 desc;


-- 선생님 풀이
select count(*) -- 전체 인원수 count
from employees e -- employees 테이블에서
where e.department_id = d.department_id
group by d.depamrtment_id; -- 부서별(group by) 인원수


select d.*,
		(select count(*)
         from employees e
         where e.department_id = d.department_id
         group by d.depamrtment_id) as cnt -- 앞에서 나왔던 부분 출력
from departments d; -- departments 테이블에서


select *
from (select d.*,
            (select count(*)
             from employees e
             where e.department_id = d.department_id
             group by d.depamrtment_id) as cnt -- 앞에서 나왔던 부분 출력
      from departments d
) -- 앞에서 썼던 부분 select ~ from 절에 넣기
where cnt is not null -- 사람이 없는 부서 출력x
order cnt desc; -- 인원수 기준 내림차순

문제 15
-- 부서에 대한 정보 전부와 , 주소 , 우편번호 , 부서별 평균 연봉을 구해서 출력하세요
-- 부서별 평균이 없으면 0 으로 출력하세요

-- 내 풀이
select round(avg(salary*12)) as 평균연봉, department_id
from employees
group by department_id; -- 1. 각 부서의 평균연봉(부서별 평균연봉이므로 group by)

select *
from departments d
left outer join (select round(avg(salary*12)) as 평균연봉, department_id
                         from employees
                        group by department_id) a
on d.department_id = a.department_id; -- 2. departments 테이블에 left join으로 평균연봉 붙이기

select d.*, nvl(a.result, 0) as 평균연봉, l.street_address, l.postal_code -- 5. 필요한 컬럼 적기
from departments d
left outer join (select round(avg(salary*12)) as result, department_id
                         from employees
                        group by department_id) a
on d.department_id = a.department_id -- 3. left join으로 필요한 부분만 꺼내기
left  join locations l
on l.location_id = d.location_id; -- 4. 뒤에 left join으로 locations 붙이기
-- left join 연속사용 가능!!!!



-- 선생님 풀이
select d.*,
       nvl(a.result, 0) as avg, -- nvl 사용하여 데이터가 없으면 0 출력
       l.street_address,
       l.postal_code
from departments d
left join (select department_id,  -- 부서별 평균 연봉 테이블을 left join
                   trunc(avg(salary)) as result
            from employees
            group by department_id) a
on d.department_id = a.department_id
left join locations l -- locations 테이블을 left join
on l.location_id = d.location_id
order by d.department_id desc;

문제 16
-- 문제 15 결과에 대해 DEPARTMENT_ID 기준으로 내림차순 정렬해서 ROWNUM 을 붙여 1 10 데이터 까지만
출력하세요

-- 선생님 풀이
select rownum rn, -- 앞서 나타낸 부분을 select ~ from 절에 넣으면서 rownum컬럼 생성
               x.*
        from (select d.*,
                     nvl(a.result, 0) as avg,
                     l.street_address,
                     l.postal_code
                from departments d
                left join (select department_id, 
                                  trunc(avg(salary)) as result
                            from employees
                            group by department_id) a
                on d.department_id = a.department_id
                left join locations l
                on l.location_id = d.location_id
                order by d.department_id desc
              ) x; -- 앞의 부분을 별칭 x로 만들기


select *
from (select rownum rn,
               x.*
        from (select d.*,
                     nvl(a.result, 0) as avg,
                     l.street_address,
                     l.postal_code
                from departments d
                left join (select department_id, 
                                  trunc(avg(salary)) as result
                            from employees
                            group by department_id) a
                on d.department_id = a.department_id
                left join locations l
                on l.location_id = d.location_id
                order by d.department_id desc
              ) x
      )
where rn > 0 and rn <= 10; -- where 조건 사용하여 1~10데이터만 출력

오늘 하루

기억에 남는 부분

- rownum은 배열을 한 후 사용하며, 1부터 진행이 필수기 때문에 특정 부분을 사용하려면 select문으로 한번 더 감싸줘야 함

- 스칼라 쿼리를 사용할 때 다른 테이블에 있는 컬럼을 select옆에 (select ~ from)을 사용하여 적음

어려운 부분

- 작은 단위 부분을 어디서부터 진행할지 못 찾음

- concatt 쓸 생각 안함..

- 문제 12 ~ 16 생각할 부분!!!!!!

문제 해결 부분

- inner join을 사용하여 문제를 풀었음
선생님 풀이보다는 복잡하지만 결과는 같았음

- 스칼라 쿼리 사용법을 잘 몰랐지만 결과는 같게 나왔음 (이제 어떻게 사용하는 지 알게 됨)

728x90

'프로그래밍 언어 > SQL-Oracle' 카테고리의 다른 글

[SQL] DDL 데이터 정의어, 테이블 CRUD (0)	2022.11.15
[SQL] DML 데이터조작어(insert, update, delete, merge, ctas), TCL(트랜잭션) (0)	2022.11.15
[SQL] 그룹함수 count(~별!), Join (inner,outer,cross,self) (0)	2022.11.10
[SQL] SQL 함수 (문자, 숫자, 날짜, 형변환, 집합, 분석) (0)	2022.11.09
[SQL] SQL (select, 조건절, 연산자) (0)	2022.11.08

서브쿼리

단일 행 서브쿼리

다중 행 서브쿼리

실습

스칼라 서브쿼리 *select 절

★★★ 인라인 뷰 *from 절 ★★★