✨ [ORACLE]_23.02.27

--8장 서브쿼리 
select sal
from emp
where ename = 'JONES';

select ename, sal, deptno from emp
where sal > 2975;

select ename, sal, deptno from emp 
where sal > (select sal from emp where ename = 'JONES');

select ename, sal deptno from emp
where sal > (select avg(sal) from emp) ;

 
select ename, sal, deptno 
from emp
where sal = (select min(sal) from emp group by deptno); 
-- 오답 
--여러 값을 비교해야하므로 = 연산자 아닌 in연산자 사용필
--where sal in (select min(sal) from emp group by deptno);

-- 다중행 서브쿼리 사용 
select ename, sal, deptno
from emp 
where sal in (select min(sal) from emp group by deptno); 

-- 
select ename, sal, deptno 
from emp 
where sal
in (950, 800, 1300); 

--
select ename, sal, deptno 
from emp
where sal = 950
or sal = 800
or sal = 1300;

--
select ename, sal, deptno
from emp 
where sal > any( select avg(sal) from emp group by deptno);
--group by deptno 여부에 출력 값이 달라짐
--select avg(sal) from emp group by deptno; // 1566 
--select avg(sal) from emp; // 2073

select ename, sal, deptno 
from emp
where sal > 1566
or sal > 2175
or sal > 2916;

select ename, sal, deptno 
from emp
where sal > (select min(avg(sal)) from emp group by deptno);
--group by deptno 여부에 출력 값이 달라짐


--
select ename, sal, deptno 
from emp
where sal > all(select avg(sal)from emp group by deptno

--
select ename, sal, deptno
from emp
where sal > 1566
and sal > 2175
and sal > 2916;
-- 
select ename, sal, deptno
from emp
where sal > (select max(avg(sal)) from emp group by deptno); 

--
--문제1. smith (ename) 와 같은 부서에 근무하는 사원 이름 출력
--select ename 
--from emp e, dept d
--where e.deptno = d.deptno
--and lower(e.ename) = 'smith';
-- sol 
select ename 
from emp
where deptno = (select deptno from emp where lower(ename) = 'smith');

-- 본인 제외 출력 
select ename 
from emp
where deptno = (select deptno from emp where lower(ename) = 'smith')
and lower(ename) != 'smith';

-- 
문제2. 사번이 7369인 사원과 직업 같고, 급여는 더 많이 받는 사원의 이름, 직업, 급여
select ename, job, sal
from emp
where sal > (select sal from emp where empno = 7369) 
and job = (select job from emp where  empno = 7369);

--
문제3. emp, dept
sales 부서

select ename 
from emp 
where deptno = (select deptno from dept where upper(dname) = 'SALES')
and hiredate between to_date('1981-01', 'yyyy-mm') and to_date('1981-06', 'yyyy-mm');
-- 날짜는 무조건 단일 부호로 

--SELECT ENAME, HIREDATE 
--FROM EMP E JOIN DEPT D
--ON E.DEPTNO = D.DEPTNO
--WHERE E.DEPTNO = (SELECT D.DEPTNO
--                FROM DEPT
--                WHERE UPPER(DNAME) = 'SALES')
--AND HIREDATE BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
--                AND TO_DATE('1981-06-30', 'YYYY-MM-DD');
--                
                
-- 
select ename, hiredate
from emp
where hiredate 
between to_date('19810101', 'yyyymmdd') and to-date('19810630', 'yyyymmdd')
and deptno = (select deptno from dept where upper(dname)= 'SALES'); 

--양 테이블의 속성을 보여줄 때는 서브쿼리가 아닌 조인을 수행 
select e.deptno, d.dname, e.ename, e.sal
from emp e, dept d 
where e.deptno = d.deptno 
and lower(job) = 'manager';

-- 서브 쿼리의 테이블은 메인 쿼리절로 가져올 수 없음. 
-- 즉 서브쿼리 테이블인 dept의 속성인 dname을 메인 select 절에 기재할 수 없음
-- 따라서 양 테이블의 속성을 보여줄 때는 서브쿼리가 아닌 조인을 수행 

-- 
select last_name 
from employees
where employee_id not in (select manager_id from employees where manager_id is not null);
-- or 
select last_name 
from employees
where employee_id not in (select nvl(manager_id, 0) from employees);

-- 12개가 나와야하는데 not in 하면 결과가 안나옴
-- 185p not in 연산자와 서브쿼리 사용시 주의 is not null 기재 필 
-- in 연산자는 null이 있어도 내부적으로 or과 같은 연산 수행하므로 error 발생 x  
-- 이와 달리 not in 연산자는 비교값에 null이 있으면 error 발생, is not null 기재 필 

-- 교재 170p 쌍방식 비교 서브 쿼리 
select employee_id, manager_id, department_id
from empl_demo 
where (manager_id, department_id) in 
                    (select manager_id, department_id
                    from empl_demo 
                    where first_name = 'John') 
and first_name != 'John'; -- 자기자신(john) 제외하고 출력 

-- 교재 171p 비쌍 방식 비교 서브 쿼리 
select employee_id, manager_id, department_id
from empl_demo 
where manager_id in 
                (select manager_id
                from empl_demo 
                where first_name = 'John')
and department_id in 
                (select department_id
                from empl_demo 
                where first_name = 'John')
and first_name <> 'John';  -- 자기자신(john) 제외하고 출력 

--select employee_id, manager_id, department_id
--from empl_demo 
--where (manager_id, department_id) in 
--                    (select manager_id, department_id
--                    from empl_demo 
--                    where first_name = 'Daniel'); 
--                    
--select manager_id, department_id
--                    from empl_demo 
--                    where initcap(first_name) = 'Daniel';

-- case when 표현식의 스칼라 서브쿼리
-- 상관 서브쿼리 이면서 스칼라 서브쿼리인 경우는 아래 경우  
--SELECT department_id, department_name,
-- (SELECT COUNT(*)
-- FROM employees e
-- WHERE e.department_id = d.department_id) as emp_count
--FROM departments d;

--  8장. Subquery _sql_prac

--1) 
select ename, deptno
from emp
where deptno = (select deptno from emp where lower(ename) = 'smith')
and ename != 'Smith';

