Contents

SQL 문법 한 장 (4·5장 전부)
0. 직접 돌려보기
1. 테이블 만들기 (DDL)
2. 행 넣기 · 지우기 · 고치기 (DML)
3. 찾기 (SELECT)
4. SELECT 안에 SELECT (중첩 질의)
5. 붙이기 (JOIN)
6. 세기 (집계 · GROUP BY · HAVING)
7. 규칙 걸기 (ASSERTION · TRIGGER)
8. 뷰 (VIEW)
9. 문제 문장 → 문법

SQL 문법 한 장 (4·5장 전부)

COMPANY 테이블 기준. < > 자리에 네 걸 넣으면 됨. 순서: 테이블 만들기 → 행 넣고 지우고 고치기 → 찾기(SELECT) → 안에 SELECT 또 넣기 → 붙이기(JOIN) → 세기(집계) → 규칙(ASSERTION·TRIGGER) → 뷰

0. 직접 돌려보기

교재 COMPANY 데이터가 들어 있는 SQLite가 브라우저 안에서 돌아감. 예제를 고르거나 쿼리를 직접 고쳐서 실행하면 결과와 테이블이 어떻게 바뀌는지 바로 보임. 변경은 실행할수록 쌓이고, 처음 상태로를 누르면 시작 데이터로 돌아감

SQLite 불러오는 중…

1. 테이블 만들기 (DDL)

CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

CREATE TABLE <테이블>
  ( <열>  <타입>  [NOT NULL]  [DEFAULT <값>],
    …
  CONSTRAINT <이름>  PRIMARY KEY (<열>[, <열>]),
  CONSTRAINT <이름>  UNIQUE (<열>),
  CONSTRAINT <이름>  FOREIGN KEY (<열>) REFERENCES <테이블>(<열>)
                     [ON DELETE <행동>] [ON UPDATE <행동>] );

CREATE TABLE DEPARTMENT
  ( Dname           VARCHAR(15)  NOT NULL,
    Dnumber         INT          NOT NULL,
    Mgr_ssn         CHAR(9)      NOT NULL  DEFAULT '888665555',
    Mgr_start_date  DATE,
  CONSTRAINT DEPTPK     PRIMARY KEY (Dnumber),
  CONSTRAINT DEPTSK     UNIQUE (Dname),
  CONSTRAINT DEPTMGRFK  FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
                        ON DELETE SET DEFAULT  ON UPDATE CASCADE );

타입	뭐	예
`INT`	정수	Dnumber, Salary
`CHAR(n)`	길이 딱 n 글자	Ssn CHAR(9), Sex CHAR
`VARCHAR(n)`	최대 n 글자	Fname VARCHAR(15)
`DATE`	날짜 '1965-01-09'	Bdate
`DECIMAL(p, s)`	소수. 전체 p자리 중 소수 s자리	Hours DECIMAL(3,1)

줄	뜻
`NOT NULL`	빈 칸 금지. PK 열은 안 써도 자동
`DEFAULT <값>`	값 안 주면 이걸로 채움
`PRIMARY KEY (…)`	기본키. 둘 이상이면 `(Essn, Pno)`
`UNIQUE (…)`	PK는 아닌데 겹치면 안 되는 열
`FOREIGN KEY (a) REFERENCES T(b)`	a에는 T.b에 있는 값만
`CONSTRAINT <이름>`	제약에 이름. 나중에 고치거나 지울 때

FK가 가리키는 행이 지워지거나 PK가 바뀌면 (FK 줄 뒤에 붙임)

행동	ON DELETE 때 FK 칸	언제
안 씀	삭제 거부	아래가 있는 한 위를 못 지우게
`SET NULL`	NULL로	아래는 남기되 연결만 끊기
`SET DEFAULT`	DEFAULT 값으로	아래는 남기되 기본값으로 옮기기
`CASCADE`	그 행도 같이 삭제	위 없어지면 아래도 필요 없음

ON UPDATE CASCADE: 상대 PK 바뀌면 FK도 새 값으로. ON UPDATE는 거의 이거

2. 행 넣기 · 지우기 · 고치기 (DML)

INSERT INTO <테이블> VALUES (<값>, <값>, …);                       -- CREATE TABLE 순서대로 전부
INSERT INTO <테이블> (<열>, <열>, …) VALUES (<값>, <값>, …);       -- 고른 열만, 그 순서대로

INSERT INTO EMPLOYEE
VALUES ('Richard', 'K', 'Marini', '653298653', '1962-12-30', '98 Oak Forest, Katy, TX', 'M', 37000, '653298653', 4);

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
VALUES ('Richard', 'Marini', 4, '653298653');

