Or10DML

/********

# 파일명 : Or10DML.sql

# DML :  Data Maripulation Language

# 설명 : 데이터를 조작할 때 사용하는 쿼리문, select를 포함하여

update(데이터수정, delete(데이터삭제, insert(데이터입력) 가 있다.

********/

/*

[데이터조회 : select문]

--데이터를 조회하기 위한 구문으로 가장 사용빈도가 높은 쿼리문이다.

사용법 :

    select 컬렴1, 컬럼명2 ....

    from 테이블명

    where 조건1, and 조건2 or....

    group by 그룹화할 컬럼명

    having 그룹의 조건

    order by 정렬을 하기위한 컬럼 asc(desc);

*/

/*

[데이터입력 : insert문]

-데이터 입력을 위한 구문으로 문자형을 입력시에는 반드시 '(싱글쿼테이션)으로

감싸야한다. 숫자형은 '(싱글)이 필요없다.

사용법 1 : 컬럼을 지정하는 방법

    insert into 테이블명 (컬럼1, 컬럼2 .....컬럼N)

    values (값1, 값2..... 값N);

사용법 2: 컬럼을 지정하지 않고 전체컬럼을 대상으로 하는 방법

    

    insert into 테이블명 values (값1, 값2......테이블의 ㅊ컬럼수와 동일하게);

*/

--테이블 생성하기

create table tb_sample (

    deptNo number(10),  -- 부서번호

    deptName varchar2(20), -- 부서명

    deptLoc varchar2(15), -- 지역

    deptManager varchar2(30)  --매니져이름

);

--방식1을 통한 데이터 입력

insert into tb_sample (deptNo, deptname, deptLoc, deptManager)

values (10, '기획실', '서울', '이순신');

--방식2를 통한 데이터 입력

insert into tb_sample values (20, '전산실', '수원', '김유신');

insert into tb_sample values (30, '기획팀', '대전', '홍길동');

insert into tb_sample values (40, '영업팀', '대구', '세종대왕');

insert into tb_sample values (50, '인사팀', '부산', '최길동');

select * from tb_sample;

commit; /* 형재 상태를 유지하겠다는 명령 

커밋을 수행하지 않으면 외부에서는 변경된 데이터를 확인할 수 없다.

*/

/*

[데이터 수정 : update문]

사용법 :

    update 테이블명

    set 칼럼 1 =값, 칼럼2 =값2 .....칼럼N=값N

    where 조건1 and 조건2;

*/

select * from tb_sample;

update tb_sample set deptLoc = '광주' where deptNo = 50;

select * from tb_sample;

select * from tb_sample where deptName = '기획실';

update tb_sample set deptManager = '문재인', deptLoc='강원'

where deptName='기획실';

select * from tb_sample;

update tb_sample set deptName='', deptLoc ='', deptManager=''

where deptNo=20;

select * from tb_sample;

/*

데이터 삭제하기 : delete문

    사용법 : delete from 테이블명 where 조건;

*/

delete from tb_sample where deptNo=10; --부서번호 10인 레코드 삭제

delete from tb_sample; --전체 레코드 삭제

select * from tb_sample;

rollback;

/* 마지막 commit 이후의 상태로 되돌려준다. 즉 commit하면

그 이전 상태로는 rollback되지 않는다.*/

/*

칼럼이 number타입이면 컬럼명 +100과 같이 사칙연산을 수행할 수 없다.

select, update 동일하게 적용됨.

*/

--deptNo에 20을 더하여 업데이트 수행

update tb_sample set deptNo= deptNo + 20 where deptNo = 50;

select * from tb_sample;

/*

1. 다음 조건에 맞는 'pt_dept' 테이블을 생성하시오.

*/

create table pt_dept(

    dno number(2),

    dname varchar2(20),

    loc varchar2(35)

);

/*

2. 다음 조건에 맞는 'pt_emp' 테이블을 생성하시오.

*/

create table pr_emp

(

    eno number(4),

    ename varchar2(10),

    job varchar2(30),

    regist_date date

);

/*

3. pr_emp 테이블의 ename 컬럼을 varchar2(50) 로 수정하시오.

*/

--테이블 스키마 확인

desc pr_emp;

alter table pr_emp modify ename varchar2(50);

/*

4. employees 테이블을 복사해서 pr_employees라는 테이블을 생성하되 

사원ID, 이름, 급여, 부서ID 만 복사하고 새로 생성된 칼럼명은 

emp_id, name, sal, dept_id로 지정하시오.

*/

create table pr_employees (

    emp_id, name, sal, dept_id

)

as

select employee_id, first_name ||' '||last_name, salary, department_id

from employees

where 1=1

;

/*

5. 수업시간에 복사했던  tb_emp_cp 테이블을 삭제하시오

*/

