728x90
sample_table(Mysql) DB.zip예제 압축 파일은 공개되어 있습니다만 DB 파일은 사전에 확인된 분에게만 암호를 알려드리고 있습니다.
이용에 불편을 드려 죄송합니다.
select * from dept; -- 주석
select * from tab;
-- DML : insert, update, delete, select
-- DCL : COMMIT, ROLLBACK, GRANT, REVOKE ...
-- DDL : create, alter, drop ...
-- 테이블 생성
-- 형식) create table 테이블명(칼럼명 자료형, ... 제약조건)
-- 자료형 : varchar2, char, number, date, timestamp, clob, blob...
create table test(no number, name varchar2(10));
desc test;
drop table test;
select * from tab;
create table test(no number primary key,
name varchar2(10) not null, tel varchar2(15),
inwon number(3), addr varchar2(30));
desc test;
-- 자료 추가
-- insert into 테이블명(칼럼명,...) values(입력자료,...)
insert into test(no,name,tel,inwon,addr)
values(1, '인사과','111-1111',5,'강남구 역삼동');
insert into test values(2,'영업과','222-2222',7,'강남구 압구정동');
insert into test(no, name) values(3,'자재과');
insert into test(name, no, tel) values('자재2과', 4,'678-7788');
select * from test;
insert into test(no, name) values(3,'자재3과'); -- 에러: no 중복
insert into test(no, addr) values(5,'서초2동'); -- 에러: name not null
insert into test(no, name) values('오','자재3과'); --에러: no 숫자만 가능
insert into test(no, name) values(5,'박차고 나온놈이 성질을 부르는 삼월생 아이'); --에러:너무 큰 자료
-- 자료 수정
-- update 테이블명 set 칼럼명=수정값,... where 조건
update test set inwon=12 where no=1;
update test set tel='777-7777',inwon=22 where no=3;
update test set inwon=null where no=3;
update test set inwon='' where no=3;
select * from test;
update test set no=null where no=3; --err : no not null
update test set inwon=12345 where no=3; --err : inwon 999 까지 가능
select * from test;
--자료 삭제
-- delete from 테이블명 where 조건
delete from test where no=2;
select * from test;
-- truncate table 테이블명 <== 모든 행 삭제 (취소 불가)
commit;
-- 무결성(constraint) 제약 조건 : 잘못된 자료의 입력을 막고자 제약조건 부여.
-- domain 제약조건 : 구조 작성시 칼럼의 이름, 성격, 크기, null 허용
-- 기본키(primary key) 제약조건 : 특정칼럼의 중복 불허, not null
-- 사용자 정의 제약조건 : check, unique, foreign key, ...
-- 기본키(PK) 제약조건 :
-- 방법1) 칼럼 레벨 : 칼럼 선언과 함께 PK를 부여
create table aa(bun number primary key, irum char(10));
drop table aa;
select constraint_type,constraint_name from user_constraints
where table_name = 'AA';
-- 방법2) 제약조건 이름 부여
create table aa(bun number constraint aa_bun primary key, irum char(10));
drop table aa;
-- 방법3) 테이블 레벨
create table aa(bun number, irum char(10),
constraint aa_bun primary key(bun));
drop table aa;
-- 제약조건
-- 추가 : alter table 테이블명 add constraint 제약조건명 제약조건
-- 제거 : alter table 테이블명 drop constraint 제약조건명
-- check 제약조건 : 입력되는 자료의 칼럼값 검사
create table aa(bun number, irum char(10),
nai number(2) check(nai >= 20));
insert into aa values(1,'tom',25);
insert into aa values(2,'tom2',15);
drop table aa;
-- unique : 특정 칼럼의 대해 동일한 값 입력 불허
create table aa(bun number, irum char(10) unique);
insert into aa values(1,'tom');
insert into aa values(2,'tom');
drop table aa;
-- 외부키(참조키, Foreign key, FK) - 다른 테이블의 칼럼값을 참조
-- on delete cascade : 부모 테이블의 행이 삭제되면 자식도 함께 삭제
create table jikwon(bun number primary key, irum varchar2(10),
buser char(10));
insert into jikwon values(1,'한송이','인사과');
insert into jikwon values(2,'박치기','영업과');
insert into jikwon values(3,'한가해','총무과');
insert into jikwon values(4,'한송이','총무과');
select * from jikwon;
create table gajok(code number primary key, name varchar2(10),
birth date, jikwon_bun number references jikwon(bun));
insert into gajok values(100, '공기밥', '2000-01-12', 1);
insert into gajok values(101, '김밥', sysdate, 2);
select * from gajok;
drop table jikwon;
delete from jikwon where bun = 1; -- X
delete from jikwon where bun = 4; -- O
select * from jikwon;
drop table gajok;
drop table jikwon;
-- default : 특정 칼럼에 초기값 부여
drop table aa;
create table aa(bun number, irum varchar2(10),
juso varchar2(20) default '강남구 역삼동');
insert into aa values(1, '강나루', '강남구 신사동');
select * from aa;
insert into aa(bun, irum) values(2, '박치기');
drop table aa;
-- sequence : type이 number인 칼럼에 대해 숫자를 자동 증가 - 대개는 pk 칼럼이 대상
create table aa(bun int primary key, irum varchar2(10));
create sequence my_seq increment by 1 start with 0
minvalue 0 maxvalue 1000;
insert into aa values(my_seq.nextval,'이기자');
insert into aa values(my_seq.nextval,'이겨라');
select * from aa;
create table bb(code int primary key, name varchar2(10));
insert into bb values(my_seq.nextval,'김밥');
select * from bb;
insert into aa values(my_seq.nextval,'신기해');
select * from aa;
create sequence our_seq;
insert into aa values(our_seq.nextval,'손오공');
drop sequence my_seq;
drop sequence our_seq;
drop table aa;
drop table bb;
-- index(색인) : 검색속도를 증진시키기 위해 칼럼에 색인을 부여
-- pk 칼럼은 자동으로 index가 만들어 진다.
-- 나머지 칼럼은 우리가 작성
-- 인덱스를 사용하지 말야야 할 경우
-- 입력, 수정, 삭제가 빈번한 테이블
create table aa(bun int primary key, irum varchar2(10));
create index ind_irum on aa(irum); --비고유 인덱스
--create unique index ind_irum2 on aa(irum); --고유인덱스
select index_name, table_name from USER_INDEXES;
select index_name, table_name from USER_INDEXES
where TABLE_NAME='AA';
alter index ind_irum rebuild; -- 수정, 삭제, 추가 후 인덱스 재생성
drop index ind_irum;
drop table aa;
-- 테이블 관련 명령
--create table 테이블명 ~
--alter table 테이블명 ~
--drop table 테이블명 ~
--rename 옛이름 to 새이름
create table aa(bun int primary key, irum varchar2(10),
inwon number(3));
insert into aa values(1, 'tom', 12);
select * from aa;
rename aa to kbs;
select * from kbs;
-- 칼럼 관련 명령
-- 칼럼 추가
alter table kbs add(juso varchar2(10));
desc kbs;
select * from kbs;
-- 칼럼 수정(크기)
alter table kbs modify(inwon number(2)); --X
alter table kbs modify(inwon number(5)); --O
-- 칼럼 수정(이름)
alter table kbs rename column inwon to inwonsu;
select * from kbs;
-- 칼럼 삭제
alter table kbs drop (juso);
alter table kbs drop (inwonsu);
select * from kbs;
-- 데이터 이동 (export, import) - command 창에서 실습
select * from kbs;
select * from tab;
create table bb(bun int primary key, irum varchar2(10));
insert into bb values(10,'james');
insert into bb values(20,'oscar');
select * from kbs;
select * from bb;
-- kbs, bb 테이블을 export 후 import 연습
--C:\>exp scott/tiger tables=kbs,bb file=c:\work\scott.dmp
drop table kbs;
drop table bb;
--C:\>imp scott/tiger tables=kbs,bb file=c:\work\scott.dmp
-- 주의 : import 시 테이블이 있으면 안됨
select * from kbs;
select * from bb;
-- 테이블 삭제 관련 : 오라클 휴지통
select * from tab;
drop table kbs; -- 완전삭제가 아니고 휴지통에 저장됨
select * from kbs;
show recyclebin; -- 오라클 휴지통 목록 확인
flashback table kbs to before drop;
select * from kbs;
purge recyclebin; -- 오라클 휴지통 완전삭제
show recyclebin;
drop table kbs purge; -- 테이블 완전삭제
select * from EMP;
create table buser(
buser_no number(4) primary key,
buser_name varchar2(10) not null,
buser_loc varchar2(10),
buser_tel varchar2(15));
insert into buser values(10,'총무부','서울','02-100-1111');
insert into buser values(20,'영업부','서울','02-100-2222');
insert into buser values(30,'전산부','서울','02-100-3333');
insert into buser values(40,'관리부','인천','032-200-4444');
select * from buser;
create table sawon(
sawon_no number(4) primary key,
sawon_name varchar2(10) not null,
buser_num number(4) not null,
sawon_jik varchar2(10) default '사원',
sawon_pay number,
sawon_ibsail date default sysdate,
sawon_gen char(2));
insert into sawon values(1,'홍길동',10,'사장',9900,'1999/09/01','남');
insert into sawon values(2,'한국남',20,'부장',8200,'2003/12/03','남');
insert into sawon values(3,'이순신',20,'과장',6500,'2011/03/03','남');
insert into sawon values(4,'이미라',30,'대리',5500,'2010/11/04','여');
insert into sawon values(5,'이순라',20,'사원',3000,'2013/08/05','여');
insert into sawon values(6,'김이화',20,'사원',2950,'2014/08/05','여');
insert into sawon values(7,'김부만',40,'부장',8000,'2004/01/05','남');
insert into sawon values(8,'김기만',20,'과장',7000,'2009/01/01','남');
insert into sawon values(9,'채송화',30,'대리',5500,'2011/03/02','여');
insert into sawon values(10,'박치기',10,'사원',3700,'2014/03/02','남');
insert into sawon values(11,'김부해',30,'사원',2900,'2015/09/06','남');
insert into sawon values(12,'박별나',40,'과장',5300,'2010/03/05','여');
insert into sawon values(13,'박명화',10,'대리',4900,'2012/05/01','남');
insert into sawon values(14,'박궁화',40,'사원',2800,'2016/01/05','여');
insert into sawon values(15,'채미리',20,'사원',3200,'2015/01/03','여');
insert into sawon values(16,'이유가',10,'사원',3900,'2013/02/01','여');
select * from sawon;
create table gogek(
gogek_no number(4) primary key,
gogek_name varchar2(10) not null,
gogek_tel varchar2(20),
gogek_jumin char(14),
gogek_damsano number(4),
CONSTRAINT FK_gogek_damsano foreign key(gogek_damsano) references sawon(sawon_no)
);
create sequence gogek_gogek_no_seq increment by 1
minvalue 0 start with 0;
insert into gogek values(gogek_gogek_no_seq.nextval,'이나라','02-535-2580','880612-1156789',1);
insert into gogek values(gogek_gogek_no_seq.nextval,'김혜순','02-375-6946','750101-1054225',3);
insert into gogek values(gogek_gogek_no_seq.nextval,'최부자','02-692-8926','890305-1065773',3);
insert into gogek values(gogek_gogek_no_seq.nextval,'김해자','032-393-6277','870412-2028677',1);
insert into gogek values(gogek_gogek_no_seq.nextval,'차일호','02-294-2946','790509-1062677',2);
insert into gogek values(gogek_gogek_no_seq.nextval,'박상운','02-631-1204','790623-1023566',6);
insert into gogek values(gogek_gogek_no_seq.nextval,'이분','02-546-2372','880323-2558021',2);
insert into gogek values(gogek_gogek_no_seq.nextval,'신영래','031-948-0283','790908-1063765',5);
insert into gogek values(gogek_gogek_no_seq.nextval,'장도리','02-496-1204','870206-2063245',4);
insert into gogek values(gogek_gogek_no_seq.nextval,'강나루','032-341-2867','780301-1070425',12);
insert into gogek values(gogek_gogek_no_seq.nextval,'이영희','02-195-1764','880103-2070245',3);
insert into gogek values(gogek_gogek_no_seq.nextval,'이소리','02-296-1066','910609-2046266',9);
insert into gogek values(gogek_gogek_no_seq.nextval,'배용중','02-691-7692','820920-1052677',1);
insert into gogek values(gogek_gogek_no_seq.nextval,'김현주','031-167-1884','880128-2062665',2);
insert into gogek values(gogek_gogek_no_seq.nextval,'송사리','02-887-9344','830301-2013345',2);
select * from gogek;
create table sangdata(code number primary key,
sang varchar2(20) not null,
su number, dan number);
insert into sangdata values(1,'마우스',5,5000);
insert into sangdata values(2,'물티슈',12,2000);
insert into sangdata values(3,'볼펜',7,1000);
commit;
select * from buser;
select * from sawon;
alter session set nls_date_format='YYYY-MM-DD';
--select
-- select [distinct] db명.소유자명.테이블명.칼럼명 [as 별명]...
-- [into 테이블명] from 테이블명...
-- where 조건... order by 기준키 [asc / desc]
select * from sawon;
select sawon_no,sawon_name,sawon_pay from sawon;
select sawon_pay,sawon_no,sawon_name from sawon;
select sawon_no as 사번,sawon_name as 직원명,sawon_pay 연봉 from sawon;
select sawon_no || sawon_name as 직원자료 from sawon;
select 10,'안녕',sawon_name from sawon;
select sawon_no,sawon_name || '님' as name from sawon;
select 'kbs', 10 / 3, 10 * 2 from dual; --dummy table
select sawon_name as 이름, sawon_pay * 0.02 as 세금 from sawon;
select sawon_no, sawon.sawon_name from sawon;
-- 치환 변수
select * from &table_name;
select &column_name from sawon;
-- 정렬 : order by 칼럼명 [asc | desc]
select * from sawon order by sawon_no desc;
select * from sawon order by sawon_jik asc, sawon_pay desc;
select * from sawon order by sawon_jik, sawon_pay desc;
select * from sawon order by sawon_jik, sawon_gen, sawon_pay;
select sawon_name,sawon_pay, sawon_pay * 0.05 as tex
from sawon order by tex desc;
select sawon_name,sawon_jik,buser_num from sawon
order by 2 desc, 3 asc; -- position number 사용
select distinct sawon_jik from sawon; --중복자료 배제
select distinct sawon_jik, sawon_name from sawon; --X
select distinct buser_num from sawon order by 1;
-- 레코드(행) 제한 : where 조건
select * from sawon where sawon_jik='대리';
select * from sawon where sawon_no=3;
select * from sawon where sawon_ibsail='2011-03-03';
select * from sawon where sawon_no=3 or sawon_no >= (3 + 4);
select * from sawon where sawon_jik='사원' or sawon_jik='대리';
select * from sawon where sawon_jik='사원'
and sawon_gen='남' and sawon_ibsail <= '2015-1-1';
select * from sawon where sawon_no < 5 or sawon_no > 10;
select * from sawon where not(sawon_no < 5 or sawon_no > 10);
select * from sawon where sawon_no >= 5 and sawon_no <= 10;
select * from sawon where sawon_no between 5 and 10;
select * from sawon where sawon_ibsail between '2010-1-1' and '2012-12-31';
select * from sawon where sawon_name='이미라';
select * from sawon where sawon_name >= '이';
select ascii('a'),ascii('A'),ascii('가'),ascii('나') from dual;
select * from sawon where sawon_name >= '이' and sawon_name <= '최';
select * from sawon where sawon_name between '이' and '최';
select * from sawon where sawon_jik='대리'
or sawon_jik='과장' or sawon_jik='부장';
select * from sawon where sawon_jik in('대리','과장','부장');
select * from sawon where sawon_jik in('대리','과장','부장')
order by sawon_jik;
select * from sawon where buser_num in(10, 30);
select * from buser;
select * from buser where buser_name in('총무부','영업부');
select * from gogek where GOGEK_DAMSANO in(1,2,5);
select gogek_no,gogek_name,GOGEK_DAMSANO from gogek
where GOGEK_DAMSANO in(1,2,5) order by GOGEK_DAMSANO desc;
-- like 조건에는 %(0개 이상의 문자열), _(하나의 문자)
select * from sawon where sawon_name like '이%';
select * from sawon where sawon_name like '%라';
select * from sawon where sawon_name like '%순%';
select * from sawon where sawon_name like '이%라';
select * from sawon where sawon_name like '이_라';
select * from sawon where sawon_name like '__라';
select * from gogek where gogek_name like '__';
select * from sawon where sawon_name like '이순%' or
sawon_name like '이미_';
select * from sawon where sawon_pay like '5%';
select * from gogek where GOGEK_JUMIN like '%-1%';
select * from gogek where GOGEK_JUMIN like '_______1%';
-- null 인 자료
update sawon set sawon_jik=null where sawon_no=5;
commit;
select * from sawon;
select * from sawon where sawon_jik=null; -- X
select * from sawon where sawon_jik is null;
select * from sawon where sawon_jik is not null;
select sawon_no as 사번,sawon_name as 직원명,sawon_jik as 직급,
sawon_pay 연봉,sawon_pay / 12 as 보너스, sawon_ibsail 입사일
from sawon
where sawon_jik in('사원','대리','과장','부장') and
sawon_pay >= 1500 and
sawon_ibsail between '2000-1-1' and '2017-12-31' and
sawon_pay like '5%'
order by sawon_jik, sawon_pay desc;
-- 연산자 우선순위
-- () > 산술(*,/ > +,-) > 비교(관계) > is null, like, in >
-- between > not > > and > or
-- function(함수) - 내장함수 연습
-- 문자함수
select lower('Hello'),upper('Hello') from dual;
select initcap('hello world') from dual;
select concat('Hello','world') from dual; -- ||
select substr('Hello world',3) from dual;
select substr('Hello world',3, 3) from dual;
select length('Hello world') from dual;
select instr('Hello world','e') from dual;
select instr('Hello world','o',6) from dual;
select instr('Hello world','o',1,2) from dual;
select lpad('Hello',10,'*') from dual;
select replace('011.111.2222','.','-') from dual;
-- 문제 : sawon 테이블에서 이름에 '이' 가 포함된 직원이 있으면
-- '이'부터 2글자 출력
select sawon_name, substr(sawon_name,instr(sawon_name,'이'),2)
from sawon
where sawon_name like '%이%';
-- 숫자 함수
select round(45.678,0),round(45.678,1),round(45.678,-1) from dual;
select sawon_name,sawon_pay,
round(sawon_pay * 0.025,0) as tex from sawon;
select trunc(45.678),trunc(45.678,2) from dual; --절삭
select mod(15, 2) from dual; --나머지
-- 날짜 함수
select sysdate from dual; --현재 DB server의 날짜 및 시간. now()
select sysdate + 5, sysdate - 5, sysdate + 500 from dual;
select sawon_name, trunc((sysdate - sawon_ibsail) / 7) as week
from sawon;
-- 혹시 '금'이 인식되지 않으면 alter session set nls_language=korean
select sysdate, last_day(sysdate), next_day(sysdate,'금') from dual;
select months_between(sysdate,'17-5-5') from dual;
select months_between('19-6-12','17-5-5') from dual;
select add_months(sysdate,3),add_months(sysdate,-3) from dual;
-- 형변환 함수
select sawon_pay * 0.5,sawon_pay * '0.5' from sawon; --자동 형변환
-- 강제 형변환
-- 문자열을 날짜로 : to_date()
select trunc(sysdate - to_date('2010-1-1')) from dual;
-- 날짜나 숫자를 문자로 : to_char(숫자[날짜], '서식')
select to_char(sysdate + 3,'YYYY-MM-DD HH:MI:SS') from dual;
select to_char(sysdate + 3,'YEAR') from dual;
select to_char(sysdate + 3,'MON') from dual;
select to_char(sysdate + 3,'WW') from dual; -- 년 중 몇주차
select to_char(sysdate + 3,'W') from dual; --월 중 주차
select to_char(1234,'9,990.9') from dual;
select to_char(12,'9,990.9') from dual;
select to_char(12.567,'9,990.9') from dual;
select to_char(12.567,'0,000.9') from dual;
-- 기타 함수
-- nvl(value1, value2) : value1이 null이면 value2를 취함
select sawon_name, nvl(sawon_jik,'임시직') from sawon;
-- nvl2(value1, value2, value3) : value1이 null 인지 평가
-- null이면 value3을 null이 아니면 vbalue2를 취함
select sawon_name,nvl2(sawon_jik,'정규직','임시직') from sawon;
select sawon_name,nvl2(sawon_pay,sawon_pay,sawon_pay - 1000)
from sawon;
--nullif(value1, value2) : 두 개의 값이 일치하면 null을, 아니면 value1을 취함
select nullif(length('abc'), length('efgh')) from dual;
select nullif(length('abc'), length('efg')) from dual;
select sawon_name, sawon_jik, nullif(sawon_jik,'대리') from sawon;
--순위 지정 함수 : rank() - order by를 병행
select sawon_no,sawon_name,sawon_pay,
rank()over(order by sawon_pay desc) as rank
from sawon;
select sawon_no,sawon_name,sawon_pay,
dense_rank()over(order by sawon_pay desc) as rank
from sawon;
-- 조건 표현식
-- 형식1
-- case 표현식 when 비교값1 then 결과값1 ... else 결과값n end
select case 10 / 2
when 5 then '안녕'
when 2 then '수고'
else '잘가' end as result from dual;
select sawon_name,
case sawon_pay
when 3000 then '연봉 3000'
when 4000 then '연봉 4000'
else '기타 연봉' end as result from sawon;
select sawon_name,sawon_jik,
case sawon_jik
when '부장' then sawon_pay * 0.05
when '과장' then sawon_pay * 0.04
else sawon_pay * 0.03 end as donation from sawon;
-- 형식2
-- case when 조건1 then 결과값1 ... else 결과값n end
select sawon_name,
case
when sawon_gen='남' then 'M'
when sawon_gen='여' then 'F'
end as result
from sawon where sawon_jik='사원';
select sawon_name,
case
when sawon_pay >= 5000 then '고액연봉'
when sawon_pay >= 3000 then '보통연봉'
else '부족연봉'
end as res from sawon;
-- 문제1) 10년 이상 근무하면 감사합니다, 5년 이상 근무하면 '고마워요' 그 외는 '열심히' 라고 표현
-- (1990년 이후 직원만 참여), 특별수당(pay기준) 10년 이상 10%, 5년 이상 5%, 나머지 3%
-- 수당은 정수만 출력(반올림)
-- 출력 형태 사번 직원명 표현 특별수당
-- 1 홍길동 감사합니다 150
select sawon_no, sawon_name,
case
when trunc((sysdate - sawon_ibsail) / 365) >= 10 then '감사합니다'
when trunc((sysdate - sawon_ibsail) / 365) >= 5 then '고마워요'
else '열심히' end as 표현,
case
when trunc((sysdate - sawon_ibsail) / 365) >= 10 then round(sawon_pay * 0.1)
when trunc((sysdate - sawon_ibsail) / 365) >= 5 then round(sawon_pay * 0.05)
else round(sawon_pay * 0.03) end as 특별수당
from sawon where sawon_ibsail >= '1990-1-1';
-- 문제2) 입사 후 10년 이상이면 왕고참, 5년 이상이면 고참, 3년 이상이면 보통, 나머지는 일반으로 표현
-- 출력 형태 직원명 직급 입사년월일 근무개월수 구분 부서
-- 홍길동 부장 2000.1.15 5678 왕고참 총무
-- 부서는 buser 테이블을 참조
select * from sawon;
select sawon_name, sawon_jik,
to_char(sawon_ibsail, 'YYYY.MM.DD') as 입사년월일,
trunc(months_between(sysdate, sawon_ibsail)) as 근무개월수,
case
when (sysdate - sawon_ibsail) / 365 >= 10 then '왕고참'
when (sysdate - sawon_ibsail) / 365 >= 5 then '고참'
when (sysdate - sawon_ibsail) / 365 >= 3 then '보통'
else '일반' end as 구분,
case buser_num
when 10 then '총무'
when 20 then '영업'
when 30 then '전산'
when 40 then '관리'
end as buser
from sawon;
-- decode(칼럼, 비교값, 반환값,비교값, 반환값,...반환값)
select trunc(sawon_pay / 1000) from sawon;
select sawon_name, sawon_pay,
decode(trunc(sawon_pay / 1000),9,'A',8,'B',7,'C',6,'D','F') grade
from sawon;
select sawon_name,decode(sawon_gen,'남','M','여','F','') gender
from sawon;
select sawon_name, decode(buser_num,10,'총무부',20,'영업부',
30,'전산부','관리부') as buser from sawon
where sawon_jik='사원' order by buser_num;
--문3) 각 부서별로 실적에 따라 급여를 다르게 인상하려한다.
-- 10번 부서는 10%, 20번 부서는 20%, 나머지 부서는 동결할
-- 경우의 연봉을 decode 함수를 사용하여 출력하라.
-- 장기근속은 10년 이상이면 O, 아니면 X로 표시
-- 형태 : 사번 직원명 부서 연봉 인상연봉 장기근속
-- 1 홍길동 10 *** *** O
select sawon_no 사번,sawon_name as 직원명,buser_num 부서,
to_char(sawon_pay,'999,999') as 연봉,
decode(buser_num, 10, round(sawon_pay * 1.1,0),
30, round(sawon_pay * 1.2,0),sawon_pay) 인상연봉,
case when round((sysdate - sawon_ibsail) / 365, 0) >= 10 then 'O'
else 'x' end as 장기근속
from sawon;
select sawon_name, round(sawon_pay) from sawon;
-- 복수 행 함수(aggregation functions)
-- 전체 자료를 그룹별로 처리
-- 해당칼럼의 값이 null인 경우는 연산에서 제외
-- count(), sum(), avg(), std(), variance()...
select sum(sawon_pay) as sum, avg(sawon_pay) as avg from sawon;
select max(sawon_pay) as max, min(sawon_pay) as min from sawon;
update sawon set sawon_pay=null where sawon_no=3;
commit;
select * from sawon;
select avg(sawon_pay), avg(nvl(sawon_pay, 0)) from sawon;
select avg(sawon_pay) from sawon where sawon_pay is not null;
select sum(sawon_pay) / 15,sum(sawon_pay) / 16 from sawon;
select count(sawon_no), count(sawon_name),
count(sawon_jik),count(sawon_pay), count(*) from sawon;
-- 과장은 몇명?
select count(*) as 인원 from sawon where sawon_jik='과장';
-- 2000년 이전에 입사한 남직원은 몇명?
select count(*) from sawon
where sawon_ibsail < '2000-1-1' and sawon_gen='남';
-- 2000년 이후에 입사한 여직원의 급여합, 급여평균, 인원수?
select sum(sawon_pay), avg(sawon_pay), count(*) from sawon
where sawon_ibsail >= '2000-1-1' and sawon_gen='여';
-- group by 절 : 소계 처리
-- select 그룹칼럼명,계산함수()... from 테이블명 where 조건
-- group by 그룹칼럼명 having 출력결과조건
-- group 칼럼에 order by 할 수 없다.
-- 단, 출력결과는 order by 할 수 있다.
-- 성별 급여의 평균, 인원수를 출력
select sawon_gen,round(avg(sawon_pay)) as avg, count(*)
from sawon
group by sawon_gen;
-- 부서별 급여합
select buser_num, sum(sawon_pay) from sawon group by buser_num;
-- 부서별 급여합 : 급여합이 15000 이상
select buser_num, sum(sawon_pay) from sawon group by buser_num
having sum(sawon_pay) >= 15000;
-- 부서별 급여합 : 남자만
select buser_num, sum(sawon_pay) from sawon where sawon_gen='남'
group by buser_num;
-- 부서별 급여합 : 급여합이 17000 이상인 남자
select buser_num, sum(sawon_pay) from sawon where sawon_gen='남'
group by buser_num having sum(sawon_pay) >= 17000;
-- 주의 group by 전에 order by 불가
select buser_num, sum(sawon_pay) from sawon where sawon_gen='남'
order by buser_pay group by buser_num; -- X
select buser_num, sum(sawon_pay) from sawon where sawon_gen='남'
group by buser_num order by buser_num desc; -- O
select buser_num, sum(sawon_pay) from sawon where sawon_gen='남'
group by buser_num order by sum(sawon_pay) desc; -- O
-- 그룹함수 중첩
select max(avg(sawon_pay)) from sawon group by sawon_jik;
--문1) 직급별 급여의 평균 (NULL인 직급 제외)
select sawon_jik 직급, round(avg(sawon_pay)) 급여평균
from sawon where sawon_jik is not null group by sawon_jik;
--문2) 부장,과장에 대해 직급별 급여의 총합
select sawon_jik 직급, sum(sawon_pay) 급여합
from sawon where sawon_jik = '부장' or sawon_jik = '과장'
group by sawon_jik;
--문3) 2003년 이전에 입사한 자료 중 년도별 직원수 출력
select to_char(sawon_ibsail, 'yyyy') 연도, count(*) 직원수
from sawon where sawon_ibsail < '2004-1-1'
group by to_char(sawon_ibsail, 'yyyy');
--문4) 직급별 성별 인원수, 급여합 출력 (NULL인 직급은 임시직으로 표현)
select nvl(sawon_jik, '임시직'), sawon_gen, count(*), sum(sawon_pay)
from sawon
group by sawon_jik, sawon_gen
order by sawon_jik;
--문5) 부서번호 10,20에 대한 부서별 급여 합 출력
select buser_num 부서번호, sum(sawon_pay) 급여합
from sawon where buser_num = 10 or buser_num = 20
group by buser_num;
--문6) 급여의 총합이 7000 이상인 직급 출력(NULL인 직급은 임시직으로 표현)
select nvl(sawon_jik, '임시직') 직급, sum(sawon_pay) 급여합
from sawon
group by sawon_jik
having sum(sawon_pay) >= 7000;
--문7) 직급별 인원수, 급여합계를 구하되 인원수가 3명 이상인 직급만 출력
-- (NULL인 직급은 임시직으로 표현)
select nvl(sawon_jik, '임시직') 직급, count(*) 인원수,
sum(sawon_pay) 급여합
from sawon
group by sawon_jik having count(*) >= 3;
select buser_num, sawon_jik, sum(sawon_pay), avg(sawon_pay)
from sawon
group by buser_num, sawon_jik;
-- cube : 총계 - crosstab 형식의 보고서 출력
select buser_num, sawon_jik, sum(sawon_pay), avg(sawon_pay)
from sawon
group by cube(buser_num, sawon_jik);
-- rollup : group by 결과에 누적된 부분 집계 출력
select buser_num, sawon_jik, sum(sawon_pay), avg(sawon_pay)
from sawon
group by rollup(buser_num, sawon_jik);
desc buser;
desc sawon;
desc gogek;
-- join : 하나 이상의 테이블에서 워하는 데이터 추출
-- cross join
select sawon_name,buser_name from sawon,buser;
select sawon_name,buser_name from sawon cross join buser; --ANSI SQL
-- equi join
-- inner join : 양쪽 테이블 모두에 자료가 있는 경우만 출력
select * from buser;
insert into buser values(50,'축구부','파주','031-234-5678');
select * from sawon;
alter table sawon modify buser_num number(4) null;
update sawon set buser_num=null where sawon_no=5;
commit;
select sawon_name,buser_name
from sawon, buser where buser_num=buser_no;
select sawon_name,buser_name
from sawon inner join buser on buser_num=buser_no; --ANSI
select sawon.sawon_name,buser.buser_name
from sawon inner join buser on sawon.buser_num=buser.buser_no;
-- outer join
-- left outer join : 좌쪽 테이블에 자료는 모두 나오고, 우측 자료는 null인 경우도 있다.
select sawon_name,buser_name
from sawon,buser where buser_num=buser_no(+);
select sawon_name,buser_name
from sawon left outer join buser on buser_num=buser_no;
-- right outer join : 우쪽 테이블에 자료는 모두 나오고, 좌측 자료는 null인 경우도 있다.
select sawon_name,buser_name
from sawon,buser where buser_num(+)=buser_no;
select sawon_name,buser_name
from sawon right outer join buser on buser_num=buser_no;
-- full join : 양쪽 테이블 자료 모두 출력
select sawon_name,buser_name
from sawon full join buser on buser_num=buser_no;
-- non equi join : 조건에 = 이외의 연산을 할 경우
create table paygrade(grade number(1) primary key,
lowpay number, highpay number);
insert into paygrade values(1,0,1999);
insert into paygrade values(2,2000,2999);
insert into paygrade values(3,3000,3999);
insert into paygrade values(4,4000,4999);
insert into paygrade values(5,5000,9999);
select * from PAYGRADE;
select sawon_no,sawon_name,sawon_pay,grade
from sawon,paygrade
where sawon_pay >= lowpay and sawon_pay <= highpay; --oracle
select sawon_no,sawon_name,sawon_pay,grade
from sawon inner join paygrade
on sawon_pay >= lowpay and sawon_pay <= highpay; --ansi
-- self join
select a.sawon_name, b.sawon_jik
from sawon a, sawon b where a.sawon_no=b.sawon_no;
-- sawon + gogek
select sawon_no,sawon_name,sawon_jik,gogek_name,gogek_jumin
from sawon inner join gogek on sawon_no=gogek_damsano;
-- 부서내에 근무자 출력 (부서없는 직원은 제외)
select buser_name,sawon_name,sawon_jik,buser_tel
from sawon inner join buser
on sawon.buser_num = buser.buser_no
order by buser_name asc;
-- 부서별 급여합
select nvl(buser_name,'임시부') as 부서,
sum(sawon_pay) as 급여합
from sawon,buser where buser_num=buser_no
group by buser_name; --oracle
select nvl(buser_name,'임시부') as 부서,
sum(sawon_pay) as 급여합
from sawon inner join buser on buser_num=buser_no
group by buser_name; --ansi
--문1) 직급이 사원인 직원이 관리하는 고객 출력
--출력 ==> 사번 사원명 직급 고객명 고객전화 고객성별
-- 3 한국인 사원 우주인 123-4567 남
select sa.sawon_no, sa.sawon_name, sa.sawon_jik,
go.gogek_name, go.gogek_tel,
case substr(gogek_jumin,8,1)
when '1' then '남' else '여' end as 고객성별
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano
where sa.sawon_jik = '사원';
--문2) 직원별 고객 확보 수 -- GROUP BY 사용
-- - 모든 직원 참여
select sawon_name 직원이름,count(gogek_name) 고객확보수
from sawon left outer join gogek
on sawon_no = gogek_damsano
group by sawon_name; --동명이인 처리X
select sawon_no 직원이름,count(gogek_name) 고객확보수
from sawon left outer join gogek
on sawon_no = gogek_damsano
group by sawon_no; --동명이인 처리
--문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면,
-- 담당직원 자료 출력
-- : ~ WHERE GOGEK_NAME='강나루'
--출력 ==> 직원명 직급
-- 한국인 사원
select sawon_name, sawon_jik
from sawon inner join gogek
on sawon_no = gogek_damsano where gogek_name='이나라';
--문4) 직원명을 입력하면 관리고객 자료 출력
-- : ~ WHERE SAWON_NAME='한국인'
--출력 ==>고객명 고객전화 주민번호 나이
-- 강나루 123-4567 700512-1234567 38
-- 2000년생 이전 고객
select go.gogek_name 고객명, go.gogek_tel 고객전화,
go.gogek_jumin 주민번호,
to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin,1,2)+1900) 나이
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano where sa.sawon_name='한국남';
--2000년 생 이후 고객 존재 시
select go.gogek_name 고객명, go.gogek_tel 고객전화,
go.gogek_jumin 주민번호,
case
when substr(go.gogek_jumin, 8,1) > 3
then to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin, 1,2)+2000)
else to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin, 1,2)+1900) end as 나이
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano where sa.sawon_name='한국남';
-- 3개의 테이블 조인
select sawon_name,buser_name,gogek_name
from sawon,buser,gogek
where buser_num=buser_no and sawon_no=gogek_damsano;
select sawon_name,buser_name,gogek_name
from sawon
inner join buser on buser_num=buser_no
inner join gogek on sawon_no=gogek_damsano;
--문1) 총무부에서 관리하는 고객수 출력 (고객 30살 이상만 작업에 참여)
select buser_name 부서이름, count(gogek_name) 고객수
from sawon
inner join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no
where buser_name='총무부' and
(to_char(sysdate, 'YYYY')-(substr(gogek_jumin,1,2)+1900)) >= 30
group by buser_name;
--문2) 부서명별 고객 인원수 (부서가 없으면 "무소속")
select nvl(buser_name,'무소속') 부서명,
count(gogek_name) 고객수
from sawon
full outer join buser on buser_no = buser_num
full outer join gogek on gogek_damsano = sawon_no
group by buser_name;
--문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면 담당직원 자료 출력
-- : ~ WHERE GOGEK_NAME='강나루'
--출력 ==> 직원명 직급 부서명 부서전화 성별
select sawon_name 직원명, nvl(sawon_jik,'프리랜서') 직급,
nvl(buser_name,'프리랜서') 부서명,
nvl(buser_tel,'프리랜서') 부서전화, sawon_gen 성별
from sawon
left outer join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no
where gogek_name = '강나루';
--문4) 부서와 직원명을 입력하면 관리고객 자료 출력
-- ~ WHERE BUSER_NAME='영업부' AND SAWON_NAME='이순신'
--출력 ==> 고객명 고객전화 성별
-- 강나루 123-4567 남
select gogek_name 고객명, gogek_tel 고객전화,
case substr(gogek_jumin,8,1) when '1' then '남' else '여'
end as 성별
from sawon
left outer join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no
where buser_name='영업부' and sawon_name = '이순신';
-- union : 구조가 일치하는 두 개 이상의 테이블 자료 합쳐 보기
create table pum1(bun int, pummok varchar2(20));
insert into pum1 values(1,'귤');
insert into pum1 values(2,'바나나');
insert into pum1 values(3,'한라본');
insert into pum1 values(50,'레몬');
select * from pum1;
create table pum2(num int, sangpum varchar2(20));
insert into pum2 values(10,'수박');
insert into pum2 values(20,'토마토');
insert into pum2 values(30,'참외');
insert into pum2 values(40,'딸기');
insert into pum2 values(50,'레몬');
select * from pum2;
select bun,pummok from pum1 union select num,sangpum from pum2;
select bun,pummok from pum1 union all select num,sangpum from pum2;
select bun,pummok from pum1 minus select num,sangpum from pum2;
select bun,pummok from pum1 intersect select num,sangpum from pum2;
select * from pum1;
select * from pum2;
-- 고객을 관리하는 직원 목록 출력
select sawon_no from sawon intersect
select gogek_damsano from gogek;
select sawon_no,sawon_name from sawon
where sawon_no in(select sawon_no from sawon intersect
select gogek_damsano from gogek);
-- 고객을 관리하지 않는 직원 목록 출력
select sawon_no,sawon_name from sawon
where sawon_no in(select sawon_no from sawon minus
select gogek_damsano from gogek);
-- merge : 구조가 일치하는 두 개 이상의 테이블 자료를 하나로 합치기
-- 이미 있는 테이블을 이용하여 새로운 테이블 생성
create table msa1 as select sawon_no,sawon_name,sawon_pay
from sawon where sawon_no <= 10;
select * from msa1;
desc msa1;
create table msa2 as select sawon_no,sawon_name,sawon_pay
from sawon where sawon_no <= 10 and sawon_jik='사원';
select * from msa2;
insert into msa1 values(100,'김밥',8000);
insert into msa2 values(200,'공기밥',6700);
select * from msa1;
select * from msa2;
-- 기존에 행이 있으면 update하고, 없으면 insert 함
merge into msa1 a using msa2 b on(a.sawon_no=b.sawon_no)
when matched then update set a.sawon_name=b.sawon_name,
a.sawon_pay = b.sawon_pay
when not matched then insert values(b.sawon_no,b.sawon_name,
b.sawon_pay);
select * from msa1;
select * from msa2;
--subquery:질의 속에 질의가 있는 형태(안쪽 질의의 결과를 바깥쪽에서 참조)
--직원 중 이순신과 직급이 같은 자료 출력
select sawon_jik from sawon where sawon_name='이순신';
select * from sawon where sawon_jik='과장';
select * from sawon
where sawon_jik=(select sawon_jik from sawon
where sawon_name='이순신');
-- 직급이 대리 중에 가장 먼저 입사한 자료는?
select min(sawon_ibsail) from sawon where sawon_jik='대리';
select * from sawon where sawon_ibsail='10/11/4';
select * from sawon
where sawon_ibsail=(select min(sawon_ibsail) from sawon
where sawon_jik='대리'); --'10/11/4'일에 입사한 모든 직원이 대상
select * from sawon
where sawon_jik='대리' and
sawon_ibsail=(select min(sawon_ibsail) from sawon
where sawon_jik='대리'); -- O
-- 담당 직원이 '한국남'인 고객자료 출력
select * from gogek where gogek_damsano=(select
sawon_no from sawon where sawon_name='한국남');
-- 고객 중에서 '차일호'와 나이가 같은 고객자료 출력
select * from gogek where substr(gogek_jumin,1,2) = (select
substr(gogek_jumin,1,2) from gogek where gogek_name='차일호');
-- 인천에서 근무하는 직원 출력
select * from sawon where buser_num=(select buser_no
from buser where buser_loc='인천');
-- 인천 이외의 지역에서 근무하는 직원 출력
select * from sawon where buser_num in(select buser_no
from buser where not buser_loc='인천');
-- where 조건이 복수
-- 7번 직원과 직급이 같고, 직급이 사원인 직원의 평균연봉 보다 급여가
-- 많은 직원 출력
select sawon_no,sawon_name, sawon_pay from sawon
where sawon_jik=(select sawon_jik from sawon
where sawon_no=7) and sawon_pay >= (select avg(sawon_pay)
from sawon where sawon_jik='사원');
--SAWON, BUSER, GOGEK 테이블을 사용한다.
--문1) 2000년 이후에 입사한 남자 중 급여를 가장 많이 받는 직원은?
select * from sawon
where sawon_gen = '남' and sawon_ibsail > '2000-1-1' and
sawon_pay = (select max(sawon_pay) from sawon
where sawon_gen = '남' and sawon_ibsail > '2000-1-1');
--문2) 평균급여보다 급여를 많이 받는 직원은?
select * from sawon
where sawon_pay > (select avg(sawon_pay) from sawon);
--문3) '한국남' 직원의 입사 이후에 입사한 직원은?
select * from sawon
where sawon_ibsail > (select sawon_ibsail from sawon
where sawon_name = '한국남');
--문4) 2000 ~ 2005 사이에 입사한 총무부,영업부,전산부 직원 중
-- 급여가 가장 적은 사람은?
-- (직급이 NULL인 자료는 작업에서 제외)
select * from sawon
where sawon_pay = (select min(sawon_pay) from sawon
where buser_num in
(select buser_no from buser where buser_name in('총무부', '영업부', '전산부'))
and sawon_ibsail between '2000-1-1' and '2005-12-31' and sawon_jik is not null)
and
buser_num in
(select buser_no from buser where buser_name in('총무부', '영업부', '전산부'))
and
sawon_ibsail between '2000-1-1' and '2005-12-31' and sawon_jik is not null;
--문5) 홍길동, 이순신과 직급이 같은 사람은 누구인가?
select * from sawon
where sawon_jik in(select sawon_jik from sawon
where sawon_name = '이순신' or sawon_name = '홍길동');
--문6) 과장 중에서 최대급여, 최소급여를 받는 사람은?
select * from sawon
where sawon_jik = '과장' and
sawon_pay in((select min(sawon_pay) from sawon
where sawon_jik = '과장'),(select max(sawon_pay) from sawon
where sawon_jik = '과장'));
--문7) 20번 부서의 최소급여보다 많은 사람은?
select * from sawon
where sawon_pay > (select min(sawon_pay) from sawon
where buser_num = 20);
--문8) 30번 부서의 평균급여보다 급여가 많은 '대리'는 몇명인가?
select count(sawon_jik) 인원수 from sawon
where sawon_jik = '대리' and
sawon_pay > (select avg(sawon_pay) from sawon
where buser_num = 30);
--문9) 고객을 확보하고 있는 직원들의 이름, 직급, 부서명을
-- 입사일 별로 출력하라.
select sawon_name,sawon_jik,buser_name from sawon
left outer join buser on buser_num=buser_no
where sawon_no in(select distinct gogek_damsano from gogek)
order by sawon_ibsail;
select sawon_name 이름, nvl(sawon_jik, '프리랜서') 직급,
nvl((select buser_name from buser where buser_no = buser_num),
'프리랜서') 부서명
from sawon
where sawon_no in (select distinct gogek_damsano from gogek)
order by sawon_ibsail asc;
--문10) 이순신과 같은 부서에 근무하는 직원과 해당 직원이 관리하는 고객 출력
--(고객은 나이가 30 이하면 '청년', 40 이하면 '중년', 그 외는 '노년'으로 표시하고, 고객 연장자 부터 출력)
--출력 ==> 직원명 부서명 부서전화 직급 고객명 고객전화 고객구분
-- 한송이 총무부 123-1111 사원 백송이 333-3333 청년
select sawon_name 직원명, buser_name 부서명, buser_tel 부서전화,
sawon_jik 직급, gogek_name 고객명, gogek_tel 고객전화,
case when (100 + to_char(sysdate,'YY') - substr(gogek_jumin,1,2)) <= 30 then '청년'
when (100 + to_char(sysdate,'YY') - substr(gogek_jumin,1,2)) <=40 then '중년' else '노년'
end 고객구분 from sawon
left outer join gogek on sawon_no = gogek_damsano
inner join buser on buser_num = buser_no
where buser_num = (select buser_num from sawon where sawon_name = '이순신')
order by 고객구분;
-- subquery 추가 연습 : any와 all 연산자
-- < any : subquery의 반환값 중 최대값 보다 작은 ~
-- > any : subquery의 반환값 중 최소값 보다 큰 ~
-- < all : subquery의 반환값 중 최소값 보다 작은 ~
-- > all : subquery의 반환값 중 최대값 보다 큰 ~
-- 대리의 연봉이 최대값 보다 작은 자료 출력
select * from sawon
where sawon_pay < any (select sawon_pay from sawon
where sawon_jik='대리');
-- 30번 부서의 최대 급여자 보다 급여를 많이 받는 사람은?
select * from sawon
where sawon_pay > all (select sawon_pay from sawon
where buser_num=30);
-- 30번 부서의 최저 급여자 보다 급여를 많이 받는 사람은?
select * from sawon
where sawon_pay > any (select sawon_pay from sawon
where buser_num=30);
-- 총무부에 근무하는 직원들이 관리하는 고객 출력
-- 방법1 : subquery
select gogek_no,gogek_name,gogek_jumin from gogek
where gogek_damsano in(select sawon_no from sawon where
buser_num=(select buser_no from buser where buser_name='총무부'));
-- 방법2 : join
select gogek_no,gogek_name,gogek_jumin from gogek
inner join sawon on sawon_no=gogek_damsano
inner join buser on buser_num=buser_no
where buser_name='총무부';
-- exists 연산자 사용 - subquery의 결과 유무를 검사
-- 직원이 있는 부서 출력
select buser_name, buser_loc from buser bu
where exists (select 'imsi' from sawon
where buser_num=bu.buser_no);
-- 직원이 없는 부서 출력
select buser_name, buser_loc from buser bu
where not exists (select 'imsi' from sawon
where buser_num=bu.buser_no);
-- from 절에 subquery 사용 : inline view
-- 직원 전체 평균 급여와 최대 급여 사이의 급여를 받는 직원 출력
select sawon_no, sawon_name,sawon_pay
from sawon a, (select avg(sawon_pay) avgs, max(sawon_pay) maxs
from sawon) b
where a.sawon_pay between b.avgs and b.maxs;
-- group by의 having 절 안에 subquery 사용
-- 부서별 평균급여 중 20번 부서의 평균 급여보다 큰 자료만 출력
select buser_num, avg(sawon_pay) from sawon
group by buser_num
having avg(sawon_pay) > (select avg(sawon_pay) from sawon
where buser_num=20);
-- 상관 서브쿼리 : 안쪽 질의의 자료를 바깥쪽에서 참조하고, 다시
-- 안쪽의 결과를 바깥쪽 질의에서 참조하는 형태
-- 각 부서의 최대 급여자는?
select * from sawon a
where a.sawon_pay=(select max(b.sawon_pay) from sawon b
where a.buser_num=b.buser_num);
-- 급여순위 3위 이내의 자료 출력 (desc)
select a.sawon_name, a.sawon_pay from sawon a
where 3 > (select count(*) from sawon b
where b.sawon_pay > a.sawon_pay) and sawon_pay is not null
order by sawon_pay desc;
-- subquery를 이용한 table 생성 및 insert
create table sa1 as select * from sawon; -- 제약조건(pk)는 복사 안됨
select * from sa1;
desc sa1;
create table sa2 as select * from sawon where 1=0;
select * from sa2;
desc sa2;
create table sa3 as select sawon_no bunho,
sawon_name irum, sawon_pay pay from sawon where 1=0;
select * from sa3;
-- 복수 테이블에 insert
create table sa4 as select sawon_no, sawon_name, sawon_jik
from sawon where 1=0;
create table sa5 as select sawon_no, sawon_name, sawon_pay,
sawon_gen from sawon where 1=0;
select * from sa4;
select * from sa5;
insert all
into sa4 values(sawon_no, sawon_name, sawon_jik)
into sa5 values(sawon_no, sawon_name, sawon_pay,sawon_gen)
select sawon_no, sawon_name, sawon_jik, sawon_pay, sawon_gen
from sawon where buser_num = 10;
select * from sa4;
select * from sa5;
-- 조건에 따른 복수 테이블에 insert 하기
insert all
when sawon_jik='사원' then
into sa4 values(sawon_no, sawon_name, sawon_jik)
when sawon_gen='남' then
into sa5 values(sawon_no, sawon_name, sawon_pay,sawon_gen)
select sawon_no, sawon_name, sawon_jik, sawon_pay, sawon_gen
from sawon where buser_num in(20, 30);
select * from sa4;
select * from sa5;
-- subquery로 update, delete
create table sa6 as select * from sawon;
select * from sa6;
update sa6 set sawon_jik=(select sawon_jik from sawon
where sawon_name='한국남') where sawon_no=1;
select * from sa6;
delete from sa6 where sawon_no in(select distinct gogek_damsano
from gogek); --고객을 가진 직원자료 모두 삭제
select * from sa6;
-- Database Transactions
-- 단위별 처리를 의미한다. 한 사용자에 의해 수행되는 한 개 이상의
-- SQL구문을 포함하는 가장 작은 논리적인 작업이다.
-- 데이터의 일관성을 보장한다.
-- A(Atomicity)C(Consistency)I(Isolation)D(Durablility)를 만족해야한다.
-- Transaction 발생 : insert,update,delete ...
-- Transaction 종료 : commit, rollback ...
select * from sa6;
delete from sa6 where sawon_no >= 10; -- Tr 시작
select * from sa6;
rollback; -- Tr 종료
select * from sa6;
delete from sa6 where sawon_no = 10; -- Tr 시작
select * from sa6;
commit; -- Tr 종료
select * from sa6;
-- save point 사용하여 transaction 처리
select * from sa6;
update sa6 set sawon_pay=777 where sawon_no=11;
savepoint a;
update sa6 set sawon_pay=888 where sawon_no=12;
rollback to savepoint a;
select * from sa6;
commit;
select * from sa6;
-- deadlocks : 두 개의 트랜젝션이 서로의 진행을 막고 충돌하는 상황
-- 이 것은 자원과 시간을 낭비하므로 피해야 한다.
-- 트랜젝션을 완료해 주면 된다. 일관성 있는 작업이 중요.
select * from sa6;
update sa6 set sawon_name='tom' where sawon_no=11;
rollback;
-- View 파일 :
-- 물리적인 테이블을 근거로 논리적인 가상 테이블을 만들어 사용.
-- select 문의 조건을 파일로 만들어 테이블 처럼 사용한다.
-- 복잡하고 긴 select 문을 단순화
-- 보안 강화
-- 자료의 독립성 확보
-- 형식: create[or replace] view 뷰파임명 as select문
create table jikwon as select * from sawon;
select * from jikwon;
-- system 계정에서 scott 계정에 대한 view 생성 권한 부여 작업 필요
-- SQL> grant create view to scott;
-- 권한이 부여되었습니다.
---------------------------
create or replace view v_a as
select sawon_no,sawon_name,sawon_pay from jikwon
where sawon_ibsail < '2013-12-30';
select * from v_a;
select sawon_no,sawon_name from v_a;
select sawon_no,sawon_name from v_a where sawon_no <= 5;
select count(*) from v_a;
desc v_a;
create view v_b as select * from jikwon
where sawon_name like '김%' or sawon_name like '박%';
select * from v_b;
rename jikwon to nice;
select * from v_a;
select * from v_b;
rename nice to jikwon;
create view v_c as select * from jikwon
order by sawon_pay desc;
select * from v_c;
create view v_d as select sawon_no as bun,
sawon_name as irum, sawon_pay * 10000 as ypay
from jikwon where sawon_jik is not null and
sawon_pay is not null;
select * from v_d;
create view v_e as select bun,irum,ypay from v_d
where ypay >= 50000000;
select * from v_e;
update v_e set irum='tom' where bun=1;
select * from v_e;
select * from v_d;
select * from jikwon;
update v_e set ypay='100' where bun=1; -- X
delete from v_e where bun = 1;
delete from v_e where ypay = 55000000;
select * from v_e;
drop view v_e;
create view v_f as select sawon_no,sawon_name,
buser_num, sawon_pay
from jikwon where sawon_pay >= 2000;
select * from v_f;
insert into v_f values(100,'황사',20,4550);
insert into v_f values(110,'황진이',20,1550);
select * from jikwon;
create view v_g as
select sawon_jik, sum(sawon_pay) as hap from jikwon
group by sawon_jik;
select * from v_g;
create view v_h as
select sawon_name,buser_name,gogek_name from jikwon
inner join buser on buser_num=buser_no
inner join gogek on sawon_no=gogek_damsano;
select * from v_h;
--문1) 사번 이름 부서 직급 근무년수 고객확보
-- 1 홍길동 영업부 사원 6 O or X
--조건 : 직급이 없으면 임시직, 전산부 자료는 제외
--위의 결과를 위한 뷰파일 v_exam1을 작성
create or replace view v_exam1 as
select distinct sawon_no as 사번,sawon_name 직원명,buser_name 부서,
nvl(sawon_jik,'임시직') 직급,
to_char(sysdate, 'YYYY') - to_char(sawon_ibsail,'YYYY') as 근무년수,
case nvl(gogek_name,'a')
when 'a' then 'X' else 'O' end 고객확보
from sawon
left outer join buser on buser_num=buser_no
left outer join gogek on sawon_no=gogek_damsano
where buser_name <> '전산부';
select * from v_exam1;
--문2) 부서명 인원수
-- 영업부 7
--조건 : 직원수가 가장 많은 부서 출력
--위의 결과를 위한 뷰파일 v_exam2을 작성
create or replace view v_exam2 as
select buser_name 부서명,count(*) 인원수
from buser
inner join sawon on buser_num=buser_no
group by buser_name having count(*)=(select
max(count(*)) from sawon group by buser_num);
select * from v_exam2;
--문3) 가장 많은 직원이 입사한 요일에 입사한 직원 출력
-- 직원명 요일 부서명 부서전화
-- 한국인 수요일 전산부 222-2222
--위의 결과를 위한 뷰파일 v_exam3을 작성
--select to_char(sawon_ibsail,'DAY') from sawon;
create or replace view v_exam3 as
select sawon_name 직원명, to_char(sawon_ibsail,'DAY') 요일,
buser_name 부서명, buser_tel 부서전화
from sawon
left outer join buser on buser_num=buser_no
where to_char(sawon_ibsail, 'DAY') in(
select to_char(sawon_ibsail, 'DAY') from sawon
group by to_char(sawon_ibsail, 'DAY')
having count(*) = (select max(count(*)) from sawon
group by to_char(sawon_ibsail, 'DAY')));
select * from v_exam3;
-- 계정(사용자) 생성 및 보안
-- 별도의 사용자를 만들고, 사용자별 DB공유를 위함
-- 각 사용자들에 대해 사용권한을 제한할 수 있다.
-- 계정은 system, sys 계정에 의해 생성, 삭제할 수 있다.
C:\Users\kitcoop>sqlplus system/oracle
SQL> show user;
USER은 "SYSTEM"입니다
SQL> select * from all_users;
계정 생성 create user 사용자명 identified by 암호;
계정 삭제 drop user 사용자명;
SQL> create user tom identified by tom123;
SQL> grant connect,resource to tom;
-- 동의어(synonym) : 다른 객체에 대한 별명 부여
-- PL / SQL : 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어 중 하나이다.
-- 구조
-- 1)선언부
-- 2)실행부
-- 3)예외부
create table aa(bun number, munja varchar2(20), su number);
set serveroutput on; -- 표준출력장치로 출력 선언
declare
no number:=0;
begin
no := 100 + 200;
dbms_output.put_line(no);
insert into aa(bun) values(no);
end;
select * from aa; --pl/sql 수행 결과 확인
-- 배열처리
declare
type result is record(a number, b number);
type test is varray(100) of result;
test1 test := test(); -- 객체 초기화
begin
test1.extend(50); -- 50개에 대한 공간 할당
test1(1).a := 10;
test1(1).b := 20;
dbms_output.put_line(test1(1).a);
end;
-- exception
declare
counter number(3):=10;
re number;
begin
--re := counter / 2;
re := counter / 0;
dbms_output.put_line(re);
exception when zero_divide then
dbms_output.put_line('error');
end;
-- 변수 선언 - 해당 테이블 형
declare
v_a sawon%rowtype;
begin
select * into v_a from sawon where sawon_no=1;
dbms_output.put_line(v_a.sawon_no || ' ' || v_a.sawon_name);
insert into aa values(v_a.sawon_no,v_a.sawon_name,v_a.sawon_pay);
end;
select * from aa;
-- 변수 선언 - 해당 테이블 칼럼형
declare
a sawon.sawon_no%type;
b sawon.sawon_name%type;
c sawon.sawon_pay%type;
begin
select sawon_no,sawon_name,sawon_pay into a,b,c from sawon
where sawon_no=10;
dbms_output.put_line(a || ' ' || b || ' ' || c);
end;
-- 조건판단문 if
declare
v_a sawon%rowtype;
v_str varchar2(20);
begin
select * into v_a from sawon where sawon_no=9;
if(v_a.buser_num=10)
then v_str:=concat(v_a.sawon_name, ' 10');
end if;
if(v_a.buser_num=20)
then v_str:=concat(v_a.sawon_name, ' 20');
end if;
if(v_a.buser_num=30)
then v_str:=concat(v_a.sawon_name, ' 30');
end if;
if(v_a.buser_num=40)
then v_str:=concat(v_a.sawon_name, ' 40');
end if;
dbms_output.put_line(v_str);
if(v_a.buser_num=10)
then v_str:=concat(v_a.sawon_name, ' 10번');
elsif(v_a.buser_num=20)
then v_str:=concat(v_a.sawon_name, ' 20번');
elsif(v_a.buser_num=30)
then v_str:=concat(v_a.sawon_name, ' 30번');
elsif(v_a.buser_num=40)
then v_str:=concat(v_a.sawon_name, ' 40번');
else
v_str:=concat(v_a.sawon_name, ' 기타');
end if;
dbms_output.put_line(v_str);
end;
-- 반복문 for loop
declare
dan number(2):=2;
i number(2):=0;
tot number(2):=0;
begin
for i in 1..9 loop
tot := dan * i;
dbms_output.put_line(dan || '*' || i || '=' || tot);
end loop;
end;
-- 반복문 while
declare
v_cou number := 1;
begin
while(v_cou <= 10) loop
dbms_output.put_line(v_cou);
exit when (v_cou = 5);
v_cou := v_cou + 1;
end loop;
end;
-- 무한 루핑
declare
v_cou number := 1;
begin
loop
dbms_output.put_line(v_cou);
exit when (v_cou = 5);
v_cou := v_cou + 1;
end loop;
end;
declare
v_a number := 0;
v_b number := 0;
begin
while v_a < 10 loop
v_a := v_a + 1;
if mod(v_a, 2) = 0 then
dbms_output.put_line('짝수');
else
dbms_output.put_line('홀수');
end if;
end loop;
end;
-- 커서 : 사용자가 실행한 sql문의 단위를 말함
-- 1개의 행에 대한 수행은 별도 선언없이 암시적인 커서를 사용
-- 여러 개의 행에 대해서는 커서를 이용해야 한다.
-- 형식) cursor 커서명 open 커서명 fetch 커서명 into 변수 close 커서명
declare
no number;
name varchar2(10);
pay number;
cursor cs is select sawon_no,sawon_name,sawon_pay from sawon
where sawon_jik='사원';
begin
open cs;
loop
fetch cs into no, name, pay;
exit when cs%notfound;
dbms_output.put_line(no || ' ' || name || ' ' || pay);
end loop;
close cs;
end;
-- open ~ fetch ~ close 없이 커서 처리
declare
saw sawon%rowtype;
cursor cc is
select sawon_no,sawon_name,sawon_pay from sawon
where sawon_jik='과장';
begin
for saw in cc loop
exit when cc%notfound;
dbms_output.put_line(saw.sawon_no || ' ' || saw.sawon_name || ' ' || saw.sawon_pay);
end loop;
end;
-- 함수 작성하기
-- create or replace function 함수명 [(인자)...]
-- return datatype
-- is
-- begin
-- end;
create or replace function func1(no number) return number is
pay number(9);
begin
pay := 0;
select sawon_pay * 0.1 into pay from sawon where sawon_no=no;
return pay;
end;
/
select func1(2) from dual;
select sawon_no,sawon_name,sawon_pay,
sawon_pay * 0.1,func1(sawon_no) from sawon;
-- 부서명 얻기
create or replace function func2(bno number) return varchar2 is
bname varchar2(10);
begin
select buser_name into bname from buser where buser_no=bno;
return bnamecreate or replace function func2(bno number) return varchar2 is, bname varchar2(10);,begin, select buser_name into bname from buser where buser_no=bno;, return bname;,end;,/;
end;
/
select sawon_no,sawon_name,buser_num,func2(buser_num) from sawon;
--문) 부서번호가 없으면 '임시직', 있으면 해당 부서명 출력 함수 작성
-- null로 판단
create or replace function func3(bno number) return varchar2 is
bname varchar2(10);
begin
if bno is null then
return '임시직';
else select buser_name into bname from buser where buser_no=bno;
return bname;
end if;
end;
/
select sawon_no,sawon_name,buser_num,func3(buser_num) from sawon;
-- procedure : 이름을 가진 PL/SQL 블럭
-- 형식)
-- create or replace procedure 프로시저명 [(인자)...] is
-- 변수선언
-- begin
-- end;
create table sawon1 as select * from sawon;
select * from sawon1;
-- 레코드 삭제 프로시저
create or replace procedure pr_a is
begin
delete from sawon1 where sawon_no=1;
end;
/
execute pr_a; -- procedure의 실행
select * from sawon1;
-- 레코드 수정 프로시저
create or replace procedure pr_b is
bun number := 2;
begin
update sawon1 set sawon_pay = 7777 where sawon_no=bun;
end;
/
exec pr_b;
select * from sawon1;
-- 매개변수가 있는 레코드 삭제 프로시저
create or replace procedure pr_c(no sawon1.sawon_no%type) is
begin
delete from sawon1 where sawon_no=no;
end;
/
exec pr_c(2);
exec pr_c(3);
select * from sawon1;
-- 매개변수가 있는 레코드 수정 프로시저
create or replace procedure
pr_d(no sawon1.sawon_no%type, pay sawon1.sawon_pay%type,
jik sawon1.sawon_jik%type) is
begin
update sawon1 set sawon_pay = pay, sawon_jik = jik
where sawon_no=no;
end;
/
exec pr_d(5,8888,'이사');
exec pr_d(6,5678,'과장대우');
select * from sawon1;
create or replace procedure pr_sel1(no in sawon1.sawon_no%type) is
name varchar2(10);
begin
select sawon_name into name from sawon1
where sawon_no=no;
dbms_output.put_line('이름은 ' || name);
end;
/
exec pr_sel1(5);
create or replace procedure pr_sel2(no in sawon1.sawon_no%type,
name out sawon1.sawon_name%type,
jik out sawon1.sawon_jik%type) is
begin
select sawon_name,sawon_jik into name, jik from sawon1
where sawon_no=no;
dbms_output.put_line('이름은 ' || name || ', 직급은 ' || jik);
end;
/
-- 정리 : person 테이블 --
create table person(id number(3) primary key,
name varchar2(20), weight number(3));
desc person;
insert into person values(1,'홍길동',66);
insert into person values(2,'고길동',76);
insert into person values(3,'신길동',68);
select * from person;
-- procedure로 insert
create or replace procedure p_1(no in person.id%type,
name varchar2, weight number) is
begin
insert into person values(no,name,weight);
commit;
exception when others then
dbms_output.put_line('insert err');
rollback;
end;
/
exec p_1(5,'김밥',77);
select * from person;
-- procedure로 update
create or replace procedure p_2(pid person.id%type,
pname person.name%type, pweight person.weight%type) is
begin
update person set name=pname,weight=pweight where id=pid;
commit;
exception when others then
dbms_output.put_line('update err');
rollback;
end;
/
exec p_2(3,'손오공',99);
select * from person;
-- procedure로 delete
create or replace procedure p_3(d_id person.id%type) is
begin
delete from person where id=d_id;
commit;
exception when others then
dbms_output.put_line('delete err');
rollback;
end;
/
exec p_3(5);
select * from person;
-- procedure로 select
create or replace procedure p_4(s_id person.id%type) is
pid person.id%type;
pname person.name%type;
pweight person.weight%type;
begin
select id,name,weight into pid,pname,pweight from person
where id=s_id;
dbms_output.put_line(pid || ' ' || pname || ' ' || pweight);
end;
/
exec p_4(2);
-- select 복수 개 반환 : 커서 사용
create or replace procedure p_5 is
cursor cur is select id,name,weight from person;
pid person.id%type;
pname person.name%type;
pweight person.weight%type;
begin
open cur;
loop
fetch cur into pid,pname,pweight;
exit when cur%notfound;
dbms_output.put(pid);
dbms_output.put(pname);
dbms_output.put_line(pweight);
end loop;
close cur;
end;
/
exec p_5;
-- select 복수 개 반환 : 커서 사용
create or replace procedure p_6 is
cursor cur is select id,name,weight from person;
begin
for per_list in cur loop
dbms_output.put(per_list.id);
dbms_output.put(per_list.name);
dbms_output.put_line(per_list.weight);
end loop;
end;
/
exec p_6;
본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.
728x90
'DATA' 카테고리의 다른 글
| 파이썬(주피터 노트북) 주석처리할 때 방법 (0) | 2018.08.13 |
|---|---|
| EverSQL SQL Query Syntax Check - SQL 문법이 맞는지 미리 검사하자 (2) | 2018.04.04 |
| JAVA - SQL 테이블 연결 예제 (0) | 2018.01.23 |
| PL/SQL 기초 연습 (0) | 2018.01.22 |
| SQL 계정(사용자) 생성 및 보안 요약 (0) | 2018.01.19 |
| SQL transaction, view 요약 (0) | 2018.01.18 |