1.오전은 과제복습 select마무리
--insert 문
--테이블 구조 확인
desc departments;
--1st 안되는데?..
insert into departments(department_id,department_name,manager_id,location_id)
values(290, '개발자',200,1700); --1행에 삽입됨
insert into departments(department_id, department_name, location_id) values(300,'디자이너',1700); --이건 2행에삽입
insert into departments(department_id, department_name,location_id) values(400,'안녕',1222); -- 이건 3행에삽입 <,따라서 기존테이블에 있는정보제외하고 삽입한순서대로 레코드가 추가됨nsert 문
--테이블 구조 확인
desc departments;
--1st 안되는데?..
insert into departments(department_id,department_name,manager_id,location_id)
values(290, '개발자',200,1700); --1행에 삽입됨
insert into departments(department_id, department_name, location_id) values(300,'디자이너',1700); --이건 2행에삽입
insert into departments(department_id, department_name,location_id) values(400,'안녕',1222); -- 이건 3행에삽입 <,따라서 기존테이블에 있는정보제외하고 삽입한순서대로 레코드가 추가됨
--2st
insert into departments values(330, '퍼블리셔', 200,1700);
select * from departments;
--DML문장은 트랜잭션을 통해서 DML이전으로 되돌릴수 있습니다.
ROLLBACK;
SELECT * FROM DEPARTMENTS;
--테이블 구조 복사
CREATE TABLE MANAGERS AS (SELECT * FROM EMPLOYEES WHERE 1 = 2); -- where 1=1은 다복사 1=2는 데이터뺴고 속성만복사 <<<<여기서 as는 별칭아님 진짜 create as 구조임lect마무리
--insert 문
--테이블 구조 확인
desc departments;
--1st 안되는데?..
insert into departments(department_id,department_name,manager_id,location_id)
values(290, '개발자',200,1700); --1행에 삽입됨
insert into departments(department_id, department_name, location_id) values(300,'디자이너',1700); --이건 2행에삽입
insert into departments(department_id, department_name,location_id) values(400,'안녕',1222); -- 이건 3행에삽입 <,따라서 기존테이블에 있는정보제외하고 삽입한순서대로 레코드가 추가됨nsert 문
--테이블 구조 확인
desc departments;
--1st 안되는데?..
insert into departments(department_id,department_name,manager_id,location_id)
values(290, '개발자',200,1700); --1행에 삽입됨
insert into departments(department_id, department_name, location_id) values(300,'디자이너',1700); --이건 2행에삽입
insert into departments(department_id, department_name,location_id) values(400,'안녕',1222); -- 이건 3행에삽입 <,따라서 기존테이블에 있는정보제외하고 삽입한순서대로 레코드가 추가됨
--2st
insert into departments values(330, '퍼블리셔', 200,1700);
select * from departments;
--DML문장은 트랜잭션을 통해서 DML이전으로 되돌릴수 있습니다.
ROLLBACK;
SELECT * FROM DEPARTMENTS;
--테이블 구조 복사
CREATE TABLE MANAGERS AS (SELECT * FROM EMPLOYEES WHERE 1 = 2); -- where 1=1은 다복사 1=2는 데이터뺴고 속성만복사 <<<<여기서 as는 별칭아님 진짜 create as 구조임
SELECT * FROM MANAGERS; -- 현재 필드만 복사완료 (구조복사완료라 봐도되는갓?)
--3ND(다른테이블의 특정 행ㅡ 서브쿼리절 INSERT) --위의 insert는 테이블(속성) valaue(값)이지만 insert 테이블 (서브쿼리) << 서브쿼리 사용할때는 value도없고 과정도다름
INSERT INTO MANAGERS (SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG');
INSERT INTO MANAGERS (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)
( SELECT
EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = 'FI_ACCOUNT');
SELECT *
FROM MANAGERS;
SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';
2. insert
롤백있음
3.UPDAET
* 하기전에 업데이트할 내용이 맞는지 한번더확인하는 습관을 가지라고하심
--아몰라 그냥 다 붙여넣음 복습하삼
--insert 문
--테이블 구조 확인
desc departments;
--1st 안되는데?..
insert into departments(department_id,department_name,manager_id,location_id)
values(290, '개발자',200,1700);
insert into departments(department_id, department_name, location_id) values(300,'디자이너',1700);
insert into departments(department_id, department_name,location_id) values(400,'안녕',1222);
--2st
insert into departments values(330, '퍼블리셔', 200,1700);
select * from departments;
--DML문장은 트랜잭션을 통해서 DML이전으로 되돌릴수 있습니다.
ROLLBACK;
SELECT * FROM DEPARTMENTS;
--테이블 구조 복사
CREATE TABLE MANAGERS AS (SELECT * FROM EMPLOYEES WHERE 1 = 2);
SELECT * FROM MANAGERS; -- 현재 필드만 복사완료 (구조복사완료라 봐도되는갓?)
--3ND(다른테이블의 특정 행ㅡ 서브쿼리절 INSERT)
INSERT INTO MANAGERS (SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG');
INSERT INTO MANAGERS (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)
( SELECT
EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = 'FI_ACCOUNT');
SELECT *
FROM MANAGERS;
SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';
--update문
desc employees;
--사본테이블 생성(데이터까지 복사)
create table emps as (select * from employees where 1 = 1); --1 = 1은 다복사한다는뜻임
select * from emps;
update emps set salary = 30000;
rollback;
--update문은 조건절을 반드시 명시 합니다.
select * from emps where employee_id = 100;
update emps set salary = salary * 1.1 where employee_id = 100;
UPDATE emps set phone_number = '511.123.1111',
hire_date = sysdate,
commission_pct = 0.1
where employee_id = 100; << 한번에 여러개 업데이트도 가능
-- JOB_ID 가 IT_PROG인 사람의 커미션 0.1로 업데이트
UPDATE emps set commission_pct = 0.1
where job_id = 'IT_PROG';
--where 절에 서브쿼리
update emps set commission_pct = 0.2
where department_id = (select department_id from emps where first_name = 'Donald');
select * from emps where department_id = (select department_id from emps where first_name = 'Donald');
select * from emps where employee_id = 100;
--set 절에 서브쿼리
--update emps set (컬럼대상) = (서브쿼리) where 조건
select job_id, salary, commission_pct, manager_id,department_id from employees where employee_id = 103;
update emps set(job_id, salary, commission_pct, manager_id, department_id) =
(select job_id, salary, commission_pct, manager_id,department_id from employees where employee_id = 103)
where employee_id = 102;
select * from employees where employee_id = 103 union all
select * from emps where employee_id = 102;
rollback;
--delete
select * from departments;
delete from departments where department_id = 50; -- (employees 테이블에서 50번 부서를 참조하여 사용중이기 때문에 삭제이상) 참조 무결성 제약조건 위배
select * from employees where department_id = 50;
select * from emps where employee_id = 105;
delete from emps where employee_id = 105;
select * from emps;
--where절의 서브쿼리
select department_id from departments where department_name = 'Shipping';
delete from emps where department_id = (select department_id from departments where department_name = 'Shipping'); << 걍별거없음 진짜 조건대신 서브쿼리 넣은거임
select * from emps;
ROLLBACK;
--2st
insert into departments values(330, '퍼블리셔', 200,1700);
select * from departments;
--DML문장은 트랜잭션을 통해서 DML이전으로 되돌릴수 있습니다.
ROLLBACK;
SELECT * FROM DEPARTMENTS;
--테이블 구조 복사
CREATE TABLE MANAGERS AS (SELECT * FROM EMPLOYEES WHERE 1 = 2);
SELECT * FROM MANAGERS; -- 현재 필드만 복사완료 (구조복사완료라 봐도되는갓?)
--3ND(다른테이블의 특정 행ㅡ 서브쿼리절 INSERT)
INSERT INTO MANAGERS (SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG');
INSERT INTO MANAGERS (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)
( SELECT
EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = 'FI_ACCOUNT');
SELECT *
FROM MANAGERS;
SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';
2. insert
롤백있음
3.UPDAET
* 하기전에 업데이트할 내용이 맞는지 한번더확인하는 습관을 가지라고하심
--아몰라 그냥 다 붙여넣음 복습하삼
--insert 문
--테이블 구조 확인
desc departments;
--1st 안되는데?..
insert into departments(department_id,department_name,manager_id,location_id)
values(290, '개발자',200,1700);
insert into departments(department_id, department_name, location_id) values(300,'디자이너',1700);
insert into departments(department_id, department_name,location_id) values(400,'안녕',1222);
--2st
insert into departments values(330, '퍼블리셔', 200,1700);
select * from departments;
--DML문장은 트랜잭션을 통해서 DML이전으로 되돌릴수 있습니다.
ROLLBACK;
SELECT * FROM DEPARTMENTS;
--테이블 구조 복사
CREATE TABLE MANAGERS AS (SELECT * FROM EMPLOYEES WHERE 1 = 2);
SELECT * FROM MANAGERS; -- 현재 필드만 복사완료 (구조복사완료라 봐도되는갓?)
--3ND(다른테이블의 특정 행ㅡ 서브쿼리절 INSERT)
INSERT INTO MANAGERS (SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG');
INSERT INTO MANAGERS (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)
( SELECT
EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = 'FI_ACCOUNT');
SELECT *
FROM MANAGERS;
SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';
--update문
desc employees;
--사본테이블 생성(데이터까지 복사)
create table emps as (select * from employees where 1 = 1); --1 = 1은 다복사한다는뜻임
select * from emps;
update emps set salary = 30000;
rollback;
--update문은 조건절을 반드시 명시 합니다.
select * from emps where employee_id = 100;
update emps set salary = salary * 1.1 where employee_id = 100;
UPDATE emps set phone_number = '511.123.1111',
hire_date = sysdate,
commission_pct = 0.1
where employee_id = 100;
-- JOB_ID 가 IT_PROG인 사람의 커미션 0.1로 업데이트
UPDATE emps set commission_pct = 0.1
where job_id = 'IT_PROG';
--where 절에 서브쿼리
update emps set commission_pct = 0.2
where department_id = (select department_id from emps where first_name = 'Donald');
select * from emps where department_id = (select department_id from emps where first_name = 'Donald');
select * from emps where employee_id = 100;
--set 절에 서브쿼리
--update emps set (컬럼대상) = (서브쿼리) where 조건
select job_id, salary, commission_pct, manager_id,department_id from employees where employee_id = 103;
update emps set(job_id, salary, commission_pct, manager_id, department_id) =
(select job_id, salary, commission_pct, manager_id,department_id from employees where employee_id = 103)
where employee_id = 102;
select * from employees where employee_id = 103 union all
select * from emps where employee_id = 102;
rollback;
--delete
select * from departments;
delete from departments where department_id = 50; -- (employees 테이블에서 50번 부서를 참조하여 사용중이기 때문에 삭제이상) 참조 무결성 제약조건 위배
select * from employees where department_id = 50;
select * from emps where employee_id = 105;
delete from emps where employee_id = 105;
select * from emps;
--where절의 서브쿼리
select department_id from departments where department_name = 'Shipping';
delete from emps where department_id = (select department_id from departments where department_name = 'Shipping');
select * from emps;
ROLLBACK;
