데이터베이스 4주차 이론 정리

#1. 부속질의

• 부속질의: SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
  • 부속 질의문(서브 질의문): 다른 SELECT 문 안에 들어 있는 SELECT 문
  • 부속 질의문 먼저 수행 -> 상위 질의문 수행
  • 부속 질의문과 상위 질의문 연결하는 연산자 필요
  • 예시: 가장 비싼 도서의 이름

SELECT bookname
FROM Book
WHERE price = (SELECT MAX(price) FROM Book);

ㄴ> 결과를 토대로 상위 질의문을 수행하게 하는 이중 SELECT 문

• 상관 부속질의
  • 부속질 간에는 상하 관계 있음
  • 행 단위로 반복 실행
  • 예시: 출판사별로 출판사의 평균 도서 가격보다 비싼 도서 구하기

SELECT bl.bookname
FROM Book b1
WHERE b1.price > (SELECT avg(b2.price)
				  FROM Book b2
                  WHERE b2.publisher=b1.publisher);

ㄴ> 부속 질의의 상하관계 생각하며 문제 풀기

• 집합 연산: UNION, MINUS, INTERSECT
  • UNION: 합집합 테이블
  • 예시: 대한민국에 거주하는 고객의 이름과 도서를 주문한 고객의 이름 나타내기

SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

• MINUS, INTERSECT -> NOT IN, IN
• 예시: 대한민국에 거주하는 고객의 이름에서 도서를 주문한 고객의 이름을 제외하고 나타내기

SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND
		name NOT IN (SELECT name
        			FROM Customer
        			WHERE custid IN (SELECT custid FROM Orders));

• 예시: 대한민국에 거주하는 고객 중 도서를 주문한 고객의 이름 나타내기

SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND
			name IN (SELECT name
            FROM Customer
            WHERE custid IN (SELECT custid FROM Orders));

ㄴ> UNION, NOT IN, IN을 통해 합집합, 차집합, 교집합 연산 가능

• EXISTS, NOT EXISTS
• 상관 부속질의문 형식으로 부속 질의의 결과가 존재하는지 여부를 확인하는 연산자
• 예시: 주문이 있는 고객의 이름과 주소 나타내기

SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
			  FROM Orders od
			  WHERE cs.custid=od.custid);

ㄴ> 부속 질의의 결과 존재하는지 여부 확인

---

#2. 데이터 정의어

• CREATE TABLE
  • 테이블 구성
  • 속성과 속성에 관한 제약 정의: NULL | NOT NULL | DEFAULT
  • 문법

CREATE TABLE 테이블이름
	({속성이름 데이터타입
    	[NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
    }
    [PRIMARY KEY 속성이름(들)]
    [FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
    	[ON DELETE {CASCADE | SET NULL}]
    )

• 예시: NewBook 테이블 생성, 기본키를 bookid로 지정

CREATE TABLE NewBook (
	bookname VARCHAR(20) NOT NULL,
    publisher VARCHAR(20) UNIQUE,
    price INTEGER DEFAULT 10000 CHECK(price >= 1000),
    PRIMARY KEY (bookname, publisher)
);

• 외래키 지정시: 참조 무결성 제약 조건 유지를 위해 참조되는 테이블에서 투플 삭제 시 처리 방법을 지정하는 옵션
  • ON DELETE CASCADE | SET NULL | RESTRICT | NO ACTION

ㄴ> 테이블 구성과 특성을 토대로 새로운 테이블 생성

• ALTER: 생성된 테이블의 속성 변경 및 속성에 관한 제약 변경, 기본키 및 외래키 변경
  • 문법

ALTER TABLE 테이블이름
	[ADD 속성이름 데이터타입]
    [DROP COLUMN 속성이름]
    [ALTER COLUMN 속성이름 데이터타입]
    [ALTER COLUMN 속성이름 [NULL | NOT NULL]]
    [ADD PRIMARY KEY(속성이름)]
    [[ADD | DROP] 제약이름]

• 예시: NewBook 테이블에 VARCHAR(13)의 자료형으로 isbn 속성 추가

ALTER TABLE NewBook ADD isbn VARCHAR(13);

ㄴ> 생성된 테이블 속성 변경

• DROP Table: 테이블 삭제하는 명령, 테이블의 구조와 데이터 모두 삭제
  • 데이터만 삭제하려면 DELETE 문 사용
  • 삭제할 테이블 참조하는 테이블 있다면 수행 x
  • 문법

DROP TABLE 테이블이름

• 예시: NewBook 테이블 삭제

DROP TABLE NewBook;

ㄴ> 테이블 삭제, 참조 유의

---

#3. 데이터 조작어

• INSERT 문
  • 테이블에 새로운 투플 삽입
  • 문법

INSERT INTO 테이블이름[(속성리스트)]
	   VALUES (값리스트);

• 예시: Book 테이블에 새로운 도서 '스포츠 의학'을 삽입

INSERT INTO Book(bookid, bookname, publisher, price)
	   VALUES (11, '스포츠 의학', '한솔의학서적', 90000);

• SELECT문 사용하여 작성 가능

INSERT INTO Book(bookid, bookname, price, publisher)
	   SELECT bookid, bookname, price, publisher
       FROM Imported_book;

ㄴ>  작성한 형태에 따라 만들어진 투플 삽입

• UPDATE문
  • 특정 속성값 수정하는 명령
  • UPDATE 문에서 다른 테이블의 속성값 이용 가능
  • 문법

UPDATE 테이블이름
SET 속성이름 1=값 1[, 속성이름 2=값 2, ...]
[WHERE <검색조건>];

• 예시: Customer 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경

SET SQL_SAFE_UPDATES = 0;
UPDATE Customer
SET address = '대한민국 부산'
WHERE custid=5;

ㄴ> 특정한 속성값만 수정할 수 있음

• DELETE문
  • 테이블에 있는 기존 투플 삭제
  • 문법

DELETE FROM 테이블이름
[WHERE 검색조건];

ㄴ> 알맞은 명령어 이용하여 테이블의 속성 추가, 수정, 삭제 가능

---

문제풀기

1. 우리 플랫폼에 정착한 판매자 1

2. 테이블 결합하기

3. 작품이 없는 작가 찾기

4. 오랜 기간 보호한 동물

5. 보호소에서 중성화한 동물