레이커 갓생일기

기본 개념

트리거와 프로시저의 차이점은 무엇인지 서술하시오

트리거는 이벤트(주로 DML sql문) 발생시 자동으로 실행되지만 프로시저는 명시적으로 호출을 해줘야만 실행이 된다.

PL/SQL

1~10 사이의 정수 중에서 짝수의 합을 구하는 PL/SQL 프로그램을 작성 하시오

SET SERVEROUTPUT ON
declare
    n number := 1; -- 루프를 돌릴 변수
    s number := 0; -- 합이 누적될 변수
begin
    loop
        if n > 10 then
            exit;
        end if;
                
        if mod(n, 2) = 1 then -- 홀수일 때는
            s := s + 0;       -- 누적 안시킴
        else                  -- 짝수일 때는
            s := s + n;       -- 누적 시킴
        end if;

        n := n + 1; -- 1씩 증가
        
    end loop;
    DBMS_OUTPUT.put_line('1~10 사이의 정수 중에서 짝수의 합 : ' || s);
end;

프로시저 생성, 실행

사원 테이블(EMP)에서 사원번호(EMPNO)를 매개변수로 받아서, 사원 정보를 삭제하는 프로시저를 생성하고, 프로시저를 실행하시오

-- 저장 프로시저 생성
create or replace procedure del_emp (vempno in emp.empno%type)
is
begin
    delete from emp where empno = vempno;
end;

-- 프로시저 실행
execute del_emp(7788);

프로시저 생성, 실행

사원테이블(EMP)에서 SCOTT 사원의 사원명을 프로시저의 매개변수로 전달해서 SCOTT 사원의 급여를 출력하는 프로시저를 생성하고 실행하시오
1) 프로시저명은 emp_sal 이름으로 생성한다.
2) 생성된 프로시저를 실행할때 매개변수에 SCOTT 사원을 전달해서 실행한다.

-- 프로시저 생성
create or replace procedure emp_sal (
    vename in emp.ename%type,
    vsal out emp.sal%type)
is
begin
    select sal into vsal from emp where ename = vename;
end;

-- 바인드 변수 생성
variable var_sal number;

-- 프로시저 실행
execute emp_sal('SCOTT', :var_sal);

-- 출력
print var_sal;

프로시저 생성, 실행

사원 테이블(EMP)에서 사원번호를 프로시저의 매개변수로 전달 받아서, 그 사원의 사원명, 급여, 부서번호를 구하는 프로시저 생성해서 실행하시오?
1) 프로시저명은 emp_info 이름으로 생성한다.
2) 생성된 프로시저를 실행할때 매개변수에 사원들의 사원번호를 전달해서 실행한다.
3) 프로시저를 실행할때 바인드변수를 만들어서 출력한다.

-- 프로시저 생성
create or replace procedure emp_info (
    vempno in emp.empno%type,
    vename out emp.ename%type,
    vsal out emp.sal%type,
    vdeptno out emp.deptno%type )
is
begin
    select ename, sal, deptno into vename, vsal, vdeptno from emp where empno = vempno;
end;

-- 바인드 변수 생성
variable var_ename varchar2(10);
variable var_sal number;
variable var_deptno number;

-- 프로시저 실행
execute emp_info(7788, :var_ename, :var_sal, :var_deptno);

-- 출력
print var_ename;
print var_sal;
print var_deptno;

점수

100 / 100

서브쿼리 문제

사원 테이블(EMP)에서 SCOTT의 급여보다 적게 받는 사원의 이름, 급여를 출력하는 SQL문을 작성하시오

select ename, sal from emp where sal < (select sal from emp where ename = 'SCOTT');

그룹화, 그룹함수

사원 테이블(EMP)에서 각 부서별 평균 급여를 구하는 SQL문을 작성하시오

select avg(sal) from emp group by deptno;

감점 -5 점, 어느 부서의 평균인지 모르기때문에 5점이 감점됨 

답 > select deptno, avg(sal) from emp group by deptno;

Like 연산자, 와일드카드

사원테이블(EMP)에서 사원명에 A가 포함된 사원을 검색하는 SQL문을 작성하시오

