🐷전동준(Jeon DongJun)

Go with the flow

[Oracle]오라클 강의내용 복습 3일차

Oracle 3️⃣

  • 오늘은 HR.txt파일과 문제를 주셔서 그것을 푸는 날이다 신난다 하핳….
  • 하나하나 문제를 풀어보고 내 답과 강사님 답을 비교해보자!

문제

  1. 사원의 FULL_NAME(FIRST_NAME + LAST_NAME) 과 이메일을 출력하자 (이메일@HR.COM 으로 출력하자)

    • 내 답
    SELECT first_name||' '||last_name FULL_NAME, email||'@HR.COM' EMAIL FROM employees;
    • 강사님 답
    SELECT first_name || last_name AS full_name, email || '@HR.com' AS email 
FROM employees;

  2. 06년 이후에 입사한 사원의 이름(FIRST_NAME) 과 사원번호 (EMPLOYEE_ID)를 출력하자

    • 내 답
    SELECT first_name, employee_id FROM employees WHERE hire_date >= '2006-01-01';
    • 강사님 답
    SELECT first_name, employee_id
FROM employees
WHERE hire_date > '05/12/31';

  3. 사원의 이름(FIRST_NAME), 부서이름(DEPARTMENT_NAME), 부서가 있는 거리(STREET_ADDRESS), 부서가 있는 나라(COUNTRY_NAME) 를 출력하자

    • 내 답
    SELECT FIRST_name, department_name, street_address, country_name
FROM employees JOIN departments using(department_id) JOIN locations using(location_id) JOIN countries using(country_id);
    • 강사님 답
    SELECT FIRST_name, department_name, street_address, country_name
FROM employees JOIN departments using(department_id) 
	JOIN locations using(location_id) 
	JOIN countries using(country_id);

  4. ‘부서번호가 90이고, 전화번호가 515로 시작하면서, 끝자리가 4567인 사원’이 관리하는 사원의 사번과 이름을 출력하자

    • 내 답
    SELECT employee_id, first_name
FROM employees
WHERE MANAGER_ID =
(SELECT employee_id
FROM employees
WHERE (department_id = 90)  AND (phone_number LIKE '515%4567'));
    • 강사님 답
    SELECT employee_id, FIRST_NAME
FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE department_id = 90
AND phone_number LIKE '515%4567');

  5. 전화번호가 650.121.8009인 사원의 이름과, 사원이 속한 부서가 위치한 도시, 주소(STREET_ADDRESS + POSTAL_CODE) 를 출력하자

    • 내 답
    SELECT first_name, city, street_address||' '||postal_code address
FROM employees JOIN departments using(department_id) JOIN locations using(location_id)
WHERE phone_number = '650.121.8009';
    • 강사님 답
    SELECT first_name, city, street_address||' '||postal_code AS address
FROM employees JOIN departments using(department_id)
	JOIN locations using(location_id)
WHERE phone_number = '650.121.8009';

  6. Canada 에서 일하고 있는 사원의 이름과, 도시, 월급을 출력하자

    • 내 답
    SELECT first_name, city, salary
FROM employees JOIN departments using(department_id) JOIN locations using(location_id) JOIN countries using(country_id)
WHERE country_name = 'Canada';
    • 강사님 답
    SELECT FIRST_name, city, salary
FROM employees JOIN departments using(department_id)
	JOIN locations using(location_id)
	JOIN countries using(country_id)
WHERE country_name = 'Canada';

  7. 이름이 Guy 인 사원과 같은 부서에서 일하면서, Guy와 직업(JOB_ID)이 다른 사원의 모든 것을 출력하자

    • 내 답
    SELECT *
FROM EMPLOYEES
WHERE (DEPARTMENT_ID  =(SELECT department_id FROM employees WHERE first_name = 'Guy'))
AND (job_id != (SELECT job_id FROM employees WHERE first_name = 'Guy'));
    • 강사님 답
    SELECT *
FROM employees
WHERE department_id = 
(SELECT department_id
FROM employees
WHERE first_name = 'Guy')
AND job_id !=
(SELECT job_id
FROM employees
WHERE first_name = 'Guy')

  8. 직업이 06년 7월 24일에 변경된 사원의 이름과, 과거 직업, 현재 직업을 출력하자

    • 내 답
    SELECT e.first_name, 
