**HR 샘플 스키마 설치
sqlplus / as sysdba
alter session set "_ORACLE_SCRIPT"=true;
@?/demo/schema/human_resources/hr_main.sql
hr
users
temp
$ORACLE_HOME/demo/schema/log/
**SQL
- DB에서 데이타를 추출할려면 sql select문을 사용해야 합니다.
- 테이블(실제저장소), 뷰(가상저장소) 라는 개체를 통해서 데이타를 보편적으로 추출합니다.
- 하나의 테이블에서 추출가능
- 여러개의 테이블에서 추출하기 위해서는 JOIN 문을 사용
Table format
table name : TBN1
column
--col1----|---col2---|---col3---|--col4---|---col5---
<= row
------|------|------|-----|------
------|------|------|-----|------
------|------|------|-----|------
------|------|------|-----|------
Display 의 모습
-------------------------------
SELECT col1,col2,col3,col4,col5
FROM TBN1 ;
-------------------------------
SELECT col3,col5,col1,col4,col2
FROM TBN1 ;
-------------------------------
SELECT col3,col2
FROM TBN1 ;
-------------------------------
SELECT * (모든 컬럼)
FROM TBN1 ;
*SQL 문 작성
-SQL문은 대소문자를 구분하지 않는다.
select *
FrOM tbn1 ;
- SQL문은 한줄, 또는 여러줄로 입력 가능
select * from tbn1;
select *
from tbn1;
-키워드는 약어를 사용해서 안된다.
sel * from tbn1;
**SQL
SELECT *
FROM hr.employees;
SELECT employee_id,last_name,first_name
FROM hr.employees;
SELECT last_name,first_name,salary
FROM hr.employees;
SELECT last_name,first_name,salary,
salary+300 as BONUS
FROM hr.employees;
**산술연산자 사용
-우선순위 규칙
-곱하기와 나누기는 더하기,빼기보다 우선한다.
-동일한 우선순위는 연산자 왼쪽에서 오른쪽으로 계산
-괄호는 기본 우선 순위보다 우선한다.
사원의 성, 급여 및 연간 총수입을 표시해 보세요.
SELECT last_name, salary,salary*12 as "Multi Salary"
FROM employees;
**Null 정의
-사용할 수 없거나, 할당되지 않거나, 정의되지 않은
알수 없는 값이다.
-공백과 0 하고는 다르다.
- null*0 오류발생
- null*1의 결과는 null
- null+문자의 결과는 문자
**별칭
-컬럼의 명을 별칭으로 대체한다.
-계산식 유용
-컬럼명 as 별칭명 ( 또는 컬럼명 별칭명)
-별칭명에 공백, 특수문자,대소문자을 구분할 경우
" " 로 묶는다.
SELECT last_name,12*salary*commission_pct Annual
FROM employees;
SELECT last_name,12*salary*commission_pct "Annual"
FROM employees;
SELECT last_name,12*salary*commission_pct "Annual Salary"
FROM employees;
SELECT last_name,12*salary*commission_pct AS "Annual Salary"
FROM employees;
**연결 연산자
-컬럼이나 문자열을 다른 컬럼에 연결한다.
-두개의 컬럼을 || 로 연결한다.
SELECT first_name||last_name as "Name"
FROM employees;
**리터럴 문자
-select절에 문자을 포함 할 수있다.
-작은 따옴표로 묶는다.
SELECT first_name||' '||last_name as "Name"
FROM employees;
SELECT last_name||'님 Did you have happy lunch?'
FROM employees;
SELECT last_name||q'[님 Do you have your's stuff?]'
FROM employees;
**중복 행 제거
-기본적으로 질의에 대한 결과는 중복되는 모든 행을 출력한다.
-중복된 행의 값을 하나의 값으로만 표현
SELECT job_id
FROM employees;
SELECT DISTINCT job_id
FROM employees;
**테이블 구조 파악
-테이블의 컬럼명, 데이타 타입 등의 정보를 출력
DESCRIBE tablename;
DESC tablename;
datatype
number(p,n); p는 최대자리수, n은 소숫점이하 자리수
varchar2(10); 10자리 문자열
date ; 날짜
**제한된 데이타
-원하는 행의 데이타만 출력
-조건절을 사용한다.
-SELECT... FROM...WHERE 조건
-WHERE절에 문자값은 대소문자 구별
-문자열은 ' '으로 열고 닫아야한다.
-숫자는 ' '이 필요없다.
-WHERE절은 FROM절 다음에 반드시 위치
FirstName이 Ellen의 샐러리을 출력
SELECT first_name, salary
FROM employees;
SELECT first_name, salary
FROM employees
WHERE first_name = 'Ellen';
SELECT first_name, salary
FROM employees
WHERE salary = 11000;
Q1]90번 부서에 있는 사용자의 성과 이름을 출력하세요
SELECT last_name, first_name
FROM employees
WHERE department_id = 90;
Q2]이름이 Steven King과 동일한 급여를 받는 사람들의
급여, 이름과 성을 출력하세요.
SELECT last_name,first_name,salary
FROM employees
WHERE first_name='Steven' and last_name='King';
값 : 24000
SELECT last_name,first_name,salary
FROM employees
WHERE salary = 24000;
Q2] 2007년 6월 2에 입사한 사원들의 이름과 성을 출력하세요.
**날짜
-날짜열도 문자열과 같이 ' ' 으로 열고 닫는다.
-대소문자 구별
-기본 날짜 형식 DD-MON-RR
SELECT first_name,last_name
FROM employees
WHERE hire_date = '02-06-07';
**비교 연산자
= ; 같음
> ; 보다 큼
>= ; 보다 크거나 같음
< ; 보다 작음
<= ; 보다 작거나 같음
<> ; 같지 않음
between...and.... ; 두 값 사이
IN ; 또는
LIKE ; 일치하는 패턴 검색(% ; 0개 이상의 문자열과 일치
_ ; 임의의 단일 문자와 일치
)
IS NULL ; 값이 NULL 인것
Q3]급여가 15000보다 많이 수령하는 사원의 이름, 성 그리고 급여를 출력하세요.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 15000;
SELECT first_name, last_name, salary
FROM employees
WHERE salary < 3000;
Q4]급여가 3000보다 많고 5000보다 작은 급여를 수령하는 사원의 이름, 성 그리고 급여를 출력하세요.
SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 3000 AND 5000;
SELECT first_name, last_name, salary
FROM employees
WHERE last_name BETWEEN 'King' AND 'Stephen';
Q5]급여가 3000 또는 3200 수령하는 사원의 이름, 성 그리고 급여를 출력하세요.
SELECT first_name, last_name, salary
FROM employees
WHERE salary = 3000 OR salary = 3200;
Q6]급여가 3000 또는 3200 또는 3300 수령하는 사원의 이름, 성 그리고 급여를 출력하세요.
SELECT first_name, last_name, salary
FROM employees
WHERE salary = 3000 OR salary = 3200 OR salary = 3300;
SELECT first_name, last_name, salary
FROM employees
WHERE salary IN (3000,3200,3300);
Q7]사원의 이름이 S로 시작하는 이름, 입사일과 급여를 출력하세요.
SELECT first_name, hire_date, salary
FROM employees
WHERE first_name LIKE 'S%';
% ; 0개 이상의 문자열과 일치
_ ; 임의의 단일 문자와 일치
SELECT first_name, hire_date, salary
FROM employees
WHERE first_name LIKE '%n';
SELECT first_name, hire_date, salary
FROM employees
WHERE first_name LIKE 'S%n';
Q8]최고경영자의 이름,성과 급여를 출력하세요.
SELECT first_name, last_name, salary
FROM employees
WHERE manager_id IS NULL;
**논리 연산자
AND ; 주어진 조건이 모두 참인것
OR ; 주어진 조건중 하나가 참인것
NOT ; 조건이 거짓인 경우 참
Q9] 급여가 9000 이고 이름이 A로 시작하는 사원의 이름,성 그리고 급여를 출력하세요.
SELECT first_name, last_name, salary
FROM employees
WHERE salary = 9000
AND first_name LIKE 'A%';
**우선 순위 규칙
-NOT 는 AND보다 우선 판단한다.
-AND 는 OR 보다 우선 판단한다.
(NOT > AND > OR)
Q9] 급여가 9000 이고 이름이 A 또는 D로 시작하는 사원의 이름,성 그리고 급여를 출력하세요.
SELECT first_name,last_name,salary
FROM employees
WHERE salary = 9000 AND first_name LIKE 'A%' OR salary = 9000 AND first_name LIKE 'D%';
SELECT first_name,last_name,salary
FROM employees
WHERE salary = 9000 AND (first_name LIKE 'A%' OR first_name LIKE 'E%');
**Order by 절
-검색된 행을 정렬합니다.
-오름차순 ; ASC (기본 값이다.)
-내림차순 ; DESC
-SELECT 절의 가장 마지막에 배치 합니다.
SELECT
FROM
WHERE
ORDER BY
SELECT first_name
FROM employees
ORDER BY first_name;
SELECT first_name
FROM employees
ORDER BY first_name DESC;
SELECT first_name,12*salary AS "Annual"
FROM employees
ORDER BY "Annual" DESC;
SELECT first_name,12*salary AS "Annual"
FROM employees
ORDER BY 2 DESC;
SELECT first_name,12*salary AS "Annual"
FROM employees
ORDER BY 1 DESC;
SELECT first_name,12*salary AS "Annual"
FROM employees
ORDER BY 1,2 DESC;
SELECT first_name,12*salary AS "Annual"
FROM employees
ORDER BY 1 DESC,2 ASC;
SELECT first_name,12*salary AS "Annual"
FROM employees
WHERE first_name = 'Steven' and last_name='King'
ORDER BY 1;
**오라클 함수
-데이타에 대한 계산 수행
-개별 데이터 항목 수정
-행 그룹 출력한 후 조작
-날짜, 숫자에대한 형식 지정
-열 데이터 타입 변환
**문자 함수
-대소문자 변환 함수
--LOWER, UPPER,INITCAP
-문자 조작 함수
--CONCAT,SUBSTR,LENGTH
SELECT 'Oracel SQL'
FROM dual;
SELECT LOWER('Oracle SQL')
FROM dual;
SELECT UPPER('Oracle SQL')
FROM dual;
Kevin Kim
SELECT first_name
FROM employees
WHERE first_name=INITCAP('steven');
SELECT INITCAP('how are you today?')
FROM dual ;
-CONCAT(두 개의 값을 연결하는 함수)
SELECT CONCAT('Steven','King')
FROM dual;
SELECT CONCAT('Steven','King')
FROM dual;
SELECT CONCAT(first_name,last_name)
FROM employees
WHERE LOWER(first_name) ='steven';
-LENGTH ; 길이의 값을 숫자로 반환
SELECT LENGTH('first_name AND last_name')
FROM dual;
-SUBSTR ; 지정된 길의 문자열을 추출한다.
SELECT SUBSTR('first_name AND last_name',1,7)
FROM dual;
-LPAD , RPAD ;지정자릿수에 부족한 부분을 특정 문자열로 채운다.
SELECT LPAD(12*salary,7,'*') as salary
FROM employees;
SELECT RPAD(12*salary,7,'*') as salary
FROM employees;
-REPLACE ; 지정된 문자열을 치환하는 것
SELECT REPLACE('first_name AND last_name','AND','and')
FROM dual;
** 숫자 함수
-ROUND ; 지정된 자릿수로 값을 반올림
-TRUNC ; 지정된 소수점 자릿수로 값을 잘라냄
-MOD ; 나눈 나머지 값을 반환
SELECT 42.195
FROM dual;
SELECT ROUND(42.195,1)
FROM dual;
SELECT ROUND(42.195,2)
FROM dual;
SELECT ROUND(42.195,3)
FROM dual;
SELECT TRUNC(42.195,1)
FROM dual;
SELECT TRUNC(42.195,2)
FROM dual;
SELECT TRUNC(42.195,3)
FROM dual;
SELECT MOD(43,1)
FROM dual;
SELECT MOD(43,2)
FROM dual;
SELECT MOD(43,3)
FROM dual;
SELECT last_name,salary,MOD(salary,5000)
FROM employees;
**날짜 함수
-오라클은 날짜를 저장할때
세기,년,월,일,시,분,초(20,19,04,25,11,10,45)
-기본 날짜 DD-MON-RR
--연도의 마지막 두 자릿수만 지정하면 21세기,20세기를
날짜로 저장할 수 있다.
SELECT hire_date
FROM employees
ORDER BY 1 ;
현재연도 지정된날짜 RR형식 YY형식
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095
지정된 두 자리 연도가 다음과 같을 경우
0-49 50-99
현재연도를 0-49 현재세기 이전세기
두자리수가
다음과 같을 50-99 이후세기 현재세기
경우
SELECT hire_date
FROM employees
WHERE hire_date > '07/03/24' ;
(현재 19, 지정 07 ; 현재세기)
SELECT hire_date
FROM employees
WHERE hire_date > '50/03/24' ;
(현재 19, 지정 50 ; 이전세기)
SELECT sysdate
FROM dual ;
(19/04/25)
**날짜 연산
날짜 + 숫자 = 날짜
날짜 - 숫자 = 날짜
날짜 - 날짜 = 일 수
날짜 + 숫자/24 = 날짜(날짜에 시간을 더하는 의미)
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees ;
SELECT last_name, TRUNC((SYSDATE-hire_date)/7,0) AS WEEKS
FROM employees ;
SELECT last_name,hire_date,hire_date+365 AS FutureDay
FROM employees ;
SELECT last_name,hire_date,hire_date-365 AS FutureDay
FROM employees ;
**날짜 조작 함수
MONTHS_BETWEEN 두 날짜 사이의 월수
ADD_MONTHS 날짜에 월을 추가
NEXT_DAY 지정된 날짜의 다음 날짜
LAST_DAY 월의 마지막 날
ROUND 날짜 반올림
TRUNC 날짜 짜름
SELECT sysdate,ADD_MONTHS(sysdate,100) AS ADDMONS
FROM dual ;
SELECT sysdate,MONTHS_BETWEEN(sysdate+100,sysdate) AS ADDMONS FROM dual ;
SELECT NEXT_DAY(sysdate,'금') FROM dual ;
SELECT sysdate, NEXT_DAY('01-apr-2019','friday') FROM dual ;
SELECT NEXT_DAY('95-09-05','금') FROM dual ;
**SQL Command Line으로 들어가서 system계정으로 접속해서 바꿔야 한다.(영구적 변경)
alter system set nls_date_format = 'YYYY-MM-DD' scope=spfile;
alter system set nls_timestamp_format = 'YYYY-MM-DD HH:MI:SS' scope=spfile;
위 구문을 입력한뒤에 데이터베이스를 껐다 켜야 적용된다.(Stop Database후 Start Database)
**변환 함수
-데이타 타입을 변환
-암시적으로 변환(WHERE salary='24000')\
varchar2, char ==> number
varchar2, char ==>date
number =>varchar2, char
date => varchar2, char
-명시적으로 변환
TO_NUMBER
TO_DATE
TO_CHAR
SELECT last_name,TO_CHAR(hire_date)
FROM employees ;
SELECT last_name,TO_CHAR(hire_date,'fmYYYY MON DD')
FROM employees ;
SELECT last_name,TO_CHAR(hire_date,'fmYYYY MON DD PM:MI:SS')
FROM employees ;
SELECT last_name,TO_CHAR(salary,'999,999.00') AS salary
FROM employees ;
SELECT hire_date,TO_DATE(hire_date,'DD-MM-RR')
FROM employees ;
SELECT hire_date,TO_CHAR(hire_date,'YYYY-MM-DD')
FROM employees ;
SELECT hire_date,TO_CHAR(hire_date,'DD/Mon YYYY')
FROM employees ;
SELECT hire_date,TO_CHAR(hire_date,'DD MM YYYY')
FROM employees ;
**중첩 함수
-중첩된 함수는 내부에 정의 함수부터 처리한다.
UPPER(CONCAT(SUBSTR()))
SUBSTR(last_name,1,5)
CONCAT(SUBSTR(last_name,1,5),'-KOR')
UPPPER(CONCAT(SUBSTR(last_name,1,5),'-KOR'))
SELECT UPPER(CONCAT(SUBSTR(last_name,1,15),salary)) AS LN
FROM employees;
**일반함수
-NVL(exp1,exp2) ; null값을 지정한한 값으로 변환
-NVL2(exp1,exp2,exp3); exp1이 널이 아닌경우 exp2를 반환하고,
exp1이 널인 경우 exp3를 반환한다.
-NULLIF(exp1,exp2) ; exp1,exp2를 비교하여 같으면 널을 반환하고,
같지 않으면 exp1을 반환한다.
-COALESCE(exp1,exp2,...expN) ; exp중에서 첫번째로 널이 아닌값을 반환.
SELECT last_name,commission_pct,NVL(commission_pct,0)
FROM employees;
SELECT last_name,commission_pct,NVL2(commission_pct,commission_pct,0) AS NNN
FROM employees;
SELECT last_name,first_name,NULLIF(last_name,first_name)
FROM employees;
SELECT last_name,first_name,COALESCE(commission_pct,commission_pct,TO_NUMBER(LENGTH(last_name))) AS last_name
FROM employees;
SELECT last_name,first_name,COALESCE(commission_pct,commission_pct,LENGTH(last_name)) AS last_name
FROM employees;
본 회사에서 커미션을 받지 않은 사원에게 $2000달러를 제공하고,
커미션을 받는 사원은 기존급여에 커미션을 추가해서 주는
급여 질의를 하세요.
SELECT last_name, salary, commission_pct,
COALESCE((salary+(commission_pct*salary)), salary+2000, salary)
"New Salary"
FROM employees;
**조건부 표현식
IF.조건.THEN.실행..ELSE 실행..
IF A > 0 THEN
PRINTF()
ELSE IF A<0 THEN
PRINTF()
ELSE
PRINTF()
** CASE함수
SELECT last_name,job_id,salary,
CASE job_id WHEN 'IT_PROG' THEN 1.1*salary
WHEN 'PU_CLERK' THEN 1.15*salary
WHEN 'ST_CLERK' THEN 1.17*salary
WHEN 'AC_MGR' THEN 1.19*salary
ELSE salary
END AS BONUS
FROM employees;
SELECT last_name,job_id,TO_CHAR(salary,'$99,999') AS salary,
TO_CHAR(CASE job_id WHEN 'IT_PROG' THEN 1.1*salary
WHEN 'PU_CLERK' THEN 1.15*salary
WHEN 'ST_CLERK' THEN 1.17*salary
WHEN 'AC_MGR' THEN 1.19*salary
ELSE salary
END,'$99,999') AS BONUS
FROM employees;
**DECODE 함수
SELECT last_name,job_id,TO_CHAR(salary,'$99,999') AS salary,
TO_CHAR(DECODE (job_id , 'IT_PROG' , 1.1*salary
, 'PU_CLERK' , 1.15*salary
, 'ST_CLERK' , 1.17*salary
, 'AC_MGR' , 1.19*salary
,salary),'$99,999') AS BONUS
FROM employees;
**집계(그룹) 함수
-그룹함수는 행 집합 연산 처리하여 그룹별로 결과 도출
-AVG ; 평균
-COUNT ; 행 개수 카운트
-MAX ; 최대값
-MIN ; 최소값
-SUM ; 합계
SELECT salary,SUM(salary)
FROM employees ;
SELECT department_id,SUM(salary)
FROM employees
GROUP BY department_id ;
SELECT SUM(salary)
FROM employees
GROUP BY department_id ;
SELECT department_id,SUM(salary),AVG(salary),MAX(salary),MIN(salary)
FROM employees
GROUP BY department_id ;
SELECT department_id,COUNT(employee_id)
FROM employees
GROUP BY department_id ;
본 회사의 커미션의 평균은 얼마인가요?
SELECT AVG(commission_pct),COUNT(commission_pct)
FROM employees;
SELECT AVG(NVL(commission_pct,0))
FROM employees;
SELECT department_id,SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000 ;
SELECT department_id,job_id,SUM(salary)
FROM employees
GROUP BY department_id,job_id
ORDER BY 1 ;
**조인
-한 개 이상의 테이블로 부터 데이터를 추출
-조인이 참여된 테이블은 상호 관계 유지
-Natural Join ; using, on
-Outer Join ; left,right,full JOIN
-Cross Join
*Natural Join
- 상호 테이블의 모든 행을 기반으로 한다.
- 상호 테이블에 동일한 모든 열에 값을 기반으로 선택
- 동일한 컬럼은 동일한 데이타 타입을 같아야한다.
SELECT department_name,last_name,first_name
FROM departments , employees
WHERE departments.department_id = employees.department_id ;
SELECT department_name,last_name,first_name
FROM departments JOIN employees
ON departments.department_id = employees.department_id ;
**USING절 사용
SELECT department_name,last_name,first_name
FROM departments NATURAL JOIN employees
USING department_id ;
SELECT department_name,last_name,first_name
FROM departments JOIN employees
USING (department_id) ;
**ON절 사용
SELECT department_name,last_name,first_name
FROM departments JOIN employees
ON departments.department_id = employees.department_id ;
**테이블 이름 별칭 사용
SELECT department_name,last_name,first_name
FROM departments D JOIN employees E
ON (d.department_id = e.department_id) ;
SELECT department_name,last_name,first_name
FROM departments D JOIN employees E
ON d.department_id = e.department_id ;
**여러 테이블 조인
SELECT employee_id, city, department_name
FROM employees e JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id ;
SELECT e.employee_id, l.city, d.department_name
FROM employees e JOIN departments d
USING (department_id)
JOIN locations l
USING (location_id);
**LEFT OUTER JOIN
SELECT e.last_name,d.department_name
FROM employees E LEFT OUTER JOIN departments D
ON (d.department_id = e.department_id ) ;
**RIGHT OUTER JOIN
SELECT e.last_name,d.department_name
FROM departments D RIGHT OUTER JOIN employees E
ON (d.department_id = e.department_id ) ;
**FULL OUTER JOIN
SELECT e.last_name,d.department_name
FROM employees E FULL OUTER JOIN departments D
ON (d.department_id = e.department_id ) ;
**CROSS JOIN
-두 데이블의 교차 곱을 생성
-Cartesian Product라고도 한다.
-조인 조건이 생략된 경우
-조인 조건이 잘못 된우
SELECT last_name, department_name
FROM employees CROSS JOIN departments ;
**서브 쿼리
-단일 쿼리로 결과을 얻을 수 없을 때
Abel 사용자 보다 급여를 더 많이 받는 사원은?
select last_name
from employees
where salary > Abel's salary
select last_name
from employees
where salary > (select salary from employees where last_name='Abel');
-Abel 사용자와 같은 부서에 근무하는 사원들은?
SELECT last_name
FROM employees
WHERE department_id = Abel's dept. (80)
SELECT last_name
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name='Abel') ;
-Abel과 같은 직업을 갖은 사원들 중 Abel보다 급여를 많이
받는 사원들은?
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name='Abel')
AND salary > (SELECT salary FROM employees WHERE last_name='Abel') ;
--평균보다 급여를 많이 받은 사원들의 이름과 잡아이디 급여을 출력하세요?
SELECT last_name,job_id,salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees) ;
--부서아이디 50 부서의 최소 급여보다 많은 부서별 최소 급여와 부서아이디를 출력하세요.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50) ; =>2100
SELECT last_name,hire_date
FROM employees
WHERE hire_date = (SELECT MAX(hire_date)
FROM employees) ;
SELECT last_name,hire_date
FROM employees
WHERE hire_date = (SELECT MIN(hire_date)
FROM employees) ;
SELECT last_name,hire_date
FROM employees
WHERE hire_date = (SELECT MIN(hire_date)
FROM employees) OR
hire_date = (SELECT MAX(hire_date)
FROM employees) ;
SELECT last_name,hire_date
FROM employees
WHERE hire_date IN (SELECT MIN(hire_date)
FROM employees GROUP BY department_id) ;
**서브쿼리에서 여러 행이 출력된 서브쿼리 완성
-연산자
IN ; 결과의 행과 같은것
ANY ; 최대값, 최소값
; > ANY (최소값보다 큰) , < ANY (최대값보다 작은)
ALL ; 최대값, 최소값
; > ALL (최대값보다 큰), < ALL (최소값보다 작은)
SELECT last_name,salary
FROM employees
WHERE salary IN (SELECT salary FROM employees WHERE last_name='Taylor') ; =>8600 , 3200
SELECT last_name,salary
FROM employees
WHERE salary IN (SELECT salary FROM employees WHERE last_name='Taylor') ; =>8600 , 3200
**최소값 3200보다 급여을 많이 받은 사용자
SELECT last_name,salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE last_name='Taylor') ; =>8600 , 3200
**최대값 8600보다 급여을 적게 받은 사용자
SELECT last_name,salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE last_name='Taylor') ; =>8600 , 3200
**최대값 8600보다 급여을 많이 받은 사용자
SELECT last_name,salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE last_name='Taylor') ; =>8600 , 3200
**최소값 3200보다 급여을 적게 받은 사용자
SELECT last_name,salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE last_name='Taylor') ; =>8600 , 3200
**집합 연산자
합집합, 차집합, 교집합
합집합 ; UNION(중복행제거한 후), UNION ALL(중복행포함 후)
교집합 ; INTERSECT(공통적인 행)
차집합 ; MINUS (첫질의 - 두번째 질의)
**집합 연산자 규칙
-두 질의의 SELECT절에 나오는 컬럼의 갯수가 일치
SELECT col1,col2
FROM employees
UNION
SELECT col1,col2
FROM departments
-두 질의의 SELECT절에 나오는 컬럼의 데이타타입 일치
SELECT col1(varchar2),col2(number)
FROM employees
UNION
SELECT col1(varchar2),col2(number)
FROM departments
SELECT employee_id,job_id,department_id
FROM employees
UNION
SELECT employee_id,job_id,department_id
FROM job_history ;
SELECT employee_id,job_id,department_id
FROM employees
UNION ALL
SELECT employee_id,job_id,department_id
FROM job_history ;
SELECT employee_id,job_id,department_id
FROM employees
INTERSECT
SELECT employee_id,job_id,department_id
FROM job_history ;
SELECT employee_id,job_id,department_id
FROM employees
MINUS
SELECT employee_id,job_id,department_id
FROM job_history ;
**아래의 경우는 데이타 유형이 다른경우 에러발생
SELECT last_name,job_id,department_id
FROM employees
MINUS
SELECT employee_id,job_id,department_id
FROM job_history ;
**컬럼의 갯수가 다를경우 에러발생
SELECT employee_id,job_id,department_id
FROM employees
MINUS
SELECT employee_id,job_id
FROM job_history ;
**정상적인 쿼리
SELECT employee_id,job_id,department_id
FROM employees
MINUS
SELECT NULL,NULL,NULL
FROM job_history ;
**데이타 타입 불일치
SELECT employee_id,job_id,department_id
FROM employees
MINUS
SELECT employee_id,department_id,job_id
FROM job_history ;
**데이타 타입과 갯수 일치하므로 결과 실행
SELECT employee_id,department_id,job_id
FROM employees
MINUS
SELECT department_id,employee_id,job_id
FROM job_history ;
============================
**테이블 생성
CREATE TABLE depts
( DEPT_ID NUMBER(4)
,DEPT_NAME VARCHAR2(30)
,MAN_ID NUMBER(6)
,LOC_ID NUMBER(4)
) ;
**데이타 입력
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (1,'SALES',100,100) ;
SELECT * FROM depts ;
INSERT INTO depts (DEPT_ID,MAN_ID,LOC_ID,DEPT_NAME)
VALUES (2,100,100,'SALES') ;
INSERT INTO depts
VALUES (3,'SALES',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME)
VALUES (4,'SALES') ;
INSERT INTO depts
VALUES (5,'SALES',NULL,NULL) ;
**데이타 수정
-update수행시 where를 생략하시면 모든 레코드를
수정하게 됩니다.
UPDATE depts
SET man_id = 100
WHERE dept_id = 4 ;
UPDATE depts
SET man_id = 200
WHERE man_id = (SELECT man_id FROM depts WHERE dept_id = 3 ) ;
**데이타 삭제
-DELETE 수행시 where를 생략하시면 모든 레코드를
삭제 하게 됩니다.
DELETE FROM depts
WHERE dept_id = 5 ;
**모든 데이타 삭제
-테이블의 모든 데이타 삭제할때
TRUNCATE TABLE depts ; 데량 데이타 삭제할때 성능상 유리
DELETE FROM depts; 데량 데이타 삭제할때 삭제시간 많이 소요
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (1,'Sales',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (2,'Marketing',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (3,'Research',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (4,'IT',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (5,'Management',100,100) ;
SELECT * FROM depts ;
DELETE FROM depts
WHERE dept_id = 4 ;
COMMIT;
DELETE FROM depts
WHERE dept_id = 4 ;
ROLLBACK;
트랜젝션은 한 작업의 단위다.
COMMIT;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (1,'Sales',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (2,'Marketing',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (3,'Research',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (4,'IT',100,100) ;
INSERT INTO depts (DEPT_ID,DEPT_NAME,MAN_ID,LOC_ID)
VALUES (5,'Management',100,100) ;
SAVEPOINT done_insert ;
DELETE FROM depts
WHERE dept_id = 4 ;
SAVEPOINT done_delete ;
UPDATE depts
SET man_id = 200
WHERE dept_id = 5 ;
ROLLBACK To done_insert ;
COMMIT;
NCS/SQL 활용