select * from emp where ename like '%A%';

서브쿼리 인라인뷰, rownum

사원테이블(EMP)에서 급여를 많이 받는 사원 5명을 구하는 SQL문을 인라인뷰로 작성하시오

select rownum, ename, sal from (
    select * from emp order by sal desc)
where rownum <= 5;

substr

사원테이블(EMP)에서 82년도에 입사한 모든 사원의 정보를 출력하는 SQL문을 작성하시오

select * from emp where substr(hiredate, 1, 2) = '82';

nvl 함수, 별칭

사원테이블(EMP)에서 사번, 이름, 급여, 연봉을 조회하는 SQL문장을 작성하시오

단, 연봉은 별칭을 사용하고, comm이 null값인 경우에는 0으로 연산 처리하시오.

select empno, ename, sal, sal * 12 + nvl(comm, 0) 연봉 from emp;

새로운 계정 생성, 권한 부여
아래의 문제에서 요구하는 SQL문을 각각 작성하시오
1) 아래의 정보로 전산실에 입사한 신입사원에게 새로운 계정을 생성하시오.
    계정명 : myuser ,  비밀번호 : tiger
2) 생성한 계정에게 데이터 접속 및 테이블, 뷰를 생성할 수 있는 권한을 롤(role)을 이용하여 부여하시오.

--1) 아래의 정보로 전산실에 입사한 신입사원에게 새로운 계정을 생성하시오.
--    계정명 : myuser ,  비밀번호 : tiger

create user myuser identified by tiger;

--2) 생성한 계정에게 데이터 접속 및 테이블, 뷰를 생성할 수 있는 권한을 롤(role)을 이용하여 부여하시오.

grant connect, resource to myuser;

테이블 관계, foreign key

아래의 명세서에 따라 제약조건을 만족하는 테이블을 각각 작성하시오

create table Book (
    bookid number(2) primary key,
    bookname varchar2(40),
    publisher varchar2(40),
    price number(8) );

create table Customer (
    custid number(2) primary key,
    name varchar2(40),
    address varchar2(50),
    phone varchar2(20) );
    
create table Orders (
    orderid number(2) primary key,
    custid number(2) references Customer(custid),
    bookid number(2) references Book(bookid),
    saleprice number(8),
    orderdate date );

점수

95 / 100

조건에 맞는 테이블 생성 (제약조건)

아래와 같은 조회결과가 나오도록 주어진 조건에 맞게 book 테이블을 생성하시오

조건1 : no 컬럼은 반드시, 중복되지 않은 값이 들어가도록 설정한다.
조건2 : title 컬럼에는 반드시 값이 들어가도록 설정한다.
조건3 : pub_day 컬럼에 값을 넣지 않으면 기본 값으로 오늘 날짜가 들어가도록 설정 한다.

create table book (
    no number primary key,
    title varchar2(12) not null,
    author varchar2(12),
    publisher varchar2(12),
    price number,
    pub_day date default sysdate);

포맷에 맞는 날짜 출력 (형변환)

Oracle 데이터베이스를 통한 현재 날짜를 기본형식(22/01/01)과

사용자 형식(2022-01-01)으로 차례로 출력하는 SQL문을 작성하시오

사용자 형식은 연도를 4자리로 출력 하도록 한다.

select sysdate 기본형식, to_char(sysdate, 'YYYY-MM-DD') 사용자형식 from dual;

그룹함수, 서브쿼리 문제

사원테이블(EMP)에서 최대 급여를 받는 사원명과, 최대급여 금액을 출력하는 SQL문을 작성하시오

select ename, sal from emp where sal = (select max(sal) from emp);

mysql의 auto_increment 와 insert문을 oracle 코드로 옮기기 (sequence)

MySQL을 Oracle로 DB Migration을 하고자 한다. 마이그레이션 한 결과가 같아지도록 아래 내용을 완성하시오

1. Oracle sequence 생성
2. insert문 작성하기

create table member (
    id NUMBER(6) primary key,
    name VARCHAR2(20)
);

-- 1. Oracle sequence 생성
create sequence member_id_seq;
-- 2. insert문 작성하기
insert into member values(member_id_seq.nextval, 'hong');

