558p~561p 문제풀이

후엥_ 2023. 5. 14. 05:27

728x90

558p 문제 1.

hr스키마에 있는 Employees, Departments 테이블의 구조를 파악한 후 사원수가 5명 이상인 부서명과 사원수를 출력하시오. 이때 사원 수가 많은 순으로 정렬하시오(5행)

힌트 > 기본적으로 SQL의 각 절의 사용방법을 이해해야 한다. 요구하는 컬럼이 서로 다른 테이블에 있고, 동등한 조건으로 데이터를 처리해야 적합한 데이터를 출력할 수 있다. 또한 어떤 컬럼을 기준으로 그룹화하고 집계 결과에 대한 조건 처리도 해야한다.

풀이:

SELECT d.department_name, COUNT(*)
FROM Departments d, Employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_name
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC;

그룹화를 일단 진행하여야 하는데 나는 name을 기준으로 그룹화를 진행하였다.

SELECT d.department_name, COUNT(*) 문과 GROUP BY d.department_name문 HAVING COUNT(*) > 5 문은 세트라고 보면 되는데

뭔뜻이냐면 department_name을 그룹화 하여 같은 이름을 가진 것끼리 카운트 한다.

HAVING COUNT(*) > 5 는 5개 이상인 것들만이라는 조건을 붙인 것이다.

FROM Departments d, Employees e
WHERE d.department_id = e.department_id

이 부분은 조인을 위해 만든것이다.

조인을 하는 이유는 department_id로 부서명을 알 수 있는데 부서명과 인원은 둘이 조인해야만 알 수 있는 정보이다.

559P 문제 3번

각 사원과 직속상사와의 관계를 이용하여 다음과 같은 형식의 보고서를 작성하고자 한다.

예) 홍길동은 허균에게 보고한다. -> Eleni Zlotkey report to Steven King

어떤 사원이 어떤 사원에게 보고하는 위 예를 참고하여 출력하시오. 보고할 상사가 없는 사원이 있다면 그 정보도 포함하여 출력하고, 상사의 이름은 대문자로 출력하시오 (107행)

힌트 > 외부조인(CUTER JOIN)은 동등조인의 조건에서 누락된 정보를 포함하여 출력하고자 할 때 사용된다.

풀이

SELECT e.first_name || ' ' || e.last_name || 'report to' || ' ' || UPPER(m.first_name) || ' ' || UPPER(m.last_name) as "Employee vs Mansger"
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

이번거는 어렵다기 보다는 귀찮은 문제이다.

SELECT 문에서 ||는 그냥 뒤에 계속 추가해라 라는 기호, UPPER문은 소문자를 대문자로 출력해라 라는 문법, as는 이름을 뒷문장으로 바꿔라 라는 내용이다.

FROM 문에서 employees e로 한 이유는 외부조인을 할것이라서 그렇다.

LEFT JOIN문은 왼쪽 외부조인으로 LEFT JOIN employees m ON e.manager_id = m.employee_id 문을 해석하자면

" 왼쪽 외부조인 할거야 : employees m 은 e.manager_id를 m.employee_id 에 넣어"

라는 뜻으로 해석 가능하다.

그렇기 때문에 UPPER 문을 보면 m.last_name이라고 적을 수 있는데

manager_id가 100이라면 STIVEN KING을 출력하는 것이다.

560p 문제 1

사원의 급여 정보 중 업무별 최소 급여를 받고 있는 사원의 성과 이름(Name으로 별칭), 업무, 급여, 입사일을 출력하시오(21행).

힌트 > 부속질의에는 그룹 데이터 처리가 가능하다.

풀이

SELECT e.first_name || ' ' || e.last_name AS Name, e.job_id, e.salary, e.hire_date
FROM employees e
WHERE e.salary = (
    SELECT MIN(salary)
    FROM employees
    WHERE job_id = e.job_id
)
ORDER BY e.employee_id;

이건 생각보다 애먹었다. 부속질의를 쓰니까 어려웠다.

일단 SELECT에서 원하는 name,job_id,salary,hire_date를 출력하자

그리고 FROM 문에서는 employees e를 작성하는데 이유는 밑에 부속질의를 쓰면서 employees를 하나 더 쓸거라서 구분을 위해 뒤에 e를 붙여준다.

드디어 대망의 부속질의다. where문에서 e.salary 와 같은것만 반환한다.

