[오라클]하위쿼리, 상관쿼리, 치환변수

- Subquery

Subquery와 JOIN은 서로 바꿔서 작성가능

--SUBQUERY(하위쿼리)

SELECT USERID,COMPANY,DEPT,PAY,AVG(PAY) FROM COMPANY;

 

--COMPANY,DEPT,PAY가 다중값이므로 GROUP BY로 묶을 수 없다

--얻고자 하는 데이터를 위해서 하나의 쿼리로 실행이 돠야 함. 아래의 두 쿼리는 총 2번 실행. 이렇게 사용하면 안됨

SELECT ROUND(AVG(PAY)) FROM COMPANY; --결과 : 1778763

SELECT USERID,COMPANY,DEPT,PAY,1778763 평균 FROM COMPANY; --평균이 변동되면 잘못된 데이터가 됨

 

--데이터가 바뀌어도 평균에 반영되므로 문제 없음.

--SUBQUERY는 SELECT만 가능

SELECT USERID,COMPANY,DEPT,PAY,

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

FROM COMPANY;

SELECT USERID,COMPANY,DEPT,PAY,

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

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

FROM COMPANY;

 

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

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

 

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

SELECT AVG(AGE) FROM CUSTOM; --결과 : 28

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(PAY) FROM COMPANY);

 

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

SELECT * FROM CUSTOM

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

 

--현대자동차에 근무하는 유저 아이디(15명)

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

 

--서브쿼리는 JOIN문으로 서로 변경가능

SELECT *

FROM CUSTOM A INNER JOIN COMPANY B

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

 

--4회 이상 물건을 구매한 고객의 ID

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

 

--4회 이상 물건을 구매하지 않은 고객의 정보

SELECT * FROM CUSTOM

 

WHERE USERID NOT IN (SELECT USERID FROM SALES GROUP BY USERID HAVING COUNT(*) >= 4) ;

- INLINE-VIEW

FROM 절에 사용되는 서브 쿼리. SELECT * FROM (SELECT 컬럼명 FROM 테이블명)

--INLINE-VIEW

--조인문에서는 NCOUNT열도 불러올 수 있음

SELECT A.*,NCOUNT

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

ON A.USERID = B.USERID;

 

- ANY(범위O), ALL(범위X) 최소값~최대값

ANY는 범위값이 포함되어 있어야 함. 최대값보다 작을 수 밖에 없음

ALL은 범위값이 포함되어 있으면 안됨

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

 

NOT IN과 <>ALL은 같은 결과를 출력

 

--ANY, ALL

--MAX와 MIN을 쓰는거보다 속도가 훨씬 빠르므로 사용함

SELECT POINT FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT;

 

--제주도에 사는 사람중 가장 큰 포인트, 가장 작은 포인트

SELECT MIN(POINT) FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT; --132

SELECT MAX(POINT) FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT; --269

 

-- >ANY(최소값보다 큰 데이터) 132

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

 

-- <ANY(최대값보다 작은 데이터) 269

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

 

-- >ALL(최대값보다 큰 데이터) 269

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

 

-- <ALL(최소값보다 작은 데이터) 132

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

SELECT USERID,USERNAME,POINT FROM CUSTOM

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

 

SELECT POINT FROM CUSTOM WHERE AGE >=70; --결과 없음

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

SELECT * FROM CUSTOM WHERE POINT >ANY(SELECT 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은 같은 결과를 출력

SELECT * FROM CUSTOM

WHERE USERID NOT IN

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

SELECT * FROM CUSTOM

WHERE USERID <>ANY

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

SELECT * FROM CUSTOM

WHERE USERID <>ALL

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

 

---

 

 

 

- 하위쿼리와 상관쿼리 비교

EXISTS, NOT EXIST

--하위쿼리

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

SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%'; --O

--상관쿼리(WHERE뒤에 컬럼명이 나오지않고 EXISTS나옴)

--상관쿼리는 하위쿼리와는 다르게 나누어서 실행되지 않음.

--속도가 빠르기 때문에 사용함.

SELECT * FROM CUSTOM

WHERE EXISTS (SELECT * FROM COMPANY WHERE COMPANY.USERID = CUSTOM.USERID AND COMPANY LIKE '현대자동차%');

SELECT * FROM COMPANY WHERE COMPANY.USERID = CUSTOM.USERID AND COMPANY LIKE '현대자동차%'; --X

--NOT EXIST

SELECT * FROM CUSTOM

WHERE NOT EXISTS (SELECT * FROM COMPANY WHERE COMPANY.USERID = CUSTOM.USERID AND COMPANY LIKE '현대자동차%');

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

SELECT * FROM (SELECT * FROM CUSTOM WHERE ADDR1='경기도') K WHERE AGE <= 20;

SELECT * FROM CUSTOM WHERE ADDR1='경기도' AND AGE <= 20;

 

SUBQUERY-PAIRWISE : 동시에 비교, NON-PAIRWISE : 분해작업이 된 개념

 

ROWNUM(일렬 번호)

--ROWNUM(일렬번호) : 실제로 저장되는 데이터가 아님. 중간에 데이터가 지워지더라도 상관없음. 존재하는 데이터에만 번호붙음

SELECT ROWNUM,USERID,USERNAME FROM CUSTOM

WHERE ROWNUM <5;--ROWNUM은 실제 존재하는 데이터가 아니지만 연산작업이 가능함. 단, 1부터 시작해야 함.

 

--3~7에 해당하는 데이터를 사용하려면 하위쿼리로 묶어줘야 함

SELECT * FROM (

SELECT ROWNUM RNUM, DATA.* FROM

(SELECT USERID,USERNAME FROM CUSTOM) DATA)

WHERE RNUM>=3 AND RNUM<=7;

 

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

--하나의 쿼리안에 하위쿼리가 3개 들어있음.

SELECT * FROM CUSTOM

WHERE USERID IN (SELECT USERID FROM COMPANY

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

 

 

- PRIMARY KEY

DNO에 기본키(PRIMARY KEY)를 준것 - NULL이 와선 안됨.

중복값을 허용하지 않음. 테이블당 오로지 한개만 가능(제약조건 존재)

무결성 결함이 없다

.

 

- 치환변수를 사용한 데이터 조회 (CMD 이용)

1. 계정로그인

2. DIV 설정 후 데이터 조회

3.  / : 버퍼 통해 전에 코드 불러옴

4.  DIV 설정 후 데이터 재조회

5. @DIV_DNO SQL 파일 생성

6. @DIV_DNO 파일 불러와서 실행

치환변수를 사용한 데이터 조회 (CMD 이용)

 

 B_NO에 10이라는 숫자가 저장

 

UNDEFINE COL_NAME; 초기화 << 버퍼에 이 줄이 저장되는 게 아니라 치환변수 설정한 것이 초기화됨

 

SAVE - SQL파일 생성

SET VERIFY OFF :  이 변수의 값의 변동전/후 보여주는게 사라짐

 

 

ED : 버퍼에 저장된 쿼리 조회

 

 

 

 

 

--SUBQUERY
--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); --PAY,BONUS 둘을 비교함

--NON-PAIRWISE. 분해작업이 된 개념. 하나씩 AND 조건으로 넣어준것 
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);

