2012/11/30 SQL강의

/*Join , 제약조건 primary key , foreign key */]

/* Join 테이블과 테이블을 연결한다.  테이블 제거시에 다른 테이블에 연결되어 제약조건을 걸면 해당 테이블의 내용을 삭제할수 없게 된다.

   primary key 

   foreign key 

   카티션 곱 : 조건절 없이 테이블끼리 연결했을떄 중복된 데이터를 보여줘서 실제 데이터보다 배수로 표시하게 된다.   

   */

select * from emp,dept;

/*

동등 조인(Equi join)

조건절 (=)에 의하여 조인이 이루어짐

*/

select * from emp,dept

where emp.deptno = dept.deptno;

select emp.ename, dept.deptno, dept.dname

from emp,dept

where emp.deptno = dept.deptno;

/* 두 테이블간 중복된 칼럼이 없으면 테이블명을 명시 안해줘도 된다. 

 만약에 중복이 있는 테이블명을 명시안해주면 오류가 뜬다*/

select ename, dept.deptno , dname

from emp,dept

where emp.deptno= dept.deptno;

/* 

테이블에 알리아스 부여

*/

select e.ename,d.deptno

from emp e, dept d

where e.deptno = d.deptno;

select e.ename,d.dname

from emp e,dept d

where e.deptno = d.deptno

and e.ename = 'ALLEN';

/*

문제

1)emp 테이블과 dept 테이블을 조인해서 이름과 부여이름을 

 출력하는데 급여가 3000~4000 사이에 있는 정보만 출력

*/

select e.ename,d.dname,e.sal

from emp e,dept d

where e.deptno = d.deptno

and e.sal between 3000 and 4000;

/*

비동등 조인(non equi join)

*/

select e.ename,d.dname

from emp e,dept d

where e.sal between 3000 and 4000;

/*self join*/

select a.ename || ':$' || b.sal 급여

from emp a, emp b

where a.empno = b.empno;

/* 외부 조인: 동등 조인의 조건에 미포함된 내용을 보여주는 조인 방식

   누락되지 않은 반대 테이블에다 (+) 기호를 붙여서 실행한다 */

select e.deptno, d.deptno

from emp e, dept d 

where e.deptno(+) = d.deptno;

/* 중복제거용 함수 distinct : 중복값을 제거 해준다. */

select distinct(e.deptno),d.deptno

from emp e, dept d

where e.deptno(+) = d.deptno;

/*문제

커미션이 책정된 사원들의 사원번호, 이름 ,연봉, 연봉+커미션

,급여등급을 출력하되, 각각의 컬러명을 '사원번호','연봉',

'실급여','급여등급'으로 출력(emp,salgradem join)

*/

select e.empno "사원번호", e.ename "사원이름", e.sal*12 "연봉" , e.sal*12+comm "실급여" , s.grade "급여등급"

from emp e, salgrade s

where e.sal between s.losal and s.hisal 

and e.comm is not null;

/* 실제 DB연동해서 쓸때는 알리아스는 영어를 사용해서 쓴다. 공부용으로는 식별자 한글 사용 가능 */

/* 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름,

   월급, 급여등급을 출력 (dept,emp,salgrade)

*/

select e.deptno , 

d.dname ,

e.ename ,

e.sal ,

s.grade

from emp e, salgrade s ,dept d 

where e.deptno = d.deptno

and e.deptno = 10

and e.sal between s.losal and s.hisal;

/*

집합연산자 : union(합집합 중복값 제거)

union은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환

*/

select deptno from emp

union

select deptno from dept;

/*

union all

union과 같으나 두 테이블의 중복되는 값까지 반환

*/

select deptno from emp

union all

select deptno from dept;

/*

intersect 는 두 행의 집합 중 공통된 행을 반환

*/

select deptno from emp

intersect

select deptno from dept;

/*

minus는 첫번째 select 문에 의해 반횐되는 행 중에서 두 번째 select문에 의해 반환되는 행에 존재하지 않는 행들을 보여줌

*/

select deptno from dept

minus

select deptno from emp;

/*

Subquery 질의

다른 하나의 SQL문자의 절에 내장된 select 문장이다

단일행 서브쿼리 : 오직 한 개의 행(값)을 반환

*/

select * from emp where deptno = 10;

select ename,job from emp

where job = (select job from emp where empno=7369);

select job from emp where empno=7369; /*메모리에 저장될 값*/

select ename,sal from emp

where sal > (select sal from emp where ename='BLAKE');

select sal from emp where ename='BLAKE';

/*문제

10번 부서에서 급여를 가장 적게 받는 사원의 이름 출력

*/

select min(sal) from emp

where deptno=10;

select ename from emp

where sal = (select min(sal) from emp where deptno = 10);

