SQL 퀴즈 풀기

1. 각 직책별로 급여의 총합을 구하되 직책이 부장인 사람은 제외하시오
 단, 급여 총합이 8000이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오.

select title, sum(salary)
from s_emp
where title != '%부장' -- 이 라인에 주석을 추가제거해가면서 데이터의 변화를 볼 것
group by title
having sum(salary) >= 8000
order by sum(salary);

 

2. '김정미'와 같은 직급을 가진 직원 찾기

SELECT E2.NAME, E2.TITLE, E2.SALARY, E2.DEPT_ID 
FROM S_EMP E1, S_EMP E2
WHERE E1.TITLE = E2.TITLE   -- '김정미'와 같은 직급을 가진 직원 찾기
AND E1.NAME = '김정미'      -- E1은 '김정미'로 고정
AND E2.NAME <> '김정미';    -- E2는 '김정미'가 아닌 다른 직원만 포함

 

 

3.  직원(S_EMP)테이블과 고객(S_CUSTOMER)테이블에서 사원의 이름(e.name)과 사번(e.id), 그리고 각 사원의 담당고객 이름(c.name)을 나타내시요.
 단, 고객에 대하여 담당영업사원이 없더라도 모든 고객의 이름을 나타내고, 사번(e.id) 순으로 오름차순 정렬하시오.

select e.name, e.id, c.name 
from s_emp e, s_customer c
where e.id(+) = c.sales_rep_id --s_customer 테이블을 기준으로, s_emp 테이블과 외부 조인(LEFT OUTER JOIN
order by e.id;

 

4.  서울 지역에 근무하는 사원에 대해 각 사원의 이름과 근무하는 부서명을 나타내시오.

SELECT e.name, e.salary, g.grade 급여동급
FRoM s_emp e, salgrade g
WHERE e.salary BETWEEN g.losal AND g.hisal;

5. 서울 지역(s_region.id=1)에 근무하는 사원에 대해 각 사원의 이름과 근무하는 부서명을 나타내시오.

select e.name, dr.dname, dr.name 
from s_emp e, 
(SELECT D.ID ID, d.name dname, R.NAME NAME 
FROM S_DEPT D, S_REGION R
WHERE d.region_id = r.id) DR

 

6. 각 부서별(GROUP BY DEPT_ID)로 급여의 최소값(MIN(SALARY)과 최대값(MAX(SALARY)을 나타내시오. 
 단, 최소값과 최대값이 같은 부서는 출력하지 마시오.

select dept_id, min(salary), max(salary) from s_emp
group by dept_id
having min(salary) <> max(salary)
order by dept_id;

7. 각 부서(DEPT_ID)내에서 각 직책별(TITLE)로 몇 명의 인원이 있는지를 나타내시오.

select dept_id, title, count(*) from s_emp
group by dept_id, title
order by dept_id, title;

8. 각 직책별(group by title)로 급여의 총합(sum(salary)을 구하되 직책(title)이 부장인 사람은 제외하시오.
 단, 급여총합이 8000(만원) 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오.

select title, sum(salary) from s_emp
where title not like '%부장'
group by title
having sum(salary) >= 8000 
order by sum(salary);

​
9. 각 부서별(group by dept_id)로 평균 급여(avg(salary)를 구하되 평균 급여가 2000이상인 부서만 나타내시오.

select dept_id, avg(salary) from s_emp
group by dept_id
having avg(salary) >= 2000
order by avg(salary);

10. 각 지역(region_id)별로 몇 개의 부서(count(s_dept)가 있는지를 나타내시오.

select s_region.name, region_id, count(region_id) 
from s_dept, s_region
where s_dept.region_id = s_region.id
group by region_id
order by region_id;

11. 각 부서(dept_id)별로 직책(title)이 사원인 직원들의 평균 급여(avg(salary)를 계산해서 보여주시오.

select dept_id, avg(salary) from s_emp
where title = '사원'
group by dept_id
order by dept_id;

12. 1부터 n까지의 숫자를 만들어 내기

select  level  from dual -- dual : 가상 테이블
connect by level <= 5; -- connect by : 계층구조

 

 

13. '*'로 피라미드 출력시키기 

SELECT RPAD('*', LEVEL, '*') --에 따라 별(*) 문자를 LEVEL 길이만큼 오른쪽으로 패딩.
FROM DUAL
CONNECT BY LEVEL <= 5;

14. 날짜 리스트 출력시키기

SELECT TO_DATE('2024/02/01', 'YYYY/MM/DD') + (LEVEL - 1) 
FROM DUAL
CONNECT BY LEVEL <= 7;

15. 최근 12개월동안의 날짜(yyyymm)출력하기

SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1 - LEVEL), 'YYYYMM') AS yyyymm
-- TRUNC(SYSDATE, 'MM')을 사용하면 현재 월의 첫번째 
FROM DUAL
CONNECT BY LEVEL <= 12
ORDER BY yyyymm;

16. 직원 테이블(s_emp)에서 상위 봉급자 6등부터 10등까지 출력하는 쿼리를 작성하시오.

select name, salary 
from (
  select name, salary , rownum as rn
  from ( 
    select name, salary
    from s_emp
    order by salary desc )
  where rownum <= 10 )
where rn >=6;

 

17. 15년도 월별 입사자의 수를 세어서 출력하시오.
단, 1월 12월까지 월별로 출력하시오. 입사자가 없는달은 0으로 출력하시오.

WITH months AS ( -- WITH months AS: 1부터 12까지의 월을 생성하는 서브쿼리입니다. LEVEL을 사용하여 월 번호(month_num)를 생성
    SELECT LEVEL AS month_num
    FROM DUAL
    CONNECT BY LEVEL <= 12
)
SELECT 
    TO_CHAR(ADD_MONTHS(DATE '2015-01-01', month_num - 1), 'MM') AS month,
    NVL(COUNT(e.start_date), 0) AS emp_count --NVL(COUNT(e.start_date), 0): 입사자가 없는 월은 0으로 표시
FROM months m
LEFT JOIN s_emp e
    ON EXTRACT(YEAR FROM e.start_date) = 2015 -- 연도만 추출
    AND EXTRACT(MONTH FROM e.start_date) = m.month_num
GROUP BY m.month_num
ORDER BY m.month_num;

18. 담당직원이 배정되지 않은 고객들을 출력하시오.

select name from s_customer
where sales_rep_id like 'null';

 

==> 내가 썼던 오답 코드

 

SELECT name 
FROM s_customer
WHERE sales_rep_id IS NULL;

 

==> 정답

19. title이 %부장% 인 사람이 2명 이하인 직급 dept_id  수 

select count(dept_id) from s_emp
where (title like '%부장%') <=2
group by dept_id;

 

==> 내가 썼던 오답 코드

 

SELECT COUNT(*) AS dept_count
FROM (
    SELECT dept_id
    FROM s_emp
    WHERE title LIKE '%부장%'
    GROUP BY dept_id
    HAVING COUNT(*) <= 2
);

 

==> 정답 코드