SQL - 5 (서브쿼리, 제약조건)

■ 서브쿼리 1

 

-- Q) Daniel이 근무하는 부서의 평균급여를 구하시오.

 

1) 만약 100부서인지 안다면

 

SELECT ROUND(AVG(salary)) 평균급여 
FROM employees
WHERE department_id = 100; --8601

 

2) 만약 100부서인지 모른다면

 

- Daniel 부서의 아이디는? -- subquery 

- 평균급여 -- main query

 

2-1) 

 

SELECT department_id
FROM employees
WHERE LOWER(first_name) = 'daniel' --100

 

2-2)

 

SELECT ROUND(AVG(salary)) 평균급여 
FROM employees
WHERE department_id = 100; --8601

 

2-3)

 

SELECT ROUND(AVG(salary)) 평균급여 
FROM employees
WHERE department_id = (SELECT department_id
                        FROM employees
                        WHERE LOWER(first_name) = 'daniel');

 

-- Q) 봉급을 24000 달러 받는 사원의 부서아이디를 알아내어 
-- 부서아이디 및 해당 부서명을 조회 출력하시오.

 

SELECT department_id 부서아이디, department_name 부서명                           
FROM departments                
WHERE department_id = (SELECT department_id 
                        FROM employees
                        WHERE salary= 24000);

 

90 Executive

 

-- Q) 급여를 8200을 받는 직원의
-- 부서아이디를 찾아 해당 부서아이디와 해당부서명을 출력하시오.

 

SELECT department_id 부서아이디, department_name 부서명                           
FROM departments                
WHERE department_id = (SELECT department_id 
                        FROM employees
                        WHERE salary = 8200);  -- 50 or 100

-- 오류발생

 

급여 8200 인 사원이  여러명이 있다는 것.  

IN을 사용한다.

 

SELECT department_id 부서아이디, department_name 부서명                           
FROM departments                
WHERE department_id IN (SELECT department_id 
                        FROM employees
                        WHERE salary=8200);

 

-- Q) nancy가 속한 부서의 최소급여를 구하시오.

 

SELECT MIN(salary) 최소급여
FROM employees 
WHERE department_id = (SELECT department_id
                        FROM employees
                        WHERE LOWER(first_name) = 'nancy');

-- 6900

 

SELECT department_id 부서ID, MIN(salary) 최소급여
FROM employees 
WHERE department_id = (SELECT department_id
                        FROM employees
                        WHERE LOWER(first_name) LIKE '%nancy%')
GROUP BY department_id;

 

-- Q) 각 부서별로 최저급여를 받는 사원의 
-- 이름과 부서아이디,급여를 조회 출력하시오(subquery) 
-- (단, 출력은 부서아이디 오름차순)

 

1) 부서별 최저급여

 

SELECT MIN(salary) 
FROM employees
GROUP BY department_id;

 

2) 

 

SELECT first_name, department_id, salary
FROM employees
WHERE salary IN (5000, 3000, 10000)

 

3)

 

SELECT first_name, department_id, salary
FROM employees
WHERE salary IN (SELECT MIN(salary) 
                    FROM employees
                    GROUP BY department_id)
ORDER BY department_id;

-- pair wise 발생

 

정답)

 

SELECT first_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MIN(salary) 
                                    FROM employees
                                    GROUP BY department_id)
ORDER BY department_id;

 

■ Join vs Subquery

 

□ Join

 

-- Q) 'IT'부서에서 근무하는 사원들의 이름, 급여, 입사일, 부서아이디, 부서명을 조회출력하시오. (join) 
-- (출력시 부서명추가)    

-- <hint> 
-- 1) 사원들의 이름, 급여, 입사일, 부서아이디, 부서명을 조회출력
-- 2) 'IT'부서에서 근무하는 사원(추가조건) 

 

SELECT e.first_name 이름, e.salary 급여, e.hire_date 입사일, 
       e.department_id 부서아이디, d.department_name 부서명
