[국비 4일차 TIL] 서브쿼리, DML, DDL, DCL

 

-- ARGUMENT = 인수 (구체적으로)

-- 파라미터       = 매개변수 (일반적으로)

---

-- 서브쿼리(subquery)

-- SQL문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문

-- 서브쿼리의 결과 값을 사용하여 기능을 수행하는 영역은 메인쿼리(main query)

-- SQL문 안의 ()

-- 서브쿼리 결과 값이 날짜(DATE) 자료형일 때도 사용 가능

---

Q. 서브쿼리를 사용하여 EMP 테이블의 사원 정보 중 사원 이름이 ALLEN인 사원의 추가수당보다 많은 추가 수당을 받는 사원 정보를 구해라

 

SELECT * FROM EMP
WHERE COMM > (SELECT COMM FROM EMP
            WHERE ENAME = 'ALLEN');

 

-- ALLEN의 추가수당은 300임.

-- 300보다 많은 사람은 2명 WARD, MARTIN임.

 

 

SELECT ENAME, COMM FROM EMP;

SELECT * FROM EMP
WHERE HIREDATE < (SELECT HIREDATE FROM EMP
                WHERE ENAME = 'ALLEN');

---

Q. 서브쿼리 사용 EMP 테이블에서 전체 사원의 평균 급여보다 작거나 같은 급여를 받고있는 20번 부서 사원 및 부서 정보를 구해라

 

SELECT * FROM EMP
WHERE SAL <= (SELECT AVG(SAL) FROM EMP
                WHERE DEPTNO = 20);

--> 위의 코드는 부서번호 20이외에도 모든 부서가 출력됨

--> 수정하려면 아래에 AND로 조건을 추가해야함.

 

 

SELECT * FROM EMP
WHERE SAL <= (SELECT AVG(SAL) FROM EMP
                WHERE DEPTNO = 20)
                AND DEPTNO = 20;

--> 이러면 20번 부서만 출력됨.

---

-- 다중열 서브쿼리 (multiple-column subquery)

-- 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식

 

SELECT * FROM EMP
WHERE(DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP
                    GROUP BY DEPTNO);

 

-- EMP 테이블에서 DEPTNO, MAX(SAL)을 선택

-- DEPTNO, SAL 내에서 위 조건과 일치하는 내용 검색

-- 최종적으로 위의 조건에 해당하는 정보를 EMP 테이블에서 모두 검색

---

Q. 전 사원 중 ALLEN과 같은 직책인 사원들의 정보 출력

 

SELECT * FROM EMP
WHERE JOB IN (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN');

---

 

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);

 

-- = EMP E와 DEPT D와 SALGRADE S에서 모든 정보 출력

-- = WHERE E.DEPTNO = D.DEPTNO 두 칼럼을 이어줌

-- = 급여의 최저와 최고 사이 출력

-- = 급여가 EMP테이블의 평균 급여보다 많은 경우 출력

---

 

-- DML, DDL, DCL

-- C.R.U.D = CREATE, READ, UPDATE, DELETE

 

-- 데이터 조작어 DML (Data Manipulation Language)

-- INSERT / UPDATE / DELETE / SELECT

-- 삽입 / 수정 / 삭제 / 조회

 

-- 데이터 정의어 DDL (Data Definition Language)= 데이터베이스 객체, 테이블

-- CREATE / ALTER / RENAME / DROP / TRUNCATE

---

-- 테이블 복사하기

CREATE TABLE DEPT_TEMP3

AS SELECT * FROM DEPT;

 

-- DROP TABLE : 테이블 삭제

 

-- 세션

-- 데이터베이스에 접속한 사용자 또는 응용 프로그램의 활동 기간 동안의 상태

 

-- LOCK : 수정 중인 데이터 접근을 막음

-- 트랜잭션이 완료(COMMIT, ROLLBACK)되기 전까지 다른 세션에서 조작 불가

 

 

-- CREATE TABLE : 테이블 생성

-- 기존 테이블에서 복사해서 새 테이블 생성 가능

-- 기존의 열 구조 또는 일부 데이터만도 복사 가능

 

CREATE TABLE DEPT_DDL
AS SELECT * FROM DEPT;

DESC DEPT_DDL;

 

-- ALTER : 테이블 변경

 

CREATE TABLE EMP_ALTER
AS SELECT * FROM EMP;

SELECT * FROM EMP_ALTER;

 

-- ADD : 테이블에 열 추가

 

ALTER TABLE EMP_ALTER
ADD HP VARCHAR2(20);

 

-- RENAME : 열 이름 변경

-- 칼럼 뒤에 AS를 붙이는 것은 임시적인 별칭임. 실제 테이블에는 적용 안 됨.

-- RENAME을 사용하면 실제 테이블의 컬럼 이름이 변경됨.

 

ALTER TABLE EMP_ALTER
RENAME COLUMN HP TO TEL;

 

-- RENAME 더 간단한 방법 (RENAME 기존테이블 TO 변경내용)

RENAME EMP_ALTER TO EMP_RENAME;

 

 

-- MODIFY

ALTER TABLE EMP_ALTER

MODIFY EMPNO NUMBER(5);

DESC EMP_ALTER;

 

-- DROP : 열 삭제

ALTER TABLE EMP_ALTER
DROP COLUMN TEL;

DROP TABLE EMP_RENAME;

 

-- TRUNCATE : 테이블의 모든 데이터 삭제, 테이블 구조는 유지됨

TRUNCATE TABLE EMP_RENAME;

SELECT * FROM EMP_RENAME;

---

Q. 다음의 조건을 충족하는 EMP_HW테이블을 만드시오.

열 이름 : EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

자료형 : 정수형 숫자, 가변형 문자열, 가변형 문자열, 정수형 숫자, 날짜, 소수점 둘째자리까지 표현되는 숫자,소수점 둘째자리까지 표현되는 숫자, 정수형 숫자

길이 : 4,10,9,4,-,7,7,2

 

CREATE TABLE EMP_HW1 (
    EMPNO  NUMBER(5),
    ENAME  VARCHAR2(10),
    JOB  VARCHAR2(9),
    MGR  NUMBER(4),
    HIREDATE  DATE,
    SAL  NUMBER(7,2),
    COMM  NUMBER(7,2),
    DEPTNO  NUMBER(7)
    );
   
DESC EMP_HW1;

-- VARCHAR2 : 가변형 문자열