7 1 2 SQL CREATE TABLE CREATE TABLE

7. 1. 2 SQL의 CREATE TABLE 명령 CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10, 2), [SINGLE, ] SUPERSSN CHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER)) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 7

7. 1. 2 SQL의 CREATE TABLE 명령 CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCE EMPLOYEE (SSN)) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 8

7. 1. 2 SQL의 CREATE TABLE 명령 CREATE TABLE DEPT_LOCATONS ( DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATON), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DNUMBER)) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 9

7. 1. 2 SQL의 CREATE TABLE 명령 CREATE TABLE PROJECT ( PNAME VARCHAR(15) NOT NULL, PNUMBER INT NOT NULL, PLOCATION VARCHAR(15), DNUM INT NOT NULL, PRIMARY KEY (PNUMBER), UNIQUE (PNAME), FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER)) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 10

7. 1. 2 SQL의 CREATE TABLE 명령 CREATE TABLE WORKS_ON ( ESSN CHAR(9) NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3, 1) [SINGLE, ] NOT NULL, PRIMARY KEY (ESSN, PNO), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN), FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER)) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 11

7. 1. 2 SQL의 CREATE TABLE 명령 CREATE TABLE DEPENDENT ( ESSN CHAR(9) NOT NULL, DEPENDENT_NAME VARCHAR(15) NOT NULL, SEX CHAR, BDATE, REATIONSHIP VARCHAR(8), PRIMARY KEY (ESSN, DEPENDENT_NAME), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN)) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 12

7. 2. 1 애트리뷰트 제약조건과 디폴트 값 명시 CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, … DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 15

7. 2. 1 애트리뷰트 제약조건과 디폴트 값 명시 CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL DEFAULT “ 888665555” MGRSTARTDATE, CONSTRAINT DEPTPK PRIMARY KEY (DNUMBER), CONSTRAINT DEPTSK UNIQUE (DNAME), CONSTRAINT DEPTMGRFK FOREIGN KEY (MGRSSN) REFERENCE EMPLOYEE (SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE) ; 해당 투플이 삭제되면 그 투플을 참조하 고 있는 모든 투플을 기본값으로 변경하 라 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 16

7. 2. 3 제약조건에 이름 부여 q CONSTRAINT 절에 이름 부여 예: CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), ON DELETE SET NULL ON UPDATE CASCADE; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 19

7. 4. 1 SQL 질의의 SELECT-FROM-WHERE 구조 q 질의 0 ü 이름이 ‘John B. Smith’인 사원의 생일(BDATE)과 주소(ADDRESS)를 검색 하시오. SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’ ; ü 관계대수 표현 ΠBDATE, ADDRESS(σNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’(EMPLOYEE)) 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 25

7. 4. 5 부분 문자열 패턴 비교와 산술 연산자 q 질의내 산술식 허용 q 질의 13 ü ‘Product. X’ 프로젝트에 참여하는 모든 사원의 급여를 10% 올린 경우의 급여 를 검색하라. SELECT FNAME, LNAME, 1. 1*SALARY [SELECT FNAME, LNAME, 1. 1*SALARY AS NEWSALARY] FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME=‘Product. X’ ; q 질의 14 ü 급여가 30, 000$에서 40, 000$ 사이에 있는 5번 부서의 모든 사원을 검색하라. SELECT * FROM EMPLOYEE WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO=5 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 38

7. 5. 2 중첩질의(nested query)와 집합비교 q 질의 4 A ü 성이 ‘Smith’인 종업원(일반 직원 혹은 프로젝트를 담당하는 부서의 관리자) 이 참여하는 프로젝트의 프로젝트 번호 목록을 작성하시오. SELECT DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN ( SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’) OR PNUMBER IN ( SELECT PNO FROM WORKS_ON, EMPLOYEE WHERE ESSN=SSN AND LNAME=‘Smith’) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 43

7. 5. 4 SQL의 EXISTS함수와 UNIQUE함수 q 질의 7 ü 부양가족이 적어도 한명 이상 있는 관리자의 이름을 검색하라. SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS ( SELECT * 부양가족 존재? FROM DEPENDENT WHERE SSN=ESSN) AND EXISTS ( SELECT * 관리부서 존재? FROM DEPARTMENT WHERE SSN=MGRSSN) ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 50

7. 5. 8 그룹핑: Group by와 Having 절 q 질의 28 ü 6명 이상의 사원이 근무하는 각 부서에 대해서 부서번호와 40, 000 달러가 넘 는 급여를 받는 사원의 수를 검색하라. SELECT DNUMBER, COUNT (*) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO AND SALARY > 40000 AND DNO IN ( SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT (*) >= 6) GROUP BY DNUMBER ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 65

7. 6. 1 INSERT 명령 q SELECT와 결합된 INSERT 명령 ü 질의의 결과로 생성된 다중 투플을 또 다른 릴레이션에 삽입하는 경우에 SELECT와 INSERT가 결합된 문장을 사용함 U 3 A: CREATE TABLE ( DEPT_NAME NO_OF_EMPS TOTAL_SAL U 3 B: INSERT INTO TOTAL_SAL) SELECT FROM WHERE GROUP BY DEPTS_INFO VARCHAR(15), INTEGER) ; DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, DNAME, COUNT (*), SUM (SALARY) DEPARTMENT, EMPLOYEE DNUMBER=DNO DNAME ; 충북대학교 구조시스템공학과 시스템공학연구실 - 공학데이터베이스 70