열 골라 넣을 땐 NOT NULL인데 DEFAULT 없는 열은 꼭 / 없는 Dno, 빠진 Ssn, 겹치는 Ssn → 거부

DELETE FROM <테이블> WHERE <조건>;        -- WHERE 빼면 전부 삭제

DELETE FROM EMPLOYEE WHERE Ssn = '123456789';
DELETE FROM EMPLOYEE WHERE Dno = 5;

UPDATE <테이블> SET <열> = <값>, <열> = <값> WHERE <조건>;

UPDATE PROJECT  SET Plocation = 'Bellaire', Dnum = 5  WHERE Pnumber = 10;
UPDATE EMPLOYEE SET Salary = Salary * 1.1             WHERE Dno = 5;

값 자리에 계산식 됨 / DELETE·UPDATE 둘 다 FK에 CASCADE 있으면 다른 테이블까지 바뀜

3. 찾기 (SELECT)

SELECT   [DISTINCT] <열>, <열>, <계산식> AS <이름>
FROM     <테이블> [AS <별명>], <테이블> [AS <별명>]
WHERE    <행 조건> AND <테이블 잇는 조건>
ORDER BY <열> [ASC | DESC], <열>;

쓰는 순서: 뭐 보일지 → 어느 테이블에 있는지 → 조건 → 테이블 둘 이상이면 FK = PK로 잇기 (n개면 n−1개)

SELECT Bdate, Address
FROM   EMPLOYEE
WHERE  Fname = 'John' AND Minit = 'B' AND Lname = 'Smith';

SELECT Fname, Lname, Address
FROM   EMPLOYEE, DEPARTMENT
WHERE  Dname = 'Research' AND Dnumber = Dno;

Dnumber = Dno가 잇는 조건. 빼먹으면 8 × 3 = 24행 다 나옴

쓰는 법	뜻	예
`*`	열 전부	`SELECT * FROM EMPLOYEE WHERE Dno = 5`
`<테이블>.<열>`	같은 열 이름이 두 테이블에 있을 때	`EMPLOYEE.Name`, `DEPARTMENT.Name`
`AS <별명>`	테이블 짧게. 같은 테이블 두 번 쓸 땐 필수	`FROM EMPLOYEE AS E` → `E.Fname`
`<식> AS <이름>`	결과 열 이름	`1.1 * Salary AS Increased_sal`
`BETWEEN a AND b`	`>= a AND <= b`	`Salary BETWEEN 30000 AND 40000`
`DISTINCT`	겹치는 행 지움. 안 쓰면 `ALL`(남김)	`SELECT DISTINCT Salary`
`ORDER BY`	정렬. 기본 ASC, 내림차순 DESC. 앞 열 같으면 다음 열	`ORDER BY Dname DESC, Lname ASC`

SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM   EMPLOYEE AS E, EMPLOYEE AS S
WHERE  E.Super_ssn = S.Ssn;

직원 + 상사 이름. 같은 테이블을 E(직원)·S(상사) 둘로

SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM   EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE  E.Ssn = W.Essn AND W.Pno = P.Pnumber AND P.Pname = 'ProductX';

집합 연산: SELECT 결과 둘을 합침. 열 개수·타입 같아야 함. 중복은 지움, 남기려면 ALL

( SELECT … ) UNION ( SELECT … );        -- 합집합
( SELECT … ) EXCEPT ( SELECT … );       -- 차집합
( SELECT … ) INTERSECT ( SELECT … );    -- 교집합
( SELECT … ) UNION ALL ( SELECT … );    -- 중복 안 지움

