July18 cohort, W3D1 Breakout

w3d1_breakout.sql

-- 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;