Let's Talk

Feel free to reach out. I'll get back to you as soon as possible.

Study 15 min

SQL 시작편 - Oracle편

SQL 입문 — Oracle을 기준으로 데이터베이스 SQL의 기초를 정리합니다.

SQL 시작편 - Oracle편

개발을 함에 있어서 가장 필수적인 요소들을 크게 잡아 프론트엔드, 백엔드, DB 이렇게 한번 잡고 생각을 했을 때 DB를 대충 사용하고자 하면 할 수 있지만 이에 대한 내용들을 정리할 필요를 느꼈습니다.

초창기에는 DB가 아닌 파일 시스템을 사용했었습니다. 파일 시스템은 데이터를 파일 단위로 파일 서버에 저장하는 형식인데 이는 각 응용 프로그램이 독립적으로 파일을 다루기 때문에 데이터가 중복 저장될 가능성이 있고, 동시에 파일을 다루기 때문에 일관성이 훼손될 수 있죠.

이후에 나온 것이 바로 데이터베이스 시스템 줄여서 DBMS(DateBase Management System)입니다.

데이터를 요청하는 쪽은 Client, 데이터를 소유하는 쪽은 Server라고 합니다.

DBMS 서버가 파일을 다루며 데이터의 일관성 유지, 복구, 동시 접근제어 등의 기능을 수행합니다.

아래는 간단한 개념

DB (DateBase)

우선 DB는 컴퓨터에 저장되는 데이터 집합입니다. 이런 데이터들은 쉽게 접근, 사용하기 위해서 구조화된 형태로 존재합니다. (Table, key-value 형태 등등)

DBMS (DateBase Management System)

말 그대로 해석하면 DB를 관리하는 시스템, 다시 한번 정리하면 DB는 여러 사람들이 공유하고 사용하는 목적으로 관리되는 정보를 의미합니다.

Oracle Korea에서는 DB를 이렇게 정의하고 있습니다. ‘일반적으로 컴퓨터 시스템에 전자적으로 저장되는 구조화된 정보 or 데이터의 조직화된 모음’, 이런 정보는 우리가 잘 아는 엑셀 파일로도 관리할 수 있고 테이블 형태로 저장할 수도 있습니다.

DBMS에서는 DB와 유저, 프로그램 사이의 인터페이스 역할을 수행하고 유저가 정보를 생성, 검색, 수정, 삭제 등등의 작업을 관리할 수도 있게 해줍니다. 추가적으로 SQL 튜닝이나, 데이터 백업, 복구, 모니터링까지도 가능하게 해줍니다.(SQL 기반 DBMS경우)

RDB (Relational Database) - 관계형 데이터베이스

관계형 데이터 베이스는 데이터를 테이블 형태로 저장합니다.

쉽게 생각하면 엑셀 표에 데이터를 저장하는 것과 동일하다고 보면됩니다. 각 데이터 항목들은 행(row)에, 항목의 속성은 열(column)이라고 표현됩니다. 열은 항목의 속성인 만큼 입력되는 데이터의 유형이 정해집니다.

Relation DBMS - 관계형 DB 관리 시스템

관계형?의 의미가 무엇일까 정리해봅니다. 정보를 저장할때, Table이라는 형태로 저장을 하는데, 단순하게 생각하면 엑셀에 표를 생각하면 편합니다(위에 그림). 이런 테이블은 정보(유저 정보, 상점 정보, 결제 정보 등)들로 각각 구성이 되는데, 이런 테이블 사이의 연관성을 관계성이라고 뜻합니다.

RDMBS 특징

  1. 2차원 구조 모델(열 : 속성, 행 : 튜플, 데이터)
  2. ACID(원자성, 일관성, 독립성, 지속성) 트랜잭션을 보장
  3. SQL를 사용해서 데이터 접근을 하며 CRUD 조작을 허용
  4. 여러 플랫폼에서 사용이 가능 (Android, iOS, Solaris, Linux, Window, VxWorks, Unix …)