FROM employees e, departments d
WHERE d.department_id = e.department_id 
AND d.department_name = 'IT';

 

 

□ Subquery

 

-- 'IT'부서에서 근무하는 사원들의 이름, 급여, 입사일, 부서아이디, 부서명을 
-- 조회출력하시오. (subquery) 

--<hint> 
-- 1) IT부서의 부서아이디를 알아내자.(100번이라면)
-- 2) 100번 부서아이디 조건으로 처리

 

SELECT first_name 이름, salary 급여, hire_date 입사일,
                    department_id 부서아이디, 'IT' 부서명
FROM employees           
WHERE department_id = (SELECT department_id   
                        FROM departments   
                        WHERE department_name = 'IT');

  

---

 

■ 제약조건(constraint)

 

제약조건을 거는 이유 : 무결성

 

□ 제약조건의 종류

 

1. pk
2. fk
3. nn(not null)
4. uk(unique) : null 여러개 o
5. ck(check) check(sal >= 0), check(tel like '02-3333-%')

 

SELECT * FROM dictionary;
SELECT * FROM USER_TAB_COLS;

 

□ 제약조건 방식

 

1. column level 방식

-- 제약조건을 column 쓰고 이어서 기록하는 것 

 

2. table level 방식

-- 제약조건을 맨 뒤에 몰아서 기록하는 것

 

-- 단, nn은 only column level 방식

 

-- schema : 데이터 베이스의 구조와 제약조건 등을 정의해 놓은 것
-- 외부 스키마, 개념 스키마, 내부 스키마

 

■ 제약조건 (컬럼 레벨)

 

회원 테이블을 생성한다. (ID, 회원이름)

 

CREATE TABLE memtable2(
    memid number(6) 
        CONSTRAINT memt2_memid PRIMARY KEY,
    memname VARCHAR2(20) 
        CONSTRAINT memt2_memname_uk unique
);

 

DESC memtable2

 

참고)

 

SELECT * FROM USER_CONSTRAINTS;

제약조건 이름을 만들 때는 테이블명_컬럼명_제약조건.

 

이제는 만화테이블을 만들어보자.

 

-- 만화테이블 (mantable2)
-- 만화명은 null이면 안 됨(manname)
-- 만화수는 0보다 커야 함(mansoo)
-- 만화테이블의 memid는 fk(memid)

 

CREATE TABLE mantable2(
manname VARCHAR2(20) CONSTRAINT mant2_manname_nn NOT NULL,
mansoo NUMBER(6) CONSTRAINT mant2_mansoo_ck CHECK(mansoo > 0) ,
memid NUMBER(6) CONSTRAINT mant2_memid_fk REFERENCES memtable2(memid) 
);

 

-- 앞에 회원테이블이 pk 때문에 먼저 만들고 뒤의 것 fk를 만든다.

-- 회원테이블로부터 자료 입력

 

INSERT INTO memtable2
VALUES(2, 'hong');

INSERT INTO memtable2
VALUES(3, 'choi');

COMMIT;

SELECT * FROM memtable2;

 

-- 만화테이블 자료 입력

 

INSERT INTO mantable2
VALUES('yakoo', 10, 3);

INSERT INTO mantable2
VALUES('nongkoo', 7, 2);

COMMIT;

 

INSERT INTO mantable2(manname, mansoo) 
VALUES('noinki', 5);

COMMIT;

SELECT * FROM mantable2;

 

■ 제약조건 (테이블레벨)

 

-- 테이블명 : memtable3
-- 컬럼명 이름, 전화번호, 취미, 회비, 아이디(memname, memtel, memhoby, membi, memid)

 

-- memidtable 테이블 생성

 

CREATE TABLE memidtable(
    memid NUMBER(6) CONSTRAINT midt_memid_pk PRIMARY KEY,
    memname VARCHAR2(20)
);

 

-- memtable3 테이블 생성

 

