Skip to content

Instantly share code, notes, and snippets.

@jholman
Created August 2, 2016 00:02
Show Gist options
  • Save jholman/7de5fe568c16bdbb28cbbc2b47cbb0d3 to your computer and use it in GitHub Desktop.
Save jholman/7de5fe568c16bdbb28cbbc2b47cbb0d3 to your computer and use it in GitHub Desktop.
July18 cohort, W3D1 Breakout
-- 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