Working with databases #3

This time we'll speak about JOIN types, GROUP BY and HAVING

Transcript

1. ВЪВЕДЕНИЕ В БАЗИ ДАННИ Янко Симеонов [VarnaLab] @zecho23
2. РАБОТИЛНИЦА #3 Свързване на таблици и агрегатни функции
3. СТРУКТУРА rel_book_author n 1 public. author author_id serial « nn » name text « nn » total_books smallint « nn » public. book book_id serial « nn » author_id smallint « fk » name text « nn »
4. AUTHOR author_id name birthday 1 Stephen King NULL 2 Terry Pratchet NULL 3 J. R. R. Tolkien NULL 4 Paulo Coehlo 2000-01-01
5. BOOK book_id name author_id genre earnings 1 Lord of the rings 3 fantasy 2461.53 2 It 1 horror 960.26 3 Firestarter 1 horror 7416.70 4 Cell 1 horror 4202.73 5 Going postal 2 fantasy 8763.56 6 Foundation NULL Sci Fi 1209.59 7 Running man 1 Sci Fi 9757.26
6. JOIN
7. INNER JOIN name author Lord of the rings J. R. R. Tolkien It Stephen King Firestarter Stephen King Cell Stephen King Going postal Terry Pratchet Running man Stephen King SELECT book.name, author.name AS "author" FROM book INNER JOIN author ON (author.author_id=book.author_id);
8. Ако имената на полетата по които свързваме са едни и същи в двете таблици SELECT book.name, author.name AS "author" FROM book INNER JOIN author USING (author_id);
9. LEFT JOIN name author It Stephen King Firestarter Stephen King Cell Stephen King Running man Stephen King Going postal Terry Pratchet Lord of the rings J. R. R. Tolkien Foundation NULL SELECT book.name, author.name AS "author" FROM book LEFT JOIN author ON (author.author_id=book.author_id);
10. RIGHT JOIN name author Lord of the rings J. R. R. Tolkien It Stephen King Firestarter Stephen King Cell Stephen King Going postal Terry Pratchet Running man Stephen King NULL Paulo Coehlo SELECT book.name, author.name AS "author" FROM book RIGHT JOIN author ON (author.author_id=book.author_id);
11. CROSS JOIN
12. ВЛОЖЕНИ ЗАЯВКИ name cnt Stephen King 4 Terry Pratchet 1 J. R. R. Tolkien 1 Paulo Coehlo 0 SELECT author.name, (SELECT COUNT(1) FROM book WHERE book.author_id=author.author_id) AS cnt FROM author
13. GROUP BY name Stephen King Stephen King Stephen King Stephen King Terry Pratchet J. R. R. Tolkien SELECT author.name FROM author INNER JOIN book USING (author_id)
14. name J. R. R. Tolkien Stephen King Terry Pratchet SELECT author.name FROM author INNER JOIN book USING (author_id) GROUP BY author.name
15. HAVING
16. WHERE ИЛИ HAVING? name earnings J. R. R. Tolkien 2461.53 Terry Pratchet 8763.56 SELECT author.name, SUM(book.earnings) AS earnings FROM author LEFT JOIN book USING (author_id) GROUP BY author.name HAVING SUM(book.earnings) < 10000
17. NULL PROBLEM FIXED name earnings J. R. R. Tolkien 2461.53 Paulo Coehlo 0.00 Terry Pratchet 8763.56 SELECT author.name, COALESCE(SUM(book.earnings), 0) AS earnings FROM author LEFT JOIN book USING (author_id) GROUP BY author.name HAVING COALESCE(SUM(book.earnings), 0) < 10000
18. АГРЕГАТНИ ФУНКЦИИ
19. SUM Колко общо е спечелил всеки от продажби? name earnings J. R. R. Tolkien 2461.53 Stephen King 22336.95 Terry Pratchet 8763.56 SELECT author.name, SUM(book.earnings) AS earnings FROM author INNER JOIN book USING (author_id) GROUP BY author.name
20. COUNT Колко книги има издадени от всеки автор? name published books J. R. R. Tolkien 1 Stephen King 4 Terry Pratchet 1 SELECT author.name, COUNT(1) AS "published books" FROM author INNER JOIN book USING (author_id) GROUP BY author.name
21. AVG Каква е средната печалба от писане на книги? average_earnings 5593.673333 SELECT AVG(book.earnings) AS average_earnings FROM author INNER JOIN book USING (author_id)
22. ВЪПРОСИ?
23. ПРАКТИЧЕСКА ЧАСТ Нека видим всичко, което говорихме до сега в действие