Oracle 5일차(1)-하위쿼리(SUBQUERY),ANY/ALL, 상관쿼리, ROWNUM, INSERT

1.하위쿼리(SUBQUERY)

SUBQUERY는 SELECT만 가능

SELECT USERID,COMPANY,DEPT,PAY,

(SELECT ROUND(AVG(PAY)) FROM COMPANY) 평균 ▶ SUBQUERY

FROM COMPANY;

SELECT USERID,COMPANY,DEPT,PAY,

(SELECT ROUND(AVG(PAY)) FROM COMPANY) 평균 , ▶ SUBQUERY

(PAY-(SELECT ROUND(AVG(PAY)) FROM COMPANY)) 차액 ▶ SUBQUERY

FROM COMPANY;

평균 나이보다 나이가 많은 직원의 개인 정보

SELECT USERID,USERNAME,JUMIN,AGE,TEL,JOB,SCHOL,POINT

FROM CUSTOM

WHERE AGE> (SELECT ROUND(AVG(AGE)) FROM CUSTOM); ▶ SUBQUERY

2번 실행된 것. 이렇게 짜면 안됨

SELECT AVG(AGE) FROM CUSTOM;

SELECT USERID, USERNAME,AGE,ADDR1 FROM CUSTOM

WHERE AGE > 28;

▶SUBQUERY로 변경

SELECT USERID, USERNAME,AGE,ADDR1 FROM CUSTOM

WHERE AGE > (SELECT AVG(AGE) FROM CUSTOM);

평균월급보다 적은월급을 받는 사원의 정보

SELECT * FROM COMPANY;

WHERE PAY < (SELECT  AVG(AGE) FROM COMPANY);

현대자동차에 근무하는 사원의 고객정보를 검색

SELECT * FROM CUSTOM