drop table tb_emp_cp;

/*

6. pr_employees 테이블의 이름을 pr_employees_rename 으로 변경하시오.

*/

rename pr_employees to pr_employees_rename;

desc pr_employees;

desc pr_employees_rename;

/*

7. pr_dept 테이블에서 dname 칼럼을 삭제하시오.

*/

alter table pr_dept drop column dname;

/*

8. 5번에서 삭제한 tb_member_rename 테이블이 휴지통에 있는지

확인하고 복원할수 없도록 휴지통에서 완전히 삭제하시오.

*/

show recyclebin;

purge table pr_empoyees;

/*

9. “pr_emp” 테이블의 job 컬럼을 varchar2(50) 으로 수정하시오.

*/

alter table pr_emp modify job varchar2(50);

desc pr_emp;

/*

1. DDL문 연습문제 2번에서 만든 “pr_emp” 테이블에

다음과 같이 레코드를 삽입하시오.

단, 날자는 sysdate를 이용해서 오늘 날자를 입력하시오.

*/

insert into pr_emp values (1, '문재인', '대통령', sysdate);

insert into pr_emp values (2, '이낙연', '국무총리', sysdate);

insert into pr_emp values (3, '김상조', '공정거래위원장', sysdate);

select * from pr_emp;

/*

2. 위 테이블에 다음 조건에 맞는 레코드를 삽입하시오.

이름 : 서훈

직책 : 국정원장

등록날자 : to_date함수를 이용해서 7일전 날자를 입력하시오.

*/

insert into pr_emp (

    eno, ename, job, regist_date

)

values (4, '서훈', '국정원장', to_date(sysdate-7, 'yy/mm/dd'));

select * from pr_emp;

/*

3. 등록일이 오늘인 레코드를 찾아서 ename 의 내용을 다음과 같이 변경하시오.

“문재인 공정한 대한민국” 즉 이름뒤에 “공정한 대한민국” 이라는 문자열을 추가한다.

*/

select

    *

from pr_emp

where to_char(regist_date, 'yy/mm/dd') = '17/11/08';

desc pr_emp;

update pr_emp

set ename = concat(ename, ' 공정한 대한민국')

where to_char(regist_date, 'yy/mm/dd') = '17/11/08';

select * from pr_emp;

/*

4. “김상조”의 등록일자가 “서훈” 의 등록일자와 같아지도록

업데이트 하시오. 

즉 “김상조”의 등록일자가 “서훈”과 같이 7일전 날자가 되어야 하며 

to_date 함수를 사용하는 것이 아니라 “서훈”의 등록일자를 이용해서 

업데이트 하는것이다.

*/

update pr_emp

set regist_date =

    (

        select regist_date from pr_emp

        where ename='서훈'

    )

where eno=3;

select * from pr_emp;

/*

5. pr_employees 테이블에서 

사원ID 146번과 동일한 직무를 가진 사원의 부서ID를

사원ID 197번의 부서ID로 업데이트 하시오.

*/

create table pr_employees

as

select * from employees;

-- 146번 사원의 직무ID를 확인한다.

select * from pr_employees

where employee_id=146; --job_id : SA_MAN

-- 197번 사원의 부서ID를 확인한다.

select department_id from pr_employees

where employee_id=197; -- depantment_id : 50

--정답

update pr_employees

set department_id = 

(

    select department_id from pr_employees

    where employee_id=197

)

where job_id = 

        (   

            select job_id from pr_employees

            where employee_id=146

        );

select * from pr_employees;

/*

6.  pr_employees 테이블에서 전화번호에 123 이 포함된 모든 직원을 삭제하시오.

*/

select * from pr_employees

where phone_number like '%123%';

delete from pr_employees

where phone_number like '%123%';

/*

7. pr_employees 테이블에서 입사일자가 가장 늦은 직원을 찾아서 

first_name 컬럼을 “기존의이름(신입사원)” 이 될수 있도록 업데이트 하시오. 

예) Sundita(신입사원)

*/

desc pr_employees;

--입사일의 최대값을 찾는데 (가장 입사일이 늦은 날짜)

select max(hire_date) from pr_employees;

--입사일이 가장 늦은 직원의 정보 가져오기

select * from pr_employees where hire_date = 

            (

                select max(hire_date) from pr_employees            

            );

update pr_employees

set

    first_name = concat(first_name, '(신입사원)')

where hire_date = 

            (

                select max(hire_date) from pr_employees            

            );

        --first_name 컬럼이 크기가 부족함

-first_name 컬럼의 크기를 50으로 수정후 위 쿼리문 실행

alter table pr_employees modify first_name varchar2(50);

        

desc pr_employees;

select * from pr_employees;