ACID

  • Atomicity : 원자성은 트랜잭셔과 관련된 작업들이 부분적으로 실행되다가 중지되는 것이 아니라 하나의 원자 단위로 수행되는 것을 보장하는 특징입니다. 즉, 중간 단계까지 실행되는 것이 아니라 처음부터 끝까지 완전하게 실행되며 중간에서 실패하는 일이 없도록 합니다.

  • Consistency : 일관성은 트랜잭션이 완료되면 언제나 일관된 DB 상태를 유지하는 것을 의미합니다.

  • Isolation : 독립성은 트랜잭션을 수행 할 시, 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미하는데, 다시말해 다른 트랜잭션의 연산이 중간 단계의 데이터를 볼 수 없음을 의미합니다.

  • Durability : 지속성은 성공적인 트랜잭션은 영원히 반영되어야 함을 의미합니다. 시스템 에러, DB 일관성 체크 등을 하더라도 유지되어야함을 의미합니다. 트랜잭션은 로그에 모든 것이 저장된 후에만 Commit 상태로 간주될 수 있습니다.

! 주의사항

RDBMS에 관해서 오해가 있을 수 있는데 모든 DBMS가 RDBMS에서 속한 것은 아닙니다. 흔히 NoSQL이라고 하는데 자세한 설명은 하지 않겠습니다.

RDBMS의 종류 : MySQL, Oracle, SQLite, PostgresSQL, MariaDB

DBMS의 종류 : Redis, Hbase, MongoDB, OrientDB

RDBMS

위와 같이 여러 종류의 RDMBS가 존재합니다. 아무튼 서론이 길었는데 이런 DBMS를 사용하기 위한 DB언어를 SQL이라고 한다. 제목에서도 아시다시피 본 편은 Oracle을 다루고 있습니다.

하지만 SQL Developer나 Oracle 21c 설치는 알려드리지 않습니다. 아래 코드의 내용은 오라클로 배우는 데이터 개론과 실습의 코드를 가져왔습니다.

사용환경

  • Window 10
  • SQL developer
  • Oracle 21c

SQL developer의 SYSTEM에서 실행하는 코드

-- 세션에서 "_ORACLE_SCRIPT" 플래그를 true로 설정하여 특정 시스템 권한을 활성화
alter session set "_ORACLE_SCRIPT"=true;

-- "ecom6Shopspace"라는 테이블스페이스 생성
-- 데이터 파일은 지정된 경로에 생성되고 초기 크기는 2GB로 설정
-- EXTENT는 로컬로 관리되고, SEGMENT는 자동으로 관리됨
create tablespace ecom6Shopspace
datafile 'C:\app\USER\product\21c\oradata\XE\ecom6Shop.dbf' size 2G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- "ecom6Shop" 사용자 생성, 비밀번호는 '1234'
-- 기본 테이블스페이스는 "ecom6Shopspace"이고, 임시 테이블스페이스는 "temp"로 설정
create user ecom6Shop IDENTIFIED by 1234
default tablespace ecom6Shopspace
temporary tablespace temp;

-- "ecom6Shop" 사용자에게 "connect"와 "resource" 역할(롤) 부여
-- "connect": 데이터베이스에 연결할 수 있는 권한
-- "resource": 객체를 생성할 수 있는 권한 (테이블, 뷰, 인덱스 등)
grant connect, resource to ecom6Shop;

-- "ecom6Shop" 사용자가 "ecom6Shopspace" 테이블스페이스를 무제한으로 사용할 수 있도록 설정
ALTER USER ecom6shop DEFAULT TABLESPACE ecom6Shopspace QUOTA UNLIMITED ON ecom6Shopspace;

사용한 데이터 생성 코드

CREATE TABLE Book (
bookid NUMBER(2) PRIMARY KEY,
bookname VARCHAR2(40),
publisher VARCHAR2(40),
price NUMBER(8)
);