CREATE TABLE memtable3(
memname VARCHAR2(20),
memtel VARCHAR2(20),
memhoby VARCHAR2(30) CONSTRAINT meme3_memhoby_nn NOT NULL,
membi NUMBER(5),
memid NUMBER(6),
CONSTRAINT memt3_mname_pk PRIMARY KEY(memname),
-- CONSTRAINT memt3_mname_pk PRIMARY KEY(memname, memtel)
CONSTRAINT memt3_mtel_uk UNIQUE(memtel),
CONSTRAINT memt3_mtel_ck CHECK(memtel LIKE '02-7788-%'),
CONSTRAINT memt3_mbi_ck CHECK(membi > 0),
CONSTRAINT memt3_mid_fk FOREIGN KEY(memid) REFERENCES memidtable(memid)
);

-- not null은 밑으로 내려갈 수가 없다. 무조건 컬럼 level로

 

-- 데이터 입력

 

INSERT INTO memidtable
VALUES(1, '홍길동');

INSERT INTO memidtable
VALUES(2, '홍길말');

COMMIT;

INSERT INTO memtable3(memid, memtel, memhoby, memname)
VALUES (1, '02-7788-3333', '테니스', '이은우');

COMMIT;

SELECT * FROM memtable3;

 

-- membi 는 아무것도 입력하지 않았지만 (null) 처리 되기 때문에 오류가 나지 않는다.

-- 다음과 같은 경우엔 입력이 진행되지 않는다.

 

INSERT INTO memtable3(memid, memtel, memhoby, membi, memname)
VALUES (2, '010-7788-3333', '테니스', 200, '이운우'); 
-- 에러 ORA-02290: 체크 제약조건(HR.MEMT3_MTEL_CK)이 위배되었습니다
-- 번호를 02 대신 010으로 사용했기 때문에

INSERT INTO memtable3(memid, memtel, memhoby, membi, memname)
VALUES (2, '02-3456-7890', '없어', 0, '이선우'); -- 회비 0
-- 오류 보고 - ORA-02290: 체크 제약조건(HR.MEMT3_MBI_CK)이 위배되었습니다

INSERT INTO memtable3(memid, memtel, memhoby, membi, memname)
VALUES (5, '02-7788-8888', '축구', 10000, '이도령');
-- 오류 보고 - 무결성 제약조건(HR.MEMT3_MID_FK)이 위배되었습니다- 부모 키가 없습니다
-- memid는 foreign key 이기 때문에 

COMMIT;

DELETE FROM memidtable; -- 부드럽게 지우는 방법, 안에 있는 내용을 지우는 것.
-- ORA-02292: 무결성 제약조건(HR.MEMT3_MID_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
-- 지워지지 않음

DELETE FROM memtable3;
ROLLBACK;

DROP TABLE memidtable;

-- 드랍 명령도 되지 않기 때문에 ALTER를 사용하자.

ALTER TABLE memidtable DROP CONSTRAINT midt_memid_pk;
-- 오류 보고 - ORA-02273: 고유/기본 키가 외부 키에 의해 참조되었습니다
-- 지워지지 않기 때문에 fk 먼저 지워야 한다.

ALTER TABLE memtable3 DROP CONSTRAINT memt3_mid_fk; -- 자식쪽의 fk 먼저
ALTER TABLE memidtable DROP CONSTRAINT midt_memid_pk;

-- fk의 연결고리를 먼저 끊어야 한다.

 

---

 

■ 서브쿼리 2

-- 서브쿼리 문장이 where 절에 있으면 subquery
-- 서브쿼리 문장이 select 절에 있으면 scala subquery
-- 서브쿼리 문장이 from 절에 있으면 inline view

 

□ 스칼라 서브쿼리

 

-- 서브쿼리가 select 절이 있으면서 1행만 반환

 

-- Q) 사원의 이름, 부서id, 급여를 조회 출력하시오.
-- 단, 사원이 속한 해당 부서의 최소급여를 급여 다음란에 넣어 출력하시오.

 

