DATABASE SUBQUERY

Let's Just Go·2022년 5월 3일
0

DATABASE

목록 보기
4/11

DATABASE

DATABASE SUBQUERY

  • SUB Query
    • 하나의 SQL문장에 포함되어 있는 다른 SQL문장

    • SELECT, FROM, WHERE에 들어갈 수 있음

    • 괄호() 로 감싸서 사용하며 서브쿼리 연산을 먼저 진행

    • 서브쿼리의 반환 행이 1줄 이하이면 단일 행이고 2줄 이상이면 다중 행 서브쿼리

    • 서브쿼리 안에서는 정렬을 할 수 없음

      --Nancy의 급여보다 많은 사람 검색
      SELECT
          e.first_name,
          e.salary
      FROM
          employees e
      WHERE
          e.salary > (
              SELECT
                  salary
              FROM
                  employees
              WHERE
                  first_name = 'Nancy'
          );
          
      --employee_id가 103번인 사람과 job_id가 동일한 사람을 검색
      SELECT
          *
      FROM
          employees
      WHERE
          job_id = (
              SELECT
                  job_id
              FROM
                  employees
              WHERE
                  employee_id = 103
          );

다중 행 서브쿼리

  • 다중 행 서브쿼리
    • IN : 목록의 어떤 값과 같은지 확인

      SELECT
          *
      FROM
          employees
      WHERE
          job_id IN (
              SELECT
                  job_id
              FROM
                  employees
              WHERE
                  job_id = 'IT_PROG'
          );
      --IT_PROG의 값과 같은 값이 있는지 확인하여 조회

    • ANY : 서브쿼리의 결과가 앞의 부등호를 하나라도 만족하면 조회

      --first_name이 David인 사람 중 가장 작은 값보다 급여가 큰 사람을 조회
      SELECT
          *
      FROM
          employees
      WHERE
          salary > ANY (
              SELECT
                  salary
              FROM
                  employees
              WHERE
                  first_name = 'David'
          );
      --ANY : 서브쿼리의 결과가 앞의 부등호를 하나라도 만족하면 모두 조회 
      --이러면 최소값을 찾을 수 있겠네

    • ALL : 서브쿼리의 결과가 앞의 부등호를 모두 만족해야 조회

      SELECT
          salary
      FROM
          employees
      WHERE
          salary > ALL (
              SELECT
                  salary
              FROM
                  employees
              WHERE
                  first_name = 'David'
          );

  • Scalar Sub Query
    • SELECT 구문에 Sub Query 작성하며 LEFT OUTER JOIN과 유사한 결과

    • 스칼라 서브쿼리가 조인보다 좋은 경우는 함수처럼 한 레코드당 정확히 하나의 값만을 반환할 때

    • 조인이 스칼라 서브쿼리보다 좋은 경우는 조회할 데이터가 대용량인 경우, 해당 데이터가 수정, 삭제 등이 빈번한 경우

      --LEFT OUTER JOIN
      SELECT
          e.first_name,
          d.department_name
      FROM
          employees   e
          LEFT OUTER JOIN departments d ON e.department_id = d.department_id
      ORDER BY
          first_name ASC;
      
      --scalar sub query
      SELECT
          e.first_name,
          (
              SELECT
                  d.department_name
              FROM
                  departments d
              WHERE
                  d.department_id = e.department_id
          ) AS department_name
      FROM
          employees e
      ORDER BY
          first_name ASC;
          
      --각 부서의 매니저 이름
      
      --LEFT OUTER JOIN
      SELECT
          d.*,
          e.first_name AS 매니저아이디
      FROM
          departments d
          LEFT OUTER JOIN employees   e ON d.manager_id = e.employee_id
      ORDER BY
          d.manager_id ASC;
          
      --scalar sub query
      SELECT
          d.*,
          (
              SELECT
                  e.first_name
              FROM
                  employees e
              WHERE
                  d.manager_id = e.employee_id
          ) AS 매니저이름
      FROM
          departments d
      ORDER BY
          d.manager_id ASC;
      
      --각 부서별 사원수 뽑기
      SELECT
          d.*,
          (
              SELECT
                  COUNT(e.department_id)
              FROM
                  employees e
              WHERE
                  d.department_id = e.department_id
              GROUP BY
                  e.department_id
          ) AS 사원수
      FROM
          departments d;
      
      SELECT
          d.*,
          (
              SELECT
                  COUNT(e.department_id)
              FROM
                  employees e
              GROUP BY
                  e.department_id
              HAVING
                  d.department_id = e.department_id
          ) AS 사원수
      FROM
          departments d;

  • Inline View
    • FROM에 서브쿼리가 오는 것을 말함

      SELECT
          *
      FROM
          (
              SELECT
                  ROWNUM AS rn,
                  tbl.*
              FROM
                  (
                      SELECT
                          employee_id,
                          first_name,
                          salary
                      FROM
                          employees
                      ORDER BY
                          salary DESC
                  ) tbl
          )tbl2
      WHERE
              rn <= 20
          AND rn > 10;
      --가장 안쪽 SELECT절에서 필요한 테이블 형식을 생성 
      --바깥쪽 SELECT 절에서 ROWNUM을 붙여서 다시 조회 
      --가장 바깥쪽 SELECT 절에서는 이미 붙어있는 ROWNUM의 범위를 지정해서 조회
      --실행순서 : FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
      
      SELECT tt.name, tt.* FROM 
      (SELECT TO_CHAR(TO_DATE(test, 'YY/MM/DD'), 'MMDD') as mm, name FROM
      (SELECT
          '홍길동'      AS name,
          '20211126' AS test
      FROM
          dual
      UNION ALL
      SELECT
          '김철수',
          '20210301'
      FROM
          dual
      UNION ALL
      SELECT
          '박영희',
          '20210401'
      FROM
          dual
      UNION ALL
      SELECT
          '김뽀삐',
          '20210501'
      FROM
          dual
      UNION ALL
      SELECT
          '박뚜띠',
          '20210601'
      FROM
          dual
      UNION ALL
      SELECT
          '김테스트',
          '20210701'
      FROM
          dual)) tt
      WHERE mm = '0401';