WHERE USERID IN (SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%');

▶ IN : 하위쿼리 안에 존재하냐

JOIN : 하위퀴리는 JOIN으로 변경가능

SELECT A.*

FROM CUSTOM A, COMPANY B

WHERE A.USERID=B.USERID AND COMPANY LIKE '현대자동차%';

4회 이상 물건을 구입한 고객의 정보

SELECT USERID, COUNT(*) FROM SALES

GROUP BY USERID

HAVING COUNT(*)>=4;

▶JOIN

SELECT A.*, NCOUNT

FROM CUSTOM A ,

(SELECT USERID, COUNT(*) NCOUNT FROM SALES GROUP BY USERID HAVING COUNT(*)>=4) B

WHERE A.USERID=B.USERID;

▶하위쿼리

SELECT * FROM CUSTOM

WHERE USERID IN(

SELECT USERID FROM SALES GROUP BY USERID

HAVING COUNT(*)>=4);

▲두 개는 같은 결과

PAIRWISE : 두 개의 컬럼을 묶어서 이 한 세트를 비교하여 같은 경우만 참으로 보고 조회

SELECT PNAME,DNO,PAY,NVL(BONUS,-1)

FROM PERSONNEL

WHERE (PAY, NVL(BONUS,-1)) IN (SELECT PAY,NVL(BONUS,-1) FROM PERSONNEL WHERE DNO=30);

NON-PAIRWISE : 컬럼별로 따로 비교한다

SELECT PNAME,DNO,PAY, NVL(BONUS,-1)

FROM PERSONNEL

WHERE PAY IN (SELECT PAY FROM PERSONNEL WHERE DNO=30)

AND NVL(BONUS,-1) IN (SELECT NVL(BONUS,-1) FROM PERSONNEL WHERE DNO=30);

           

▲PAIRWISE 결과    ▲NON-PAIRWISE 결과



2. ANY / ALL

▶ANY/ALL이 MAX/MIN보다 더 빠름

▶ANY : 범위값이 포함O

▶ALL :  범위값이 포함X

>ANY(최소값보다 큰)

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT > ANY(SELECT POINT FROM CUSTOM WHERE ADDR1='제주도')

ORDER BY POINT;

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT > (SELECT MIN(POINT) FROM CUSTOM WHERE ADDR1='제주도') ▶하위쿼리 MIN 사용

ORDER BY POINT;

▲두 개는 같은 결과

<ANY(최대값보다 작은)

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT < ANY(SELECT POINT FROM CUSTOM WHERE ADDR1='제주도')

ORDER BY POINT;

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT < (SELECT MAX(POINT) FROM CUSTOM WHERE ADDR1='제주도') ▶하위쿼리 MAX 사용

ORDER BY POINT;

▲두 개는 같은 결과

>ALL(최대값보다 큰)

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT > ALL(SELECT POINT FROM CUSTOM WHERE ADDR1='제주도')

ORDER BY POINT;

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT > (SELECT MAX(POINT) FROM CUSTOM WHERE ADDR1='제주도') ▶하위쿼리 MAX 사용

ORDER BY POINT;

▲두 개는 같은 결과

<ALL(최소값보다 작은)

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT < ALL(SELECT POINT FROM CUSTOM WHERE ADDR1='제주도')

ORDER BY POINT;

SELECT USERID,USERNAME,POINT FROM CUSTOM

WHERE POINT < (SELECT MIN(POINT) FROM CUSTOM WHERE ADDR1='제주도') ▶하위쿼리 MIN 사용

ORDER BY POINT;

▲두 개는 같은 결과 : 값이 없음

>ANY : 하위쿼리의 결과가 없으면 전체쿼리의 결과도 없다

SELECT * FROM CUSTOM

WHERE POINT > ANY(SELECT CT POINT FROM CUSTOM WHERE AGE>=70);

>ALL은 하위쿼리의 결과가 없으면 모든데이터를 검색한다.

SELECT * FROM CUSTOM

WHERE POINT > ALL(SELECT  POINT FROM CUSTOM WHERE AGE>=70);

IN과 =ANY는 값은 결과를 출력

SELECT * FROM CUSTOM

WHERE USERID IN (SELECT  USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%');

SELECT * FROM CUSTOM

WHERE USERID =ANY (SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%');

▲두 개는 같은 결과

NOT IN과 <>ALL이 값은 결과를 출력

※<>ANY가 X!

SELECT * FROM CUSTOM

WHERE USERID NOT IN (SELECT  USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%');

SELECT * FROM CUSTOM

WHERE USERID <>ANY (SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%');

▲두 개는 같은 결과

3.상관쿼리

▶하위쿼리

○ WHERE절의 컬럼명과 하위쿼리 WHERE절의 컬럼명이 일치

○ 하위쿼리를 따로 실행 가능

SELECT * FROM CUSTOM

WHERE USERID IN (SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%');

▶상관쿼리

○ 상관쿼리를 따로 실행 불가능

EXISTS

SELECT * FROM CUSTOM

WHERE EXISTS (SELECT * FROM COMPANY

WHERE USERID=CUSTOM.USERID AND COMPANY LIKE '현대자동차%');

▲두 개는 같은 결과

NOT EXISTS

SELECT * FROM CUSTOM

WHERE NOT EXISTS (SELECT * FROM COMPANY

WHERE USERID=CUSTOM.USERID AND COMPANY LIKE '현대자동차%');

20살 이하의 경기도에 사는 사람

SELECT * FROM (SELECT * FROM CUSTOM WHERE ADDR1='경기도') K

WHERE AGE<=20;

SELECT * FROM CUSTOM

WHERE ADDR1='경기도' AND AGE<=20;

4.ROWNUM(일련번호) : 실제 존재하는 데이터X

SELECT ROWNUM,USERID,USERNAME FROM CUSTOM

WHERE ROWNUM<=5;

3번에서 7번까지 출력

SELECT ROWNUM,USERID,USERNAME FROM CUSTOM

WHERE ROWNUM>=3 AND ROWNUM<=7; → 실행X

▼변경

SELECT * FROM(

SELECT ROWNUM RNUM, DATA.* FROM  

(SELECT USERID,USERNAME FROM CUSTOM) DATA)

WHERE RNUM>=3 AND RNUM<=7;

평균월급보다 많은 월급을 받는 사람들의 고객정보를 검색

▶JOIN

SELECT A.*, PAY

FROM CUSTOM A, COMPANY B

WHERE A.USERID=B.USERID AND PAY>(SELECT AVG(PAY) FROM COMPANY);

▶SUBQUERY

SELECT * FROM CUSTOM

WHERE USERID IN

(SELECT USERID FROM COMPANY WHERE PAY>(SELECT AVG(PAY) FROM COMPANY));

▲두 개는 같은 결과

5.DML(INSERT,UPDATE,DELETE)

DNO (NOT NULL)

▶ PRIMARY KEY : 중복X, NULL값X, 테이블 당 하나만 가능

INSERT

INSERT INTO 테이블명 VALUES (VALUE1, VALUE2, VALUE3, ... );

INSERT INTO 테이블명 (COLUMN1,COLUMN2,COLUMN3, ...) VALUES (VALUE1, VALUE2, VALUE3, ... );

INSERT INTO DIVISION VALUES (60); ▶실행X. 무결성 제약 조건에 위배됩니다 : 60을 어디에 넣는지 모름

INSERT INTO DIVISION (DNO) VALUES (60); ▶실행O, 나머지 값은 NULL

명시적 / 암시적 NULL삽입

INSERT INTO DIVISION VALUES (70,'AAA','123',NULL);

INSERT INTO DIVISION VALUES (80,'BBB','123',''); ▶명시적

INSERT INTO DIVISION (DNO) VALUES (60); ▶암시적

SUBQUERY를 사용한 INSERT

▶INSERT INTO TABLE명 SELECT문

▶VALUES 필요X

INSERT INTO MANAGER (PNO,PNAME,PAY,STARTDATE)

SELECT PNO,PNAME,PAY,STARTDATE FROM PERSONNEL WHERE JOB='MANAGER';

INSERT INTO SALESMAN

SELECT * FROM PERSONNEL WHERE JOB='SALESMAN';

6.치환변수(ORACLE에만)

&

SELECT PNO,PNAME,JOB,STARTDATE,DNO

FROM PERSONNEL

WHERE DNO=&DIV_DNO; ▶여기서는 실행X. CMD에서 실행 가능

&&

SELECT PNO,PNAME,JOB,&&COL_NAME ▶&&는 한 번만 설정하면 고정 / &는 실행 할 때마다

FROM PERSONNEL

UNDEFINE COL_NAME; ▶&&값 초기화

ACCEPT : ACCEPT만 가지고는 사용 불가

▶ACCEPT 문장을 보여줌

ACCEPT B_NO PROMPT '부서 번호를 입력하세요: ';