231124 4일차(오라클)

--1124 수업
※서브쿼리: SQL문 속 또 다른 SQL문, 서브쿼리
-괄호()로 묶어서 사용합니다.
-ORDER BY절을 사용할 수 없다
-메인쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정
-메인쿼리에 사용한 연산자가 단 하나의 데이터로만 연산이 가능한 연산자라면 서브쿼리의 결과 행 수는 반드시 하나여야 한다. 

*서브쿼리의 결과값이 날짜형인 경우 : HIREDATE~
*서브쿼리 안에 함수를 사용 : AGV(SAL)~

※실행 결과가 여러 개인 다중행 서브쿼리
:실행 결과 행이 여러 개로 나오는 서브쿼리
*IN 연산자 : 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 TRUE

 

-부서번호 별로 최대 급여 출력
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;

 

-각 부서별 최고 급여와 동일한 급여를 받는 사원 정보
SELECT * FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
부서별 최대 급여=동일한 급여 받는 사원이 1:1로 일치 하지 않기 때문에 IN을 쓴다..

*ANY,SOME 연산자: 한개 이상이라면~, 최댓값보다 작은 

--30번 부서 사원들의 최대 급여보다 적은 급여를 받는 사원 정보
SELECT*FROM EMP 
WHERE SAL<ANY(SELECT SAL FROM EMP WHERE DEPTNO=30)
ORDER BY SAL, EMPNO;

SELECT *FROM EMP
WHERE SAL<MAX(SAL)(SELECT SAL FROM EMP WHERE DEPTNO=30);

--부서 번호가 30인 사원들의 급여
SELECT SAL FROM EMP
WHERE DEPTNO =30
ORDER BY SAL;

*30번 부서 사원들의 최대 급여보다 적은급여를 받는 사원 정보
--30번 부서 사원들의 최대 급여
SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO=30;

--사원정보를 찾을 거야 / 30번 부서 최대 급여< 급여
SELECT *FROM EMP
WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);

*ALL 연산자 : 모두 만족~ =최솟값보다 작은
SELECT*FROM EMP 
WHERE SAL<ALL(SELECT SAL FROM EMP WHERE DEPTNO=30)
ORDER BY SAL, EMPNO;


--부서번호 30번인 사원들의 최소 급여
SELECT MIN(SAL) FROM EMP
WHERE DEPTNO=30;

 

--사원을 찾을거야. 30번인 사원들의 최소 급여보다 더 적은 급여를 받는 사원
SELECT ENAME FROM EMP
WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

*EXISTS연산자 : 저 내용이 있나, 없나 알고싶을 때 사용(서브쿼리 결과값이 존재하는가?, 행이 1개 이상일 경우)
SELECT * FROM EMP WHERE EXISTS(SELECT DNAME FROM DEPT WHERE DEPTNO=10);

--서브쿼리를 이용하여 EMP 테이블의 사원 중에 10번 부서에 속한 모든 사원들보다 일찍 입사한 사원 정보를 구해봥
SELECT ENAME, EMPNO, HIREDATE FROM EMP 
WHERE HIREDATE <ALL (SELECT HIREDATE FROM EMP WHERE DEPTNO=10);

SELECT HIREDATE FROM EMP WHERE DEPTNO=10;

*EMP테이블의 사원중/10번부서에 속한 모든 사원들보다/ 일찍 입사한 사원 정보
--10번 부서에 속한 사원들의 입사일
SELECT HIREDATE FROM EMP
WHERE DEPTNO=10;

--10번부서 모든 사원보다 일찍 입사한 사람
SELECT * FROM EMP
WHERE HIREDATE<ALL (SELECT HIREDATE FROM EMP WHERE DEPTNO=10);


--다중열 서브쿼리 사용
SELECT*FROM EMP 
WHERE(DEPTNO, SAL)
IN(SELECT DEPTNO, 
MAX(SAL) 
FROM EMP
GROUP BY DEPTNO);

*FROM절에 사용하는 서브쿼리: 인라인뷰 보통 WHERE절에 많이 쓰지만 꼭 WHERE절에만 쓰지 않는다
SELECT * FROM EMP ,(SELECT ENAME, EMPNO FROM EMP);

-- 인라인뷰 사용하기
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM(SELET*FROM EMP WHERE DEPTNO = 10) E10,
(SELET*FROM DEPT) D
WHERE E10.DEPTNO=D.DEPTNO;

*SELECT절에 사용하는 서브쿼리 :스칼라 서브쿼리


--전체 사원 중 ALLEN과 같은 직책인 사원들의 사원 정보, 부서 정보 출력
SELECT JOB
FROM EMP
WHERE ENAME='ALLEN';

SELECT E.JOB,E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.JOB=(SELECT JOB FROM EMP
WHERE ENAME='ALLEN');

--전체 사원의 평균 급여(SAL)보다 높은 급여를 받는 사원들의 사원 정보, 부서 정보, 급여 등급 정보를 출력

SELECT AVG(SAL) FROM EMP;

SELECT* FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S. HISAL
AND E.SAL> (SELECT AVG(SAL) FROM EMP);

※데이터 조작어
데이터 추가하기 INSERT
데이터 수정하기 UPDATE
데이터 삭제하기 DELETE

※데이터 정의어
테이블을 생성하는 CREATE
테이블을 변경하는 ALTER
테이블 이름을 변경하는 RENAME
테이블의 데이터를 삭제하는 TRUNCATE
테이블을 삭제하는 DROP

※트랜잭션 제어
트랜잭션을 제어하는 명령어 COMMIT ROLLBACK


--※데이터 조작어 : 추가하기, 수정하기, 삭제하기

*DEPT 테이블을 복사해서 DEPT_TEMP 테이블 만들기
CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT;

