DB /day18 / 23.09.15(금) / (핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축
DAY04
05_서브쿼리.SQL
-- # 05_서브쿼리.SQL
/*
# 서브 쿼리 ( SUBQUERY)
- 하나의 SELECT 문장 안에 또 하나의 SELECT 문을 사용
- 비교 연산의 오른쪽에 작성, 반드시 괄호()로 묶어서 사용
- 메인 쿼리가 실행되기 전에 한 번만 실행
- 하나의 테이블에서 검색한 결과를 다른 테이블에 전달하여 새로운 결과를 검색
*/
-- S1
SELECT DEPTNO
FROM EMP
WHERE ENAME = 'SMITH';
-- S2
SELECT DEPTNO
FROM EMP
WHERE DEPTNO = 20;
-- 서브쿼리(S1) 의 실행 결과를 메인쿼리(S2) 에게 반환
-- 서브쿼리는 메인쿼리가 필요한 값을 제공
-- SMITH 사원 부서명
SELECT DNAME
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME = 'SMITH');
-- SMITH 와 같은 부서에 근무하는 사원의 이름과 부서번호
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME = 'SMITH')
AND ENAME != 'SMITH';
-- 서브쿼리에서 그룹 함수 사용
-- 평균 급여보다 더 많은 급여를 받는 사원 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT AVG(SAL)
FROM EMP);
SELECT * FROM EMP;
-- 특정 사원의 급여와 동일하거나 더 많은 사원이 이름, 급여 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT SAL
FROM EMP
WHERE ENAME ='ALLEN');
SELECT * FROM EMP;
SELECT * FROM DEPT;
-- #1 QUIZ
-- DALLAS 에서 근무하는 사원의 이름, 부서번호를 출력하세요
SELECT ENAME DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'DALLAS');
-- SALES 부서에서 근무하는 모든 사원의 이름, 급여를 출력하세요
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'SALES');
-- 직속 상관이 KING 인 사원의 이름, 급여를 출력하세요
SELECT ENAME, SAL
FROM EMP
WHERE MGR = (SELECT EMPNO
FROM EMP
WHERE ENAME = 'KING');
-- 사원 테이블에서 최대 급여를 받는 사원을 출력하세요
SELECT ENAME, SAL
FROM EMP
WHERE SAL = (SELECT MAX(SAL)
FROM EMP);
-- 사원 테이블에서 20번 부서의 최소 급여보다 많은 부서를 출력하세요
SELECT D.DNAME, MIN(E.SAL)
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO != 20
AND SAL > (SELECT MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING DEPTNO = 20)
GROUP BY D.DNAME;
SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > (SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 20);
/*
# 다중행 서브쿼리
- 서브쿠리에서 반환되는 결과가 하나 이상일 때 사용하는 서브쿼리
- 다중행 서브쿼리는 반드시 다중행 연산자와 함께 사용
> IN, ANY, ALL, EXISTS
*/
-- # IN : 메인 쿼리의 비교 조건이 서브쿼리 결과 중에서 하나라도 일치하면 참
-- 서브쿼리를 OR 처럼 쓸 때
-- 급여를 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DISTINCT DEPTNO
FROM EMP
WHERE SAL >= 3000);
-- # ANY : 메인 쿼리의 비교 조건이 서브쿼리의 결과와 하나 이상이 일치하면 참
-- 30번 부서에서 가장 작은 급여를 받는 사원보다 많은 급여를 받는 사원의 이름, 급여
-- 최솟값 비교시 유리
-- MIN을 쓰지 않는 이유
-- > 950 보다 큰 애들 다 나옴
-- > ANY 앞에 > 때문에 하나라도 참 인 값이 있으면 전부 출력이므로 굳이 MIN 을 하지 않아도 됨
-- > EX) 950, 1250 이 있을 때 950 보다 큰 값들은 모두 참이므로 반환함
-- OR 보다 더 강한? OR느낌ㅋ
SELECT ENAME, SAL
FROM EMP
WHERE SAL >ANY(SELECT SAL
FROM EMP
WHERE DEPTNO = 30);
-- # ALL : 메인쿼리의 비교 조건이 서브쿼리의 검색 결과와 모두 일치하면 참
-- 최댓값 비교시 유리
-- 30번 부서에서 가장 많은 급여를 받는 사원보다 더 많은 급여를 받는 사원의 이름, 급여
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL
FROM EMP
WHERE DEPTNO = 30);
-- # EXISTS : 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나로 있으면 참
-- IN 과의 차이점 : IN 연산자는 실제 존재하는 데이터들의 모든 값까지 확인
-- EXISTS 연산자는 해당 행이 존재하는지의 여부만 확인
-- EMP 테이블에 있는 DEPTNO 와 서브쿼리에 있는 DEPT 테이블의 DEPTNO 를 조인
-- DEPTNO 10, 20 이 있으면 EMP 테이블의 이름, 부서번호, 급여 출력
SELECT 1
FROM EMP E, DEPT D
WHERE D.DEPTNO IN (10,20)
AND E.DEPTNO = D.DEPTNO;
SELECT ENAME, DEPTNO, SAL
FROM EMP E
WHERE EXISTS (SELECT 1
FROM EMP E, DEPT D
WHERE D.DEPTNO IN (10,20)
AND E.DEPTNO = D.DEPTNO);
-- #2 QUIZ
SELECT * FROM EMP ORDER BY DEPTNO;
SELECT * FROM DEPT ORDER BY DEPTNO;
-- 부서별로 가장 급여를 많이 받는 사원의 정보(사원번호, 사원이름, 급여, 부서번호) 를 출력하세요
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
-- job 이 MANAGER 인 사람이 속한 부서의 부서번호, 부서명, 지역을 출력하세요
SELECT DEPTNO, DNAME, LOC
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE JOB ='MANAGER');
-- SALESMAN 의 최소 급여보다 많이 받는 사원들의 이름, 급여, 직급을 출력하세요
SELECT ENAME, SAL, JOB
FROM EMP
WHERE SAL > ANY(SELECT MIN(SAL)
FROM EMP
WHERE JOB ='SALESMAN')
AND JOB != 'SALESMAN';
-- SALESMAN 보다 급여를 많이 받는 사원들의 이름, 급여, 직급을 출력하세요
SELECT ENAME, SAL, JOB
FROM EMP
WHERE SAL > ALL(SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN')
ORDER BY SAL ;
-- emp 테이블에서 적어도 한명의 사원으로부터 보고를 받을 수 있는
-- 사원의 정보(사원번호, 이름, 업무, 입사일자, 급여)를 사원번호 순으로 내림차순 정렬해서 출력하세요
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL
FROM EMP E1
WHERE EMPNO IN(SELECT DISTINCT E2.EMPNO
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO)
ORDER BY EMPNO DESC;
--------------
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL
FROM EMP E1
WHERE EXISTS(SELECT 1
FROM EMP E2
WHERE E1.EMPNO = E2.MGR)
ORDER BY EMPNO DESC;06_TABLE.SQL
-- 06_TABLE.SQL
/*
# DDL (DATA DEFINITION LANGUAGE ) : 테이블 생성, 수정, 삭제
- 테이블을 작성할 때 컬럼명도 같이 작성
- CREATE TABLE : 데이터를 저장하는 새로운 테이블을 생성
Ex) CREATE TABLE TABLE_NAME (
COLUMN_NAME, DATA_TYPE(EXPR),
COLUMN_NAME, DATA_TYPE(EXPR),
.......
);
*/
DESC EMP;
-- EMPNO NOT NULL NUMBER(4)
-- ENAME VARCHAR2(10)
-- JOB VARCHAR2(9)
-- MGR NUMBER(4)
-- HIREDATE DATE
-- SAL NUMBER(7,2)
-- COMM NUMBER(7,2)
-- DEPTNO NUMBER(2)
-- 사원번호, 사원이름, 급여 3개의 컬럼으로 구성된 EMP01 테이블 생성
CREATE TABLE EMP01(
EMPNO NUMBER(4),
ENAME VARCHAR(10),
SAL NUMBER(7,2)
);
DESC EMP01;
SELECT * FROM TAB;
-- CREATE TABLE 문에서 서브쿼리를 사용하여
-- 기존 테이블과 동일한 구조`의 내용을 갖는 새로운 테이블 생성 가능
CREATE TABLE EMP02
AS
SELECT * FROM EMP;
DESC EMP02;
SELECT * FROM EMP02;
-- 기존 테이블에서 원하는 컬럼만 선택적으로 복사해서 생성
CREATE TABLE EMP03
AS
SELECT EMPNO, ENAME FROM EMP;
SELECT * FROM EMP03;
-- 기존 테이블의 구조만 복사 / 컬럼만 따오기
-- WHERE 조건에 항상 거짓이 되는 조건을 지정
CREATE TABLE EMP04
AS
SELECT * FROM EMP WHERE 1=0;
SELECT * FROM EMP04;
/*
# ALTER TABLE
- 기존 테이블의 구조를 변경하기 위해 사용되는 DDL 명령문
- ADD COLUMN : 새로운 컬럼 추가
- MODIFY COLUMN : 기존 컬럼 수정
- DROP COLUMN : 기존 컬럼 삭제
*/
-- # ALTER TABLE ADD : 기존 테이블에 새로운 컬럼 추가
-- >> 새로운 컬럼은 테이블의 마지막에 추가됨
-- >> 자신이 원하는 위치에 넣을 수 없음
-- ALTER TABLE 테이블명
-- ADD ( 컬렴명 데이터타입, .....);
DESC EMP01;
-- EMP01 테이블에 JOB 컬럼 추가
ALTER TABLE EMP01
ADD(JOB VARCHAR2(9));
-- 컬럼 이름 수정
-- ALTER TABLE 테이블명
-- RENAME COLUMN 현재컬럼명 TO 수정컬럼명;
ALTER TABLE EMP02
RENAME COLUMN HI TO MGRNO;
DESC EMP02;
/*
# ALTER TABLE MODIFY
- 기존 컬럼의 속성을 변경
- 속성을 변경한다는 것은 컬럼에 대한 데이터 타입이나 크기를 변경
Ex) NUMBER(3) > NUMBER(4) / NUMBER(3) > VARCHAR2(10)
ALTER TABLE 테이블명
MODIFY (컬럼명 데이터타입 ,,,,,);
*/
-- EMP01 의 JOB 컬럼을 최대 30글자까지 저장할 수 있게 수정
DESC EMP01;
ALTER TABLE EMP01
MODIFY (JOB VARCHAR2(30));
/*
# ALTER TABLE DROP
- 테이블에서 사용중인 컬럼을 삭제
ALTER TABLE 테이블명
DROP COLUMN 컬럼명;
*/
--
ALTER TABLE EMP01
DROP COLUMN JOB;
/*
# 테이블 삭제 / 휴지통 보관
- DROP TABLE 테이블명;
*/
DROP TABLE EMP01;
SELECT * FROM TAB;
-- 휴지통 조회
SELECT * FROM RECYCLEBIN;
-- 휴지통에서 테이블 복구
-- FALSHBACK TABLE 복구테이블명 TO BEFORE DROP;
FLASHBACK TABLE EMP01 TO BEFORE DROP;
-- 휴지통 거치지 않고 삭제
-- DROP TABLE 삭제테이블명 PURGE;
DROP TABLE EMP01 PURGE;
/*
# 테이블의 모든 행(ROW) 삭제 / 레코드 삭제
- TRUNCATE TABLE 테이블명;
*/
SELECT * FROM EMP02;
-- EMP02 테이블의 모든 행 삭제
TRUNCATE TABLE EMP02;
SELECT * FROM EMP02;
/*
# 테이블 이름 변경
- RENAME 기존테이블명 TO 수정테이블명
*/
-- EMP02 테이블 이름 변경
RENAME EMP02 TO NEWEMP02;
SELECT * FROM TAB;
-- #3 QUIZ
-- 아래와 같은 구조를 가진 dept01 테이블을 생성하세요
-- deptno NUMBER(2)
-- dname VARCHAR2(14)
-- loc VARCHAR2(13)
CREATE TABLE DEPT01(
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
DESC DEPT01;
-- emp 테이블의 사원번호, 사원이름, 급여 컬럼을 복사해서 empcopy 테이블을 생성하세요
CREATE TABLE EMPCOPY
AS
SELECT EMPNO, ENAME, SAL FROM EMP;
DESC EMPCOPY;
-- dept 테이블과 동일한 구조의 빈 테이블 dept02 를 생성하세요
CREATE TABLE DEPT02
AS
SELECT * FROM DEPT WHERE 1=0;
SELECT * FROM DEPT02;
-- dept02 테이블을 삭제하세요
DROP TABLE DEPT02;
-- dept 테이블과 동일한 구조의 빈 테이블 dept03 을 생성하세요
-- dept03 테이블에 문자 타입의 부서장(dmgr) 컬럼을 추가하세요 ( 크기 자유 )
CREATE TABLE DEPT03
AS
SELECT * FROM DEPT WHERE 1=0;
ALTER TABLE DEPT03
ADD (DMGR VARCHAR2(10));
DESC DEPT03;
-- dept03 테이블의 부서장 컬럼을 삭제하세요
ALTER TABLE DEPT03
DROP COLUMN DMGR;
DESC DEPT03;
07_TABLE_DML.SQL
/* 07_TABLE_DML.SQL */
/*
# INSERT
- 테이블에 새로운 데이터를 추가할 때 사용
INSERT INTO 테이블명(컬럼명, ....)
VALUES(컬럼값, ....);
*/
SELECT * FROM DEPT01;
-- 새로운 데이터를 추가하기 위해 사용하는 명령어 INSERT INTO VALUES는
-- 괄호 안의 컬럼명에 있는 목록수와 VALUES 다음에 오는 괄호에 작성한 값의 갯수를 일치시켜야 함
-- 모든 컬럼에 데이터를 입력하는 경우 컬럼 목록 생략 가능
-- >> 생략 시 VALUES 다음에 값들이 테이블의 컬럼 순서대로 저장
-- >> DATA TYPE는 지켜야 함!
INSERT INTO DEPT01
(DEPTNO, DNAME, LOC) -- 3개
VALUES
(10, 'ACCOUNTING','NEW YORK'); -- 3개
-- #ERROR
-- 컬럼수와 VALUES 수 불일치
-- 컬럼명 불일치
-- 자료형(DATA TYPE) 불일치
INSERT INTO DEPT01
VALUES
(50, 'PROGRAMMERS', 'DEV1');
INSERT INTO DEPT01
(DEPTNO, DNAME)
VALUES
(60,'AI');
-- 아래와 같이 컬럼명의 순서는 맞주지 않아도 됨
-- 단, 컬럼명, VALUES 의 데이터 타입은 맞아야 함
INSERT INTO DEPT01
(LOC, DNAME, DEPTNO)
VALUES
('BUSAN','DEVELOP', 70);
/*
# NULL 값 추가
- NULL 또는 '' 사용
*/
INSERT INTO DEPT01
VALUES
(40,'OPERATIONS',NULL);
INSERT INTO DEPT01
VALUES
(80,'',NULL);
SELECT * FROM DEPT01;
/*
# 서브쿼리를 사용해서 ROW(행) 복사
*/
CREATE TABLE DEPT02
AS
SELECT * FROM DEPT WHERE 1=0;
INSERT INTO DEPT02
SELECT * FROM DEPT;
SELECT * FROM DEPT02;
/*
# INSERT ALL 을 사용한 다중 행 입력
- 여러 테이블에 값을 넣을 수 있음
*/
-- 사원번호, 사원명, 입사일자를 관리하는 EMP_HIRE 테이블 생성
CREATE TABLE EMP_HIRE
AS
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE 1=0;
SELECT * FROM EMP_HIRE;
-- 사원번호, 사원명, 상관을 관리하는 EMP_MGR 테이블 생성
CREATE TABLE EMP_MGR
AS
SELECT EMPNO, ENAME, MGR
FROM EMP
WHERE 1=0;
SELECT * FROM EMP_MGR;
DROP TABLE EMP_MGT PURGE;
-- INSERT ALL 을 이용하여 HIRE, MGR 테이블에 같은 조건으로 값 넣기
INSERT ALL
INTO EMP_HIRE VALUES(EMPNO, ENAME, HIREDATE)
INTO EMP_MGR VALUES(EMPNO, ENAME, MGR)
SELECT EMPNO, ENAME, HIREDATE, MGR
FROM EMP
WHERE DEPTNO = 20;
SELECT * FROM EMP_HIRE;
SELECT * FROM EMP_MGR;
-- INSERT ALL ~ WHEN(조건) 으로 다중 테이블에 값 넣기
-- 사원번호, 사원명, 입사일자를 관리하는 EMP_HIRE2 테이블 생성
CREATE TABLE EMP_HIRE2
AS
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE 1=0;
SELECT * FROM EMP_HIRE2;
-- 사원번호, 사원명, 급여 관리 EMP_SAL 테이블 생성
CREATE TABLE EMP_SAL
AS
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE 1=0;
SELECT * FROM EMP_SAL;
-- EMP_HIRE2 테이블에는 1982년 1월 1일 이후에 입사한 사원의 정보 추가
-- EMP_SAL 테이블에는 급여가 2000 이상인 사원의 정보 추가
INSERT ALL
WHEN HIREDATE > '1982/01/01' THEN
INTO EMP_HIRE2 VALUES (EMPNO, ENAME, HIREDATE)
WHEN SAL >= 2000 THEN
INTO EMP_SAL VALUES (EMPNO, ENAME, SAL)
SELECT EMPNO, ENAME, HIREDATE, SAL
FROM EMP;
SELECT * FROM EMP_HIRE2;
SELECT * FROM EMP_SAL;
/*
# UPDATE
- 테이블에 저장된 데이터를 수정할 때 사용
UPDATE 테이블명
SET 컬럼명 = VALUE, ....
WHERE CONDITIONS;
- WHERE 절을 지정하여 특정한 행 만 값 수정 가능
> 사용하지 않으면 모든 행이 수정
*/
-- 연습 테이블
DROP TABLE EMP01 PURGE;
CREATE TABLE EMP01
AS
SELECT * FROM EMP;
SELECT * FROM EMP01;
-- 모든 부서번호 30으로 바꾸기
UPDATE EMP01
SET DEPTNO = 30;
-- 급여 10% 인상
UPDATE EMP01
SET SAL = SAL * 1.1;
-- 모든 사원의 입사일을 오늘로 변경
UPDATE EMP01
SET HIREDATE = SYSDATE;
-- WHERE 절을 사용하여 특정한 행만 값 바꾸기
-- 부서번호가 10번인 사원의 부서번호를 30번으로 수정
UPDATE EMP01
SET DEPTNO = 30
WHERE DEPTNO = 10;
-- 급여가 3000 이상인 사원의 급여를 10% 인상
UPDATE EMP01
SET SAL = SAL * 1.1
WHERE SAL >=3000;
SELECT * FROM EMP01;
-- 1982년도 입사한 사원 입사일을 오늘로 변경
UPDATE EMP01
SET HIREDATE = SYSDATE
WHERE HIREDATE >= '1982/01/01' AND HIREDATE < '1983/01/01';
UPDATE EMP01
SET HIREDATE = SYSDATE
WHERE SUBSTR(HIREDATE,1,2) = '82';
/*
# 2개 이상의 컬럼값 변경
- 기존 SET 절에 쉼표로 구분해서 컬럼=값 을 작성
*/
UPDATE EMP01
SET DEPTNO = 40, JOB = 'MANAGER'
WHERE ENAME= 'SMITH';
/*
# 서브쿼리를 사용한 데이터 수정
- UPDATE 문의 SET 절에서 서브쿼리 작성
>> 서브쿼리를 실행한 결과로 내용 변경
UPDATE 테이블명
SET 서브쿼리
*/
DROP TABLE DEPT01 PURGE;
CREATE TABLE DEPT01
AS
SELECT * FROM DEPT;
SELECT * FROM DEPT;
SELECT * FROM DEPT01;
-- 20번 부서의 지역명을 40번 부서의 지역명으로 설정
UPDATE DEPT01
SET LOC =(SELECT LOC
FROM DEPT01
WHERE DEPTNO = 40)
WHERE DEPTNO = 20;
-- 부서번호 20번인 부서의 부서명과 지역명을 부서번호 30번으로 설정
UPDATE DEPT01
SET (DNAME, LOC) = (SELECT DNAME, LOC
FROM DEPT01
WHERE DEPTNO = 30)
WHERE DEPTNO = 20;
Fintech