js.job_title 과거직업,
(SELECT job_title FROM employees JOIN jobs USING(job_id) WHERE employee_id = (SELECT employee_id FROM job_history WHERE end_date='2006-07-24')) 현직업
FROM employees e INNER JOIN job_history jh ON e.employee_id = jh.employee_id INNER JOIN jobs js ON jh.job_id = js.job_id
WHERE end_date = '2006-07-24';
    • 강사님 답
    SELECT first_name, jh.job_id AS "과거 직업", e.job_id AS "현재 직업"
FROM job_history jh JOIN employees e using(employee_id)
WHERE end_date = '06/07/24';

  9. 관리자가 없는 부서의 이름과 부서번호를 출력하자

    • 내 답
    SELECT department_name, department_id
FROM departments
WHERE manager_id IS NULL;
    • 강사님 답
    SELECT department_name, department_id
FROM DEPARTMENTS
WHERE manager_id IS NULL;

  10. IT 부서 관리자의 성(LAST_NAME)과, 월급을 출력하자 (JOIN 사용하지 않고)

    • 내 답
    select last_name, salary
FROM employees
WHERE employee_id = (SELECT manager_id FROM departments WHERE department_name = 'IT');
    • 강사님 답
    SELECT last_name, salary
FROM employees
WHERE employee_id = 
(SELECT manager_id
FROM departments
WHERE department_name = 'IT');
-- 조인 사용
SELECT last_name, salary
FROM employees e JOIN departments d on(e.employee_id = d.manager_id)
WHERE department_name = 'IT';

  11. 관리자가 존재하는 부서의 장소 중 가장 많은 장소의 도시 이름을 출력하자 (ROWNUM 사용)

    • 내 답
    SELECT city
FROM (SELECT city, count(city) people FROM departments JOIN locations using(location_id) WHERE (manager_id IS NOT NULL) GROUP BY city ORDER BY people DESC)
WHERE rownum = 1;
    • 강사님 답
    SELECT city, cnt, rownum
FROM
(SELECT city, count(*) AS cnt
FROM departments JOIN locations USING(location_id)
WHERE department_id IN 
	(SELECT department_id
	FROM departments
	where manager_id IS NOT NULL)
GROUP BY city
ORDER BY 2 DESC)
WHERE rownum = 1;

  12. 18년도가 근속 10주년인 사원의 이름과 입사일을 출력하자

    • 내 답
    SELECT first_name, hire_date
FROM employees
WHERE to_char(add_months(HIRE_DATE, 120)) LIKE '18%'
    • 강사님 답
    SELECT first_name, hire_date
FROM employees
WHERE substr(add_months(hire_date, 120), 1, 2) = 18;

  13. 사원 이름이 ‘S’로 시작하는 사원의 이름과 사원번호, 전화번호를 출력하자

    • 내 답
    SELECT first_name, employee_id, phone_number
FROM employees
WHERE FIRST_name LIKE 'S%'
    • 강사님 답
    SELECT first_name, employee_id, phone_number
FROM employees
WHERE first_name LIKE 'S%';

  14. 입사년도가 04년 이후인 사원들중 Seattle 에서 근무중인 사원들의 월급 총 합을 출력하자

    • 내 답
    SELECT sum(salary)
FROM employees JOIN departments using(department_id) JOIN locations using(location_id)
WHERE (hire_date >= '2004-01-01') AND city = 'Seattle'
    • 강사님 답
    SELECT sum(salary)
FROM employees JOIN departments using(department_id)
	JOIN locations using(location_id)
WHERE hire_date > '03/12/31'
AND city = 'Seattle';

  15. 전체 평균 월급보다 월급을 많이 받는 사원들 중 9월에 입사한 사원들의 이름과 월급을 출력하자

    • 내 답
    SELECT first_name, salary
FROM employees
WHERE (salary > (SELECT avg(salary) FROM employees)) AND (to_char(hire_date) LIKE '%%/09/%%');
    • 강사님 답
    SELECT first_name, salary
FROM employees
WHERE salary >
	(SELECT avg(salary)
	FROM employees)
