DBMS: SQL

7 분 소요

INTRO 🙌

저번 시간에는 ER Diagram을 Relation Model/Design으로 변환에 대하여 알아보았다.

이번 시간에는 실질적으로 SQL(Structured Query Language)을 사용하여 모델을 생성해보자.

    SELECT    S
    FROM       R1,…,Rn
    WHERE    C1
    GROUP BY a1,…,ak
    HAVING     C2

SQL 기본 ✌

대소문자 구분 X
문장 맨 뒤 ;
문자열은 작은 따옴표(‘)로 표시
’==’ 대신 ‘=’
NULL은 집계 함수에 영향 x

SQL 쿼리 구조

    SELECT [ALL | DISTINCT] 속성이름들
    FROM 테이블이름들
    WHERE 조건들
    GROUP BY 속성이름
    HAVING 검색조건들
    ORDER BY 속성이름 [ASC | DESC]

SQL 문법 처리순서

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

집계 함수

    SUM([ALL | DISTINCT] 속성이름)
    AVG([ALL | DISTINCT] 속성이름)
    COUNT({[[ALL | DISTINCT] 속성이름] | *})
    MAX([ALL | DISTINCT] 속성이름)
    MIN([ALL | DISTINCT] 속성이름)

DISTINCT: 중복 제거

SQL 문법💜

Single-Relation Query

하나의 관계(테이블)를 포함하는 쿼리문 작성

	SELECT *
	FROM Beers
	WHERE manf = ‘Anheuser-Busch’;

Multi-Table Queries

다수의 관계(테이블)들을 포함하는 쿼리문 작성

    SELECT Person.name
    FROM Person, Purchase, Product
    WHERE Person.name=Purchase.buyer AND Product=Product.name AND Product.category=“telephony”

Select, From, Where

    SELECT   속성들
    FROM     테이블들
    WHERE    조건들

Beers 테이블에서 Anheuser-Busch 회사에서 만든 맥주 데이터 불러오기

	SELECT *
	FROM Beers
	WHERE manf = ‘Anheuser-Busch’;

Output:

AND, OR, NOT, AS, LIKE

AND Operator

    SELECT *
    FROM Company
    WHERE country="USA" AND stockPrice > 50

AS Operator

    SELECT bar, beer, price * 120 AS priceInYen
    FROM Sells;

Output:

    SELECT drinker, ‘likes Bud’ AS whoLikesBud
    FROM Likes
    WHERE beer = ‘Bud’;

Output:

LIKE Operator

    <Attribute> LIKE <pattern>
    <Attribute> NOT LIKE <pattern>

% = any sequence of characters

_ = any single character

    SELECT name
    FROM Drinkers
    WHERE phone LIKE ‘%555-_ _ _ _’;

    SELECT *
    FROM Company
    WHERE country=“USA” AND address LIKE “%Mountain%”

Explicit Tuple-Variables

    SELECT b1.name, b2.name
        FROM Beers b1, Beers b2
        WHERE b1.manf = b2.manf AND
            b1.name < b2.name;

    SELECT b1.name, b2.name
        FROM Beers AS b1, Beers AS b2
        WHERE b1.manf = b2.manf AND
            b1.name < b2.name;

상기 쿼리문에서 Beers b1, Beers b2 부분에 해당한다.

여기서, Tuple Variable은 각각 b1, b2이다.

다음 예시 또한 참고해보자.

    SELECT a1, a2, …, ak
    FROM    R1 AS x1, R2 AS x2, …, Rn AS xn
    WHERE  Conditions

[Nested Loops]

[Parallel Assignment]

[Translation to Relational algebra]

Group-by, Having

Group-by

Sells(bar, beer, price) 테이블에서 맥주별 평균 가격 구하기:

    SELECT beer, AVG(price)
    FROM Sells
    GROUP BY beer;

※ 만약 쿼리가 집계 함수 포함 → SELECT로 내보낼 다른 요소 또한 집계됐거나, 해당 속성이 GROUP BY 목록에 포함되어야 한다.

상기 경우는 beer 별 그룹화를 진행하고, SELECT에서 beer, AVG(price)를 도출한다.

beer 별 그룹화 –> beer은 집계함수 사용 안 해도 무방

아래 예시를 통해 이해해보자.

    SELECT bar, MIN(price)
    FROM Sells
    WHERE beer = ‘Bud’;

price 속성은 집계 함수가 사용된 반면, bar 속성은 집계되지 않았으므로 오류이다.

