Database/Oracle

[국비] SQL 내용정리 Day01

tpleehan 2021. 12. 5. 21:02

관계형 데이터베이스

관계형 데이터베이스는 관계들의 모음 또는 이차원 테이블의 모음이다.

파일 시스템과 데이터베이스 시스템의 차이점

  • 데이터의 무결성을 지켜준다.
  • 데이터의 공유
  • 데이터의 정확성과 일관성
  • 중복성의 제거
  • 불일치를 피할 수 있다.
  • 표준화가 가능하다.
  • 데이터의 독립성 보장 -개념화 기법 발달
  • 각 테이블들은 고유한 이름을 갖는다.
  • 중복 문제의 해결 기법 제공: 정규형
  • 데이터의 조작언어의 발달: SQL
  • 관계형 데이터베이스는 테이블(table)들의 모음으로 구성
  • 각 행은 일련의 값들 사이의 관계(relationship)

관계형 데이터베이스는 SQL(Structured Query Language)문을 사용하여 이용되고 수정한다.

SQL문 이란?

데이터베이스로부터 데이터를 조회, 삭제, 수정, 변경 등의 작업을 수행할 때 사용하는 언어이다.

ER 모델링의 장점

  • 조직에 대한 정보를 정확하고 자세하게 문서화할 수 있다.
  • 정보 요구사항의 범위를 명확히 기술할 수 있다.
  • 데이터베이스 설계를 쉽게 이해할 수 있는 표본을 제공할 수 있다.
  • 복수 응용프로그램의 통합화를 위한 효과적 프레임워크를 제공한다.

일반적 개념 모델링 DB객체
데이터의 집합, relation,
관계집합(relationship set)		 개체집합(Entity set)
엔티티(Entity)		 테이블(table)
관계집합 중 어떤 행(row) 튜플(Tuple) 레코드(Record)
관계집합 중 어떤 열(column) 속성(Attribute) 필드(Filed)
  1. 후보키 (Cadidate key)
    • 행(튜플)을 유일하게 식별할 수 있는 키
    • 기본키가 될 수 있는 후보를 후보키라고 한다.
  2. 기본키 (Primary key)
    • 후보키 중에서 선택한 주가 되는 키
    • 행(튜플)을 유일하게 식별할 수 있는 키
    • not null, 중복 X
  3. 대체키 (Alternate key)
    • 후보키가 둘 이상일 때 기본키를 제외한 나머지 후보키
  4. 슈퍼키 (Super key)
    • 테이블(relation) 내에 있는 속성들의 집합으로 구성된 키(2개로 이루어진 키)
  5. 외래키 (Foreign key)
    • 참조되는 테이블(릴레이션)의 기본키와 대응되어 참조관계를 표현하는 키
    • 외래키로 지정되면 참조 테이블의 기본키에 없는 값은 입력할 수 없다.

SQL 계정 생성 및 권한 부여

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

SQL 문장

문장 설명
DML SELECT 데이터베이스로부터 데이터를 검색(조회)한다.
INSERT DML(Data Manipulation Language)로 분류된다.
개별적으로 데이터베이스 테이블에서 새로운 행을 입력(INSERT)하고, 기존의 행을 변경(UPDATE)하며, 기존의 행을 제거(DELETE) 한다.
UPDATE
DELETE
DDL CREATE DDL(Data Definition Language)로 분류된다.
테이블로부터 데이터 구조를 생성(CREATE), 변경(ALTER), 제거(DROP)한다.
또는 이름을 변경(RENAME)하거나 구조만 남기고 모든 데이터를 삭제(TRUNCATE)한다.
ALTER
DROP
RENAME
TRUNCATE
TCL COMMIT TCL(Transaction Control Language)로 분류된다.
DML 명령문으로 만든 변경을 관리하며, 데이터 변경은 논리적 트랜잭션으로 함께 그룹화 될 수 있다.
ROLLBACK
SAVEPOINT
DCL GRANT DCL(Data Control Language)로 분류된다.
데이터베이스에 접근하고 객체들을 사용할 수 있도록 권한을 주거나 회수한다.
REVOKE

