--DCL
--revoke : 부여된 권한을 회수하는 명령
select * from user_views;
grant select on employee_view to dev; -- dev계정에게 employee_view에 대한 select 권한 부여
revoke select on employee_view from dev; --dev계정으로부터 employee_view를 select할수 있는권한을 회수하겠다
--set Operator : union
create table t2(
data number
);
create table t1(
data number
);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
select * from t1;
insert into t2 values(2);
insert into t2 values(3);
insert into t2 values(4);
select * from t2;
-- union 두 테이블의 출력 결과를 합치지만 중복 되는 값 한번만 병합
select from t1
union
select from t2;
-- union all 두 테이블의 출력결과를 중복제거 없이 병합
select from t1
union all
select from t2;
--intersect : 두 테이블의 교집합을 병합
select from t1
intersect
select from t2;
--minus : 첫번쨰 테이블의 조회결과에서 두번쨰 테이블 조회 결과를 빼고 남는(차집합) 결과만 생성
select from t1
minus
select from t2;
select emp_name, phone, email from employee
union
select *from job; -- 에러
--UNION 규칙 1. 두테이블의 갯수가 일치해야 한다. 2. 데이터 타입도 일치해야 한다.
SELECT EMP_NAME, PHONE, SALARY FROM EMPLOYEE
UNION
SELECT * FROM DEPARTMENT;
---- Subquery 쿼리내부에서 사용되는 쿼리
--전지연 직원의 매니저 이름을 출력
select from employee where emp_name='전지연';
select from employee where emp_id=214;
-- 위의 문제를 해겨랗기 위해서는 두번의 쿼리가 필요
-- 하지만 서브쿼리를 사용하면 한번으로 가능
--서브쿼리는 항상 괄호 안에 넣어 써야됨
select * from employee where emp_id = (select manager_id from employee where emp_name= '전지연');
-- 전 직원의 평균 급여보다 월급을 많이 받는 직원의 사번, 이름, 급여를 출력하세요
--두번의 쿼리 사용 경우
select emp_id, emp_name, salary from employee where salary >= avg(salary); -- where절에서는 그룹합수 사용불가 에러 "group function is not allowed here"
select avg(salary) from employee;
select emp_id, emp_name, salary from employee where salary >= 3047662;
-- 서브 쿼리 사용 -- 단일행 단일열 일떄 예시(여러값이 없는 경우)
select emp_id, emp_name, salary from employee where salary >= (select avg(salary) from employee);
--subquery
--단일행 단일열 서브쿼리
-- 1직원 중에서 윤은해와 급여가 같은 사원들을 검색하여, 사원번호 이름 급여를출력하세요 윤운해 제외
select salary from employee where emp_name = '윤은해';
select salary, emp_name from employee where emp_name not like '윤은해' and salary = 2000000;
select emp_id, emp_name, salary from employee where salary=(select salary from employee where emp_name not like '윤은해' and salary = 2000000);--
--내코드 틀림
select emp_id, emp_name, salary from employee where salary = (select salary from employee where emp_name='윤은해')
and emp_name != '윤은해'; -- 강사님
--2 employee테이블에서 급여를 가장 많이 받는 직원의 이름과 급여를 출력하세요
select emp_name, salary from employee where salary >= (select max(salary) from employee);
-- 급여 가장 적게 받는 직원의 이름과 급여도 함께 출력하세요)
select max(salary) from employee;
select min(salary) from employee;
select emp_name, salary from employee where salary =(select min(salary) from employee) or salary = (select max(salary) from employee);--3 D1, D2 부서에서 근무하는 직원들 중에 급여가 D5 부서직우너들의 평균급여보다 많이 받는 사람들의 사번, 사원며, 급여 부서코드를 출력하세요
select avg(salary) from employee where dept_code = 'D5' group by Dept_Code; -- D5의 평균급여
select avg(salary) from employee where dept_code='D5';
select emp_id, emp_name, salary, dept_code from employee where salary >(select avg(salary) from employee where dept_code = 'D5' group by Dept_Code)
and dept_code in ('D1', 'D2');
select emp_id, emp_name, salary, dept_code from employee where salary >(select avg(salary) from employee where dept_code='D5')
and dept_code in ('D1', 'D2');
--다중행 서브쿼리 = 사용불가
-- 송종기 또는 박나라 속한 부서에 속해 있는 모든 직원의 정보
select dept_code from employee where emp_name ='송종기';
select dept_code from employee where emp_name ='박나라';
select dept_code from employee where emp_name IN ('송종기', '박나라');
select * from employee where --서브쿼리
dept_code IN (select dept_code from employee where emp_name IN ('송종기', '박나라'));
select * from employee where --서브쿼리
dept_code NOT IN (select dept_code from employee where emp_name IN ('송종기', '박나라')); --반대의 경우 NOT IN 사용
-- 차태현 직원, 전지연 직원의 등급과 동일한등급을 가진 직원들의 사원명 직급명, 급여등급
select sal_level from employee where emp_name in ('차태연', '전지연'); -- 다중행
select emp_name, job_name, sal_level from employee e inner join job j on e.job_code = j.job_code
where sal_level in (select sal_level from employee where emp_name in ('차태연', '전지연')) order by 3;
-- 직급명이 대표 부사장도 아닌 모든 직원의 이름, 부서명, 직급코드를 부서명으로 정렬하여 출력
select * from job where job_name not in ('대표', '부사장');
select job_code from job where job_name not in ('대표', '부사장');
select emp_name, nvl(dept_title,'인턴'), j.job_code from employee e left join department d on e.dept_code = d.dept_id
inner join job j on e.job_code = j.job_code where job_name in(select job_Name from job where job_name not in ('대표', '부사장')) order by 3;
--job테이블에서 employee에는 없는 job_name을뺴와서 이너조인 두번 사용
select emp_name, nvl(dept_title,'인턴'), job_code from employee e left join department d on e.dept_code = d.dept_id
where job_code in(select job_code from job where job_name not in ('대표', '부사장')) order by 3; --조인 한번 사용 job테이블에도 있는 job_Code를 job테이블에서 뺴와서
select emp_name, dept_title, job_Code from emplotee e left join department d on e.dept_code - d.dept_id
where job_code in(
select
emp_name,
nvl(dept_title,'인턴'),
job_code
from employee left join department on dept_code = dept_id
where job_code not in(select job_code from job where job_name in('대표','부사장')) order by 3; --강사님 코드 조인을 한번밖에 안하심
select job_code from job where job_name in('대표','부사장');
select from job where job_name not in ('대표', '부사장');
select from job;
select * from department;
--다중열 서브쿼리
-- 박나라 / 차태연 직원의 부서코드와 직급코드가 같은 직원들 명단
select emp_name, dept_code, job_code from employee where emp_name in('박나라', '차태연');
select emp_name,
dept_code,
job_code
from employee
where (dept_code, job_code) in ( select dept_code, job_code from employee where emp_name in('박나라', '차태연')); --모르ㅔㄳㄸ..
select dept_code, job_code from employee where emp_name in('박나라', '차태연');--다중행 다중열 서브쿼리
select * from employee;
--직급별 급여가 가장 작은 사람의 이름과 급여를 출력하세요
select job_code, min(salary) from employee group by job_code;
select emp_name, job_code, salary from employee
where (job_code, salary)
in (select job_code, min(salary) from employee group by job_code)
order by 2; --job_code --강사님 코드 where 안 내용이랑 서브쿼리의 select 갯수같아야함
SELECT emp_name,salary,e.job_code,job_name FROM employee e
inner JOIN job j ON e.job_code = j.job_code
WHERE (e.job_code,salary) in
(SELECT
e.job_code,
MIN(salary)
FROM employee GROUP BY job_code)
ORDER BY 3; --job_name 윤은해가 나온다?
select emp_name, salary from employee where group by
select min(salary) from employee;
--다중행과 다중열 사용법은 똑같다
--상관 서브쿼리
--서브쿼리에서 mainquery 를 가져가 사용한후 결과를 반환하는 형태 join필요없지만 단일행, 단일열만 가능
select emp_name, (select dept_title from department where dept_id= dept_code) 부서명 from employee; --
--Inline view from에서 서브쿼리를 사용함
select emp_name, email from (select emp_id, emp_name, phone, email from employee);
---TOP - N분석(순위기법)
select emp_id, emp_name, salary, rank() over (order by salary desc) 급여순위 from employee; --1818 19위 생략
select emp_id, emp_name, salary, dense_rank() over(order by salary desc) 급여순위 from employee; --1818 19 ~
select emp_id, emp_name, salary, row_number() over(order by salary desc) "급여 순위" from employee; --동일할때 공동순위 x
-- 5~10 순위까지만 출력
select * from(
select emp_id, emp_name, salary, row_number() over(order by salary desc) "급여순위" from employee) where 급여순위 between 5 and 10; --동일할때 공동순위 x
-- inline view 활용해서 쿼리 순서 활용 from where group by having select order by
