亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

Database: coursera assignment 1

系統 2454 0

q.1:? Find the titles of all movies directed by Steven Spielberg.?

select title
from movie
where director = 'Steven Spielberg'

?

q.2:? Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order.

select year
from movie, rating
where movie.mid = rating.mid and rating.stars >= 4
group by movie.mid
order by year

?

q.3:? Find the titles of all movies that have no ratings.?

select title
from movie, rating
where movie.mid not in (select rating.mid from rating)
group by movie.mid

?

q.4:? Some reviewers didn't provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date.?

select name
from reviewer re, rating ra
where re.rid = ra.rid and ra.ratingdate is null

?

q.5:? Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie title, and lastly by number of stars.?

select name, title, stars, ratingdate
from movie, reviewer, rating
where movie.mid = rating.mid and reviewer.rid = rating.rid
order by name, title, stars

?

q.6:? For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie.?

select name, title
from movie, reviewer, rating r1, rating r2
where movie.mid = r1.mid and reviewer.rid = r1.rid and r1.mid = r2.mid and r1.rid = r2.rid and r1.stars < r2.stars and r1.ratingdate < r2.ratingdate

?

q.7:? For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title.?

select title, max(stars)
from movie, rating
where movie.mid = rating.mid
group by rating.mid
order by title

?

q.8:? For each movie, return the title and the 'rating spread', that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title.?

select title, max(stars) - min(stars) as spread
from movie, rating
where movie.mid = rating.mid
group by rating.mid
order by spread desc, title

?

q.9:? Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)?

?

Database: coursera assignment 1


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 国产色视频一区二区三区 | 亚洲偷图色综合色就色 | 亚洲精品日本一区二区在线 | 亚洲国产精品一区二区九九 | 一区二区三区四区国产 | 99激情网 | 亚洲国产精品婷婷久久 | 九九国产精品九九 | 国产亚洲在线观看 | 99热久久这里只精品国产ww | 神马影院伦理我不卡 | 久久国产资源 | 伊人色婷婷综在合线亚洲 | 久草久草久草 | 色久优优 欧美色久优优 | 农村妇女又色黄一级毛片 | 国产精品美女久久久久 | 久久精品视频2 | 尹人香蕉网在线观看视频 | 欧洲黄色毛片 | 亚洲欧洲久久 | 一区二区三区四区在线视频 | 亚洲欧美中文日韩在线 | 黄视频在线观看网站 | 国产在线精品香蕉综合网一区 | 5x性区m免费毛片视频看看 | 奇米影视888狠狠狠777九色 | 国产精品 视频一区 二区三区 | 亚洲欧美人成综合在线最新 | 国产成人午夜精品影院游乐网 | 韩国19禁青草福利视频在线 | 中文字幕中文字幕在线 | 久久久久亚洲精品影视 | 在线免费观看一区二区三区 | 亚洲精品国产suv一区88 | 久久高清免费视频 | 日本不卡高清免费v | 亚洲码和乱人伦中文一区 | 日韩免费一级毛片 | 成人国产精品免费视频 | 天天综合天天做天天综合 |