[오라클] SQL

데이터베이스(Database)

데이터 - 현실세계에서 단순히 관찰 또는 측정을 통해서 수집된 사실이나 값

정보(Information) - 상황에 따른 적절한 의사 결정을 할 수 있게 하는 지식  - 데이터의 유효한 해석이나 데이터 상호 간의 관계를 말하는 것 - 데이터를 처리해서 얻어진 결과

데이터베이스 관리 시스템(DBMS) - 방대한 양의 데이터를 편리하게 관리하고, 효율적으로 저장하고 검색할 수 있는 환경을 제공해주는 소프트웨어

SQL  = Structured Query Language
- DB 에 접근해서 처리하는 명령어들
- 국제 표준

제약조건(constraint)

제약조건	의미
Primary key (주키)	테이블  내의 각 레코드를 구별하기 위한 유일한 값
Foreign Key (외래키)	관계 설정을 위해 필요 (참조 무결성) : delete cascade
Unique	유일한 값을 갖도록 ( 주민번호, 폰 번호)
Not Null	빈 값이면 안되는 것 (이름)
Check	데이터의 값을 한정시키는 용도 (부동산, 성별, 학년)
default	데이터가 없을 때 삽입되는 기본값 : 제약조건은 아니지만 제약 조건처럼 사용할 수 있다.

 

hr 계정의 기본 테이블 조회해보기

SELECT * FROM COUNTRIES;
SELECT * FROM REGIONS;
SELECT * FROM DEPARTMENTS;
SELECT * FROM EMPLOYEES;

특정 명령문은 마우스로 잡고 실행하거나  세미콜론으로 구분짓는다.

 

명령어 종류

명령어	종류
DML (조작어)  : 각각의 레코드를 처리하는 명령문	Insert , Select(조회), Delete, Update
DDL (정의어) : 테이블을 포함한 여러 객체 대상 명령문	테이블, 유저, 뷰, 시노님, 인덱스 ... 등을 Alter(변경), Create(생성), Drop(삭제), Truncate
TCL : 트랜잭션의 데이터와 관련된 명령문	트랜잭션(Transaction) 처리 해주는 명령 Commit (저장), RollBack(취소), Savepoint (RollBack할 포인트를 만듦)
DCL(제어어) :데이터 사용 권한과 관련된 명령문	Grant(권한 부여), Revoke(권한 취소)

 

오라클에서 다루는 객체의 종류

테이블 (Table)

유저 (User)

뷰(View)

인덱스(Index)

시퀀스(Sequence)

시노님(Synonym)

 

조회 명령문의 기본 

SELECT [DISTINCT | (ALL)] 컬럼명들 (*)
FROM 테이블명
ORDER BY 컬럼명 [DESC | (ASC)];

DESC = 내림차순, ASC = 오름차순 이다.

 

[Q] 부서 번호를 조회하시오.

SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID DESC;

 

[Q] 이름, 급여, 부서번호를 이름순으로 오름차순하여 조회하시오

select FIRST_NAME as 이름, SALARY as 급여, DEPERTMENT_ID as 부서번호
from EMPLOYEES
order by FIRST_NAME;

 

연산자의 종류

연산자	종류
산술 연산자	+ - * /
문자열 결합 연산자	||
비교 연산자  (WHERE 조건절 뒤에서 쓰임)	= !=  > < >= <=
논리 연산자	AND, OR, NOT
NULL 연산자	컬럼명 IS NULL  / 컬럼명 IS NOT NULL
SQL 연산자	컬럼명 BETWEEN 값 1 AND 값 2 (AND 연산과 동일) 컬럼명 IN (값 1, 값2) (OR 연산과 동일)
LIKE 연산자와 와일드 카드	컬럼내에 특정 단어나 문자가 포함되어 있는지 조회할 때  % : 문자 0개 이상을 대신 _ : 문자 1개를 대신 SELECT 절의 컬럼명에서 사용한 * 도 와일드 카드의 일종

 

[Q] 직원의 월급인 5%를 보너스로 지급하고자 한다. (직원의 사원번호, 이름, 급여, 보너스, 합계)

select employee_id, first_name, salary, salary*0.05 as bonus, salary+salary as total
from employees;

 

[Q]  직원의 이름 뒤에 '님' 을 붙여 이름이라는 별칭으로 조회하시오.

(힌트)  오라클에서 문자열은 작은 따옴표로 사용한다.

select first_name || '님' as "이름"
from employees;

 

[Q]  이름과 성을 붙여 조회하시오 (예: Steven King ) - 별칭은 Name. 이름순으로 조회

select first_name || ' ' || last_name as name
from employees
order by name;  -- 또는 첫 번째 컬럼의 순서 1 도 가능

 

조건절의 사용

특정 조건에 맞는 데이터만 조회할때 사용

SELECT [DISTINCT | (ALL)] 컬럼명들 (*)
FROM 테이블명
WHERE 조건절
ORDER BY 컬럼명 [DESC |(ASC)];   -- ORDER BY  명령의 마지막에

 

[Q]  급여가 1000 이상인 직원의 사원번호, 이름, 급여, 직급을 조회하시오.(급여의 내림차순으로 )

SELECT employee_ID, FIRST_NAME, SALARY, JOB_ID
FROM employees
WHERE SALARY >= 1000
ORDER BY SALARY DESC;

 

[Q] 사원 번호, 이름, 급여, 부서번호를 조회하는데 부서번호가 80번인 데이터를 조회하시오.

