무결성 제약조건 - 테이블에 유효하지않는 데이터가 입력되는것을 방지.
종류
not null
- 열에 null이 포함되지 않도록 지정.
unique - 테이블 전체에서 고유한 값을 가지도록 지정.
primary key
- 테이블에서 각 행을 고유하게 식별하도록 지정. 즉 각 행을 대표할수 있게 함.
foreign key
- 열에 값이 입력될때 참조 테이블(다른 테이블)의 지정된 열을 참조.
- 참조되는 칼럼은 반드시 프라임키나 유닉 제안조건이 지정된 컬럼이어야함.
check - 열에 입력가능한 값을 미리 지정함.
제약조건 정의
컬럼 레벨에서 제약조건 정의하기.
- 각각의 컬럼이 가져야 될 제약조건을 정의할 수 있다.
CREATE TABLE 테이블명
(
컬럼명 데이터타입 CONSTRAINT 제약조건이름 제약조건 타입
);
테이블 레벨에서 제약조건 정의하기.
- 하나 이상의 컬럼을 조합해서 제약조건을 정의할 수 있다.
CREATE TABLE 테이블명
(
컬럼명 테이터타입
컬럼명 테이터타입
...
CONSTRAINT 제약조건이름 제약조건타입 (컬럼명, ...),
)
ㄴ 컬럼들을 미리 지정해놓고. 하나 이상일때니까 1개도 됨.
아래껀 두개이상의 제약조건을 만들때.
NOT NULL, PRIMARY 제약조건이면 위에거밖에 안됨.
아래거는두개이상을 조합해서 하나의 제약조건으로 걸어버림.
그 위에껀 1개에 1개.
제약조건을 걸때 학생 테이블이 있어, 거기 학번, 이름이있겠지.
근데, 100번도 101도 102도 3번학생 홍길동 김유신 강감찬 이순신이 있다하면,
과목 테이블이 있는거야 과목테이블엔
과목코드, 과목 번호 같은게 있겠지.
S_001 002 003
전자기학, 재료역학, 전자실험1 이런게 있다고 하자고, 이런 과목들이 있어
수강 테이블이 있는거지.
수강에는 어떤 학생이 어떤 과목을 듣냐는거
학번, 과목번호가 있는 테이블. 그러면, 여기엔 어떤 값이 가야하지?
이런게 들어가면 안되니까. 이런경우는 절대 안되는게
100번학생이 S001 을 수강했어 근데, 100번이 또 S001 을 수강하면 안되니까.
101학생이 S001을 들어도 되지만, 한 학생이 두개를 중복해서 들으면 안되니까.
그런 경우에는 아래걸 쓴다는거지. 그런 방법일때 아래껄 쓴다는거지.
두개이상의 제약을 조합.
학번이랑 과목번호 둘다 고유 해야하니까. 두개가 합침(조합)을 하려면 아래껄 쓴다.
나중에 프로젝트를 하면, 테이블 명세서를 받게되는데 테이블의 데이터 타입 각 컬럼의 간단한 설명
제약조건이 어떤게 걸렸는지 그게 엑셀로 되어있는 명세서를 받게됨.
NOT NULL은 체크 제약조건임.
포린키, 유니크, 체크 제약조건은 많아도 되지만
프라이머리키 제약조건은 무조건 1개.
제약조건 추가/삭제
- 추가
alter table 테이블명
add contraint 제약조건명 제약조건타입 (컬럼명)
- 삭제
alter table 테이블명
drop contraint 제약조건명;
제약조건 정의하기
-- 회원 테이블 (회원번호, 이름, 연락처, 이멜, 생일, 가입일, 프로필사진, ...)
-- 회원번호
-> 그 회원을 대표하는 컬럼
-- 이름 -> 반드시 입력되야함
-- 연락처 -> 중복되는 값 x
-- 이메일 -> 중복되는 값 x, 반드시 입력되야함
-- 포인트 -> 0보다 큰 값이어야함.
CREATE TABLE SAMPLE_MEMBER
(
no number constraint mem_no_pk primary key,
namk varchar2(50)
constraint mem_name_nn not null,
gender char(1) constraint mem_gender_ck check
(gender in ('F', 'M') ),
tel varchar2(20)
constraint mem_tel_uk unique,
email varchar2(100)
constraint mem_email_nn not null,
point number constraint mem_point_ck check(point >0),
regdate date defalut sysdate,
constraint mem_email_uk unique (email)
);
-- 차일드 테이블에서 참조하고 있는 값을 삭제 할수 없다.
-- 차일드 테이블에서 참조하고 있는 테이블은 삭제 불가.
-- 차일드 테이블을 먼저 삭제후 부모 테이블을 삭제 가능.
-- 회원들이 작성하는 게시판 테이블 (글번호, 제목, 작성자, 내용, 조회수, 작성일, ...)
CREATE TABLE SAMPLE_BOARD (
)
-- 정의된 제약조건 정보가 있는 Dictionary -> USER_CONSTRAINTS
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES';
-- 정의된 시퀀스 정보가 있는 딕셔너리.
SELECT *
FROM USER_SEQUENCES;
-- 정의된 테이블 정보가 있는 딕셔너리
SELECT *
FROM USER_TABLES;
-- 정의된 인덱스 정보가 있는 딕.
SELECT *
FROM USER_INDEXES;
-- 정의된 뷰 정보가 있는 딕.
SELECT *
FROM SYS.USER_VIEWS;
제약조건 추가/삭제
- 추가
alter table 테이블명
add contraint 제약조건명 제약조건타입 (컬럼명)
- 삭제
alter table 테이블명
drop contraint 제약조건명;
뷰 (View)
- 뷰는 가상의 테이블임
- 하나 or 두개 이상의 테이블을 사용, 가상의 테이블(뷰)를 만들 수 있음.
뷰 쓰는 이유?
- 데이터에 대한 접근을 제한하기 위해.
- 복잡한 쿼리를 쉽게 작성하려고.
- 동일한 데이터로 다양한 결과를 얻기 위해.
뷰의 생성
Create or replace 뷰
as SELECT구문
with read only;
뷰 삭제
Drop view 뷰이름
인라인 뷰
- SQL문에서 사용되는 서브쿼리
- SELECT문의 FROM절에 있는 서브쿼리를 인라인뷰 라고 부름.
-
껐다 켜도, 뷰는 살아있음.
-- 부서번호가 80번인 사원의 정보를 가진 뷰
CREATE VIEW EMP_80
as SELECT employee_id, last_name, salary
FROM EMPLOYEES
where DEPARTMENT_ID = 80;
SELECT *
FROM EMP_80
WHERE SALARY > 10000;
-- 서재수의 사은품?
SELECT A.GNO, A.GNAME, POINT, B.GNAME as gift_name
FROM GOGAK A, gift B
where A.POINT >= B.G_START and A.POINT <= B.G_END
and A.GNAME = '서재수';
-- 고객 정보, 포인트에 해당하는 사은품 정보 주는 뷰.
CREATE or REPLACE VIEW gogak_gift -- 새로 만들거나, 바꾸거나
as select a.gno, A.gname as name,
point, b.gname as gift_name, SUBSTR(A.JUMIN, 1, 6) || '*******' as jumin
FROM GOGAK A, GIFT B
WHERE a.point >= B.G_START and a.point <= B.G_END
with read only; -- 읽기전용.
-- 서재수의 사은품?
SELECT GNO, NAME, POINT, GIFT_NAME
FROM GOGAK_GIFT
where NAME = '서재수';
SELECT DEPARTMENT_ID as id, round(avg(SALARY)) as avg_sal
FROM EMPLOYEES
group by
DEPARTMENT_ID
ORDER BY AVG(SALARY) DESC;
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS;
SELECT a.id, d.DEPARTMENT_NAME, a.avg_sal
FROM DEPARTMENTS D, (SELECT DEPARTMENT_ID as id,
round(avg(SALARY)) as avg_sal
FROM EMPLOYEES
group by
DEPARTMENT_ID)
a
WHERE a.id = D.DEPARTMENT_ID
ORDER BY a.avg_sal DESC;
-- 사번, 이름, 급여, 급여등급을 표시하는 SQL
--SELECT E.EMPLOYEE_ID, E.last_name, E.SALARY, G.Grade
--FROM EMPLOYEES E, (SELECT GRADE_LEVEL as Grade
-- FROM
JOB_GRADES) as G
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY, G.GRADE_LEVEL as Grade
FROM EMPLOYEES E, JOB_GRADES G
where E.SALARY >= G.LOWEST_SAL
and E.SALARY <= G.HIGHEST_SAL;
-- 사원 테이블의 모든 사원들의 사원번호, 이름,
-- 부서번호, 부서이름, 급여 급여등급 정보를 포함한 뷰 만들기.
create or REPLACE VIEW EMP_SALARY_DETAIL as
SELECT E.DEPARTMENT_ID, e.last_name,
D.DEPARTMENT_NAME, E.SALARY, G.GRADE_LEVEL
FROM EMPLOYEES E, JOB_GRADES G, DEPARTMENTS D
where E.SALARY >= G.LOWEST_SAL
and E.SALARY <= G.HIGHEST_SAL
and E.DEPARTMENT_ID = D.DEPARTMENT_ID
WITH READ ONLY;
SELECT *
FROM EMP_SALARY_DETAIL
where last_name = 'King';
-- 인라인뷰에서 순위 계산.
-- 급여를 가장 많이 받는 사원3.
SELECT ranking, last_name, salary
FROM
(SELECT ROW_NUMBER() OVER
(ORDER BY salary desc) as ranking,
last_name,
salary
FROM EMPLOYEES)
where ranking <=3;
-- 1997년 이후, Clerk.
SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, JOB_Id
FROM EMPLOYEES
WHERE JOB_ID = 'ST_CLERK'
and HIRE_DATE > '1997-01-01'
ORDER BY HIRE_DATE;
--...????
--2번 커미션 사람 표시.
SELECT LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT is not null
ORDER BY SALARY DESC;
-- 3번.
SELECT LAST_NAME, SALARY as before, salary*1.1 as after
FROM EMPLOYEES
WHERE COMMISSION_PCT is null
ORDER BY SALARY;
-- 4번 일한 년 월.
SELECT LAST_NAME, round(months_between (SYSDATE, hire_date)) as month,
round(months_between (SYSDATE, hire_date)/12) as years
FROM EMPLOYEES;
-- 5번 J K L M 시작 이름 사원
SELECT LAST_NAME
FROM EMPLOYEES
WHERE substr(LAST_NAME, 1, 1) in ('J', 'K', 'L', 'M');
-- 6번
SELECT LAST_NAME, SALARY, COMMISSION_PCT as com,
CASE
WHEN commission_pct is null then 'no'
when commission_pct is not null THEN 'yes'
end as yes_or_no
FROM employees;
-- 2005년 이후
SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE > TO_DATE('2006/01/01', 'yyyy-mm-dd')
and JOB_ID like '%CLERK';
-- 2번.
SELECT LAST_NAME, JOB_ID, TO_CHAR(SALARY, '$999,999') as sal,
to_char(COMMISSION_PCT) as comm
FROM EMPLOYEES
WHERE COMMISSION_PCT is not null
ORDER BY SALARY DESC;
-- 3번.
SELECT LAST_NAME || '의 급여가
10% 인상되어
' || SALARY *1.1 ||
'
달러가 되었심심'as salsal
FROM EMPLOYEES
WHERE COMMISSION_PCT is null;
-- 4번.
SELECT LAST_NAME, trunc(months_between (SYSDATE, hire_date)) as month,
trunc(months_between (SYSDATE, hire_date)/12) as years
FROM EMPLOYEES
ORDER BY HIRE_DATE;
-- 4번 정확.
SELECT LAST_NAME, trunc(months_between (SYSDATE, hire_date)/12) as years,
trunc(mod(months_between
(SYSDATE, hire_date), 12)) as dal
FROM EMPLOYEES;
with MONTHS_TABLE as (
SELECT LAST_NAME, MONTHS_BETWEEN(SYSDATE, hire_date) months
FROM EMPLOYEES
)
SELECT last_name, trunc(months/12) nyeon, trunc(mod(months, 12)) dal
FROM MONTHS_TABLE; -- 임시의 테이블.
-- 5번.
SELECT LAST_NAME
FROM EMPLOYEES
WHERE substr(LAST_NAME, 1, 1) in ('J', 'K', 'L', 'M');
-- 6번
SELECT LAST_NAME, SALARY,
CASE
WHEN commission_pct is null then 'no'
when commission_pct is not null THEN 'yes'
end as yes_or_no
FROM employees;
SELECT LAST_NAME, SALARY,
CASE
WHEN commission_pct is not null then 'yes'
ELSE 'no'
end comm
from EMPLOYEES;
SELECT LAST_NAME, SALARY,
decode(commission_pct, null, 'no', 'yes') comm
FROM EMPLOYEES;
SELECT LAST_NAME, SALARY,
nvl2 (to_char(commission_pct), 'yes', 'no') comm
FROM EMPLOYEES;
-- 7번 1800위치 부서정보 찾고, 부서아디와, 사원테이블 조인.
SELECT D.DEPARTMENT_ID, D.LOCATION_ID, E.LAST_NAME, E.JOB_ID, E.SALARY
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.LOCATION_ID = 1800
and D.DEPARTMENT_ID = E.DEPARTMENT_ID;
-- 8번. 이름이 n으로 끝나는 놈. 몇?
SELECT COUNT(*)
FROM EMPLOYEES
WHERE LAST_NAME like '%n';
SELECT COUNT(*)
FROM EMPLOYEES
where substr(LAST_NAME, length(last_name), 1) = 'n';
-- 9
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES E
GROUP BY DEPARTMENT_ID;
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID, nvl(e.cnt, 0)
FROM
(SELECT DEPARTMENT_ID, COUNT(*) as cnt
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID ) E, DEPARTMENTS D
WHERE e.DEPARTMENT_ID(+) = D.DEPARTMENT_ID;
-- 10, 부서번 10,20, 50의 업무 표시.
SELECT DISTINCT JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID in (10, 20, 50);
-- 11
SELECT E.JOB_ID, COUNT(*) as cnt
FROM DEPARTMENTS D, EMPLOYEES E
WHERe D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND D.DEPARTMENT_NAME in
('Administration', 'Executive')
GROUP BY E.JOB_ID
ORDER BY cnt desc;
-- 12, 입사일이 16일 이전의 사원명 입사일 표시.
SELECT LAST_NAME, to_char(HIRE_DATE, 'yyyy-mm-dd') as ippsa
FROM EMPLOYEES
WHERE to_char(HIRE_DATE, 'dd') < '16';
-- 13.
-- 14.
SELECT sa.LAST_NAME as sawon, ma.LAST_NAME as "s'manager",
ma.SALARY, G.GRADE_LEVEL
FROM EMPLOYEES sa, EMPLOYEES ma, JOB_GRADES G
WHERE sa.MANAGER_ID = ma.EMPLOYEE_ID
and ma.SALARY > 15000
AND ma.SALARY >= G.LOWEST_SAL
and ma.SALARY <= G.HIGHEST_SAL;
-- 16. 평높은 부서의 최저급여
SELECT DEPARTMENT_ID, min(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING avg(SALARY) = (SELECT max(avg(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);
-- 17 SA_REP 없는 부서번, 이름, 위치.
SELECT *
FROM EMPLOYEES E, DEPARTMENTS D
WHERE JOB_ID <> 'SA_REP';
-- 18 모든 사원번호, 이름, 급여, 부서번, 부서별 평균 급여
-- 3인 미만.
SELECT D.DEPARTMENT_ID as Dn, D.DEPARTMENT_NAME as DNAME, COUNT(*)
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP by D.DEPARTMENT_ID, D.DEPARTMENT_NAME
HAVING count(*) < 3;
SELECT B.DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM
(SELECT d.DEPARTMENT_ID, COUNT(*) cnt
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP by D.DEPARTMENT_ID
HAVING count(*) <3) A, DEPARTMENTS B
wHERE a.DEPARTMENT_ID = B.DEPARTMENT_ID;
-- 사원 가장많은.
-- 사원 가장 적은 부서
SELECT a.DEPARTMENT_ID, B.DEPARTMENT_NAME, cnt
FROM
(SELECT DEPARTMENT_ID, COUNT(*) cnt
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
having COUNT(*) =
(SELECT min(count(*))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)) A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
--19 패스
--20
SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'DAY')
FROM EMPLOYEES;
--WHERE to_char(HIRE_DATE, 'DAY') in (SELECT to_char(HIRE_DATE, 'DAY')
day;
SELECT *
FROM EMPLOYEES
WHERE to_char(HIRE_DATE, 'DAY')
in
(SELECT to_char(HIRE_DATE, 'DAY'), count(*)
FROM employees
GROUP
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
