CS/데이터베이스

lab 6

seeyoun 2023. 10. 18. 11:42

◆ 서브쿼리 

중첩질의.

서브 쿼리(inner query)는 메인 쿼리가 실행되기 전에 한 번만 실행됨
→ 매번 돌려서 넣는 것이 아니라, 서브 쿼리의 결과가 메인 쿼리에서 사용됨
 

◆ 서브쿼리 사용법

  • 서브 쿼리는 괄호로 묶어야 한다.
  • 서브 쿼리는 비교 연산자의 오른쪽에 위치한다.
  • 서브 쿼리는 ORDER BY절을 포함할 수 없다.
  • 서브 쿼리 결과가 하나가 나올지 다수가 나올지에 따라 적절한 연산자를 사용해야 한다.

 

◆ 서브 쿼리 - 반환값 (feat. 행)

  • 단일행 서브 쿼리 : 단일 행을 반환하는 경우
  • 다중행 서브 쿼리 : 하나 이상의 행을 반환하는 경우

 

◆ 서브 쿼리- 반환값 (feat. 열)

튜플로 반환 가능.

◆ 다중행 비교 연산자

 IN 은 =ANY와 동일하다.

NOT IN은 <>ALL과 동일함

ANY는 (서브쿼리에 의해) 반환된 각 값 중 하나라도 비교연산 만족하면 됨

ALL은 (서브쿼리에 의해) 반환된 모든 값이 다 비교연산 만족해야 함

 

job_id가 IT_PROG가 아닌 사람 중에서, job_id가 IT_PROG인 누군의 salary(최대값)보다 salary가 적은 경우의 행들을 뽑음

 

 

job_id가 IT_PROG가 아닌 사람 중에서, 항상 job_id가 IT_PROG인 사람들의 salary(최소값)보다 salary가 적은 경우의 행들을 뽑음

 

◆ EXISTS

•EXISTS: 중첩 질의의 결과가 빈 릴레이션인지 여부를 검사함
•중첩 질의의 결과가 빈 릴레이션이 아니면 참이 되고, 그렇지 않으면 거짓

EXISTS(서브 쿼리)는 서브 쿼리의 결과가 "한 건이라도 존재하면" TRUE 없으면 FALSE를 리턴한다.

EXISTS는 서브 쿼리에 일치하는 결과가 한 건이라도 있으면 쿼리를 더 이상 수행하지 않는다.

 

◆ 서브쿼리에서 널 값

서브 쿼리에서 반환한 값 중 하나라도 널 값이면 전체 질의가 널 값을 반환함.

비교연산자를 사용한 널 값의 비교 결과는 

서브 쿼리 내에서 IS NOT NULL로 제한 가능

 

◆ 상관 중첩질의

중첩질의 안에서 외부질의에 있는 테이블에 엑세스하는 것을 의미함.

이런 경우에는 한번 수행해놓고 계속 쓸 수 없다.

매번 부질의를 실행시켜줘야 함 (상관중첩질의가 아닌 경우에는 그냥 한번 실행해놓고 결과 쓰면 됨)

→ 수행시간 오래걸림
 

◆ 중첩질의 - FROM 절

FROM 절에 중첩 질의를 넣을 수도 있다.

이때 여러 릴레이션을 쓴다면 항상 alias를 사용해 이름을 부여해야 한다.

 

 

 

1. Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하  는 질의를 작성하십시오. (Zlotkey는 제외)

 

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

 

2. 급여가 평균 급여보다 많은 모든 사원의 사원 번호와 이름을 표시하  는 질의를 작성하고 결과를 급여에 대해 오름차순으로 정렬하십시오.

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

 

3.  이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원  번호와 이름을 표시하는 질의를 작성하십시오.

SELECT employee_id, last_name
FROM employees
WHERE department_id IN(SELECT department_id   FROM employees   WHERE last_name LIKE '%u%');

 

4. 부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표  시하십시오.

 

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

 

 

SELECT last_name, department_id, job_id
FROM employees JOIN departments USING(department_id)
WHERE location_id = 1700;

 

 

5. King에게 보고하는 모든 사원 이름과 급여를 표시하십시오

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

주의)) 이름이 king인 사람이 여러명 있을 수도 있다는 점 주의

 

6. Executive 부서의 모든 사원에 대한 부서 번호, 이름 및 업무 ID를 표  시하십시오.

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

 

 

7. 평균 급여보다 많은 급여를 받고 이름에 u가 포함된 사원과 같은 부서  에서 근무하는 모든 사원의 번호, 이름 및 급여를 표시하십시오.

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

 

8.미국(locations.country_id = ‘US’) 내에서 근무하는 사원들의 평균  급여보다 많은 급여를 받는 사원의 번호, 이름 및 급여를 표시하십시  오.
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >
(SELECT AVG(salary)
    FROM employees
    WHERE department_id IN
     (SELECT department_id
        FROM departments
        WHERE location_id IN 
            (SELECT location_id
            FROM locations
            WHERE country_id = 'US')));

 
9.부서 별로 최고 급여를 받는 사원의 번호, 이름, 급여 몇 부서 번호를  표시하고 부서 번호에 대해 오름 차순 정렬을 하시오.
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE (salary, department_id) IN(SELECT MAX(salary),department_id  FROM employees GROUP BY department_id)
ORDER BY department_id;

주의) 형식 기억하자

 

 
10.FROM 절을 사용하여 9 번 질의를 재 작성하시오.
SELECT e1.employee_id, e1.last_name, e1.salary, e1.department_id
FROM employees e1 JOIN (
    SELECT MAX(salary) as max_salary ,department_id  
    FROM employees 
    GROUP BY department_id) e2
ON e1.department_id = e2.department_id AND e1.salary = e2.max_salary
ORDER BY e1.department_id;

주의)) 서브 쿼리 내 SELECT문에서 as 안쓰고 " "큰따 안에 넣으면 오류 뜸. 왜지? as 쓰고 큰따 넣어도 오류 뜸.

이런 형식알아두면 좋을듯. 9번 <-> 10번

 

 

11.사원이 한 명 이상 존재하는 부서의 번호 및 부서 이름을 표시하시오.  (exists 키워드 사용)
SELECT department_id, department_name
FROM departments
WHERE EXISTS (
    SELECT *
    FROM employees
    WHERE employees.department_id = departments.department_id)

주의)) EXISTS 형태 기억하기

 

12.다음을 참고하여 급여를 가장 적게 받는 사원 5명에 대한 정보를 표  시하시오.
SELECT rownum, employee_id, last_name, salary
FROM (
    SELECT * 
    FROM employees 
    ORDER BY salary)
WHERE rownum <= 5

주의) rownum 키워드와, 사용법 기억

 

저작자표시