SELECT employee_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM employees
WHERE DEPARTMENT_ID = 80
ORDER BY SALARY DESC;

 

[Q] 급여가 5000 이상 10000 이하인 직원의 사원번호, 이름, 급여, 직급을 조회하시오.

SELECT employee_ID, FIRST_NAME, SALARY, JOB_ID
FROM employees
WHERE 5000 <= SALARY AND SALARY <= 10000 ;

 

[Q] 부서 번호가 10 이거나 50 인 부서에 속한 부서의 직원들의 이름과 부서번호를 조회하시오

SELECT employee_ID, FIRST_NAME,department_id 
FROM employees
WHERE 10= department_id OR department_id = 50 ;

 

[Q]  부서 번호가 10 이거나 50 이 아닌 부서에 속한 부서의 직원들의 이름과 부서번호를 조회하시오

SELECT employee_ID, FIRST_NAME,department_id 
FROM employees
WHERE NOT(10 = department_id OR department_id = 50) ;

 

[Q] 부서 번호가 10 이거나 50 이 아닌 부서에 속한 부서의 직원들의 이름과 부서번호를 조회하시오.

부서별로 오름차순

SELECT employee_ID, FIRST_NAME,department_id 
FROM employees
WHERE NOT(10 = department_id OR department_id = 50) 
ORDER BY department_id ;

 

[Q]  이름, 급여, 커미션비율을 조회하시오

SELECT FIRST_NAME, SALARY, commission_pct
FROM EMPLOYEES;

 

[Q]  이름, 급여, 커미션비율을 조회하시오. 단, 커미션 값이 있는 사람만

SELECT FIRST_NAME, SALARY, commission_pct
FROM EMPLOYEES
-- WHERE commission_pct = NULL;  --조회되지 않는다.
WHERE COMMISSION_PCT IS NOT NULL;

 

[Q] 이름, 급여, 커미션비율을 조회하시오. 단, 커미션 값이 없는 사람만

SELECT FIRST_NAME, SALARY, commission_pct
FROM EMPLOYEES
-- WHERE commission_pct = NULL;  --조회되지 않는다.
WHERE COMMISSION_PCT IS NULL;

 

[Q]  매니저가 없는 사람의 이름과 직급 조회

SELECT FIRST_NAME, JOB_ID
FROM employees
WHERE manager_id IS NULL;

 

[Q]  이름, 급여, 커미션 금액, 총 수령액을 조회하시오 (단, 커미션이 있는 사람만)

컬럼명은 이름, 급여, 커미션 금액, 총 수령액 이다.

SELECT FIRST_NAME AS 이름, SALARY AS 급여 , SALARY*COMMISSION_PCT AS 커미션금액, 
        SALARY + (SALARY*COMMISSION_PCT) AS 총수령액
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

 

SQL 연산자와 LIKE 연산자

[Q] 급여가 5000 이상 10000 이하인 직원의 사원번호, 이름, 급여, 직급을 조회하시오

SELECT employee_ID, FIRST_NAME, SALARY, JOB_ID
FROM employees
WHERE SALARY BETWEEN 5000 AND 10000 ;

 

[Q] 부서 번호가 10 이거나 50인 부서에 속한 부서의 직원들의 이름과 부서번호를 조회하시오

SELECT employee_ID, FIRST_NAME,department_id 
FROM employees
WHERE DEPARTMENT_ID IN (10,50);

 

[Q] 부서 번호가 10 이거나 50 이 아닌 부서에 속한 부서의 직원들의 이름과 부서번호를 조회하시오

ELECT employee_ID, FIRST_NAME,department_id 
FROM employees
WHERE DEPARTMENT_ID NOT IN (10,50);

 

[Q] 급여가 7000을 초과하고 이메일이 'SKING' 인 직원의 이름, 전화번호, 이메일을 조회

SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
FROM EMPLOYEES 
WHERE SALARY > 7000 AND EMAIL = 'SKING';

 

날짜 데이터

직접 입력, 연산시 비교 연산이 가능하다.

[Q] 직원의 이름 과 입사일을 입사일 순으로 조회하시오

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE;

 

[Q] 입사년도가 07년도에 입사한 직원의 이름과 입사일을 조회하시오 (입사일 순)

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '07/01/01' AND '07/12/31'
ORDER BY HIRE_DATE;

 

[Q] 입사년도가 07년도 이전에 입사한 직원의 이름과 입사일을 조회하시오 (입사일 순)

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE < '07/01/01'
ORDER BY HIRE_DATE;

 

[Q] 날짜데이터를 산술연산해봅시다.

SELECT FIRST_NAME, HIRE_DATE,HIRE_DATE+25
FROM EMPLOYEES;

 

LIKE 연산

[Q] 이름이 'J'로 시작하는 직원을 조회하시오

SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'J%';

 

[Q] 이름이 'n'으로 끝나면서 이름의 길이가 5인 직원을 조회하시오.

SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '____n';

 

[Q] 이름이 'J'로 시작하면서 이름의 길이가 5인 직원을 조회하시오.

SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'J____';

 

LIKE 와 날짜

[Q] 07 년도에 입사한 직원의 이름과 입사일을 조회하시오

select first_name, hire_date
from employees
where hire_date like '07%'
order by hire_date;

 

[Q] 1월에 입사한 직원의 이름과 입사일을 조회하시오

select first_name, hire_date
from employees
where hire_date like '%/01/%'  -- '__01__' 도 가능
order by hire_date;