Selection

  • 질의에 대해 리턴하고자 하는 테이블의 행을 선택하기 위해 SQL의 selection 기능을 사용할 수 있다. 보고자 하는 행을 선택적으로 제한하기 위해 다양한 방법을 사용할 수 있다.

Join

  • 공유 테이블 양쪽의 열에 대해 링크를 생성하여 다른 테이블에 저장되어 있는 데이터를 함께 가져오기 위해 SQL의 join 기능을 사용할 수 있다.

 

SELECT [DISTINCT] { * | column [[AS] alias], ... }

FROM table;

  • SELECT: 하나 이상의 열을 나열한다.
  • DISTINCT: 중복을 제거한다.
  • *: 모든 열을 선택한다.
  • coulmn: 명명된 열을 선택한다.
  • AS: 열 별칭(alias)을 지정한다.
  • alias: 선택된 열을 다른 이름으로 변경한다.
  • FROM table: 열을 포함하는 테이블을 명시한다.
SELECT *
FROM employees;

SELECT employee_id, first_name, last_name
FROM employees;

SELECT email, phone_number, hire_date
FROM employees;

  • 컬럼을 조회하는 위치에서 * / + - (사칙연산) 기호를 연산해서 사용할 수 있다.
SELECT employee_id, first_name, last_name, salary, salary + salary * 0.1
FROM employees;

SELECT department_id, commission_pct
FROM employees;
  • alias (컬럼명의 이름을 변경해서 조회)

SELECT first_name as 이름, last_name as 성, salary as 급여
FROM employees;

  • 홑따옴표로 문자를 표현하고, 문자열 안에 홑따옴표를 표현하려면 ''를 두번 연속으로 사용한다.
  • ||로 연결할 수 있다.
