{"id":480,"date":"2014-10-01T22:00:38","date_gmt":"2014-10-02T06:00:38","guid":{"rendered":"http:\/\/www.tech.dimprash.com\/?p=480"},"modified":"2014-10-01T22:01:41","modified_gmt":"2014-10-02T06:01:41","slug":"mysql-query-set","status":"publish","type":"post","link":"http:\/\/www.tech.dimprash.com\/?p=480","title":{"rendered":"MySQL Query Set"},"content":{"rendered":"<p>1)<br \/>\n<a href=\"http:\/\/www.crazyforcode.com\/mysql-query-set-5\/\">http:\/\/www.crazyforcode.com\/mysql-query-set-5\/<\/a><\/p>\n<pre>\r\nWe have 3 tables Movie, Reviewer, Rating as shown below:\r\nMovie ( mID, title, year, director )\r\nThere is a movie with ID number mID, a title, a release year, and a director.\r\n\r\nReviewer ( rID, name )\r\nThe reviewer with ID number rID has a certain name.\r\n\r\nRating ( rID, mID, stars, ratingDate )\r\nThe reviewer rID gave the movie mIDa number of stars rating (1-5) on a certain ratingDate.\r\n\r\nQ1. Find the titles of all movies that have no ratings.\r\nQ2. For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer\u2019s name and the title of the movie.\r\n\r\nAns 1) select title from movie where mid not in (select distinct mid from rating)\r\n\r\nAns 2) \r\nSELECT  NAME,TITLE FROM RATING AS R1,RATING AS R2,REVIEWER,MOVIE \r\nWHERE MOVIE.MID=R1.MID AND REVIEWER.RID=R1.RID \r\nAND R1.MID=R2.MID AND R1.RID = R2.RID \r\nAND R1.STARS < R2.STARS \r\nAND R1.RATINGDATE < R2.RATINGDATE \r\nORDER BY R1.RATINGDATE ASC\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>1) http:\/\/www.crazyforcode.com\/mysql-query-set-5\/ We have 3 tables Movie, Reviewer, Rating as shown below: Movie ( mID, title, year, director ) There is a movie with ID number mID, a title, a release year, and a director. Reviewer ( rID, name ) The reviewer with ID number rID has a certain name. Rating ( rID, mID, stars, &hellip; <a href=\"http:\/\/www.tech.dimprash.com\/?p=480\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">MySQL Query Set<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-480","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/480","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=480"}],"version-history":[{"count":2,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/480\/revisions"}],"predecessor-version":[{"id":482,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/480\/revisions\/482"}],"wp:attachment":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=480"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}