CREATE TABLE Customer (
custid NUMBER(2) PRIMARY KEY,
name VARCHAR2(40),
address VARCHAR2(50),
phone VARCHAR2(20)
);

CREATE TABLE Orders (
orderid NUMBER(2) PRIMARY KEY,
custid NUMBER(2) REFERENCES Customer(custid),
bookid NUMBER(2) REFERENCES Book(bookid),
saleprice NUMBER(8),
orderdate DATE
);

/* Book, Customer, Orders 데이터 생성 */
INSERT INTO Book VALUES(1, '축구의 역사', '굿스포츠', 7000);
INSERT INTO Book VALUES(2, '축구아는 여자', '나무수', 13000);
INSERT INTO Book VALUES(3, '축구의 이해', '대한미디어', 22000);
INSERT INTO Book VALUES(4, '골프 바이블', '대한미디어', 35000);
INSERT INTO Book VALUES(5, '피겨 교본', '굿스포츠', 8000);
INSERT INTO Book VALUES(6, '역도 단계별기술', '굿스포츠', 6000);
INSERT INTO Book VALUES(7, '야구의 추억', '이상미디어', 20000);
INSERT INTO Book VALUES(8, '야구를 부탁해', '이상미디어', 13000);
INSERT INTO Book VALUES(9, '올림픽 이야기', '삼성당', 7500);
INSERT INTO Book VALUES(10, 'Olympic Champions', 'Pearson', 13000);

INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스타', '000-5000-0001');
INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001');
INSERT INTO Customer VALUES (3, '장미란', '대한민국 강원도', '000-7000-0001');
INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001');
INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전', NULL);

INSERT INTO Orders VALUES (1, 1, 1, 6000, TO_DATE('2020-07-01','yyyy-mm-dd'));
INSERT INTO Orders VALUES (2, 1, 3, 21000, TO_DATE('2020-07-03','yyyy-mm-dd'));
INSERT INTO Orders VALUES (3, 2, 5, 8000, TO_DATE('2020-07-03','yyyy-mm-dd'));
INSERT INTO Orders VALUES (4, 3, 6, 6000, TO_DATE('2020-07-04','yyyy-mm-dd'));
INSERT INTO Orders VALUES (5, 4, 7, 20000, TO_DATE('2020-07-05','yyyy-mm-dd'));
INSERT INTO Orders VALUES (6, 1, 2, 12000, TO_DATE('2020-07-07','yyyy-mm-dd'));
INSERT INTO Orders VALUES (7, 4, 8, 13000, TO_DATE('2020-07-07','yyyy-mm-dd'));
INSERT INTO Orders VALUES (8, 3, 10, 12000, TO_DATE('2020-07-08','yyyy-mm-dd'));
INSERT INTO Orders VALUES (9, 2, 10, 7000, TO_DATE('2020-07-09','yyyy-mm-dd'));
INSERT INTO Orders VALUES (10, 3, 8, 13000, TO_DATE('2020-07-10','yyyy-mm-dd'));

CREATE TABLE Imported_Book (
bookid NUMBER,
bookname VARCHAR(40),
publisher VARCHAR(40),
price NUMBER(8)
);

INSERT INTO Imported_Book VALUES(21, 'Zen Golf', 'Pearson', 12000);
INSERT INTO Imported_Book VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000);

COMMIT;

데이터 정의어 DDL

CREATE & DROP (테이블 생성 및 삭제)

다른 테이블에서 삭제하려는 테이블의 기본키를 참조하고 있다면 삭제가 되지 않는다.

create table newBook(
bookid          number,
bookname        varchar(20),
publisher       varchar(20),
price           number);

drop table newBook;

create table newBook(
bookid          number,
bookname        varchar(20),
publisher       varchar(20),
price           number,
primary key     (bookid));

create table newBook(
bookid          number,
bookname        varchar(20),
publisher       varchar(20),
price           number,
primary key     (bookid, publisher));

