[Oracle] SQL문 정리 2

제약조건

Check

 - 데이터를 입력 받을 때 해당 값이 미리 정해진 조건에 부합하는지 확인 후

   부합하면 입력받고, 부합하지 않으면 오류를 발생

CREATE TABLE emp08(
	empno NUMBER,
	ename VARCHAR(20),
	job VARCHAR(20),
	deptno NUMBER,
	gender char(1) CHECK(gender IN('M', 'F'))
);

-- 오류 : ORA-02290: check constraint (HR.SYS_C007072) violated
INSERT INTO emp08 VALUES(100, 'park', 'IT', 30, 'A');

 - Check의 부합 조건 (CHECK gender IN('M', 'F')) 에 'A'는 부합하지 않기 때문에

   INSERT INTO emp08 VALUES(100, 'park', 'IT', 30, 'A');

 

 

DEFAULT

 - 테이블에 데이터 입력시 DEFAULT 제약 조건이 갖는 column에 데이터가 입력되지 않는 경우 default 값을 입력

 - 이말은 즉, DEFAULT 제약 조건을 갖는 column의 경우 data를 입력받지 않아도 오류가 생기지 않는다.

--emp09 DEFAULT를 통해 초기 값을 설정 가능
CREATE TABLE emp09(
	empno NUMBER,
	ename VARCHAR2(20),
	job VARCHAR(20),
	deptno NUMBER,
	loc VARCHAR2(20) DEFAULT 'SEOUL'
	);

INSERT INTO emp09 (empno, ename, job, deptno)
    VALUES(100, 'park', 'IT', 30);

 

 

다중 PRIMARY KEY (복합키)

 - 1개의 PRIMARY KEY로 테이블에서 유일한 row를 판별하기 어려운 경우

   ( ex) 학교의 학생 이름을 유일 Primary Key로 사용할 경우 동명이인이 있을 수 있음 )

 

 - 다중 PRIMARY KEY를 통해 유일한 row를 판별해낼 수 있음

   ( ex) 학번과 이름을 PRIMARY KEY로 생성 )

 

 * 각각의 PRIMARY KEY 가 모두 같은 경우가 아니면 중복이 아니다.

   ( ex) 학번과 이름이 같은 경우가 아니면 중복 오류가 발생하지 않는다.)

--emp10 1개의 column으로 식별하기 어려운 경우에 PRIMARI KEY를 여러개 만든다.
--PRIMARY KEY가 여러개인 경우 여러개가 종합적으로 하나의 PRIMARY KEY의 역할을 하므로
--각각의 PRIMARY KEY가 모두 같은경우가 아니면 중복이 아니다.
CREATE TABLE emp10(
empno NUMBER,
ename VARCHAR(20),
job VARCHAR(20),
deptno NUMBER
);

ALTER TABLE emp10
    ADD CONSTRAINT emp10_empno_ename_pk
    PRIMARY KEY(empno, ename);
    
INSERT INTO emp10 VALUES(100, 'park', 'IT', 30);
INSERT INTO emp10 VALUES(100, 'kim', 'IT', 30);

--오류 ORA-00001: unique constraint (HR.EMP10_EMPNO_ENAME_PK) violated
--위와 PRIMARY KEY가 모두 겹침
INSERT INTO emp10 VALUES(100, 'park', 'IT', 30);

 

 

제약 조건 삭제 (해제)

--ALTER TABLE 테이블명
--    DROP CONSTRAINT 제약조건명

-- 테이블 생성
CREATE TABLE dept01
    as select * from departments;

-- 테이블에 제약 조건 주입
ALTER TABLE dept01
    ADD CONSTRAINT dept01_department_id_pk
    PRIMARY KEY(department_id);
    
-- 직전에 생성한 제약조건 삭제    
ALTER TABLE dept01
	DROP CONSTRAINT dept01_department_id_pk;

 

 

의존 관계(참조)의 제약 조건 삭제

 - 자식 테이블 (참조하는 테이블)이 부모 테이블 (참조 테이블)을 참조하고 있다면,

   즉 부모 테이블을 참조하여 row를 생성하였다면 부모 테이블에서 참조되는 Column과 해당 값은 삭제되지 않는다.

