[Oracle] SQL문 정리

지난 수업

 - select 절

 

 - order by 정렬

 

 - where 조건절 (연산자 : and, or, between, and, in, not, is not null, like)

 

 

집합 (column의 자료형이 일치해야함)

 - UNION : 결과 값을 합 집합으로 묶음, 중복은 제거

 

 - UNION ALL : 결과 값을 합집합으로 묶음, 중복 허용

 

 - MINUS : 결과 값을 차 집합으로 묶음

 

 - INTERSECT : 결과 값을 교집합으로 묶음

 

그룹함수

 - GROUP BY : 데이터들을 원하는 그룹으로 나눌 수 있음

 

 - HAVING : 그룹화된 데이터에서의 조건

   (Where : 그룹화된 데이터에 대해 적용하더라도 그룹화 이전을 기준으로 적용됨)

 

 - sum(column) : column 값들의 합계를 계산해주는 함수

 

 - avg(column) : column 값들의 평균을 계산해주는 함수

 

 - max(column) : column 값들 중 가장 큰 값을 찾아주는 함수

 

 - count(column) : column 값의 총 개수를 계산해주는 함수

 

 - SELECT sum(salary) FROM employees;

 

* test table : ex) SELECT 'DATABASE', LOWER('DATABASE') FROM dual;

 

 

문자함수

 - LOWER() -> 소문자로 변환

 

 - UPPER()  -> 대문자로 변환

 

 - SUBSTR() -> 문자열 추출

 

 - SELECT SUBSTR('abcdef', index, count) : index 부터 count 개 만큼의 문자를 출력 (인덱스의 범위가 1부터 시작됨)

   index 에 음수도 입력 가능 => 뒤에서부터 indexing. ex) SUBSTR('abcdef', -3) : def

 

 - LPAD()

 

 - RPAD()

 

 

숫자함수

 - MOD(a, b) -> a % b

 

 - ROUND(a, b) -> a를 소수점 b 번째 자리까지 출력. ex) select round(123.123, 1) from dual;

 

 

날짜함수

 - SYSDATE => 현재시간을 리턴하는 함수

   => SELECT SYSDATE -1 "어제", SYSDATE "오늘", SYSDATE +1 "내일" FROM dual;

 

 - ADD_MONTHS(날짜데이터, 더할 개월 수)

 

 - NEXT_DAY(날짜데이터, 요일문자)

   ex) SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일') FROM dual;

 

 

변환함수

 - TO_CHAR() : 숫자, 날짜 => 문자열 변환

    ex) SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD'), TO_CHAR(50000000, '$999,999,999') FROM dual;

 

 - TO_DATE() : 문자열 => 날짜 데이터 변환

    ex) SELECT TO_DATE('2022-03-07', 'YYYY-MM-DD'), TO_DATE('20220308', 'YYYY-MM-DD') FROM dual;

 

 - NVL() : NULL을 0 또는 기타 다른 default값으로 반환

    ex) SELECT employee_id, salary, NVL(commission_pct, 0) FROM employees;

 

 - NVL(expr, a, b) : expr 값이 null이 아니면 a 반환,

                                      null이면 b 반환

 

 - DECODE() :

   ex) SELECT job_id, DECODE(job_id, 'SA_MAN', 'Sales Dept', 'SH_CLERK', 'Sales Dept', 'Another') FROM employees;

 

 

데이터형 (자료형)

 - 문자형 : CHAR(size) - 고정형 , VARCHAR2(size) - 가변형

 

 - 숫자형 : NUMBER

 

 - 날짜형 : DATE, TIMESTAMP

 

 - 대용량 : LOB, BLOB

 

테이블 생성, 복사 및 수정

테이블 생성

CREATE TABLE 테이블명(
	컬럼명1 데이터형,
    컬럼명2 데이터형
    }

 

테이블 복사 (구조 + 데이터)

CREATE TABLE emp01 
AS SELECT * FROM EMPLOYEES;

 

테이블 복사 (구조)

CREATE TABLE emp01 
	AS SELECT * FROM EMPLOYEES WHERE 1 = 0;

 

테이블 구조 수정

 - 컬럼 추가

ALTER TABLE emp02
	ADD(job VARCHAR2(50));

 

 - 컬럼 수정