drop table newBook;

create table newBook(
bookname        varchar(20) not null,
publisher       varchar(20) unique,
price           number default 10000 check(price > 1000),
primary key     (bookname, publisher));

create table NewCustomer(
custid       number      primary key,
name         varchar(40),
address         varchar(40),
phone         varchar(30));

create table NewOrders(
orderid      number,
custid       number      not null,
bookid       number      not null,
saleprice    number,
orderdate    date,
primary key (orderid),
foreign key (custid) references NewCustomer(custid) on delete cascade);

alter문

drop table newBook;

create table newBook(
bookid          number,
bookname        varchar2(20),
publisher       varchar2(20),
price           number);

-- isbn 속성 추가 (자료형 : varchar2(13))
alter table newBook add isbn varchar2(13);

-- isbn 속성 수정 (자료형 : number);
alter table newBook modify isbn number;

-- isbn 속성 삭제;
alter table newBook drop column isbn;

-- bookid 속성 제약조건 추가;
alter table newBook modify bookid number not null;

-- bookid 속성 기본키로 변경;
alter table newBook add primary key(bookid);

--  table 삭제
drop table NewOrders;
drop table NewCustomer;
drop table newBook;

데이터 조작어 DML

insert 문

insert into book(bookid, bookname, publisher, price)
    values(11, '스포츠 의학', '한솔의학서적', 90000);
    
insert into book(bookid, bookname, publisher)
    values(14, '스포츠 의학', '한솔의학서적');
    
insert into book(bookid, bookname, publisher, price)
    select bookid, bookname, publisher, price
    from imported_book;

update 문

update customer
set address='대한민국 부산'
where custid=5;

update customer
set address=(select address
             from customer
             where name='김연아')
where name='박세리';

delete 문

delete from customer
where custid=5;

select * from customer;

  • 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름

    SELECT publisher FROM Customer, Orders, Book WHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid AND name LIKE ‘박지성’;

두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름

SELECT name 
FROM Customer c1 
WHERE 2 >= (
    SELECT COUNT(DISTINCT publisher) 
    FROM Customer, Orders, Book 
    WHERE Customer.custid=Orders.custid 
    AND Orders.bookid=Book.bookid 
    AND name LIKE c1.name
);

전체 고객의 30% 이상이 구매한 도서

SELECT bookname 
FROM Book b1 
WHERE (
    (
        SELECT COUNT(Book.bookid) 
        FROM Book, Orders 
        WHERE Book.bookid=Orders.bookid 
        AND Book.bookid=b1.bookid
    ) >= 0.3 * (
        SELECT COUNT(*) 
        FROM Customer
    )
);

새로운 도서 (‘스포츠 세계’, ‘대한미디어’, 10000원)이 마당서점에 입고되었다. 삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보시오.

INSERT INTO BOOK VALUES(11, '스포츠 세계', '대한미디어', 10000);

‘삼성당’에서 출판한 도서를 삭제하시오.

DELETE FROM Book WHERE publisher LIKE '삼성당';

‘이상미디어’에서 출판한 도서를 삭제하시오. 삭제가 안 될 경우 원인을 생각해보시오.

DELETE FROM Book WHERE publisher LIKE '이상미디어';

출판사 ‘대한미디어’를 ‘대한출판사’로 이름을 바꾸시오.

UPDATE Book SET publisher='대한출판사' WHERE publisher LIKE '대한미디어';

(테이블 생성) 출판사에 대한 정보를 저장하는 테이블 Bookcompany(name, address, begin)를 생성하고자 한다. name은 기본키며 VARCHAR(20), address는 VARCHAR(20), begin은 DATE 타입으로 선언하여 생성하시오.

CREATE TABLE Bookcompany (name VARCHAR(20) PRIMARY KEY, address VARCHAR(20), begin DATE);

