해당 편은 SQL 시작편 - Oracle편에서 이어집니다. 이번에는 SQL 고급 문법에 대해 알아볼 겁니다. 주로 내장함수, 부속질의, 뷰, 인덱스로 이뤄져 있습니다,

아래는 사용하는 데이터와 테이블을 생성하는 테이블입니다.

데이터 생성을 위한 코드

/* madang 계정으로 접속 실행하면 Emp, Dept 테이블이 생성된다. 
/* 오라클 데이터베이스에서 scott 계정으로 많이 사용되는 샘플 데이터베이스이다. 

use madang;

drop table emp;
drop table dept;
drop table salgrade;
CREATE TABLE IF NOT EXISTS DEPT (
  DEPTNO integer NOT NULL,
  DNAME varchar(14) DEFAULT NULL,
  LOC varchar(13) DEFAULT NULL,
  PRIMARY KEY (DEPTNO)
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(40, 'OPERATIONS', 'BOSTON');

CREATE TABLE IF NOT EXISTS EMP (
  EMPNO integer NOT NULL PRIMARY KEY,
  ENAME varchar(10) DEFAULT NULL,
  JOB varchar(9) DEFAULT NULL,
  MGR integer DEFAULT NULL,
  HIREDATE date DEFAULT NULL,
  SAL integer DEFAULT NULL,
  COMM integer DEFAULT NULL,
  DEPTNO integer DEFAULT NULL,
  FOREIGN KEY (deptno) REFERENCES Dept(deptno)
);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600, 300, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250, 500, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250, 1400, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500, 0, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300, NULL, 10);

CREATE TABLE IF NOT EXISTS SALGRADE(
	GRADE INTEGER,
	LOSAL INTEGER,
	HISAL INTEGER
	);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

select * from emp; 

추가 데이터 생성 코드

drop table temp;
CREATE TABLE TEMP (
 EMP_ID      NUMBER NOT NULL PRIMARY KEY,
 EMP_NAME    VARCHAR2(10) NOT NULL,
 BIRTH_DATE  DATE,
 DEPT_CODE   VARCHAR2(06) NOT NULL,
 EMP_TYPE    VARCHAR2(10),
 USE_YN      VARCHAR2(01) NOT NULL,
 TEL         VARCHAR2(15),
 HOBBY       VARCHAR2(30),
 SALARY      NUMBER,
 LEV         VARCHAR2(10)
);

CREATE TABLE TDEPT (
 DEPT_CODE   VARCHAR2(06) NOT NULL PRIMARY KEY,
 DEPT_NAME   VARCHAR2(20) NOT NULL,
 PARENT_DEPT VARCHAR2(06) NOT NULL,
 USE_YN      VARCHAR2(01) NOT NULL,
 AREA        VARCHAR2(10),
 BOSS_ID     NUMBER
);

INSERT INTO TEMP VALUES (19970101,'김길동',TO_DATE('19740125','YYYYMMDD'),'AA0001','정규','Y','','등산',100000000,'부장');
INSERT INTO TEMP VALUES (19960101,'홍길동',TO_DATE('19730322','YYYYMMDD'),'AB0001','정규','Y','','낚시',72000000,'과장');
INSERT INTO TEMP VALUES (19970201,'박문수',TO_DATE('19750415','YYYYMMDD'),'AC0001','정규','Y','','바둑',50000000,'과장');
INSERT INTO TEMP VALUES (19930331,'정도령',TO_DATE('19760525','YYYYMMDD'),'BA0001','정규','Y','','노래',70000000,'차장');
INSERT INTO TEMP VALUES (19950303,'이순신',TO_DATE('19730615','YYYYMMDD'),'BB0001','정규','Y','','',56000000,'대리');
INSERT INTO TEMP VALUES (19966102,'지문덕',TO_DATE('19720705','YYYYMMDD'),'BC0001','정규','Y','','',45000000,'과장');
INSERT INTO TEMP VALUES (19930402,'강감찬',TO_DATE('19720815','YYYYMMDD'),'CA0001','정규','Y','','',64000000,'차장');
INSERT INTO TEMP VALUES (19960303,'설까치',TO_DATE('19710925','YYYYMMDD'),'CB0001','정규','Y','','',35000000,'사원');
INSERT INTO TEMP VALUES (19970112,'연흥부',TO_DATE('19761105','YYYYMMDD'),'CC0001','정규','Y','','',45000000,'대리');
INSERT INTO TEMP VALUES (19960212,'배뱅이',TO_DATE('19721215','YYYYMMDD'),'CD0001','정규','Y','','',39000000,'과장');


INSERT INTO TDEPT VALUES ('AA0001','경영지원','AA0001','Y','서울',19940101);
INSERT INTO TDEPT VALUES ('AB0001','재무','AA0001','Y','서울',19960101);
INSERT INTO TDEPT VALUES ('AC0001','총무','AA0001','Y','서울',19970201);
INSERT INTO TDEPT VALUES ('BA0001','기술지원','BA0001','Y','인천',19930301);
INSERT INTO TDEPT VALUES ('BB0001','H/W지원','BA0001','Y','인천',19950303);
INSERT INTO TDEPT VALUES ('BC0001','S/W지원','BA0001','Y','인천',19966102);
INSERT INTO TDEPT VALUES ('CA0001','영업','CA0001','Y','본사',19930402);
INSERT INTO TDEPT VALUES ('CB0001','영업기획','CA0001','Y','본사',19950103);
INSERT INTO TDEPT VALUES ('CC0001','영업1','CA0001','Y','본사',19970112);
INSERT INTO TDEPT VALUES ('CD0001','영업2','CA0001','Y','본사',19960212);

내장 함수

문자 함수 - 숫자 75를 문자로 바꿔라

select chr(65) from dual;
select chr(97) from dual;

concat는 첫번째와 두번째 문자를 연결해라

select concat(concat(ename, ' is a '), job) from emp;

select initcap('the soap') from dual;

replace는 문장에서 지정된 문자를 찾아 다른 문자로 대체

select replace('JACK and JUE', 'J','bl')from dual;

서브스트링 - 지정된 문장의 뒤에서 부터 지정한 위치의 해당길이 만큼 추출

select substr('ABCDEF', -3,2) from dual;

정의된 단어의 길이 반환

select length('CAN') from dual;

숫자함수

select ceil(15.2) from dual; -- 올림
select floor(15.2) from dual; -- 버림
select round(15.7) from dual; -- 반올림
select round(15.193, 1) from dual; -- 반올림 > 15.2
select round(15.193, -1) from dual; -- 반올림 > 20
select mod(11,4) from dual; -- 나머지
select power(3,2) from dual; -- 3의 2승
select sign(-15) from dual; -- 정의된 값이 음수 -1, 0 은 0, 양수 1
select trunc(15.79, 1) from dual;

coalesce 함수

coalesce(a1, a2, aN…) -> a1이 null이면 a2 출력, a1,a2 둘다 null이면 aN 출력

select comm, coalesce(comm, sal) from emp;

select comm, coalesce(comm, 100) from emp;

날짜, 시간 함수

select TO_DATE('02-04-2000','dd-mm-yyyy') from dual;

정의한 시간을 지정한 포멧으로 출력

select TO_DATE('01:30', 'HH24:MI') from dual;

1970-01-01 00:00:00 부터 946075441초가 지난 날짜를 date 형식으로 바꾸기

select TO_DATE('19700101000000', 'yyyymmddhh24miss') + 946075441/(24*3600) from dual;

select orderid "주문번호", orderdate "주문일", orderdate+10 "확정"
from orders;

select sysdate, to_char(sysdate, 'yyyymmdd day') from dual;

select orderid "주문번호", to_char(orderdate, 'yyyy-mm-dd day') "주문일",
       custid "고객번호",
       bookid "도서번호" from orders
where orderdate = to_date('20200707', 'yyyymmdd');

interval year to month

/*
년과 월을 사용해 두 날짜 사이의 간격을 저장하기 위한 데이터 형
형식 : INTERVAL YEAR(년도에 대한 자리수) TO MONTH (달에 대한 자리수)
*/
CREATE TABLE sam02 (YEAR01 INTERVAL YEAR(3) TO MONTH);

