PostgreSQL - SELECT

SELECT

-- 전체 SELECT
SELECT * FROM books;

-- 선택적 SELECT
SELECT id, author_id, title FROM books;

-- 표현식 사용 1
SELECT 2+2, pi(), 'PostgreSQL is more than calculator!';
-- 표현식 사용 2
SELECT 2+2 AS "2 plus 2", pi AS "the pi function", 'PostgreSQL is more than a calculator!' AS comments;

-- 다중 테이블 SELECT
SELECT books.id, title, authors.id, last_name FROM books, authors WHERE books.author_id=authors.id;

-- sub-query SELECT
SELECT 'test' AS test, id FROM (SELECT * FROM books) AS example_sub_query;

-- Aliasing
SELECT b.id, title, a.id, last_name FROM books AS b, authors AS a WHERE b.author_id=a.id;
SELECT the_books_id, title, id, last_name FROM books AS b (the_book_id), authors WHERE author_id=id;

-- DISTINCT
SELECT DISTINCT author_id FROM books;
SELECT DISTINCT ON (author_id), author_id, title FROM books;

-- WHERE
SELECT * FROM books WHERE subject_id=4;
SELECT title FROM books WHERE subject_id=4 AND author_id=7805;
SELECT title FROM books WHERE subject_id=4 OR subject_id=0;
SELECT * FROM books WHERE author_id=1866 AND subject_id=15 OR subject_id=3;
SELECT * FROM books WHERE author_id=1866 AND (subject_id=15 OR subject_id=3);

-- CROSS JOIN
-- 아래 2개의 SELECT는 동일한 결과
SELECT b.id, title, a.id, last_name FROM books AS b, authors AS a WHERE b.author_id=a.id;
SELECT b.id, title, a.id, last_name FROM books AS b CROSS JOIN authors AS a WHERE b.author_id=a.id;

-- INNER JOIN
-- 아래 2개의 SELECT는 동일한 결과
SELECT title, last_name, first_name FROM books, authors WHERE (books.author_id=authors.id) AND last_name='Geisel';
SELECT title, last_name, first_name FROM books AS b INNER JOIN authors AS a WHERE last_name='Geisel';

-- USING
SELECT title, last_name, first_name FROM books INNER JOIN authors AS a (author_id) USING (author_id) WHERE last_name='Geisel';

-- NATURAL
SELECT title, last_name, first_name FROM books NATURAL INNER JOIN authors AS a (author_id) WHERE last_name='Geisel';

-- OUTER JOIN
SELECT title, isbn FROM books LEFT OUTER JOIN editions ON (books.id=editions.book_id);
SELECT title, isbn FROM books RIGHT OUTER JOIN editions ON (books.id=editions.book_id);
SELECT title, isbn FROM books FULL OUTER JOIN editions ON (books.id=editions.book_id);

-- 다중 INNER JOIN
SELECT a.last_name, p.name AS publisher, e.isbn, s.subject
    FROM ((((authors AS a INNER JOIN books AS b
        ON (a.id = b.author_id))
    INNER JOIN editions AS e ON (e.book_id = b.id))
    INNER JOIN publishers AS p ON (p.id = e.publisher_id))
    INNER JOIN subjects AS s ON (s.id = b.subject_id));

-- GROUP BY
SELECT COUNT(e.isbn) AS "number of books",
             p.name AS publisher
             FROM editions AS e INNER JOIN publishers AS p
                       ON (e.publisher_id = p.id)
             GROUP BY p.name;

-- HAVING
SELECT COUNT(e.isbn) AS "number of books",
             p.name AS publisher
             FROM editions AS e INNER JOIN publishers AS p
                       ON (e.publisher_id = p.id)
             GROUP BY publisher
             HAVING COUNT(e.isbn) > 1;

-- ORDER BY
SELECT isbn, edition, publication FROM editions ORDER BY publication ASC;
-- ORDER BY (multiple)
SELECT edition, publication FROM editions ORDER BY edition ASC, publication DESC;

-- DISTINCT with ORDER BY (아래 두 query의 결과는 같음)
-- 1.
SELECT DISTINCT ON (edition) edition, publication FROM editions ORDER BY edition ASC, publication DESC;
-- 2.
SELECT edition, max(publication) FROM editions GROUP BY edition;

-- LIMIT and OFFSET
-- LIMIT
SELECT isbn, title, publication FROM editions NATURAL JOIN books AS b (book_id) ORDER BY publication DESC LIMIT 5;
-- LIMIT + OFFSET
SELECT isbn, title, publication FROM editions NATURAL JOIN books AS b (book_id) ORDER BY publication DESC LIMIT 5 OFFSET 2;

-- UNION : all non-distinct rows into a single data set
SELECT title FROM books
    UNION
    SELECT last_name FROM authors
    LIMIT 11;

-- INTERSECT : any rows not found in both data sets to be omitted
SELECT isbn FROM editions
    INTERSECT
    SELECT isbn FROM shipments
        GROUP BY isbn
        HAVING count(id) > 2;

-- EXCEPT : any rows found in both data sets to be omitted from the returned data set
SELECT last_name, first_name
    FROM authors
    EXCEPT
    SELECT last_name, first_name
        FROM authors AS a (author_id)     
        NATURAL INNER JOIN books
    ORDER BY first_name ASC;

-- sub-query 결과값 비교
(SELECT title FROM books ORDER BY title DESC LIMIT 7)
    EXCEPT
    (SELECT title FROM books ORDER BY title ASC LIMIT 11)
    ORDER BY title DESC;

-- case
SELECT isbn,
    CASE WHEN cost > 20 THEN 'over $20.00 cost'
             WHEN cost = 20 THEN '$20.00 cost'
             ELSE 'under $20.00 cost'
    END AS cost_range
    FROM stock
    LiMIT 8;

-- case with sub-queries
SELECT isbn,
    CASE WHEN cost > 20 THEN 'N/A - (Out of price range)'
             ELSE (SELECT title FROM books b JOIN editions e
                                                                ON (b.id = e.book_id)
                                                    WHERE e.isbn = stock.isbn)
    END AS cost_range
    FROM stock
    ORDER BY cost_range ASC
    LIMIT 8;

-- SELECT INTO
SELECT * INTO stock_backup FROM stock;