20230404
[문제1] 입사한 월을 출력하고 월별 입사자 수를 1월부터 12월 까지 순서대로 출력하시오.
((내가 쓴 답안)) SELECT to_char(hire_date, 'mm') "입사한 월", count(*) "월별 입사자 수"
FROM employees
GROUP BY to_char(hire_date, 'mm')
ORDER BY 1;
-- extract 날짜를 숫자형으로
-- to_char 날짜를 문자형으로
((강사님 답안))1) to_char 사용
SELECT month||'월' 월, 인원수
FROM(SELECT to_number(to_char(hire_date, 'mm')) month, count(*) 인원수
FROM employees
GROUP BY to_number(to_char(hire_date, 'mm'))
ORDER BY 1);
2) extract 사용
SELECT month||'월' 월, 인원수
FROM(SELECT extract(month from hire_date) month, count(*) 인원수
FROM employees
GROUP BY extract(month from hire_date)
ORDER BY 1);
[문제2] 근속연수가 가장 긴 10위까지 사원들의 employee_id, last_name, hire_date를 출력하세요.(연이은 순위를 구하세요)
((내가 쓴 답안)) -- asc는 안 써도 됨 써야 함!!, 연이은 순위는 dense_rank 임SELECT *
FROM(SELECT employee_id, last_name, hire_date, rank() over(order by hire_date asc) rank
FROM employees)
WHERE rank <= 10;
((강사님 답안))SELECT *
FROM(SELECT employee_id, last_name, hire_date,
dense_rank() over(order by hire_date) rank
FROM employees)
WHERE rank <= 10;
[문제3] 아래화면과 같이 급여의 도수분포표를 생성하세요.
계급 도수
2000~5000 49
5001~10000 43
10001~15000 12
15001~20000 2
20001~ 1
((내가 쓴 답안)) SELECT *
FROM(SELECT
COUNT(CASE WHEN salary BETWEEN 2000 AND 5000 THEN '1' END) "2000~5000",
COUNT(CASE WHEN salary BETWEEN 5001 AND 10000 THEN '2' END) "5001~10000",
COUNT(CASE WHEN salary BETWEEN 10001 AND 15000 THEN '3' END) "10001~15000",
COUNT(CASE WHEN salary BETWEEN 15001 AND 20000 THEN '4' END) "15001~20000",
COUNT(CASE WHEN salary >= 20001 THEN '5' END) "20001~"
FROM employees)
UNPIVOT(도수 FOR 계급 IN ("2000~5000","5001~10000","10001~15000","15001~20000","20001~"));
--- 구간의 빈도수
((강사님 답안))1) COUNT 사용
SELECT *
FROM(SELECT
COUNT(CASE WHEN salary BETWEEN 2000 AND 5000 THEN 1 END) "2000~5000",
COUNT(CASE WHEN salary BETWEEN 5001 AND 10000 THEN 1 END) "5001~10000",
COUNT(CASE WHEN salary BETWEEN 10001 AND 15000 THEN 1 END) "10001~15000",
COUNT(CASE WHEN salary BETWEEN 15001 AND 20000 THEN 1 END) "15001~20000",
COUNT(CASE WHEN salary >= 20001 THEN 1 END) "20001~"
FROM employees)
UNPIVOT(도수 FOR 계급 IN ("2000~5000","5001~10000","10001~15000","15001~20000","20001~"));
2) SUM 사용
SELECT *
FROM(SELECT
SUM(CASE WHEN salary BETWEEN 2000 AND 5000 THEN 1 END) "2000~5000",
SUM(CASE WHEN salary BETWEEN 5001 AND 10000 THEN 1' END) "5001~10000",
SUM(CASE WHEN salary BETWEEN 10001 AND 15000 THEN 1 END) "10001~15000",
SUM(CASE WHEN salary BETWEEN 15001 AND 20000 THEN 1 END) "15001~20000",
SUM(CASE WHEN salary >= 20001 THEN 1 END) "20001~"
FROM employees)
UNPIVOT(도수 FOR 계급 IN ("2000~5000","5001~10000","10001~15000","15001~20000","20001~"));
3) 연속현 자료(구간) -> 범주형 자료로(★꼭 기억★)
SELECT substr(계급,2) 계급, 도수
FROM(
SELECT CASE_SAL 계급, COUNT(*) 도수
FROM(SELECT
CASE
when salary between 2000 and 5000 then 'a2000~5000'
when salary between 5001 and 10000 then 'b5001~10000'
when salary between 10001 and 15000 then 'c10001~15000'
when salary between 15001 and 20000 then 'd15001~20000'
when salary >= 20001 then 'e20001~'
END CASE_SAL
FROM employees)
GROUP BY CASE_SAL
ORDER BY 1);
[문제4] 15000 이상 급여를 받는 관리자 이름, 급여, 급여등급을 출력하세요.
((내가 쓴 답안)) -- 이걸로는 King이 안 나옴..왜지...SELECT w.last_name "관리자 이름", w.salary "급여", (SELECT grade_level
FROM job_grades
WHERE w.salary BETWEEN lowest_sal AND highest_sal) 급여등급
FROM employees w, employees m
WHERE w.salary >= 15000
AND w.manager_id = m.employee_id;
((강사님 답안))SELECT last_name, salary, (SELECT grade_level
FROM job_grades
WHERE e.salary BETWEEN lowest_sal AND highest_sal)
FROM employees e
WHERE EXISTS (SELECT 'x'
FROM employees
WHERE manager_id = e.employee_id)
AND e.salary >= 15000;
[문제5] 년도 분기별 급여 총액을 출력하세요.
((내가 쓴 답안)) -- GROUP BY 안 써도 됨, 피벗, 총액 구하는 법 다시 확인SELECT to_char(hire_date, 'yyyy') 연도, to_char(hire_date, 'q') 분기, sum(salary)
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date, 'q'));
SELECT *
FROM(
SELECT to_char(hire_date, 'yyyy') 연도, to_char(hire_date, 'q') 분기, sum(salary) SUMSAL
FROM employees
GROUP BY to_char(hire_date, 'yyyy'), to_char(hire_date, 'q'))
PIVOT(MAX(SUMSAL) FOR 분기 IN ('1'"1분기",'2'"2분기",'3'"3분기",'4'"4분기"));
SELECT *
FROM(
SELECT to_char(hire_date, 'yyyy') 연도, to_char(hire_date, 'q') 분기, nvl(sum(salary),0) SUMSAL
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date, 'q')))
PIVOT(MAX(SUMSAL) FOR 분기 IN ('1'"1분기",'2'"2분기",'3'"3분기",'4'"4분기",1 "총액"))
ORDER BY 1;
((강사님 답안))SELECT *
FROM(SELECT to_char(hire_date,'yyyy') 년도, to_char(hire_date,'q') 분기, salary
FROM employees)
PIVOT(sum(salary) FOR 분기 IN ('1'"1분기",'2'"2분기",'3'"3분기",'4'"4분기"))
ORDER BY 1;
[문제6] 같은 부서에서 last_name이 같은 사원들을 찾아주세요. --> 상호관련 섭쿼리 쌍비교 불가?
((내가 쓴 답안))SELECT *
FROM employees e
WHERE EXISTS (SELECT 'x'
FROM employees
WHERE last_name = e.last_name
AND employee_id != e.employee_id
AND department_id = e.department_id);
((강사님 답안))--- EXISTS 이용 (★별 표시★)
SELECT *
FROM employees e
WHERE EXISTS (SELECT 'x'
FROM employees
WHERE last_name = e.last_name
AND employee_id <> e.employee_id
AND department_id = e.department_id);
[추가 문제 6-1] 부서별 last_name 가로로 정렬
SELECT department_id,
LISTAGG(last_name||'('||employee_id||')',',') WITHIN GROUP (ORDER BY last_name)
FROM employees
GROUP BY department_id;
▽▽▽(결과창)10 Whalen(200)
20 Hartstein(201)
30 Baida(116),Colmenares(119),Raphaely(114),Tobias(117),soobin(115)
40 Mavris(203)
50 Atkinson(130),Bell(192),Bissot(129),Bull(185),Cabrio(187),Chung(188),Davies(142),Dellinger(186),Dilly(189),Everett(193),Feeney(197),Fleaur(181),Fripp(121),Gates(190),Gee(135),Geoni(183),Grant(199),Jones(195),Kaufling(122),Ladwig(137),Landry(127),Mallin(133),Markle(128),Marlow(131),Matos(143),McCain(194),Mikkilineni(126),Mourgos(124),Nayer(125),OConnell(198),Olson(132),Patel(140),Perkins(191),Philtanker(136),Rajs(141),Rogers(134),Sarchand(184),Seo(139),Stiles(138),Sullivan(182),Taylor(180),Vargas(144),Vollman(123),Walsh(196),Weiss(120)
60 Austin(105),Ernst(104),Hunold(103),Lorentz(107),Pataballa(106)
70 Baer(204)
80 Abel(174),Ande(166),Banda(167),Bates(172),Bernstein(151),Bloom(169),Cambrault(148),Cambrault(154),Doran(160),Errazuriz(147),Fox(170),Greene(163),Hall(152),Hutton(175),Johnson(179),King(156),Kumar(173),Lee(165),Livingston(177),Marvins(164),McEwen(158),Olsen(153),Ozer(168),Partners(146),Russell(145),Sewall(161),Smith(159),Smith(171),Sully(157),Taylor(176),Tucker(150),Tuvault(155),Vishney(162),Zlotkey(149)
90 De Haan(102),King(100),Kochhar(101)
100 Chen(110),Faviet(109),Greenberg(108),Popp(113),Sciarra(111),Urman(112)
110 Gietz(206),Higgins(205)
Grant(178)
[문제7] 사원수가 3명 미만인 부서번호, 부서이름, 인원수를 출력해주세요.
((내가 쓴 답안)) -- 스칼라 아닌 인라인뷰에 넣어야 함..SELECT e.department_id "부서번호", count() "인원수", (SELECT department_name
FROM departments
WHERE department_id = e.department_id) "부서이름"
FROM employees e
GROUP BY e.department_id
HAVING count() < 3;
((강사님 답안))SELECT d.department_id, d.department_name, e.인원수
FROM(SELECT department_id, count() 인원수
FROM employees
GROUP BY department_id
HAVING count() < 3) e, departments d
WHERE e.department_id = d.department_id;
[문제8] 사원 수가 가장 많은 부서정보, 도시, 인원수를 출력해주세요.
((내가 쓴 답안)) -- employees 테이블 두 번 방문해야 한다는 문제/분석함수 이용SELECT department_id, count()
FROM employees
GROUP BY department_id
HAVING count() = (SELECT max(count(*))
FROM employees
GROUP BY department_id);
SELECT e.department_id, count(), (SELECT city
FROM locations l, departments d
WHERE l.location_id = d.location_id
AND d.department_id = e.department_id) 도시
FROM employees e
GROUP BY e.department_id
HAVING count() = (SELECT max(count(*))
FROM employees
GROUP BY department_id);
((강사님 답안))1) 악성프로그램
SELECT d., l.city, e.인원수
FROM (SELECT department_id, count() 인원수
FROM employees
GROUP BY department_id
HAVING count() = (SELECT max(count())
FROM employees
GROUP BY department_id)) e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
2) 분석함수, case 이용
(풀이 과정) - 분석 함수 이용
SELECT
FROM (SELECT department_id, 인원수, max(인원수) over(), case when 인원수 = max(인원수) over() then 'max' end max_cnt
FROM(SELECT department_id, count() 인원수
FROM employees
GROUP BY department_id))
WHERE max_cnt = 'max';
(최종 답안)
SELECT d., l.city, e.인원수
FROM(SELECT
FROM (SELECT department_id, 인원수, case when 인원수 = max(인원수) over() then 'max' end max_cnt
FROM(SELECT department_id, count(*) 인원수
FROM employees
GROUP BY department_id))
WHERE max_cnt = 'max') e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
[문제9] 요일별 입사한 인원수를 출력해주세요. --> 요일별 정렬?
((내가 쓴 답안)) SELECT to_char(hire_date, 'dy') 요일, count(*)
FROM employees
GROUP BY to_char(hire_date, 'dy');
((강사님 답안)) 1) 피벗 이용
SELECT
FROM (SELECT to_char(hire_date, 'd') day
FROM employees)
PIVOT(count() FOR day IN (2 "월",3 "화",4 "수",5 "목",6 "금",7 "토",1 "일"));
2) 언피벗 이용
SELECT
FROM(SELECT
FROM (SELECT to_char(hire_date, 'd') day
FROM employees)
PIVOT(count(*) FOR day IN (2 "월",3 "화",4 "수",5 "목",6 "금",7 "토",1 "일")))
UNPIVOT(인원수 FOR 요일 IN ("월","화","수","목","금","토","일"));
[문제10] 부서별 최고 급여자들을 출력해주세요 --> 모든 정보?
((내가 쓴 답안)) -- 분석함수 MAX, RANK 이용 가능SELECT *
FROM
(SELECT employee_id, last_name, department_id, salary, rank() over(partition by department_id order by salary desc) rank
FROM employees)
WHERE rank = 1;
((강사님 답안))1) 분석함수 max 이용
SELECT *
FROM(SELECT employee_id, last_name, salary, department_id,
max(salary) over(partition by department_id) max_sal,
case when salary = max(salary) over(partition by department_id) then 'max' end case_max
FROM employees
ORDER BY 4)
WHERE case_max = 'max';
2) 분석함수 rank 이용
SELECT *
FROM(SELECT employee_id, last_name, salary, department_id,
rank() over(partition by department_id order by salary desc) rank
FROM employees)
WHERE rank = 1;