이유는 부속질의에다 최소값을 넣어버릴거라서 그렇다.

최소값과 겉에있는 salary값이 같으면 그것이 그 부서의 최소값이 될것이다.

그래서 부속질의의 select 문에는 min(salary)를 넣는다.

그러면 salary의 최소값을 찾는다. 어디서? employees 에서

그리고 where문에서 밖의것과 안에것을 맞춰보는것이다.

그러면 최소값들이 나오게 된다.

ORDER BY는 정렬이다. employee_id 순으로 정렬한것이다.

560p 문제 2

소속 부서의 평균 급여보다 많은 급여를 받는 사원의 성과 이름(Name으로 별칭), 급여, 부서번호, 업무를 출력하시오 (38행).

힌트> 상관관계 부속질의는 바깥쪽 쿼리의 컬럼이 안쪽 부속질의에 사용되는 경우로, 테이블에서 행을 먼저 읽어서 각 행의값을 관련된 데이터와 비교하는 방법 중 하나이다.

풀이

SELECT e.first_name || ' ' || e.last_name AS Name, e.salary, e.department_id, e.job_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
)

이것도 위에 문제와 같다.

다만 달라진건 department_id로 부속질의를 묶은것이다.

왜냐하면 위에서는 업무별 최소급여였지만 현재는 부서의 평균급여보다 적은 사람을 찾는것이기 때문에 department_id 를 내부쿼리에 넣어서 확인한다. 나머지는 다 같다.

561p 문제 3번

사원들의 지역별 근무 현황을 조회하고자 한다. 도시의 이름이 영문 'O'로 시작하는 지역에 살고 있는 사원의 사번, 성과 이름(Name으로 별칭), 업무, 입사일을 출력하시오(34행).

힌트 > 요구사항에 나타내는 정보를 조인을 이용하지 않고 하나하나 연결하여 부속질의로 처리할 수 있다. 도시정보와 부서정보, 부서정보와 사원정보는 서로 연관이 가능한 구조이므로 부속질의 처리 가능하다.

풀이

SELECT e.employee_id, e.first_name || ' ' || e.last_name AS Name, e.job_id, e.hire_date
FROM employees e
WHERE e.department_id IN (
    SELECT d.department_id
    FROM departments d
    WHERE d.location_id IN (
        SELECT l.location_id
        FROM locations l
        WHERE l.city LIKE 'O%'
    )
);

거 진도가 너무 빠른거 아니오...

갑자기 확어려워 지는데..

일단 부속질의를 2개를 해야하나다.

이제 뭐 부속질의 위에는 다 알것이라고 생각한다.

WHER 문에서 부터 시작할건데.

e.deparment_id IN( ) 이라는 부분을

부서 id가 같은 것만 출력하라는 의미다

자 일단 이정도 알고 다음문장을 보자

WHERE d.location_id IN () 이게 또있다.

이번에는 location_id랑 같은것만 출력하라는 의미다

그리고 그안에는 'o%'가 존재하는데 시작이 O인 것을 찾으라는 뜻을 의미하며

즉 O로시작하는 지역의 location_id와 같은것을

전체 departments에서 department_id를 찾고

그 다음 employees 에서 department_id와 같은것을 출력하라는 의미이다.

밑에서 부터 올라가면 조금 해석이 쉽게 될 것 같다.

p561 문제4

모든 사원의 소속 부서 평균연봉을 계산하여 사원별로 성과 이름(Name으로 별칭), 업무, 급여, 부서번호, 부서 연봉(Department Avg Salary로 별칭)을 출력하시오 (107행)

힌트 > 스칼라 서브쿼리는 부속질의의 SELECT 절에 포함되는 항목 값이며, 하나의 행에서 하나의 컬럼 값만 출력하는 부속질의 이다.

풀이

SELECT e.first_name || ' ' || e.last_name AS "Name", e.job_id, e.salary, e.department_id,
    ROUND((SELECT AVG(salary)
    FROM employees
    WHERE department_id=e.department_id
    ),0)AS "Department AVG Salary"
FROM employees e
ORDER BY "Department AVG Salary";

뭔가 이상하다..

아무리 해도 위와 같게는 안된다

그냥 일단 결과는 같다.

평균값을 하면 소수점 단위가 나와서 ROUND 함수를 사용해 소수점은 반올림 시켜버렸다.

그것외에는 전과 같다

728x90