📚 1. SubQuery(하위질의)

📝 시작하기전, group by와 having 절
하나의 쿼리에 다른 쿼리가 포함되는 구조, ()로처리
1) 단일행 서브쿼리(단일행반환) : > , < , >=, <= , <>

• Main Query, Sub Query -- 1개결과
  2) 다중행 서브쿼리(여러행반환) : in, any, all
• Main Query
• Sub Query -- 여러개의 결과
• < any : 비교대상중 최대값보다 작음
  (ex. 과장직급의 최대급여보다 적게 받는 사원조회)

  • any : 비교대상중 최소값보다 큼
    (ex. 과장직급의 최소급여보다 많이 받는 사원조회)

  • = any : in연산자와 동일
    (ex. 과장 직급과 동일한 급여를 받는 사원조회)
  • < all : 비교대상중 최소값보다 작음
    (ex. 과장직급의 최소 급여보다 적게 받는 사원조회)

  • all : 비교대상중 최대값보다 큼
    (ex. 과장직급의 최대 급여보다 많이 받는 사원조회)

 

✔️ Neena 사원의 부서명

select department_id
from departments
where department_id=(select department_id
					from employees where first_name='Neena');

 

✔️ Valli Pataballa의 업무명(job_title)을 알아내시오

select job_title
from jobs
where job_id=(select job_id from employees where first_name='Valli' and last_name='Pataballa');

 

✔️ Alexander Hunold의 근무지(city)를 알아내시오.
department_id -> location_id -> city

select city
from locations
where location_id=(select location_id
					from departments
					where department_id=(select department_id
										from employees
										where first_name='Alexander' and last_name='Hunold'));

 

✔️ Neena사원의 부서에서 Neena사원보다 급여를 많이 받는 사원들을 구하시오 ==> 1건

(90)         (17000)
first_name    department_id    salary
----------------------------------------
Steven	     90	           24000

 

select first_name, department_id, salary
from employees
where department_id=(select department_id
					from employees
					where first_name='Neena')
and salary > (select salary
				from employees
				where first_name='Neena');

 

✔️ oliver와 같은 업무ID이면서 같은 부서가 아닌 사원의 이름(first_name),업무ID, 부서ID를 출력하시오 ==> 1건

                       (SA_REP)  (80)       
first_name        job_id        department_id
------------------------------------------------
Kimberely	SA_REP	

select first_name, job_id, department_id
from employees
where job_id = (select job_id 
				from employees 
				where lower(first_name)='oliver')
and ifnull(department_id,0) != (select department_id
							from employees
							where lower(first_name)='oliver');

• ifnull이란? : Column의 값이 NULL을 반환할 때, 다른 값으로 출력할 수 있도록 하는 함수

 

✔️ Austin과 같은부서이면서 같은 급여를 받는사원들의 이름, 부서명, 급여를 구하시오

--         last_name     department_name   salary
--        -------------------------------------------------  
--        Austin	          IT            4800
--        Pataballa           IT            4800     

select last_name, department_name, salary
from employees
left join departments using(department_id)
where department_id=(select department_id
					from employees
                    where last_name='Austin')
and salary=(select salary
			from employees
			where last_name='Austin');

 

✔️ 최저급여를 받는 사원들의 이름과 급여를 구하시오.

--       last_name   salary
--       ---------------------
--       Olson       2100  

select last_name,salary
from employees
where salary=(select min(salary)
              from employees);

 

✔️ 평균급여를 받는 사원들의 이름과 급여를 구하시오 ==> 2건
평균급여를 천단위 절삭하시오

--       사원명        급여
--       ----------------------
--       Ernst          6000
--       Fay            6000

select last_name, salary
from employees
where salary=(select truncate(avg(salary),-3) 
              from employees);

• truncate(숫자, 버릴 자릿수) : 숫자를 버릴 자릿 수 아래로 버린다.

 

✔️ 업무ID별 급여평균중 전체평균급여보다 적게 받는 업무ID의 업무명과 급여평균를 구하시오(단일행 서브쿼리) ==> 6건

