CREATE TABLE 16 SQL99 CREATE TABLE EMPLOYEE FNAME

CREATE TABLE 사용 예제 (1/6) SQL-99 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), SUPERSSN CHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER)) ; Page 6 Database by Yang-Sae Moon

CREATE TABLE 사용 예제 (2/6) SQL-99 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)) ; Page 7 Database by Yang-Sae Moon

CREATE TABLE 사용 예제 (3/6) SQL-99 CREATE TABLE DEPT_LOCATONS ( DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATON), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DNUMBER)); Page 8 Database by Yang-Sae Moon

CREATE TABLE 사용 예제 (4/6) SQL-99 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)) ; Page 9 Database by Yang-Sae Moon

CREATE TABLE 사용 예제 (5/6) SQL-99 CREATE TABLE WORKS_ON ( ESSN CHAR(9) NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3, 1) NOT NULL, PRIMARY KEY (ESSN, PNO), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN), FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER)) ; * DECIMAL(3, 1): 에서 3는 세 자리, 1은 최소 한 시간 Page 10 Database by Yang-Sae Moon

CREATE TABLE 사용 예제 (6/6) SQL-99 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)) ; Page 11 Database by Yang-Sae Moon

SQL에서 애트리뷰트 타입과 도메인 (1/2) SQL-99 애트리뷰트의 데이터 타입 • 정수: INTEGER, INT, SMALLINT • 실수: FLOAT, REAL, DOUBLE PRECISION • 고정길이 문자열: CHAR(n), CHARACTER(n) • 가변길이 문자열: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) (문자열 연결 연산: ||, 예: ‘abc’||’XYZ’는 ‘abc. XYZ’와 동일함) • 비트열: BIT(n) -- 고정길이, BIT VARYING(n) -- 가변길이 • 불리언: BOOLEAN -- True, False, Unknown • 날짜와 시간: DATE(YYYY-MM-DD), TIME(HH: MM: SS) • 타임스탬프: DATE+TIME+초의 소수점 이하 자리 (예: 2002 -09 -27 09: 12: 47 648302) Page 12 Database by Yang-Sae Moon

제약조건에 이름 부여 SQL-99 CONSTRAINT 절에 이름 부여 예: CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), ON DELETE SET NULL ON UPDATE CASCADE; Page 18 Database by Yang-Sae Moon

키 제약조건과 디폴트 값 명시 예 (1/3) SQL-99 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) ; Page 19 Database by Yang-Sae Moon

키 제약조건과 디폴트 값 명시 예 (2/3) SQL-99 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) REFERENCES EMPLOYEE (SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE) ; 해당 투플이 삭제되면 그 투플을 참조하고 있는 모 든 투플을 기본값으로 변경하라 Page 20 Database by Yang-Sae Moon

키 제약조건과 디폴트 값 명시 예 (3/3) SQL-99 CREATE TABLE DEPT_LOCATONS ( DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATON), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DNUMBER) ON DELETE CASCADE ON UPDATE CASCADE) ; 해당 투플이 삭제되면 그 투플 을 참조하고 있는 모든 투플을 삭제하라 해당 투플이 변경되면 그 투플 을 참조하고 있는 모든 투플을 변경하라 Page 21 Database by Yang-Sae Moon

ALTER TABLE (2/2) SQL-99 예 1: EMPLOYEE 테이블에 JOB 애트리뷰트 추가 • ALTER TABLE COMPANY. EMPLOYEE ADD JOB VARCHAR(12); 예 2: EMPLOYEE 테이블에 ADDRESS 애트리뷰트 제거 • ALTER TABLE COMPANY. EMPLOYEE DROP ADDRESS CASCADE; 예 3: EMPLOYEE 테이블의 MGRSSN 애트리뷰트 DEFAULT 절 변경 • ALTER TABLE COMPANY. EMPLOYEE ALTER MGRSSN DROP DEFAULT; • ALTER TABLE COMPANY. EMPLOYEE ALTER COLUMN MGRSSN SET DEFAULT “ 333445555”; Page 27 Database by Yang-Sae Moon

SELECT-FROM-WHERE 예제 (1/3) SQL-99 질의 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)) Page 31 Database by Yang-Sae Moon

