Just Do IT

✔ 테이블에 컬럼 추가하기

-- 11_Table_Alter
-- 테이블 구조(설계)변경하기 : DBA(Database Administrator : DB 관리자)
-- 실습 테이블 : DEP20 (구조만 복사)
CREATE TABLE DEPT20
AS
SELECT * FROM DEPARTMENT
WHERE 1=2;

-- 결과보기
SELECT * FROM DEPT20;

-- 예제 1) 부서 테이블에 날짜 자료형(DATE)을 가지는 BIRTH 컬럼 추가하기
-- 날짜 자료형 : DATE 사용(크기 없음)
-- 테이블에 새로운 컬럼 추가하기
ALTER TABLE DEPT20
ADD(BIRTH DATE);

-- 결과보기
SELECT * FROM DEPT20;

✔ 컬럼의 크기 변경하기

-- 예제 2) 부서 테이블에(DEPT20) 부서명(DNAME)의 크기를 변경하기-- 예제 2) 부서 테이블에(DEPT20) 부서명(DNAME)의 크기를 변경하기
-- 테이블의 컬럼 변경(크기변경, 자료형 변경 등)
ALTER TABLE DEPT20 MODIFY DNAME VARCHAR2(30);
-- 테이블 구조보기 : DESC 테이블명
DESC DEPT20;

✔ 테이블의 컬럼 삭제하기

-- 예제 3) 부서테이블(DEPT20) BIRTH 컬럼 삭제하기
ALTER TABLE DEPT20 DROP COLUMN BIRTH;
SELECT * FROM DEPT20;

✔ 테이블 이름 변경하기

-- 예제 4) 테이블 이름 변경하기 : DEPT20 -> DEPT30
RENAME DEPT20 TO DEPT30;
DESC DEPT30; -- DEPT20에서 DEPT30으로 변경확인

✔ 연습문제

-- 12_Table_Alter_Exam
-- 테이블 구조(설계) 연습문제

-- 1) DEPT 테이블을 생성하세요.
-- 단 , DEPT 이 있으면 삭제하고(DROP TABLE ~) 재생성하세요.
-- 컬럼명 : DNO   NUMBER(2)
--         DNAME VARCHAR2(14)
--         LOC   VARCHAR2(13)
DROP TABLE DEPT;

CREATE TABLE DEPT(
    DNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);

-- 2) EMP 테이블을 생성하세요.
-- 단, EMP 테이블이 있으면 삭제하고(DROP TABLE ~) 재생성하세요.
-- 컬럼명 : ENO   NUMBER(4)
--         ENAME VARCHAR2(10)
--         DNO   NUMBER(2)
DROP TABLE EMP;

CREATE TABLE EMP(
    ENO NUMBER(4),
    ENAME VARCHAR2(10),
    DNO NUMBER(2)
);

-- 3) EMP 테이블을 수정하세요.(ENAME 컬럼 크기)
-- 컬럼명 : ENO   NUMBER(4)
--         ENAME VARCHAR2(25)
--         DNO   NUMBER(2)

ALTER TABLE EMP MODIFY ENAME VARCHAR2(25);
DESC EMP;

-- 4) EMPLOYEE 테이블을 복사해서 EMPLOYEE2 테이블을 생성하되
--    사원번호, 이름, 급여, 부서번호 컬럼만 복사하고,
--    새로 생성된 테이블의 컬럼명은 각각 
--    EMP_ID, NAME, SAL, DEPT_ID로 지정하세요.
--   (데이터도 복사)
-- 힌트) CREATE TABLE 복사테이블명(변경될컬럼명, 변경될컬럼명2 ...)
--      AS 
--      SELECT 원본컬러명, 원본컬럼명2,... FROM 테이블명
-- 참고 사용법) ALTER TABLE 테이블명 RENAME COLUMN 원본컬럼명 TO 변경될컬럼명;


