SQL과 데이터베이스

1.1 PostgreSQL 샘플 Database

https://www.postgresqltutorial.com 를 참고하여 학습 가능
샘플 DB(DVD Rental System)의 ERD(Entity Relationship Diagram)

2.1 SELECT 문법

• 테이블에 저장된 데이터를 조회
  

SELECT절에 정의된 컬럼 alias의 경우, 결국 표준SQL에서는 ORDER BY에서만 사용 가능하다. 하지만 vendor 마다 다른 절에서도 사용가능 하도록 추가 허용하는 경우가 있다.
MYSQL - https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html (GROUP BY, HAVING, ORDER BY 허용)
PostgreSQL(허용 안함) - https://www.postgresqltutorial.com/postgresql-having/
Oracle(허용 안함)

2.2 SELECT 실습 준비

CREATE TABLE T1 ( ID SERIAL NOT NULL PRIMARY KEY, 
BCOLOR VARCHAR, FCOLOR VARCHAR );

INSERT
  INTO T1 (BCOLOR, FCOLOR)
VALUES
         ('red', 'red')
       , ('red', 'red')
       , ('red', NULL)
       , (NULL, 'red')
       , ('red', 'green')
       , ('red', 'blue')
       , ('green', 'red')
       , ('green', 'blue')
       , ('green', 'green')
       , ('blue', 'red')
       , ('blue', 'green')
       , ('blue', 'blue')
;

CREATE TABLE CONTACTS 
(
    ID INT GENERATED BY DEFAULT AS IDENTITY
  , FIRST_NAME VARCHAR(50) NOT NULL
  , LAST_NAME VARCHAR(50) NOT NULL
  , EMAIL VARCHAR(255) NOT NULL
  , PHONE VARCHAR(15)
  , PRIMARY KEY (ID) 
);

 INSERT 
   INTO 
   CONTACTS(FIRST_NAME, LAST_NAME, EMAIL, PHONE) 
 VALUES
   ('John','Doe','john.doe@example.com',NULL),
   ('Lily','Bush','lily.bush@example.com','(408-234-2764)');

2.3 SELECT 실습

• 아래 문장을 실행해 보고 SELECT 문법을 이해하기

SELECT * FROM CUSTOMER;
SELECT FIRST_NAME, LAST_NAME, EMAIL FROM CUSTOMER;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER ORDER BY FIRST_NAME ASC;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER ORDER BY FIRST_NAME DESC;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER ORDER BY FIRST_NAME ASC, LAST_NAME DESC;
SELECT DISTINCT BCOLOR FROM T1 ORDER BY BCOLOR;
SELECT DISTINCT BCOLOR, FCOLOR FROM T1 ORDER BY BCOLOR, FCOLOR;
SELECT DISTINCT ON (BCOLOR) BCOLOR, FCOLOR FROM T1 ORDER BY BCOLOR, FCOLOR;
SELECT DISTINCT ON (BCOLOR) BCOLOR, FCOLOR FROM T1 ORDER BY BCOLOR, FCOLOR DESC;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE FIRST_NAME='Jamie' AND LAST_NAME='Rice’;
SELECT CUSTOMER_ID, AMOUNT, PAYMENT_DATE FROM PAYMENT WHERE AMOUNT<=1 OR AMOUNT>=8;
SELECT FILM_ID, TITLE, RELEASE_YEAR FROM FILM ORDER BY FILM_ID LIMIT 5;
SELECT FILM_ID, TITLE, RELEASE_YEAR FROM FILM ORDER BY FILM_ID LIMIT 4 OFFSET 3;
SELECT FILM_ID, TITLE, RENTAL_RATE FROM FILM ORDER BY RENTAL_RATE DESC LIMIT 10;
SELECT FILM_ID, TITLE FROM FILM ORDER BY TITLE FETCH FIRST ROW ONLY;
SELECT FILM_ID, TITLE FROM FILM ORDER BY TITLE FETCH FIRST 1 ROW ONLY;
SELECT FILM_ID, TITLE FROM FILM ORDER BY TITLE OFFSET 5 ROWS FETCH FIRST 5 ROW ONLY;
SELECT CUSTOMER_ID, RENTAL_ID, RETURN_DATE FROM RENTAL WHERE CUSTOMER_ID IN (1,2) ORDER BY RETURN_DATE DESC;
SELECT CUSTOMER_ID, RENTAL_ID, RETURN_DATE FROM RENTAL WHERE CUSTOMER_ID=1 OR CUSTOMER_ID=2 ORDER BY RETURN_DATE DESC;
SELECT CUSTOMER_ID, RENTAL_ID, RETURN_DATE FROM RENTAL WHERE CUSTOMER_ID NOT IN (1,2) ORDER BY RETURN_DATE DESC;
SELECT CUSTOMER_ID, RENTAL_ID, RETURN_DATE FROM RENTAL WHERE CUSTOMER_ID<>1 AND CUSTOMER_ID<>2 ORDER BY RETURN_DATE DESC;
SELECT CUSTOMER_ID FROM RENTAL WHERE CAST(RETURN_DATE AS DATE)='2005-05-27';
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM RENTAL WHERE CAST(RETURN_DATE AS DATE)='2005-05-27');
SELECT CUSTOMER_ID,PAYMENT_ID,AMOUNT FROM PAYMENT WHERE AMOUNT BETWEEN 8 AND 9;
SELECT CUSTOMER_ID,PAYMENT_ID,AMOUNT FROM PAYMENT WHERE AMOUNT NOT BETWEEN 8 AND 9;
SELECT CUSTOMER_ID,PAYMENT_ID,AMOUNT, PAYMENT_DATE FROM PAYMENT WHERE CAST(PAYMENT_DATE AS DATE) BETWEEN '2007-02-07' AND '2007-02-15’;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE FIRST_NAME LIKE 'Jen%’;
SELECT 'FOO' LIKE 'FOO', 'FOO' LIKE 'F%', 'FOO' LIKE '_O_', 'BAR' LIKE 'B_’;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE FIRST_NAME LIKE ‘%er%’;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE FIRST_NAME LIKE ‘_her%’;
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE FIRST_NAME NOT LIKE 'Jen%’;
SELECT ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE FROM CONTACTS WHERE PHONE IS NULL;
SELECT ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE FROM CONTACTS WHERE PHONE IS NOT NULL;