/* 다중 행 서브쿼리

하나 이상의 행을 반환하는 서브쿼리 

in 연산자의 사용 : 

any 연산자의 사용 : 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이 되면 결과값을 반환*/

select empno,ename from emp

where sal in (select min(sal) from emp group by deptno);

select min(sal) from emp group by deptno;

select ename,sal from emp

where deptno !=20

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

select sal from emp where job = 'SALESMAN';

/*

2번 문제: BLAKE와 같은 부서에 있는 사원들의 이름과 고용일 뽑는데

BLAKE는 제외하고 출력

*/

select ename,job,hiredate from emp

where ename = 'BLAKE';

select ename,hiredate from emp

where ename != 'BLAKE'

and job = any (select job from emp where job = 'MANAGER');

select job from emp where job = 'MANAGER';

select deptno from emp where ename = 'BLAKE';

select ename, hiredate

from emp

where deptno = (select deptno from emp where ename = 'BLAKE')

and ename != 'BLAKE';

/*

평균급여보다 많은 급여를 받는 사원들의 사원번호,이름,월급을 출력

월급이 높은 사람 순으로 출력.

*/

select empno,ename,sal from emp;

select round(avg(sal)) from emp;

select empno,ename,sal from emp

where sal > (select round(avg(sal)) from emp)

order by sal desc;

/*

사원수가 3명이 넘는 부서의 부서명과 사원수를 보여주세요.

서브쿼리:부서별로 사원수 구하기

*/

select * from emp;

select count(job) from emp order by job;

select deptno,count(empno) cnt from emp group by deptno;

select a.dname , b.cnt

from dept a,(select deptno,count(empno) cnt from emp group by deptno) b

where a.deptno = b.deptno

and b.cnt > 3;

/*

20번 부서에서 가장 급여를 많이 받는 사람의 이름과 부서번호, 급여, 급여등급을 출력

서브쿼리 : 20번 부서에서 가장 급여를 많이 받는 사람의 급여

*/

select ename,deptno,sal from emp;

select ename,deptno,sal from emp where deptno = 20;

select e.ename,max(e.sal),s.grade from emp e,salgrade s,(select ename,deptno,sal from emp where deptno = 20) c

group by e.sal

where max(e.sal) 

and e.sal between s.losal and s.hisal;

select e.ename,d.dname,e.sal,s.grade

from emp e,dept d,salgrade s

where e.deptno = d.deptno

and e.sal between s.losal and s.hisal

and e.sal = (select max(sal) from emp where deptno =20);

select max(sal) from emp where deptno = 20;

/*

insert문

insert into 테이블명 (컬럼명) values (값)

*/

/*컬럼명에 맞쳐서 넣어야한다. null이 생길때는 컬럼명을 생략할수가 없다*/

select * from emp;

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)

values (8000,'DENIS','SALESMAN',7693,'99/01/22',1700,200,30);

insert into emp

values (8002,'DENIS','SALESMAN',7693,'99/01/22',1700,200,30);

/*

update 문

update 테이블명 set 컬럼명=변경할 데이터 where 조건

*/

update emp set ename = 'SUNNY' where empno=8000;

/*delete문 delete from 테이블명 where 조건; */

delete from emp where empno=8002 ;

/*테이블 생성*/

create table employee (

empno number(6),

name varchar2(20) not null,

salary number(8,2),

hire_date date default sysdate,

constraint employee_pk_empno primary key(empno)

);

create table employee2(

empno number(6)

);

/*

primary key & foreign key

*/

create table suser(

id varchar2(20),

name varchar2(20),

constraint suser_pk_id primary key(id)

);

create table sboard(

num number,

id varchar2(20) not null,

content varchar2(4000) not null,

constraint sboard_pk_num primary key(num),

constraint sborad_suser_fkl foreign key(id) references suser(id)

);

/*테이블 삭제*/

drop table suser;

drop table sboard;

/*

테이블 관리 

add 연산자 : 테이블에 새로운 컬럼을 추가

modify 연산자 : 테이블의 컬럼을 수정하거나 not_null컬럼으로 변경할 수 있음

drop 연산자 : 컬럼을 삭제

rename 연산자 : 컬럼의 이름 변경

*/

alter table employee add(addr varchar2(50));

alter table employee modify (salary employee);

alter table employee drop column name;

alter table employee rename column salary to sal;

/* 테이블명 변경 */

rename employee to employee3;

/* 연동시 필요한 시퀀스 기능 : Sequence

유일한 값을 생성해주는 오라클 객체

*/

create sequence test_seq 

start with 1

increment by 1

maxvalue 10000000 ;

select test_seq.currval from dual;

select test_seq.nextval from dual;

alter sequence test_seq

increment by 2;

select test_seq.currval from dual;

select test_seq.nextval from dual;

drop sequence test_seq;

www.oracle.com 에서 랜선 뽑고 연습해보세요.