create table emp11(
empno NUMBER PRIMARY KEY,
ename VARCHAR2(20) NOT NULL,
job VARCHAR2(20),
deptno NUMBER REFERENCES dept01(department_id) -- 자식:emp11 =(참조)> dept01(department_id)
);

insert into emp11 values(100, 'park', 'it', 30);

--오류:ORA-02292: integrity constraint (HR.SYS_C007078) violated - child record found
--의존관계인 테이블이 존재하므로 삭제 불가 
--(department_id = 30 을 자식 테이블에서 참조하므로 삭제 불가
--만약 department_id = 30이 아닌 다른 값을 delete 했다면 삭제 가능)
delete from dept01 where department_id = 30;

--해결방법
--1.emp11 데이터 중 deptno 컬럼값이 30번인 row를 먼저 삭제하고 dept01의 department_id = 30을 삭제
delete from emp11 where deptno = 30;
delete from dept01 where department_id = 30;

    
CASCADE

- CASCADE를 이용하여 부서테이블의 데이터가 삭제가 되면 
- 참조하는 데이터(자식 테이블의 row)도 함께 삭제되도록 설정 (ON DELETE CASCADE)

CREATE TABLE emp12(
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER REFERENCES dept01(department_id)
    ON DELETE CASCADE
    );
    
    INSERT INTO emp12 VALUES(100, 'park', 'IT', 30);
    
    --CASCADE에 의해 위에 생성된 emp12의 row도 함께 삭제 : 오류 발생 x
    DELETE FROM dept01 WHERE department_id = 30;

 

 

 

---

Problem

 

1. DEPT_CONST 테이블

CREATE TABLE DEPT_CONST(
deptno NUMBER(2) CONSTRAINT DEPTCONST_DEPTNO_PK PRIMARY KEY,
dname varchar(14) constraint deptconst_dname_unq unique,
loc varchar(13) constraint deptconst_loc_nn not null
);

 

 

2. EMP_CONST 테이블

create table emp_const(
empno number(4),
ename varchar(10),
job varchar(9),
tel varchar(20),
hiredate date,
sal number(7),
comm number(7),
deptno number(2)
);

ALTER TABLE emp_const
    add constraint  EMPCONST_EMPNO_PK
    PRIMARY KEY(empno);

ALTER TABLE emp_const
    modify ename constraint EMPCONST_ENAME_NN not null;

ALTER TABLE emp_const
    ADD CONSTRAINT EMPCONST_TEL_UNQ 
    unique (tel);
    
ALTER TABLE emp_const
    ADD CONSTRAINT EMPCONST_SAL_CHK
    check(sal between 1000 and 9999);

ALTER TABLE emp_const
    ADD CONSTRAINT EMPCONST_DEPTNO_FK    
    FOREIGN KEY (deptno) references dept_const(deptno);

 

 

3. 각 테이블의 제약조건 확인

 - USER_CONSTRAINTS 뷰를 통해 각 테이블의 제약조건을 확인할 수 있다.

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 
  FROM USER_CONSTRAINTS 
 WHERE TABLE_NAME IN ( 'EMP_CONST', 'DEPT_CONST' ) 
ORDER BY CONSTRAINT_NAME;

상위의 코드 실행 결과

 

 

 

---

JOIN

 - 2개 이상의 데이블을 연결해서 데이터를 검색 

 

 - 최소 1개의 Column을 공유해야 하며 이 Column을 Primary Key 또는 Foreign Key로 사용

 

 

조인 사용 방법

 1. 내가 원하는 데이터가 무엇인가? (Column 목록)

 

 2. 원하는 데이터가 어느 테이블에 있는가?

 

 3. 여러 테이블에 있다면 각각의 테이블의 공통 컬럼을 찾는다.

 

ex ) 'King'의 부서이름을 출력하시오

