DB 중간고사 대비

<, <=, >, >=, =, <>(!=를 의미)

• WHERE salary > 70000

BETWEEN A AND B

• WHERE salary BETWEEN 900 AND 1000

Tuple comparison

• WHERE (instruction.ID, dept_name) = (teahces.ID, 'Biology');

IS NULL

• WHERE salary IS NULL

IS NOT NULL

• WHERE salary IS NOT NULL

SET

• UNION

  • (SELECT course_id FROM teahces WHERE semester = 'Fall' AND year 2017)UNION(SELECT course_id FROM teahces WHERE semester = 'Spring' AND year 2018)

• INTERSECT -> 교집합

  • SELECT LT.course_idFROM (SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017)AS LTJOIN (SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2018)AS RTON LT.course_id = RT.course_id;

• EXCEPT -> 차집합

  • SELECT course_id FROM teahces WHERE semester = 'Fall' AND year 2017 AND course_id NOT IN (SELECT course_id FROM teahces WHERE semester = 'Spring' AND year 2018);

String Operations

• percent (%) - substring

• underscore (_) - 글자수

• WHERE name LIKE '%ri%'

  • 기호를 쓰고 싶으면 \를 쓴다.

    • LIKE '100\%'

  • ""가 아닌, ''를 써야 한다.

  • Escape Character

    • LIKE '100 \%' ESCAPE '\'

    • \를 사용해서 Escape를 구분한다.

Primary Key vs Unique

Integrity Constraints

• DEFAULT

  • genre VARCHAR(20) DEFAULT 'Comedy'

• CHECK A IN ()

  • genre VARCHAR(20) DEFAULT 'Comedy' CHECK genre IN ('Comedy', 'Action', 'Drama')

FOREGIN KEY

• FOREGIN KEY (dept_name) REFERENCES department

WITH 절

• WITH D(dept_name, avg_salary) AS(SELECT dept_name, AVG(salary)FROM instructorGROUP BY dept_name)SELECT dept_name, avg_salaryFROM DWHERE avg_salary > 42000;

Set Membership

• IN

  • genre IN ()

• NOT IN

  • genere NOT IN()

Set Comparison

• SOME : 적어도 하나

• ALL : 모든 값

EXISTS : 뭔가 있다!

• WHERE EXISTS ()

NOT EXISTS : 아무것도 없다!

• WHERE NOT EXISTS ()

UNIQUE : 중복이 없다!

• WHERE UNIQUE ()

ALTER

• 컬럼 추가 : ALTER TABLE table_name ADD column_name column_type;

  • ALTER TABLE instructor add salary NUMERIC(4,1);

• 컬럼 삭제 : ALTER TABLE table_name DROP column_name;

  • ALTER TABLE instructor DROP salary;

• 기본키 추가 : ALTER TABLE table_name ADD PRIMARY KEY column_name;

  • ALTER TABLE instructor ADD PRIMARY KEY ID;

• 기본키 삭제 : ALTER TABLE table_name DROP PRIMARY KEY;

  • ALTER TABLE instructor DROP PRIMARY KEY;

• 외래키 삭제 : ALTER TABLE table_name DROP FOREIGN KEY fk_name;

  • ALTER TABLE instructor DROP FOREIGN KEY instructor_ibfk_1;

• 컬럼 수정 + DEFAULT 값 설정 : ALTER TABLE table_name MODIFY column_name column_type DEFAULT set_value;

  • ALTER TABLE test MODIFY age INT DEFAULT 10;

디비 설계 시 주의할점

1. 중복 Redundance : "같은 정보를 여러 번 반복해서 저장하는 문제"

2. 불완전성 Incompleteness : "중요한 정보를 표현할 수 없는 문제"

E-R 다이어그램

• 사각형 : 엔티티 (Entity set)

• 마름모 : 관계 (Relationship set)

• 밑줄 : primary key

• 점선 연결 + 작은 사각형 : 관계의 속성