[Database] 서브쿼리 문제풀이

[Database] 서브 쿼리

---

연습문제 1-1

EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들을 데이터를 출력 하세요. 

SELECT FIRST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY > ALL (SELECT TRUNC(AVG(SALARY),0) FROM EMPLOYEES)
ORDER BY SALARY DESC;

연습문제 1-2

EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들을 수를 출력하세요.

SELECT COUNT(FIRST_NAME) FROM EMPLOYEES
WHERE SALARY > ALL (SELECT TRUNC(AVG(SALARY),0) FROM EMPLOYEES);

연습문제 1-3

EMPLOYEES 테이블에서 job_id가 IT_PFOG인 사원들의 평균급여보다 높은 사원들을 데이터를 출력하세요.

SELECT FIRST_NAME, JOB_ID, SALARY FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG')
ORDER BY SALARY DESC;

---

연습문제 2

DEPARTMENTS테이블에서 manager_id가 100인 사람의 department_id와 EMPLOYEES테이블에서 
department_id가 일치하는 모든 사원의 정보를 검색하세요.

SELECT E.FIRST_NAME, D.DEPARTMENT_ID, E.MANAGER_ID 
FROM DEPARTMENTS D
JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.MANAGER_ID = 100;

---

연습문제 3-1

EMPLOYEES테이블에서 “Pat”의 manager_id보다 높은 manager_id를 갖는 모든 사원의 데이터를 출력하세요.

SELECT FIRST_NAME, MANAGER_ID 
FROM EMPLOYEES
WHERE MANAGER_ID < ALL ( SELECT MANAGER_ID
                         FROM EMPLOYEES
                         WHERE FIRST_NAME = 'Pat' )
OR MANAGER_ID IS NULL
ORDER BY MANAGER_ID ASC;

---

연습문제 3-2

EMPLOYEES테이블에서 “James”(2명)들의 manager_id와 갖는 모든 사원의 데이터를 출력하세요.

SELECT * FROM EMPLOYEES 
WHERE MANAGER_ID IN (SELECT MANAGER_ID
                    FROM EMPLOYEES
                    WHERE FIRST_NAME LIKE ('James')
                    );

---

연습문제 4

EMPLOYEES테이블 에서 first_name기준으로 내림차순 정렬하고, 41~50번째 데이터의 행 번호, 이름을 출력하세요.

SELECT * 
FROM (SELECT ROWNUM AS RN, FIRST_NAME 
      FROM EMPLOYEES ORDER BY FIRST_NAME ASC)
WHERE RN BETWEEN 41 AND 50;

---

연습문제 5

EMPLOYEES테이블에서 hire_date기준으로 오름차순 정렬하고, 31~40번째 데이터의 행 번호,
사원id, 이름, 번호, 입사일을 출력하세요.

SELECT * 
FROM ( SELECT ROWNUM AS NUMS, 
              EMPLOYEE_ID, 
              FIRST_NAME, 
              PHONE_NUMBER,
              HIRE_DATE
        FROM EMPLOYEES
        ORDER BY HIRE_DATE ASC)
WHERE NUMS BETWEEN 31 AND 40;

---

연습문제 6

employees테이블 departments테이블을 left 조인하세요.

조건 1) 직원아이디, 이름(성, 이름), 부서아이디, 부서명 만 출력합니다.
조건 2) 직원아이디 기준 오름차순 정렬

SELECT E.EMPLOYEE_ID, 
       E.FIRST_NAME || ' ' || E.LAST_NAME, 
       E.DEPARTMENT_ID, 
       D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY EMPLOYEE_ID ASC;

---

연습문제 7

문제 6의 결과를 (스칼라 쿼리)로 동일하게 조회하세요

SELECT EMPLOYEE_ID,
       FIRST_NAME || ' ' || LAST_NAME,
       DEPARTMENT_ID,
       (SELECT DEPARTMENT_NAME FROM DEPARTMENTS D
        WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS DEPARTMENT_NAME
FROM EMPLOYEES E
ORDER BY EMPLOYEE_ID ASC;

---

연습문제 8

departments테이블 locations테이블을 left 조인하세요.

조건 1) 부서아이디, 부서이름, 매니저아이디, 로케이션아이디, 스트릿_어드레스, 포스트 코드, 시티 만 출력
조건 2) 부서아이디 기준 오름차순 정렬