SELECT-FROM-WHERE 예제 (2/3) SQL-99 질의 1 • ‘Research’ 부서에서 일하는 모든 사원의 이름(FNAME, LNAME)과 주소를 검색하시오. SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME=‘Research’ AND DNUMBER=DNO ; ü 관계대수 연산 SELECT-PROJECT-JOIN과 유사 ü SELECT 절은 관계 대수의 PROJECT 연산에 해당 ü WHERE 절에서 DNAME=‘Research’은 선택조건, 관계대수에서 SELECT 연산에 해당 ü WHERE 절에서 DNUMBER=DNO는 조인조건, 관계대수의 JOIN 연산에 해당 Page 32 Database by Yang-Sae Moon

SELECT-FROM-WHERE 예제 (3/3) SQL-99 질의 2 • ‘Stafford’에 위치한 모든 프로젝트에 대하여 프로젝트 번호, 담당부서 번호, 부서 관리자의 성, 주소, 생일을 검색하라. SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION=‘Stafford’ ; • 두 개의 조인조건이 존재 ü 조인조건 DNUM=DNUMBER는 프로젝트와 담당 부서를 조인 ü 조인조건 MGRSSN=SSN은 부서와 담당 관리자를 조인 Page 33 Database by Yang-Sae Moon

WHERE의 생략과 ‘ ’의 사용 (3/3) SQL-99 질의 1 D • ‘Research’ 부서에서 일하는 모든 종업원들에 대하여 EMPLOYEE의 모든 애트리뷰트들과 DEPARTMENT의 모든 애트리뷰트들을 검색하라. SELECT FROM EMPLOYEE, DEPARTMENT WHERE DNAME=‘Research’ AND DNO=DNUMBER ; 질의 10 A • 릴레이션 EMPLOYEE와 DEPARTMENT의 카티션 프로덕트 결과를 모두 검색하라. SELECT FROM EMPLOYEE, DEPARTMENT ; Page 39 Database by Yang-Sae Moon

