day30-DB: sql

1. join

--테이블생성
CREATE TABLE INFO 
(
  ID NUMBER NOT NULL 
, TITLE VARCHAR2(100) 
, CONTENT VARCHAR2(100) 
, REGDATE DATE DEFAULT sysdate NOT NULL 
, CONSTRAINT INFO_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);
   
CREATE TABLE auth 
(
  AUTH_ID NUMBER NOT NULL 
, NAME VARCHAR2(30) 
, JOB VARCHAR2(30) 
, CONSTRAINT TABLE1_PK PRIMARY KEY 
  (
    AUTH_ID 
  )
  ENABLE 
);            
--시퀀스 생성            
CREATE SEQUENCE SEQ_INFO;            
CREATE SEQUENCE SEQ_AUTH;
--데이터 삽입            
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'java', 'java is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'jsp', 'jsp is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'spring', 'spring is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'oracle', 'oracle is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'mysql', 'mysql is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'c', 'c is');             

INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '박인욱', 'developer');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '홍길자', 'DBA');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '이순신', 'designer');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '고길동', 'scientist');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '박인욱', 'teacher');

-----------------------------------------------------------------------------------
select * from info; -- 6행
select * from auth; -- 5행

-- inner join, null값 안 나옴(쓸 수 없는 것은 안나옴) (중요)
select * from info inner join auth on info.auth_id = auth.auth_id;

-- left join, null값 출력(중요)
select * from info left outer join auth on info.auth_id = auth.auth_id;

-- right join, 5번부터 나오는 이유: 조회된 순서가 다를 수 있음
select * from info right outer join auth on info.auth_id = auth.auth_id;

-- full join, 둘 다 유실 없이 출력
select * from info full outer join auth on info.auth_id = auth.auth_id;

-- cross join, 잘못된 형태의 조인(6 * 5 = 30행 출력)
select * from info cross join auth;

-- auth_id는 양쪽 테이블에 존재하기 때문에 테이블.컬럼명으로 지칭해야 합니다.
select id, title, info.auth_id, name, job
from info inner join auth on info.auth_id = auth.auth_id;

-- 테이블 엘리어스를 이용한 조인(여기)
select *
from info i inner join auth a on i.auth_id = a.auth_id;

-- 조건
select *
from info i inner join auth a on i.auth_id = a.auth_id
where i.auth_id = 1;

-- using(키)를 통한 조인
select * 
from info i inner join auth a using(auth_id);

-- 여러 테이블 조인
select * from employees; -- department_id
select * from departments; -- department_id = pk
select * from locations; -- location_id = pk

select * 
from employees e
left outer join departments d on e.department_id = d.department_id
left outer join locations l on d.department_id = l.location_id;

-- self join(하나의 테이블로 조인)
select NVL(e2.first_name, '없음') as 상사, 
e1.first_name as 직원
from employees e1 
left outer join employees e2 on e1.manager_id = e2.employee_id
order by e1.employee_id;

-- 예제
-- 문제 1. EMPLOYEES 테이블과, DEPARTMENTS 테이블은 DEPARTMENT_ID로 연결되어 있습니다.
-- EMPLOYEES, DEPARTMENTS 테이블을 엘리어스를 이용해서
-- 각각 INNER , LEFT OUTER, RIGHT OUTER, FULL OUTER 조인 하세요. (달라지는 행의 개수 확인)
select * 
from employees e 
inner join departments d on e.department_id = d.department_id;

select *
from employees e 
left outer join departments d on e.department_id = d.department_id;

select * 
from employees e 
right outer join departments d on e.department_id = d.department_id;

select * 
from employees e 
full outer join departments d on e.department_id = d.department_id; -- 누락 없이 다 나옴

-- 문제 2.
-- EMPLOYEES, DEPARTMENTS 테이블을 INNER JOIN하세요
-- 조건)employee_id가 200인 사람의 이름, department_id를 출력하세요
-- 조건)이름 컬럼은 first_name과 last_name을 합쳐서 출력합니다
select e.first_name || '' || e.last_name, 
       d.department_name --e의 last_name