SELECT first_name || ' ' || last_name || '''s salary is $' || salary as 급여내역
FROM employees;

  • distinct (중복 행 제거)
SELECT DISTINCT department_id
FROM employees;

  • ROWNU: 쿼리에 의해 반환되는 행 번호를 출력
  • ROWID: 데이터베이스에서 행의 주소값을 반환
SELECT ROWNUM, ROWID, employee_id
FROM employees;

  • WHERE절 비교 (데이터 값은 대/소문자를 구분한다.)
SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = 'IT_PROG';

SELECT *
FROM employees
WHERE last_name = 'king';

SELECT *
FROM employees
WHERE last_name = 'King';

SELECT *
FROM employees
WHERE department_id = 90;

SELECT *
FROM employees
WHERE salary >= 15000;

SELECT *
FROM employees
WHERE hire_date = '04/01/30';

  • 데이터 행 제한(BETWEEN, IN, LINK)
SELECT *
FROM employees
WHERE salary BETWEEN 15000 AND 20000;

SELECT *
FROM employees
WHERE hire_date BETWEEN '03/01/01' AND '03/12/31';

SELECT *
FROM employees
WHERE salary >= 15000
AND salary <= 20000;

SELECT *
FROM employees
WHERE manager_id IN (100, 101, 102);

SELECT *
FROM employees
WHERE job_id IN ('IT_PROG', 'AD_VP');

SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '03%';

SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '%15';

SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '%05%';

SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '___05%';

SELECT *
FROM employees
WHERE manager_id is null;

SELECT *
FROM employees
WHERE commission_pct is null;

SELECT *
FROM employees
WHERE commission_pct is not null;

SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'FI_MGR'
AND salary >= 6000;

  • AND가 OR보다 연산순위가 높기 때문에 salary의 값이 다르게 조회할 수 있는 것을 확인할 수 있다.
SELECT *
FROM employees
WHERE (job_id = 'IT_PROG'
OR job_id = 'FI_MGR')
AND salary >= 6000;

  • 데이터의 정렬 (SELECT 구문의 가장 마지막에 배치된다.)
  • ASC: 오름차순(ascending)
  • DESC: 내림차순(descending)
SELECT *
FROM employees
ORDER BY hire_date ASC;

SELECT *
FROM employees
ORDER BY hire_date DESC;

SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
ORDER BY first_name ASC;

SELECT *
FROM employees
WHERE salary >= 5000
ORDER BY employee_id DESC;

SELECT first_name, salary * 12 as pay
FROM employees
ORDER BY pay ASC;

SQL Quiz01

1. 모든 사원의 사원번호, 이름, 입사일, 급여를 출력한다.
SELECT employee_id, first_name, hire_date, salary
FROM employees;
2. 모든 사원의 이름과 성을 붙여 출력한다. 열 별칭은 name로 지정한다.
SELECT first_name || ' ' || last_name as name
FROM employees;

3. 50번 부서 사원의 모든 정보를 출력한다.

SELECT *
FROM employees
WHERE department_id = 50;
4. 50번 부서 사원의 이름, 부서번호, 직무아이디를 출력한다.
SELECT first_name, department_id, job_id
FROM employees
WHERE department_id = 50;

5. 모든 사원의 이름, 급여 그리고 300달러 인상된 급여를 출력한다.
SELECT first_name, salary, salary + 300
FROM employees;
6. 급여가 10000보다 큰 사원의 이름과 급여를 출력한다.
SELECT first_name, salary
FROM employees
WHERE salary > 10000;

7. 보너스를 받은 사원의 이름과 직무, 보너스율을 출력한다.
SELECT first_name, job_id, commission_pct
FROM employees
WHERE commission_pct is not null;
8. 2003년도 입사한 사원의 이름과 입사일 그리고 급여를 출력한다. (BETWEEN 연산자 사용)
SELECT first_name, hire_date, salary
FROM employees
WHERE hire_date BETWEEN '03/01/01' AND '03/12/31';
9. 2003년도 입사한 사원의 이름과 입사일 그리고 급여를 출력한다. (LIKE 연산자 사용)
SELECT first_name, hire_date, salary
FROM employees
WHERE hire_date LIKE '03%';
10. 모든 사원의 이름과 급여를 급여가 많은 사원부터 적은 사원순서로 출력한다.
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
11. 위 질의를 60번 부서의 사원에 대해서만 질의한다. (컬럼: department_id)
SELECT first_name, salary
FROM employees
WHERE department_id = 60
ORDER BY salary DESC;
12. 직무아이디가 IT_PROG 이거나, SA_MAN인 사원의 이름과 직무아이디를 출력한다.
SELECT first_name, job_id
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'SA_MAN';
SELECT first_name, job_id
FROM employees
WHERE job_id IN ('IT_PROG', 'SA_MAN');
13. Steven King 사원의 정보를 "Steven King 사원의 급여는 24000달러 입니다" 형식으로 출력한다.
SELECT first_name || ' ' || last_name || '사원의 급여는 ' || salary || '달러 입니다' AS info
FROM employees
WHERE first_name = 'Steven'
AND last_name = 'King';

14. 매니저(MAN) 직무에 해당하는 사원의 이름과 직무아이디를 출력한다. (컬럼: job_id)
SELECT first_name, job_id
FROM employees
WHERE job_id LIKE '%MAN';
15. 매니저(MAN) 직무에 해당하는 사원의 이름과 직무아이디를 직무아이디 순서대로 출력한다.
SELECT first_name, job_id
FROM employees
WHERE job_id LIKE '%MAN'
ORDER BY job_id ASC;

함수

  • SQL의 함수를 사용해서 데이터 값을 간편하게 조작하여 사용할 수 있다.
    • 데이터 계산 수행
    • 개별적인 데이터 항목 수정
    • 행의 그룹에 대해 결과 조작
    • 출력을 위한 날짜와 숫자 형식 설정
    • 열의 데이터타입 변환

  • 단일행 함수
    • 단일행 함수에는 여러 유형들이 있으며, 단일 행에서만 적용이 가능하고 행별로 하나의 결과를 리턴한다.
      • 문자, 숫자, 날짜, 변환
  • 다중행 함수
    • 복수의 행을 조작해서 행의 그룹당 하나의 결과를 리턴한다.

  • 데이터 값을 조작한다.
  • 인수(argument)를 받고 하나의 결과를 리턴한다.
  • 리턴될 각각의 행에 적용된다.
  • 행별로 하나의 결과를 리턴한다.
  • 데이터타입을 수정할 수 있다.
  • 중첩(nested)될 수 있다.

  • 단일행 함수 종류에는 문자 함수, 숫자 함수, 날짜 함수, 변환 함수 등이 있다.
  • 문자 함수는 문자 입력을 받고 문자와 숫자 값 모두를 리턴할 수 있다.
  • 숫자 함수는 숫자 입력을 받고 숫자 값을 리턴한다.
  • 날짜 함수는 날짜 데이터타입의 값에 대해 수행한다.
    • 숫자를 리턴하는 MONTHS_BETWEEN 함수를 제외한 모든 날짜 함수는 날짜 데이터타입의 값을 리턴한다.
  • 변환 함수는 어떤 데이터타입의 값을 다른 데이터타입의 값으로 변환한다.
  • 그 외 함수에는 NVL, NVL2, LNNVL, NULLIF, COALESCE, DECODE 등이 있다.
  • lower(소문자), initcap(앞글자만 대문자), upper(대문자)
SELECT 'abcDEF', lower('abcDEF'), upper('abcDEF')
FROM dual;

  • dual이라는 테이블은 sys가 소유하는 오라클의 표준 테이블로 오직 한 행에 한 컬럼만 담고 있는 dummy 테이블이다.
  • 일시적인 산술 연산이나 날짜 연산 등을 위로 주로 사용되며, 모든 사용자가 접근할 수 있다.
SELECT last_name, lower(last_name), initcap(last_name) , upper(last_name)
FROM employees;

SELECT last_name
FROM employees
WHERE lower(last_name) = 'kim';

  • length(길이), instr(문자 찾기)
SELECT 'abcdef' AS ex, LENGTH('abcdef'), INSTR('abcdef', 'a')
FROM dual;

SELECT first_name, LENGTH(first_name), INSTR(first_name, 'a')
FROM employees;

  • substr(문자열 자르기), concat(문자 연결)
SELECT 'abcdef' AS ex, SUBSTR('abcdef', 1, 2), CONCAT('abc', 'def')
FROM dual;

SELECT first_name AS ex, SUBSTR(first_name, 1, 3), CONCAT(first_name, last_name)
FROM employees;

  • LPAD, RPAD (좌, 우측 지정문자열로 채우기)
SELECT LPAD('abc', 10, '*')
FROM dual;

SELECT RPAD('abc', 10, '*')
FROM dual;

  • LTRIM(), RTRIM(), TRIM() 공백 제거
  • LTRIM, RTRIM은 좌/우측에 지정한 문자 제거
SELECT LTRIM('javascript_java', 'java')
FROM dual;

SELECT RTRIM('javascript_java', 'java')
FROM dual;

SELECT TRIM('    java    ')
FROM dual;

  • REPLACE(문자열 치환)
SELECT REPLACE('my dream is a president','president','doctor')
FROM dual;

SELECT REPLACE(CONCAT('hello', 'world'), ' ', '')
FROM dual;

SQL Quiz02

문제 1.
EMPLOYEES 테이블에서 이름, 입사일자 컬럼으로 변경해서 이름순으로 오름차순 출력한다.
조건1) 이름 컬럼은 first_name, last_name을 붙여서 출력한다.
조건2) 입사일자 컬럼은 xx/xx/xx로 저장되어 있는데, xxxxxx 형태로 변경해서 출력한다.
SELECT 
    CONCAT(first_name, last_name) AS 이름,
    REPLACE(hire_date,'/','') AS 입사일자
FROM employees
ORDER BY 이름 ASC;

문제 2.
EMPLOYEES 테이블에서 phone_number 컬럼은 ###.###.#### 형태로 저장되어 있는데, 처음 세 자리는 숫자 대신 서울 지역번호 (02)를 붙여 전화번호를 출력한다.
SELECT 
    CONCAT('(02)', SUBSTR(phone_number, 4, LENGTH(phone_number))) AS phone_number
FROM employees;​
문제 3.
EMPLOYEES 테이블에서 job_id가 IT_PROG인 사원의 이름(first_name)과 급여(salary)를 출력한다.
조건1) 비교하기 위한 값은 소문자로 입력해야 한다. (힌트: lower 사용)
조건2) 이름은 앞 3문자까지 출력하고 나머지는 *로 출력한다. 이 열의 열 별칭은 name이다. (힌트: rpad와 substr 또는 substr 그리고 length 사용)
조건3) 급여는 전체 10자리로 출력하되 나머지 자리는 *로 출력한다. 이 열의 열 별칭은 salary이다. (힌트: lpad사용)
SELECT 
    RPAD(SUBSTR(first_name, 1, 3), LENGTH(first_name), '*') AS name,
    LPAD(salary, 10, '*') as salary
FROM employees
WHERE lower(job_id) = 'it_prog';​

숫자 함수

  • ROUND(반올림)
-- 소수점 1번째 위치 반올림, 0번째 위치 반올림, 좌측 1번째 위치 반올림
SELECT ROUND(3.1415, 1), ROUND(45.923, 0), ROUND(45.923, -1)
FROM dual;

  • TRUNC(절사)
  • 정해진 소수점 자리수까지 잘라낸다.
SELECT TRUNC(3.1415, 1), TRUNC(45.923, 0), TRUNC(45.923, -1)
FROM dual;

  • ABS(절대값)
SELECT ABS(-34)
FROM dual;

  • CEIL(올림), FLOOR(내림)
SELECT CEIL(3.14), FLOOR(3.14)
FROM dual;

  • MOD(나머지)
SELECT 10 / 2, MOD(10,2)
FROM dual;

날짜 함수

  • 오라클은 세기, 년, 월, 일, 시간, 분, 초의 내부 숫자형식으로 날짜를 저장한다.
  • 디폴트 날짜 형식은 'DD-MON-YY'이며, 시스템에 따라 'YY/MM/DD'가 될 수 있다.
  • SYSDATE는 현재의 날짜를 변환하는 함수이다.
  • SYSTIMESTAMP는 현재의 날짜와 시간을 반환하는 함수이다.
  • DUAL은 SYSDATE를 보기 위해 사용된 dummy 테이블이다.
SELECT sysdate
FROM dual;

SELECT systimestamp
FROM dual;

  • 날짜 연산
SELECT first_name, sysdate - hire_date
FROM employees;

SELECT first_name, hire_date, (sysdate - hire_date) / 7 AS week
FROM employees; -- 주수

SELECT first_name, hire_date, (sysdate - hire_date) / 365 AS week
FROM employees; -- 년수

-- 날짜 반올림
SELECT ROUND(sysdate)
FROM dual;

SELECT ROUND(sysdate, 'year')
FROM dual; -- 년 기준으로 반올림

SELECT ROUND(sysdate, 'month')
FROM dual; -- 월 기준으로 반올림

SELECT ROUND(sysdate, 'day')
FROM dual; -- 일 기준으로 반올림 (해당 주 일요일 날짜)

-- 날짜 절사
SELECT TRUNC(sysdate)
FROM dual;

SELECT TRUNC(sysdate, 'year')
FROM dual;

SELECT TRUNC(sysdate, 'month')
FROM dual;

SELECT TRUNC(sysdate, 'day')
FROM dual; -- 일 기준으로 절사 (해당 주 일요일 날짜 기준)

암시적 형변환

  • 값 할당(assignment) 시, 오라클은 자동으로 표와 같이 변환할 수 있다.
From To 비고
VARCHAR2 또는 CHAR NUMBER  표현식 계산의 경우
VARCHAR2 또는 CHAR DATE 표현식 계산의 경우
NUMBER VARCHAR2  
DATE VARCHAR2  
  • VARCHAR2 또는 CHAR를 NUMBER로 변환
  • VARCHAR2 또는 CHAR를 DATE로 변환
  • NUMBER를 VARCHAR2로 변환
  • DATE를 VARCHAR2로 변환

변환 함수 설명
TO_CHAR(number[, 'fmt']) 숫자를 포맷 모델 fmt를 사용하여 VARCHAR2 문자 스트링으로 변환한다.
TO_CHAR(date[, 'fmt']) 날짜를 포맷 모델 fmt를 사용하여 VARCHAR2 문자 스트링으로 변환한다.
TO_NUMBER(char[, 'fmt']) 숫자를 포함하는 문자 스트링을 숫자로 변환한다.
TO_DATE(char[, 'fmt']) 날짜를 나타내는 문자 스트링을 명시된 fmt에 따라서 날짜 값으로 변환한다. (fmt가 생략되면 DD-MON-YY 형식이다.)
  • 날짜를 문자로 TO_CHAR(값, 형식)
SELECT TO_CHAR(sysdate)
FROM dual;

SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH:MI:SS')
FROM dual;

SELECT TO_CHAR(sysdate, 'YY-MM-DD PM HH:MI:SS')
FROM dual;

-- 사용하고 싶은 문자를 ""를 묶어서 전달한다.
SELECT first_name, TO_CHAR(hire_date, 'YYYY"년" MM"월" DD"일"')
FROM employees;

  • 숫자를 문자로 TO_CHAR(값, 형식)
SELECT TO_CHAR(20000, '99999')
FROM dual;

-- 주어진 자릿수에 숫자를 모두 표기하지 못할 경우 #으로 표시된다.
SELECT TO_CHAR(20000, '9999')
FROM dual;

SELECT TO_CHAR(20000.21, '99999.9')
FROM dual;

SELECT TO_CHAR(20000.21, '99999.99')
FROM dual; -- 소수점 자리수 지정 가능.

SELECT TO_CHAR(20000, '99,999')
FROM dual;

SELECT TO_CHAR(salary, '$999,999') AS salary
FROM employees;

SELECT TO_CHAR(salary, 'L999,999') AS salary
FROM employees;

  • 문자를 숫자로 TO_NUMBER(값, 형식
SELECT '2000' + 2000 FROM dual; -- 자동 형변환

SELECT TO_NUMBER('2000') + 2000 FROM dual; -- 명시적 형 변환

SELECT '$3,300' + 2000 FROM dual; -- 에러
SELECT TO_NUMBER('$3,300', '$9,999')+ 2000 FROM dual;

  • 문자를 날짜로 변환하는 함수 TO_DATE(값, 형식)
SELECT TO_DATE('2021-11-23') FROM dual; -- 기본 패턴

SELECT sysdate - TO_DATE('2021-11-23') FROM dual; -- 날짜로 변환해야 연산이 가능

SELECT TO_DATE('2020/12/25', 'YY/MM/DD') FROM dual;

SELECT TO_DATE('2021-03-31 12:23:50', 'YYYY-MM-DD HH:MI:SS') FROM dual;

-- 주어진 문자열은 모두 변환해야 한다.
SELECT TO_DATE('2021-03-31 12:23:50', 'YYYY-MM-DD') FROM dual; -- 에러

SQL Quiz

- xxxx년 xx월 xx일 문자열 형식으로 변환한다.
- 조회 컬럼명은 dateInfo로 한다.
SELECT 
    TO_CHAR(
        TO_DATE('20050102', 'YYYY/MM/DD'), 'YYYY"년" MM"월" DD"일"'
    ) AS dateInfo 
FROM dual;​