(테이블 수정) Bookcompany 테이블에 인터넷 주소를 저장하는 webaddress 속성을 VARCHAR(30)으로 추가하시오.

ALTER TABLE Bookcompany ADD webaddress VARCHAR(30);

Bookcompany 테이블에 임의의 투플 name=한빛아카데미, address=서울시 마포구, begin=1993-01-01, webaddress=http://hanbit.co.kr를 삽입하시오.

INSERT INTO Bookcompany VALUES ('한빛아카데미', '서울시 마포구', '1993-01-01', '<http://hanbit.co.kr>');

다음 EXISTS 질의의 결과를 보이시오.

SELECT *
FROM Customer c1
WHERE NOT EXISTS (SELECT *
    FROM Orders c2
    WHERE c1.custid=c2.custid);
  1. 질의 결과 : 5 박세리 대한민국 대전 (null)
  2. NOT을 지우고 난 후 질의 결과 : 박세리의 데이터를 제외한 전부

[사원 데이터베이스] Dept는 부서(Department) 테이블로 deptno(부서번호), dname(부서이름), loc(위치, location)으로 구성되어 있다. Emp는 사원(Employee) 테이블로 empno(사원번호), ename(사원이름), job(업무), mgr(팀장번호, manager), hiredate(고용날짜), sal(급여, salary), comm(커미션금액, commission), deptno(부서번호)로 구성되어 있다. 밑줄 친 속성은 기본키이고 Emp의 deptno는 Dept의 deptno를 참조하는 외래키이다. 사원 데이터베이스는 demo_scott, sql 스크립트를 실행하여 설치하도록 한다.

Dept(deptno Number(2), dname VARCHAR2(14), loc VARCHAR2(13))
Emp(empno Number(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2))

(1) 사원의 이름과 업무를 출력하시오. 단, 사원의 이름은 ‘사원이름’, 업무는 ‘사원업무’ 머리글이 나오도록 출력한다.

(2) 30번 부서에 근무하는 모든 사원의 이름과 급여를 출력하시오.

(3) 사원번호와 이름, 현재 급여, 증가된 급여분(열 이름은 ‘증가액’), 10% 인상된 급여(열 이름은 ‘인상된 급여’)를 사원번호 순으로 출력하시오.

(4) ‘S’로 시작하는 모든 사원과 부서번호를 출력하시오.

(5) 모든 사원의 최대 및 최소 급여, 합계 및 평균 급여를 출력하시오. 열 이름은 각각 MAX, MIN, SUM, AVG로 한다. 단, 소수점 이하는 반올림하여 정수로 출력한다.

(6) 업무 이름과 업무별로 동일한 업무를 하는 사원의 수를 출력하시오. 열 이름은 각각 ‘업무’와 ‘업무별 사원수’로 한다.

(7) 사원의 최대 급여와 최소 급여의 차액을 출력하시오.

(8) 30번 부서의 사원 수와 사원들 급여의 합계와 평균을 출력하시오.

(9) 평균 급여가 가장 높은 부서의 번호를 출력하시오.

(10) 세일즈맨(SALESMAN)을 제외하고, 각 업무별 사원의 총급여가 3,000 이상인 각 업무에 대해서, 업무명과 각 업무별 평균 급여를 출력하시오. 단 평균 급여의 내림차순으로 출력한다.

(11) 전체 사원 가운데 직속상관이 있는 사원의 수를 출력하시오.

(12) Emp 테이블에서 이름, 급여, 커미션(comm) 금액, 총액(sal*12+comm)을 구하여 총액이 많은 순서대로 출력하시오. 단, 커미션이 NULL 인 사람은 제외한다.

(13) 각 부서별로 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무 이름, 인원수를 출력하시오.

(14) 사원이 1명도 없는 부서의 이름을 출력하시오.

(15) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하시오.

(16) 사원번호가 7400 이상 7600 이하인 사원의 이름을 출력하시오.

(17) 사원의 이름과 사원의 부서이름을 출력하시오.

