10. 5장 SQL: QUERIES,CONSTRNNTS, TRIGGERS - SQL 연습하기

조건을 보고, 알맞는 SQL 쿼리를 짜는것을 연습한다.

후반에는 앞에서 설명안한 문법들을 소개한다.

 

Q. Find the number of reservations for each red boat.

SELECT	B.bid, COUNT(*)
FROM	Reserves R, Boats B
WHERE	R.bid = B.bid AND B.color = 'red'
GROUP BY B.bid;

 

Q. Find the age of the yongest sailor with age >= 18, for each rating with at least 2 such sailors

18살 이상인 애들 중, 가장 어린 선원을 찾는데, "각 rating"에 적어도 두명의 선원이 있어야함

=> 즉 rating별로 그룹짓고, 이 중 선원이 2명 이상인 그룹만 남긴 다음에 거기서 제일 어린애를 찾으라는것 같다!

SELECT 	S.rating, MIN(S.age)
FROM	Sailors S
WHERE	S.age >= 18
GRPUT BY S.rating
	HAVING COUNT(*) > 1;

 

Q. Find sailors who;ve reserved all boats.

모든 보트를 예약해본 선원을 찾아라

=> 앞에서 NOT EXISTS 두번 겹쳐서 했던것!

1. 특정 선원 하나에 대해, 예약기록이 "없는" 보트들만 뽑아옴 (table A라고 해보기)

2. => table A가 비어있으면 특정 선원은 모든 보트들을 한번씩 예약해본것!! 정답에 부합한다.

    => table A에 뭐라도 있으면, 예약해본적 없는 보트가 있는거니까 조건에 안맞음!!

SELECT	S.sname
FROM	Sailors S
WHERE	NOT EXISTS (
    SELECT	B.bid
    FROM	Boats B
    WHERE	NOT EXISTS (
    	SELECT	R.bid
        FROM	Reserves R
        WHERE	B.bid = R.bid AND S.sid = R.sid));

 

=> 근데 이걸,,, GROUP BY랑 HAVING을 이용해서 해볼 수 있을까?

가능하다!!

1. 일단 sailors와 reserves테이블을 join한다 (from에 둘 다 적어주기)

2. 그리고 선원의 sid와 sname을 기준으로 group해준다

    * 이 때, sname도 그룹기준에 넣어주는 이유는,, 나중에 출력할 때 이름도 출력할거면 group by 조건에 있어야 하기 떄문이다!

3. 여기서부터가 중요한데,... having의 기준에 들어가는게

    위에서 그룹해주면 각 선원별로 몇번의 예약이 있는지를 확인할수 있게된다.

    여기서 count로 예약보트 개수 == 전체 보트 개수 인지를 확인해준다!

    이 때 선원이 날짜만 다르면 똑같은 보트를 여러번 예약할수도 있었으므로, DISTINCT를 사용해준다.

SELECT
FROM	Sailors S, Reserves R
GROUP BY S.sid, S.sname
	HAVING	COUNT(DISTINC R.bid) = (SELELCT COUNT(*) FROM Boats);

 

INSERT

테이블에 새로운 값을 추가할 때 사용한다.

이 때, 일부 column을 비어있는 채로 넣을수도 있다

INSERT	INTO	Boat	VALUES	(105, 'wooo', 'blue');
INSERT	INTO	Boat	(bid, color)	VALUES	(105, 'yello')

 

DELETE & UPDATE

특정 튜플을 삭제하거나, 수정도 가능하다.

삭제할 때, WHERE를 이용해서 삭제한다!

DELETE	FROM	Boats	WHERE	color = 'red';

DELETE	FROM	Boats b
WHERE	b.bid = (
		SELECT	r.bid
            FROM	Reserves r
            WHERE	r.sid = 22);
            
UPDATE	Boats
SET	Color = 'green'
WHERE	bid = 103;

 

GRANT

사용자에게 특정 테이블에 대한 권한을 주기 위해 사용한다.

GRANT	종류	ON object	TO	uesers	[WITH GRNAT OPTION]

종류 : Select, Insert, Delete, References, All 같은 어디까지 가능한지 적어준다.