ALTER TABLE emp02
	MODIFY(job VARCHAR(100));

 

 - 컬럼 삭제

Alter Table emp2
	DROP COLUMN job;

 

 

데이터 정의 언어 - DDL (Data Definition Language)

 테이블이나 관계의 구조를 생성하는데 사용하며

 

 - CREATE : 새로운 데이터베이스 관계 (테이블, 뷰, 인덱스, 저장 프로시저) 만들기

 

 - DROP : 이미 존재하는 데이터베이스 관계 (테이블, 뷰, 인덱스, 저장 프로시저) 삭제

 

 - ALTER : 이미 존재하는 데이터베이스 개체에 대한 변경, RENAME의 역할

 

 - TRUNCATE : 관계 (테이블, 뷰, 인덱스, 저장 프로시저)에서 데이터를 제거 (한번 삭제시 돌이킬 수 없음)

 

 

 

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

 테이블에 데이터 검색, 삽입, 수정, 삭제하는데 사용

 

 - SELECT : 검색(질의)

 

 - INSERT : 삽입(등록)

INSERT INTO dept01 VALUES(300, 'Developer', 100, 10);
INSERT INTO dept01 (department_id, department_name)
	VALUES(400, 'Developer2');

 

 - UPDATE : 업데이트(수정)

UPDATE 테이블명 SET 컬럼명 = 수정값, 컬럼명 = 수정값
	WHERE 수정대상

 

UPDATE dept01 SET department_name = 'IT Service'
	WHERE department_id = 300;

 

 - DELETE : 삭제

DELETE FROM 테이블명 WHERE 삭제대상

 

 

데이터 제어 언어 - DCL (Data Control Language)

 데이터의 사용 권한을 관리하는데 사용

 

 - GRANT : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 부여

 

 - REVOKE : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 박탈 or 회수

 

 

 * 데이터 베이스 사용자에게 GRANT 및 REVOKE로 설정할 수 있는 권한 목록

 

 - CONNECT : Database or Skima에 연결하는 권한

 

 - SELECT : Database에서 데이터를 검색할 수 있는 권한

 

 - INSERT : Database에서 데이터를 등록(삽입)할 수 있는 권한 

 

 - UPDATE : Database의 데이터를 업데이트 할 수 있는 권한

 

 - DELETE : Database의 데이터를 삭제할 수 있는 권한

 

 - USAGE :  스키마 또는 함수와 같은 데이터베이스 개체를 사용할 수 있는 권한

 

 

* DELETE, TRUNCATE, DROP의 차이

 - DELETE : 데이터는 지워지지만 테이블 용량은 줄어 들지 않는다.

               원하는 데이터만 지울 수 있다.

               잘못 삭제한 경우 삭제한 것을 되돌릴 수 있다.

               rollback; 가능! (트랜잭션 적용 가능)

 

 - TRUNCATE : 삭제 후 용량이 줄어들고 인덱스등도 모두 삭제.

                    삭제 후 되돌릴 수 없다. (트랜잭션 적용 불가능)

 

 - DROP : 테이블 전체를 삭제

             삭제 후 되돌릴 수 없다. (트랜잭션 적용 불가능)

 

 

제약조건

 - 데이터 추가, 수정, 삭제하는 가운데 DB의 무결성을 보장

 

CREATE TABLE emp01(empno NUMBER, ename VARCHAR(20), job VARCHAR(20), deptno NUMBER);

INSERT INTO emp01 VALUES(NULL, NULL, 'IT', 30);

 

- NOT NULL 

   NULL값을 저장할 수 없음

-- Column : empno, ename 에 조건 NOT NULL을 통해 NULL의 입력을 방지
CREATE TABLE emp02(
	empno NUMBER NOT NULL,
	ename VARCHAR(20) NOT NULL,
	job VARCHAR(20),
	deptno NUMBER
	);

-- empno 와 ename 에 null이면 오류 발생
INSERT INTO emp02 VALUES(NULL, NULL, 'IT', 30);
INSERT INTO emp02 VALUES(NULL, 'kim', 'IT', 30);

-- 성공!
INSERT INTO emp02 VALUES(1, 'park', 'IT', 30);

 