--2) 
select ename, job, sal
from emp 
where job = (select job from emp where empno = 7369)
and sal > (select sal from emp where empno = 7369);

--3) 
select empno, ename, hiredate, sal+comm "income"
from emp 
where deptno = (select deptno from dept where dname = 'SALES')
and hiredate between to_date('1981-01', 'yyyy-mm') and to_date('1981-06', 'yyyy-mm')
order by hiredate; 

select ename
from emp 
where deptno = (select deptno from dept where upper(dname) = 'SALES')
and hiredate between to_date('1981-01', 'yyyy-mm') and to_date('1981-06', 'yyyy-mm');


--4) 
select e.deptno, d.dname, e.ename, e.sal
from emp e, dept d 
where e.deptno = d.deptno 
and job = 'MANAGER';

--5) 
select ename 
from emp 
where mgr = (select empno from emp where ename = 'KING'); 

--6) 
select ename, deptno, sal
from emp
where sal = (select min(sal) from emp); 

--7) 
select ename, sal, job
from emp 
where sal > (select avg(sum(sal)) from emp group by ename);

--8) 
select job, avg(sal) 
from emp
group by job
having avg(sal)  = (select min(avg(sal))from emp group by job);

--9) 
select deptno, dname, loc
from dept
where deptno in (select deptno from emp where deptno is not null);

--10) 
select e.ename, e.sal, d.dname 
from emp e 
join dept d 
on e.deptno = d.deptno 
select grade_level from job_grades between lowest_sal and highest_sal);

select * 
from salgrade;

select e.ename, e.sal, d.dname
from emp e 
join dept d
on e.deptno = d.deptno 
and sal between (select losal from salgrade where grade = '5')
                    and (select hisal from salgrade where grade = '5');


--11) 
select deptno, dname, loc
from dept  
where deptno not in (select deptno from emp where deptno is not null);

--12) 
select empno, ename
from emp 
where empno in (select mgr from emp where mgr is not null);

--13) 
select empno, ename
from emp 
where empno not in (select mgr from emp where mgr is not null);

--14)
select empno, ename, deptno, sal
from emp 

