[Database] SQL Assignment 4

 

 

 

 

 

 

 

 

 

 

 

 

 

#1. 

SELECT last_name, department_id, department_name
FROM employees NATURAL JOIN departments

-- department_id가 동일하기 때문에 NATURAL JOIN (32개) -> 하나를 없애버린다. 

-- But departments_id 뿐만 아니라, manager_id가 같기 때문에 (서로 다른 의미, 나의 사수 vs 부서장), natural join을 하면 틀림

 

 

 

select e.last_name, e.department_id, d.department_name 
from employees e INNER JOIN departments d 
on (e.department_id = d.department_id)

-- 106개 값이 다르다 - departments의 manager는 부서장 

-- 꼭 join의 조건을 붙여주자 

 

 

 

#2.

SELECT DISTINCT job_id, location_id
FROM employees JOIN departments USING (department_id)
WHERE department_id = 80;

 

 

 

#3.

SELECT last_name, department_name, location_id, city
FROM employees JOIN departments USING (department_id) NATURAL JOIN locations
WHERE commission_pct is not null

-- location id만 겹치므로, natural join 가능 

 

 

 

 

 

 

#4. 

SELECT last_name, department_name
FROM employees JOIN departments USING (department_id)
WHERE employees.last_name LIKE '%a%'

 

 

 

#5. 

SELECT last_name, job_id, e.department_id, department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'Toronto';

 

 

 

#6.

SELECT worker.last_name "Employee", worker.employee_id "Emp#", manager.last_name "Manager", worker.manager_id "Mgr#"
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id

-- 왜 같은 table인데 join 사용?

-- manager 이름이 추가되도록, self join을 해야 한다. 

 

* NULL 값은 조인에 포함이 안되는데, 매니저가 없는 사원도 포함하기 위해서 left outer join을 해줘야 함

SELECT worker.last_name "Employee", worker.employee_id "Emp#", manager.last_name "Manager", worker.manager_id "Mgr#"
FROM employees worker LEFT OUTER JOIN employees manager ON worker.manager_id = manager.employee_id

 

 

 

 

 

 

#7. 

SELECT worker.last_name "Employee", department_name "Department", collegue.last_name "Colleague"
FROM employees worker, employees collegue, departments
WHERE worker.department_id = departments.department_id 
    AND worker.department_id = collegue.department_id 
    AND worker.last_name != collegue.last_name
ORDER BY departments.department_id, worker.last_name, collegue.last_name

 

* 해당 사원과 동일한 부서에서 근무하는 모든 사원을 표시하라 

* 자기 자신도 같은 부서에 근무하기 때문에, 불필요한 투플이 만들어짐 

 

-- Department 필요 X

SELECT worker.last_name "Employee", collegue.last_name "Colleague"
FROM employees worker, employees collegue
WHERE worker.department_id = collegue.department_id 
    AND worker.last_name != collegue.last_name
ORDER BY worker.department_id, worker.last_name, collegue.last_name

 

* (a, b)와 (b,a) 여전히 두 번 출력됨 -> how? 

SELECT worker.last_name "Employee", collegue.last_name "Colleague"
FROM employees worker, employees collegue
WHERE worker.department_id = collegue.department_id 
    AND worker.last_name > collegue.last_name
ORDER BY worker.department_id, worker.last_name, collegue.last_name

* 부등호를 통해서 해결 가능 

 

 

 

#8. 

SELECT e.last_name, e.hire_date
FROM employees e, employees d
WHERE d.last_name = 'Davies' AND d.hire_date <= e.hire_date AND e.last_name <> 'Davies'

* where 절에서 조건을 잘 검색하도록 big table을 잘 구성해야 한다. 

* Self-join 하는 것은 자연스럽지 X -> 이중 select

 

* 중첩 질의 

SELECT last_name, hire_date
FROM employees
WHERE hire_date >= (SELECT hire_date from employees where last_name = 'Davies')

-- 안쪽에 들어간 질의를 sub query, 부질의라고 한다. 괄호 안에 넣어야 함

 

 

 

#9.

SELECT e.last_name "Employee NAME", e.hire_date, m.last_name "Manager NAME", m.hire_date
FROM employees e, employees m
WHERE e.manager_id = m.employee_id AND e.hire_date <= m.hire_date

* 관리자의 입사일까지 한 번에 나타낼 수 없으니, self-join이 필요한 것