EXERCISE

  • Practice
    • Sub Query

      SELECT
          *
      FROM
          employees e
      WHERE
          e.salary > (
              SELECT
                  AVG(e2.salary)
              FROM
                  employees e2
          );
      
      SELECT
          COUNT(*)
      FROM
          employees e
      WHERE
          e.salary > (
              SELECT
                  AVG(e2.salary)
              FROM
                  employees e2
          );
      
      SELECT
          *
      FROM
          employees e
      WHERE
          e.salary > (
              SELECT
                  AVG(e2.salary)
              FROM
                  employees e2
              WHERE
                  e2.job_id = 'IT_PROG'
          );
      
      SELECT
          *
      FROM
          employees e
      WHERE
          e.department_id = (
              SELECT
                  d.department_id
              FROM
                  departments d
              WHERE
                  manager_id = 100
          );
      
      SELECT
          *
      FROM
          employees
      WHERE
          manager_id > (
              SELECT
                  e.manager_id
              FROM
                  employees e
              WHERE
                  e.first_name = 'Pat'
          );
      
      SELECT
          *
      FROM
          employees
      WHERE
          manager_id IN (
              SELECT
                  e.manager_id
              FROM
                  employees e
              WHERE
                  e.first_name = 'James'
          );
      
      SELECT
          *
      FROM
          (
              SELECT
                  ROWNUM AS num,
                  tr.first_name
              FROM
                  (
                      SELECT
                          *
                      FROM
                          employees e
                      ORDER BY
                          e.first_name DESC
                  ) tr
          )
      WHERE
              num >= 41
          AND num <= 50;
      
      SELECT
          *
      FROM
          (
              SELECT
                  ROWNUM AS rn,
                  tbl.*
              FROM
                  (
                      SELECT
                          e.employee_id,
                          e.first_name,
                          e.phone_number,
                          e.hire_date
                      FROM
                          employees e
                      ORDER BY
                          e.hire_date ASC
                  ) tbl
          )
      WHERE
              rn > 30
          AND rn <= 40;
      
      /*
      문제 6 
      직원 테이블 부서 테이블을 left 조인하세요 
      -- 직원 아이디, 이름(성, 이름), 부서아이디, 부서명만 출력 
      -- 직원 아이디 기준 오름차순 정렬
      */
      
      SELECT
          e.employee_id,
          e.first_name || e.last_name,
          d.department_id,
          d.department_name
      FROM
          employees   e
          LEFT OUTER JOIN departments d ON e.department_id = d.department_id
      ORDER BY
          e.employee_id DESC;
      
      /*
      문제 7
      문제 6의 결과를 스칼라 쿼리로 동일하게 조회 
      */
      SELECT
          e.employee_id,
          e.first_name || e.last_name,
          e.department_id,
          (
              SELECT
                  d.department_name
              FROM
                  departments d
              WHERE
                  e.department_id = d.department_id
          ) AS 부서이름
      FROM
          employees e
      ORDER BY
          e.employee_id DESC;
      
      /*
      문제 8
      departments 테이블 locations 테이블을 left 조인하세요
      -- 부서아아디, 부서이름, 매니저아아디, 로케이션 아이디, 스트릿 어드레스 ,포스트 코드, 시티만 출력
      -- 부서아이디 기준 오름차순 정렬
      */
      SELECT
          d.department_id,
          d.department_name,
          d.manager_id,
          d.location_id,
          l.street_address,
          l.postal_code,
          l.city
      FROM
          departments d
          LEFT OUTER JOIN locations   l ON d.location_id = l.location_id
      ORDER BY
          d.department_id ASC;
      
      /*
      문제 9
      문제 8의 결과를 스칼라 쿼리로 동일하게 조회 
      */
      SELECT
          d.department_id,
          d.department_name,
          d.manager_id,
          d.location_id,
          (
              SELECT
                  l.street_address
              FROM
                  locations l
              WHERE
                  d.location_id = l.location_id
          ) AS 지역주소,
          (
              SELECT
                  l.postal_code
              FROM
                  locations l
              WHERE
                  d.location_id = l.location_id
          ) AS postal_code,
          (
              SELECT
                  l.city
              FROM
                  locations l
              WHERE
                  d.location_id = l.location_id
          )AS 도시
      FROM
          departments d
      ORDER BY
          d.department_id ASC;
      --조인이 훨씬 좋음
      
      /*
      문제 10 
      locations 테이블 countries 테이블을 left 조인하세요
      -- 로케이션 아이디, 주소, 시티, 나라 아이디, 나라 이름만 출력 
      -- 나라 이름 기준 오름차순 정렬 
      */
      SELECT
          l.location_id,
          l.street_address,
          l.postal_code,
          l.city,
          c.country_id,
          c.country_name
      FROM
          countries c
          LEFT OUTER JOIN locations l ON l.country_id = c.country_id
      ORDER BY c.country_name;
      
      /*
      문제 10의 결과를 스칼라 쿼리로 동일하게 조회
      */
      SELECT
          l.location_id,
          l.street_address,
          l.postal_code,
          l.city,
          (
              SELECT
                  c.country_id
              FROM
                  countries c
              WHERE
                  l.country_id = c.country_id
          ) AS 나라아이디,
          (
              SELECT
                  c.country_name
              FROM
                  countries c
              WHERE
                  l.country_id = c.country_id
          ) AS 나라이름
      FROM
          locations l
      ORDER BY 나라이름;

    • Join & Sub Query

      SELECT
          *
      FROM
          employees e
      WHERE
          e.salary > (
              SELECT
                  AVG(e2.salary)
              FROM
                  employees e2
          );
      
      SELECT
          COUNT(*)
      FROM
          employees e
      WHERE
          e.salary > (
              SELECT
                  AVG(e2.salary)
              FROM
                  employees e2
          );
      
      SELECT
          *
      FROM
          employees e
      WHERE
          e.salary > (
              SELECT
                  AVG(e2.salary)
              FROM
                  employees e2
              WHERE
                  e2.job_id = 'IT_PROG'
          );
      
      SELECT
          *
      FROM
          employees e
      WHERE
          e.department_id = (
              SELECT
                  d.department_id
              FROM
                  departments d
              WHERE
                  manager_id = 100
          );
      
      SELECT
          *
      FROM
          employees
      WHERE
          manager_id > (
              SELECT
                  e.manager_id
              FROM
                  employees e
              WHERE
                  e.first_name = 'Pat'
          );
      
      SELECT
          *
      FROM
          employees
      WHERE
          manager_id IN (
              SELECT
                  e.manager_id
              FROM
                  employees e
              WHERE
                  e.first_name = 'James'
          );
      
      SELECT
          *
      FROM
          (
              SELECT
                  ROWNUM AS num,
                  tr.first_name
              FROM
                  (
                      SELECT
                          *
                      FROM
                          employees e
                      ORDER BY
                          e.first_name DESC
                  ) tr
          )
      WHERE
              num >= 41
          AND num <= 50;
      
      SELECT
          *
      FROM
          (
              SELECT
                  ROWNUM AS rn,
                  tbl.*
              FROM
                  (
                      SELECT
                          e.employee_id,
                          e.first_name,
                          e.phone_number,
                          e.hire_date
                      FROM
                          employees e
                      ORDER BY
                          e.hire_date ASC
                  ) tbl
          )
      WHERE
              rn > 30
          AND rn <= 40;
      
      /*
      문제 6 
      직원 테이블 부서 테이블을 left 조인하세요 
      -- 직원 아이디, 이름(성, 이름), 부서아이디, 부서명만 출력 
      -- 직원 아이디 기준 오름차순 정렬
      */
      
      SELECT
          e.employee_id,
          e.first_name || e.last_name,
          d.department_id,
          d.department_name
      FROM
          employees   e
          LEFT OUTER JOIN departments d ON e.department_id = d.department_id
      ORDER BY
          e.employee_id DESC;
      
      /*
      문제 7
      문제 6의 결과를 스칼라 쿼리로 동일하게 조회 
      */
      SELECT
          e.employee_id,
          e.first_name || e.last_name,
          e.department_id,
          (
              SELECT
                  d.department_name
              FROM
                  departments d
              WHERE
                  e.department_id = d.department_id
          ) AS 부서이름
      FROM
          employees e
      ORDER BY
          e.employee_id DESC;
      
      /*
      문제 8
      departments 테이블 locations 테이블을 left 조인하세요
      -- 부서아아디, 부서이름, 매니저아아디, 로케이션 아이디, 스트릿 어드레스 ,포스트 코드, 시티만 출력
      -- 부서아이디 기준 오름차순 정렬
      */
      SELECT
          d.department_id,
          d.department_name,
          d.manager_id,
          d.location_id,
          l.street_address,
          l.postal_code,
          l.city
      FROM
          departments d
          LEFT OUTER JOIN locations   l ON d.location_id = l.location_id
      ORDER BY
          d.department_id ASC;
      
      /*
      문제 9
      문제 8의 결과를 스칼라 쿼리로 동일하게 조회 
      */
      SELECT
          d.department_id,
          d.department_name,
          d.manager_id,
          d.location_id,
          (
              SELECT
                  l.street_address
              FROM
                  locations l
              WHERE
                  d.location_id = l.location_id
          ) AS 지역주소,
          (
              SELECT
                  l.postal_code
              FROM
                  locations l
              WHERE
                  d.location_id = l.location_id
          ) AS postal_code,
          (
              SELECT
                  l.city
              FROM
                  locations l
              WHERE
                  d.location_id = l.location_id
          ) AS 도시
      FROM
          departments d
      ORDER BY
          d.department_id ASC;
      --조인이 훨씬 좋음
      
      /*
      문제 10 
      locations 테이블 countries 테이블을 left 조인하세요
      -- 로케이션 아이디, 주소, 시티, 나라 아이디, 나라 이름만 출력 
      -- 나라 이름 기준 오름차순 정렬 
      */
      SELECT
          l.location_id,
          l.street_address,
          l.postal_code,
          l.city,
          c.country_id,
          c.country_name
      FROM
          countries c
          LEFT OUTER JOIN locations l ON l.country_id = c.country_id
      ORDER BY
          c.country_name;
      
      /*
      문제 11
      문제 10의 결과를 스칼라 쿼리로 동일하게 조회
      */
      SELECT
          l.location_id,
          l.street_address,
          l.postal_code,
          l.city,
          (
              SELECT
                  c.country_id
              FROM
                  countries c
              WHERE
                  l.country_id = c.country_id
          ) AS 나라아이디,
          (
              SELECT
                  c.country_name
              FROM
                  countries c
              WHERE
                  l.country_id = c.country_id
          ) AS 나라이름
      FROM
          locations l
      ORDER BY
          나라이름;  
      
      --문제 12
      SELECT
          *
      FROM
          (
              SELECT
                  ROWNUM AS num,
                  tt.*
              FROM
                  (
                      SELECT
                          e.employee_id,
                          e.first_name,
                          e.phone_number,
                          e.hire_date,
                          d.department_id,
                          d.department_name
                      FROM
                          employees   e
                          LEFT OUTER JOIN departments d ON d.department_id = e.department_id
                      ORDER BY
                          e.hire_date ASC
                  ) tt
          )
      WHERE
              num > 0
          AND num <= 10;   
      
      --문제 13 
      SELECT
          e.last_name,
          e.job_id,
          e.department_id,
          d.department_name
      FROM
          employees   e
          LEFT OUTER JOIN departments d ON e.department_id = d.department_id
      WHERE
          e.job_id = 'SA_MAN';
          
      SELECT
          d.department_name,
          tbl.*
      FROM
               (
              SELECT
                  last_name,
                  job_id, 
                  department_id
              FROM
                  employees
              WHERE
                  job_id = 'SA_MAN'
          ) tbl
          JOIN departments d ON tbl.department_id = d.department_id;
          
      --문제 14
      SELECT
          *
      FROM
               departments d
          JOIN (
              SELECT
                  department_id,
                  COUNT(*) AS total
              FROM
                  employees
              GROUP BY
                  department_id
          ) groupa ON groupa.department_id = d.department_id
      ORDER BY
          groupa.total DESC;
      --GROUPBY한 것을 먼저 작성하고 이후 조인을 통해 원하는 쿼리 적으면 groupby 열 말고도 다른 열도 조회가능
      
      --문제 15
      SELECT
          d.*,
          loc.street_address,
          loc.postal_code,
          nvl(groupemp.result, 0)
      FROM
               departments d
          INNER JOIN locations loc ON d.location_id = loc.location_id
          LEFT JOIN (
              SELECT
                  department_id,
                  trunc(AVG(salary)) AS result
              FROM
                  employees
              GROUP BY
                  department_id
          )         groupemp ON d.department_id = groupemp.department_id;
      --우선 안에 들어가는 서브쿼리부터 먼저 작성(여기서 그룹화)하고 조인하여 나머지 정보 출력
profile
안녕하세요! 공부한 내용을 기록하는 공간입니다.

0개의 댓글