Created
August 2, 2016 00:02
-
-
Save jholman/7de5fe568c16bdbb28cbbc2b47cbb0d3 to your computer and use it in GitHub Desktop.
July18 cohort, W3D1 Breakout
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Let's briefly talk about Postgres's facilities for looking at schemas, and getting help | |
help | |
\? | |
\l | |
\d | |
-- Now let's talk about transactions. Not relevant to your life right now, but super relevant | |
-- if you graduate and your first boss gives you access to the production database. Forget | |
-- for now, but later if you find yourself in that situation, google! | |
begin; | |
select * from books; | |
delete books; -- Look, I made a syntax error... | |
delete from books; -- ... so this won't run. | |
rollback; begin; -- Rollback, start again. | |
delete from books; -- Delete | |
select * From books;-- Everything's gone | |
rollback; -- Roll back. | |
select * From books;-- Everything is back. | |
-- Now we're gonna do some exploring, talk about JOINs and such. | |
\d authors; | |
\d books; | |
select id, first_name, last_name FROM authors; | |
select id, first_name firstie, last_name FROM authors a join books b on b.author_id = a.id; | |
select a.id, first_name firstie, last_name FROM authors a join books b on b.author_id = a.id; | |
select count(*) from authors; | |
select count(*) from books; | |
select a.id, first_name firstie, last_name FROM authors a join books b on b.author_id = a.id; | |
-- Hey, what the heck. If I have 17 authors and 15 books, why do I have 13 book/author combos? | |
-- Of course, some authors have no books.... but how is it that some books have no authors? | |
select * from authors where id = 1809; -- Oh look, #1809 (Dr. Seuss) has no entry in authors | |
select * from books; -- ... but he does have 2 books. Sigh. | |
-- Then I gave a little rant about foreign keys that aren't enforced in the DB. Le sigh. | |
-- Okay, let's figure out this aggregation/joining thing. | |
select a.id, first_name firstie, last_name FROM authors a LEFT JOIN books b on b.author_id = a.id ; -- Legal | |
select count(a.id) from authors a left join books b on b.author_id = a.id; -- Legal | |
select a.id, first_name firstie, last_name, count(a.id) FROM authors a LEFT JOIN books b on b.author_id = a.id ; -- NOT LEGAL. DAMMIT. | |
-- So then I talked about how we can't combine aggregate with non-aggregate, unless we GROUP-BY. | |
-- Then we tried a few different count(_) expressions, with a GROUP-BY, and got different answers. | |
select a.id, first_name firstie, last_name, count(a.id) FROM authors a LEFT JOIN books b on b.author_id = a.id group by a.id; | |
select a.id, first_name firstie, last_name, count(*) FROM authors a LEFT JOIN books b on b.author_id = a.id group by a.id; | |
select a.id, first_name firstie, last_name, count(b.id) FROM authors a LEFT JOIN books b on b.author_id = a.id group by a.id; | |
-- Only the last one works, and hopefully I was able to explain why (after thinking about it for | |
-- embarassingly long) | |
-- I also talked briefly, at the whiteboard, about INNER JOIN vs OUTER JOIN and so on. | |
-- Let's talk for a moment about CROSS JOIN, and how toxic it is, and why you need to know | |
select * from books CROSS JOIN authors; | |
select * from books, authors; | |
-- Look, it's an implicit inner join! Tricky! | |
select * from books, authors WHERE books.author_id = authors.id; | |
select * from books join authors on books.author_id = authors.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment