-- sequence
create table dept_sequence
as select * from dept where 1<>1; -- 틀만 복사
select * from dept_sequence;
-- 시퀀스 객체 만들기
-- 이름을 지을 때는 테이블명_seq 이런 식....
create sequence seq_dept_sequence
increment by 10 start with 10 maxvalue 90 minvalue 0 nocycle cache 2;
select * from user_sequences;
insert into dept_sequence (deptno, dname, loc)
values (seq_dept_sequence.nextval, 'DATABASE', 'SEOUL');
select * from dept_sequence order by 1;
-- 시퀀스 옵션 수정(객체 수정) : alter 사용
alter sequence seq_dept_sequence
increment by 3 maxvalue 99 cycle;
-- 시퀀스 삭제
drop sequence seq_dept_sequence;
-- 제약 조건
create table table_notnull(
login_id varchar2(20) not null,
login_pwd varchar2(20) not null,
Tel varchar2(20)
);
desc table_notnull;
insert into table_notnull values('aaa', null, 'dddd'); -- Error, 제약조건 위반
insert into table_notnull values('aaa', 'dddd', null );
-- 제약조건 확인
select owner, constraint_name, constraint_type, table_name from user_constraints;
-- P : Primary Key
-- R : Foreign Key
-- U : Unique
-- C : Check, not null
-- 제약조건에 이름 달기
create table table_notnull2(
login_id varchar2(20) constraint tblnn2_lgnid_nn not null,
login_pwd varchar2(20) constraint tblnn2_lgnpw_nn not null,
Tel varchar2(20)
);
select owner, constraint_name, constraint_type, table_name from user_constraints;
-- 생성한 테이블에 제약 조건 추가하기
delete table_notnull; -- 테이블에 이미 null 데이터가 들어있어서 없애주고 제약조건 추가하기
alter table table_notnull
modify(Tel not null);
desc table_notnull;
-- 생성한 테이블에 제약 조건 추가하기 + 제약 조건 이름도 정해주기
alter table table_notnull2
modify(Tel constraint tblnn_tel_nn not null);
desc table_notnull2;
select owner, constraint_name, constraint_type, table_name from user_constraints;
-- 제약 조건의 이름 변경
alter table table_notnull2
rename constraint tblnn_tel_nn to tblnn2_tel_nn;
select owner, constraint_name, constraint_type, table_name from user_constraints;
-- 제약 조건 삭제
alter table table_notnull2
drop constraint tblnn2_tel_nn;
select owner, constraint_name, constraint_type, table_name from user_constraints;
-- unique
create table table_unique(
login_id varchar2(20) unique,
login_pwd varchar2(20) not null,
tel varchar2(20)
);
select owner, constraint_name, constraint_type, table_name from user_constraints;
insert into table_unique (login_id, login_pwd, tel)
values('TEST_ID_01', 'PWD01', '010-1234-5678');
insert into table_unique (login_id, login_pwd, tel)
values(null, 'PWD01', '010-1234-5678'); -- unique여도 null은 가능하다....
select * from table_unique;
-- foreign key
select
owner, constraint_name, constraint_type, table_name, R_OWNER, r_constraint_name
from user_constraints
where table_name in ('EMP', 'DEPT');
select * from emp;
select * from dept;
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7777, '????', 'CLERK', 7902, sysdate, 1000, null, 50); -- Error : dept에 50에 해당하는 값이 없으므로 --> 데이터 무결성
--
create table dept_fk (
deptno number(2) constraint deptfk_deptno_pk primary key,
dname varchar2(14),
loc varchar2(13)
);
create table emp_fk (
empno number(4) constraint empfk_empno_pk primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint empfk_deptno_fk references dept_fk (deptno)
);
insert into emp_fk
values (9999, 'TEST_NAME', 'TEST_JOB', null, to_date('2001/01/01', 'YYYY/MM/DD'), 3000, null, 10);
-- Error : dept_fk는 존재하지 않는데 참조하려고 해서
insert into dept_fk
values (10, 'TEST_DNAME', 'TEST_LOC');
select * from dept_fk;
insert into emp_fk
values (9999, 'TEST_NAME', 'TEST_JOB', null, to_date('2001/01/01', 'YYYY/MM/DD'), 3000, null, 10);
select * from emp_fk;
delete dept_fk where deptno = 10; -- Error : emp_fk에서 참조를 하고 있으므로 지울 수가 없다.
drop table emp_fk;
create table emp_fk (
empno number(4) constraint empfk_empno_pk primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint empfk_deptno_fk
references dept_fk (deptno) on delete cascade
);
delete dept_fk where deptno = 10; -- 삭제 가능
-- check
create table table_check (
login_id varchar2(20) constraint tbck_loginid_pk primary key,
login_pwd varchar2(20) constraint tblck_loginpw_ck check (length(login_pwd) > 3),
tel varchar2(20)
);
desc table_check;
insert into table_check
values ('TEST_ID', '123', '010-1234-5678'); -- Error : check 조건 위반... 비밀번호 길이가 3 초과해야 함
insert into table_check
values ('TEST_ID', '1234', '010-1234-5678');
select * from table_check;
-- default
create table table_default (
login_id varchar2(20) constraint tblck2_loginid_pk primary key,
login_pwd varchar2(20) default '1234',
inputdate date default sysdate
);
insert into table_default (login_id)
values ('TEST_ID');
select * from table_default;
insert into table_default (login_id, login_pwd)
values ('TEST_ID2', '3333');
select * from table_default;-- 테이블 생성 예제1
-- 1. 회원정보 테이블
create table market_member (
memberid varchar2(20) primary key,
memberpw varchar2(100) unique,
membername varchar2(20) not null,
phone varchar2(20) not null,
enabled number(1) default 1 check (enabled in (0, 1)),
rolename varchar2(20) default 'ROLE_USER' check (rolename in ('ROLE_USER', 'ROLE_ADMIN')) -- 일반 사용자, 관리자
);
-- 2. 판매글 테이블
create table market_board (
boardnum number primary key,
memberid varchar2(20) not null,
title varchar2(200) not null,
contents varchar2(2000) not null,
inputdate date default sysdate,
category varchar2(50),
soldout char(1) default 'N',
buyerid varchar2(20) references market_member (memberid) on delete cascade
);
-- alter table market_board add foreign key(buyerid) references market_member (memberid) on delete cascade;
-- drop table market_board;
create sequence market_board_seq
increment by 1
start with 1
maxvalue 10
minvalue 0
nocycle
nocache;
insert into market_member (memberid, memberpw, membername, phone)
values ('abc123', '1234', '피카츄', '010-1234-5678');
select * from market_member;
insert into market_board (boardnum, memberid, title, contents)
values (market_board_seq.nextval, 'abc123', 'first', 'Hi');
select * from market_board;
-- 3. 리플 테이블
create table market_reply (
replynum number primary key,
boardnum number references market_board (boardnum) on delete cascade,
memberid varchar2(20) not null,
replytext varchar2(200),
inputdate date default sysdate
);
-- alter table market_reply add foreign key(boardnum) references market_board (boardnum) on delete cascade;
drop table market_reply;
create sequence market_reply_seq
increment by 1
start with 1
maxvalue 10
minvalue 0
nocycle
nocache;
insert into market_reply (replynum, memberid)
values (market_reply_seq.nextval, 'abc123');
select * from market_reply;
-- 테이블 생성 예제2
create table girl_group (
group_no number primary key,
group_name varchar2(30) unique,
debut_date date default sysdate,
agency varchar2(30) not null
);
create sequence girl_group_seq
increment by 1
start with 1
maxvalue 10
minvalue 0
nocycle
nocache;
create table group_members (
member_no number primary key,
member_name varchar2(30) not null,
group_no number references girl_group (group_no)
);
create sequence group_member_seq
increment by 1
start with 1
maxvalue 100
minvalue 0
nocycle
nocache;
drop sequence group_members_seq;
INSERT INTO GIRL_GROUP VALUES (GIRL_GROUP_SEQ.nextval, '트와이스', '2015/10/20', 'JYP');
INSERT INTO GIRL_GROUP VALUES (GIRL_GROUP_SEQ.nextval, '레드벨벳', '2014/08/01', 'SM');
INSERT INTO GIRL_GROUP VALUES (GIRL_GROUP_SEQ.nextval, '여자친구', '2015/01/15', 'Source');
INSERT INTO GIRL_GROUP VALUES (GIRL_GROUP_SEQ.nextval, '블랙핑크', '2016/08/08', 'YG');
INSERT INTO GIRL_GROUP VALUES (GIRL_GROUP_SEQ.nextval, '걸스데이', '2010/07/09', 'DreamT');
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '나연', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '정연', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '모모', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '사나', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '지효', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '미나', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '다현', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '채영', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '쯔위', 1);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '웬디', 2);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '아이린', 2);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '슬기', 2);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '조이', 2);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '예리', 2);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '소원', 3);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '예린', 3);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '은하', 3);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '유주', 3);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '신비', 3);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '엄지', 3);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '지수', 4);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '제니', 4);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '로제', 4);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '리사', 4);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '소진', 5);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '유라', 5);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '민아', 5);
INSERT INTO GROUP_MEMBERS VALUES (GROUP_MEMBER_SEQ.nextval, '혜리', 5);
- 걸그룹 예제 문항
-- 1. 가장 최근에 데뷔한 순으로 정렬하여 출력하시오.
-- (걸 그룹명, 데뷔날짜 (YYYY-MM-DD), 기획사)
select * from GIRL_GROUP;
select * from GROUP_MEMBERS;
select group_name, to_char(debut_date, 'YYYY-MM-DD'), agency
from girl_group
order by 2;
-- 2. 테이블에 등록된 다섯 걸그룹의 그룹명과 멤버 수를 그룹명 오름차순으로 정렬하여 출력하시오.
select g.group_name, count(m.group_no)
from girl_group g, group_members m
where g.group_no = m.group_no
group by g.group_name
order by g.group_name;
-- 3. 걸 그룹별 기획사와 멤버 전부를 출력하시오.
-- (걸그룹명, 기획사, 멤버명_listagg사용)
-- listagg : 하나의 컬럼에 데이터를 여러개 넣을 수 있음(행이 너무 많을 때 주로 사용)
-- listagg(나열할 열(필수), 데이터 구분자(선택))
-- within group(order by 정렬 기준 컬럼(선택))
select deptno,
listagg(ename, ', ')
within group(order by sal desc) as enames
from emp
group by deptno;
select g.group_name, g.agency,
listagg(m.member_name, ', ')
within group(order by g.group_name) as "멤버이름"
from girl_group g, group_members m
where g.group_no = m.group_no
group by g.group_name, g.agency;
-- 4. 걸그룹 멤버 수가 5명 이상인 그룹의 그룹명과 멤버 수를 출력하시오.
select g.group_name, count(m.group_no)
from girl_group g, group_members m
where g.group_no = m.group_no
group by g.group_name
having count(m.group_no) >= 5;-- 추가1.
-- 사원별로 급여(salary)에 급여*commission 비율을 더한 금액을 모든 사원에게 지급하려고한다.
-- 사번, 부서이름, 급여, 커미션 비율(commission_pct), 지급금액을 사번순으로 출력하라. employees, departments 참조
select * from employees;
select * from departments;
select employee_id, department_name, salary, commission_pct,salary+(salary*commission_pct)
from employees e, departments d
where e.department_id = d.department_id and commission_pct is not null
order by 3 desc;
-- 추가2.
-- 모든 사원의 job 이력을 알고 싶다.
-- 사번, full_name, job이력의 횟수
-- job이력이 한번도 없으면 'zero', 한번있으면 'one', 두번있으면 'two', 나머지는 'many'라고 등급을 정하여 출력하라. employees, job_history 참조
select * from employees;
select * from job_history order by employee_id;
select e.employee_id, first_name || ' ' || last_name as FULL_NAME, count(j.start_date) as "job 이력",
decode(count(j.start_date),
0, 'zero',
1, 'one',
2, 'two',
'many') as "등급"
from job_history j, employees e
where e.employee_id = j.employee_id (+)
group by e.employee_id, e.first_name, e.last_name, first_name || ' ' || last_name
order by 3 desc;
-- 선생님 답
select
emp.employee_id, first_name || ' ' || last_name as FULL_NAME, count(start_date) as "job 이력의 횟수",
decode(count(start_date),
0, 'zero',
1, 'one',
2, 'two',
'many') as "등급"
from employees emp left join job_history jh on emp.employee_id = jh.employee_id
group by emp.employee_id, emp.first_name, emp.last_name
order by "등급" asc;
재밌당