day31-DB: sql

1. 인라인 뷰: from 하위에 select 구문(select 구문으로부터 다시 결과를 조회해서 나오는 문장)

- 서브 쿼리를 from 절에 사용해 하나의 테이블 또는 뷰(가짜 테이블)처럼 사용할 수 있습니다.

-- 인라인뷰
select * from employees; -- 내가 필요한 select 구문을 먼저 만들고 , 결과를 확인한 다음에 from절 안에 삽입
select  * 
from (select * from employees);

-- order를 거치면서 순서(rownum)가 섞임
select rownum, first_name, job_id, salary --rownum: 조회된 순서
from employees -- order하기 전에는 rownum이 순서대로 되어 있음
order by salary; -- salary로 되어 있어서 섞임

-- 인라인뷰를 사용하면 사용 가능
select rownum, a.*
from(select first_name, job_id, salary from employees order by salary) a -- 그대로 다 가지고 와서 쓸거면 테이블 엘리어스 사용 
where rownum <= 10; -- 바깥에 있는 select 구문의 where 절

-- rownum이 중간카운트 불가
select rownum, a.*
from(select first_name, job_id, salary from employees order by salary) a 
where rownum > 10 and rownum <= 20; -- 10보다 크고 20보다 작거나 같음: 결과 안 나옴 / rownum은 첫번째 행부터 조회할 수 있음 

-- 3중 인라인 뷰
select * from employees
order by salary desc; --안쪽에서 부터 select절 만들어서 끼워넣기

select * -- 테이블이 들어갈 위치에는 언제든 서브쿼리절이 들어갈 수 있다(left join(테이블-여기))
from(select rownum as rn,
            a.*
     from (select * 
          from employees
          order by salary desc) a)
where rn > 10 and rn <= 20; -- 11번째부터 20번째까지 뽑아줄 수 있음

-- 인라인 뷰 응용
-- departments 테이블에서 manager_id가 null이 아닌 데이터를 employees에 조인

select * 
from employees E 
left outer join(select * 
                from departments 
                where manager_id is not null) D --() 자리에 밑에 테이블 들어감
on e.department_id = d.department_id;

select * from departments where manager_id is not null;

-- salary가 10000이상인 직원의 정보 전부, 부서명, 부서의 주소, job_title을 출력. salary 기준으로 내림 차순
-- 1st
select e.*, d.department_id, 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
order by salary desc;

select * from employees;
select * from departments;

-- 인라인 뷰에 들어갈 구문
-- 1. 선 작성
select * 
from employees e
left outer join departments d
on e.department_id = d.department_id
where salary >= 10000;

select * 
from (select * 
        from employees e
        left outer join departments d
        on e.department_id = d.department_id
        where salary >= 10000);
        