bar 또한 집계 함수가 사용되어야 한다.

Having

GROUP BY 사용으로 생성된 각 그룹별로 HAVING 조건에 해당되지 않는 Tuple 제거.

[예시]

Sells(bar, beer, price) & Beers(name, manf): Pete’s에 의해 제작되었거나 3개 이상의 bars에서 판매되고 있는 맥주들의 평균 가격 구하기

    SELECT beer, AVG(price)
    FROM Sells
    GROUP BY beer
    HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = ‘Pete’’s’);

NULL Value

Missing value(결측치) 혹은 Inapplicable(해당없음)

하기 쿼리문은 나이 정보로 NULL 값을 가지는 사람들은 포함하지 않는다.

    SELECT *
    FROM     Person
    WHERE  age < 25  OR  age >= 25

따라서, 하기 쿼리문처럼 직접 NULL 경우에 대한 조건을 추가해야 한다.

    SELECT *
    FROM     Person
    WHERE  age < 25  OR  age >= 25 OR age IS NULL

Subqueries

Sells(bar, beer, price), Joe가 Bud 구매에 지불한 가격과 동일한 가격으로 Miller를 판매하는 bars 구하기

    SELECT bar
    FROM Sells
    WHERE beer = ‘Miller’ AND
        price = (SELECT price
                FROM Sells
                WHERE bar = ‘Joe’’s Bar’
                AND beer = ‘Bud’);

Boolean Operators IN, EXISTS, ANY, ALL

IN

IN(<relation>): 하나라도 포함

Beers(name, manf) and Likes(drinker, beer), Fred가 좋아하는 맥주 제조사 이름 구하기

    SELECT *
    FROM Beers
    WHERE name IN (SELECT beer
    FROM Likes
    WHERE drinker = ‘Fred’);

EXIST

EXISTS(<relation>): 빈 공간이 아니라면

From Beers(name, manf), 제조사별 고유 맥주 구하기

    SELECT name
    FROM Beers b1
    WHERE NOT EXISTS (
        SELECT *
        FROM Beers
        WHERE manf = b1.manf AND
            name <> b1.name);

ANY

ANY(<relation>): 하나라도 포함 (T/F)

ALL

ALL(<relation>): 모든 서브쿼리 tuple 조회 및 비교

x <> ALL(<relation>)는 모든 서브쿼리 tuple 중에서 x와 같지 않은 튜플 리턴

Sells(bar, beer, price), 최고가로 팔린 맥주 구하기

    SELECT beer
    FROM Sells
    WHERE price >= ALL(
        SELECT price
        FROM Sells);

SQL 실습 ✏

    Product ( pname,  price, category, maker)
    Purchase (buyer,  seller,  store,  product)
    Company (cname, stock price, country)
    Person( per-name, phone number, city)

Ex #1: Find people who bought telephony products.

Ex #2: Find names of people who bought American products

Ex #3: Find names of people who bought American products and did not buy French products

Ex #4: Find names of people who bought American products and they live in Champaign.

Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.

Defining a Database Schema ✒

배경지식

INT or INTEGER (synonyms).
REAL or FLOAT (synonyms).
CHAR(n) = fixed-length string of n characters.
VARCHAR(n) = variable-length string of up to n characters.

CREATE/DROP

	CREATE TABLE Sells (
		bar	CHAR(20),
		beer	VARCHAR(20),
		price	REAL
	);

	DROP TABLE <name>;

DELETE

    ALTER TABLE Bars DROP license;

Declaring Keys

PRIMARY KEY or UNIQUE

    CREATE TABLE Beers (
		name	CHAR(20) UNIQUE,
		manf	CHAR(20)
	);

    CREATE TABLE Sells (
		bar	CHAR(20),
		beer	VARCHAR(20),
		price	REAL,
		PRIMARY KEY (bar, beer)
	);

오로지 하나의 PRIMARY KEY, 여러 개의 UNIQUE
PRIMARY KEY 속성값 NULL 불가, UNIQUE 가능
DBMS 종류에 따라, 새로운 index 생성시 자동 KEY 생성 차이 존재
- PRIMARY KEY 생성 O, UNIQUE 생성 X.
UNIQUE
- 유일성 O
- 중복 제어
- 각 컬럼마다 지정 가능

DEFAULT

기본값 지정

    CREATE TABLE Drinkers (
        name CHAR(30) PRIMARY KEY,
        addr CHAR(50) DEFAULT ‘123 Sesame St.’,
        phone CHAR(16)
    );