insert into sam02 values(INTERVAL '36' MONTH(3));

select YEAR01, sysdate, sysdate+YEAR01 from sam02;

select * from sam02;

drop table sam02;

interval day to second

/*
일, 시, 분, 초를 사용해 두날짜 사이의 기간을 지정하기 위해 사용
형식 : interval day(일수에 대한 자리수) to second (초에 대한 자리수)
        자리수를 지정하지 않으면 기본적으로 2자리로 잡힘
*/

CREATE TABLE sam03 (DAY01 INTERVAL DAY(3) TO SECOND);

INSERT INTO sam03 VALUES(INTERVAL '100' DAY(3));

select DAY01, sysdate, sysdate+DAY01 from sam03;

select * from sam03;

drop table sam03;

TRIM 함수 - LEADING 함수 => LTRIM의 기능

select ename, TRIM(LEADING 'A' from ename) as trim 
from emp where ename like 'A%';

TRAILING 함수 => RTRIM의 기능

select ename, TRIM(TRAILING 'N' from ename) as trim 
from emp where ename like '%N';

BOTH 함수 : 왼쪽, 오른쪽에 해당하는 문자열 있는 경우 절삭

select ename, TRIM(both 'A' from ename) as trim 
from emp where ename like 'A%';

nvl2(9i) *** - NULL 값을 임의 다른 값으로 변경

