/*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 에서 랜선 뽑고 연습해보세요.
'스터디 티스토리' 카테고리의 다른 글
자기소개서 대고민 (0) | 2014.07.12 |
---|---|
12/3 PL/ SQL (0) | 2012.12.03 |
12/11/29 SQL 및 JAVA (2) | 2012.11.29 |
SQL 메모장 (0) | 2012.11.28 |
이런 글은 계속 메모하고 두고 봐야 합니다!! (0) | 2012.03.14 |