CREATE TABLE EMPLOYEE2(EMP_ID, NAME, SAL, DEPT_ID)
AS
SELECT ENO, ENAME, SALARY, DNO FROM EMPLOYEE;


-- 5) EMP 테이블을 삭제하세요.

DROP TABLE EMP;

-- 6) EMPLOYEE2란 이름을 EMP로 변경하세요.

RENAME EMPLOYEE2 TO EMP;

-- 7) DEPT 테이블에서 DNAME 컬럼을 제거하세요.

ALTER TABLE DEPT DROP COLUMN DNAME;

-- 08_Table_CUD
-- 테이블 생성
-- CRUD : 약어, DB 또는 실무 프로젝트에서 많이 사용
-- C(CREATE문, 생성), R(READ, SELECT문, 조회), U(UPDATE문, 수정), D(DELETE문, 삭제)
-- 테이블 의미 : DB(데이터베이스)에서 자료를 저장하는 곳(논리적 공간)
-- 프로젝트 : 개발자 계정으로 접속해서 테이블 등을 생성함 (SCOTT 계정)

-- 1) 테이블 생성 : DDL ( DATA DEFINITION LANGUAGE : 데이터 정의어)
-- NUMBER(크기) : 숫자 자료형 (실수, 정수 등등)
-- 예) NUMBER(2) : 두 자리수 정수, NUMBER(2, 3) : 두 자리수 정수 + 세 자리수 소수점
--     NUMBER : 실수, 정수 무한 크기로 지정가능
-- 단, 크기가 지정되면 그 크기 이하로만 값을 넣을 수 있음
-- VARCHAR2(크기) : 문자형 자료형 , 가변 자료형 (VS CHAR(크기): 고정 자료형)
-- 예) VARCHAR2(4000) : 최대 크기, VARCHAR2 : 잘 안씀(1글자만 들어감)
-- 사용법) CREATE TABLE 테이블명(
--        컬럼명   NUMBER(2),        // 2자리수 숫자
--        컬럼명2  VARCHAR2(14),     // 14자리 문자
--        ...
--          );

CREATE TABLE DEPT(
        DNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC   VARCHAR2(13)
);

-- 예제 1) 사원테이블 : EMP
--        컬럼명 : ENO(사원번호, 숫자(4,0)
--                ENAME (사원명, 문자형(10)
--                JOB(직위, 문자형(9)
--                MANAGER(관리자) 숫자(4,0)
--                HIREDATE 입사일, 날짜형 (DATE)
--                SALARY 월급, 숫자형(7,2)
--                COMMISSION 상여금, 숫자형(7,2)
--                DNO  부서번호,  숫자형(2,0)  

CREATE TABLE EMP (
        ENO NUMBER(4, 0),
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MANAGER NUMBER(4, 0),
        HIREDATE DATE,
        SALARY NUMBER(7, 2),
        COMMISSION NUMBER(7, 2),
        DNO NUMBER(2, 0)
);

-- 실무 예 : 테이블 만들기 : 테이블 복사(카피)
-- 예제 2) DEPARTMENT 테이블 복사하기 : DEPT_COPY 테이블을 만드세요
-- 1) 데이터까지 포함해서 복사
-- 사용법 )  CREATE TABLE 복사할 테이블명
--          AS
--          SELECT * FROM 원본테이블;
CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPARTMENT;


-- 2) 데이터빼고 테이블 설계(디자인)만 복사
--  CREATE TABLE 복사할 테이블명
--  AS
--  SELECT * FROM 원본테이블
--  WHERE 거짓조건;
CREATE TABLE DEPT_COPY2
AS
SELECT * FROM DEPARTMENT
WHERE 1=2; -- 거짓 조건 아무거나 붙이면 데이터 빼고 복사됨