테이블 레벨 방식으로 제약조건 설정 (복합키)

다음은 테이블을 만들 때 name, phone 컬럼을 기본키로 만들려고 한다.

composite key(복합키) 이름(Constraint)은 member_compo_pk이며, 아래 테이블을 완성하시오

create table member1(
    name varchar2(10),
    address varchar2(30),
    phone varchar2(16),
    constraint member_compo_pk primary key (name, phone) );

점수

100 / 100

과제
2022.08.01/ Hw1

사원 테이블에서 사원명을 검색하여 사원의 직급을 구해오는 

저장 프로시저를 만들어서 실행하세요

의도

PL/SQL

저장 프로시저 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220801/2022_08_01_hw1.sql

코드

-- Q1. 사원 테이블에서 사원명을 검색하여 사원의 직급을 구해오는 저장 프로시저를 만들어서 실행하세요

-- 프로시저 생성
create or replace procedure job_ename (
    vename in emp.ename%type,
    vjob out emp.job%type)
is
begin
    select job into vjob from emp
    where ename = vename;
end;

-- 바인드 함수 생성
variable var_job varchar2(10);

-- 프로시저 실행
execute job_ename('SCOTT', :var_job);

-- 출력
print var_job;

출력

강사님 코드

-- 사원 테이블에서 사원명을 검색하여 사원의 직급을 구해오는 저장 프로시저를 만들어서 실행하세요?

--1. 저장 프로시저 생성
create or replace procedure ename_job(
        vename in emp.ename%type,
        vjob out emp.job%type)
is
begin
    select job into vjob from emp where ename = vename;
end;

--2. 프로시저 목록 확인
select * from  user_source;

--3. 바인드 변수 생성
variable var_job varchar2(10);

--4. 프로시저 실행
execute ename_job('SCOTT', :var_job); 
execute ename_job('KING', :var_job); 
execute ename_job('SMITH', :var_job); 

--5. 바인드 변수로 받은 값 출력
print var_job;

과제
2022.07.25 / Hw3

 직급이 'SALESMAN'인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원들의 

이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다.(ANY연산자 이용)

의도

단일행 서브 쿼리 사용

다중행 서브 쿼리 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220725/2022_07_25_hw3.sql

코드

-- Q3. 직급이 'SALESMAN'인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원들의 이름과 급여를 출력하되 부서번호가 
--	20번인 사원은 제외한다.(ANY연산자 이용)

-- 1) 단일행 서브쿼리로 풀기
select ename, sal from emp where sal > (select min(sal) from emp where job = 'SALESMAN') and deptno <> 20;
-- 2) 다중행 서브쿼리로 풀기
select ename, sal from emp where sal > any (select sal from emp where job = 'SALESMAN') and deptno <> 20;

출력

강사님 코드

-- Q3. 직급이 'SALESMAN'인 사원이 받는 급여들의 최소 급여보다 
-- 많이 받는 사원들의 이름과 급여를 출력하되 부서번호가 
-- 20번이 사원은 제외한다.(ANY연산자 이용)

-- 단일행 서브쿼리
select ename, sal from emp where sal > 
    (select min(sal)  from emp where job='SALESMAN') and deptno != 20;

-- 다중행 서브쿼리
select ename, sal from emp where sal > any
    (select sal from emp where job='SALESMAN') and deptno != 20;

과제
2022.07.25 / Hw2

직급이 'SALESMAN'인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들의

이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다.(ALL연산자 이용)

의도

단일행 서브 쿼리 사용

다중행 서브 쿼리 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220725/2022_07_25_hw2.sql

코드

-- Q2. 직급이 'SALESMAN'인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들의 이름과 급여를 출력하되 부서번호가 
--	20번인 사원은 제외한다.(ALL연산자 이용)

-- 1) 단일행 서브쿼리로 풀기
select ename, sal from emp where sal > (select max(sal) from emp where job = 'SALESMAN') and deptno <> 20;
-- 2) 다중행 서브쿼리로 풀기
select ename, sal from emp where sal > all (select sal from emp where job = 'SALESMAN') and deptno <> 20;

