Oracle 5

 

테이블 3개연결!

Ansi표준

employees와 jobs, departments를 연결

Step1.테이블 2개연결

select e.*,j.* from  employees e inner join jobs j on e.job_id=j.job_id

Step2.테이블 3개 연결

select e.last_name,e.first_name,j.job_title,d.department_name from  employees e inner join jobs j on e.job_id=j.job_id inner join departments d on e.department_id=d.department_id

Oracle에서 3테이블 연결

select e.first_name,e.last_name,j.job_title,d.department_name from employees e, jobs j, departments d where e.job_id=j.job_id and e.department_id=d.department_id

Sub Query!!!

쿼리문에 다시 쿼리를 써주는 것

Q.SALES라는 부서에 속하는 사원들을 구하시오

Step1.select deptno from dept where dname='Sales'  ->30

Step2.select deptno from dept where dname='SALES';

//Step1->Step2를 차례로 실행한 결과와 같은데 아래와 같이 where절에 괄호를 이용해서 Step1에 실행한 쿼리를 넣어서 실행하는 서브쿼리를 이용한 결과는 같다.select안에 select

select empno,ename,job,sal from emp where deptno=(select deptno from dept where dname='SALES');

Q.SCOTT과 같은 부서에서 근무하는 사원의 이름과 부서의 번호를 출력하는 SQL문

A.select ename,deptno from emp where deptno=(select deptno from emp where ename='SCOTT')

Q.SCOTT와 동일한 직급을 가진 사원을 출력하는 SQL문(SCOTT제외)

A.select * from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT'

Q.SCOTT의 급여와 동일하거나 더 많이 받는 사원명과 급여를 출력해 보세요.

 A.select * from emp where sal>=(select sal from emp where ename='SCOTT')

Q.DALLAS에서 근무하는 사원의 이름,부서번호를 출력해보세요.

A.select ename,deptno from emp where deptno=(select deptno from dept where loc='DALLAS')

Q.회사의 평균급여보다 많이 받는 사람을 출력

A.select * from emp where sal>=(select avg(sal) from emp)

Q. employees 에 last_name 이 Abel인 직원의 급여보다 많이 받는 사람들의 last_name과 급여를 구하세요

A.select last_name,salary from employees
where salary>=(select salary from employees where last_name='Abel')

Q.Pat Fay와 같은 부서에 근무하는 사람들의 이름,급여,부서,직책(job_id가 아니라 job테이블에서 연결해서)을 구하세요(Pat-first_name, Fay-last_name)

A.select last_name, salary, j.job_title, d.department_name
from employees e,jobs j, departments d
where e.department_id=(select department_id from employees where first_name='Pat' and last_name='Fay')
and j.job_id=e.job_id
and e.department_id=d.department_id

<오늘의 과제>

과제!>Q1.사원중 사원번호가 141번인 번호인 사람의 부서에서 사원번호 143번인 사람의 급여와 같거나 많이 받는 사람의 이름,직책,급여를 구하세요(참고 테이블:employees, 이름:last_name으로 지정)

A.select last_name, job_id, salary from employees
where department_id=(select department_id from employees where employee_id=141)
and salary>=(select salary from employees where employee_id=143)

과제!>Q2.우리회사에서 평균급여보다 많이 받는 사람의 이름, 직책, 급여를 구하세요

(참고 테이블:employees)

A.select first_name, job_id, salary from employees where salary>=(select avg(salary) from employees)

Q.월급 상위 3명을 구하세요.

rownum을 활용! 

select empno,ename,sal from emp where rownum<=3;

BUT!!! select empno,ename,sal from emp where rownum>=2 and rownum<=3  이렇게는 사용못함!

왜냐면 rownum은 1번이 포함되야 카운트가 시작됨.

서브쿼리로 3등뽑기>>

select rownum,e.* from (select * from emp order by sal desc) e where rownum<=3

서브쿼리로 상위 급여 상위 5위에서 8위 뽑기>>

Step1. emp를 급여별로 정렬해주고

select * from emp order by sal desc

Step2. rownum을 rnum이라는 변수로 사용해서 카운트를 변수로 저장해준다.   

select rownum rnum, e.* from (select * from emp order by sal desc) e

Step3. 1~n까지 정렬된 rnum의 순서를 조건으로 지정한다.