select empno, ename, comm, nvl2(comm, comm*1.1, 0) from emp;

nullif

select empno, ename, comm, nullif(comm, 0) from emp;

select count(*) from emp;

sum

select sum(sal) from emp;

avg

select avg(sal) from emp;

커미션 평균구하기

select avg(nvl(comm, 0)), avg(comm) from emp;

select max(sal), min(sal) from emp;

select max(hiredate)-min(hiredate) from emp;

select stddev(sal), variance(sal) from emp;

아래 요약 정보는 연습용 코드입니다.

-- 부서별 평균 급여액을 보여주기 위한 sql문

select avg(sal), deptno from emp
group by deptno
order by deptno;

select round(avg(sal)), deptno from emp
group by deptno
having avg(sal) > 4000
order by deptno;

-- 부서별 평균 급여액을 보여주는데 부서 인원이 4명 이상일 때만 출력
select round(avg(sal)), deptno from emp
group by deptno
having count(deptno) >= 4;

select count(*), avg(sal), deptno from emp
group by deptno
having count(*) > 4;

-- temp 테이블의 행의 수를 출력하면
select count(*), count(8) from temp;

-- temp 테이블의 사원번호의 수와 취미의 수를 출력
select count(emp_id), count(hobby) from temp;

-- 직급별 인원수 세어보기
select count(*), lev from temp group by lev;

-- 취미가 공부인 사람의 수를 세어보자
select count(*), hobby from temp group by hobby;
select count(*), hobby from temp where hobby like '노래' group by hobby;

-- lev가 과장이면서 salary 가 40000000 이상인 사람의 수 출력
select count(*) from temp
where lev = '과장' and salary >= '40000000';

-- lev의 종류 + 직급별 가장 빠른 사번
select count(*), lev, min(emp_id) from temp
group by lev;

-- 직급별 최소연봉을 가진 사원의 연봉과 사번 without subsquery
select lev, min(salary), substr(min(Lpad(to_char(salary), 10, '0')||emp_id),11) emp_id
from temp
group by lev;

-- 왜 반환을 이렇게 하냐?
select distinct lev, salary, emp_id from temp        
where salary in (select min(salary) from temp group by lev);

select distinct lev, salary from temp
where (salary, lev) in (select min(salary), lev from temp group by lev);

select min(salary) from temp group by lev;

grouping sets

절 여러개의 select문이 union all 집합 연산자에 의해 결합되어 결과가 리턴되는 것

부서별, 직무별로 그룹을 지어 그룹별로 평균 급여를 보는 뷰와 직무별 매니저별 그룹을 지어 또 그 그룹 별 평균을 낸 결과를 합친 것

select deptno, job, null, avg(sal) from emp
group by deptno, job --A
union all
select Null,job, mgr, avg(sal) from emp
group by job, mgr; --B