2.4 SELECT 실습 문제

• PAYMENT 테이블에서 단일 거래의 AMOUNT 액수가 가장 많은 고객들의 CUSTOMER_ID를 추출하기 (단, CUSTOMER_ID의 값은 유일해야 한다)

SELECT DISTINCT CUSTOMER_ID	
  FROM payment
 WHERE AMOUNT = (SELECT MAX(AMOUNT) 
				   FROM PAYMENT)
;

• 고객들에게 단체 이메일을 전송하고자 한다. CUSTOMER 테이블에서 고객의 EMAIL 주소를 추출하고, 이메일 형식에 맞지 않는 이메일 주소는 제외한다. (이메일 형식은 ‘@’가 존재, ‘@’로 시작하면 안되고, ‘@’로 끝나지 말아야 한다)

SELECT EMAIL
  FROM CUSTOMER
 WHERE EMAIL LIKE '%@%'
   AND EMAIL NOT LIKE '@%'
   AND EMAIL NOT LIKE '%@'
;

3.1 JOIN의 종류

• 2개 이상의 테이블에 있는 정보 중 사용자가 필요로 하는 집합에 맞게 가상의 테이블처럼 만들어서 결과를 보여주는 것

  

3.2 JOIN 실습 준비1

• 예제 테이블 생성

CREATE TABLE BASKET_A (ID INT PRIMARY KEY, FRUIT VARCHAR(100) NOT NULL);

CREATE TABLE BASKET_B (ID INT PRIMARY KEY, FRUIT VARCHAR(100) NOT NULL);

INSERT INTO BASKET_A (ID, FRUIT)
VALUES (1, 'Apple'), (2, 'Orange'), (3, 'Banana'), (4, 'Cucumber');
COMMIT; 

INSERT INTO BASKET_B (ID, FRUIT)
VALUES (1, 'Orange'), (2, 'Apple'), (3, 'Watermelon'), (4, 'Pear');
COMMIT; 

3.3 INNER JOIN

• INNER JOIN 문법

  

SELECT
       A.ID ID_A
     , A.FRUIT FRUIT_A
     , B.ID ID_B
     , B.FRUIT FRUIT_B
  FROM BASKET_A A
INNER JOIN BASKET_B B 
   ON A.FRUIT = B.FRUIT;

SELECT
       A.CUSTOMER_ID, A.FIRST_NAME
     , A.LAST_NAME, A.EMAIL
     , B.AMOUNT, B.PAYMENT_DATE
  FROM CUSTOMER A 
 INNER JOIN PAYMENT B 
ON A.CUSTOMER_ID = B.CUSTOMER_ID;

SELECT
       A.CUSTOMER_ID, A.FIRST_NAME
     , A.LAST_NAME, A.EMAIL
     , B.AMOUNT, B.PAYMENT_DATE
  FROM CUSTOMER A 
INNER JOIN PAYMENT B 
ON A.CUSTOMER_ID = B.CUSTOMER_ID
WHERE A.CUSTOMER_ID = 4;

SELECT
       A.CUSTOMER_ID, A.FIRST_NAME
     , A.LAST_NAME, A.EMAIL
     , B.AMOUNT, B.PAYMENT_DATE
     , C.FIRST_NAME AS S_FIRST_NAME 
     , C.LAST_NAME AS S_LAST_NAME
  FROM CUSTOMER A 
INNER JOIN PAYMENT B 
ON A.CUSTOMER_ID = B.CUSTOMER_ID
INNER JOIN STAFF C 
ON B.STAFF_ID = C.STAFF_ID;

3.4 LEFT | RIGHT OUTER JOIN

• LEFT | RIGHT OUTER JOIN 문법

  

SELECT
       A.ID    AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID    AS ID_B
     , B.FRUIT AS FRUIT_B
  FROM
       BASKET_A A LEFT OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT;

 SELECT
      A.ID    AS ID_A
    , A.FRUIT AS FRUIT_A
    , B.ID    AS ID_B
    , B.FRUIT AS FRUIT_B
 FROM
      BASKET_A A
LEFT OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT
WHERE B.ID IS NULL;
-- LEFT ONLY

SELECT
       A.ID    AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID    AS ID_B
     , B.FRUIT AS FRUIT_B
  FROM
       BASKET_A A
RIGHT OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT;

 SELECT
       A.ID AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID AS ID_B
     , B.FRUIT AS FRUIT_B
  FROM
       BASKET_A A
RIGHT OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT
WHERE A.ID IS NULL;
-- RIGHT ONLY