-- 컬럼/테이블 주석 : 테이블이나 컬럼에 상세 설명을 붙일 수 있음
-- 예제 3) DEPT 테이블에 컬럼 주석 달기
-- 1) 테이블 주석달기
-- 사용법 ) COMMENT ON TABLE 테이블명 IS '테이블주석';
COMMENT ON TABLE DEPT IS '부서정보';

-- 2) 컬럼 주석달기
-- 사용법 ) COMMENT ON COULUMN 테이블명.컬럼명 IS '컬럼주석';
COMMENT ON COLUMN DEPT.DNO IS '부서번호';
COMMENT ON COLUMN DEPT.DNAME IS '부서명';
COMMENT ON COLUMN DEPT.LOC IS '부서위치';

-- 예제 3) 사원테이블의 EMP 의 테이블 주석/컬럼주석을 작성해서
--      만들어 주세요
--    보기 : EMP ( 테이블주석 : 사원정보 )
--       컬럼주석 : ENO(사원번호), 
--                 ENAME(사원명), 
--                 JOB(직위), 
--                 MANAGER(관리자), 
--                 HIREDATE(입사일), 
--                 SALARY(월급), 
--                 COMMISSION(상여금), 
--                 DNO(부서번호) 

COMMENT ON TABLE EMP IS '사원정보';

COMMENT ON COLUMN EMP.ENO IS '사원번호';
COMMENT ON COLUMN EMP.ENAME IS '사원명';
COMMENT ON COLUMN EMP.JOB IS '직위';
COMMENT ON COLUMN EMP.MANAGER IS '관리자';
COMMENT ON COLUMN EMP.HIREDATE IS '입사일';
COMMENT ON COLUMN EMP.SALARY IS '월급';
COMMENT ON COLUMN EMP.COMMISSION IS '상여금';
COMMENT ON COLUMN EMP.DNO IS '부서번호';

-- 2) 테이블 삭제
-- 사용법 ) DROP TABLE 삭제할테이블명
DROP TABLE DEPT_COPY;

-- 예제 5) DEPT_COPY2 를 삭제
DROP TABLE DEPT_COPY2;

-- 3) 데이터 추가(INSERT(CREATE)/삭제(DELECT)/수정(UPDATE) 명령어 : CUD
-- DML문(DATA MANIPULATION LANGUAGE): 데이터 조작어
-- 특징 : 명령문 실행 후 취소(ROLLBACK)
-- 1) 테이블 복사 : DEPT_COPY

CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPARTMENT
WHERE 1=2;


-- 예제 6) DEPT_COPY 테이블에 데이터 INSERT
-- 컬럼 : DNO(NUMBER(2,0)), DNAME(VARCHAR2(14)), LOC(VARCHAR2(13))
-- 사용법 :  INSERT INTO 테이블명(컬럼명, 컬럼명2, ...)
--          VALUES(값, 값2, ...);

INSERT INTO DEPT_COPY(DNO, DNAME, LOC)
VALUES(10, 'ACCOUNTING', 'NEW YORK'); -- 데이터 추가

SELECT * FROM DEPT_COPY; -- 데이터 확인

-- 취소
ROLLBACK;

-- 영구반영 : 취소불가상태
-- 의미 : 영구반영하면 다른 유저가 현재 생성/수정/삭제한 데이터를 볼 수 있음
COMMIT;


-- 예제 7) 임시적으로 NULL 값을 입력해서 INSERT 하기ㅣ
-- 1) 암묵적으로(눈에 안보이지만) NULL 값 INSERT 하기
INSERT INTO DEPT_COPY(DNO, DNAME)
VALUES(30, 'SALES');

SELECT * FROM DEPT_COPY; -- 데이터 확인

-- 영구반영
COMMIT;

-- 2) 명시적으로 NULL 값 넣기
INSERT INTO DEPT_COPY(DNO, DNAME, LOC)
VALUES(40, 'OPERATIONS', NULL);

SELECT * FROM DEPT_COPY;

COMMIT;

