[Database] SQL Assignment 6

 

 

 

 

 

 

 

 

 

 

subquery 없이 join으로 표현하기

 

 

 

#1

SELECT last_name, hire_date
FROM employees
WHERE department_id in (
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
)
AND last_name <> 'Zlotkey';

 

 

subquery 없이 join으로 표현하기 

SELECT D.last_name, D.hire_date
FROM employees E, employees D
WHERE E.department_id = D.department_id AND E.last_name = 'Zlotkey' AND D.last_name <> 'Zlotkey'

 

 

 

#2

SELECT employee_id, last_name
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary

 

 

 

#3

SELECT employee_id, last_name
FROM employees
WHERE department_id in (
    SELECT department_id
    FROM employees
    WHERE last_name like '%u%'
)

 

 

 

 

 

 

 

 

#4

SELECT last_name, department_id, job_id
FROM employees 
WHERE department_id IN (
    SELECT department_id
    FROM departments  
    WHERE location_id = 1700
    )

 

 

 

#5

SELECT last_name, salary
FROM employees 
WHERE manager_id IN (
    SELECT employee_id
    FROM employees  
    WHERE last_name = 'King'
    )

 

 

 

#6

SELECT department_id, last_name, job_id
FROM employees 
WHERE department_id IN (
    SELECT department_id
    FROM departments  
    WHERE department_name = 'Executive'
    )

 

 

 

 

 

 

 

 

 

 

#7

SELECT employee_id, last_name, salary
FROM employees 
WHERE department_id IN (
    SELECT department_id
    FROM employees   
    WHERE last_name LIKE '%u%'
    ) 
AND salary > (
    SELECT AVG(salary)
    FROM employees
    )

 

 

 

#8

SELECT employee_id, last_name, salary
FROM employees 
WHERE salary > (
    SELECT AVG(salary)
    FROM locations
    JOIN departments ON locations.location_id = departments.location_id
    JOIN employees ON departments.department_id = employees.department_id
    WHERE country_id = 'US'
    )

-- 조인 조건을 명시하자 

 

 

 

#9

SELECT E1.employee_id, E1.last_name, E1.salary, E1.department_id
FROM employees E1
WHERE salary = (
    SELECT MAX(E2.salary)
    FROM employees E2
    JOIN departments ON departments.department_id = E2.department_id
    WHERE E1.department_id = E2.department_id
    )
ORDER BY E1.department_id

 

 

 

 

 

 

 

 

 

 

#10

SELECT E1.employee_id, E1.last_name, E1.salary, E1.department_id
FROM employees E1
JOIN (
    SELECT MAX(E2.salary) AS max_salary, E2.department_id
    FROM employees E2 
    JOIN departments ON departments.department_id = E2.department_id 
    GROUP BY E2.department_id
) max_salaries ON E1.department_id = max_salaries.department_id
WHERE E1.salary = max_salaries.max_salary
ORDER BY E1.department_id

 

 

 

#11

SELECT department_id, department_name
FROM departments D
WHERE EXISTS (
    SELECT * 
    FROM employees E
    WHERE E.department_id = D.department_id
    )

 

 

 

#12

* 잘못된 풀이

-- SELECT rownum, employee_id, last_name, salary
-- FROM employees
-- WHERE rownum <= 5
-- ORDER BY salary

 

* ORDER BY 먼저 처리하는 효과
SELECT rownum, employee_id, last_name, salary 
FROM (SELECT employee_id, last_name, salary 
    FROM employees 
    ORDER BY salary) 
WHERE rownum <= 5