https://www.postgresqltutorial.com 를 참고하여 학습 가능
샘플 DB(DVD Rental System)의 ERD(Entity Relationship Diagram)
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(허용 안함)
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)');
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;
SELECT DISTINCT CUSTOMER_ID
FROM payment
WHERE AMOUNT = (SELECT MAX(AMOUNT)
FROM PAYMENT)
;
SELECT EMAIL
FROM CUSTOMER
WHERE EMAIL LIKE '%@%'
AND EMAIL NOT LIKE '@%'
AND EMAIL NOT LIKE '%@'
;
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;
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;
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