-- 연습 2) DEPT_COPY 테이블에 50부서 'COMPUTING' 만들고 LOC는 ' ' 공백으로 INSERT 하세요

INSERT INTO DEPT_COPY(DNO, DNAME, LOC)
VALUES(50, 'COMPUTING', ' ');

SELECT * FROM DEPT_COPY;

COMMIT;

-- 연습용 테이블 복사
CREATE TABLE EMP_COPY
AS
SELECT * FROM EMPLOYEE
WHERE 1=2;

-- 연습 2)
INSERT INTO EMP_COPY(ENO, ENAME, JOB, HIREDATE, DNO)
VALUES (7000, 'CANDY', 'MANAGER', '2012/05/01', 10);

SELECT * FROM EMP_COPY;

COMMIT;

-- 예제 8) 명시적으로 날짜형으로 변환해서 INSERT하기
-- 날짜함수: TO_DATE(문자열, '날짜포맷')
-- 날짜포맷 : 'YYYY-MM-DD HH24:MI:SS'
INSERT INTO EMP_COPY (ENO, ENAME, JOB, HIREDATE, DNO)
VALUES(7010, 'CANDY2', 'MANAGER', TO_DATE('2012/05/01', 'YYYY-MM-DD HH24:MI:SS'), 10);

SELECT * FROM EMP_COPY;
COMMIT;

-- 예제 9) INSERT 할때 현재날짜(시간) 데이터 넣기 : SYSDATE(현재날짜)
INSERT INTO EMP_COPY (ENO, ENAME, JOB, HIREDATE, DNO)
VALUES(7020, 'CANDY3', 'MANAGER', SYSDATE, 10);

SELECT * FROM EMP_COPY;
COMMIT;

-- 예제 10) 다른 테이블의 데이터만 복사하기
SELECT * FROM DEPT_COPY;
-- 빠른 삭제 : DELETE 문 보다 속도 빠름 전체 삭제만 됨, 취소불가(ROLLBACK)
-- 사용법) TRUNCATE TABLE 테이블 명
TRUNCATE TABLE DEPT_COPY;

-- 다른 테이블의 데이터 복사(원본데이터 : DEPARTMENT(부서), 구조가 같아야함)
-- 사용법 ) INSERT INTO 복사될 테이블명
--         SELECT * FROM 원본 테이블명;
INSERT INTO DEPT_COPY
SELECT * FROM DEPARTMENT;
-- 결과 확인
SELECT * FROM DEPT_COPY;
-- 영구반영
COMMIT;

-- 07_Subquery
-- 서브쿼리 : 단일행 서브쿼리(1건만 나옴)
-- 부등호나 등호를 사용하여 비교함
-- 예제 1) SCOTT 사원보다 급여를 많이 받는 사원을 찾기위한 SQL문을 작성하세요
-- 해결 1-1) 사원테이블에서 SCOTT 사원의 급여를 찾은 후에 (1번)
-- 해결 1-2) 그 급여를 비교해서 많이 받는 사원을 추가로 찾아야함 (2번)
-- 1번
SELECT SALARY
FROM EMPLOYEE
WHERE ENAME = 'SCOTT'; -- 3000(급여)

-- 2번
SELECT ENAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000;

-- 문제점 : 성능저하 : 여러번 쿼리로 인해
-- WHERE 절의 컬럼과 서브쿼리의 SELECT 컬럼이 동일해야함
-- 사용법
-- SELECT 컬럼명 FROM 테이블명
-- WHERE 비교컬럼 > (SELECT 비교컬럼 FROM 테이블명);              
SELECT ENAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
                FROM EMPLOYEE
                WHERE ENAME = 'SCOTT');
                
-- 예제1 : SCOTT 사원과 동일한 부서에서 DNO 근무하는 사원 구하기
SELECT ENAME
FROM EMPLOYEE
WHERE DNO = (SELECT DNO 
            FROM EMPLOYEE 
            WHERE ENAME = 'SCOTT');