SELECT * FROM LOCATIONS;
SELECT * FROM DEPARTMENTS;
SELECT D.DEPARTMENT_ID AS 부서ID,
       D.DEPARTMENT_NAME AS 부서이름,
       D.MANAGER_ID AS 매니저ID,
       D.LOCATION_ID AS 지역ID,
       L.STREET_ADDRESS AS 주소,
       L.POSTAL_CODE AS 포스트코드,
       L.CITY AS 도시 
FROM DEPARTMENTS D
LEFT JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
ORDER BY DEPARTMENT_ID ASC;

---

연습문제 9

문제 8의 결과를 (스칼라 쿼리)로 동일하게 조회하세요

SELECT 
     DEPARTMENT_ID AS 부서ID,
     DEPARTMENT_NAME AS 부서명,
     MANAGER_ID AS 매니저ID,
     (SELECT LOCATION_ID FROM LOCATIONS L WHERE D.LOCATION_ID = L.LOCATION_ID) AS 지역ID,
     (SELECT STREET_ADDRESS FROM LOCATIONS L WHERE D.LOCATION_ID = L.LOCATION_ID) AS 주소,
     (SELECT POSTAL_CODE FROM LOCATIONS L WHERE D.LOCATION_ID = L.LOCATION_ID) AS 코드,
     (SELECT CITY FROM LOCATIONS L WHERE D.LOCATION_ID = L.LOCATION_ID) AS 지역
FROM DEPARTMENTS D
ORDER BY DEPARTMENT_ID;

---

연습문제 10

locations테이블 countries 테이블을 left 조인하세요

조건 1) 로케이션아이디, 주소, 시티, country_id, country_name 만 출력합니다.
조건 2) country_name기준 오름차순 정렬

SELECT L.LOCATION_ID,
       L.STREET_ADDRESS,
       L.CITY,
       C.COUNTRY_ID,
       C.COUNTRY_NAME
FROM LOCATIONS L
LEFT JOIN COUNTRIES C
ON L.COUNTRY_ID = C.COUNTRY_ID
ORDER BY COUNTRY_NAME ASC;

---

연습문제 11

문제 10의 결과를 (스칼라 쿼리)로 동일하게 조회하세요.

SELECT LOCATION_ID,
       STREET_ADDRESS,
       CITY,
       COUNTRY_ID,
       (SELECT COUNTRY_NAME FROM COUNTRIES C WHERE L.COUNTRY_ID = C.COUNTRY_ID) AS COUNTRY_NAME 
FROM LOCATIONS L
ORDER BY COUNTRY_NAME ASC;

---

연습문제 12

employees테이블, departments테이블을 left조인 hire_date를 오름차순 기준으로 1-10번째 데이터만 출력합니다

조건 1) rownum을 적용하여 번호, 직원아이디, 이름, 전화번호, 입사일, 부서아이디, 부서이름 을 출력합니다.
조건 2) hire_date를 기준으로 오름차순 정렬 되어야 합니다. rownum이 틀어지면 안됩니다.

SELECT * FROM 
(SELECT ROWNUM AS NUM, A.* FROM 
 (SELECT 
         E.EMPLOYEE_ID,
         E.FIRST_NAME,
         E.PHONE_NUMBER,
         E.HIRE_DATE,
         E.DEPARTMENT_ID,
         D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY HIRE_DATE ASC)
A );

---

연습문제 13

EMPLOYEES 과 DEPARTMENTS 테이블에서 JOB_ID가 SA_MAN 사원의 정보의 LAST_NAME,JOB_ID, DEPARTMENT_ID,DEPARTMENT_NAME을 출력하세요.

SELECT LAST_NAME,
       JOB_ID,
       E.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE E.JOB_ID LIKE ('SA_MAN');