-- 테이블 생성
-- 1. hr 스키마의 departments의 컬럼을 이름(DEPTNO, DEPTNAME, MGR, LOC) 바꿔서 복제하기
drop table hr_tb;
create table hr_tb(deptno, deptname, mgr, loc)
as
select * from DEPARTMENTS
where 1 <> 1;
select * from hr_tb;
-- 데이터 입력
-- 1. 컬럼명 순서와 입력 데이터의 순서를 맞춘다.
insert into hr_tb(deptno, deptname, mgr, loc)
values(10, '기획부', 100, 120);
-- 2. 열에서 NULLABLE이 YES인 컬럼 빈칸허용
-- 열에서 NULLABLE이 NO인 컬럼 빈칸불가
desc hr_tb;
insert into hr_tb(deptno, deptname)
values(20, '관리부');
select * from hr_tb;
-- 3. 컬럼의 위치를 바꿔서 데이터 입력
insert into hr_tb(mgr, deptno, deptname, loc)
values(120, 30, '개발부', 300);
-- 4. 컬럼명을 없이 데이터 입력
insert into hr_tb
values(40, '경리부', 200, 250);
select * from hr_tb;
-- 데이터 삭제
-- 1. '관리부' 행삭제
delete from hr_tb
where deptname = '관리부';
select * from hr_tb;
-- 2. MGR 비워있을 경우 모두삭제
delete from hr_tb
where mgr IS NULL;
select * from hr_tb;
-- 데이터 수정
-- 1. deptno = 30이면 MGR =300 바꿔라
update hr_tb
set mgr = 300
where deptno = 30;
select * from hr_tb;
-- 2. MGR = 500, loc = 222 컬럼 바꾸기 (개발부를 수정)
update hr_tb
set mgr = 500, loc = 222
where deptname = '개발부';
select * from hr_tb;
-- 1. hr 스키마에서 EMPLOYEES 테이블에서 20번 부서의 세부 사항을 포함하는 EMP_20 VIEW를 생성 하라
select * from employees;
select * from departments;
create or replace view emp_20
as
select *
from employees
where department_id = 20;
select * from emp_20;
-- 2. EMPLOYEES 테이블에서 30번 부서만 EMPLOYEE_ID 를 emp_no 로 LAST_NAME을 name으로 SALARY를 sal로 바꾸어 EMP_30 VIEW를 생성하라.
create or replace view emp_30
as
select employee_id emp_no, last_name name, salary sal
from employees
where department_id = 30;
select * from emp_30;
-- 3. 부서별로 부서명,최소 급여,최대 급여,부서의 평균 급여를 포함하는 DEPT_SUM VIEW을 생성하여라.
create or replace view dept_sum(부서명, 최소급여, 최대급여, 평균급여)
as
select department_name, min(salary), max(salary), round(avg(salary))
from employees e, departments d
where e.department_id = d.department_id
group by department_name;
select * from dept_sum;
-- 4. 앞에서 생성한 EMP_20,EMP_30 VIEW을 삭제하여라.
drop view emp_20;
drop view emp_30;
-- 1. 도서 쇼핑몰에서 사용될 Database의 테이블을 구현하라.
-- 도서테이블, 고객테이블, 주문테이블을 작성한다.
-- 테이블과의 관계를 연결하여 사용시 용이하도록 한다.
-- 1-1. 도서테이블(BOOK)에 도서번호, 도서이름, 출판사이름, 도서단가 컬럼을 추가하도록 한다
drop table book;
create table book(
bookno number(10) constraint pk_book primary key,
bookname varchar2(100) not null,
bookpub varchar2(50),
bookprice number(10)not null
);
select * from book;
-- 1-2. 고객테이블(CONSUMER)에 고객번호(시퀀스사용), 고객이름, 주소, 전화번호 컬럼을 추가하도록 한다
drop table consumer;
create table consumer(
consumerno number(10) constraint pk_consumer primary key,
consumername varchar2(50) not null,
consumeraddress varchar2(100) not null,
consumerphone number(15) not null
);
select * from consumer;
-- 1-3. 고객번호(CONSUMER_NO) 시퀀스 생성
drop sequence consumerno;
create sequence consumerno
increment by 1
start with 1
maxvalue 99999
minvalue 1;
-- 1-4. 주문테이블(ORDERS)에 주문번호(시퀀스사용), 고객번호, 도서번호, 주문일자, 주문금액을 추가한다.
drop table orders;
create table orders(
orderno number(10) not null,
consumerno number,
bookno number,
orderday date not null,
orderprice number not null,
constraint fk_orders_consumer foreign key (consumerno)
references consumer(consumerno),
constraint fk_orders_book foreign key (bookno)
references book(bookno)
);
select * from orders;
-- 1-5. 주문번호(ORDER_NO) 시퀀스 생성
drop sequence orderno;
create sequence orderno
increment by 1
start with 1
maxvalue 99999
minvalue 1;
-- 1-6. 도서상품을 3가지 등록한다. (임의 데이터 입력)
insert into book values (1, '하루', '위키', 24000);
insert into book values (2, 'DB', '도우', 30000);
insert into book values (3, 'JAVA', 'ehan', 35000);
select * from book;
-- 1-7. 고객은 2명 등록한다. (임의 데이터 입력)
insert into consumer values (consumerno.nextval, '일고객', '서울', '010-1111-1111');
insert into consumer values (consumerno.nextval, '이고객', '경기', '010-2222-2222');
select * from consumer;
-- 1-8. 주문에 2건이상 등록한다. (임의 데이터 입력)
insert into orders values (orderno.nextval, 1, 3, sysdate, 32000);
insert into orders values (orderno.nextval, 2, 1, sysdate, 24000);
select * from orders;