-- 예제2 : 30번 부서에서 최소급여를 구한후 그 최소 급여보다 부서별 최소 급여가 큰 부서만 출력하세요
SELECT MIN(SALARY)
FROM EMPLOYEE
WHERE DNO = 30;

SELECT DNO, MIN(SALARY)
FROM EMPLOYEE
GROUP BY DNO
HAVING MIN(SALARY) > (SELECT MIN(SALARY)
                      FROM EMPLOYEE
                      WHERE DNO = 30);
                      
-- 3) 특수한 경우 아래처럼 사용도 가능함
-- SELECT 문 안에도 들어감
SELECT 2*3, (SELECT 4*3 FROM DUAL)
FROM DUAL;

-- FROM 문 안에도 들어감
SELECT EMP.ENAME, EMP.ENO
FROM (SELECT ENAME, ENO FROM EMPLOYEE) EMP;

-- 2) 다중 행 서브쿼리(자식쿼리) : 여러 건이 나옴
-- 예제 4) (1) 부서별 최소급여를 받는 사원 중에서 (2) 사원번호와 이름을 출력하세요
-- (1) 부서별 최소급여 받는 사원 출력
SELECT MIN(SALARY)
FROM EMPLOYEE
GROUP BY DNO;

-- (2) 950, 1300, 800 급여를 받는 사원번호와 이름 출력
SELECT ENO, ENAME
FROM EMPLOYEE
WHERE SALARY IN (950, 1300, 800);

-- 최종쿼리
SELECT ENO, ENAME
FROM EMPLOYEE
WHERE SALARY IN (SELECT MIN(SALARY)
                    FROM EMPLOYEE
                    GROUP BY DNO);

-- 05_Group_Func
-- 그룹 함수 : 데이터 전체집계 함수들(총액, 평균, 최고, 최저액, 총개수)
-- 결과 특징)  1건이 나옴
--            SUM(컬럼명), AVG(컬럼명), MAX(컬럼명), MIN(컬럼명), COUNT(컬럼명)
-- 예제 1) 사원들의 급여 총액, 평균액, 최고액 출력하기
SELECT SUM(SALARY) AS 총액
      ,ROUND(AVG(SALARY)) AS 평균액
      ,MAX(SALARY) AS 최고액
      ,MIN(SALARY) AS 최저액
FROM EMPLOYEE;

-- 예제 1) 최근에 입사한 사원과 가장 오래전에 입사한 사원의 입사일 출력하기
SELECT MAX(HIREDATE) AS 최근
      ,MIN(HIREDATE) AS 오래전
FROM EMPLOYEE;

-- 예제 2) 사원들의 상여금(COMMISSION) 총액 출력
SELECT SUM(COMMISSION) AS "상여금 총액"
FROM EMPLOYEE;

-- 예제 3) 사원들의 총인원을 출력하세요
-- 사용법) COUNT(컬럼명[*])
SELECT COUNT(*) AS 총인원
FROM EMPLOYEE;

-- 예제 4) 상여금(COMMISSION) 받는 사원의 수 계산하기
-- COMMISSION 컬럼 NULL 있음(상여금 못받음)
-- 집계함수는 자동으로 NULL 제외하고 계산
SELECT COUNT(COMMISSION) AS "상여금 받는 사원의 수"
FROM EMPLOYEE;

-- 예제 5) NOT NULL 인 데이터만 계산하기
SELECT COUNT(COMMISSION) AS 사원수
FROM EMPLOYEE
WHERE COMMISSION IS NOT NULL;


-- 예제 6) 직위(JOB) 종류가 몇 개인지 출력하기
-- DISTINCT : 중복제거 키워드
-- 사용법 COUNT(DISTINCT 컬럼명)
-- 컬럼별칭(*) : 컬럼명 AS 별칭
-- 테이블 별칭(*) : 테이블명 별칭
-- 사용법        : 별칭.컬럼명
SELECT COUNT(DISTINCT EMP.JOB) AS 직업개수
FROM EMPLOYEE EMP;