from employees e 
inner join departments d on e.department_id = d.department_id
where employee_id = 200;

-- 문제 3.
-- EMPLOYEES, JOBS테이블을 INNER JOIN하세요
-- 조건) 모든 사원의 이름과 직무아이디, 직무 타이틀을 출력하고, 이름 기준으로 오름차순 정렬
-- HINT) 어떤 컬럼으로 서로 연결되 있는지 확인
select e.first_name || '' || e.last_name as 이름, e.department_id, j.job_id
from employees e inner join jobs j on e.job_id = j.job_id
order by 이름;

-- 선생님 답안
select first_name, e.job_id, j.job_title
from employees e
inner join jobs j on j.job_id=e.job_id
order by first_name asc;

select * from employees;
select * from jobs;

--문제 4.
--JOBS테이블과 JOB_HISTORY테이블을 LEFT_OUTER JOIN 하세요.
select * 
from jobs j left outer join job_history h on j.job_id = h.job_id;

-- 선생님 답안
select * from jobs j left outer join job_history jh on j.job_id = jh.job_id;

select * from jobs;
select * from job_history;

-- 문제 5.
--Steven King의 부서명을 출력하세요.
select first_name || ' ' || last_name, job_id
from employees
where first_name = 'Steven' and last_name = 'King';

-- 선생님 답안
select e.last_name, 
e.first_name, 
e.department_id, 
d.department_name
from employees e
left outer join departments d
on e.department_id = d.department_id
where e.first_name = 'Steven' and e.last_name = 'King';

select * from employees;

-- 문제 6.
--EMPLOYEES 테이블과 DEPARTMENTS 테이블을 Cartesian Product(Cross join)처리하세요
select *
from employees cross join departments d;

select * from employees;
select * from departments;

-- 선생님 답안
select e.last_name,
e.first_name,
e.department_id,
d.department_name
from employees e
cross join departments d;

-- 문제 7.
-- EMPLOYEES 테이블과 DEPARTMENTS 테이블의 부서번호를 조인하고 SA_MAN 사원만의 사원번호, 이름, 
-- 급여, 부서명, 근무지를 출력하세요. (Alias를 사용)
select * from employees;

select job_id, employee_id, first_name || '' || last_name, salary, d.department_id  
from employees e inner join departments d on e.department_id = d.department_id
where job_id = 'SA_MAN';

-- 선생님 답안
select e.employee_id, e.last_name, e.first_name, e.salary, d.department_name, d.location_id, 
       l.street_address
from employees e
left join departments d on e.department_id = d.department_id
left join locations l on d.location_id = l.location_id
where e.job_id = 'SA_MAN';

-- 문제 8.
-- employees, jobs 테이블을 조인 지정하고 job_title이 'Stock Manager', 'Stock Clerk'인 직원 정보만
-- 출력하세요.
select *
from employees e inner join jobs j on e.job_id = j.job_id
where job_title = 'Stock Manager' or job_title = 'Stock Clerk';

select * from employees;
select * from jobs;

-- 선생님 답안
select employee_id, first_name, last_name, job_title
from employees e
left join jobs j
on e.job_id = j.job_id
where job_title in ('Stock Manager', 'Stock clerk');


-- 문제 9.
-- departments 테이블에서 직원이 없는 부서를 찾아 출력하세요. LEFT OUTER JOIN 사용
select * 
from departments d left outer join employees e on d.department_id = e.department_id
where e.employee_id is null;

-- 문제 10. 
-- join을 이용해서 사원의 이름과 그 사원의 매니저 이름을 출력하세요
-- 힌트) EMPLOYEES 테이블과 EMPLOYEES 테이블을 조인하세요.
select NVL(e2.first_name, '없음') as 매니저, e1.first_name as 사원
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id
order by e1.employee_id;