select deptno, job, mgr, avg(sal) from emp
group by grouping sets((deptno, job),(job, mgr));

직무가 분석가이면 10%, 지굼가 clerk이면 15%, 직무가 매니저이면 20%로 급여를 인상하려고 한다

select job, sal, decode(job, 'ANALYST', sal * 1.1, 
			'CLERK', sal * 1.15,  
			'MANAGER', sal * 1.20, sal) salary
			from emp;

현재 근무하는 사원들의 월별 입사현황을 조사할 때 1~6월 별 입사현황을 decode 함수를 이용해 집계

select count(decode(to_char(hiredate, 'mm'), '01', 1)) "1월",
	count(decode(to_char(hiredate, 'mm'), '02', 1)) "2월",
	count(decode(to_char(hiredate, 'mm'), '03', 1)) "3월",
	count(decode(to_char(hiredate, 'mm'), '04', 1)) "4월",
	count(decode(to_char(hiredate, 'mm'), '05', 1)) "5월",
	count(decode(to_char(hiredate, 'mm'), '06', 1)) "6월",
	count(*)"전체"
	from emp
	where to_char(hiredate, 'mm') >= '01' and to_char(hiredate, 'mm') <= '06';
			

case 함수

select job, sal, case 
	when job = 'ANALYST' then sal * 1.1
	when job = 'ANALYST' then sal * 1.15
	when job = 'ANALYST' then sal * 1.2
	else sal
end as 인상된월급
from emp;


select count(case when to_char(hiredate, 'mm') = '01' 
	then count(*) end) "1월",
	count(case when to_char(hiredate, 'mm') = '02' 
	then count(*) end) "2월",
	count(case when to_char(hiredate, 'mm') = '03' 
	then count(*) end) "3월",
	count(case when to_char(hiredate, 'mm') = '04' 
	then count(*) end) "4월",
	count(case when to_char(hiredate, 'mm') = '05' 
	then count(*) end) "5월",
	count(case when to_char(hiredate, 'mm') = '06' 
	then count(*) end) "6월",
	count(*) Total
	from emp group by hiredate
	having to_char(hiredate, 'mm') >= '01' and to_char(hiredate, 'mm') <= '06';
			

ROLLUP & CUBE