-- 전체 집계 함수 특징 : 1) 1건이 결과로 나옴
--                    2) 일반 컬럼과 같이 사용할 수 없음 : 에러 발생

--SELECT ENAME, MAX(SALARY)
--FROM EMPLOYEE; -- 에러 (일반컬럼(ENAME), MAX(SALARY) 같이 사용불가)

-- 2) 부분(컬럼별)집계하기 : GROUP BY (예약어)
-- 사용법 ) GROUP BY 컬럼명, 컬럼명2, ...
-- 예제 7) 소속 부서별(DNO) 평균 급여를 부서번호와 함께 출력하세요
-- TRUNC() : 버림(절삭)
-- 전체집계시에는 일반컬럼과 같이 사용할 수 없지만
-- 부분집계시에는 일반컬럼과 같이 사용가능 (GROUP BY 사용하기 때문)
SELECT DNO
, TRUNC(AVG(SALARY)) AS 평균급여
FROM EMPLOYEE
GROUP BY DNO; -- 부서번호 컬럼별 집계(부서번호 컬럼은 출력할 수 있음)
-- 주의점 ) 1) 그룹함수와 일반컬럼은 같이 사용불가
--         단, 컬럼별 집계에서 그 컬럼은 그룹함수와 같이 사용할 수 있음
--SELECT DNO, ENAME
--, TRUNC(AVG(SALARY)) AS 평균급여
--FROM EMPLOYEE
--GROUP BY DNO;  -- 에러 : ENAME 컬럼은 GROUP BY 대상 컬럼이 아니므로 그룹함수와 같이 출력할 수 없음

-- 예제 8) 부서번호(DNO)별, 직위별(JOB), 데이터 건수(COUNT) 및 급여(SALARY) 총액(SUM) 구하기
SELECT DNO, JOB
      ,COUNT(*)
      ,SUM(SALARY)
FROM EMPLOYEE
GROUP BY DNO, JOB;

-- GROUP BY 사용시 조건을 추가 : HAVING 키워드 // GROUP 함수에만 사용가능
-- 사용법) HAVING 그룹함수(컬럼명) > 값
-- 예제 9) 부서번호별(DNO) 최고급여가 3000 이상인 부서의 번호와 최고금액 구하기
SELECT DNO ,MAX(SALARY)
FROM EMPLOYEE
GROUP BY DNO
HAVING MAX(SALARY) >= 3000;

-- 연습 1) 사원테이블에서(EMPLOYEE) JOB(직위)이 1) MANAGER 인
--       값을 제외하고 3) 급여(SALARY) 총액(SUM)이 5000 이상인
--       2) 직급별(JOB) 총액 구하기

SELECT JOB, SUM(SALARY)
FROM EMPLOYEE
WHERE JOB NOT LIKE '%MANAGER%'
GROUP BY JOB
HAVING SUM(SALARY) >= 5000;

📌 문자함수 - 대/소문자 변환

📌 문자함수 - 문자열 길이 리턴 함수

📌 문자함수 - 문자열 자르기, 붙이기, 공백제거

📌 숫자함수 - 반올림, 버림, 나머지 연산

📌 데이터에 조건을 붙여 조회하기(WHERE)

📌 논리연산자와 함께 조건절 사용하기

📌 BETWEEN 예약어

📌 IN 예약어

📌 LIKE 검색

📌 NULL 검색

📌 ORDER BY 정렬

📌 SQL 기본이론

📌 전체를 의미하는 *

 

SELECT * FROM DEPARTMENT; -- 전체부서를 조회하는 쿼리(질의문)

 

 

-- 예제 1) 사원테이블의 모든 컬럼 정보를 보기

SELECT * FROM EMPLOYEE;

📌 중복을 제거하는 DISTINCT