# 다중행 서브쿼리
- 여러 행을 반환하는 서브쿼리
- 다중 행 연산자(in, any, all)을 사용한다.
* IN : 조회된 결과들 중에서 임의의 하나와 일치하는
* ANY : 조회된 결과의 각 값과 비교. 이중 하나라도 조건이 만족하면
SALARY < ANY(100, 200, 300) 최대값보다 작은 것
SALARY > ANY(100, 200, 300) 최소값보다 큰것. 세 조건들중 아무거나 하나보다 크면 찾아진다.
SALARY = ANY(100, 200. 300) IN과 동일
* ALL : 조회된 결과의 모든 값의 비교
SALARY < ALL(100, 200, 300) 최소값보다 작은 것. 모든 값보다 작은 것. 100보다 작은것
SALARY > ALL(100, 200, 300) 최대값보다 큰 것. 모든 값보다 큰것. 300보다 큰것
* 다중 컬럼 서브쿼리
SELECT NAME, GRADE, HEIGHT
FROM STUDENT
WHERE (GRADE, HEIGHT) = (SELECT 컬럼/표현식, 컬럼/표현식 FROM STUDENT);
- 서브쿼리의 값이 2개가 온다.
SELECT NAME, GRADE, HEIGHT
FROM STUDENT
WHERE (GRADE, HEIGHT) IN (SELECT 컬럼/표현식, 컬럼/표현식 FROM STUDENT);
-- 다중행 서브쿼리
-- 각 부서별 최저 급여 표시하기
SELECT MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY IN (6900, 2500, 7000);
-- 부서별 최저급여와 그 급여와 같은 사원의 이름, 부서번호, 급여 출력
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
-- IN 연산자는 결과값이 하나 이상 나오는 경우 사용가능하다
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEES);
-- 다중행 서브쿼리
-- ALL, ANY보다는 IN 이 많이 쓰인다.
-- ANY는 위험한 연산자
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL(15000, 20000);
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY(15000, 20000);
-- 다중 컬럼 서브쿼리
-- 비교해야하는 값이 2개
-- 각 학년별 최고 키
SELECT GRADE, MAX(HEIGHT)
FROM STUDENT
GROUP BY GRADE;
-- 각 학년별 최고 키를 가진 학생의 이름과 학년, 키를 표시
-- 학년과 키를 같이 비교
SELECT NAME, GRADE, HEIGHT
FROM STUDENT
WHERE (GRADE, HEIGHT) IN (SELECT GRADE, MAX(HEIGHT) FROM STUDENT GROUP BY GRADE);
* 스칼라 서브쿼리
-- 부서별 인원수 계산하기
-- SELECT 절에도 서브쿼리가 가능하다
SELECT B.DEPARTMENT_ID, (SELECT COUNT(*) FROM EMPLOYEES A WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) CNT
FROM DEPARTMENTS B;
-- King 사원의 사원번호, 이름, 부서아이디, 부서명을 표시
-- 1) JOIN 경우
SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.LAST_NAME = 'King';
-- 2) 서브쿼리 사용
-- JOIN이 많을 수록 성능이 저하 -> 스칼라 서브쿼리
-- 조인 없이 특정 테이블의 값을 가져오는 방법 -> 값을 가져오는 쿼리를 SELECT문에 적으면 된다.
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.DEPARTMENT_ID,
(SELECT D.DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) 부서명
FROM EMPLOYEES E
WHERE E.LAST_NAME = 'King';
-- 부서별 사원수를 스칼라 서브쿼리로 구하기
SELECT A.DEPARTMENT_ID,
(SELECT COUNT(*)
FROM EMPLOYEES B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID) 사원수
FROM DEPARTMENTS A;
-- 고객의 포인트가 50만점을 넘을 경우 산악자전거를 선물로 제공
-- 고객의 포인트가 50만점 이하일 떄는 샴푸세트를 제공
-- 고객번호, 고객이름, 포인트, 선물
-- 이런 쿼리가 있다 정도만 파악
-- 2) 포인트 추가하기
SELECT GNO, GNAME, POINT,
CASE
WHEN POINT > 500000
THEN
(SELECT GNAME
FROM GIFT WHERE POINT + 200000 > G_START
AND POINT + 200000 <= G_END) -- 2) GIFT의 POINT에다 20만점 점수 추가
WHEN POINT <= 500000
THEN
(SELECT GNAME
FROM GIFT WHERE POINT + 100000 > G_START
AND POINT + 100000 <= G_END)
END AS 경품
FROM GOGAK;
* VIEW
- 가상의 테이블
- 쿼리를 (SELECT)실행하면 얻어지는 결과는 항상 테이블의 형태다.
=> 얻어진 결과(테이블)를 갖고 값을 읽어 올 수 있다.
- 부서번호별 사원수로 얻어진 결과를 FROM 절의 가상테이블로 지정하여 쿼리문 작성
* 시퀀스(SEQUENCE)
- 연속적인 인련번호를 만들어주는 기능
- 무조건 앞의 번호보다 큰 값이 나온다
- 오라클에서 제공하는 객체
- 예) 은행의 번호표발행기기 같은 역할
- 캐쉬 : 성능(속도)향상을 위해 자주 사용될 것같은 것을 미리 저장해놓는 기능
- 기본적으로 시퀀스를 20개를 만들어 놓는다
- 중복되는 번호를 없게 만들기 위해 사용하는 것
=> 그 행을 대표하는 값이 될 수 있다
CREATE SEQUENCE 시퀀스명
INCREAMENT BY 1
START WITH 1
NOCACHE;
-- BOOK_NO에 일련번호 부여
INSERT INTO SAMPLE_BOOK
(BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER,
BOOK_PRICE, BOOK_PUBDATE, BOOK_SUMMARY)
VALUES(MY_SEQ.NEXTVAL, '어린왕자', '쌩떽쥐베리','한성출판사', 10000, SYSDATE, '어른 동화책');
-- ROWNUM 행에다가 숫자를 붙이는 것
SELECT ROWNUM, BOOK_NO, BOOK_TITLE
FROM SAMPLE_BOOK;
-- 정렬된 순서에도 번호를 매길수 있다.
-- 페이징 처리에 활용
=> 예를 들어 주문번호들 중에서 몇개가 빠져있을수 있기 때문에 정렬후 새로운 번호를 부여하여 값을 처리
SELECT ROW_NUMBER() OVER(ORDER BY BOOK_NO DESC) 일련번호, BOOK_NO, BOOK_TITLE
FROM SAMPLE_BOOK;
-- 다중행 서브쿼리
-- 각 부서별 최저 급여 표시하기
SELECT MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY IN (6900, 2500, 7000);
-- 부서별 최저급여와 그 급여와 같은 사원의 이름, 부서번호, 급여 출력
SELECT E.LAST_NAME, D.DEPARTMENT_ID, E.SALARY
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE E.SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
-- IN 연산자는 결과값이 하나 이상 나오는 경우 사용가능하다
-- 다중행 서브쿼리
-- ALL, ANY보다는 IN 이 많이 쓰인다.
-- ANY는 위험한 연산자
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL(15000, 20000);
SELECT LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY(15000, 20000);
-- 다중 컬럼 서브쿼리
-- 비교해야하는 값이 2개
-- 각 학년별 최고 키
SELECT GRADE, MAX(HEIGHT)
FROM STUDENT
GROUP BY GRADE;
-- 각 학년별 최고 키를 가진 학생의 이름과 학년, 키를 표시
-- 학년과 키를 같이 비교
SELECT NAME, GRADE, HEIGHT
FROM STUDENT
WHERE (GRADE, HEIGHT) IN (SELECT GRADE, MAX(HEIGHT) FROM STUDENT GROUP BY GRADE);
-- 부서별 인원수 계산하기
-- SELECT 절에도 서브쿼리가 가능하다(스칼라 서브쿼리)
--조인을 많이해야할때 스칼라서브 쿼리를 이용한다.
SELECT B.DEPARTMENT_ID, (SELECT COUNT(*) FROM EMPLOYEES A WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) CNT
FROM DEPARTMENTS B;
-- King 사원의 사원번호, 이름, 부서아이디, 부서명을 표시
-- 1) JOIN 경우
SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.LAST_NAME = 'King';
-- 2) 서브쿼리 사용
-- JOIN이 많을 수록 성능이 저하 -> 스칼라 서브쿼리
-- 조인 없이 특정 테이블의 값을 가져오는 방법 -> 값을 가져오는 쿼리를 SELECT문에 적으면 된다.
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.DEPARTMENT_ID,
(SELECT D.DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) 부서명
FROM EMPLOYEES E
WHERE E.LAST_NAME = 'King';
-- 부서별 사원수를 스칼라 서브쿼리로 구하기
SELECT A.DEPARTMENT_ID,
(SELECT COUNT(*)
FROM EMPLOYEES B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID) 사원수
FROM DEPARTMENTS A;
-- 고객의 포인트가 50만점을 넘을 경우 산악자전거를 선물로 제공
-- 고객의 포인트가 50만점 이하일 떄는 샴푸세트를 제공
-- 고객번호, 고객이름, 포인트, 선물
-- 이런 쿼리가 있다 정도만 파악
-- 2) 포인트 추가하기
SELECT GNO, GNAME, POINT,
CASE
WHEN POINT > 500000
THEN
(SELECT GNAME
FROM GIFT WHERE POINT + 200000 > G_START
AND POINT + 200000 <= G_END) -- 2) GIFT의 POINT에다 20만점 점수 추가
WHEN POINT <= 500000
THEN
(SELECT GNAME
FROM GIFT WHERE POINT + 100000 > G_START
AND POINT + 100000 <= G_END)
END AS 경품
FROM GOGAK;
-- VIEW (가상의 테이블)
-- SELECT의 실행 결과를 가상의 테이블로 FROM 절에 넣어 쿼리문 작성
SELECT DEPARTMENT_ID, CNT
FROM (SELECT DEPARTMENT_ID, COUNT(*) CNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)
WHERE CNT>30;
-- 시퀀스(SEQUENCE)
-- 시퀀스에서 새로운 일련번호 발행함기
SELECT MY_SEQ.CURRVAL
FROM DUAL;
-- BOOK_NO에 일련번호 부여
INSERT INTO SAMPLE_BOOK
(BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER,
BOOK_PRICE, BOOK_PUBDATE, BOOK_SUMMARY)
VALUES(MY_SEQ.NEXTVAL, '어린왕자', '쌩떽쥐베리','한성출판사', 10000, SYSDATE, '어른 동화책');
-- ROWNUM 행에다가 숫자를 붙이는 것
SELECT ROWNUM, BOOK_NO, BOOK_TITLE
FROM SAMPLE_BOOK;
-- 정렬된 순서에도 번호를 매길수 있다.
-- 페이징 처리에 활용
SELECT ROW_NUMBER() OVER(ORDER BY BOOK_NO DESC) 일련번호, BOOK_NO, BOOK_TITLE
FROM SAMPLE_BOOK;
-- 많이 사용되는 방법
SELECT RN, BOOK_NO, BOOK_TITLE
FROM (SELECT ROW_NUMBER() OVER(ORDER BY BOOK_NO DESC) RN, BOOK_NO, BOOK_TITLE FROM SAMPLE_BOOK)
WHERE RN >= 1 AND RN <= 3;
-- 1. Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일 표시.
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE last_name = 'Zlotkey';
SELECT last_name, HIRE_DATE
FROM EMPLOYEES
WHERE department_id = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE last_name = 'Zlotkey');
-- 2. 급여가 평균 급여보다 많은 모든 사원의 사원번호와 이름을 표시.
SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT avg(SALARY)
FROM EMPLOYEES)
ORDER BY SALARY ASC;
-- 3. 이름에 u가 포함된 사원과 같은 부서에 일하는 모든 사원의 사원번호와 이름 표시.
SELECT DEPARTMENT_ID
from EMPLOYEES
where LAST_NAME like '%u%';
SELECT employee_id, last_name
fROM EMPLOYEES
where DEPARTMENT_ID in (SELECT DEPARTMENT_ID
from EMPLOYEES
where LAST_NAME like '%u%');
-- 4. 부서 위치 아이디가 1800인 모든 사원의 이름, 부서번호, 업무아디 표시.
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOCATION_ID = 1800;
SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
where DEPARTMENT_ID in(SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOCATION_ID = 1800);
SELECT E.LAST_NAME, D.DEPARTMENT_ID, E.JOB_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = 1800;
-- 5. King가 관리하는 모든 사원의 이름과 급여 표시
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'King'
and MANAGER_ID is null;
SELECT LAST_NAME, SALARY, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID in (SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'King');
-- 6. Executive 부서의 모든 사원에 대한 이름과 업무이름 표시.
SELECT e.LAST_NAME, j.JOB_ID, J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID
and DEPARTMENT_ID in (SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Executive');
DML
-insert, update, delete
DDL(Data definition language)
- 오라클의 객체(테이블, 뷰, 시퀀스, 인덱스, 동의어)
- CREATE(생성), ALTER(수정), DROP(삭제), TRUNCATE(비우기)
테이블
- 테이블 이름은, 문자 숫자 특문(_ $ #) 사용 가능
- 문자로 시작해야함
- CREATE TABLE 테이블명 (
칼럼명 데이터 타입 DEFALULT 기본값.
컬럼명 데이터 타입
)
오라클의 데이터 타입
- VARCHAR2(size) - 가변 길이 문자 데이터, 최대 4000byte
이름, 제목, 주소, 리뷰... 같은 글
- CHAR(size) - 고정 길이 문자 데이터, 최대 2000byte
주민번, 우편번호, 군번, 학번... 같은..
- DATE - 날짜와 시간정보 (년, 월, 일, 시, 분, 초)
- TIMESTAMP - 날짜와 시간정보 (년, 월, 일, 시, 분, 초, 소숫점 이하의 초)
- NUMBER(p,5) - 숫자(p:십진자리수, s:소수점 자리수)
- LONG - 가변길이 문자 데이터 2GB
- CLOB - 가변길이 문자 데이터 4GB
- BLOB - 이진데이터(사진, 영상, 기타..) 4GB
예제 테이블 만들기.
방명록 테이블
- 작성자, 작성일자, 열람횟수, 내용
ALTER TABLE
- 컬럼 추가, 컬럼 수정, 컬럼 삭제
- 컬럼 추가
ALTER TABLE 테이블명
ADD (컬럼명 데이터 타입);
- 컬럼 수정 < 데이터타입, 크기, 기본값 등을 변경할 수 있다.
ALTER TABLE 테이블명
MODIFY (컬럼명 데이터타입)
- 컬럼 삭제
ALTER TABLE 테이블명
DROP COLUMN 컬럼명;
Create Table BAND_Article (
NO NUMBER PRIMARY KEY
Writer varchar2(500) NOT NULL,
Write_DATE DATE default sysdate,
Read_cnt NUMBER(10) default 0,
contents varchar2(4000)
)
-- 테이블 데이터 삭제
DELETE FROM BAND_ARTICLE;
ㄴ롤백으로 되돌리기 가능.
TRUNCATE TABLE BAND_ARTICLE;
ㄴ 롤백으로도 불가능.
ㄴ 테이블 모든 행을 제거하고, 테이블이 사용했던 저장공간을 해제.
ㄴ DELETE보다 처리 속도가 빠름.
ㄴ 해당 테이블 초기화
-- 테이블 삭제
DROP TABLE BAND_ARTICLE;