--15)  
--select e.deptno, d.dname, e.empno, e.ename, e.sal 
--from emp e
--join dept d
--on e.deptno = d.deptno
--and  sal > (select avg(sal) 
--            from emp e1 
--            where  e1.deptno=d.deptno )
--and e.empno in (select mgr from emp);

select e.deptno, d.dname, e.empno, e.ename, e.sal 
from emp e
join dept d
on e.deptno = d.deptno
and  sal > (select avg(sal) 
            from emp e1 
            where  e1.deptno=d.deptno )
and job = 'MANAGER';
상관 쿼리 
--select department_id, employee_id, salary
--from employees o 
--where salary in (select(min(salary)) from employees where department_id = o.department_id);


--16) 
select deptno, dname, loc,
nvl((select count(ename) from emp where deptno = d.deptno group by deptno), '0') as cnt
from dept d
group by deptno, dname, loc;

--select deptno, dname, loc, nvl((select count(ename) from emp where deptno = d.deptno), '0') as cnt
--from dept d;

--select deptno, dname, loc, nvl((select count(ename) from emp where deptno =deptno group by deptno), '0') as cnt
--from dept 
--group by deptno, dname, loc;


-- 16. 인라인 뷰 dept, emp
SELECT d.deptno, d.dname, d.loc, nvl(e.cnt, '0') "cnt"
FROM (SELECT deptno, count(empno) cnt FROM EMP group by deptno) e
right join DEPT D
on  E.DEPTNO = D.DEPTNO;

SELECT d.deptno, d.dname, d.loc, nvl(e.cnt, '0') "cnt"
FROM (SELECT deptno, count(empno) cnt FROM EMP group by deptno) e, DEPT D
where E.DEPTNO(+) = D.DEPTNO;

-- 16. 스칼라 서브 쿼리 
select deptno, dname, loc,
nvl((select count(ename) from emp where deptno = d.deptno group by deptno), '0') as cnt
from dept d; 
-- or 
select d.deptno, d.dname, d.loc,
	(select count(*) from emp
	where  deptno = d.deptno) cnt
from  dept d;

--컬럼별 비교가 필요한 경우 -> 상관 서브 쿼리 활용 
--select department_id, employee_id, salary
--from employees
--where salary in (select(min(salary)) from employees group by department_id);


--상관 서브 쿼리 활용 (메인 테이블 as를 서브 쿼리 내 활용) 
--select department_id, employee_id, salary
--from employees o 
--where salary in (select(min(salary)) from employees where department_id = o.department_id);

--상관 서브 쿼리 예시 
--select last_name, salary, department_id
--from employees outer_table
--where salary > 
--    (select avg(salary)
--    from employees inner_table 
--    where inner_table.department_id = outer_table.department_id);
-- 상관 쿼리문     
select last_name, salary, department_id
from employees outer_table
where salary in 
    (select min(salary)
    from employees inner_table 
    where inner_table.department_id = outer_table.department_id);
-- or     
--다중 컬럼 쿼리문 
select department_id, employee_id, salary
from employees
where (department_id, salary) in
        (select department_id, min(salary)
        from employees
        group by department_id);
        
-- in 연산자 활용 
select employee_id, last_name 
from employees 
where employee_id in (select manager_id from employees) ;

-- exists 활용 
select employee_id, last_name 
from employees o
where exists (select manager_id 
                from employees
                where manager_id = o.employee_id) ;
                
-- exists 활용, 반드시 상관 서브 쿼리와 함께 사용
--(상관 서브 쿼리, 리터럴 문자(숫자) select 절 기재) 

select employee_id, last_name 
from employees o
where exists (select 1
                from employees
                where manager_id = o.employee_id) ;
                
-- not exists-> 값의 존재 유무를 확인 (not in 연산과 달리 is not null 기재 불필요)  
select employee_id, last_name 
from employees o
where not exists (select 1
                from employees
                where manager_id = o.employee_id) ;         
                
select employee_id, last_name 
from employees 
where employee_id not in (select manager_id from employees
where manager_id is not null);    

-- sql_실습 exists 
--select empno, ename, sal, deptno 
--from emp 
--where empno in (select mgr from emp);
--
--select empno, ename, sal, deptno 
--from emp e
--where exists (select 1 from emp where mgr = e.empno);
--
--select empno, ename, sal, deptno 
--from emp
--where empno not in (select mgr from emp);
--
--select empno, ename, sal, deptno from emp e
--where not exists (select 1 from emp where mgr = e.empno);