SELECT e.employee_id, e.department_id, d.department_name, e.last_name 
from employees e, departments d
where e.department_id = d.department_id --조건1
AND last_name = 'King';                 --조건2

 

 

 

조인 표기 방식

 1. Oracle전용 조인

select e.employee_id, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id
and last_name = 'King';

 

 1. ANSI JOIN (SQL-99)

select e.employee_id, e.department_id, d.department_name
FROM employees e inner join departments d
on e.department_id = d.department_id
where last_name = 'King';

 

 

 

3개 이상의 테이블 JOIN 방법

테이블1 join 테이블2
ON 공통컬럼1 = 공통컬럼1
join 테이블3
on공통컬럼2 = 공통컬럼2

 

 

Q. 3개 이상 테이블을 조인하여 사원이름, 이메일, 부서번호, 부서이름, 직종번호(job_id), 직종이름(job_title)을 출력 

 1. Oracle

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

 2. ANSI

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

 

Q. 'Seattle' (city)에 근무하는 사원 이름, 부서번호, 직종번호, 직종 이름, 도시 이름을 출력하라.

 1. Oracle

select e.last_name, d.department_id, j.job_id, j.job_title, l.city
from employees e, departments d, jobs j, locations l
where e.department_id = d.department_id
and e.job_id = j.job_id
and d.location_id = l.location_id
and l.city = 'Seattle';

 

 2. ANSI

select e.last_name, d.department_id, j.job_id, j.job_title, l.city
from employees e join departments d
on e.department_id = d.department_id
join jobs j
on e.job_id = j.job_id
join locations l
on d.location_id = l.location_id
where l.city = 'Seattle';

 

 

 

Self 조인

 - 동일 테이블 사이의 조인

 ex) 직원 a의 상사 b, 다시 직원 b의 상사 c, 즉 a 의 차상위 관리자 c를 구하기 위해 사용

 

Q. 'Kochhar' 직속상사의 정보를 출력

select a.last_name||'의 매니저는 '||b.last_name||'이다.'
from employees a, employees b
where a.manager_id = b.employee_id
and a.last_name = 'Kochhar';

 

 

 

OUTER JOIN (외부조인)

 - 정상적으로 조인 조건을 만족하지 못하는 행 (data : null) 들을 보기 위해 사용됨

 SELECT * FROM employees;
 select e.employee_id, e.department_id, d.department_id,    d.department_name
 from employees e, departments d
 where e.department_id = d.department_id(+);

 

 Q. kosta236> 등록된 과목에 대한 모든 교수를 검색하라. (등록되지 않은 교수도 출력, 누락된 교수가 없도록 출력)

 1. Oracle

 select c.cno, c.cname, c.st_num, p.pname
 from course c, professor p
 where c.pno(+) = p.pno;

  2. ANSI

select c.cno, c.cname, c.st_num, p.pname
from course c right join professor p
on c.pno = p.pno;

 

 

 

---

Problem

Q. '송강' 교수가 강의하는 과목을 검색
 1. 교수번호(pno), 교수이름(pname), 과목명(cname)
 2. professor, course
 3. pno

select p.pno, p.pname, c.cname
FROM professor p, course c
WHERE p.pno = c.pno AND pname = '송강';

 

 

Q. 학점이 2학점인 과목과 이를 강의하는 교수를 검색

select c.cname, p.pname, c.pno
from course c, professor p
where c.st_num = 2 and c.pno = p.pno;

 

 

Q. 화학과 1학년 학생의 기말고사 성적을 검색

select st.sno, st.sname, sc.result from student st, score sc
where st.sno = sc.sno AND major = '화학' and syear = 1;

 

 

Q. 화학과 1학년 학생이 수강하는 과목을 검색

select major, syear, sname, cname
from student st, course c, score sc
where c.cno = sc.cno
and st.sno = sc.sno
and major = '화학'
and syear = 1;

 

 

Q. 학생 중에 동명이인을 검색하라

 SELECT distinct A.sno, A.sname
from student a, student b
where a.sname = b.sname
and a.sno != b.sno;

 

 

Q. 이름이 ‘Himuro’인 사원의 부서명을 출력하라.