AND hire_date LIKE '%/09/%'
-- 서브스트링 사용시
AND substr(hire_date, 4, 2) = '09';

  16. LAST_NAME 의 세번째 글자가 c인 사원들의 풀네임을 출력하자

    • 내 답
    SELECT first_name||' '||last_name FULL_NAME
FROM employees
WHERE last_name LIKE '__c%';
    • 강사님 답
    SELECT first_name||' '||last_name
FROM employees 
WHERE last_name LIKE '__c%';

  17. 부서 번호가 없는 사원의 이름(FIRST_NAME)과 직업(JOB_ID) 입사날(HIRE_DATE)을 출력하자

    • 내 답
    SELECT first_name, job_id, hire_date
FROM employees
WHERE department_id IS NULL;
    • 강사님 답
    SELECT first_name, job_id, hire_date
FROM employees
WHERE department_id is NULL;

  18. Kevin보다 월급을 많이 받고 Susan 보다 적게 받는 사원의 이름과 월급을 출력하자

    • 내 답
    SELECT first_name, salary
FROM employees
WHERE (salary > ALL(SELECT salary FROM employees WHERE first_name = 'Kevin'))
AND (salary < (SELECT salary FROM employees WHERE first_name = 'Susan'));
    • 강사님 답
    SELECT FIRST_name, salary 
FROM employees
WHERE salary > all
	(SELECT salary
	FROM employees
	WHERE first_name = 'Kevin')
AND salary < 
	(SELECT salary
	FROM employees
	WHERE first_name = 'Susan');

  19. Asia에 속해있는 도시이름과 나라 이름을 출력하자

    • 내 답
    SELECT city, country_name
FROM locations JOIN countries using(country_id) JOIN regions using(region_id)
WHERE region_name = 'Asia'
    • 강사님 답
    SELECT city, country_name
FROM locations JOIN countries using(country_id)
	JOIN regions using(region_id)
WHERE region_name = 'Asia';

  20. 월급이 4000이상인 사원의 이름과 부서명, 월급을 출력하자

    • 내 답
    SELECT e.first_name, d.department_name, e.salary
FROM employees e LEFT OUTER JOIN DEPARTMENTS d on(e.department_id=d.DEPARTMENT_ID)
WHERE e.salary >= 4000;
    • 강사님 답
    SELECT first_name, department_name, salary
FROM employees JOIN departments using(department_id)
WHERE salary >= 4000;

  21. 부서의 평균월급이 전체 평균월급보다 높은 부서의 부서이름과 평균월급을 출력하자

    • 내 답
    SELECT department_name, avg(salary)
FROM employees JOIN departments using(department_id)
GROUP BY department_name
HAVING avg(salary) > (SELECT avg(salary) FROM employees)
ORDER BY avg(salary) DESC;
    • 강사님 답
    SELECT department_name, avg(salary)
FROM employees JOIN departments using(department_id)
GROUP BY department_name 
HAVING  avg(salary) >
(SELECT avg(salary)
FROM employees)

  22. 월급을 가장 많이 받는 사원의 전화번호를 출력하자

    • 내 답
    SELECT phone_number
FROM employees
WHERE salary = (SELECT max(salary) FROM employees);
    • 강사님 답
    SELECT phone_number
FROM employees
WHERE salary =
	(SELECT max(salary)
	FROM employees);

  23. Seattle 에 근무하는 사원중 이름이 ‘l’ (엘)로 끝나는 사원의 이름과 직업을 출력하자

    • 내 답
    SELECT first_name, job_title, job_id
FROM employees JOIN jobs using(job_id) JOIN DEPARTMENTS using(department_id) JOIN locations using(location_id)
WHERE (city = 'Seattle') AND first_name LIKE '%l'
    • 강사님 답
    SELECT FIRST_name, job_id
FROM employees JOIN departments using(department_id)
	JOIN locations using(location_id)
WHERE city = 'Seattle'
AND first_name LIKE '%l';

  24. 입사한 년도(hire_date) 별로 인원수를 출력하자

    • 내 답
    SELECT hire_date, count(*)
FROM employees
GROUP BY hire_date
ORDER BY hire_date ASC;
    • 강사님 답
    SELECT substr(hire_date, 1, 2), count(*)
