{"id":1303,"date":"2016-11-19T22:24:31","date_gmt":"2016-11-20T03:24:31","guid":{"rendered":"http:\/\/my.dev.vanderbilt.edu\/cs265\/?page_id=1303"},"modified":"2018-01-18T18:08:02","modified_gmt":"2018-01-18T23:08:02","slug":"introductory-sql-exercises-2","status":"publish","type":"page","link":"https:\/\/my.dev.vanderbilt.edu\/cs265\/introductory-sql-exercises-2\/","title":{"rendered":"Introductory SQL Exercises 2"},"content":{"rendered":"<ul>\n<li>Use template below for preparing the answers to the two exercise sets<\/li>\n<li>Do DB5 <strong>Extra<\/strong> QUERY exercises (<a href=\"https:\/\/class.stanford.edu\/courses\/DB\/SQL\/SelfPaced\/courseware\/ch-sql\/seq-exercise-sql_movie_query_extra\/\">https:\/\/class.stanford.edu\/courses\/DB\/SQL\/SelfPaced\/courseware\/ch-sql\/seq-exercise-sql_movie_query_extra\/<\/a> (Q1-Q6, Q8 without HAVING or COUNT, Q9 without COUNT)<\/li>\n<li>and\u00a0<a href=\"https:\/\/class.stanford.edu\/courses\/DB\/SQL\/SelfPaced\/courseware\/ch-sql\/seq-exercise-sql_social_query_extra\/\">https:\/\/class.stanford.edu\/courses\/DB\/SQL\/SelfPaced\/courseware\/ch-sql\/seq-exercise-sql_social_query_extra\/<\/a> (Q1-Q2)<\/li>\n<\/ul>\n<p>&#8212;&#8211;<br \/>\nAnswers to DB5 EXTRA Query exercises<\/p>\n<p>ANSWERS FOR seq-exercise-sql_movie_query_extra<\/p>\n<p>Q1 Answer:<\/p>\n<p>SELECT DISTINCT Re.name<br \/>\nFROM Reviewer Re, Rating Ra, Movie M<br \/>\nWHERE Re.rID = Ra.rID AND Ra.mID = M.mID AND M.title = &#8216;Gone with the Wind&#8217;<\/p>\n<p>Q2 Answer:<\/p>\n<p>SELECT Re.name, M.title, Ra.stars<br \/>\nFROM Reviewer Re, Rating Ra, Movie M<br \/>\nWHERE Re.name = M.director AND M.mID = Ra.mID AND Ra.rID = Re.rID<\/p>\n<p>Q3 Answer:<\/p>\n<p>SELECT Re.name AS Label \/* don&#8217;t need to rename attribute *\/<br \/>\nFROM Reviewer Re<br \/>\nUNION<br \/>\nSELECT M.title AS Label<br \/>\nFROM Movie M<br \/>\nORDER BY Label \/* or title or name *\/<\/p>\n<p>Q4 Answer:<\/p>\n<p><del>SELECT DISTINCT M.title FROM Reviewer Re, Rating Ra, Movie M<\/del><br \/>\n<del>WHERE M.mID = Ra.mID AND Ra.rID = Re.rID AND Re.name &lt;&gt; &#8216;Chris Jackson&#8217; <\/del>\u00a0<span style=\"color: #ff0000\">Wrong &#8212; why?<\/span><\/p>\n<p>SELECT M.title FROM Movie M<br \/>\nEXCEPT<br \/>\nSELECT M.title FROM Movie M, Rating Ra, Reviewer Re<br \/>\nWHERE Re.name = &#8216;Chris Jackson&#8217; AND Re.rID = Ra.rID AND Ra.mID = M.mID<\/p>\n<p>SELECT M.title FROM Movie M<br \/>\nWHERE M.mID NOT IN<br \/>\n(SELECT M.mID FROM Movie M, Rating Ra, Reviewer Re<br \/>\nWHERE Re.name = &#8216;Chris Jackson&#8217; AND Re.rID = Ra.rID AND Ra.mID = M.mID)<\/p>\n<p>Q5 Answer:<\/p>\n<p><del>SELECT DISTINCT Re1.name, Re2.name<\/del><br \/>\n<del>FROM Reviewer Re1, Reviewer Re2, Rating Ra1, Rating Ra2<\/del><br \/>\n<del>WHERE Re1.rID &lt; Re2.rID AND <\/del><br \/>\n<del> Re1.rID = Ra1.rID AND <\/del><br \/>\n<del> Re2.rID = Ra2.rID AND <\/del><br \/>\n<del> Ra1.mID = Ra2.mID<\/del>\u00a0 <span style=\"color: #ff0000\">Wrong &#8212; why?<\/span><\/p>\n<p>SELECT DISTINCT Re1.name, Re2.name<br \/>\nFROM Reviewer Re1, Reviewer Re2, Rating Ra1, Rating Ra2<br \/>\nWHERE<br \/>\nRe1.rID = Ra1.rID AND<br \/>\nRe2.rID = Ra2.rID AND<br \/>\nRa1.mID = Ra2.mID AND<br \/>\nRe1.name &lt; Re2.name<\/p>\n<p>&nbsp;<\/p>\n<p>\/* consider possibility of same-named reviewers *\/<br \/>\nSELECT DISTINCT Re1.name, Re2.name<br \/>\nFROM Reviewer Re1, Reviewer Re2, Rating Ra1, Rating Ra2<br \/>\nWHERE Re1.rID &lt;&gt; Re2.rID AND<br \/>\nRe1.rID = Ra1.rID AND<br \/>\nRe2.rID = Ra2.rID AND<br \/>\nRa1.mID = Ra2.mID AND<br \/>\nRe1.name &lt;= Re2.name\u00a0 <span style=\"color: #0000ff\">Best answer &#8212; why?<\/span><br \/>\nQ6 Answer:<\/p>\n<p><span style=\"color: #0000ff\">left it as a challenge, using construct found in Quiz Q-w2 key for finding minimal stars (similar to finding oldest vehicle)<\/span><\/p>\n<p>Q8 Answer without HAVING or COUNT:<\/p>\n<p>Q9 Answer without COUNT:<\/p>\n<p>ANSWERS for seq-exercise-sql_social_query_extra<\/p>\n<p>Q1 Answer:<\/p>\n<p>Q2 Answer:<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Use template below for preparing the answers to the two exercise sets Do DB5 Extra QUERY exercises (https:\/\/class.stanford.edu\/courses\/DB\/SQL\/SelfPaced\/courseware\/ch-sql\/seq-exercise-sql_movie_query_extra\/ (Q1-Q6, Q8 without HAVING or COUNT, Q9 without COUNT) and\u00a0https:\/\/class.stanford.edu\/courses\/DB\/SQL\/SelfPaced\/courseware\/ch-sql\/seq-exercise-sql_social_query_extra\/ (Q1-Q2) &#8212;&#8211; Answers to DB5 EXTRA Query exercises ANSWERS FOR seq-exercise-sql_movie_query_extra Q1 &hellip; <a href=\"https:\/\/my.dev.vanderbilt.edu\/cs265\/introductory-sql-exercises-2\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":633,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1303","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/pages\/1303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/users\/633"}],"replies":[{"embeddable":true,"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/comments?post=1303"}],"version-history":[{"count":7,"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/pages\/1303\/revisions"}],"predecessor-version":[{"id":2052,"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/pages\/1303\/revisions\/2052"}],"wp:attachment":[{"href":"https:\/\/my.dev.vanderbilt.edu\/cs265\/wp-json\/wp\/v2\/media?parent=1303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}