1) 110번 부서의 최소급여

 

SELECT MIN(salary)
FROM employees
WHERE department_id = 110;

 

2) 

 

SELECT first_name 이름, department_id 부서아이디, salary 급여
FROM employees;

 

3) 

 

SELECT first_name 이름, department_id 부서아이디, salary 급여, 
        (SELECT MIN(salary) -- 8300
            FROM employees
            WHERE department_id = 110) 최소급여
FROM employees;

 

4) 이제 모든 부서의 최소급여를 적용하자

 

SELECT first_name 이름, department_id 부서아이디, salary 급여, 
        (SELECT MIN(salary) -- 8300
            FROM employees
            WHERE department_id = 110) 최소급여
FROM employees;

 

□ 인라인 뷰

 

-- FROM 절에 있는 subquery

 

-- Q) 사원의 급여가 자신이 속한 부서의 평균급여보다 많은 사원에 대해
-- 사원명, 급여, 평균급여를 조회출력하시오.

-- (단, from 절에 subquery를 사용하시오)

 

1) 부서별 평균급여 -- a 테이블

 

SELECT department_id 부서, ROUND(AVG(salary)) avgsal
FROM employees -- a 테이블
GROUP BY department_id;

 

2) 기본조인

 

SELECT e.first_name 사원명, e.salary 급여, a.avgsal
FROM employees e, a 
WHERE e.department_id = a.department_id;

 

3) 추가조건

 

SELECT e.first_name 사원명, e.salary 급여, a.avgsal
FROM employees e, a 
WHERE e.department_id = a.department_id
AND e.salary > a.avgsal;

 

4) 답

 

SELECT e.first_name 사원명, e.salary 급여, a.avgsal
FROM employees e, (SELECT department_id, ROUND(AVG(salary)) avgsal
                    FROM employees 
                    GROUP BY department_id) a 
WHERE e.department_id = a.department_id
AND e.salary > a.avgsal;

 

-- Q) 급여를 많이 받는 순으로 직원 이름 급여를 조회출력하시오. (join x, from subquery)

 

평소방법이라면 있는 테이블을 sort한 후 이름, 급여를 출력해야 하겠지만 지금은 테이블을 만들어 가야 한다.

 

1) 내림차순으로 테이블을 생성, 이름 급여 출력

 

-- a 테이블을 만들자

SELECT first_name, salary 
FROM employees 
ORDER BY salary DESC

 

2) a 테이블의 이름, 급여 출력

 

SELECT first_name 이름,  salary 급여
FROM (SELECT first_name, salary 
        FROM employees 
        ORDER BY salary DESC)

 

□ rownum

 

예) 상위 3명만 출력하고자 할 때

 

-- Q) 급여를 가장 많이 받는 상위 7명의 사원 이름 급여를 조회 출력하시오.
-- 단, rownum을 사용하시오.

 

SELECT first_name 이름, salary 급여
FROM employees
WHERE ROWNUM <= 7
ORDER BY salary DESC;

-- from 다음에 where 조건을 먼저 따진 후 sort하기 때문에 정확한 결과가 나오지 않는다.

 

-- 따라서 무조건 서브쿼리가 먼저 들어가야 한다.

-- subquery로 먼저 sort + 순번

 

SELECT first_name 이름, salary 급여
FROM(SELECT first_name, salary
        FROM employees
        ORDER BY salary DESC) -- 이 부분이 바로 소트된 사원테이블
WHERE ROWNUM <= 7;

 

Steven 24000
Neena 17000
Lex 17000
John 14000
Karen 13500
Michael 13000
Nancy 12008

 

-- Q) 급여를 많이 받는 순으로 직원 이름 급여를 조회 출력하시오.
-- (조회된 순서대로 일련번호도 출력하시오)
-- 단, from에 subquery 사용

 

1 Steven 24000
2 Neena 17000
3 Lex 17000
4 John 14000
5 Karen 13500
6 Michael 13000
7 Nancy 12008

 