-- 선생님 답안
select e1.first_name as 직원, e2.first_name as MANAGER_NAME
from employees e1
join employees e2 on e1.manager_id = e2.employee_id;


-- 문제 11. 
-- EMPLOYEES 테이블에서 left join하여 관리자(매니저)와, 매니저의 이름, 매니저의 급여 까지 출력하세요
--매니저 아이디가 없는 사람은 배제하고 급여는 역순으로 출력하세요.
select e1.first_name, e2.manager_id, e2.first_name, e2.salary
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id
where e2.manager_id is not null
order by salary desc;

-- 선생님 답안
select a.employee_id, a.last_name, a.manager_id, a.job_id, a.salary,
b.employee_id, b.first_name, b.job_id, b.salary
from employees a
left join employees b
on a.manager_id = b.employee_id
where a.manager_id is not null
order by a.salary desc;


-- 문제 12.
-- self join 2번, case when then
select NVL2(e3.first_name, e3.first_name || ' > ', '') || NVL2(e2.first_name, e2.first_name || ' > ', '' ) || e1.first_name
from employees e1 
left outer join employees e2 on e1.manager_id = e2.employee_id
left outer join employees e3 on e2.manager_id = e3.employee_id
order by e3.employee_id desc;

select first_name, manager_id
from employees;

-- 오라클 조인(오라클만 사용하는 문법)
-- inner
select * from employees e, departments d -- 조인 테이블
where e.department_id = d.department_id; -- 조인키
-- left outer 붙일 테이블에 (+) 붙여줌
select * from employees e, departments d -- 조인 테이블
where e.department_id = d.department_id(+); -- 조인키
-- right outer
select * from employees e, departments d -- 조인 테이블
where e.department_id(+) = d.department_id; -- 조인키

select  * from employees e, departments d -- 조인 테이블
where e.department_id(+) = d.department_id
and first_name = 'Pat'; -- 조건

 

2. 서브쿼리

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

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

select * from employees
where salary  > = 12008; -- 낸시의 급여보다 많이 받는 사람

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 * from employees where job_id = 'IT_PROG';

select * 
from employees 
where employee_id >= (select * from employees where job_id = 'IT_PROG'); -- "too many values" 단일행이 나와야함

-- 다중행 서브쿼리
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의 급여보다 작은 사람
select * from employees where salary < all (select salary from employees where first_name = 'Steven' );

select * from employees
where first_name = 'Steven';

-- 스칼라 서브쿼리(select 구문에 서브쿼리가 오는 것, left outer 조인이랑 같은 결과)

-- 스칼라 서브쿼리랑 동일한 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;

-- 스칼라 서브쿼리(아래쪽이 더 좋은 sql문)
select first_name,
      (select department_name from departments D where e.department_ID = d.department_ID) as department_name 
      -- select 뒤에 두개의 컬럼 못 씀, 서브쿼리라 1 row
from employees E --괄호랑 join 들어간다고 생각할 것  
order by first_name asc;

-- 한개의 컬럼 이상 쓸 때(남발은 좋지 않음)
select first_name,
      (select department_name from departments D where e.department_ID = d.department_ID) as department_name,  
      (select location_id from departments D where e.department_ID = d.department_ID) as location_id
from employees E --괄호랑 join 들어간다고 생각할 것  
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;
    
-- 스칼라
select d.*,
    (select first_name from employees e where d.manager_id = e.employee_id) as 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;
    
-- 스칼라 
    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 e2.manager_id = e2.employee_id) as 매니저이름
    from employees e1;
    
-- 부서별 사원수(스칼라)
select count(*) from employees GROUP by department_id; -- 스칼라 

-- 부서정보를 다 찍어줌
select d.*, -- department를 뽑아줘야함
NVL((select count(*) from employees e where e.department_id = d.department_id group by department_id), 0) as 사원수 
from departments d;

54행 참고(스칼라 서브쿼리) f10 - 위에서 부터 볼 것

기존 8번의 비용 소모 - 스칼라 4번(낮으면 좀 더 빠름)