레이커 갓생일기

서브쿼리 문제

사원 테이블(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.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';

과제
2022.07.21 / Hw2

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

의도

서브쿼리 사용

그룹함수 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220721/2022_07_21_hw2.sql

-- Q2. 사원 테이블(EMP)에서 최대 급여를 받는 사원명과 최대급여 금액을 출력하는 SQL문을 작성 하세요?
select ename, sal from emp where sal = (select max(sal) from emp);

출력

강사님 코드

-- Q2. 사원 테이블(EMP)에서 최대 급여를 받는 사원명과 최대급여 금액을 출력하는 SQL문을 작성 하세요?
select ename, sal from emp where sal=
		(select max(sal) from emp);

과제
2022.07.21 / Hw1

사원 테이블(EMP)에서 가장 최근에 입사한 사원명을 출력 하는 
           SQL문을 작성 하세요?

의도

서브쿼리 사용

그룹함수 사용

깃허브

https://github.com/kindacool/Hw/blob/main/HW20220721/2022_07_21_hw1.sql

코드

-- Q1. 사원 테이블(EMP)에서 가장 최근에 입사한 사원명을 출력 하는 SQL문을 작성 하세요?
select ename from emp where hiredate = (select max(hiredate) from emp) ;

출력

강사님 코드

-- Q1. 사원 테이블(EMP)에서 가장 최근에 입사한 사원명을 출력하는 SQL문을 작성 하세요?
select ename, hiredate from emp where hiredate=
		(select max(hiredate) from emp);