SELECT ROWNUM, first_name 이름, salary 급여
FROM(SELECT first_name, salary
        FROM employees
        ORDER BY salary DESC) 
WHERE ROWNUM <= 7;

 

-- Q) 커미션을 가장 많이 받는 상위 3명의 이름과 커미션을 조회 출력하시오.
-- (from subquery 사용)

 

방법 1)

 

SELECT first_name 이름, commission_pct 커미션
FROM (SELECT first_name, commission_pct
        FROM employees
        WHERE commission_pct IS NOT NULL --1) null 제외
        ORDER BY commission_pct DESC) -- 2) sort
WHERE ROWNUM <= 3; -- 3) 순번

 

방법 2)

 

SELECT first_name 이름, commission_pct 커미션
FROM (SELECT first_name, commission_pct
        FROM employees
        ORDER BY commission_pct DESC NULLS LAST) -- NULL을 마지막에 정렬시킨다.
WHERE ROWNUM <= 3;

 

-- Q) salary가 제일 많은 직원 한명을 조회 출력하시오.
-- 출력은 이름, 급여, 등수
-- (from subquery, rownum)

 

SELECT first_name 이름, salary 급여, ROWNUM 등수
FROM (SELECT first_name, salary
        FROM employees
        ORDER BY 2 DESC) 
WHERE ROWNUM = 1;

 

보통은 별명을 달아서 많이 쓴다.

 

SELECT A.*, ROWNUM 등수
FROM (SELECT first_name, salary
        FROM employees
        ORDER BY 2 DESC) A
WHERE ROWNUM = 1;

 

-- Q) salary가 제일 많은 직원부터 차례로 출력을 하는데, 등수를 매겨서 조회, 출력하시오.
-- (출력은 이름, 급여, 등수이며 같은 급여 구별은 안 함 즉, 등수는 1,2,3 ... 107)

 

SELECT A.*, ROWNUM 등수
FROM (SELECT first_name, salary
        FROM employees
        ORDER BY 2 DESC) A

 

-- Q) salary가 제일 많은 직원부터 차례로 출력을 하는데 등수를 매겨서 조회 출력하세오.
-- (출력은 이름, 급여, 등수이며 같은 급여는 같은 등수를 매기고 이 경우 출력 순번은 임의대로 한다.)

   

aa 3000 1

bb 3000 1
cc 2700 3

 

SELECT A.*
FROM (SELECT first_name 이름, salary 급여,
        RANK() OVER (ORDER BY salary DESC) 등수
        FROM employees
        ORDER BY 2 DESC) A

 

-- Q) 월별 입사자 수를 조회, 출력하되 입사자수가 가장 적은 상위 8개의 월만 출력하며 소트하여 출력

 

1) group by

 

SELECT  SUBSTR(hire_date, 4, 2) || '월' 입사월, COUNT(*) || '명' 입사자수
FROM employees
GROUP BY SUBSTR(hire_date, 4, 2); -- 입사월

 

2) order by 먼저되고 순번

 

SELECT  SUBSTR(hire_date, 4, 2) || '월' 입사월, COUNT(*) || '명' 입사자수
FROM employees
GROUP BY SUBSTR(hire_date, 4, 2) -- 입사월
ORDER BY COUNT(*) ASC;

 

3) order by 먼저되고 순번

 

SELECT B.*
FROM (SELECT  SUBSTR(hire_date, 4, 2) || '월' 입사월, COUNT(*) || '명' 입사자수
        FROM employees
        GROUP BY SUBSTR(hire_date, 4, 2) -- 입사월
        ORDER BY COUNT(*) ASC) B
WHERE ROWNUM <= 8;

 

-- Q) 'South San Francisco'에 근무하는 직원의 최소급여보다 급여를 많이 받으면서
-- 50번 부서의 평균급여보다 많은 급여를 받는 직원의 이름, 급여, 부서명, 부서id를 조회하시오.

 