select * from (select rownum rnum, e.* from (select * from emp order by sal desc) e) where rnum>=5 and rnum<=8

 

select * from (select rownum rnum, emp.* from (select avg(sal) from emp where deptno=50)emp)
where rnum>=10 and rnum<=15

where kor>=90 and rownum>=3

90

80

70   이경우 첫번째 조건은 만족하나 2번째 조건은 만족하지 못하므로 출력x, 두번째는 둘다안됨, 세번째도 안됨...  이럴떄 rownum자체가 카운트증가가 되지 않으므로 결과값은 계속 false가 나오므로 잘못된 공식이다.

과제!!>Q3.부서 50번에서 급여가 10등부터 15등까지 사원의 이름,급여,직책을 출력하세요.

A.select * from (select rownum r, e.first_name,e.salary,e.job_id from (select * from employees where department_id=50)e) where r>=10 and r<=15

Q.연봉 3000이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 정보를 출력하세요.

select distinct deptno from emp where sal>=3000      //조건에 만족하는 deptno에 속한 사람 여러명. 따라서 distinct로 중복을 제거한다.  (결과:10,20)

select deptno,ename,sal from emp where deptno in (select distinct deptno from emp where sal>=3000)

//위를 보면 deptno가 10과 20의 값인데 부등호나 비교연산자를 사용할 수 없다.(단일값만 가능)

그렇기 때문에 in연산자를 사용하게 되는데 in연산자는 (10,20) 값중 하나만 일치해도 참!

 

Q.부서별로 가장 급여를 많이 받는 사원의 정보(사원번호,사원명,급여,부서,번호)를 출력

A.select empno,ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno)

Q.부서가 Executive인 직원의 부서번호와 부서번호와 이름과 직책을 구하세요(Employees와 Departments테이블 이용)

A.select e.department_id, e.last_name,j.job_title  from employees e, jobs j where e.department_id=(select department_id from departments where department_name='Executive') and j.job_id=e.job_id

 

과제>Q4.이름이  u가 포함된 사원이 있는 부서에서 평균 급여보다 많이 번 사원들의 이름,급여,사원번호를 출력하세요

A1.select * from employees
where department_id in(select department_id from employees
where lower(last_name) like '%u%' )
and salary > (select avg(salary) from employees)

 

Q.직급(Job)이 Manager인 사람이 속한 부서의 부서번호와 부서명과 지역을 출력

A1.select distinct emp.deptno,dname,loc from emp,dept where dept.deptno in (select deptno from emp where job='MANAGER')and emp.deptno=dept.deptno

A2.select emp.deptno,dname,loc from emp,dept where job='MANAGER' and emp.deptno=dept.deptno

 

All연산자 

30번 소속 사원중에서 급여를 가장 많이 받은 사원보다 더 많이 받은 사원들의 이름,급여를 출력하라.

select max(sal) from emp group by deptno having dept=30

//  =      select max(sal) from emp where deptno=30 group by deptno

(max값이 한명일수도 있지만 값이 같아서 여러개가 나올 수 도 있다.)

select * from emp where sal > (select max(sal) from emp where deptno=30 group by deptno)

(따라서 오류남.. max가 여러값일수 있는데 sal과 비교한다는 것은 말이안됨!!꺅!!)

이것을 해결하기 위해 all연산자를 사용한다!

select * from emp where sal> all(select...) 하면 괄호안에 나올수있는 여러값들을 뽑아서 sal과 비교

10,20,30 각각 보다 크면 출력(한개라도 크면 출력)

all은 10,20,30보다 다 크면 출력(모두 만족해야 출력)

 

Q.30번 부서사람들 보다 많이 받는 사람을 출력

1600 1250 2850

>all(1600, 1250,2850)

만일 30번 부서사람중에 한사람이라도 만족하는 사람

>any(..)

Q.영업사원보다 급여를 많이 받는 사원들의 이름과 급여를 출력하되 영업사원은 출력하지 않습니다.,

Q.영업사원들의 최소급여보다 많이 받는 사원들의 이름과 급여직급을 출력하되.

where sal>any(select sal from emp where job='SALESMAN')

and job=<>'SALESMAN'

DDL - >create, alter, drop

DML ->insert, update, delete

Database Object - table, view ,sequence, index, synonym

char - 고정길이 지정 문자   vs    varchar2 - 가변길이 지정문자