- UNIQUE

   해당 필드(Column)는 서로 다른 값을 가져야 함 (중복 방지 - 유일성)

   한 테이블안에서 여러 필드에 사용 가능

 --Column : empno 에 조건 UNIQUE 를 통해 중복을 방지
 CREATE TABLE emp03(
    empno NUMBER UNIQUE,
    ename VARCHAR(20) NOT NULL,
    job VARCHAR(20),
    deptno NUMBER
    );

INSERT INTO emp03 VALUES(100, 'kim', 'IT', 30);

-- empno가 100으로 이전의 데이터와 중복되므로 
-- ORA-00001: unique constraint 오류 발생
INSERT INTO emp03 VALUES(100, 'PARK', 'IT', 30);

 

 - PRIMARY KEY (기본키, 식별자) 

  테이블당 1개의 필드만 PRIMARY KEY로 설정 가능

  NOT NULL과 UNIQUE의 특징을 모두 가짐

--column : empno 에 primary key 속성을 주입하여 유일성을 보장 (NOT NULL)
-- primary key는 테이블에 단 1개의 column에만 주입할 수 있으며,
-- unique key는 여러개의 column에 주입할 수 있고 NOT NULL이 보장되지 않는다.
CREATE TABLE emp04(
    empno NUMBER PRIMARY KEY, 
    ename VARCHAR2(20) NOT NULL, 
    job VARCHAR(20), 
    deptno NUMBER
    );

INSERT INTO emp04 VALUES(101, 'park', 'IT', 300000);
INSERT INTO emp04 VALUES(100, 'park', 'IT', 300000);

--ORA-00001: unique constraint 오류 발생
INSERT INTO emp04 VALUES(101, 'park', 'IT', 300000);

 

 - FOREIGN KEY

   현재 테이블을 다른 테이블과 연결 (현재 테이블을 다른 테이블에 의존하게 만듬 = 의존성 부여)

   * 반드시 참조되는 테이블의 유일성(UNIQUE KEY, PRIMARY KEY)가 보장되어야 한다.

CREATE TABLE emp05(
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR(20),
    deptno NUMBER REFERENCES departments(department_id)    
    );


-- ORA-02291: integrity constraint (HR.SYS_C007058) violated - parent key not found
-- 의존하는 Table에 해당 키(300000)가 존재하지 않아서 오류 발생
INSERT INTO emp05 VALUES(100, 'park', 'IT', 300000);

--의존하는 Table에 해당 키(10)가 존재하여 성공!
INSERT INTO emp05 VALUES(100, 'park', 'IT', 10);

 

 - 제약 조건 추가 방법

-- 테이블 당 1개의 PRIMARY KEY를 가질 수 있으므로, 
-- 8번째 줄에서 ORA-02260: table can have only one primary key 오류 발생
CREATE TABLE emp06(
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR(20),
    deptno NUMBER,
    
    CONSTRAINT emp06_empno_pk PRIMARY key(ename),
    CONSTRAINT emp06_deptno_fk
    	FOREIGN KEY(deptno)
        REFERENCES departments(department_id)
    );

 

CREATE TABLE emp07(
    empno NUMBER,
    ename VARCHAR(20),
    job VARCHAR(20),
    deptno NUMBER
    );

--제약조건 추가
-- emp07_empno_pk 라는 CONSTRAINT_NAME 으로 empno에 PRIMARY KEY 속성 주입
ALTER TABLE emp07
	ADD CONSTRAINT emp07_empno_pk PRIMARY KEY(empno);

-- emp07_depno_fk 라는 CONSTRAINT_NAME 으로 DEPTNO에 FOREIGN KEY 속성 주입
ALTER TABLE emp07
	ADD CONSTRAINT emp07_deptno_fk FOREIGN KEY(deptno)
    REFERENCES departments(department_id);
    
--emp07_ename_nn 라는 CONSTRAINT_NAME 으로 ename에 NOT NULL 속성 주입   
ALTER TABLE emp07
	MODIFY ename CONSTRAINT emp07_ename_nn NOT NULL;

 

---

실습 코드

-- 부서별로 평균 급여를 검색
SELECT department_id, avg(salary) FROM employees
GROUP BY department_id;

-- 부서별로 사원의 수와 커미션을 받는 사원의 수를 검색
SELECT commission_pct FROM employees;