SELECT e.first_name, e.salary, d.department_name, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
        AND salary > (SELECT MIN(salary)
                    FROM employees e, departments d, locations l
                    WHERE e.department_id = d.department_id
                        AND d.location_id = l.location_id
                        AND l.city = 'South San Francisco') -- 추가조건
        AND salary > (SELECT AVG(salary)
                    FROM employees
                    WHERE department_id = 50); -- 추가조건

 

-- Q) 사원의 이름, 부서아이디, 급여를 조회출력하시오.
-- 그리고 사원이 속한 해당 부서의 평균급여를
-- 맨 뒤 컬럼으로 추가하여 출력하시오.

 

-- 홍길동 12345 3000 avg(salary) of IT부서

 

-- 일반적으로는

 

SELECT first_name 이름, department_id 부서아이디, salary 급여, avg_salary(평균급여)
FROM employees

 

1) 

 

SELECT ROUND(AVG(salary)) 평균급여
FROM employees;

 

2)

 

SELECT first_name 이름, department_id 부서아이디, salary 급여, avg_salary 평균급여
FROM employees;

 

3)

 

SELECT first_name 이름, department_id 부서아이디, salary 급여,
    (SELECT ROUND(AVG(salary))
        FROM employees
        WHERE department_id = e.department_id) 평균급여
FROM employees e;

 

□ create절의 서브쿼리

 

-- Q) 부서아이디가 80번인 사원의 사원아이디와 급여가 들어있는 테이블을 작성하시오.

 

-- 조건에 맞는 내용을 원본 테이블에서 추출하여 새테이블에 옮겨넣으면 OK

 

1) 

 

SELECT employee_id, salary
FROM employees
WHERE department_id = 80;

 

2) create table 테이블명(...); 후 insert 작업이 필요한데..

 

2-1)

 

CREATE TABLE emp_depid_80
    AS SELECT employee_id, salary
    FROM employees
    WHERE department_id = 80
    
SELECT * FROM emp_depid_80;

 

2-2) alias를 포함시켜 보자

 

CREATE TABLE emp_depid_80_1
    AS SELECT employee_id 사원아이디, salary 급여
    FROM employees
    WHERE department_id = 80
    
SELECT * FROM emp_depid_80_1;

-- 오류발생

 

2-3) alias를 쓰고 싶다면

 

CREATE TABLE emp_depid_80_2(사원아이디, 급여)
    AS SELECT employee_id, salary
    FROM employees
    WHERE department_id = 80;
    
SELECT * FROM emp_depid_80_2;

 

-- 만약 테이블의 틀만 가져오고 데이터는 가져오고 싶지 않다면,

-- 조건절에 참일 수 없는 조건을 입력 (1=2)

 

CREATE TABLE emp_member
AS SELECT * 
   FROM employees
   WHERE 1=2;
   
DESC emp_member;

 

□ having절의 서브쿼리

 

-- Q) 80번 부서의 평균급여보다 평균급여가 작은 부서의
-- 부서 아이디와 해당 부서의 평균급여를 조회 출력하시오.

 

1) 80번 부서의 평균급여

 

SELECT ROUND(AVG(salary))
FROM employees
WHERE department_id = 80; -- 8956

 

2) 

 

SELECT department_id 부서아이디, avg(salary) 해당부서평균급여
FROM employees
GROUP BY department_id

-- 여기에 조건을 줘야 한다.

-- AVG(salary) < 8956, 따라서

 

SELECT department_id 부서아이디, avg(salary) 해당부서평균급여
FROM employees
GROUP BY department_id
HAVING AVG(salary) < (SELECT ROUND(AVG(salary))
                        FROM employees
                        WHERE department_id = 80);

-- 반올림 등 계산을 하다보니 오차가 발생
-- 즉, 8955.882352941176470588235294117647058824와 8956 사이에 오차
-- 그러므로 상식선에서 80번 부서는 빼는 것이 맞다.

 

3) 정답 - 위의 방식처럼 하되 80번 부서아이디는 빼라.

 