--     조건1) 업무별 급여평균을 구한다
--     조건2) 급여평균은 천단위 절삭한다
--     조건3) 타이틀은 업무명,급여합계로 한다
--     조건4) 모든 사원을 포함한다

--    업무명                                             급여평균
--    ---------------------------------
--    Programmer	             5000
--    Purchasing Clerk	         2000
--    Marketing Representative   6000
--    Administration Assistant   4000
--    Stock Clerk	             2000
--    Shipping Clerk	         3000

select job_title as "업무명", truncate(avg(salary),-3) as "급여평균"
from employees
left join jobs using(job_id)
group by job_title
having avg(salary) < (select avg(salary) from employees);

&nbps;

✔️ 'SA_REP' 직급보다 급여가 많은 'ST_MAN'직급 직원들을 조회하시오

select last_name, job_id, salary
from employees
where job_id='ST_MAN'
and salary > any (select distinct salary  
				  from employees  
				  where job_id='SA_REP');

 

✔️ 'SA_REP' 직급의 최소급여보다 급여가 적은 'ST_MAN'직급 직원들을 조회하시오

-- last_name   job_id   salary
-- -----------------------------
-- Mourgos	ST_MAN	5800

select last_name, job_id, salary
from employees
where job_id='ST_MAN'
and salary < all(select distinct salary
					from employees
                    where job_id='SA_REP');

 

✔️ 'IT_PROG' 직급중 가장 많이 받는 사원의 급여보다,더 많은급여를 받는 'FI_ACCOUNT' 또는 'SA_REP' 직급 직원들을 조회하시오

--      조건1) 급여순으로 내림차순정렬하시오
--      조건2) 급여는 세자리마다 콤마(,) 찍고 화폐단위 '달러'을 붙이시오
--      조건3) 타이틀은  사원명, 업무ID, 급여로 표시하시오

--      사원명      업무ID          급여
--      ----------------------------------------
--      Ozer         SA_REP        11,500달러
--      Abel         SA_REP        11,000달러
--      Vishney      SA_REP        10,500달러

select last_name as "사원명", job_id as "업무ID", 
       concat(format(salary,0),'달러') as "급여"
from employees
where job_id in ('FI_ACCOUNT','SA_REP')
and salary > all (select distinct salary
				  from employees
				  where job_id='IT_PROG');

 

✔️ 전체직원에 대한 관리자와 직원을 구분하는 표시를 하시오(in, not in이용)

--       조건1) 구분별 오름차순하고 사원번호별 오름차순정렬하시오

-- 사원번호      이름          구분
-------------------------------------
-- 100      King    관리자

-- 방법1 (case, in연산자)
select employee_id as "사원번호", last_name as "이름",
       case  when employee_id in(select distinct manager_id from employees) then '관리자'
       else '직원'
	   end as "구분"      
from employees
order by 3,1;

--  방법2 (uinon, in, not in연산자)
select employee_id as "사원번호", last_name as "이름", '관리자' as "구분"
from employees
where employee_id in (select distinct manager_id from employees)
union
select employee_id as "사원번호", last_name as "이름", '직원' as "구분"
from employees
where employee_id not in (select distinct manager_id from employees where manager_id is not null)
order by 3;

 

✔️ 마지막

--      조건1) 직급별 평균급여를 구한후 모든 사원중 그 급여를 받는 사원을 조회하시오
--            (단, 100단위 이하 절삭)
--      조건2) 출력할 급여는 세자리마다 콤마와 $표시
--      조건3) 사원이름(last_name),직무(job_title) ,급여(salary) 로 표시하시오
--      조건4) 급여순으로 오름차순 정렬하시오

--         사원이름                  직무                 급여
--         ---------------------------------------
--        Mikkilineni Stock Clerk    $2,700
--        Seo         Stock Clerk    $2,700
--        Nayer       Stock Clerk    $3,200

select last_name as "사원이름", job_title as "직무",
       concat('$',format(salary,0)) as "급여"
from employees
left join jobs using(job_id)
where truncate(salary, -2) in(select distinct truncate(avg(salary),-2)
							from employees
							group by job_id)
order by salary;   

 

끝부분 많이 어렵다. 나중에 다시 한번 보기