( SELECT DISTINCT Pnumber
  FROM   PROJECT, DEPARTMENT, EMPLOYEE
  WHERE  Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith' )
UNION
( SELECT DISTINCT Pnumber
  FROM   PROJECT, WORKS_ON, EMPLOYEE
  WHERE  Pnumber = Pno AND Essn = Ssn AND Lname = 'Smith' );

Smith가 관리자인 프로젝트 ∪ Smith가 일하는 프로젝트

4. SELECT 안에 SELECT (중첩 질의)

SELECT … FROM … WHERE <값> <비교> ( SELECT … );

비교	참일 때	예
`v IN (…)`	v가 안쪽 결과에 있음	`Pnumber IN (SELECT Pno FROM WORKS_ON WHERE …)`
`v NOT IN (…)`	없음
`v > ALL (…)`	안쪽 결과 전부보다 큼	`Salary > ALL (SELECT Salary FROM EMPLOYEE WHERE Dno = 5)`
`v = ANY (…)`	하나라도 같음 (= IN)	`Pnumber = ANY (SELECT …)`
`EXISTS (…)`	안쪽 결과가 한 행이라도 있음	부양가족 있는 직원
`NOT EXISTS (…)`	안쪽 결과가 비어 있음	부양가족 없는 직원

ALL·ANY 앞엔 =, >, >=, <, <=, <> 다 됨

SELECT DISTINCT Pnumber
FROM   PROJECT
WHERE  Pnumber IN ( SELECT Pnumber
                    FROM   PROJECT, DEPARTMENT, EMPLOYEE
                    WHERE  Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith' )
    OR Pnumber IN ( SELECT Pno
                    FROM   WORKS_ON, EMPLOYEE
                    WHERE  Essn = Ssn AND Lname = 'Smith' );

위 UNION을 IN으로

안쪽에서 바깥 별명을 쓰면 바깥 행 하나마다 안쪽을 다시 돌림. 별명 필수

SELECT E.Fname, E.Lname
FROM   EMPLOYEE AS E
WHERE  E.Ssn IN ( SELECT D.Essn
                  FROM   DEPENDENT AS D
                  WHERE  E.Fname = D.Dependent_name AND E.Sex = D.Sex );

자기랑 이름·성별 같은 부양가족 있는 직원

SELECT Fname, Lname
FROM   EMPLOYEE
WHERE  NOT EXISTS ( SELECT * FROM DEPENDENT WHERE Ssn = Essn );

SELECT Fname, Lname
FROM   EMPLOYEE
WHERE  EXISTS ( SELECT * FROM DEPENDENT  WHERE Ssn = Essn )
  AND  EXISTS ( SELECT * FROM DEPARTMENT WHERE Ssn = Mgr_ssn );

부양가족 없는 직원 / 부양가족 있는 관리자. EXISTS는 있나 없나만 보니까 안쪽은 SELECT *

"~ 전부에 참여하는": (전부) EXCEPT (그 사람 것)이 비어야 함 → NOT EXISTS ( (SELECT …) EXCEPT (SELECT …) )

SELECT Fname, Lname
FROM   EMPLOYEE
WHERE  NOT EXISTS ( ( SELECT Pnumber FROM PROJECT WHERE Dnum = 5 )
                    EXCEPT
                    ( SELECT Pno FROM WORKS_ON WHERE Essn = Ssn ) );

5번 부서 프로젝트 전부에 참여하는 직원

5. 붙이기 (JOIN)

SELECT … FROM ( <테이블> [INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER] JOIN <테이블> ON <잇는 조건> ) WHERE …;

SELECT Fname, Lname, Address
FROM   (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE  Dname = 'Research';

잇는 조건을 WHERE 말고 ON에. 결과 열 = 첫 테이블 열 + 둘째 테이블 열

종류	결과
`JOIN` (= INNER JOIN)	양쪽 다 짝 있는 행만
`LEFT OUTER JOIN`	왼쪽 행 전부, 짝 없으면 오른쪽 칸 NULL
`RIGHT OUTER JOIN`	오른쪽 행 전부
`FULL OUTER JOIN`	양쪽 전부

SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM   (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON E.Super_ssn = S.Ssn);

JOIN이면 상사 없는 Borg 빠짐 / LEFT OUTER JOIN이면 Borg도 나오고 상사 칸 NULL

6. 세기 (집계 · GROUP BY · HAVING)

SELECT   <묶는 열>, <집계 함수>
FROM     …
WHERE    <행 조건>
GROUP BY <묶는 열>
HAVING   <묶음 조건>;

함수	뭐	주의
`COUNT(*)`	행 수
`COUNT(<열>)`	NULL 아닌 값 수	`COUNT(DISTINCT Salary)` 서로 다른 값 수
`SUM`, `AVG`	합, 평균	숫자 열만
`MAX`, `MIN`	최대, 최소

SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM   (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE  Dname = 'Research';

GROUP BY 없으면 전체가 한 묶음 → 결과 한 줄

SELECT   Dno, COUNT(*), AVG(Salary)
FROM     EMPLOYEE
GROUP BY Dno;

"부서별", "프로젝트마다" → GROUP BY. SELECT엔 GROUP BY 쓴 열 + 집계 함수만

SELECT   Pnumber, Pname, COUNT(*)
FROM     PROJECT, WORKS_ON
WHERE    Pnumber = Pno
GROUP BY Pnumber, Pname
HAVING   COUNT(*) > 2;

WHERE는 행을 거르고 HAVING은 묶음을 거름. WHERE에 COUNT 쓰면 에러

SELECT Fname, Lname
FROM   EMPLOYEE
WHERE  ( SELECT COUNT(*) FROM DEPENDENT WHERE Ssn = Essn ) >= 2;

집계를 WHERE 안 중첩 질의로. 부양가족 2명 이상

실행 순서: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

7. 규칙 걸기 (ASSERTION · TRIGGER)

CREATE ASSERTION <이름>
CHECK ( NOT EXISTS ( SELECT … 어기는 행 … ) );

CREATE ASSERTION SALARY_CONSTRAINT
CHECK ( NOT EXISTS ( SELECT *
                     FROM   EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
                     WHERE  E.Salary > M.Salary
                       AND  E.Dno = D.Dnumber
                       AND  D.Mgr_ssn = M.Ssn ) );

어기는 행을 찾는 SELECT를 NOT EXISTS로 감쌈 = "그런 행 없어야 함". 직원 급여 > 부서장 급여 되는 변경은 거부

CREATE TRIGGER <이름>
BEFORE | AFTER  INSERT | DELETE | UPDATE [OF <열>]  ON <테이블>
FOR EACH ROW
WHEN ( <조건> )
  <행동>;

CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN ( NEW.SALARY > ( SELECT SALARY FROM EMPLOYEE
                      WHERE  SSN = NEW.SUPERVISOR_SSN ) )
  INFORM_SUPERVISOR(NEW.Supervisor_ssn, NEW.Ssn);

부분	코드	뜻
Event	`BEFORE INSERT OR UPDATE OF … ON EMPLOYEE`	어떤 명령 전·후에
Condition	`WHEN (…)`	안 쓰면 매번 실행
Action	`INFORM_SUPERVISOR(…)`	SQL문, 트랜잭션, 외부 프로그램

NEW.<열>은 넣거나 바뀐 뒤 값 / Assertion은 막는 거, Trigger는 뭔가 하는 거

8. 뷰 (VIEW)

CREATE VIEW <이름> [(<열>, <열>, …)] AS SELECT …;
SELECT … FROM <뷰> WHERE …;
DROP VIEW <이름>;

CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
   FROM   EMPLOYEE, PROJECT, WORKS_ON
   WHERE  Ssn = Essn AND Pno = Pnumber;

CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)
AS SELECT   Dname, COUNT(*), SUM(Salary)
   FROM     DEPARTMENT, EMPLOYEE
   WHERE    Dnumber = Dno
   GROUP BY Dname;

열 이름은 SELECT 그대로. COUNT·SUM처럼 이름 없으면 앞에 (…)로 직접

SELECT Fname, Lname FROM WORKS_ON1 WHERE Pname = 'ProductX';
DROP VIEW WORKS_ON1;

뷰는 저장 안 됨, 질의할 때 원본에서 만들어짐 / DROP VIEW는 원본 안 건드림

뷰에 INSERT·DELETE·UPDATE

뷰	갱신
테이블 하나 + 집계 없음 + 원본 PK·NOT NULL 열 다 있음	됨
테이블 둘 이상 join	안 됨 (어느 테이블 고칠지 여러 가지)
GROUP BY·집계 함수	안 됨 (계산한 값)

CREATE VIEW DEPT5EMP       AS SELECT * FROM EMPLOYEE WHERE Dno = 5;
CREATE VIEW BASIC_EMP_DATA AS SELECT Fname, Lname, Address FROM EMPLOYEE;

권한용. 원본 권한 안 주고 뷰 권한만 주면 행(5번 부서만)·열(이름·주소만) 숨김

9. 문제 문장 → 문법

문제에 이 말 나오면	쓰는 것
테이블 둘 이상의 열	FROM에 둘 다, WHERE에 FK = PK (또는 JOIN ON)
직원과 그 상사	같은 테이블 두 번, `AS E`, `AS S`
중복 없이	`DISTINCT`
~별, ~마다	`GROUP BY`
~인 부서만 (묶음에 조건)	`HAVING`
몇 명, 합, 평균, 최대	`COUNT`, `SUM`, `AVG`, `MAX`
~보다 많이 받는 (비교값을 DB에서)	중첩 질의 `> ALL (SELECT …)`
~에 속한, ~한 적 있는	`IN (SELECT …)` / `EXISTS`
~가 없는	`NOT EXISTS` / `NOT IN`
전부에 참여하는	`NOT EXISTS ( … EXCEPT … )`
짝 없는 행도 보여야 (상사 없는 직원도)	`LEFT OUTER JOIN`
둘 다 / 둘 중 하나 / 하나만	`INTERSECT` / `UNION` / `EXCEPT`
정렬, 높은 순	`ORDER BY … DESC`
급여 10% 인상	`UPDATE … SET Salary = Salary * 1.1`
지우면 같이 지워지게	`ON DELETE CASCADE`
항상 지켜야 하는 규칙	`CREATE ASSERTION … CHECK (NOT EXISTS …)`
~할 때 자동으로	`CREATE TRIGGER`
자주 쓰는 질의에 이름	`CREATE VIEW`