*EMP 테이블을 복사해서 EMP_TEMP 테이블 만들기
--행 없이 열만 만들기
CREATE TABLE EMP_TEMP AS SELECT* FROM EMP
WHERE 1<>1;

SELECT*FROM EMP_TEMP;

*INSERT문 : 테이블에 데이터를 추가
INSERT INTO 테이블 이름[(열1, 열2,...,열N)]
VALUES(열1에 들어갈 데이터, 열2에 들어갈 데이터, ...,열N에 들어갈 데이터);


DEPT_TEMP 테이블에 데이터 추가하기
INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
VALUES(50,'DATABASE','SEOUL');

SELECT* FROM DEPT_TEMP;


--테이블에 NULL 데이터 입력하기
--NULL의 명시적 입력
INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
VALUES(70,'WEB', NULL);

SELECT* FROM DEPT_TEMP;



--INSERT문으로 날짜 데이터 입력하기(날짜 사이에 / 입력)
INSERT INTO EMP_TEMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(9999,'홍길동','PRESIDENT',NULL,'2001/01/01',5000,1000,10);

--INSERT문으로 날짜 데이터 입력하기(TO_DATE)
INSERT INTO 
EMP_TEMP(EMPNO,ENAME,JOB,MGR,HIREDATE, SAL, COMM, DEPTNO)
VALUES(2111,'이순신','MANAGRE',9999,
TO_DATE('2001/07/01', 'YYYY/DD/MM'),
4000,NULL,20);

SELECT*FROM EMP_TEMP;


*연습문제
--EMPNO, ENAME, JOB 칼럼을 가지는 EMP_TEMP 테이블을 만들어보자
CREATE TABLE EMP_TEMP
AS SELECT EMPNO, ENAME, JOB FROM EMP;

SELECT *FROM EMP_TEMP;

DROP TABLE EMP_TEMP;

--부서번호 20번인 사원정보가 들어있는 
--EMPNO, ENAME, JOB 칼럼을 가지는 EMP_TEMP 테이블을 만들어보자

CREATE TABLE EMP_TEMP
AS SELECT EMPNO, ENAME,JOB FROM EMP
WHERE DEPTNO=20;


※한번에 여러 데이터 추가하기

*데이터 수정하기 UPDATE
--DEPT 테이블을 복사해서 DEPT_TEMP2테이블 만들기
CREATE TABLE DEPT_TEMP2
AS SELECT *FROM DEPT;

SELECT *FROM DEPT_TEMP2;

*UPDATE문의 기본 사용법
UPDATET [변경할 테이블]
SET [변경할 열1]=[데이터],[변경할 열2]=[데이터],
[WHERE 데이터를 변경할 대상 행을 선별하기 위한 조건];

--테이블 데이터 중 일부분만 수정하기
UPDATE DEPT_TEMP2
SET DNAME='DATBASE',
LOC='SEOUL'
WHERE DEPTNO = 40;

SELECT*FROM DEPT_TEMP2;

DEPT_TEMP2 테이블 업데이트
UPDATE DEPT_TEMP2
SET LOC='SEOUL';
>LOC열을 다 SEOUL로 바꿈

--수정한 내용을 되돌리고 싶을 때 사용
ROLLBACK;

--EMP_TEMP 테이블의 사원들 중에서 급여가 2500 이하인 사원만 추가 수당을 50으로 수정
--2500이하 사원
SELECT ENAME FROM EMP_TEMP WHERE SAL<=2500;

UPDATE EMP_TEMP
SET COMM = 50
WHERE SAL<=2500;

--저장하기
COMMIT;


*서브쿼리를 사용하여 데이터 수정하기

--40번 부서의 부서 이름과 지역을 서브 쿼리를 사용하여 수정
UPDATE DEPT_TEMP2
SET(DNAME,LOC)=(SELECT DNAME,LOC 
                FROM DEPT WHERE DEPTNO=40)
WHERE DEPTNO=40;

SELECT *FROM DEPT_TEMP2;

UPDATE DEPT_TEMP2
SET DNAME=(SELECT DNAME FROM DEPT WHERE DEPTNO=40),
LOC=(SELECT LOC FROM DEPT WHERE DEPTNO=40)
WHERE DEPTNO=40;

--UPDATE문의 WHERE절에 서브쿼리 사용하기
UPDATE DEPT_TEMP2
SET LOC='SEOUL'
WHERE DEPTNO=(SELECT DEPTNO
FROM DEPT_TEMP2
WHERE DNAME='OPERATIONS');

SELECT*FROM DEPT_TEMP2;

*데이터 삭제하기
CREATE TABLE EMP_TEMP2
AS SELECT * FROM EMP;

SELECT*FROM EMP_TEMP2;

--행삭제
DELETE FROM EMP_TEMP2
WHERE JOB = 'MANAGER';

SELECT*FROM EMP_TEMP2;
ROLLBACK;



※트랜잭션이란? 더이상 분할 할 수 없는 최소 수행 단위
*트랜잭션을 취소하고 싶을 때 ROLLBACK
*트랜잭션을 반영하고 싶을 때 COMMIT

※세션? 접속상태 ; 데이터베이스 접속을 시작으로 여러 데이터베이스에서 관련 작업을 수행한 후 접속을 종료하기까지 전체 기간을 의미
>하나의 세션 안에는 여러 개의 트랜잭션이 존재

*읽기 일관성 
:특정 세션에서 테이블의 데이터를 변경 중일 때 그 외 다른 세션에서는 현재 진행 중인 변경과 무관한 본래의 데이터를 보여주는 특성

*LOCK? : 트랜잭션이 완료(COMMIT, ROLLBACK)되기 전까지 다른 세션에서 조작할 수 없는 상태