--DML(INSERT, UPDATE, DELETE)
SELECT * FROM DIVISION;
DESC DIVISION;

--제약조건 조회. 데이터베이스가 가지고있는 테이블명 - USER_CONSTRAINTS. 
SELECT * FROM USER_CONSTRAINTS;

INSERT INTO DIVISION(DNO,DNAME,PHONE,POSITION) VALUES (50,'OPERATION','031-123-1234','DAEGU'); 
--컬럼에 맞춰서 데이터 입력
--데이터가 어느 컬럼에 들어가는지 정확하게 볼 수 있도록 다 기재해주는 편.
--두번 실행시 오류메세지: 무결성 제약 조건(KIM.DIVISION_DNO_PK)에 위배됩니다.
--DIVISION_DNO_PK : 중복을 허용하지 않는데 중복값을 입력함

INSERT INTO DIVISION(DNO) VALUES (60);--컬럼에 해당하는 전체 데이터가 없을경우 컬럼명을 지정해줘야함

INSERT INTO DIVISION(DNAME,POSITION) VALUES ('ACCOUNT','DAEJEON');--X
--ORA-01400: NULL을 ("KIM"."DIVISION"."DNO") 안에 삽입할 수 없습니다 

INSERT INTO PERSONNEL(PNO,PNAME,PAY,DNO) VALUES (7711,'YOUNG',4000,20);

SELECT * FROM PERSONNEL;

--명시적 NULL 삽입(특정 컬럼에 NULL 입력)
INSERT INTO DIVISION VALUES (70,'AAA','123',NULL); 
INSERT INTO DIVISION VALUES (80,'BBB','123',''); 
SELECT * FROM DIVISION;

INSERT INTO PERSONNEL (PNO,PNAME,JOB,STARTDATE,DNO)
VALUES (1204,'YOU','SALESMAN',SYSDATE,10);

SELECT PNO,PNAME,JOB,TO_CHAR(STARTDATE, 'YYYY-MM-DD') STARTDATE,DNO FROM PERSONNEL;

 

--치환변수(UPDATE, DELETE에서도 사용가능. 오라클에서 사용)
--쿼리박스에선 인식안됨, 커맨드창 이용
SELECT PNO,PNAME,JOB,STARTDATE,DNO FROM PERSONNEL WHERE DNO=&DIV_DNO;
CREATE TABLE MANAGER
AS 
	SELECT * FROM PERSONNEL
	WHERE 1=2;-- PERSONNEL테이블의 틀만 복사. 데이터는 조건식이 거짓이므로 가져오지 않음

CREATE TABLE BUSEO
AS SELECT * FROM DIVISION;

CREATE TABLE SAWON
AS SELECT * FROM PERSONNEL;

CREATE TABLE EXAM1
AS SELECT * FROM PERSONNEL;

CREATE TABLE EXAM2
AS SELECT * FROM DIVISION;

--&뒤의 내용은 사용자가 정의하는 이름.말그래도 변수명. 내마음대로 설정가능
INSERT INTO BUSEO (DNO,DNAME,PHONE,POSITION)
VALUES(&B_NO,'&B_NAME','&B_PHONE','&B_POSIT');

--&&
SELECT PNO,PNAME,JOB,&&COL_NAME --&&COL_NAME 한번 만들면 고정
FROM PERSONNEL 
ORDER BY &ORDER_COL;--&ORDER_COL 실행할때마다 설정

--초기화 << 버퍼에 이 줄이 저장되는 게 아니라 치환변수 설정해논게 초기화됨
UNDEFINE COL_NAME;

--ACCEPT
ACCEPT B_NO PROMPT '부서번호를 입력하세요:' ;--값 입력시 B_NO에 저장

--SUBQUERY 사용해서 INSERT
--테이블이 이미 생성된 이후이므로 AS 쓰면 안됨
INSERT INTO MANAGER(PNO,PNAME,PAY,STARTDATE)  
SELECT PNO,PNAME,PAY,STARTDATE FROM PERSONNEL
WHERE JOB='MANAGER';

--하위쿼리를 이용해서 데이터를 INSERT하는 방법
INSERT INTO SALESMAN
SELECT * FROM PERSONNEL WHERE JOB='SALESMAN';