(18) 사원의 이름과 팀장(mgr)의 이름을 출력하시오.

(19) 사원 SCOTT보다 급여를 많이 받는 사람의 이름을 출력하시오.

(20) 사원 SCOTT이 일하는 부서번호 혹은 DALLAS에 있는 부서번호를 출력하시오.

  1. 사원의 이름과 업무

     SELECT ename AS "사원이름", job AS "사원업무" FROM Emp;

  2. 30번 부서의 사원 이름과 급여

     SELECT ename, sal FROM Emp WHERE deptno = 30;

  3. 사원별 현재 급여, 증가액, 인상된 급여

     SELECT empno, ename, sal, sal * 0.1 AS "증가액", sal * 1.1 AS "인상된 급여" FROM Emp ORDER BY empno;

  4. ‘S’로 시작하는 사원과 부서번호

     SELECT ename, deptno FROM Emp WHERE ename LIKE 'S%';

  5. 모든 사원의 급여 통계

     SELECT ROUND(MAX(sal)) AS "MAX", ROUND(MIN(sal)) AS "MIN", ROUND(SUM(sal)) AS "SUM", ROUND(AVG(sal)) AS "AVG" FROM Emp;

  6. 업무별 사원 수

     SELECT job AS "업무", COUNT(*) AS "업무별 사원수" FROM Emp GROUP BY job;

  7. 최대 급여와 최소 급여의 차액

     SELECT MAX(sal) - MIN(sal) FROM Emp;

  8. 30번 부서의 사원 수, 급여 합계와 평균

     SELECT COUNT(*), SUM(sal), AVG(sal) FROM Emp WHERE deptno = 30;

  9. 평균 급여가 가장 높은 부서 번호

     SELECT deptno FROM Emp GROUP BY deptno ORDER BY AVG(sal) DESC FETCH FIRST 1 ROW ONLY;

  10. 세일즈맨을 제외한 업무별 평균 급여

    SELECT job, AVG(sal) FROM Emp WHERE job != 'SALESMAN' GROUP BY job HAVING SUM(sal) >= 3000 ORDER BY AVG(sal) DESC;

  11. 직속 상관이 있는 사원의 수

    SELECT COUNT(*) FROM Emp WHERE mgr IS NOT NULL;

  12. 이름, 급여, 커미션, 총액(순서대로)

    SELECT ename, sal, comm, (sal * 12 + comm) AS total FROM Emp WHERE comm IS NOT NULL ORDER BY total DESC;

  13. 부서별, 업무별 인원수

    SELECT deptno, job, COUNT(*) FROM Emp GROUP BY deptno, job;

  14. 사원이 없는 부서 이름

    SELECT dname FROM Dept WHERE deptno NOT IN (SELECT deptno FROM Emp);

  15. 같은 업무를 하는 사람이 4명 이상인 업무

    SELECT job, COUNT(*) FROM Emp GROUP BY job HAVING COUNT(*) >= 4;

  16. 사원번호 7400 이상 7600 이하인 사원 이름

    SELECT ename FROM Emp WHERE empno BETWEEN 7400 AND 7600;

  17. 사원 이름과 부서 이름

    SELECT E.ename, D.dname FROM Emp E JOIN Dept D ON E.deptno = D.deptno;

  18. 사원 이름과 팀장 이름

    SELECT E1.ename, E2.ename AS "mgr_name" FROM Emp E1 JOIN Emp E2 ON E1.mgr = E2.empno;

  19. SCOTT보다 급여를 많이 받는 사람의 이름

    SELECT ename FROM Emp WHERE sal > (SELECT sal FROM Emp WHERE ename = 'SCOTT');

  20. SCOTT이 일하는 부서 번호 또는 DALLAS에 있는 부서 번호

    SELECT deptno FROM Dept WHERE deptno = (SELECT deptno FROM Emp WHERE ename = 'SCOTT') OR loc = 'DALLAS';