object : 권한을 허용할 테이블, 뷰를 지정해준다

users : 권한을 받는 유저를 지정해준다

WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는지 여부를 선택할 수 있나봄!

 

Integrity Constraints (IC)

무결성 제약 -> 데이터의 일관성을 유지하기 위해 필요하다.

IC에 맞지 않는 데이터는 INSERT, DELETES, UPDATE가 불가능하다!

- Domain Contraints : 조건에 맞지 않는 타입의 값은 못넣음! (int인데 string을 입력하려 한다던지,,,)

- Primary key, foreign key : 앞에서 배운 키 제약들

Primary Key

- super key : 서로다른 튜플들은 무조건 다른 superkey를 가진다

    -> 필드들의 집합으로 표현됨!! (그러니까,,, 음,,, 여러개의 속성값을 모았을 때 고유한값이면 super key가 될 수 있다)

- key : super key중에 minimal 집합인것

    -> 여러 필드 조합 중, super key를 만족하면서 가장 필드를 적게 모은것을 말한다.

- primary key : key가 여러개일 때,,, 이 중 key목적으로 선택한 하나의 key를 primary key라고 한다.

- candidate key : key인데, 위에서 선택 안된친구! 전부 candidate key가 된다 -> UNIQUE를 사용함

=> 내가 CREATE TABLE할 때, 제약을 어떻게 넣느냐에 따라 중복여부가 달라진다!

CREATE TABLE Enrolloed1(
    sid	CHAR(20),
    cid	CHAR(20),
    grade	CHAR(2),
    PRIMARY KEY (sid, cid));

sid랑 cid의 "조합"이 primary key로 등록되었다.

즉 sid랑 cid의 조합이, 튜플마다 고유해야 한다는 뜻이 된다.

=> sid(학생아이디), cid(수업아이디)의 조합을 키로 등록했기 때문에

하나의 학생이 하나의 수업을 여러번 들을수는 없지만,

하나의 학생이 여러개의 수업을 듣거나, 하나의 수업에 여러명의 학생이 참여할수는 있다!

CREATE TABLE Enrolloed1(
    sid	CHAR(20),
    cid	CHAR(20),
    grade	CHAR(2),
    PRIMARY KEY (sid),
    UNIQUE	(cid, grade));

candidate key를 사용한 모습이다!

이러면,, 일단 primary key로 등록된 sid는 모든 튜플에서 고유해야 한다.

그리고 후보키로 등록된 cid와 grade의 조합또한 고유해야 한다는 제약을 가진다!

이렇게되면,,, 모든 학생들은 하나의 수업만 들을 수 있게 되고, 각 수업마다 하나의 성적만 가지게된다.

=> 이러면 우리가 일반적으로 생각하는 "수업수강"이라는 상황에 맞지 않는 테이블이 만들어진다.

 

Foreign Key, Referential Integrity

외래키 -> 논리적 포인터 역할을 한다!

A테이블의 primary 키를, B테이블에서 Foreign key로 받아올 수 있다.

=> 이렇게 되면 B테이블에서는 자기가 가지고있는 FK값이, 진짜 A테이블에 존재하는지 확인한다! (참조무결성)

참조무결성은 반드시 정해져야하고, 이덕분에 내가 가지고있는 FK값이 반드시 A테이블에 존재한다는게 보장된다.

 

참조무결성을 유지하기 위한 다양한 키워드들이 존재한다.

A테이블의 primary key를 sid, B테이블의 foreign key를 sid라고 하면...

- A테이블에 존재하지 않는 sid를, B테이블에 INSERT하려고 하면? -> 데이터베이스가 막아준다

- A테이블에서, 튜플 한개가 사라지면,,,

    - CASCADE : sid를 참조하는 B에서도 해당하는 튜플을 삭제해버린다

    - NO ACTION : 그냥 삭제를 못하게함 (이게 기본동작임)

    - SET DEFAULT : B테이블의 sid값을 미리 지정해놨던 기본값으로 바꿈

    - SET NULL : B테이블의 sid를 그냥 NULL로 처리함