— 부서별 직무별 인원과 평균 급여액 분석 /* ROLLUP : 가로방향 CUBE : 세로방향 / select deptno, grouping(deptno), job, grouping(job), count(), avg(sal)*12 from emp group by rollup(deptno, job);

select deptno, grouping(deptno), job, grouping(job), count(*), avg(sal)*12 from emp
group by cube(deptno, job);

— join-using 절 (정의된 컬럼을 기준으로 natural JOIN이 됨)

select e.empno, e.ename, d.dname from emp e join dept d using (deptno);

— self 조인

select e.empno, e.ename, e.mgr, d.ename 
from emp e join emp d 
		on e.mgr = d.empno; 

select e.empno, e.ename, e.mgr, d.ename 
from emp e, emp d 
where e.mgr = d.empno; 

부속질의

서브쿼리, 조인, 집계 함수, 조건 검색을 조합하여 복잡한 데이터를 효과적으로 조회 및 처리를 시도할 겁니다.

중첩질의 - Where 부속질의

비교연산자

-- 스칼라 서브쿼리 (Scalar Subqueries)
select orderid, saleprice from orders
where saleprice <= (select avg(saleprice) from orders);

-- 다중 행 서브쿼리
select orderid, custid, saleprice from orders md
where saleprice > (select avg(saleprice) from orders so
                    where md.custid = so.custid);

IN, NOT IN

select sum(saleprice) "total" from orders
where custid in (select custid from customer where address like '%대한민국%');

ALL, SOME(ANY)

select orderid, saleprice from orders
where saleprice > all (select saleprice from orders where custid=3);

EXISTS, NOT EXISTS

select sum(saleprice) "total" from orders od
where exists (select * from customer cs where address like '%대한민국%' and cs.custid=od.custid);

스칼라 부속질의-select 부속질의

select (select name from customer cs 
where cs.custid = od.custid) "name", sum(saleprice) "total"
from orders od
group by od.custid;

inner join을 이용한 부속질의

select name, sum(saleprice) total from orders od
inner join customer cs on cs.custid = od.custid
group by name;

alter table orders add bookname varchar2(40);
update orders
set bookname = (select bookname 
                from book 
                where book.bookid = orders.bookid);

인라인 뷰-From 부속질의

select cs.name, sum(od.saleprice) total
from (select custid, name 
      from customer where custid <= 2) cs,
      orders od
where cs.custid = od.custid
group by cs.name;

부속질의 연습

-- (1)
select custid, (select address 
                from customer cs 
                where cs.custid = od.custid) "address",
                      sum(saleprice) "total"
from orders od
group by od.custid;

-- (2)
select cs.name, s
from (select custid, avg(saleprice) s from orders
      group by custid) od, customer cs
where cs.custid = od.custid;

-- (3)
select sum(saleprice) "total"
from orders od
where exists (select * from customer cs where custid <=3 and cs.custid = od.custid);

select sum(saleprice) total
from orders od, (select * from customer where custid <=3) cs
where cs.custid = od.custid;

데이터베이스의 정보

데이터베이스를 만들면 그냥 정보만 있는 게 아닙니다. 제약조건도 붙어있고 시퀀스라든지 프로시저라든지 여러가지 데이터베이스 내 사용자지정 함수라든지 추가적인 제한들이 붙어있습니다. 그렇기 때문에 함부로 데이터를 직접 입력하면 안 됩니다.

-- 모든 자료사전의 정보 출력
select * from dictionary;

-- 자료사전 테이블의 각 컬럼에 대한 설명 출력
select * from dict_columns;

-- 사용자가 생성한 모든 테이블 출력
select * from user_tables;
    select table_name, tablespace_name
    from user_tables;

-- 사용자가 생성한 모든 인덱스 출력
select * from user_indexes;
    select index_name, index_type, table_owner from user_indexes;

-- 사용자가 생성한 모든 뷰 출력
select * from user_views;

-- 사용자가 생성한 모든 제약조건 출력
select * from user_constraints;
    select owner, constraint_name, constraint_type, table_name from user_constraints;

뷰

뷰 같은 경우는 하나 이상의 테이블을 합해 사용하기 편하게 만든 가상 테이블입니다. 덕분에 사용자가 필요한 정보만 볼 수 있고, 요구에 맞춰서 가공해 뷰로 만들면 개념스키마의 테이블 변경이 응용 프로그램의 변경에 영향 없도록하죠.

create view bookview as select * from book;
drop view bookview;

create view vw_customer as select * from customer
where address like '%대한민국%';

select * from vw_customer;
drop view vw_customer;

create view vw_orders (orderid, custid, name, bookid, bookname, saleprice, orderdate)
as select od.orderid, od.custid, cs.name, od.bookid, bk.bookname, od.saleprice, od.orderdate
from orders od, customer cs, book bk
where od.custid = cs.custid and od.bookid = bk.bookid;

select * from vw_orders;
drop view vw_orders;

create or replace view vw_customer (custid, name, address)
as select custid, name, address
from customer
where address like '%영국%';

select * from vw_customer;
drop view vw_customer;

constraint(제약조건)

제약조건에도 종류가 존재합니다. 아래와 같이 크게 2개로 나눌 수 있습니다.

-- 1) column 레벨 제약조건
create table dept2 (deptno number(2) constraint dept_deptno_pk primary key,
dname varchar2(15),
loc varchar2(1) constraint dept_loc_ck check (loc in ('1', '2')));

insert into dept2 values(11, '이쁜이', '2');
-- loc 칼럼은 1,2만 받을 수 있음
insert into dept2 values(22, '점쟁이', '3');

drop table dept2;

--2) table 레벨 제약조건
create table dept4 (deptno number(2),
                    dname varchar2(15),
                    loc varchar2(1),
                    constraint dept4_deptno_pk primary key(deptno),
                    constraint dept4_loc_ck check(loc in ('1', '2')));

insert into dept4 values(11, '이쁜이', '2');
-- loc 칼럼은 1,2만 받을 수 있음
insert into dept4 values(22, '점쟁이', '3');

drop table dept4;