SELECT department_id 부서아이디, avg(salary) 해당부서평균급여
FROM employees
WHERE department_id <> 80 -- 80번 부서는 무조건 제외
GROUP BY department_id
HAVING AVG(salary) < (SELECT ROUND(AVG(salary))
                        FROM employees
                        WHERE department_id = 80);

 

-- kimberly도 출력하려면

SELECT department_id 부서아이디, avg(salary) 해당부서평균급여
FROM employees
GROUP BY department_id
HAVING AVG(salary) < (SELECT AVG(salary)
                        FROM employees
                        WHERE department_id = 80);

 

SQL에서 NULL값은 다른 값들과는 다르게 처리된다.

NULL 값은 값 자체가 존재하지 않는 것으로 처리되며, 산술 계산이나 비교 연산에서도 다른 값들과 다르게 처리된다.

따라서 WHERE절에서 department_id가 NULL인 행은 조건에 포함되지 않기 때문에 쿼리 결과에서 나오지 않게 된다.

 

□ insert절의 서브쿼리

 

-- Q) emp_depid_80 테이블에다가 부서아이디가 100인 사원을 insert 하시오.

 

INSERT INTO emp_depid_80 
(SELECT employee_id, salary FROM employees WHERE department_id = 100);

SELECT * FROM emp_depid_80;

 

□ update절의 서브쿼리

 

SELECT * FROM employees
WHERE first_name = 'Alexander';

 

-- Q) Alexander라는 이름의 사원을 Daniel이 근무하는 부서로 부서를 수정(부서이동) 하시오.

 

-- 만일 Dainel이 근무하는 부서가 100번이라면

 

1)

 

UPDATE employees 
SET department_id = 100
WHERE first_name = 'Alexander';

 

2) 100을 구해보면

 

SELECT department_id
FROM employees
WHERE first_name = 'Daniel';

 

3)

 

UPDATE employees 
SET department_id = (SELECT department_id
                        FROM employees
                        WHERE first_name = 'Daniel')
WHERE first_name = 'Alexander';

 

---

 

■ ALL, ANY

 

-- Q) 부서ID가 80인 부서 사원들의 급여(예:14000이 최대라면) 보다
-- 많은 급여를 받는 사원을 조회 출력.

 

1) 부서ID가 80인 부서 사원들의 급여출력

 

SELECT salary FROM employees
WHERE department_id = 80; -- 6100 ~ 14000

 

2) 14000원을 기준으로 한다면

 

SELECT employee_id 사원아이디, first_name 이름, salary 급여
FROM employees
WHERE salary > 14000;

-- 100 Steven 24000
-- 101 Neena 17000
-- 102 Lex 17000

 

SELECT employee_id 사원아이디, first_name 이름, salary 급여
FROM employees
WHERE salary < (SELECT salary 
                    FROM employees
                    WHERE department_id = 80); --> 14000, > 6100~14000

-- 오류발생

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

 

정답) ALL을 사용한다.

 

 

SELECT employee_id 사원아이디, first_name 이름, salary 급여
FROM employees
WHERE salary > ALL (SELECT salary 
                    FROM employees
                    WHERE department_id = 80); -- 최댓값보다 크면
                    -- salary < ALL() --> 컬럼명의 값이 최소값보다 작으면
                    -- salary > ALL() --> 컬럼명의 값이 최댓값보다 크면

 

-- Q) 부서ID가 80인 부서 사원중 급여가 최대인 사원(14000)보다
-- 급여가 같거나 적은 사원 조회 출력
-- (14000과 같거나 적은)

 

 

SELECT employee_id 사원아이디, first_name 이름, salary 급여
FROM employees
WHERE salary <= ANY (SELECT salary 
                    FROM employees
                    WHERE department_id = 80);
-- salary > ANY() 컬럼명의 값이 최소값보다 큰 경우
-- salary < ANY() 컬럼명의 값이 최대값보다 작은 경우