[KOSTA] Spring 기반 Cloud 서비스 개발자 양성 과정 26일차 - SQL 제약 조건, 조인 실습

🎲 제약 조건을 지정해 테이블 만들기

CREATE TABLE DEPT_CONST (
    DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13));
    
ALTER TABLE DEPT_CONST ADD CONSTRAINT DEPTCONST_DEPTNO_PK PRIMARY KEY(DEPTNO);
ALTER TABLE DEPT_CONST ADD CONSTRAINT DEPTCONST_DNAME_UNQ UNIQUE(DNAME);
ALTER TABLE DEPT_CONST MODIFY LOC CONSTRAINT DEPTCONST_LOC_NN NOT NULL; 

CREATE TABLE EMP_CONST (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    TEL VARCHAR2(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 EMPCON_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT_CONST(DEPTNO);

INSERT INTO DEPT_CONST VALUES (1, '회계', '3층');
INSERT INTO EMP_CONST VALUES (1234,'PARK','DEVELOPER','111-222','16/02/03','50000','1000',1); /*체크 확인용*/
INSERT INTO EMP_CONST VALUES (1234,'PARK','DEVELOPER','111-222','16/02/03','5000','1000',1);
INSERT INTO EMP_CONST VALUES (1234,'PARK','DEVELOPER','111-222','16/02/03','50000','1000',2); /* FK 확인용 */

🎲 '송강' 교수가 강의 하는 과목을 검색

SELECT P.PNO, P.PNAME, C.CNAME
FROM PROFESSOR P , COURSE C
WHERE P.PNO = C.PNO
AND P.PNAME = '송강';

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

SELECT P.PNAME, C.ST_NUM, C.CNAME
FROM PROFESSOR P, COURSE C
WHERE P.PNO = C.PNO
AND ST_NUM = 2;

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

SELECT ST.SYEAR, ST.MAJOR, ST.SNAME, SC.RESULT
FROM STUDENT ST, SCORE SC
WHERE ST.SNO = SC.SNO
AND SYEAR = 1
AND MAJOR = '화학';

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

SELECT S.SYEAR, S.MAJOR, S.SNAME, C.CNAME
FROM STUDENT S, COURSE C, SCORE SC
WHERE S.SNO = SC.SNO
AND SC.CNO = C. CNO
AND S.SYEAR = 1
AND S.MAJOR = '화학';

🎲 3개 이상 테이블을 조인하여 사원이름, 이메일, 부서번호, 부서이름, 직종번호, 직종이름을 출력

/* WHERE절 */
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;

/* ANSI */
SELECT E.LAST_NAME, E.EMAIL, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, J.JOB_ID, J.JOB_TITLE
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN JOBS J
ON E.JOB_ID = J.JOB_ID;

🎲 등록된 과목에 대한 모든 교수를 검색(누락된 교수가 없도록 출력)

/* WHERE절 */
SELECT C.CNO, C.CNAME, C.ST_NUM, P.PNAME
FROM COURSE C, PROFESSOR P
WHERE C.PNO(+) = P.PNO;

/* ANSI */
SELECT C.CNO, C.CNAME, C.ST_NUM, P.PNAME
FROM COURSE C RIGHT JOIN PROFESSOR P 
ON C.PNO = P.PNO;