{"action":"created","comment":{"url":"https://api.github.com/repos/WDI-SEA/booktown/pulls/comments/67426160","id":67426160,"diff_hunk":"@@ -0,0 +1,148 @@\n+######### Booktown deliverable answers #########\n+\n+\n+######### ######### Order ######### #########\n+\n+\n+1. Find all subjects sorted by subject \n+\n+  SELECT subject FROM subjects\n+  ORDER BY subject ASC;\n+\n+\n+2. Find all subjects sorted by location\n+\n+  SELECT subject FROM subjects\n+  ORDER BY location ASC;\n+\n+\n+######### ######### Where ######### #########\n+\n+\n+1. Find the book \"Little Women\"\n+\n+\n+  SELECT title FROM books\n+  WHERE title = 'Little Women';\n+\n+\n+2. Find all books containing the word \"Python\"\n+\n+\n+  SELECT title FROM books\n+  WHERE title ILIKE '%python%';\n+\n+\n+3. Find all subjects with the location \"Main St\" sort them by subject\n+\n+\n+  SELECT subject FROM subjects\n+  WHERE location = 'Main St'\n+  ORDER BY subject ASC;\n+\n+\n+######### ######### Joins ######### #########\n+\n+\n+1. Find all books about Computers list ONLY book title\n+\n+\n+  SELECT books.title FROM books\n+  INNER JOIN subjects \n+  ON books.subject_id = subjects.id\n+  WHERE subjects.id = 4;\n+\n+\n+2. Find all books and display ONLY:\n+\n+  * Book title\n+  * Author's first name\n+  * Author's last name\n+  * Book subject\n+\n+\n+  SELECT books.title, authors.first_name, authors.last_name, subjects.subject FROM books\n+  INNER JOIN authors\n+  ON books.author_id = authors.id\n+  INNER JOIN subjects\n+  ON books.subject_id = subjects.id;\n+\n+\n+3. Find all books that are listed in the stock table:\n+ \n+  * Sort them by retail price (most expensive first)\n+  * Display ONLY: title and price\n+\n+\n+  SELECT books.title, stock.retail FROM books\n+  INNER JOIN editions\n+  ON books.id = editions.book_id\n+  INNER JOIN stock\n+  ON editions.isbn = stock.isbn\n+  ORDER BY stock.retail DESC;\n+\n+\n+4. Find the book \"Dune\" and display ONLY:\n+\n+  * Book title\n+  * ISBN number\n+  * Publisher name\n+  * Retail price\n+\n+  \n+  SELECT books.title, editions.isbn, publishers.name, stock.retail FROM books                    \n+  INNER JOIN editions\n+  ON books.id = editions.book_id\n+  INNER JOIN publishers\n+  ON editions.publisher_id = publishers.id\n+  INNER JOIN stock\n+  ON editions.isbn = stock.isbn\n+  WHERE books.title ILIKE 'Dune';\n+\n+\n+5. Find all shipments sorted by ship date display ONLY:\n+\n+  * Customer first name\n+  * Customer last name\n+  * Ship date\n+  * Book title\n+\n+  \n+  SELECT customers.first_name, customers.last_name, shipments.ship_date, books.title FROM customers\n+  INNER JOIN shipments\n+  ON customers.id = shipments.customer_id\n+  INNER JOIN editions\n+  ON shipments.isbn = editions.isbn\n+  INNER JOIN books\n+  ON editions.book_id = books.id\n+  ORDER BY shipments.ship_date;\n+\n+\n+\n+######### ######### Grouping and Counting #########\n+\n+\n+1. Get the COUNT of all books\n+\n+  SELECT COUNT(*) FROM books;\n+\n+\n+2. Get the COUNT of all Locations\n+\n+  SELECT COUNT(DISTINCT subjects.location) FROM subjects;\n+\n+\n+3. Get the COUNT of each unique location in the subjects table. Display the count and the location name. (hint: requires GROUP BY).\n+\n+  SELECT COUNT(subjects.location), subjects.location FROM subjects\n+  GROUP BY subjects.location\n+  HAVING count(subjects.location) > 0;\n+","path":"answers.md","position":140,"original_position":140,"commit_id":"7190316d8a30cf7726ee13374aa39662783c43c5","original_commit_id":"7190316d8a30cf7726ee13374aa39662783c43c5","user":{"login":"scottheron","id":15911244,"avatar_url":"https://avatars.githubusercontent.com/u/15911244?v=3","gravatar_id":"","url":"https://api.github.com/users/scottheron","html_url":"https://github.com/scottheron","followers_url":"https://api.github.com/users/scottheron/followers","following_url":"https://api.github.com/users/scottheron/following{/other_user}","gists_url":"https://api.github.com/users/scottheron/gists{/gist_id}","starred_url":"https://api.github.com/users/scottheron/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/scottheron/subscriptions","organizations_url":"https://api.github.com/users/scottheron/orgs","repos_url":"https://api.github.com/users/scottheron/repos","events_url":"https://api.github.com/users/scottheron/events{/privacy}","received_events_url":"https://api.github.com/users/scottheron/received_events","type":"User","site_admin":false},"body":"alternative - select location, count(location) from subjects;\r\n","created_at":"2016-06-16T21:20:40Z","updated_at":"2016-06-16T21:20:40Z","html_url":"https://github.com/WDI-SEA/booktown/pull/37#discussion_r67426160","pull_request_url":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37","_links":{"self":{"href":"https://api.github.com/repos/WDI-SEA/booktown/pulls/comments/67426160"},"html":{"href":"https://github.com/WDI-SEA/booktown/pull/37#discussion_r67426160"},"pull_request":{"href":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37"}}},"pull_request":{"url":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37","id":74018008,"html_url":"https://github.com/WDI-SEA/booktown/pull/37","diff_url":"https://github.com/WDI-SEA/booktown/pull/37.diff","patch_url":"https://github.com/WDI-SEA/booktown/pull/37.patch","issue_url":"https://api.github.com/repos/WDI-SEA/booktown/issues/37","number":37,"state":"open","locked":false,"title":"Pull request for booktown deliverable","user":{"login":"dropTheNon","id":7907715,"avatar_url":"https://avatars.githubusercontent.com/u/7907715?v=3","gravatar_id":"","url":"https://api.github.com/users/dropTheNon","html_url":"https://github.com/dropTheNon","followers_url":"https://api.github.com/users/dropTheNon/followers","following_url":"https://api.github.com/users/dropTheNon/following{/other_user}","gists_url":"https://api.github.com/users/dropTheNon/gists{/gist_id}","starred_url":"https://api.github.com/users/dropTheNon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dropTheNon/subscriptions","organizations_url":"https://api.github.com/users/dropTheNon/orgs","repos_url":"https://api.github.com/users/dropTheNon/repos","events_url":"https://api.github.com/users/dropTheNon/events{/privacy}","received_events_url":"https://api.github.com/users/dropTheNon/received_events","type":"User","site_admin":false},"body":"**Deliverable Submission**\r\n\r\nPlease describe your comfort and completeness levels before submitting.\r\n\r\nComfort Level (1-5): 5\r\n\r\nCompleteness Level (1-5): 5\r\n\r\nWhat did you think of this deliverable?: Update: I love databases. That is all. :-)\r\n","created_at":"2016-06-16T03:40:33Z","updated_at":"2016-06-16T21:20:40Z","closed_at":null,"merged_at":null,"merge_commit_sha":"af64823735f01127b5a61f9072811db0c2fa6dfb","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37/commits","review_comments_url":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37/comments","review_comment_url":"https://api.github.com/repos/WDI-SEA/booktown/pulls/comments{/number}","comments_url":"https://api.github.com/repos/WDI-SEA/booktown/issues/37/comments","statuses_url":"https://api.github.com/repos/WDI-SEA/booktown/statuses/7190316d8a30cf7726ee13374aa39662783c43c5","head":{"label":"dropTheNon:master","ref":"master","sha":"7190316d8a30cf7726ee13374aa39662783c43c5","user":{"login":"dropTheNon","id":7907715,"avatar_url":"https://avatars.githubusercontent.com/u/7907715?v=3","gravatar_id":"","url":"https://api.github.com/users/dropTheNon","html_url":"https://github.com/dropTheNon","followers_url":"https://api.github.com/users/dropTheNon/followers","following_url":"https://api.github.com/users/dropTheNon/following{/other_user}","gists_url":"https://api.github.com/users/dropTheNon/gists{/gist_id}","starred_url":"https://api.github.com/users/dropTheNon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dropTheNon/subscriptions","organizations_url":"https://api.github.com/users/dropTheNon/orgs","repos_url":"https://api.github.com/users/dropTheNon/repos","events_url":"https://api.github.com/users/dropTheNon/events{/privacy}","received_events_url":"https://api.github.com/users/dropTheNon/received_events","type":"User","site_admin":false},"repo":{"id":61251188,"name":"booktown","full_name":"dropTheNon/booktown","owner":{"login":"dropTheNon","id":7907715,"avatar_url":"https://avatars.githubusercontent.com/u/7907715?v=3","gravatar_id":"","url":"https://api.github.com/users/dropTheNon","html_url":"https://github.com/dropTheNon","followers_url":"https://api.github.com/users/dropTheNon/followers","following_url":"https://api.github.com/users/dropTheNon/following{/other_user}","gists_url":"https://api.github.com/users/dropTheNon/gists{/gist_id}","starred_url":"https://api.github.com/users/dropTheNon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dropTheNon/subscriptions","organizations_url":"https://api.github.com/users/dropTheNon/orgs","repos_url":"https://api.github.com/users/dropTheNon/repos","events_url":"https://api.github.com/users/dropTheNon/events{/privacy}","received_events_url":"https://api.github.com/users/dropTheNon/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/dropTheNon/booktown","description":"Queries upon queries in Booktown, USA [sql, databases]","fork":true,"url":"https://api.github.com/repos/dropTheNon/booktown","forks_url":"https://api.github.com/repos/dropTheNon/booktown/forks","keys_url":"https://api.github.com/repos/dropTheNon/booktown/keys{/key_id}","collaborators_url":"https://api.github.com/repos/dropTheNon/booktown/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/dropTheNon/booktown/teams","hooks_url":"https://api.github.com/repos/dropTheNon/booktown/hooks","issue_events_url":"https://api.github.com/repos/dropTheNon/booktown/issues/events{/number}","events_url":"https://api.github.com/repos/dropTheNon/booktown/events","assignees_url":"https://api.github.com/repos/dropTheNon/booktown/assignees{/user}","branches_url":"https://api.github.com/repos/dropTheNon/booktown/branches{/branch}","tags_url":"https://api.github.com/repos/dropTheNon/booktown/tags","blobs_url":"https://api.github.com/repos/dropTheNon/booktown/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/dropTheNon/booktown/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/dropTheNon/booktown/git/refs{/sha}","trees_url":"https://api.github.com/repos/dropTheNon/booktown/git/trees{/sha}","statuses_url":"https://api.github.com/repos/dropTheNon/booktown/statuses/{sha}","languages_url":"https://api.github.com/repos/dropTheNon/booktown/languages","stargazers_url":"https://api.github.com/repos/dropTheNon/booktown/stargazers","contributors_url":"https://api.github.com/repos/dropTheNon/booktown/contributors","subscribers_url":"https://api.github.com/repos/dropTheNon/booktown/subscribers","subscription_url":"https://api.github.com/repos/dropTheNon/booktown/subscription","commits_url":"https://api.github.com/repos/dropTheNon/booktown/commits{/sha}","git_commits_url":"https://api.github.com/repos/dropTheNon/booktown/git/commits{/sha}","comments_url":"https://api.github.com/repos/dropTheNon/booktown/comments{/number}","issue_comment_url":"https://api.github.com/repos/dropTheNon/booktown/issues/comments{/number}","contents_url":"https://api.github.com/repos/dropTheNon/booktown/contents/{+path}","compare_url":"https://api.github.com/repos/dropTheNon/booktown/compare/{base}...{head}","merges_url":"https://api.github.com/repos/dropTheNon/booktown/merges","archive_url":"https://api.github.com/repos/dropTheNon/booktown/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/dropTheNon/booktown/downloads","issues_url":"https://api.github.com/repos/dropTheNon/booktown/issues{/number}","pulls_url":"https://api.github.com/repos/dropTheNon/booktown/pulls{/number}","milestones_url":"https://api.github.com/repos/dropTheNon/booktown/milestones{/number}","notifications_url":"https://api.github.com/repos/dropTheNon/booktown/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/dropTheNon/booktown/labels{/name}","releases_url":"https://api.github.com/repos/dropTheNon/booktown/releases{/id}","deployments_url":"https://api.github.com/repos/dropTheNon/booktown/deployments","created_at":"2016-06-16T00:59:51Z","updated_at":"2016-03-18T04:48:08Z","pushed_at":"2016-06-16T16:47:14Z","git_url":"git://github.com/dropTheNon/booktown.git","ssh_url":"git@github.com:dropTheNon/booktown.git","clone_url":"https://github.com/dropTheNon/booktown.git","svn_url":"https://github.com/dropTheNon/booktown","homepage":"","size":330,"stargazers_count":0,"watchers_count":0,"language":null,"has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"WDI-SEA:master","ref":"master","sha":"6be31e3fc44e1fc14dc0b3c482f0d5a631e262ff","user":{"login":"WDI-SEA","id":16675968,"avatar_url":"https://avatars.githubusercontent.com/u/16675968?v=3","gravatar_id":"","url":"https://api.github.com/users/WDI-SEA","html_url":"https://github.com/WDI-SEA","followers_url":"https://api.github.com/users/WDI-SEA/followers","following_url":"https://api.github.com/users/WDI-SEA/following{/other_user}","gists_url":"https://api.github.com/users/WDI-SEA/gists{/gist_id}","starred_url":"https://api.github.com/users/WDI-SEA/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/WDI-SEA/subscriptions","organizations_url":"https://api.github.com/users/WDI-SEA/orgs","repos_url":"https://api.github.com/users/WDI-SEA/repos","events_url":"https://api.github.com/users/WDI-SEA/events{/privacy}","received_events_url":"https://api.github.com/users/WDI-SEA/received_events","type":"Organization","site_admin":false},"repo":{"id":51113589,"name":"booktown","full_name":"WDI-SEA/booktown","owner":{"login":"WDI-SEA","id":16675968,"avatar_url":"https://avatars.githubusercontent.com/u/16675968?v=3","gravatar_id":"","url":"https://api.github.com/users/WDI-SEA","html_url":"https://github.com/WDI-SEA","followers_url":"https://api.github.com/users/WDI-SEA/followers","following_url":"https://api.github.com/users/WDI-SEA/following{/other_user}","gists_url":"https://api.github.com/users/WDI-SEA/gists{/gist_id}","starred_url":"https://api.github.com/users/WDI-SEA/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/WDI-SEA/subscriptions","organizations_url":"https://api.github.com/users/WDI-SEA/orgs","repos_url":"https://api.github.com/users/WDI-SEA/repos","events_url":"https://api.github.com/users/WDI-SEA/events{/privacy}","received_events_url":"https://api.github.com/users/WDI-SEA/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/WDI-SEA/booktown","description":"Queries upon queries in Booktown, USA [sql, databases]","fork":false,"url":"https://api.github.com/repos/WDI-SEA/booktown","forks_url":"https://api.github.com/repos/WDI-SEA/booktown/forks","keys_url":"https://api.github.com/repos/WDI-SEA/booktown/keys{/key_id}","collaborators_url":"https://api.github.com/repos/WDI-SEA/booktown/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/WDI-SEA/booktown/teams","hooks_url":"https://api.github.com/repos/WDI-SEA/booktown/hooks","issue_events_url":"https://api.github.com/repos/WDI-SEA/booktown/issues/events{/number}","events_url":"https://api.github.com/repos/WDI-SEA/booktown/events","assignees_url":"https://api.github.com/repos/WDI-SEA/booktown/assignees{/user}","branches_url":"https://api.github.com/repos/WDI-SEA/booktown/branches{/branch}","tags_url":"https://api.github.com/repos/WDI-SEA/booktown/tags","blobs_url":"https://api.github.com/repos/WDI-SEA/booktown/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/WDI-SEA/booktown/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/WDI-SEA/booktown/git/refs{/sha}","trees_url":"https://api.github.com/repos/WDI-SEA/booktown/git/trees{/sha}","statuses_url":"https://api.github.com/repos/WDI-SEA/booktown/statuses/{sha}","languages_url":"https://api.github.com/repos/WDI-SEA/booktown/languages","stargazers_url":"https://api.github.com/repos/WDI-SEA/booktown/stargazers","contributors_url":"https://api.github.com/repos/WDI-SEA/booktown/contributors","subscribers_url":"https://api.github.com/repos/WDI-SEA/booktown/subscribers","subscription_url":"https://api.github.com/repos/WDI-SEA/booktown/subscription","commits_url":"https://api.github.com/repos/WDI-SEA/booktown/commits{/sha}","git_commits_url":"https://api.github.com/repos/WDI-SEA/booktown/git/commits{/sha}","comments_url":"https://api.github.com/repos/WDI-SEA/booktown/comments{/number}","issue_comment_url":"https://api.github.com/repos/WDI-SEA/booktown/issues/comments{/number}","contents_url":"https://api.github.com/repos/WDI-SEA/booktown/contents/{+path}","compare_url":"https://api.github.com/repos/WDI-SEA/booktown/compare/{base}...{head}","merges_url":"https://api.github.com/repos/WDI-SEA/booktown/merges","archive_url":"https://api.github.com/repos/WDI-SEA/booktown/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/WDI-SEA/booktown/downloads","issues_url":"https://api.github.com/repos/WDI-SEA/booktown/issues{/number}","pulls_url":"https://api.github.com/repos/WDI-SEA/booktown/pulls{/number}","milestones_url":"https://api.github.com/repos/WDI-SEA/booktown/milestones{/number}","notifications_url":"https://api.github.com/repos/WDI-SEA/booktown/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/WDI-SEA/booktown/labels{/name}","releases_url":"https://api.github.com/repos/WDI-SEA/booktown/releases{/id}","deployments_url":"https://api.github.com/repos/WDI-SEA/booktown/deployments","created_at":"2016-02-04T23:34:33Z","updated_at":"2016-03-18T04:48:08Z","pushed_at":"2016-06-16T17:05:11Z","git_url":"git://github.com/WDI-SEA/booktown.git","ssh_url":"git@github.com:WDI-SEA/booktown.git","clone_url":"https://github.com/WDI-SEA/booktown.git","svn_url":"https://github.com/WDI-SEA/booktown","homepage":"","size":4403,"stargazers_count":0,"watchers_count":0,"language":null,"has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":43,"mirror_url":null,"open_issues_count":5,"forks":43,"open_issues":5,"watchers":0,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37"},"html":{"href":"https://github.com/WDI-SEA/booktown/pull/37"},"issue":{"href":"https://api.github.com/repos/WDI-SEA/booktown/issues/37"},"comments":{"href":"https://api.github.com/repos/WDI-SEA/booktown/issues/37/comments"},"review_comments":{"href":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37/comments"},"review_comment":{"href":"https://api.github.com/repos/WDI-SEA/booktown/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/WDI-SEA/booktown/pulls/37/commits"},"statuses":{"href":"https://api.github.com/repos/WDI-SEA/booktown/statuses/7190316d8a30cf7726ee13374aa39662783c43c5"}}}}
{"actor":{"display_login":"scottheron"}}