SQL에서 집합으로서의 테이블 (2/2) SQL-99 SQL에서의 집합 연산 • 합집합(UNION), 차집합(EXCEPT), 교집합(INTERSECT) • 릴레이션에 대한 집합 연산의 결과는 투플들의 집합임 → 중복된 투플을 결과에서 제거함 질의 4 • 성이 'Smith'인 종업원(일반 직원 혹은 프로젝트를 담당하는 부서의 관리자)이 참여하는 프 로젝트의 프로젝트 번호 목록을 작성하시오. ( SELECT PNUMBER // Smith가 관리자인 projects FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION ( SELECT PNUMBER // Smith가 참여하는 projects FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith') ; Page 41 Database by Yang-Sae Moon

부분 문자열 패턴 비교와 산술 연산자 (2/3) SQL-99 질의 12 • 주소가 Houston, Texas인 모든 종업원을 검색하시오. SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston, TX%’ ; [WHERE ADDRESS LIKE ‘*Houston, TX*’ ; ] // 다른 버전의 예 질의 12 A • 1950년대에 태어난 모든 사원을 검색하라. SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE ‘__5____’ ; [WHERE BDATE LIKE ‘? ? 5? ? ? ? ’ ; ] Page 43 // 다른 버전의 예 Database by Yang-Sae Moon

부분 문자열 패턴 비교와 산술 연산자 (3/3) SQL-99 질의內 산술식 허용 질의 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’ ; 질의 14 • 급여가 30, 000$에서 40, 000$ 사이에 있는 5번 부서의 모든 사원을 검색하라. SELECT * FROM EMPLOYEE WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO=5 Page 44 Database by Yang-Sae Moon

질의 결과의 정렬 (2/2) SQL-99 질의 15 • 프로젝트에 참여하는 종업원을 부서의 알파벳 순서대로, 각 부서 내에서는 성과 이름의 알 파벳 순서대로 출력하시오. SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME, FNAME ; Page 46 Database by Yang-Sae Moon

중첩질의(nested query)와 집합비교 (2/5) 질의 4 A ( SELECT • 성이 ‘Smith’인 종업원(일반 직원 혹은 프로젝트를 담당하는 부서의 관리자)이 참여하는 프로젝트의 프로젝트 번호 목록을 작성하시오. DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN ( SELECT FROM // Smith가 관리자인 projects FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION ( SELECT PNUMBER SQL-99 PNUMBER // Smith가 참여하는 projects FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith') ; PNUMBER PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’) OR PNUMBER IN ( SELECT FROM PNO WORKS_ON, EMPLOYEE WHERE ESSN=SSN AND LNAME=‘Smith’) ; Page 51 Database by Yang-Sae Moon

중첩질의(nested query)와 집합비교 (3/5) SQL-99 질의: SSN이 123456789인 사원이 일하는 프로젝트와 일한 시간의 조합이 동일한 사원의 SSN을 검색하라. SELECT DISTINCT ESSN FROM WORKS_ON WHERE (PNO, HOURS) IN ( SELECT PNO, HOURS FROM WORKS_ON WHERE SSN=‘ 123456789’); Page 52 Database by Yang-Sae Moon

상관 중첩질의(correlated nested query) (2/2) SQL-99 질의 16을 비중첩 질의로 변환한 예: 질의 16 A • 자신의 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하시오. SELECT E. FNAME, E. LNAME FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E. SSN=D. ESSN AND E. FNAME=DEPENDENT_NAME AND E. SEX=D. SEX ; Page 56 Database by Yang-Sae Moon

EXISTS 함수 (1/4) SQL-99 EXISTS 함수 • 상관된 중첩질의에서 내부 질의의 결과가 공집합인가를 검사함 • EXISTS(Q): 질의 Q의 결과에 최소한 한 개의 투플이 있다면 참을 반환 질의 16 B • 자신의 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하시오. SELECT E. FNAME, E. LNAME FROM EMPLOYEE E WHERE EXISTS ( SELECT * FROM DEPENDENT WHERE E. SSN=ESSN AND SEX=E. SEX AND E. FNAME=DEPENDENT_NAME) ; Page 57 Database by Yang-Sae Moon

EXISTS 함수 (2/4) SQL-99 NOT EXIST 함수 • 상관된 중첩질의에서 내부 질의의 결과가 공집합인가를 검사함 • NOT EXISTS(Q): 질의 Q의 결과에 투플이 없다면 참을 반환 질의 6 • 부양가족이 없는 종업원들의 이름을 검색하시오. SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS ( SELECT * FROM DEPENDENT WHERE SSN=ESSN) ; Page 58 Database by Yang-Sae Moon

EXISTS 함수 (3/4) SQL-99 질의 7 • 부양가족이 적어도 한명 이상 있는 관리자의 이름을 검색하라. SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS ( SELECT * FROM DEPENDENT WHERE SSN=ESSN) AND EXISTS ( SELECT * FROM DEPARTMENT WHERE SSN=MGRSSN) ; Page 59 Database by Yang-Sae Moon

SQL에서 조인된 테이블 SQL-99 FROM 절에 조인 연산의 결과를 지정 • 질의의 FROM 절에 조인연산의 결과를 지정할 수 있음 질의 1 A • ‘Research’ 부서에서 일하는 모든 종업원들의 이름과 주소를 검색하시오. SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER) WHERE DNAME=‘Research’ ; 질의 1 B SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT (DNAME, DNO, MSSN, MSDATE))) WHERE DNAME=‘Research’ ; Page 63 Database by Yang-Sae Moon

집단 함수 (Aggregate Function) (3/4) SQL-99 COUNT( ): 투플들의 수를 반환 질의 21 • 회사내의 총 종업원의 수를 검색하시오. SELECT COUNT ( ) FROM EMPLOYEE ; 질의 22 • ‘Research’ 부서에 속해 있는 종업원의 수를 검색하시오. SELECT COUNT ( ) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME=‘Research’ ; Page 66 Database by Yang-Sae Moon

집단 함수 (Aggregate Function) (4/4) SQL-99 특정 투플을 선택하기 위해 집단함수 이용: 중첩 질의 이용 질의 5 • 둘 이상의 부양가족이 있는 모든 사원의 이름을 검색하시오. SELECT LNAME, FNAME FROM EMPLOYEE WHERE (SELECT COUNT ( ) FROM DEPENDENT WHERE SSN=ESSN ) >= 2 ; Page 67 Database by Yang-Sae Moon

Grouping: Group by & Having (3/7) SQL-99 질의 25 • 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 검색하라. SELECT PNUMBER, PNAME, COUNT ( ) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER ; Page 70 Database by Yang-Sae Moon

Grouping: Group by & Having (4/7) SQL-99 Having: GROUP BY 절에 대한 조건을 제시할 수 있음 질의 26 • 세 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로 젝트에서 근무하는 사원의 수를 검색하라. SELECT PNUMBER, PNAME, COUNT( ) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER HAVING COUNT( ) >= 3 ; 각 그룹에 대해 조건 체크 Page 71 Database by Yang-Sae Moon