interval year to month

interval day to second

컬럼의 기간(3년)저장하기

m1.insert into t2(interval '123' yeart(3));  -> +123-00

m2.insert into t2 values(interval '123' month(3));  -> +010-03

m3.insert into t2 values(interval '12-2' year(3) to month)); -  +012-02

 

select sysdate,to_char(sysdate+M1,'yyyy-mm-dd') from t2

 

 

insert into t5 values(interval '3' day (3));

insert into t5 values(interval '3 05:30:30' day to second)

select sysdate,to_char(sysdate+d1,'yy-mm-dd HH24:mi:ss') from t5

테이블삭제 : drop table 테이블명 purge;

Q.emp테이블에서 job='MANAGER'인 자료를 뽑아서 하나의 테이블로 구성해보자.

A.create table tb_manager as select * from emp where job='MANAGER'

 

Q.직책이 manager인 자료중 empno와 ename과 job,buso  (컬럼이름은 이런식)

create table tb_manager(empno,ename,job,buso) as select empno,ename,job,deptno from emp where job='MANAGER'

*순서를 지켜줘야함(정상적으로 작동이 될수도있으나 컬럼의 형식이 꼬일수 있으므로..)

insert select는 자료를 형식에 맞춰야 하므로 구조가 중요함

create table..as는 형식을 지정하지 않고 원래 테이블의 구조를 이용하므로 구조가 중요하지 않으나 원하는 결과값이 나오기 위해서는 컬럼의 순서를 정확하게 지정해주어야 한다.

 

테이블 구조를 변경하는 Alter table

alter table 테이블 이름

1.컬럼추가(add사용)

alter table 테이블이름 add 컬럼명 컬럼속성(크기,타입)

Q.myinfo테이블을 조건1과 같이 만들고 조건2를 추가해라.

조건1)hakno  varchar2(10) name juso phone nai email

조건2)컬럼을 추가해서 hp jumin  - > alter table myinfo add jumin varchar2(20)

2.컬럼삭제

alter table myinfo drop column 컬럼명;

3.타입변경

alter table 테이블명 modify 컬럼명 컬럼속성변경값

 

주의! 속성값에 의해 값들이 들어가있는데 modify로 더 적은 속성값으로 변경하면 오류남.(지나치게 적은경우..)

해당컬럼에 자료가 없는경우->컬럼의 데이터타입과 크기변경가능

해당컬럼에 자료가 있는 경우->컬럼의 데이터 타입을 변경할수없음, 크기는 늘릴 수 있지만 현재 가지고 있는 데이터의 크기보다 작은 크기로 변경할 수 없다.

테이블의 모든 로우를 제거하는 Truncate

truncate table myinfo

또는 delete from myinfo

테이블의 이름을 변경하는 Rename

rename t0 to tt0

사용자가 소유한 모든 테이블확인:desc user_tables;

 

예제시간~>create table copyemp as select empno,ename,job,hiredate,sal from emp

insert into copyemp values(1111,'홍길동','사장','2011-01-01',10000)

특정 컬럼에만 자료를 입력하고 싶을때 ->

insert into copyemp(empno,ename,hiredate) values(3333,'김하나',sysdate)
또는 insert into copyemp values(5555,'이지연',null,'2009-10-10',null) //null값으로 채워주거나 특정컬럼만 써서 값을 입력해주거나!

insert into copyemp select * from emp; -copyemp의 컬럼이 emp보다 적으므로 오류

insert into copyemp(empno,ename,job,hiredate,sal) select empno,ename,job,sal,hiredate from emp  //이러면 sal과 hiredate의 타입이 다르므로 오류남

Q.직위가 salesman인 사원을 COPYEMP테이블에 추가하세요

A.insert into copyemp select empno,ename,job,hiredate,sal from emp where job='SALESMAN'

UPDATE이란 테이블의 내용을 수정

update테이블 이름 set 변경컬럼=값,컬럼=값

ex)update sungjuck set ko=90, eng=90 where조건

Q.불쌍한 세일즈맨에게 7% 급여인상을!!하고 그것을 테이블에 반영하라

A. update coypyemp set sal=sal*0.07 where job='SALESMAN';

계약직 사원인 세일즈맨~

update copyemp set hiredate=trunc(sysdate,'year') where job='SALESMAN'