select e.first_name, e.last_name, d.department_id, d.department_name
from departments d inner join employees e
on e.department_id = d.department_id
where last_name = 'Himuro';

 

Q. 직종명이 'Accountant'인 사원의 이름과 부서명을 출력하라.

select e.first_name, e.department_id, d.department_name, j.job_title
from employees e join jobs j
on j.job_title = 'Accountant'
join departments d
on e.department_id = d.department_id;

 

Q. 커미션을 받는 사람의 이름과 그가 속한 부서를 출력하라.

select e.commission_pct, e.first_name, e.last_name, e.department_id, d.department_name
from employees e inner join departments d
on e.commission_pct != 0.4
order by commission_pct;

 

Q. 급여가 4000이하인 사원의 이름, 급여, 근무지를 출력하라.

select e.first_name, e.last_name, e.salary, d.department_name, l.city
from employees e inner join departments d
on e.department_id = d.department_id
inner join locations l
on l.location_id = d.location_id
where salary <= 4000
order by salary;

 

 

Q. 'Chen'과 동일한 부서에서 근무하는 사원의 이름을 출력하라.

select e.first_name, e.last_name, e.department_id
from employees e inner join employees e2
on e.department_id = e2.department_id
where e2.last_name = 'Chen';

 

 

 

Q.

1. Oracle

select e.deptno, dname, empno, ename, sal
from emp e, dept d
where e.deptno = d.deptno
and sal>2000
order by deptno, sal;

 

2. Ansi

select e.deptno, d.dname, e.empno, e.ename, e.sal
from emp e inner join dept d
on e.sal > 2000
where e.deptno = d.deptno
order by empno asc;

 

3. Natural Join

select deptno, dname, empno, ename, sal
from emp natural join dept -- where e.deptno = d.deptno
where sal > 2000
order by empno asc;

 

 

Q.

1. Oracle

select e.deptno, dname, trunc(avg(sal), 2) as avg_sal, max(sal) as max_sal, min(sal) as min_sal, count(*) as cnt
from emp e, dept d
where e.deptno = d.deptno
group by e.deptno, dname;

 

2. Ansi (Natural JOIN)

select deptno, dname, trunc(avg(sal), 2) as avg_sal, max(sal) as max_sal, min(sal) as min_sal, count(*) as cnt
from emp natural join dept
group by deptno, dname; -- ** 여기 dname 지우면 오류 뜸 나중에 꼭 확인

 

 

Q.

1. Oracle

select e.deptno, dname, empno, ename, job, sal
from emp e, dept d
where e.deptno(+) = d.deptno
order by e.deptno, ename;

 

2. Ansi

select e.deptno, dname, empno, ename, job, sal
from emp e join dept d
on e.deptno(+) = d.deptno
order by e.deptno, ename;

select e.deptno, dname, empno, ename, job, sal
from emp e RIGHT OUTER JOIN dept d
on e.deptno = d.deptno
order by deptno, dname;

select deptno, dname, empno, ename, job, sal
from emp natural right outer join dept
order by deptno, dname;

 

 

Q.

1. Oracle

select e1.deptno, d.dname, e1.empno, e1.ename, e1.mgr, e1.sal, 
s.losal as losal, s.hisal as hisal, s.grade, e2.empno as mgr_empno, e2.ename as mgr_ename
from emp e1, emp e2, dept d, salgrade s
where e1.mgr = e2.empno(+)
and e1.deptno(+) = d.deptno
and e1.sal between s.losal(+) and s.hisal(+)
order by e1.deptno, empno;

 

2. Ansi

select e1.deptno, d.dname, e1.empno, e1.ename, e1.mgr, e1.sal, 
s.losal as losal, s.hisal as hisal, s.grade, e2.empno as mgr_empno, e2.ename as mgr_ename
from emp e1 join emp e2
on e1.mgr = e2.empno(+)
RIGHT OUTER join dept d
on e1.deptno = d.deptno
LEFT OUTER join salgrade s
on e1.sal between s.losal and s.hisal
order by e1.deptno, empno;