Grouping: Group by & Having (5/7) Page 72 SQL-99 Database by Yang-Sae Moon

Grouping: Group by & Having (6/7) SQL-99 질의 27 • 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 5번 부서에 속하면서 프로젝트에서 근무하는 사원의 수를 검색하라. SELECT PNUMBER, PNAME, COUNT ( ) FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND SSN=ESSN AND DNO=5 GROUP BY PNUMBER ; Page 73 Database by Yang-Sae Moon

Grouping: Group by & Having (7/7) - 생략 SQL-99 질의 28 • 6명 이상의 사원이 근무하는 각 부서에 대해서 부서번호와 40, 000 달러가 넘는 급여를 받 는 사원의 수를 검색하라. SELECT DNUMBER, COUNT ( ) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO AND SALARY > 40000 AND DNO IN ( SELECT FROM DNO EMPLOYEE GROUP BY DNO HAVING GROUP BY COUNT ( ) >= 6) DNUMBER ; Page 74 Database by Yang-Sae Moon

삽입, 삭제, 갱신 구문 SQL-99 INSERT 명령 DELETE 명령 UPDATE 명령 Page 77 Database by Yang-Sae Moon

INSERT 구문 (3/3) SQL-99 SELECT와 결합된 INSERT 명령 • 질의의 결과로 생성된 다중 투플을 또 다른 릴레이션에 삽입하는 경우에 SELECT와 INSERT가 결합된 문장을 사용함 U 3 A: CREATE TABLE DEPTS_INFO ( DEPT_NAME U 3 B: VARCHAR(15), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER) ; INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT ( ), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME ; Page 80 Database by Yang-Sae Moon

DELETE 구문 SQL-99 DELETE 명령은 릴레이션에서 투플(들)을 제거하는 명령임 • 삭제할 투플에 대한 조건은 WHERE 절에서 명시함 • 한번의 DELETE 명령으로 WHERE 절의 조건을 만족하는 투플을 모두 삭제함 U 4 A: DELETE FROM EMPLOYEE WHERE U 4 B: DELETE FROM EMPLOYEE WHERE U 4 C: LNAME=‘Brown’ ; SSN=‘ 123456789’ ; DELETE FROM EMPLOYEE WHERE DNO IN ( SELECT DNUMBER FROM DEPARTMENT WHERE DNAME=‘Research’ ) ; • WHERE 절을 생략한 경우에는 테이블내의 모든 투플을 삭제하며, 테이블은 데이터베이스 내에서 빈 테이블로 남게 됨 U 4 D: DELETE FROM EMPLOYEE; Page 81 Database by Yang-Sae Moon

UPDATE 구문 (2/2) SQL-99 • 예: ‘Research’ 부서에 있는 모든 종업원들의 봉급을 10％ 인상하라. U 6: UPDATE EMPLOYEE SET SALARY=SALARY*1. 1 WHERE DNO IN ( SELECT DNUMBER FROM DEPARTMENT WHERE DNAME=‘Research’) ; Page 83 Database by Yang-Sae Moon

권한 기능의 예제 화면 SQL-99 Page 86 Database by Yang-Sae Moon

Embedded SQL 예제: C SQL-99 Page 87 Database by Yang-Sae Moon

Embedded SQL 예제: PHP SQL-99 <html> <body> <? PHP $conn = mysql_connect (‘localhost’, ‘root’, ‘apmsetup’); $db_status = mysql_select_db (‘goods’); if (!$db_status) { error (“DB_ERROR”); exit; } $query = “INSERT INTO fruit VALUES “. “(‘$name’, $price, ‘$color’, ‘$country’)”; $result = mysql_query ($query); if ($result) print “입력되었습니다. ”; else print “입력되지 않았습니다. ”; ? > </body> </html> Page 88 Database by Yang-Sae Moon

트랜젹션 제어 명령문 SQL-99 Page 89 Database by Yang-Sae Moon

SQL VIEW (2/2) SQL-99 Page 91 Database by Yang-Sae Moon

기타 명령어 (인덱스 생성/삭제) Page 92 SQL-99 Database by Yang-Sae Moon

Homework #3 SQL-99 Page 94 Database by Yang-Sae Moon