출력

강사님 코드

-- Q2. 직급이 'SALESMAN'인 사원이 받는 급여들의 최대 급여보다 많이 받는 
-- 사원들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다.(ALL연산자 이용)

-- 단일행 서브쿼리
select ename, sal from emp where sal > 
    (select max(sal) from emp where job='SALESMAN') and deptno != 20;
    
-- 다중행 서브쿼리
select ename, sal from emp where sal > all
	(select sal from emp where job='SALESMAN') and deptno != 20;

과제
2022.07.25 / Hw1

SMITH와 동일한 직급을 가진 사원의 이름과 직급을 출력하는 SQL문을 작성 하세요?

의도

단일행 서브 쿼리 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220725/2022_07_25_hw1.sql

코드

-- Q1. SMITH와 동일한 직급을 가진 사원의 이름과 직급을 출력하는 SQL문을 작성 하세요?
select ename, job from emp where job = (select job from emp where ename = 'SMITH');

출력

강사님 코드

-- Q1. SMITH와 동일한 직급을 가진 사원의 이름과 직급을 출력하는 SQL문을 작성 하세요?
select ename, job from emp where  job = 
    (select job from emp where ename='SMITH') and  ename != 'SMITH';

과제
2022.07.22 / Hw3

SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하는 SQL문 작성

의도

등가 조인 사용

서브 쿼리 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220722/2022_07_22_hw3.sql

코드

-- Q3. SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하는 SQL문 작성?
select e.ename from emp e, dept d where e.deptno = d.deptno 
    and d.loc = (select d.loc from emp e, dept d where e.deptno = d.deptno and e.ename = 'SCOTT');
    
-- 또는 부서번호가 같으면 같은 근무지에서 근무하므로 조인없이 서브쿼리만으로 이렇게 해도 될 것 같습니다
select ename from emp where deptno = (select deptno from emp where ename = 'SCOTT');

출력

강사님 코드

-- Q3. SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하는 SQL문 작성?
--Ans 3.
select deptno, ename from emp 
   where deptno = (select deptno from emp where ename = 'SCOTT');

과제
2022.07.22 / Hw2

 매니저가 KING 인 사원들의 이름과 직급을 출력하는 SQL문 작성

의도

셀프 조인 사용

서브 쿼리 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220722/2022_07_22_hw2.sql

코드

-- Q2. 매니저가 KING 인 사원들의 이름과 직급을 출력하는 SQL문 작성?
select employee.ename, employee.job from emp employee, emp manager
    where employee.mgr = manager.empno and manager.ename = 'KING';

출력

강사님 코드

-- Q2. 매니저가 KING 인 사원들의 이름과 직급을 출력하는 SQL문 작성?
--Ans 2.(2가지 방법)
select employee.ename, employee.job 
    from emp employee, emp manager
    where employee.mgr=manager.empno and manager.ename='KING';
    
select ename, job from emp where mgr = 
    (select empno from emp where ename='KING');

과제
2022.07.22 / Hw1

직급이 MANAGER인 사원의 이름, 부서명을 출력하는 SQL문을
             작성 하세요? (JOIN을 사용하여 처리)

의도

등가 조인 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220722/2022_07_22_hw1.sql

코드

-- Q1. 직급이 MANAGER인 사원의 이름, 부서명을 출력하는 SQL문을 작성 하세요? (JOIN을 사용하여 처리)
select e.ename, d.dname from emp e, dept d 
    where e.deptno = d.deptno and e.job = 'MANAGER';

출력

강사님 코드

-- Q1. 직급이 MANAGER인 사원의 이름, 부서명을 출력하는 SQL문을 작성 하세요? (JOIN을 사용하여 처리)
--Ans 1. (4가지 방법)
select ename, dname from emp, dept 
   where emp.deptno=dept.deptno  and  job='MANAGER'; 
   
select ename, dname from emp inner join dept 
   on emp.deptno=dept.deptno  where  job='MANAGER'; 
   
select ename, dname from emp inner join dept 
   using(deptno)  where  job='MANAGER'; 
   
select ename, dname from emp natural join dept 
   where job='MANAGER';