-- 조인과 서브쿼리 연산이 동일할때 조인으로 수행 
-- 
--1 join 2
--1   1 -> 1
--1   m -> m 
--m   1 -> m 
--m   m -> m*m 
--
--sub_query 
--m sub 
--1  1 -> 1
--1  m -> 1 (메인이 중요한 것임. 메인에 서브쿼리 절을 충족하는 것을 하나만 있어도 반환) 
--m  1 -> m 
--m  m -> m 
--
--<조인연산과 서브쿼리 반환 값이 동일한 경우>
--join 
--m 1 -> m 
--sub_query
--m 1 -> m 
--
--<조인연산과 서브쿼리 반환 값이 상이한 경우>
--join 
--1 m -> m  (중복 튜플 발생)
--sub_query
--1  m -> 1

-- 인라인 뷰 : 가상의 테이블을 from 절 내 삽입 후 조인 수행 
-- 반드시 필요한 경우: 1) 정렬된 결과에 rownum 을 사용해야하는 경우


--1. Join 으로 처리
--2. Subquery로 처리 (join 에 들어가는 양을 절감 => 그룹핑하고 조인을 수행한다 by 인라인 뷰 활용) 
--SQL> 
--SELECT p.prod_id, p.prod_name, s.sold_sum 
--FROM prods p, 
--(SELECT prod_id, SUM(quantity_sold) AS sold_sum 
--FROM sales
--GROUP BY prod_id) s 
--WHERE p.prod_id=s.prod_id(+);

-- 인라인 뷰 실습 
select e.empno, e.ename, e.sal, e.deptno, a.avg
from emp e join (select deptno, avg(sal) as avg  
                from emp 
                group by deptno) a
on e.deptno = a.deptno 
and e.sal > a.avg;

---- 스칼라 서브쿼리 
--스칼라 서브쿼리
--: Select절에 있는 Subquery
---- 특징
--하나의 행(row)만 반환한다.
--일치하는 값이 없으면 NULL을 반환한다.

select ename, sal, deptno from emp e
where sal > (select avg(sal) from emp where deptno = e.deptno);

-- with 
-- 서브쿼리를 WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다. 특히 여러 번 반복되는 서브쿼리를 WITH 절로 만들어서 사용하면 쿼리의 성능을 높일 수 있다.
-- 인라인뷰와 유사한 기능 수행 
-- with문에서 사용한 서브쿼리의 칼럼을 다른 with문에서 참조가 가능하다. => Inlin View와 다른 장점

-- sql실습_with 절 
select deptno, sum(sal) 
from emp 
group by deptno
having sum(sal) > (select avg(sum(sal)) from emp group by deptno);

with emp_sum 
as (select deptno, sum(sal) sum from emp group by deptno)
select * 
from emp_sum 
where sum > (select avg(sum) from emp_sum);

-- jobs, employees, job_history 테이블을 이용하여 직무별 현직 사원 수, 이전 사원수를 검색하시오.
select job_id,
(select count(employee_id) from employees where job_id = j.job_id) "now_cnt",
nvl((select count(employee_id) from job_history where job_id = j.job_id and sysdate > end_date group by job_id), '0') "history_cnt"
from jobs j 
group by job_id;

select * 
from job_history;
-- 16. 인라인 뷰 dept, emp
SELECT d.deptno, d.dname, d.loc, nvl(e.cnt, '0') "cnt"
FROM (SELECT deptno, count(empno) cnt FROM EMP group by deptno) e
right join DEPT D
on  E.DEPTNO = D.DEPTNO;

SELECT d.deptno, d.dname, d.loc, nvl(e.cnt, '0') "cnt"
FROM (SELECT deptno, count(empno) cnt FROM EMP group by deptno) e, DEPT D
where E.DEPTNO(+) = D.DEPTNO;

-- 16. 스칼라 서브 쿼리 
select deptno, dname, loc,
nvl((select count(ename) from emp where deptno = d.deptno group by deptno), '0') as cnt
from dept d; 
-- or 
select d.deptno, d.dname, d.loc,
	(select count(*) from emp
	where  deptno = d.deptno) cnt
from  dept d;