기본값 지정을 통해, 하기 불완전한 쿼리문을 실행해도 처리되는 모습이다.

    INSERT INTO Drinkers(name)
	VALUES(‘Sally’);

만약, ‘phone’ 속성에 NOT NULL이라면, 상기 INSERT 쿼리문은 거절된다.

NOT NULL

NULL 값 허용 X

Database Modification 🎄

INSERT

    INSERT INTO <relation>
	({ VALUE | <subquery> });

[Likes 테이블에 VALUE 추가]

    // Likes(drinker, beer)

    INSERT INTO Likes
	VALUES(‘Sally’, ‘Bud’);

    INSERT INTO Likes(beer, drinker)
    VALUES(‘Bud’, ‘Sally’);

[Sally가 자주가는 bar를 방문하는 다른 음주가 목록 추가]

    // Frequents(drinker, bar)

    INSERT INTO PotBuddies
    (SELECT d2.drinker
    FROM Frequents d1, Frequents d2
    WHERE d1.drinker = ‘Sally’ AND
        d2.drinker <> ‘Sally’ AND
        d1.bar = d2.bar
    );

Delete

    DELETE FROM <relation>
	WHERE <condition>;

[Likes 테이블에서 Sally가 Bud 맥주를 마신 튜플 삭제]

    DELETE FROM Likes
	WHERE drinker = ‘Sally’ AND beer = ‘Bud’;

[모든 튜플 삭제]

    DELETE FROM Likes;

[동일한 제조업체에서 서로 다른 맥주 삭제]

    DELETE FROM Beers b
    WHERE EXISTS (
    SELECT name FROM Beers
    WHERE manf = b.manf AND
        name <> b.name);

Update

    UPDATE <relation>
	SET <list of attribute assignments>
	WHERE <condition on tuples>;

[Fred’s phone number을 555-1212로 변경]

    UPDATE Drinkers
	SET phone = ‘555-1212’
	WHERE name = ‘Fred’;

Constraints 🎆

Constraint: 데이터 간 관계 규칙 (i.e., key constraints)

Trigger: 특정 조건 충족시 발생

Constraints 보다 구현 간편

Value-based constraints: 특정 속성값 규제

Tuple-based constraints: 요소들 간 관계 규칙

Foreign Keys(외래키)

    FOREIGN KEY ( <list of attributes> )
	or
    REFERENCES <relation> ( <attributes> )

가령, Sells(bar, beer, price)에서 beer 속성과 Beers의 beer 속성을 연결짓는 외래키 추가

    CREATE TABLE Beers (
        name	CHAR(20) PRIMARY KEY,
        manf	CHAR(20) );

    CREATE TABLE Sells (
        bar	CHAR(20),
        beer	CHAR(20) REFERENCES Beers(name),
        price	REAL );

    CREATE TABLE Sells (
        bar	CHAR(20),
        beer	CHAR(20),
        price	REAL,
        FOREIGN KEY(beer) REFERENCES Beers(name));

외래키 정책(Policy)

어떤 관계를 R, R에 대한 기본키를 S이라 해보자.

DELETE/UPDATE to S: some tuples of R to “dangle”

Default : 변경 취소
Cascade : R 수정
- Deleted <attribute>: R tuple 삭제
- Updated <attribute>: R 속성 변경
Set NULL : R & S 해당 속성 NULL 변경.

하기 조건 추가로 DELETE/UPDATE 정책(Policy) 선택 가능하다; 기본 = Default

    ON [UPDATE, DELETE][SET NULL CASCADE]

    CREATE TABLE Sells (
        bar	CHAR(20),
        beer	CHAR(20),
        price	REAL,
        FOREIGN KEY(beer)
            REFERENCES Beers(name)
            ON DELETE SET NULL
            ON UPDATE CASCADE );

CHECK

    CHECK( <condition> )

The condition may use the name of the attribute, but any other relation or attribute name must be in a subquery.

Example 1:

CHECK (price <= 5.00) 가격이 $5 넘어가면 reject

CHECK (beer IN (SELECT name FROM Beers)): Beers에 해당 맥주 없으면 reject

  CREATE TABLE Sells (
      bar	CHAR(20),
      beer	CHAR(20)	CHECK ( beer IN
              (SELECT name FROM Beers)),
      price	REAL CHECK ( price <= 5.00 )
  );