select a.*,
        (select street_address from locations l where l.location_id = a.location_id) as street_adress, -- 스칼라
        (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;
        
select * from locations;

 

2. 예제

-- 예제
-- 문제 1.
-- EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들을 데이터를 출력 하세요 (AVG(컬럼) 사용)
select * 
from employees
where salary >= (select avg(salary) from employees); -- where 절에 들어가는 select 구문

-- EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들의 수를 출력하세요
select count(*) as 사원수
from employees
where salary >= (select avg(salary) from employees);

-- EMPLOYEES 테이블에서 job_id가 IT_PFOG인 사원들의 평균급여보다 높은 사원들을 데이터를 출력하세요
select * 
from employees
where salary >= (select avg(salary) from employees where job_id = 'IT_PROG');

select avg(salary) from employees
where job_id = 'IT_PROG'; -- 5760 이상인 사원 출력

--문제 2.
---DEPARTMENTS테이블에서 manager_id가 100인 사람의 department_id와
--EMPLOYEES테이블에서 department_id가 일치하는 모든 사원의 정보를 검색하세요.
select * 
from employees 
where department_id = (select department_id 
                        from departments 
                        where manager_id = 100);

select * from departments;
select * from employees;
 
--문제 3.
---EMPLOYEES테이블에서 “Pat”의 manager_id보다 높은 manager_id를 갖는 모든 사원의 데이터를 출력하세요
select * from employees
where manager_id > (select manager_id from employees where first_name = 'Pat');

---EMPLOYEES테이블에서 “James”(2명)들의 manager_id와 갖는 모든 사원의 데이터를 출력하세요.
select * from employees
where manager_id In (select manager_id from employees where first_name = 'James');
-- 행이 여러개(120, 121)라 단항 연산자 사용 불가(in, any, all 중에 써야 함)
-- in(120, 121)과 같음

-- 3번까지가 where 절에 들어가는 select문

--문제 4. (오라클에서는 107행이라는 데이터가 있을 때 107행 데이터에서 order by first_name 41~50 중간만 출력)
---EMPLOYEES테이블 에서 first_name기준으로 내림차순 정렬하고, 41~50번째 데이터의 행 번호, 이름을 출력하세요
select rnum, first_name
from (
select first_name, rownum As rnum
from (select first_name 
        from employees
        order by first_name desc)
)
where rnum between 41 and 50;

-- 선생님 답안
select * from employees 
order by first_name desc;

-- from 절에 넣음
select rownum rn, a.*
from(select * 
        from employees 
        order by first_name desc) a; -- 정렬된 테이블에서 rownum을 붙인다.
-- 밑에 where절에 rownum > 41 썼을 때 에러 이유: rownum은 1번부터 나와야 해서 에러
 
-- 다시 from 절에 넣음
select * 
from(select rownum rn, a.*
from(select * 
        from employees 
        order by first_name desc) a)
where rn > 40 and rn <= 50;

--문제 5.
---EMPLOYEES테이블에서 hire_date기준으로 오름차순 정렬하고, 31~40번째 데이터의 행 번호, 사원id, 이름, 번호, 
--입사일을 출력하세요.
select rnum, employee_id, first_name, hire_date, phone_number
from (
select rownum as rnum, employee_id, first_name, hire_date, phone_number
from(select employee_id, first_name, hire_date, phone_number
        from employees
        order by hire_date)
)
where rnum 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. 7번하고 세트
--employees테이블 departments테이블을 left 조인하세요
--조건) 직원아이디, 이름(성, 이름), 부서아이디, 부서명 만 출력합니다.
--조건) 직원아이디 기준 오름차순 정렬

select * from employees;
select * from departments;

select e.employee_id, 
        concat(first_name, last_name) as 이름, 
        e.department_id, 
        d.department_name
from employees e
left outer join departments d on e.department_id = d.department_id
order by e.employee_id asc;

--문제 7.
--문제 6의 결과를 (스칼라 쿼리)로 동일하게 조회하세요
select e.employee_id, 
        concat(first_name, last_name) as 이름, 
        e.department_id,
        (select d.department_name from departments d where e.department_id = d.department_id) as department_name
        -- 스칼라: employee에 없는 department_name을 department 테이블에서 가져오기 위해서 사용
from employees e
order by e.employee_id asc;

--문제 8.
--departments테이블 locations테이블을 left 조인하세요
--조건) 부서아이디, 부서이름, 매니저아이디, 로케이션아이디, 스트릿_어드레스, 포스트 코드, 시티 만 출력합니다
--조건) 부서아이디 기준 오름차순 정렬
select d.department_id, 
        d.department_name, 
        d.manager_id, 
        l.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 d.department_id asc;

--문제 9.
--문제 8의 결과를 (스칼라 쿼리)로 동일하게 조회하세요
select d.department_id, 
        d.department_name, 
        d.manager_id, 
        d.location_id,
        (select street_address from locations l where d.location_id = l.location_id) as location_id,
        (select postal_code from locations l where d.location_id = l.location_id) as postal_code,
        (select city from locations l where d.location_id = l.location_id) as city
        -- 스칼라: department에 없는 street_address을 department 테이블에서 가져오기 위해서 사용
        -- 한번에 한 컬럼만 뽑아야 함.
        -- 같은 테이블을 3번 참조한 건 효율성이 좋지 않음 - 이럴 땐 조인으로 가져오는게 나음