FROM employees
GROUP BY substr(hire_date, 1, 2)
order BY substr(hire_date, 1, 2);
    
-- to_char 이용
SELECT to_char(hire_date, 'yyyy') AS year, count(hire_date) AS num FROM employees
GROUP BY to_char(hire_date, 'yyyy')
ORDER BY to_char(hire_date, 'yyyy');

  25. Canada에서 일하는 직원의 수를 출력하자

    • 내 답
    SELECT country_name, count(*)
FROM employees JOIN departments using(department_id) JOIN locations using(location_id) JOIN countries using(country_id)
GROUP BY country_name
HAVING country_name = 'Canada'
    • 강사님 답
    SELECT count(*)
FROM employees JOIN departments using(department_id)
	JOIN locations using(location_id)
	JOIN countries using(country_id)
WHERE country_name = 'Canada';

  26. 입사 후 직업의 변경이 없는 사원의 사원 번호, 이름, 입사일, 월급, 부서 이름을 출력하자

    • 내 답
    SELECT EMPLOYEE_ID, first_name, hire_date, salary, department_name
FROM employees JOIN departments using(department_id)
WHERE employee_id NOT IN (SELECT employee_id FROM job_history);
    • 강사님 답
    SELECT employee_id, first_name, hire_date, salary, department_name
FROM employees JOIN departments using(department_id)
WHERE employee_id NOT IN
	(SELECT EMPLOYEE_ID
	FROM job_history);

  27. 각 부서 별 커미션이 책정되지 않은 사원의 수를 출력하자

    • 내 답
    SELECT department_id, count(*)
FROM (SELECT * FROM employees WHERE commission_pct IS NULL)
GROUP BY department_id ORDER BY department_id;
    • 강사님 답
    SELECT count(*)
FROM employees
WHERE commission_pct IS NULL
GROUP BY department_id;

  28. 핸드폰 번호가 011 로 시작하는 사원의 이름, 전화번호, 이메일을 출력하자

    • 내 답
    SELECT first_name, phone_number, email
FROM employees
WHERE phone_number LIKE '011%'
    • 강사님 답
    SELECT first_name, phone_number, email
FROM employees
WHERE phone_number LIKE '011%';

  29. 이름이 Britney 인 사원과 같은 부서에서 일하면서, Britney와 직업(JOB_ID)도 같은 사원의 모든 것을 출력하라.

    • 내 답
    SELECT *
FROM employees
WHERE DEPARTMENT_ID = (SELECT department_id FROM employees WHERE first_name='Britney')
AND job_ID = (SELECT job_id FROM employees WHERE first_name='Britney');
    • 강사님 답
    SELECT *
FROM employees 
WHERE (department_id, job_id) = 
	(SELECT department_id, job_id
	FROM employees
	WHERE first_name = 'Britney');

  30. IT 부서의 관리자의 연봉(commission_pct 생각하기) 보다 더 많은 평균 월급을 받는 부서의 부서번호와 부서이름을 출력하자

    • 내 답
    SELECT department_id, department_name, avg(salary)
FROM employees JOIN departments using(department_id)
GROUP BY DEPARTMENT_ID, department_name
HAVING avg(salary) > 
(SELECT salary*12 + salary *nvl(COMMISSION_PCT ,0) FROM employees WHERE employee_id = 
(SELECT manager_id
FROM DEPARTMENTS 
WHERE DEPARTMENT_NAME = 'IT'))
ORDER BY department_id;
    • 강사님 답
    SELECT DEPARTMENT_ID, department_name, avg(salary)
FROM departments JOIN employees using(department_id)
GROUP BY department_id, department_name
HAVING avg(salary) > 
(SELECT salary * 12 + salary * nvl(commission_pct, 0)
FROM employees
WHERE employee_id = 
	(SELECT manager_id
	FROM departments
	WHERE department_name = 'IT'))

2022

[web]jQuery 복습 3

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 2

13 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 1

14 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리4

5 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리3

10 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리2

7 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리1

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]CSS 기초 정리

11 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]HTML 기초 정리

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[Pandas]pandas 연습

3 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑

2021

[Python기초]module

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