Example 2: 오로지 Joe’s Bar만 $5 넘는 가격에 맥주 판매 가능:

    CREATE TABLE Sells (
        bar		CHAR(20),
        beer		CHAR(20),
        price	REAL,
        CHECK (bar = ’Joe’’s Bar’ OR
                    price <= 5.00)
    );

Assertions

Any SQL boolean expression

    CREATE ASSERTION <name>
		CHECK ( <condition> );

Example: Sells(bar, beer, price), 어떤 bar도 평균 $5 이상 부과 불가 (T/F)

    CREATE ASSERTION NoRipoffBars CHECK (
        NOT EXISTS (
            SELECT bar FROM Sells
            GROUP BY bar
            HAVING 5.00 < AVG(price)
        ));

수정 시, 일일히 모든 ASSERTION 확인 요망

다음 시간에는 Disk, Files, Buffer Manager에 대해 알아보자.

Reference

Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke

Twitter Facebook LinkedIn

쭌스🎄

INTRO 🙌

SQL 기본 ✌

SQL 쿼리 구조

SQL 문법 처리순서

집계 함수

SQL 문법💜

Select, From, Where

AND, OR, NOT, AS, LIKE

AND Operator

AS Operator

LIKE Operator

Explicit Tuple-Variables

Group-by, Having

Group-by

Having

NULL Value

Subqueries

Boolean Operators IN, EXISTS, ANY, ALL

IN

EXIST

ANY

ALL

SQL 실습 ✏

Defining a Database Schema ✒

배경지식

CREATE/DROP

DELETE

Declaring Keys

PRIMARY KEY or UNIQUE

DEFAULT

NOT NULL

Database Modification 🎄

INSERT

Delete

Update

Constraints 🎆

Foreign Keys(외래키)

외래키 정책(Policy)

CHECK

Assertions

Reference

공유하기

댓글남기기

참고

2024.10.02 Evaluating on Image Hallucination for TTI Generative Models in I-HallA via PaliGemma

2023.12.04 [논문분석] Saliency as Pseudo-Pixel Supervision for Weakly and Semi-Supervised Semantic Segmentation (PAMI 2023)

2023.12.03 [논문분석] Segment Anything (ICCV 2023)

2023.12.03 [논문분석] Learning Transferable Visual Models From Natural Language Supervision (ICMR 2021)

2023.12.03 [논문분석] Learning Pixel-level Semantic Affinity with Image-level Supervision for Weakly Supervised Semantic Segmentation (CVPR 2018)

2023.12.01 [논문분석] Grad-CAM: Visual Explanations from Deep Networks via Gradient-based Localization (ICCV 2017)

2023.12.01 [논문분석] Entropy regularization for weakly supervised object localization (PRL 2023)

2023.11.29 [논문분석] Railroad is not a Train: Saliency as Pseudo-pixel Supervision for Weakly Supervised Semantic Segmentation (CVPR 2021)

2023.11.25 [논문분석] Weaker Than You Think: A Critical Look at Weakly Supervised Learning (ACL 2023)

2023.08.03 [논문분석] PETR: Position Embedding Transformation for Multi-View 3D Object Detection (ECCV, 2022)

2024.10.02
Evaluating on Image Hallucination for TTI Generative Models in I-HallA via PaliGemma

2023.12.04
[논문분석] Saliency as Pseudo-Pixel Supervision for Weakly and Semi-Supervised Semantic Segmentation (PAMI 2023)

2023.12.03
[논문분석] Segment Anything (ICCV 2023)

2023.12.03
[논문분석] Learning Transferable Visual Models From Natural Language Supervision (ICMR 2021)

2023.12.03
[논문분석] Learning Pixel-level Semantic Affinity with Image-level Supervision for Weakly Supervised Semantic Segmentation (CVPR 2018)

2023.12.01
[논문분석] Grad-CAM: Visual Explanations from Deep Networks via Gradient-based Localization (ICCV 2017)

2023.12.01
[논문분석] Entropy regularization for weakly supervised object localization (PRL 2023)

2023.11.29
[논문분석] Railroad is not a Train: Saliency as Pseudo-pixel Supervision for Weakly Supervised Semantic Segmentation (CVPR 2021)

2023.11.25
[논문분석] Weaker Than You Think: A Critical Look at Weakly Supervised Learning (ACL 2023)

2023.08.03
[논문분석] PETR: Position Embedding Transformation for Multi-View 3D Object Detection (ECCV, 2022)