from departments d
order by d.department_id asc;

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

select * from locations; --연결 확인
select * from countries;

--문제 11.
--문제 10의 결과를 (스칼라 쿼리)로 동일하게 조회하세요
select l.location_id, l.street_address, l.city,  l.country_id,
    (select c.country_name from countries c where l.country_id = c.country_id) as country_name
from locations l
order by country_name asc;

--문제
--조인과 서브쿼리
--문제 12. 
--employees테이블, departments테이블을 left조인 hire_date를 오름차순 기준으로 1-10번째 데이터만 출력합니다
--조건) rownum을 적용하여 번호, 직원아이디, 이름, 전화번호, 입사일, 부서아이디, 부서이름 을 출력합니다.
--조건) hire_date를 기준으로 오름차순 정렬 되어야 합니다. rownum이 틀어지면 안됩니다.
select rownum, a.* from(
select e.employee_id, e.first_name, e.phone_number, e.hire_date, e.department_id, d.department_name
from employees e
left outer join departments d
on e.department_id = d.department_id
order by hire_date) a
where rownum <= 10; --from절 뒤에는 as 안붙임

select * from employees;
select * from departments;

--문제 13. 
----EMPLOYEES 과 DEPARTMENTS 테이블에서 JOB_ID가 SA_MAN 사원의 정보의 LAST_NAME, JOB_ID, 
--DEPARTMENT_ID,DEPARTMENT_NAME을 출력하세요
select *
from (select e.last_name, e.job_id, e.department_id, d.department_name
      from employees e
left outer join departments d
on e.department_id = d.department_id
where e.job_id = 'SA_MAN');

-- 선생님 답안
select a.last_name, 
        a.job_id,
        a.department_id,
        d.department_name
from (select last_name, job_id, department_id 
                from employees
                where job_id = 'SA_MAN') a
left join departments d
on a.department_id = d.department_id;


--문제 14
----DEPARTMENT테이블에서 각 부서의 ID, NAME, MANAGER_ID와 부서에 속한 인원수를 출력하세요.
----인원수 기준 내림차순 정렬하세요.
----사람이 없는 부서는 출력하지 뽑지 않습니다
select *
from (select d.*, 
        (select count(*) from employees e where e.department_id = d.department_id group by department_id) as cnt
        from departments d
        ) 
where cnt is not null
order by cnt desc;

--left join
select d.*,
        cnt
from departments d
left join (select count(*) as cnt, department_id 
            from employees e 
            group by department_id) e
on e.department_id = d.department_id;

select * from departments;

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

select d.*, l.street_address, postal_code, nvl2(a.평균연봉, a.평균연봉, 0) from departments d
left outer join
(select department_id, trunc(avg(salary)) as 평균연봉 from employees
group by department_id) a -- select 뒤에 * 일 때는 이미 그룹화 된 상태라서 안나옴(행이 다름)
on d.department_id = a.department_id
left outer join locations l
on d.location_id = l.location_id;

-- 선생님 답안
select department_id, trunc(avg(salary)) as 부서별평균연봉
from employees
group by department_id;  -- 붙여줘야 하는 부서별 평균 연봉

select d.*, 
    NV1(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;

select * from employees;
select * from departments;
select * from locations;

--문제 16
---문제 15결과에 대해 DEPARTMENT_ID기준으로 내림차순 정렬해서 ROWNUM을 붙여 1-10데이터 까지만
--출력하세요
select * 
from (select rownum as rn, a.* 
from (select d.*, l.street_address, postal_code, nvl2(a.평균연봉, a.평균연봉, 0) from departments d
left outer join (select department_id, trunc(avg(salary)) as 평균연봉 from employees
group by department_id) a -- select 뒤에 * 일 때는 이미 그룹화 된 상태라서 안나옴(행이 다름)
on d.department_id = a.department_id
left outer join locations l
on d.location_id = l.location_id
order by d.department_id desc) a)
where rn <= 10;

 

- select, 함수, group by, having ,조인, 서브쿼리 개념 알아둘 것