[Stanford]Introduction to databases (week3) 讨论帖！

bearkino 发表于 2014-1-28 21:26
那啥  刚开始做题  看到大家说union之类的关键词不能用。。就手贱去测试了下  但是结果通过了啊  0.0

在 ...

呀，你直接在文本框里用union可以允许成功？

sanguine 发表于 2014-1-28 23:06
呀，你直接在文本框里用union可以允许成功？

是啊  我直接成功了啊  0.0

sanguine 发表于 2014-1-28 23:06
Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NU ...

唔。  我开始哪儿写错了估计  

请问下为啥我用不了update语句啊。。
做第9题时我先得到一个reviewer和review次数的表格，然后把它用括号括起来作为update...set...语句的操作对象，但是总会报错这是啥情况。。
Query failed to execute: near 'select': syntax error或者Query failed to execute: near '(': syntax error

小弱问个问题，movie rating query core set第六题第七题怎么做啊？求个思路
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. 我只会找出rate了两次的movie，第二次高怎么选出来呢？
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.
为啥我写的SQL结果比答案少一项呢？？
select title,stars
from Rating,Movie
where Rating.mID in
(select Rating.mID
from Rating
group by Rating.mID
having count(*)>=1) and Movie.mID=Rating.mID
group by Rating.mID
stars=max(stars)
order by title;

企盼黎明~ 发表于 2014-2-2 11:33
小弱问个问题，movie rating query core set第六题第七题怎么做啊？求个思路
6.For all cases where the s ...

第六题我在另外一个贴回你了，第七题你用的方法好复杂阿

这样就可以呀
select title, max(stars)
from Movie, Rating
where Movie.mID=Rating.mID
group by Rating.mID
order by title

然后
(select Rating.mID
from Rating
group by Rating.mID
having count(*)>=1)
这种一般不用什么having count(*)>=1吧？你直接用exist，如果存在不就可以了么，和having count(*)>=1是一个意思

然后倒数第二行stars=max(stars) 没搞懂你这是什么意思，放在group by语句里的？没搞懂，我运行了你的程序，stars=max(stars) 直接报错的

求问Movie rating Challenge level Q4:
Find the movie(s) with the highest average rating. Return the movie title(s) and average rating.
怎样在where clause里实现找到最大的avgRating？

select title, avgRating
from
(select title, avg(stars) as avgRating
from Movie, Rating
where Movie.mID=Rating.mID
group by Rating.mID) T
where avgRating>=(select max(avgRating) from T) ？？？

斑竹，弱问下sql这个，老师上课demo用的软件是什么啊？是sql lite我的界面是个小黑窗口，没有那些操作的图形界面呢。另外，老师是不是没给我们他上课用的例子数据呢？student，apply，college这些。 谢谢你