-- *HAVING 절
-- 전체그룹에서 일부그룹만 추출하기 위해
-- 부서별 급여 평균이 5000 미만인 부서의 부서번호와 평균 급여를 검색

SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id
HAVING AVG(salary) < 5000;

-- 사원의 근속년수를 출력
select last_name, sysdate from employees;
select last_name, round((sysdate - hire_date)/365, 1) from employees;

-- 2007년도에 입사한 사원의 목록을 출력 => TO_CHAR() 사용
select last_name, hire_date from employees
--where substr(to_char(hire_date), 1, 2) = '07';
where to_char(hire_date, 'yyyy') = '2007';

SELECT job_id, 
DECODE(job_id, 'SA_MAN', 'Sales Dept', 'SH_CLERK', 'Sales Dept', 'Another') FROM employees;

SELECT job_id,
CASE job_id
WHEN 'SA_MAN' THEN 'Sales Dept'
WHEN 'SH_CLERK' THEN 'Sales Dept'
ELSE 'Another2'
END "CASE"
FROM employees;

--테이블 복사 (구조 + 데이터)
CREATE TABLE emp01 
AS SELECT * FROM EMPLOYEES;

-- 테이블 복사 (구조)
CREATE TABLE emp02
AS SELECT * FROM EMPLOYEES WHERE 1=0;

ALTER TABLE EMP02
MODIFY(job VARCHAR(100));

-- emp01 테이블에서 salary 3000 이상 대상자에게 salary 10% 임금을 인상해 보자
UPDATE emp01 SET salary = salary *1.1
WHERE salary >= 3000;

<hr.sql>

 

--1. 화학과 학년별 평균 학점을 검색하라.
SELECT syear, major, avg(avr) FROM student
WHERE major = '화학'
GROUP BY major, syear;

--2.각 학과별 학생수를 검색하라.
SELECT major, count(*) FROM student
GROUP BY major;

--3. 화학과 생물학과 학생을 4.5환산 학점의 평균을 각각 검색하라.
SELECT major, avg(avr*4.5/4.0) FROM STUDENT
WHERE major IN('화학', '생물')
GROUP BY major;

--4. 화학과를 제외한 학생들의 과별 평점평균을 검색
SELECT major, ROUND(AVG(avr), 2) FROM student
GROUP by major
HAVING major != '화학';

--5. 화학과를 제외한 각 학과별 평점 중에 평점이 2.0이상인 학과 정보를 검색하라
SELECT major, ROUND(AVG(avr), 2) FROM student
GROUP BY major
HAVING major != '화학' AND AVG(avr) >= 2.0;

--6. 과목명 마지막 글자를 제외하고 출력
SELECT cname, SUBSTR(cname, 1, LENGTH(cname) - 1) FROM course;

<kosta236.sql>

SELECT eno, ename, dno FROM emp
WHERE dno = 10
UNION
SELECT eno, ename, dno FROM emp
WHERE dno = 20;

-- 집합연산자를 이용하여 emp 내용 중 10번 부서번호를 제외한 내용을 출력
SELECT eno,ename, dno FROM emp
MINUS
SELECT eno, ename, dno FROM emp
WHERE dno = 10;

-- 1. emp테이블을 사용하여 20번, 30번 부서에 근무하고 있는 사원 중 
- -급여(sal)가 2000초과인 사원을 다음 두가지 방식의 select문을 사용하여 
-- 사원번호, 급여, 부서번호를 출력
-- 1) 집합 연산자를 사용하지 않는 방식
SELECT empno, ename, job, sal, deptno FROM emp
WHERE deptno IN(20, 30)
AND sal > 2000;

-- 2) 집합 연산자를 사용한 방식
SELECT empno, ename, job, sal, deptno FROM emp
WHERE deptno = 20 AND sal > 2000
UNION
SELECT empno, ename, job, sal, deptno FROM emp
WHERE deptno = 30 AND sal > 2000;

--4. 근무중인 직원이 3명 이상인 부서를 검색 (emp)
SELECT count(*), DEPTNO  FROM emp
GROUP BY DEPTNO
HAVING count(ENAME) >= 3;

