ORA-28000: the account is locked
select username, account\_status, lock\_date
from dba\_users
where username = '홍길동'
; ALTER USER 홍길동 ACCOUNT UNLOCK | ORACLE DBMS 테이블 스페이스 구성 (0) | 2022.10.26 |
|---|
CREATE TABLESPACE TS_APP_D01 DATAFILE '/u01/app/oracle/oradata/XE/파일명.dbf' SIZE 6144M AUTOEXTEND ON NEXT 64M MAXSIZE unlimited;
CREATE TEMPORARY TABLESPACE TS_APP_TEMP TEMPFILE '/u01/app/oracle/oradata/XE/파일명_tmp.dbf' SIZE 1G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32M;
create user APP_USR identified by APP_USR default tablespace TS_APP_D01 TEMPORARY TABLESPACE TS_APP_TEMP;
grant connect, resource to APP_USR ;
권한 종류
1) CONNECT : 사용자가 데이터베이스에 접속 가능하도록 하기 위해 다음과 같이 가장 기본적인 시스템 권한 8가지를 묶어 놓았습니다.
-- (ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW)
2) RESOURCE : 사용자 객체(테이블, 뷰, 인덱스)를 생성할 수 있도록 하기위해서 시스템 권한을 묶어 놓았습니다.
-- (CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER)
[ORA-12953]The request exceeds the maximum allowed database size of 11 GB
-- 오라클 캐릭터셋 확인 쿼리
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
* KO16KSC5601
완성형 한글- 일반적으로 많이 사용되며 2350자의 한글, 4888자의 한자, 히라카나, 카타카나, 영문 및 각종 기호를 포함하고 있음. (한글바이트: 2byte)
* KO16MSWIN949
조합형 한글- 완성형을 포함하여 11172자의 한글을 표현함 (한글바이트: 2byte)
* AL32UTF8
Unicode의 CES 중 하나- 11172자의 한글을 지원 (한글바이트: 3byte)
| 오라클 DBMS ORA-28000: the account is locked 계정잠김 해결방법 (0) | 2024.03.06 |
|---|
SQL 활용
-오라클
-순위 함수
- rank over
- dense rank over
- low number over (제일 많이 쓰임)
by 스뎅(thDeng) on July 20, 2017
https://blog.leocat.kr/notes/2017/07/20/oracle-rank-over-vs-row-number-over
레코드를 특정 column의 값을 기준으로 정렬하여 순서를 매길 수 있다. RANK() OVER 또는 ROW_NUMBER() OVER를 사용하면 된다. 간단히 아래와 같은 테이블을 생각하자.
SQL> SELECT * FROM TMP_TABLE;
USERID | SCORE
----------|----------
aaa | 10
aaa | 30
aaa | 50
aaa | 90
bbb | 80
bbb | 50
bbb | 20
bbb | 40
aaa | 50
9 rows selected.
SQL> SELECT
USERID,
SCORE,
RANK() OVER (ORDER BY SCORE DESC) RANK
FROM TMP_TABLE;
USERID | SCORE| RANK
----------|----------|----------
aaa | 90| 1
bbb | 80| 2
aaa | 50| 3
aaa | 50| 3
bbb | 50| 3
bbb | 40| 6
aaa | 30| 7
bbb | 20| 8
aaa | 10| 9
9 rows selected.SQL> SELECT USERID,
SCORE,
ROW_NUMBER() OVER (ORDER BY SCORE DESC) RANK
FROM TMP_TABLE;
USERID | SCORE| RANK
----------|----------|----------
aaa | 90| 1
bbb | 80| 2
aaa | 50| 3
aaa | 50| 4
bbb | 50| 5
bbb | 40| 6
aaa | 30| 7
bbb | 20| 8
aaa | 10| 9
9 rows selected.위의 RANK()와 ROW_NUMBER()의 차이는 결과를 자세히 보면 알 수 있다. RANK()의 결과는 3, 4, 5번째 레코드의 점수가 50점으로 동일하기 때문에 때문에 같은 순서인 3번을 매겼다. 하지만 ROW_NUMBER()의 결과는 점수가 같더라도 레코드가 달라지면 다른 순서를 매긴다.
게시자: MyungJong Kim, 2011. 12. 11. 오후 9:09
--- RANK() : 중복 순위 다음은 해당 개수만큼 건너뛰고 반환
--- DENSE_RANK() : 중복 순위 상관없이 순차적으로 반환
--- ROW_NUMBER() : 중복과 관계 없이 무조건 순서대로 반환
select id
, salary
-- 분석 함수의 order by 절을 사용하여 내림차순 정렬한 뒤 rank() 함수가 순위를 반환
, RANK() OVER (ORDER BY salary DESC) Rank
, DENSE_RANK() OVER (ORDER BY salary DESC) DenseRank
, ROW_NUMBER() OVER (ORDER BY salary DESC) RowRank
from (
select 100 id, 39000 salary from dual union all
select 101 id, 30000 salary from dual union all
select 102 id, 9000 salary from dual union all
select 103 id, 17000 salary from dual union all -- 중복
select 104 id, 60000 salary from dual union all
select 105 id, 9500 salary from dual union all
select 106 id, 17000 salary from dual union all -- 중복
select 107 id, 37500 salary from dual union all
select 108 id, 25000 salary from dual union all
select 109 id, 5000 salary from dual union all
select 110 id, 17000 salary from dual -- 중복
) ;
-- - 부서별로 월급이 높은 순위로 2명씩 뽑아보세요~
select a.*
from (select dept
, id
, salary
, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) ranking
-- 부서별로 그룹을 나누고 order by 절을 사용하여 내림차순 정렬한 뒤
-- row_number() 함수가 순위를 반환
from (
select 10 dept, 100 id, 39000 salary from dual union all
select 20 dept, 101 id, 30000 salary from dual union all
select 20 dept, 102 id, 9000 salary from dual union all
select 30 dept, 103 id, 17000 salary from dual union all
select 40 dept, 104 id, 60000 salary from dual union all
select 20 dept, 105 id, 9500 salary from dual union all
select 30 dept, 106 id, 17000 salary from dual union all
select 40 dept, 107 id, 37500 salary from dual union all
select 40 dept, 108 id, 25000 salary from dual union all
select 30 dept, 109 id, 5000 salary from dual union all
select 40 dept, 110 id, 17000 salary from dual
)
) a
where a.ranking < 3 ; -- 2순위까지 조회
workbook_script_v2.0.sql
0.92MB
워크북_v2.0.pdf
0.31MB
-- [Basic SELECT]
-- 1.춘 기술대학교의 학과 이름과 계열을 표시하시오.
-- 단, 출력 헤더는 "학과 명", "계열" 으로 표시하도록 한다.
SELECT
department_name "학과 명",
category "계열"
FROM
TB_DEPARTMENT;-- 2. 학과의 학과 정원을 다음과 같은 형태로 화면에 출력한다.
select
department_name||'의 정원은 '||capacity||'명 입니다.' "학과 별 정원"
FROM
TB_DEPARTMENT;-- 3."국어국문학과" 에 다니는 여학생 중
-- 현재 휴학중인 여학생을 찾아달라는 요청이 들어왔다. 누구인가?
-- (국문학과의 '학과코드'는 학과 테이블(TB_DEPARTMENT)을 조회해서 찾아 내도록 하자)
select
student_name
from TB_Student
where
department_no ='001' and
substr(student_ssn,8,1) in( '2','4') and
absence_yn = 'Y';-- 4. 도서관에서 대출 도서 장기 연체자 들을 찾아 이름을 게시하고자 한다.
--그 대상자들의 학번이 다음과 같을 때 대상자들을 찾는 적절한 SQL 구문을 작성하시오.
-- A513079, A513090, A513091, A513110, A513119
select
student_name
from TB_Student
where
student_no in ('A513079', 'A513090', 'A513091', 'A513110', 'A513119');-- 5. 입학정원이 20 명 이상 30 명 이하인 학과들의 학과 이름과 계열을 출력하시오.
select
department_name,
category
from TB_DEPARTMENT
where
capacity between 20 and 30;-- 6. 춘 기술대학교는 총장을 제외하고 모든 교수들이 소속 학과를 가지고 있다.
--그럼 춘 기술대학교 총장의 이름을 알아낼 수 있는 SQL 문장을 작성하시오.
select professor_name
from TB_professor
where department_no is null;-- 7. 혹시 젂산상의 착오로 학과가 지정되어 있지 않은 학생이 있는지 확인하고자 한다.
--어떠한 SQL 문장을 사용하면 될 것인지 작성하시오.
select student_name
from TB_Student
where department_no is null;-- 8. 수강신청을 하려고 한다. 선수과목 여부를 확인해야 하는데,
--선수과목이 존재하는 과목들은 어떤 과목인지 과목번호를 조회해보시오.
select class_no
from TB_class
where preattending_class_no is null;-- 9. 춘 대학에는 어떤 계열(CATEGORY)들이 있는지 조회해보시오.
select category
from TB_DEPARTMENT
group by category
order by 1;-- 10. 02 학번 전주 거주자들의 모임을 만들려고 한다.
-- 휴학한 사람들은 제외한 재학중인 학생들의
-- 학번, 이름, 주민번호를 출력하는 구문을 작성하시오.
select
student_no,
student_name,
student_ssn
from TB_Student
where
extract(year from entrance_date) like '2002' and
student_address like '%전주%' and
absence_yn = 'N';-- [Additional SELECT - 함수]
-- 1. 영어영문학과(학과코드 002) 학생들의 학번과 이름, 입학 년도를
-- 입학 년도가 빠른순으로 표시하는 SQL 문장을 작성하시오.
-- ( 단, 헤더는 "학번", "이름", "입학년도" 가 표시되도록 한다.)
select
student_no 학번,
student_name 이름,
to_char(entrance_date,'YYYY-MM-DD') 입학년도
from TB_Student
where
department_no = '002'
order by 3;-- 2.춘 기술대학교의 교수 중 이름이 세 글자가 아닌 교수가 한 명 있다고 한다.
--그 교수의 이름과 주민번호를 화면에 출력하는 SQL 문장을 작성해 보자.
--(* 이때 올바르게 작성한 SQL 문장의 결과 값이 예상과 다르게 나올 수 있다.
--원인이 무엇일지 생각해볼 것)
select
professor_name,
professor_ssn
from TB_professor
where
professor_name not like '___';-- 3. 춘 기술대학교의 남자 교수들의 이름과 나이를 출력하는 SQL 문장을 작성하시오.
--단, 이때 나이가 적은 사람에서 많은 사람 순서로 화면에 출력되도록 만드시오.
--(단, 교수 중 2000 년 이후 출생자는 없으며 출력 헤더는 "교수이름", "나이"로 한다.
--나이는 ‘만’으로 계산한다.)
select
professor_name 교수이름,
extract(year from sysdate)-1900 - substr(professor_ssn,1,2)나이
from TB_professor
order by 2;-- 4. 교수들의 이름 중 성을 제외핚 이름맊 출력하는 SQL 문장을 작성하시오.
--출력 헤더는‚ "이름" 이 찍히도록 한다.
--(성이 2 자인 경우는 교수는 없다고 가정하시오)
select
substr(professor_name,2,length(professor_name)-1)
from TB_professor;-- 5. 춘 기술대학교의 재수생 입학자를 구하려고 핚다. 어떻게 찾아낼 것인가?
-- 이때, 19 살에 입학하면 재수를 하지 않은 것으로 간주한다.
select
student_no,
student_name
from TB_Student
where extract(year from entrance_date)-(substr(student_ssn,1,2)+1900) > 19;
-- 6. 2020년 크리스마스는 무슨 요일인가?
select to_char(to_date('20201225'),'day') from dual;-- 7. TO_DATE('99/10/11','YY/MM/DD'), TO_DATE('49/10/11','YY/MM/DD') 은
--각각 몇 년 몇 월 몇 일을 의미할까?
--또 TO_DATE('99/10/11','RR/MM/DD'),TO_DATE('49/10/11','RR/MM/DD')은
--각각 몇 년 몇 월 몇 일을 의미할까?
select
extract(year from TO_DATE('99/10/11','YY/MM/DD'))||'년'||
extract(month from TO_DATE('99/10/11','YY/MM/DD'))||'월'||
extract(day from TO_DATE('99/10/11','YY/MM/DD'))||'일' "TO_DATE('99/10/11','YY/MM/DD')",
extract(year from TO_DATE('49/10/11','YY/MM/DD'))||'년'||
extract(month from TO_DATE('49/10/11','YY/MM/DD'))||'월'||
extract(day from TO_DATE('49/10/11','YY/MM/DD'))||'일' "TO_DATE('49/10/11','YY/MM/DD')",
extract(year from TO_DATE('99/10/11','RR/MM/DD'))||'년'||
extract(month from TO_DATE('99/10/11','RR/MM/DD'))||'월'||
extract(day from TO_DATE('99/10/11','RR/MM/DD'))||'일' "TO_DATE('99/10/11','RR/MM/DD')",
extract(year from TO_DATE('49/10/11','RR/MM/DD'))||'년'||
extract(month from TO_DATE('49/10/11','RR/MM/DD'))||'월'||
extract(day from TO_DATE('49/10/11','RR/MM/DD'))||'일' "TO_DATE('49/10/11','RR/MM/DD')"
from dual;-- 8. 춘 기술대학교의 2000 년도 이후 입학자들은 학번이 A 로 시작하게 되어있다.
--2000 년도 이전 학번을 받은 학생들의 학번과 이름을 보여주는 SQL 문장을 작성하시오.
select
student_no,
student_name
from TB_Student
where extract(year from entrance_date)<2000;-- 9. 학번이 A517178 인 한아름 학생의 학점 총 평점을 구하는 SQL 문을 작성하시오.
-- 단, 이때 출력 화면의 헤더는 "평점" 이라고 찍히게 하고,
-- 점수는 반올림하여 소수점 이하 한자리까지만 표시한다.
select round(avg(point),1) "평점"
from TB_grade
where student_no = 'A517178';-- 10. 학과별 학생수를 구하여 "학과번호", "학생수(명)" 의 형태로 헤더를 만들어
--결과값이 출력되도록 하시오.
select
department_no "학과번호",
count(*) "학생수(명)"
from TB_Student
group by department_no
order by 1;-- 11. 지도 교수를 배정받지 못핚 학생의 수는 몇 명 정도 되는 알아내는 SQL 문을
--작성하시오.
select
count(*) "count(*)"
from TB_Student
where coach_professor_no is null;-- 12. 학번이 A112113 인 김고운 학생의 년도 별 평점을 구하는 SQL 문을 작성하시오.
-- 단, 이때 출력 화면의 헤더는 "년도", "년도 별 평점" 이라고 찍히게 하고,
-- 점수는 반올림하여 소수점 이하 한 자리까지만 표시한다.
select
substr(term_no,1,4) "년도",
avg(point) "년도 별 평점"
from TB_grade
where student_no ='A112113'
group by substr(term_no,1,4);TCL
Transaction Control Language
Transaction
delete from cafe;
select * from cafe;
--삭제된 것으로 보이지만 롤백 가능
rollback;
--트랜젝션에서 일어난 DML 작업 취소
commit
--진짜로 DBMS에 적용, rollback 불가
작업의 원자성 : 작업이 더이상 쪼개지지 않음
savepoint
rollback으로 돌아갈 위치를 태그
savepoint beforeDelete;
--rollback으로 돌아갈 위치를 태그
delete from cafe;
rollback to beforeDelete;savepoint beforeDelete;
delete from cafe;
savepoint beforeInsert;
insert into cafe values(cafe_seq.nextval,'Milk Tea',3000);
select * from cafe;
rollback to beforeDelete;
rollback to beforeInsert;
--"savepoint '%s' never established in this session or is invalid"
--더 오래된 과거로 롤백하면 그후의 세이브포인트도 사라짐
commit을 안해놓으면 나는 insert했는데 프로그램은 데이터를 못받는 상황 발생
"값이 안나와요"->"commit 했나요?"
DCL
Data Control Language
Grant / Revoke
-- DCL 연습용 임시 계정 생성
--AdminConnection 계정 연결
create user temp identified by temp;
grant resource, connect to temp;
-- 권한들이 모여서 Role이 된다.
--temp 계정 연결
select * from employee;
select * from kh.employee;
--"table or view does not exist"
--kh 계정의 table에 접근 권한이 없다.-- 권한 주기 Grant
--kh 계정 연결
grant select on employee to temp;
--role이 아니라 권한
--employee 테이블을 select할 권한을 사용자 temp에게 부여
--DCL이라 commit 없어도 동작
--temp 계정 연결
select * from kh.employee;
--성공-- 권한 뺏기 Revoke
--kh 계정 연결
revoke select on employee from temp;
--temp 계정 연결
select * from kh.employee;
--"table or view does not exist"객체
User / Table / Sequence /
View
하나 이상의 테이블에서 일부 데이터를 선택하여 가상의 테이블을 생성한 것
create view emp_view
as
select emp_id, emp_name, emp_no from employee;
--as: ~로서
--"insufficient privileges"view 생성 권한 부여
-- kh계정
create view emp_view
as
select emp_id, emp_name, emp_no from employee;
--as: ~로서
--"insufficient privileges"
-- view 만들 권한 없음
create table emp_table
as
select emp_id, emp_name, emp_no from employee;
select * from emp_table;
--Table EMP_TABLE이(가) 생성되었습니다.
--부여된 resource 권한에는 table생성만 가능, view생성 권한은 별도-- admin 계정
grant create view to kh;-- kh계정
create view emp_view
as
select emp_id, emp_name, emp_no from employee;
--View EMP_VIEW이(가) 생성되었습니다.
select * from emp_view;table 사본과 view 비교 : 변동사항 없을 때
-- kh 계정
grant select on emp_table to temp;
grant select on emp_view to temp;-- temp 계정
select * from kh.employee;
--"table or view does not exist"
select * from kh.emp_table;
select * from kh.emp_view;
-- 변동 사항 없을 때는 같은 결과table 사본과 view 비교 : 변동사항 있을 때
-- 변동사항 : 사원이름 '선동일'이 '선동이'로 변경
-- kh 계정
update employee
set emp_name = '선동이'
where emp_id = 200;-- temp 계정
select * from kh.emp_table;
-- '선동일'
select * from kh.emp_view;
-- '선동일'
--kh세션에서 변동사항 commit을 안하면 다른세션에서는 적용되지 않음문제 해결 : 변동사항 commit을 안하면 다른세션에서는 적용되지 않음
-- 변동사항 commit
-- kh 계정
commit;-- temp 계정
select * from kh.emp_table;
-- '선동일'
select * from kh.emp_view;
-- '선동이'예제 : 사번, 이름, 부서명 view 만들기
-- kh 계정
create view emp_view2
as
select emp_id, emp_name, dept_title
from employee e, department d
where e.dept_code = d.dept_id;
grant select on emp_view2 to temp;-- temp 계정
select * from kh.emp_view2;데이터베이스 시작~ 여기까지 시험 범위
DQL : sub-Query
select manager_id from employee where emp_name='전지연';
--214
select emp_name from employee where emp_id=214;
--214 부분에 대입
select
emp_name
from employee
where emp_id=(select manager_id from employee where emp_name='전지연');-- 회사의 평균 급여보다 급여를 더 많이 받는 사원의
-- 사번, 이름, 직급코드, 급여
select
emp_id,
emp_name,
job_code,
salary
from employee
where salary >= avg(salary);
--"group function is not allowed here"
--where절은 그룹함수 쓸 수 없음
--Sub-Query
--안에 넣을 값 : 평균 급여
select
avg(salary)
from employee;
--Sub-Query를 where절에 사용
select
emp_id,
emp_name,
job_code,
salary
from employee
where salary >= (select avg(salary) from employee);
--단일행 단일열 서브쿼리Sub-Query란
쿼리 내에서 쿼리를 쓴다
그 쿼리가 무엇을 반환하냐에 따라 문법적 차이가 조금 난다.
다중행 다중열 Sub-Query
단일행 서브쿼리
-- 1. 직원 중에서 윤은해 직원과 급여가 같은 사원들의
-- 사원번호 사원이름 급여를 출력하세요
select salary from employee where emp_name='윤은해';
select
emp_id,
emp_name,
salary
from employee
where salary=(select salary from employee where emp_name='윤은해');-- 2. 급여가 제일 많은 사원과 적은 사원의 이름과 급여 출력
select emp_name, salary
from employee
where salary =(select max(salary) from employee) or
salary =(select min(salary) from employee);-- 3. D1, D2 부서에 근무하는 사원들 중에서
-- 기본급여가 D5 부서 직원들의 '평균월급' 보다 많은 사람들만
-- 부서번호, 사원번호, 사원명, 월급을 나타내세요.
select
dept_code,
job_code,
emp_name,
salary
from employee
where dept_code in ('D1','D2') and
salary > (select avg(salary) from employee where dept_code = 'D5');다중행 서브쿼리
--다중행 서브쿼리
select *
from employee
where dept_code in (select dept_code from employee where emp_name in ('송종기','박나라'));
-- 다중행 비교는 (=)이 아니라 in-- 1. 차태연 / 전지연 직원의 급여등급 (sal_level) 같은
-- 사원의 이름과 직급명을 출력하세요.
select
e.emp_name,
j.job_name
from employee e, job j
where e.job_code=j.job_code and
e.sal_level in
(select sal_level from employee where emp_name in ('차태연','전지연')
);-- 2. 직급이 대표, 부사장이 아닌 모든 사원의
-- 이름, 부서명, 직급코드를 출력하고 부서별로 출력.
select emp_name, dept_title, job_code
from employee, department
where employee.dept_code = department.dept_id
and job_code not in (select job_code from job where job_name in ('대표','부대표'));IN / ANY / ALL
-- ANY
select emp_name, salary from employee
where salary > any (2000000,5000000);
select emp_name, salary from employee
where salary > 2000000 or salary > 5000000;-- ALL
select emp_name, salary from employee
where salary > all (2000000,5000000);
select emp_name, salary from employee
where salary > 2000000 and salary > 5000000;select emp_name, salary from employee
where salary > any (select salary from employee where job_code= 'J3')-- 1. 'D1' 또는 'D5' 부서코드를 가지고 있는 사원들의
-- 급여중에서 가장 높은 급여 보다 작은
-- 모든 사원들의 이름, 급여, 부서코드를 출력하세요.
select
emp_name,
salary,
dept_code
from employee
where salary < any (select salary
from employee
where dept_code in ('D1','D5'));-- 2. 부서별 평균 급여를 조사하였을때 가장 낮은 부서의 급여보다
-- 높거나 같은 모든 사원들의 이름, 급여, 부서명을 출력하시오
-- ANSI 표준
select
emp_name,
salary,
dept_title
from employee left join department on (dept_code =dept_id)
where
salary >= any(select avg(salary) from employee group by dept_code);
-- 오라클
select
emp_name,
salary,
dept_title
from employee, department
where employee.dept_code=department.dept_id(+) and
salary >= any(select avg(salary) from employee group by dept_code);다중열 : 한줄만 여러컬럼
-- 다중행 다중열
select dept_code, job_code from employee;
-- 다중열
select dept_code, job_code from employee
where emp_name='유하진';
-- 다중열 일때 비교하는 컬럼들을 괄호로 묶어서 (,) 로 구분해 넣기
-- dept_code,job_code둘다 비교
select emp_name, dept_code, job_code from employee
where (dept_code,job_code)=(select dept_code, job_code from employee
where emp_name='유하진');-- 다중행 다중열 비교시 (=) 대신 in 사용
select emp_name, dept_code, job_code from employee
where (dept_code,job_code) in (select dept_code, job_code from employee
where emp_name in('유하진','박나라'));-- 3. 각 부서별 가장 급여를 많이 받는 직원들의 목록
select
dept_code,
emp_name,
salary
from employee
where (salary,dept_code) in (select max(salary),dept_code
from employee
group by dept_code)
order by 1;-- 4. 직급별 최소 급여를 받는 직원의
-- 이름, 직급코드, 부서코드, 입사일, 연봉을 출력
select
emp_name,
job_code,
dept_code,
hire_date,
salary*12
from employee
where (salary,dept_code) in (select min(salary),dept_code from employee group by dept_code)
order by 3;
-- 5. 기술지원부에 속한 사람들의 이름, 부서코드, 급여를 출력하시오
select
emp_name,
dept_code,
salary
from employee join department on (dept_code=dept_id)
where (dept_code) in (select dept_code from employee where dept_title = '기술지원부');
select
emp_name,
dept_code,
salary
from employee
where (dept_code) in (select dept_id from department where dept_title = '기술지원부');--JOIN 을 쓰지 않고 같이 출력
-- 상관 Sub-Query
select
emp_name,
(select dept_title from department d where d.dept_id = e.dept_code) "부서명"
from employee e;
--mainQuery의 컬럼을 subQuery가 쓸 수는 있지만
--subQuery의 컬럼을 mainQuery가 쓸 수는 없음--Inline view
--from절에 subQuery를 사용
select
emp_name,
dept_code
from (select emp_code,dept_code,job_code,email from employee);
JOIN
Natural JOIN - Cartesian Product
Inner JOIN - 조건을 충족하는 결과들만의 집합
Outer JOIN - 조건이 충족되지 않아도 일단 모두 출력하는 집합
조인할 테이블들의 컬럼명이 같을 경우 조인하기 곤란
테이블의 이름 붙여놓기 (as)
-- 조인할 테이블들의 컬럼명이 같으면 조인하기 곤란
-- employee의 job_code, job의 job_code 조인
select
*
from employee, job
where job_code = job_code;
--00918. 00000 - "column ambiguously defined"
-- 컬럼이 모호하게 정의되었습니다.-- 테이블에 별명 붙여놓기 (as)
select
*
from employee emp, job j
where emp.job_code = j.job_code;
select
emp_name, job_name, emp.job_code
from employee emp, job j
where emp.job_code = j.job_code;self join
-- 한 행씩 봤을때 자기 자신이 자신의 직속상사일수 없음
select emp_id , emp_name, Manager_id from employee
where emp_id = manager_id;-- self join도 두 테이블이라고 생각하고 구성
select e1.emp_id, e1.emp_name, e2.emp_name
from employee e1 , employee e2
where e1.manager_id = e2.emp_id;테이블 3개 연결
select
emp_name,
dept_title
from employee e, department d
where e.dept_code = d.dept_id;-- 자바의 3중 for문과 유사한 형태
select
e.emp_name
d.dept_title
d.location_id
from employee e, department d, location l
where
e.dept_code = d.dept_id and
d.location_id = l.local_code;select * from employee;
select * from department;
select * from location;
select * from national;
select
e.emp_name,
d.dept_title,
n.national_name
from employee e, department d, location l, national n
where
e.dept_code = d.dept_id and
d.location_id = l.local_code and
l.national_code = n.national_code;
--ansi 표준
select
e.emp_name,
d.dept_title,
n.national_name
from
employee e join department d on e.dept_code = d.dept_id
join location l on d.location_id = l.local_code
join national n on l.national_code = n.national_code;--outer join
select
e.emp_name,
d.dept_title,
n.national_name
from employee e, department d, location l, national n
where
e.dept_code = d.dept_id(+) and
d.location_id = l.local_code(+) and
l.national_code = n.national_code(+);--각 사원들의 이름 / 나이 / 부서명 / 직급명을 출력하세요.
select
e.emp_name "이름",
extract(year from sysdate)-(decode(substr(e.emp_no,8,1),1,1900,2,1900,3,2000,4,2000)+substr(e.emp_no,1,2)) "나이",
d.dept_title "부서명",
j.job_name "직급명"
from employee e, department d, job j
where
e.dept_code = d.dept_id(+) and
e.job_code = j.job_code(+);
-- 1. 2030년 12월 25일의 요일을 출력하시오
select
to_char(to_date(20301225),'day')
from
dual;-- 2. 주민번호가 1970년대 생이면서 성별이 여자이고,
-- 성이 전씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하세요.
select
e.emp_name "사원명",
e.emp_no "주민번호",
d.dept_title "부서명",
j.job_name "직급명"
from employee e, department d, job j
where
e.dept_code = d.dept_id(+) and
e.job_code = j.job_code(+);-- 3. 이름에 '형' 자가 들어가는 직원들의 사번, 사원명, 부서명을 조회하세요.
select
e.emp_id "사번",
e.emp_name "사원명",
d.dept_title "부서명"
from employee e, department d
where
e.emp_name like '%형%' and
e.dept_code = d.dept_id(+);-- 4. 해외영업부에 근무하는 사원명, 직급명, 부서코드, 부서명을 조회하세요.
select
e.emp_name "사원명",
j.job_name "직급명",
e.dept_code "부서코드",
d.dept_title "부서명"
from employee e, job j, department d
where
d.dept_title like '해외영업%' and
e.job_code = j.job_code and
e.dept_code = d.dept_id;-- 5. 보너스포인트를 받는 직원들의
--사원명, 보너스포인트, 부서명, 근무지역명(나라이름)을 조회하세요
select
e.emp_name "사원명",
e.bonus "보너스포인트",
d.dept_title "부서명",
n.national_name "근무지역명"
from employee e, department d , location l, national n
where
e.bonus is not null and
e.dept_code = d.dept_id and
d.location_id = l.local_code and
l.national_code = n.national_code;-- 6. 부서코드가 D2인 직원들의
-- 사원명, 직급명, 부서명, 근무지역명을 조회하세요.
select
e.emp_name "사원명",
j.job_name "직급명",
d.dept_title "부서명",
n.national_name "근무지역명"
from employee e, job j, department d, location l,national n
where
e.dept_code = 'D2' and
e.job_code = j.job_code and
e.dept_code = d.dept_id and
d.location_id = l.local_code and
l.national_code = n.national_code;-- 7. 급여등급테이블의 최대급여 (MAX_SAL - 500000)보다 많이 받는 직원들의
-- 사원명, 직급명, 급여, 연봉을 죄회하세요.
select
e.emp_name "사원명",
j.job_name "직급명",
e.salary"급여",
e.salary*12 "연봉"
from employee e, job j , SAL_GRADE s
where
e.salary*12>(s.max_sal - 5000000) and
e.job_code = j.job_code and
e.sal_level = s.sal_level ;-- 8. 한국(KO)과 일본(JP)에 근무하는 직원들의
-- 사원명, 부서명, 지역명, 국가명을 조회하세요.
select
e.emp_name "사원명",
d.dept_title "부서명",
l.local_name "지역명",
n.national_name "국가명"
from employee e, department d, location l, national n
where
n. national_code in ('KO','JP') and
e.dept_code = d.dept_id and
d.location_id =l.local_code and
l.national_code = n.national_code;-- 9. 같은 부서에 근무하는 직원들의
-- 사원명, 부서명, 동료이름을 조회하세요. (self join 사용)
-- ex) 홍길동 기술지원부 김철일
-- 홍길동 기술지원부 김철이
-- 홍길동 기술지원부 김철삼
select
e1.emp_name "사원명",
d.dept_title "부서명",
e2.emp_name "동료이름"
from employee e1, department d, employee e2
where
e1.dept_code = d. dept_id and
e1.emp_name !=e2.emp_name
order by 1;-- 10. 보너스포인트가 없는 직원들 중에서 직급이 차장과 사원인 직원들의
-- 사원명, 직급명, 급여를 조회하시오. 단, join과 IN 사용할 것
select
e.emp_name "사원명",
j.job_name "직급명",
e.salary "급여"
from employee e, job j
where
e.bonus is null and
j.job_name in ('차장','사원') and
e.job_code=j.job_code;-- 11. 재직중인 직원과 퇴사한 직원의 수를 조회하시오.
-- 재직여부 인원수
-- 퇴사 1
-- 재직 22
select
decode(ent_yn,'N','재직','Y','퇴사') "재직여부",
count(*) "인원수"
from employee
group by decode(ent_yn,'N','재직','Y','퇴사');-- DQL
-- set operator
-- sub query (중요한 내용이지만 23일 시험 내용에서는 빠짐)
set operator (집합 연산자)
UNION / UNION ALL / INTERSECT / MINUS
select * from employee
union
select * from department;
--"query block has incorrect number of result columns"
--컬럼 개수가 같고 그 자료형이 상호호환 되어야 함--오라클에서 문자열을 숫자로 호환
select '10'+5 from dual;
--15-- 상호호환
select emp_id, emp_name, dept_code from employee
union
select * from department;
--상호호환 안됨
select emp_id, salary, dept_code from employee
union
select * from department;
--"expression must have same datatype as corresponding expression"create table tempA(
temp_col varchar(10)
);
create table tempB(
temp_col varchar(10)
);
insert into tempA values(1);
insert into tempA values(2);
insert into tempA values(3);
select * from tempA;
insert into tempB values(2);
insert into tempB values(3);
insert into tempB values(4);
select * from tempB;union : 중복제거하여 합쳐준다.
-- union : 중복제거하여 합쳐준다.
select * from tempA
union
select * from tempB;
union all : 중복제거 없이 다 합쳐준다.
union all : 중복제거 없이 다 합쳐준다.
select * from tempA
union all
select * from tempB;
intersect : 겹치는 부분만 보여준다.
-- intersect : 겹치는 부분만 보여준다.
select * from tempA
intersect
select * from tempB;
minus : 결과값을 제외
--minus : 결과값을 제외
select * from tempA
minus
select * from tempB;
-- 2, 3은 겹치므로 제외, 4는 원래 없었으니 없음
sequence : 순서
값의 순서를 기억하는 객체
create table cafe(
pid number primary key,
pname varchar(30) not null,
price number default 0 not null
);
insert into cafe values(1001,'Americano',2000);
insert into cafe values(1002,'Cafe Latte',2500);
insert into cafe values(1003,'Cafe Mocha',3000);
select * from cafe;--시퀀스 : 값의 순서를 기억하는 객체
-- sequence : 순서
create sequence cafe_seq
start with 1004
increment by 1
nomaxvalue
nocache;
--nocache를 주지 않으면 오라클 시스템이 값을 변경
select sequence_name from user_sequences;
--딕셔너리 : 내린 명령을 기억하는 내장 테이블
insert into cafe values(cafe_seq.nextval,'Caramel macchiato',3500);
DML : 테이블을 대상으로 데이터 수정
delete : 데이터 지우기
--sequence 값을 줄이고 싶을때 : 인위적으로 건드리는 문법 없음
--1. 새로 만들기
--2. increment by를 -로 변경
--DDl 객체 지우기 ; drop
--하나만 지우기 (데이터 DML : delete)
--delete from cafe 는 모든 행을 지우므로, where절로 지울 행 정해주기
delete from cafe where pid=1004;
select * from cafe;
update : 변경하기
--DML : 테이블을 대상으로 데이터 수정
--DML : update
update cafe
set price=3500, pname='Hot choco'
where pid =1003;
select * from cafe;-- 데이터베이스
-- DDL / DML / DCL / TCL
-- DQL
-- 기본 SELECT / Function
-- GroupBy / JOIN
GroupBy
select dept_code
from employee ;
select dept_code
from employee
group by dept_code ;
-- 부서코드별로 그룹지어서 출력
부서코드 별로 묶이면 다른 column은 어떨게 될까?
그룹화 시킨 대상만 출력 가능
select emp_name, dept_code
from employee
group by dept_code ;
--ORA-00979: not a GROUP BY expression
--00979. 00000 - "not a GROUP BY expression"그룹별 합계는 출력 가능 (그룹화된컬럼, 그룹함수)
select dept_code , sum(salary)
from employee
group by dept_code ;
-- 직급별 급여 평균
select
job_code "직급 코드" ,
to_char(avg(salary),'L999,999,999') "급여 평균" ,
count(*) "인원 수"
from employee
group by job_code
order by job_code;
-- order by는 group by 뒤에
-- count(*) 직급별 인원수-- 부서 별 인원수
select
dept_code "부서 코드" ,
count(*) "인원 수"
from employee
group by dept_code
order by dept_code;-- 년생과 인원수
select
substr(emp_no,1,2) "년생" ,
count(*) "인원 수"
from employee
group by
substr(emp_no,1,2);-- 성별 인원수
select
case
when substr(emp_no,8,1) in ('2','4')
then '여'
when substr(emp_no,8,1) in ('1','3')
then '남'
end "성별" ,
count(*) "인원 수"
from employee
group by
case
when substr(emp_no,8,1) in ('2','4')
then '여'
when substr(emp_no,8,1) in ('1','3')
then '남'
end;select
decode(substr(emp_no,8,1),1,'남',2,'여',3,'남',4,'여') "성별",
count(*) "인원 수"
from employee
group by
decode(substr(emp_no,8,1),1,'남',2,'여',3,'남',4,'여');이중 그룹화
-- 부서별 직급코드 분류
select
dept_code,
job_code
from employee
group by dept_code, job_code
order by 1,2;
select
dept_code,
job_code,
sum(salary)
from employee
group by dept_code, job_code
order by 1,2;
-- null 대신 부서없음으로 표시
select
nvl(dept_code,'부서없음') "부서 내",
job_code "직급 별",
sum(salary) "급여 합계"
from employee
group by dept_code, job_code
order by 1,2;
-- 컬럼명 뒤에 텍스트 붙일 수 있음
select
nvl(dept_code,'부서없음')||'부서 내' "부서 내",
job_code||'직급의' "직급 별",
sum(salary) "급여 합계"
from employee
group by dept_code, job_code
order by 1,2;그룹화 예제
-- 1. 직급별 총 급여 및 연봉을 출력하세요.
select
job_code "직급",
to_char(sum(salary),'L999,999,999') "급여 합계",
to_char(sum(salary*12),'L999,999,999') "연봉합계"
from employee
group by job_code
order by 1;-- 2. 부서코드, 급여합계, 급여평균, 인원수
select
nvl(dept_code,'부서없음') "부서코드",
to_char(sum(salary),'L999,999,999') "급여합계",
to_char(avg(salary),'L999,999,999') "급여평균",
count(*) "인원수"
from employee
group by dept_code
order by 1;-- 3. 부서별로 보너스를 지급받는 직원의 총합
select
nvl(dept_code,'부서없음') "부서코드",
count(*) "인원수"
from employee
where bonus is not null
group by dept_code
order by 1;-- 4. J1과 J2 직급을 제외한 나머지 직급의 인원 수 및 급여 평균
select
job_code "부서코드",
count(*) "인원수",
to_char(avg(salary),'L999,999,999') "급여 평균"
from employee
where job_code != 'J1' and job_code != 'J2'
group by job_code
order by 1;-- 5. 부서내 성별별 인원수
select
job_code "부서코드",
decode(substr(emp_no,8,1),1,'남',2,'여',3,'남',4,'여') "성별",
count(*) "인원수"
from employee
group by job_code, substr(emp_no,8,1)
order by 1, 2;-- 6. 나이대별 인원 수 및 급여 평균
select
decode(substr(emp_no,1,1),6,'60년대생',7,'70년대생',8,'80년대생') "나이 대",
count(*) "인원수",
to_char(avg(salary),'L999,999,999') "급여 평균"
from employee
group by substr(emp_no,1,1)
order by 1;-- 7. 성별 및 연령 대별 인원 수
select
decode(substr(emp_no,8,1),1,'남',2,'여',3,'남',4,'여') "성별",
decode(substr(emp_no,1,1),6,'60년대생',7,'70년대생',8,'80년대생') "연령 대",
count(*) "인원수"
from employee
group by substr(emp_no,8,1), substr(emp_no,1,1)
order by 2, 1;
having
그룹화된 데이터에 대한 조건을 명시할때 쓰는 문법
그룹함수 포함해서를 사용할때 사용하는 조건
그룹화된 데이터라도 그룹함수를 포함하지 않는다면 where절을 사용한다.
--where절에서는 그룹함수를 쓸 수 없다.
select
nvl(dept_code, '인턴') "부서코드",
floor(avg(salary)) "급여평균"
from employee
where floor(avg(salary)) > 3000000
group by dept_code
order by 1;
--00934. 00000 - "group function is not allowed here"
-- 그룹함수는 where절에 허용 되지 않는다.--having : 그룹화된 데이터에 대한 조건을 명시할때 쓰는 문법
select
nvl(dept_code, '인턴') "부서코드",
floor(avg(salary)) "급여평균"
from employee
group by dept_code
having floor(avg(salary)) >= 3000000
order by 1;rollup
집계함수, 더 많은 내용들을 출력 할때 사용된다.
데이터베이스 내에서 내용을 확인하는 용도로 주로 쓰임
group by를 보조하는 기능
select
nvl(dept_code, '인턴') "부서",
sum(salary) "부서별 합계"
from employee
group by rollup(nvl(dept_code, '인턴'))
order by 1;
--rollup의 인자값이 1개 이상이어도 된다.
select
dept_code,
job_code,
sum(salary)
from employee
group by rollup(dept_code, job_code)
order by 1,2;
-- 집계함수로 인해 추가된 (null)값 처리 : 실패
select
nvl(dept_code,'부서없음') "부서코드",
nvl(job_code,'직급별총계') "직급코드" ,
sum(salary) "계"
from employee
group by rollup(nvl(dept_code,'부서없음'), nvl(job_code,'직급별총계'))
order by 1,2;
grouping
null값을 조사하는 함수
원래 존재하던 null은 0을 반환
집계 함수를 통해 발생한 null은 1을 반환
--grouping
select
dept_code,
grouping(dept_code),
job_code,
grouping(job_code),
sum(salary) "계"
from employee
group by rollup(dept_code,job_code)
order by 1,3;
-- 집계함수로 인해 추가된 (null)값 처리 : grouping
select
decode(grouping(dept_code),0,dept_code,1,'총계') "부서코드",
decode(grouping(job_code),0,nvl(dept_code,'인턴'),1,'부서별 총계') "직급코드",
sum(salary) "계"
from employee
group by rollup(dept_code,job_code)
order by 1;
select
decode(
grouping(dept_code),
0,
nvl(dept_code,'인턴'),
1,
'총계'
) "부서코드",
decode(
grouping(job_code),
0,
nvl(job_code,'인턴'),
1,
case
when grouping(dept_code) = 1
then '총계'
else '부서별 총계'
end
) "직급코드",
ltrim(to_char(sum(salary),'L999,999,999'),' ') "계"
from employee
group by rollup(dept_code,job_code)
order by 1,2; 
cube
rollup 함수의 업그레이드
-- cube
select
dept_code,
job_code,
sum(salary)
from employee
group by cube(dept_code, job_code)
order by 1,2;
select
decode(
grouping(dept_code),
0,
nvl(dept_code,'인턴'),
1,
case
when grouping(job_code) = 1
then '총계'
else '직급별 합계'
end
) "부서코드",
decode(
grouping(job_code),
0,
nvl(job_code,'인턴'),
1,
case
when grouping(dept_code) = 1
then '총계'
else '부서별 합계'
end
) "직급코드",
ltrim(to_char(sum(salary),'L999,999,999'),' ') "급여"
from employee
group by cube(dept_code, job_code)
order by 1,2;
select문 동작 순서
-- select문 동작 순서
-- from ~에서
-- where ~한 조건을 가진 것들을
-- group by
-- having
-- select
-- order by 정렬값은 항상 마지막
select
nvl(dept_code, '인턴') "부서코드",
floor(avg(salary)) "급여평균"
from employee
group by nvl(dept_code, '인턴')
order by 급여평균;JOIN 문
한 테이블에 여러 테이블을 참여시켜서 동작시키는 문법
Cartesian product (카티션곱)
cross join 교차 조인
--내장테이블 딕셔너리
select table_name from user_tables;
select * from DEPARTMENT;
select * from LOCATION;
select * from NATIONAL;
select * from DEPARTMENT, location;
inner join
where절에 있는 조건을 충족시키는 조인
select * from DEPARTMENT, location
where location_id = local_code;
select dept_id, dept_title, national_code from DEPARTMENT, location
where location_id = local_code;
--inner join : where절에 있는 조건을 충족시키는 조인
select
emp_name,
dept_title
from employee, DEPARTMENT
where dept_code = dept_id;
Outer join
조건이 일치하지 않지만 그래도 출력해야 되는 경우
Left Outer join , Right Outer join
select
emp_name,
dept_title
from employee, DEPARTMENT
where dept_code = dept_id(+);
-- outer join : dept_code쪽은 조건을 충족시키지 않아도 일단 출력
-- 오른쪽에 내용이 없어도 null로 채워달라
select
emp_name,
dept_title
from employee, DEPARTMENT
where dept_code(+) = dept_id;
ANSI 표준 SQL문
DBMS를 가리지 않는 문법, 직관성은 떨어지나 어디에나 붙여넣어도 동작
ANSI 표준에서는 조인을 명시해야 함
우리가 쓴 문법은 오라클 전용 문법, mysql에서는 쓰지 않음
left, right join이라는 용어는 ANSI표준에서 나온말
-- 오라클 natural join
select * from employee, department;
-- ANSI natural join
select * from
employee natural join department;--오라클 inner join
select
dept_id,
dept_title,
national_code
from DEPARTMENT, location
where location_id = local_code;
-- ANSI inner join
select
dept_id,
dept_title,
national_code
from DEPARTMENT inner join location
on (location_id = local_code);
-- inner는 생략해도 디폴트
select
dept_id,
dept_title,
national_code
from DEPARTMENT join location
on (location_id = local_code);;-- 오라클 inner join
select
emp_name,
dept_title
from employee, DEPARTMENT
where dept_code = dept_id;
-- ANSI inner join
select
emp_name,
dept_title
from employee join DEPARTMENT
on (dept_code = dept_id);-- 오라클 outer join
select
emp_name,
dept_title
from employee, DEPARTMENT
where dept_code(+) = dept_id;
-- ANSI 표준 left outer join
select
emp_name,
dept_title
from employee left outer join DEPARTMENT
on (dept_code = dept_id);
select
emp_name,
dept_title
from employee left join DEPARTMENT
on (dept_code = dept_id);
-- left를 쓸때는 outer 생략 가능-- 오라클 outer join
select
emp_name,
dept_title
from employee, DEPARTMENT
where dept_code = dept_id(+);
-- ANSI 표준 right outer join
select
emp_name,
dept_title
from employee right outer join DEPARTMENT
on (dept_code = dept_id);Full outer join
오라클에서는 지원하지 않는 문법
--ANSI 표준 full outer join
select
emp_name,
dept_title
from employee full outer join DEPARTMENT
on (dept_code = dept_id);
Select <column> from <table> where <조건> order by
-- 1. 근속년수가 10년 이상인 직원들의 이름, 급여, 근속연수 (소수점출력X)를 근속연수 오름차순으로 정렬하여 출력
-- 단, 여기서 급여는 50% 인상된 급여로 출력하시오
select emp_name 이름, salary*1.5 급여, floor((sysdate-hire_date)/365) 근속연수
from employee
where ((sysdate-hire_date)/365) > 10
order by 3 ;-- 2. 입사일이 99/01/01 ~ 10/01/01 인 사람 중에서
-- 급여가 2000000원 이하인 사람의
-- 이름, 주민번호, 이메일, 폰번호, 급여를 검색하세요
select emp_name 이름, emp_no 주민번호, email 이메일, salary 급여
from employee
where hire_date between '99/01/01' and '10/01/01' and salary <= 2000000;--null value 함수 : 첫번째 인자값이 널이 아니면 그대로 반환, 널이면 두번째 인자값 반환
--출력할 대상 컬럼의 타입에 따라 변경할 타입을 정해줘야함 (bonus는 숫자형column)
select emp_name, nvl(bonus, 0) from employee;-- 3. 급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여
-- 이름, 주민번호, 급여, 부서코드를 주민번호 순으로(내림차순) 출력하세요.
-- 단, 부서코드가 null인 사람은 부서코드가 '없음'으로 출력하세요.
select emp_name 이름, emp_no 주민번호, salary 급여, nvl(dept_code, '없음') 부서코드
from employee
where (salary between 2000000 and 3000000) and emp_no like '_______2______' and emp_no like '__04__________'
order by 2 desc;-- 4. 남자 사원 중 보너스가 없는 사원의 오늘까지의 근무일을 측정하여
-- 1000일 마다(소수점 제외) 급여의 10% 보너스를 계산하여
-- 이름, 특별 보너스 (계산 금액) 결과를 출력하세요.
-- 단, 이름 순으로 오름차순 정렬하여 출력하세요.
select emp_name 이름, salary*floor((sysdate-hire_date)/1000)*0.1 특별보너스
from employee
where emp_no like '_______1______' and bonus is null
order by 1 desc;강사님 코드
-- 1. 근속년수가 10년 이상인 직원들의,
-- 이름, 급여, 근속년수 (소수점 출력 X)를
-- 근속년수 오름차순으로 정렬하여 출력
-- 단 여기서 급여는 50% 인상된 급여로 출력하세요.
select emp_name,salary*1.5 as "인상 급여", floor((sysdate-hire_date)/365) as "근속년수"
from employee
where (sysdate-hire_date)/365 >= 10
order by 3;
-- 2. 입사일이 99/01/01 ~ 10/01/01 인 사람 중에서
-- 급여가 2000000 원 이하인 사람의
-- 이름,주민번호,이메일,폰번호,급여를 검색하세요.
select emp_name,emp_no,email,phone,salary
from employee
where (hire_date between '99/01/01' and '10/01/01')
and salary <= 2000000;
-- 3. 급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여
-- 이름,주민번호,급여,부서코드를 주민번호 순으로(내림차순) 출력하세요.
-- 단, 부서코드가 null인 사람은 부서코드가 '없음' 으로 출력하세요.
select emp_name, emp_no, salary, nvl(dept_code,'없음')
from employee
where (salary between 2000000 and 3000000) and
emp_no like '___4__-2%'
order by emp_no desc;
-- 4. 남자 사원 중 보너스가 없는 사원의 오늘까지 근무일을 측정하여
-- 1000일 마다(소수점 제외) 급여의 10% 보너스를 계산하여
-- 이름,특별 보너스 (계산 금액) 결과를 출력하세요.
-- 단, 이름 순으로 오름 차순 정렬하여 출력하세요.
select emp_name,floor((sysdate-hire_date)/1000)*(salary*0.1) as "특별 보너스" from employee
where emp_no like ('______-1%') and bonus is null
order by emp_name;DQL 함수
-- 함수 ( function ) - 자바의 메서드와 동일한 동작 방식
-- 단일 행 함수 : 행 하나하나 마다 적용되는 함수
-- floor / nvl
select emp_name, nvl(dept_code, '부서없음')
from employee;
-- 그룹 함수 : 모든 행의 값을 추출해서 하나의 결과로 만들기
-- sum
select sum(salary) from employee;length lengthb
-- length
select emp_name, length(emp_name), email, length(email)
from employee;
-- lengthb : 바이트 길이
select emp_name, lengthb(emp_name), lengthb('AB')
from employee;dual 테이블
-- dual 테이블 : 오라클 사용자가 테스트 목적으로 사용하는 단일행 단일열 테이블
-- 한번만 출력하고 싶다면 행이 하나인 테이블 필요
select 'Hello World' from employee;
select sysdate from dual;instr
-- instr(String, STR, ?, ?) : 인자값 (타겟, 찾을문자열, 어디서부터, 몇번째것인지)
select instr('Hello World Hi High','H',1, 2) from dual;
-- 13 리턴
select instr('Hello World Hi High','H',2, 1) from dual;
-- 13 리턴
select instr('Hello World Hi High','H',2, 2) from dual;
-- 16 리턴
select instr('Hello World Hi High','H',2, 3) from dual;
-- 0 리턴 (찾지 못함)
select instr('Hello World Hi High','H',-1, 1) from dual;
-- 16 리턴 (세번째 인자값이 -1이면 뒤에서부터 찾기)
select instr('Hello World Hi High','H',-5, 1) from dual;
-- 13 리턴--employee 테이블의 email에서 @문자의 위치를 출력하세요.
select email, instr(email,'@',1, 1) from employee;substr
--substr
select
substr('Hello Java Programmer',7,4)
from
dual;
-- Java
select
substr('Hello Java Programmer',-10,7)
from
dual;
-- Program
select
substr('안녕 자바 프로그래머',4,2)
from
dual;
-- 자바-- 1. Employee테이블에서 직원들의 성씨만 중복없이 출력하세요
select
distinct substr(emp_name,1,1)
from
employee;-- 2. Employee 테이블에서 남자직원의 사원번호, 사원명, 주민번호, 연봉을 출력하세요.
-- 주민번호의 뒷6자리는 *처리하세요
select
emp_id 사원번호, emp_name 사원명, substr(emp_no,1,8)||'******' 주민번호, salary*12 연봉
from
employee
where
substr(emp_no,8,1)=1;-- replace (대상, 찾을것, 변경할것)
select replace('Python Programmer','Python','Java') from dual;숫자 관련 함수
-- abs ( absolute )
select abs(-10) from dual;--mod ( 나머지연산 )
select mod(10,3) from dual;
-- 1
select mod(10,4) from dual;
-- 2--round : 반올림함수
select round(126.456 , 3) from dual;
-- 126.456
-- 3번째 자리로 반올림 (cf. 3번째 자리에서 반올림 아님)
select round(126.456 , 2) from dual;
-- 126.46
select round(126.456 , 1) from dual;
-- 126.5
select round(126.456 , 0) from dual;
-- 126
select round(126.456 , -1) from dual;
-- 130
select round(126.456 , -2) from dual;
-- 100-- floor : 버림함수
select floor(126.456) from dual;
-- trunc : 소수점 자리수에서 버림 함수
select trunc (126.456 , 1) from dual;
-- 126.4
select trunc (126.456 , 2) from dual;
-- 126.45
select trunc (126.456 , -1) from dual;
-- 120
select trunc (126.456 , 0) from dual;
-- 126날짜 관련 함수
(예제 실행시 sysdate 20/03/16 (월) )
-- sysdate 현재 날짜를 반환하는 키워드
select sysdate from dual;-- months_between : 두개의 시간값을 인자로 받아 두 시간 사이의 개월 수 반환
select emp_name, floor(months_between(sysdate, hire_date)) 근무개월
from employee;
-- 뺄셈으로는 일수가 나온다!-- add_months : 특정 날짜에 개월수를 더해서 반환
select emp_name, add_months(hire_date,6)
from employee;
select add_months(sysdate, 50) from dual;
-- 24/05/16
-- 일수 구하기
select add_months(sysdate, 18)-sysdate 일 from dual;
-- 549-- next_day : 인자로 전달받은 날짜에서 두번째 인자로 전달받은 요일 중, 가장 가까운 다음 요일을 출력
-- (숫자표현 : 1=일요일 ... 7=토요일)
select next_day(sysdate, '화요일') from dual;
-- 20/03/17
select next_day(sysdate, '월') from dual;
-- 20/03/23
select next_day(sysdate, 7) from dual;
-- 20/03/21-- last_day : 첫번째 인자로 전달받은 날짜의 달에 해당하는 마지막 날짜 반환
select last_day(sysdate) from dual;
-- 20/03/31
select last_day(sysdate+16) from dual;
-- 20/04/30
-- 다음달의 마지막 날짜 구하기
select last_day(add_months(sysdate,1)) from dual;
-- 20/04/30-- extract : 인자값으로 전달된 날짜에서 원하는 값을 추출
select extract (year from sysdate) from dual;
-- 2020
select extract (month from sysdate) from dual;
-- 3
select extract (day from sysdate) from dual;
-- 16-- Employee 테이블에서 사원의 이름, 입사년, 입사월, 입사일, 년차를 출력하세요.
-- 각 입사날짜는 YYYY년 M월 D일로 출력하도록 하세요.
-- 년차 출력은 소수점일 경우 올림으로 하여 출력하세요 (28.144 -> 29년차)
-- (출력시 정렬은 입사년 기준으로 오름차순)
select
emp_name 이름,
extract (year from hire_date) 입사년,
extract (month from hire_date)입사월,
ceil((sysdate - hire_date)/365) 년차
from employee;
-- 2. 특별 보너스를 지급하기 위하여 자료가 필요합니다.
-- 입사일을 기점으로 다음달 1일 부터 6개월 후를 출력하세요.
-- 이름, 입사일, 기준일, 기준일+6, 기준달(월)
-- ex) 90년2월6일 입사 -> 90년3월1일 부터 계산
-- ex) 90년2월26일 입사 -> 90년3월1일 부터 계산
-- ex) 97년12월1일 입사 -> 98년1월1일 부터 계산
-- 출력시 입사일 기준으로 정렬하세요.
select
emp_name as "이름",
hire_date as "입사일",
last_day(hire_date+1) as "기준일",
add_months(last_day(hire_date+1),6) as "6개월 후",
extract (month from add_months(last_day(hire_date+1),6)) as "기준달"
from employee
order by 2;형 변환 함수
-- 현 변환 함수
-- to_char : 날짜 또는 숫자를 문자형으로 변경하는
-- date to char
select to_char(sysdate, 'yyyy@mm@dd') from dual;
--2020@03@16
select to_char(sysdate, 'yyyy/mm/dd/day') from dual;
--2020/03/16/월요일
select to_char(sysdate, 'yyyy/month/dd/day') from dual;
--2020/3월 /16/월요일
select to_char(sysdate, 'yyyy/mm/dd hh:mi:ss') from dual;
--2020/03/16 02:23:28
select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;
--2020/03/16 14:24:28
select to_char(sysdate, 'yyyy/mm/dd hh12:mi:ss') from dual;
--2020/03/16 02:24:46 (뒤에12 안넣어도 디폴트)
select to_char(sysdate, 'yyyy/mm/dd hh시mi분ss초') from dual;
--01821. 00000 - "date format not recognized"
select to_char(sysdate, 'yyyy/mm/dd hh"시"mi"분"ss"초"') from dual;
--2020/03/16 02시25분51초
select to_char(sysdate, 'yyyy/mm/dd(dy) hh"시"mi"분"ss"초"') from dual;
--2020/03/16(월) 02시27분18초
select emp_name, to_char(hire_date, 'yyyy/mm/dd(dy)') from employee;문자로 바꾸기 (형식 주기)
-- number to char
select to_char(12345678, '000,000,000') from dual;
-- 012,345,678
select to_char(12345678, '999,999,999') from dual;
-- 12,345,678
select to_char(12345678, '999,999') from dual;
--######## (형식이 부족하면 에러)
select to_char(12345678, 'L999,999,999') from dual;
--￦12,345,678 (local 통화)
select emp_name, to_char(salary, 'L999,999,999') from employee;
-- ltrim rtrim 왼쪽 공백지우기 / 오른쪽 공백 지우기
select emp_name, ltrim(to_char(salary, 'L999,999,999'),' ') from employee;-- 1. employee 테이블에서 2000년도 이후에 입사한 사원의 사번, 이름, 입사일을 조회
select emp_id 사번,emp_name 이름, hire_date 입사일
from employee
where hire_date > to_date(20000101,'YYYYMMDD')
order by 3;
--내가 푼 방식
select emp_id 사번,emp_name 이름, hire_date 입사일
from employee
where to_char(hire_date,'yyyy')>=2000
order by 3;조건 비교 함수
-- dcode
-- 매서드의 인자 부분이 가변 인자로 되어있다 : 인자개수 변함
select
emp_name ,
decode(substr(emp_no,8,1),1,'남',2,'여') 성별
from employee;-- case
select
emp_name,
case
when substr(emp_no,8,1) = 1 then '남'
when substr(emp_no,8,1) = 2 then '여'
else '미정'
end "성별"
from employee;
-- 부등호 표기 가능 when substr(emp_no,8,1) >= 1 then '남'
-- 60년대생 직원들 중, 65년생 이상의 직원은 60년생 후반
-- 65년생 미만의 직원은 60년생 초반 이라고 출력하세요
-- 이름 / 주빈번호 / 정보 출력
select
emp_name 이름,
emp_no 주민번호,
case
when substr(emp_no,1,2) >= 65 then '60년생 후반'
when substr(emp_no,1,2) < 65 then '60년생 초반'
end "정보"
from employee
where substr(emp_no,1,2) between 60 and 69;그룹 함수
sum 합계 / avg 평균 / count 행의 개수
총합sum 합계
select ltrim(to_char(sum(salary), 'L999,999,999'),' ') 직원급여총합
from employee;
-- 1. 부서코드 D5 인 직원들의 급여 총합
select ltrim(to_char(sum(salary), 'L999,999,999'),' ') "D5부서 급여총계"
from employee
where dept_code ='D5';-- 2. 여직원들의 급여 총합
select ltrim(to_char(sum(salary), 'L999,999,999'),' ') "여직원 급여총계"
from employee
where substr(emp_no,8,1) = 2;avg 평균
-- avg 평균
select ltrim(to_char(avg(salary), 'L999,999,999'),' ')||'원' "직원급여평균"
from employee;count 행의 개수
select count(emp_name) from employee;
-- 23
select count(dept_code) from employee;
-- 21 (null값 세지 않음)
select count(*) from employee;
-- 23 (행의 개수)
select count(*) from employee where dept_code='D5';
-- 6 (D5인 행수)
select count(distinct dept_code) from employee;
-- 6 (부서의 개수)max / min - 촤대값, 최소값 함수
-- max / min - 촤대값, 최소값 함수
select max(salary), min(salary) from employee;-- 1. 2020년4월15일까지 몇일 남았는지 출력
select floor(to_date(20200415,'YYYYMMDD')-sysdate)||'일' "4월15일까지" from dual;
-- 29일-- 2. 2021년3월1일은 무슨 요일일까?
select to_char(to_date(20210301,'YYYYMMDD'),'day') "내년3월1일은" from dual;
-- 월요일-- 3. 직원의 이름과 이메일 주소중 아이디 부분만 출력하시오
select
emp_name 이름,
substr(email,1,instr(email,'@',1)-1) 아이디
from employee;-- 4. 70년대생 직원들의 급여총합
select ltrim(to_char(sum(salary), 'L999,999,999'),' ') "70년대생 급여총계"
from employee
where substr(emp_no,1,2) between 70 and 79;
--￦21,316,240-- 5. 60년생의 직원명과 년생, 보너스 값을 출력하시오
-- 그떄 보너스 값이 null인 경우에는 0이라고 출력 되게 만드시오
select
emp_name 직원명,
substr(emp_no,1,2) 년생,
case
when bonus is null then 0
when bonus is not null then bonus
end "보너스"
from employee
where substr(emp_no,1,2) between 60 and 69;DDL 위주로 공부
서술형 / 문제해결 시나리오
DBMS
데이터의 보관, 보안을 담당
쿼리문
SQL 명령을 통해 데이터를 관리하는 솔루션
DDL - create / drop / alter
create -> table -> 테이블 구조 (컬럼명 자료형 제약조건)
primary key, unique, not null, check, Foreign key (참조 무결성
테이블 목록 보기
select table_name from user_tables;
default
create table beverage(
text number default 10 not null
);
insert into beverage values(default);
alter - add 기존에 존재하는 테이블에 칼럼 추가하기
-- alter
create table members(
user_id varchar(20) primary key,
user_pw varchar(20) not null
);
-- 기존에 존재하는 테이블에 컬럼 추가하기
alter table members add (user_name varchar(20) not null);
alter table members add (user_age number default 0 not null);
desc members;
alter - modify 기존에 존재하는 테이블 컬럼의 자료형 또는 제약조건 수정
-- 기존에 존재하는 테이블 컬럼의 자료형 또는 제약조건 수정
alter table members modify (user_name varchar(30) constraint user_name_uq unique );
desc members;
alter - rename 기존에 존재하는 테이블 컬럼의 컬럼명을 수정
컬럼명이 변경되어도 제약조건은 존재
제약조건 이름을 변경하는 방법 1 - drop 후 수정
제먁조건 이름을 변경하는 방법 2 - rename constraint
기존 테이블의 컬럼 삭제
테이블 이름 자체 수정 rename
예제
create table Product(
pid varchar(20) primary key,
pname varchar(30) constraint pname_uq unique,
pdate timestamp default sysdate
);
create table trade_history(
tid number primary key,
tdate timestamp default sysdate constraint trade_history_tdate_nn not null,
pid varchar(20) references Product(pid)
);
alter table Product add (price number default 0);
alter table Product modify (pdate timestamp default sysdate constraint Product_pdate_nn not null );
alter table Product modify (pname varchar(50));
alter table Product rename column pname to psign;
alter table Product drop constraint pname_uq;
select * from user_constraints;
desc Product;
desc trade_history;
drop table trade_history;
drop table Product;SQL
Select
새 연습용 sql 파일
DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;
DROP TABLE JOB;
DROP TABLE LOCATION;
DROP TABLE NATIONAL;
DROP TABLE SAL_GRADE;
--------------------------------------------------------
-- DDL for Table DEPARTMENT
--------------------------------------------------------
CREATE TABLE "KH"."DEPARTMENT"
( "DEPT_ID" CHAR(2 BYTE),
"DEPT_TITLE" VARCHAR2(35 BYTE),
"LOCATION_ID" CHAR(2 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "KH"."DEPARTMENT"."DEPT_ID" IS '부서코드';
COMMENT ON COLUMN "KH"."DEPARTMENT"."DEPT_TITLE" IS '부서명';
COMMENT ON COLUMN "KH"."DEPARTMENT"."LOCATION_ID" IS '지역코드';
COMMENT ON TABLE "KH"."DEPARTMENT" IS '부서';
REM INSERTING into KH.DEPARTMENT
SET DEFINE OFF;
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D1','인사관리부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D2','회계관리부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D3','마케팅부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D4','국내영업부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D5','해외영업1부','L2');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D6','해외영업2부','L3');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D7','해외영업3부','L4');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D8','기술지원부','L5');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D9','총무부','L1');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK2
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK2" ON "KH"."DEPARTMENT" ("DEPT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table DEPARTMENT
--------------------------------------------------------
ALTER TABLE "KH"."DEPARTMENT" ADD CONSTRAINT "DEPARTMENT_PK" PRIMARY KEY ("DEPT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "KH"."DEPARTMENT" MODIFY ("LOCATION_ID" NOT NULL ENABLE);
ALTER TABLE "KH"."DEPARTMENT" MODIFY ("DEPT_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table EMPLOYEE
--------------------------------------------------------
CREATE TABLE "KH"."EMPLOYEE"
( "EMP_ID" VARCHAR2(3 BYTE),
"EMP_NAME" VARCHAR2(20 BYTE),
"EMP_NO" CHAR(14 BYTE),
"EMAIL" VARCHAR2(25 BYTE),
"PHONE" VARCHAR2(12 BYTE),
"DEPT_CODE" CHAR(2 BYTE),
"JOB_CODE" CHAR(2 BYTE),
"SAL_LEVEL" CHAR(2 BYTE),
"SALARY" NUMBER,
"BONUS" NUMBER,
"MANAGER_ID" VARCHAR2(3 BYTE),
"HIRE_DATE" DATE,
"ENT_DATE" DATE,
"ENT_YN" CHAR(1 BYTE) DEFAULT 'N'
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "KH"."EMPLOYEE"."EMP_ID" IS '사원번호';
COMMENT ON COLUMN "KH"."EMPLOYEE"."EMP_NAME" IS '직원명';
COMMENT ON COLUMN "KH"."EMPLOYEE"."EMP_NO" IS '주민등록번호';
COMMENT ON COLUMN "KH"."EMPLOYEE"."EMAIL" IS '이메일';
COMMENT ON COLUMN "KH"."EMPLOYEE"."PHONE" IS '전화번호';
COMMENT ON COLUMN "KH"."EMPLOYEE"."DEPT_CODE" IS '부서코드';
COMMENT ON COLUMN "KH"."EMPLOYEE"."JOB_CODE" IS '직급코드';
COMMENT ON COLUMN "KH"."EMPLOYEE"."SAL_LEVEL" IS '급여등급';
COMMENT ON COLUMN "KH"."EMPLOYEE"."SALARY" IS '급여';
COMMENT ON COLUMN "KH"."EMPLOYEE"."BONUS" IS '보너스율';
COMMENT ON COLUMN "KH"."EMPLOYEE"."MANAGER_ID" IS '관리자사번';
COMMENT ON COLUMN "KH"."EMPLOYEE"."HIRE_DATE" IS '입사일';
COMMENT ON COLUMN "KH"."EMPLOYEE"."ENT_DATE" IS '퇴사일';
COMMENT ON COLUMN "KH"."EMPLOYEE"."ENT_YN" IS '재직여부';
COMMENT ON TABLE "KH"."EMPLOYEE" IS '사원';
REM INSERTING into KH.EMPLOYEE
SET DEFINE OFF;
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('200','선동일','621235-1985634','sun_di@kh.or.kr','01099546325','D9','J1','S1',8000000,0.3,null,to_date('90/02/06','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('201','송종기','631156-1548654','song_jk@kh.or.kr','01045686656','D9','J2','S1',6000000,null,'200',to_date('01/09/01','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('202','노옹철','861015-1356452','no_hc@kh.or.kr','01066656263','D9','J2','S4',3700000,null,'201',to_date('01/01/01','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('203','송은희','631010-2653546','song_eh@kh.or.kr','01077607879','D6','J4','S5',2800000,null,'204',to_date('96/05/03','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('204','유재식','660508-1342154','yoo_js@kh.or.kr','01099999129','D6','J3','S4',3400000,0.2,'200',to_date('00/12/29','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('205','정중하','770102-1357951','jung_jh@kh.or.kr','01036654875','D6','J3','S4',3900000,null,'204',to_date('99/09/09','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('206','박나라','630709-2054321','pack_nr@kh.or.kr','01096935222','D5','J7','S6',1800000,null,'207',to_date('08/04/02','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('207','하이유','690402-2040612','ha_iy@kh.or.kr','01036654488','D5','J5','S5',2200000,0.1,'200',to_date('94/07/07','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('208','김해술','870927-1313564','kim_hs@kh.or.kr','01078634444','D5','J5','S5',2500000,null,'207',to_date('04/04/30','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('209','심봉선','750206-1325546','sim_bs@kh.or.kr','0113654485','D5','J3','S4',3500000,0.15,'207',to_date('11/11/11','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('210','윤은해','650505-2356985','youn_eh@kh.or.kr','0179964233','D5','J7','S5',2000000,null,'207',to_date('01/02/03','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('211','전형돈','830807-1121321','jun_hd@kh.or.kr','01044432222','D8','J6','S5',2000000,null,'200',to_date('12/12/12','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('212','장쯔위','780923-2234542','jang_zw@kh.or.kr','01066682224','D8','J6','S5',2550000,0.25,'211',to_date('15/06/17','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('213','하동운','621111-1785463','ha_dh@kh.or.kr','01158456632',null,'J6','S5',2320000,0.1,null,to_date('99/12/31','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('214','방명수','856795-1313513','bang_ms@kh.or.kr','01074127545','D1','J7','S6',1380000,null,'200',to_date('10/04/04','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('215','대북혼','881130-1050911','dae_bh@kh.or.kr','01088808584','D5','J5','S4',3760000,null,null,to_date('17/06/19','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('216','차태연','770808-1364897','cha_ty@kh.or.kr','01064643212','D1','J6','S5',2780000,0.2,'214',to_date('13/03/01','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('217','전지연','770808-2665412','jun_jy@kh.or.kr','01033624442','D1','J6','S4',3660000,0.3,'214',to_date('07/03/20','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('218','이오리','870427-2232123','loo_or@kh.or.kr','01022306545',null,'J7','S5',2890000,null,null,to_date('16/11/28','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('219','임시환','660712-1212123','im_sw@kh.or.kr',null,'D2','J4','S6',1550000,null,null,to_date('99/09/09','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('220','이중석','770823-1113111','lee_js@kh.or.kr',null,'D2','J4','S5',2490000,null,null,to_date('14/09/18','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('221','유하진','800808-1123341','yoo_hj@kh.or.kr',null,'D2','J4','S5',2480000,null,null,to_date('94/01/20','RR/MM/DD'),null,'N');
Insert into KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('222','이태림','760918-2854697','lee_tr@kh.or.kr','01033000002','D8','J6','S5',2436240,0.35,'100',to_date('97/09/12','RR/MM/DD'),to_date('17/09/12','RR/MM/DD'),'Y');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK" ON "KH"."EMPLOYEE" ("EMP_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table EMPLOYEE
--------------------------------------------------------
ALTER TABLE "KH"."EMPLOYEE" ADD CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("SAL_LEVEL" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("JOB_CODE" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("EMP_NO" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("EMP_NAME" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("EMP_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table JOB
--------------------------------------------------------
CREATE TABLE "KH"."JOB"
( "JOB_CODE" CHAR(2 BYTE),
"JOB_NAME" VARCHAR2(35 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "KH"."JOB"."JOB_CODE" IS '직급코드';
COMMENT ON COLUMN "KH"."JOB"."JOB_NAME" IS '직급명';
COMMENT ON TABLE "KH"."JOB" IS '직급';
REM INSERTING into KH.JOB
SET DEFINE OFF;
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J1','대표');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J2','부사장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J3','부장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J4','차장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J5','과장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J6','대리');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J7','사원');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK1
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK1" ON "KH"."JOB" ("JOB_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table JOB
--------------------------------------------------------
ALTER TABLE "KH"."JOB" ADD CONSTRAINT "JOB_PK" PRIMARY KEY ("JOB_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "KH"."JOB" MODIFY ("JOB_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table LOCATION
--------------------------------------------------------
CREATE TABLE "KH"."LOCATION"
( "LOCAL_CODE" CHAR(2 BYTE),
"NATIONAL_CODE" CHAR(2 BYTE),
"LOCAL_NAME" VARCHAR2(40 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "KH"."LOCATION"."LOCAL_CODE" IS '지역코드';
COMMENT ON COLUMN "KH"."LOCATION"."NATIONAL_CODE" IS '국가코드';
COMMENT ON COLUMN "KH"."LOCATION"."LOCAL_NAME" IS '지역명';
COMMENT ON TABLE "KH"."LOCATION" IS '지역';
REM INSERTING into KH.LOCATION
SET DEFINE OFF;
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L1','KO','ASIA1');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L2','JP','ASIA2');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L3','CH','ASIA3');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L4','US','AMERICA');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L5','RU','EU');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK3
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK3" ON "KH"."LOCATION" ("LOCAL_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table LOCATION
--------------------------------------------------------
ALTER TABLE "KH"."LOCATION" ADD CONSTRAINT "LOCATION_PK" PRIMARY KEY ("LOCAL_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "KH"."LOCATION" MODIFY ("NATIONAL_CODE" NOT NULL ENABLE);
ALTER TABLE "KH"."LOCATION" MODIFY ("LOCAL_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table NATIONAL
--------------------------------------------------------
CREATE TABLE "KH"."NATIONAL"
( "NATIONAL_CODE" CHAR(2 BYTE),
"NATIONAL_NAME" VARCHAR2(35 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "KH"."NATIONAL"."NATIONAL_CODE" IS '국가코드';
COMMENT ON COLUMN "KH"."NATIONAL"."NATIONAL_NAME" IS '국가명';
COMMENT ON TABLE "KH"."NATIONAL" IS '국가';
REM INSERTING into KH.NATIONAL
SET DEFINE OFF;
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('KO','한국');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('JP','일본');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('CH','중국');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('US','미국');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('RU','러시아');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK4
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK4" ON "KH"."NATIONAL" ("NATIONAL_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table NATIONAL
--------------------------------------------------------
ALTER TABLE "KH"."NATIONAL" ADD CONSTRAINT "NATIONAL_PK" PRIMARY KEY ("NATIONAL_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "KH"."NATIONAL" MODIFY ("NATIONAL_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table SAL_GRADE
--------------------------------------------------------
CREATE TABLE "KH"."SAL_GRADE"
( "SAL_LEVEL" CHAR(2 BYTE),
"MIN_SAL" NUMBER,
"MAX_SAL" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "KH"."SAL_GRADE"."SAL_LEVEL" IS '급여등급';
COMMENT ON COLUMN "KH"."SAL_GRADE"."MIN_SAL" IS '최소급여';
COMMENT ON COLUMN "KH"."SAL_GRADE"."MAX_SAL" IS '최대급여';
COMMENT ON TABLE "KH"."SAL_GRADE" IS '급여등급';
REM INSERTING into KH.SAL_GRADE
SET DEFINE OFF;
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S1',6000000,10000000);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S2',5000000,5999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S3',4000000,4999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S4',3000000,3999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S5',2000000,2999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S6',1000000,1999999);
--------------------------------------------------------
-- DDL for Index 엔터티2_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티2_PK" ON "KH"."SAL_GRADE" ("SAL_LEVEL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table SAL_GRADE
--------------------------------------------------------
ALTER TABLE "KH"."SAL_GRADE" ADD CONSTRAINT "엔터티2_PK" PRIMARY KEY ("SAL_LEVEL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "KH"."SAL_GRADE" MODIFY ("SAL_LEVEL" NOT NULL ENABLE);
COMMIT;
result set 보기
컬럼명 선택해서 result set 보기
column alias
컬럼 헤더에 보일 별명 붙이기 (메타데이터 이므로 쌍따옴표) (띄어쓰기가 없다면 쌍따옴표 없이 써도 된다)
where절
사용할수도 안할수도 있음
조건에 따라 테이블 한줄씩 비교 검사
(=) 같다는 의미 / 자바의 대입역할을 하는 문법은 따로 있음
문자는 따옴표 안에
select
emp_id , emp_name , salary
from
employee
where emp_id=204;select
emp_id , emp_name , salary
from
employee
where emp_name='송은희';예제
-- 1. JOB 테이블 내에서 JOB_NAME 정보만 출력해보세요.
select JOB_NAME from JOB;
-- 2. DEPARTMENT 테이블의 모든 내용을 출력해보세요.
select * from DEPARTMENT;
-- 3. EMPLOYEE 테이블에서 사원명, 이메일, 전화번호, 고용일만 전원 출력해보세요.
select emp_name, email, phone, hire_date from employee;
where절에 조건에 여러개일 경우
and , or , 부등식 사용가능
select
emp_id , emp_name , salary
from
employee
where emp_name='송은희' and emp_id=204;
select
emp_id , emp_name , salary
from
employee
where emp_name='송은희' or emp_id=204;
select
emp_id , emp_name , salary
from
employee
where emp_name='송은희' or emp_id=204 or salary<2000000;
-- 4. employee 테이블에서 급여가 250만원 이상인 사람의 사원명과 sal_level을 출력해 보세요
select emp_name, sal_level from employee where salary>2500000;
-- 5. employee 테이블에서 급여가 200만원 이상 350만원 이하이고,
-- 직급코드가 J3인 직원들의 사원명과 직급코드, 전화번호를 출력해보세요
select
emp_name, JOB_CODE, phone
from
employee
where
3500000>salary and salary>2000000 and JOB_CODE = 'J3';연산 가능
select
emp_name 사원명, salary * 12 연봉
from
employee;
인위적으로 컬럼을 만들어서 문자값 반복
result set에서 보여지는 출력부를 변경한다고 실제 데이터에 저장되는 것은 아님
select
emp_name 사원명, salary * 12 연봉 , '원' 단위
from
employee;
컬럼을 새로 만드는게 아니라 뒤에 연결하기 ||
select
emp_name 사원명, salary * 12||'원' 연봉
from
employee;
예제 - 이번 달 보너스
select
emp_id 사번, emp_name 사원명, salary * bonus ||'원' "이번 달 보너스"
from
employee;
예제 - 1년 보너스
select
emp_id 사번, emp_name 사원명, salary * bonus*12 ||'원' "올해 보너스"
from
employee;
예제 - 총급여
select
emp_id 사번, emp_name 사원명,
salary * bonus ||'원' "이번 달 보너스",
salary * bonus*12 ||'원' "1년간 보너스",
salary * 12 + salary*12||'원' "1년 간 급여총액"
from
employee;
날짜에 정수 연산 가능
덧셈과 뺄셈 가능 (곱셈, 나눗셈 불가)
정수값을 더하거나 빼면 일수로 계산
select emp_name, sysdate - 3 from employee;
날짜 간의 연산은 빼기만 가능
더 큰값(현재) - 더 작은 값(과거) : 날짜 사이의 일수
select emp_name, sysdate-hire_date from employee;
소수점 버리기 floor method
select emp_name, floor(sysdate - hire_date) from employee;
날짜 더하기는 안된다.
select emp_name 이름, floor(sysdate + hire_date)||'일' 근무일수 from employee;ORA-00975: date + date not allowed
00975. 00000 - "date + date not allowed"
*Cause:
*Action:
55행, 39열에서 오류 발생예제 - employee 테이블 내에서 이름, 근무일수를 ??일 형태로 출력
-- employee 테이블 내에서 이름, 근무일수를 ??일 형태로 출력
select emp_name 이름, floor(sysdate - hire_date)||'일' 근무일수 from employee;
예제
-- 2. employee 테이블 내에서 20년이상 근무한 직원의 이름, 급여, 보너스율을 출력
select
emp_name 이름, salary 급여, bonus 보너스율, floor((sysdate - hire_date)/365 )||'년' 근속연수
from
employee
where
((sysdate - hire_date)/365 )>= 20 ;
날짜 표기
select emp_name, hire_date from employee
where '05/01/01' <= hire_date and hire_date <= '10/12/31';
--between 을 사용한 문법 : 결과는 같다. inclusive한 표현(<=, >=)
select emp_name, hire_date from employee
where hire_date between '05/01/01' and '10/12/31';null 값을 찾아라 is null , is not null
select emp_name, bonus from employee
where bonus is null;
select emp_name, bonus from employee
where bonus is not null;
or 연산자가 여러개 쓰이는 경우 표현
-- 부서코드가 D2 이거나 D5인 사원들의 이름만 출력하세요.
select emp_name, dept_code from employee
where dept_code = 'D2' or dept_code = 'D5';
-- 부서코드가 D7 이거나 D9인 사원들의 이름을 추가로 출력하세요.
select emp_name, dept_code from employee
where dept_code = 'D2' or dept_code = 'D5' or dept_code = 'D7' or dept_code = 'D9';in 같은 기능이지만 더 깔끔하게 표현
select emp_name, dept_code from employee
where dept_code in ( 'D2' , 'D5' );
-- 부서코드가 D7 이거나 D9인 사원들의 이름을 추가로 출력하세요.
select emp_name, dept_code from employee
where dept_code in ( 'D2' , 'D5' , 'D7' , 'D9' );not in 부정
-- 부서코드가 D2 이거나 D5인 사원들의 이름만 빼고 출력하세요.
select emp_name, dept_code from employee
where dept_code not in ( 'D2' , 'D5' );중복 제거
-- 중복제거
select distinct dept_code from employee;
-- distinct 에러
select emp_name, distinct dept_code from employee;
--ORA-00936: missing expression
--00936. 00000 - "missing expression"
--*Cause:
--*Action:
--80행, 18열에서 오류 발생-- distinct는 맨 앞으로 와야 함
select distinct dept_code, emp_name from employee;
-- 이름이 중복이 아니면 지역코드 중복제거 포기
검색어를 특정 할 수 없을 때
-- 검색어를 특정 할 수 없을 때
-- % 어떤글자가 있을 수도 있고, 없을 수도 있고, 여러개일 수도 있는 모든 경우
select emp_id, emp_name, dept_code from employee
where emp_name like '하%유';
-- %와 같이 불특정한 기호를 쓰고 싶다면 (=)이 아니라 like로
-- (_) 한 글자가 있다.
select emp_id, emp_name, dept_code from employee
where emp_name like '하___유';
-- 이름에 '전'자가 들어가는 경우
select emp_id, emp_name, dept_code from employee
where emp_name like '%전%';-- 이름이 '연'으로 끝나는 경우
select emp_id, emp_name, dept_code from employee
where emp_name like '%연';(%) (_)를 쓰기 곤란한 경우
ESCAPE
-- employee 이메일 값의 _ 앞자리가 3자리인 직원만 검색
select email from employee
where email like '___#_%' ESCAPE '#';
-- #을 이스케이프 문자로 변경
-- # 뒤의 _는 '어떤 한 문자'라는 기능에서 그냥 (_)문자로 변환
ESCAPE '문자' : 특정 문자를 이스케이프 문자로 만듦
이스케이프 문자 뒤의 문자는 본래의 기능을 잃어버임
자바의 이스케이프 문자는 역슬래쉬\
System.out.print("\"Apple\"")
// "Apple"
System.out.print("\\")
// \-- employee 이메일 값의 _ 앞자리가 3자리인 직원만 빼고 검색
select email from employee
where email not like '___#_%' ESCAPE '#';
퀴즈 성씨가 '이'씨가 아닌 모든 직원 출력
-- Quiz 성씨가 '이'씨가 아닌 모든 직원 출력
select emp_name from employee
where emp_name not like '이%';예제
-- 1. employee 테이블에서 이름 끝이 연으로 끝나는 사원의 이름을 출력하세요.
select emp_name from employee
where emp_name like '%연';
-- 2. employee 테이블에서 전화번호 처음 3자리가 010이 아닌
-- 사원의 이름, 전화번호를 출력하세요.
select emp_name, phone from employee
where phone not like '010%';
-- 3. employee 테이블에서 메일주소의 's'가 들어가면서,
-- DEPT_CODE가 D9또는 D6이고,
-- 고용일이 90/01/01 ~ 00/ 12/01 이면서,
-- 월급이 270만원 이상인 사원의 전체 정보를 출력하시오
select * from employee
where email like '%s%' and
DEPT_CODE in ('D9' , 'D6') and
hire_date between '90/01/01' and '00/12/01' and
salary > 2700000;
-- 4. 직속 상사도 없고 부서코드도 없는 직원의 사번, 이름, 부서코드, 상사ID를 출력하세요.
select emp_id 사번, emp_name 이름 , dept_code 부서코드 , manager_id 상사ID from employee
where manager_id is null and dept_code is null;
-- 5. 부서가 없음에도 보너스가 지급되는 직원의 사번 및 이름과 보너스를 출력하세요.
select emp_id 사번, emp_name 이름 , bonus 보너스 from employee
where dept_code is null and bonus is not null;
-- 6. 부서원 중 직급코드가 J7 또는 J2이고,
-- 급여가 2,000,000원 초과인 사람의 이름 급여,직급코드를 출력
select emp_name, salary, job_code from employee
where job_code in ('J7','J2') and salary>2000000;연산 우선순위
and연산이 or연산보다 빠르다
-- 연산 우선순위
select emp_name, salary, job_code from employee
where job_code='J7' or job_code='J2' and salary>2000000;
-- and가 우선이다.
select emp_name, salary, job_code from employee
where (job_code='J7' or job_code='J2') and salary>2000000;
-- 연산우선순위를 외우기 보다는 필요하다면 괄호를 쓰자정렬
-- 정렬, 뒤에 아무것도 안쓰면 asc로 동작 (오름차순)
select emp_id, emp_name, salary from employee
order by salary asc;
-- 내림차순
select emp_id, emp_name, salary from employee
order by salary desc;-- 컬럼 숫자로 정렬 가능
select emp_id, emp_name, salary from employee
order by 1 desc;
-- 1번컬럼 emp_id 기준
select emp_id, emp_name, salary from employee
order by 2 desc;
-- 2번컬럼 emp_name 기준-- 정렬 기준 2개
select dept_code, salary
from
employee
order by 1 , 2;
-- dept_code로 정렬한 후 salary로 정렬-- null을 위로
select dept_code, salary
from
employee
order by 1 nulls first, 2;
-- 문제1.
-- 입사일이 5년이상, 10년 이하인 직원의
-- 이름, 주민번호, 급여, 입사일을 검색하세요.
select emp_name 이름, emp_no 주민번호, salary 급여, hire_date 입사일
from employee
where (sysdate-hire_date)/365 between 5 and 10 ;
select emp_name 이름, emp_no 주민번호, salary 급여, hire_date 입사일
from employee
where (sysdate-hire_date)/365 >= 5 and (sysdate-hire_date)/365 <=10 ;
-- 문제2.
-- 퇴사한 직원의 이름, 부서코드, 고용일, 근무기간, 퇴사일 검색하세요. (퇴사 여부 : ENT_YN)
select emp_name 이름, dept_code 부서코드, hire_date 고용일, ent_date-hire_date 근무기간, ent_date 퇴사일
from employee
where ent_yn = 'Y' ;