Skip to content

Instantly share code, notes, and snippets.

@jholman

jholman/w3d1_breakout.sql

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.
Download ZIP

Revisions

  1. jholman created this gist Aug 2, 2016.
    55 changes: 55 additions & 0 deletions w3d1_breakout.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,55 @@
    -- 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;