--** 그룹화와 관련된 함수 (ROLLUP, CUBE...)
SELECT deptno, job, COUNT(*), MAX(sal), SUM(sal), AVG(sal) FROM emp
--GROUP BY deptno, job
--ORDER BY deptno, job;
 GROUP BY ROLLUP (deptno, job);
 
 -- 테스트 테이블
 SELECT 'DATABASE', LOWER('DATABASE') FROM dual;
 
 -- job컬럼 문자열 중 문자열 첫번째부터 2개 문자, 3번째부터 2개문자, 5번째 이후 문자 출력
 SELECT job, SUBSTR(job, 1, 2), SUBSTR(job, 3, 2), SUBSTR(job, 10) FROM emp;
 
 --**함수 과제
 -- Q1. EMPNO 열에는 EMP 테이블에서 사원 이름(ENAME)이 다섯 글자 이상이며 
 -- 여섯 글자 미만인 사원 정보를 출력
 -- MASKING_EMPNO 열에는 사원 번호 (EMPNO) 앞 두 자리 외 뒷자리를 * 기호로 출력
 -- MASKING_ENAME 열에는 사원 이름의 첫 글자만 보여주고 나머지 글자수만큼 *기호를 출력
SELECT
    EMPNO, 
    RPAD(SUBSTR(EMPNO, 1, 2), 
    LENGTH(EMPNO), '*') AS MASKING_EMPNO, 
    ename, 
    rpad(substr(ename, 1, 1), 
    length(ename), '*') "MASKING_ENAME" 
from emp
WHERE length(ename) >= 5 and length(ename) < 6;
-- rpad(a, b, '*') : a 출력 후 전체길이 b에서 남은 부분만큼 (b - a) '*'로 패딩


--Q2. EMP 테이블에서 사원들의 월 평균 근무일 수는 21.5일 입니다. 
-- 하루 근무 시간을 8시간으로 보았을 때 사원들의 하루 급여(DAY_PAY)와 시급(TIME_PAY)을 계산하여
-- 결과를 출력합니다.
-- 단 하루 급여는 소수점 세 번째 자리에서 버리고, 시급은 두 번째 소수점에서 반올림하세요.
select
    empno,
    ename,
    sal,
    trunc(sal/21.5, 2) "DAY_PAY",
    ROUND(sal/(21.5 * 8), 1) "TIME_PAY" 
    from emp;
-- trunc : 버림, round : 반올림


-- Q3. emp 테이블에서 사원들은 입사일(hiredate)을 기준으로 3개월이 지난 후 
-- 첫 월요일에 정직원이 됩니다. 
-- 사원들이 정직원이 되는 날짜(R_JOB)를 yyyy-mm-dd형식으로 출력해 주세요
-- 단, 추가 수당(comm)이 없는 사원의 추가 수당은 N/A로 출력하세요.
select
    empno,
    ename,
    hiredate,
    next_day(add_months(hiredate, '3'), '월요일') as R_JOB,
case
	when comm is null then 'N/A'
	when comm >= 0 THEN ''||comm
end as comm
from emp;

select
    empno,
    ename,
    hiredate,
    next_day(add_months(hiredate, '3'), '월요일') as R_JOB,
decode(comm, null, 'N/A', comm) as comm 
from emp;


--Q4. emp 테이블의  모든 사원을 대상으로 직속 상관의 사원 번호(mgr)을 
-- 다음과 같은 조건을 기준으로 변환해서 chg_mgr열에 출력하세요.

-- 직속 상관의 사원 번호가 존재하지 않을 경우 :0000
-- 직속 상관의 사원 번호 앞 두 자리가 75일 경우 :5555
-- 직속 상관의 사원 번호 앞 두자리가 76일 경우 : 6666
-- 직속 상관의 사원 번호 앞 두자리가 77일 경우 : 7777
-- 직속 상관의 사원 번호 앞 두자리가 78일 경우 : 8888
-- 그 외 직속 상관 사원 번호의 경우: 본래 직속 상관의 사원 번호 그대로 출력

select
    empno,
    ename,
    decode(mgr, null, ' ', mgr) "mgr",
decode(substr(mgr,1, 2),
    null, '0000',
    '75', '5555',
    '76', '6666',
    '77', '7777',
    '78', '8888',
    MGR) AS CHG_MGR 
FROM emp;