根据下面的关系写出SQl:
MovieInfo (mvID, title, rating, year, length, studio)
Director(directorID, firstname, lastname)
Member(username, email, password)
Actor(actorID, firstname, lastname, gender, birthplace)
Cast(mvID*, actorID*)
Direct(mvID*, directorID*)
Genre(mvID*, genre)
Ranking(username*, mvID*, score, voteDate)
问题1:a) Rewrite the query below without using the NOT EXISTS or NOT IN operators.
select firstname, lastname
from actor
where not exists
(select *
from cast
where cast.actorid = actor.actorID);问题2:b) What is the total number of movies directed by each director? Output each director
first name and last name together with his/her total number of movies. Your output
should include directors who have not directed any movies.
问题3:c) List studios whose score for Action movies is higher than the average ranking score of
Action movies.
问题4:d) Show the actorID, firstname, lastname of actors who played in drama movies, together
with the number of drama movies they played. Show the list in descending order by
number of movies played. You must use a subquery.
问题5:e) Explain in English what the query below is doing.
select actor.actorID,actor.firstname,actor.lastname, genre.mvid, genre.genre
from cast, genre, actor
where cast.actorID = actor.actorID
and cast.mvID = genre.mvID
and actor.actorID not in (
select actorID
from cast, genre
where cast.mvID =genre.mvID
and genre.genre not in ('Drama','Comedy'));
MovieInfo (mvID, title, rating, year, length, studio)
Director(directorID, firstname, lastname)
Member(username, email, password)
Actor(actorID, firstname, lastname, gender, birthplace)
Cast(mvID*, actorID*)
Direct(mvID*, directorID*)
Genre(mvID*, genre)
Ranking(username*, mvID*, score, voteDate)
问题1:a) Rewrite the query below without using the NOT EXISTS or NOT IN operators.
select firstname, lastname
from actor
where not exists
(select *
from cast
where cast.actorid = actor.actorID);问题2:b) What is the total number of movies directed by each director? Output each director
first name and last name together with his/her total number of movies. Your output
should include directors who have not directed any movies.
问题3:c) List studios whose score for Action movies is higher than the average ranking score of
Action movies.
问题4:d) Show the actorID, firstname, lastname of actors who played in drama movies, together
with the number of drama movies they played. Show the list in descending order by
number of movies played. You must use a subquery.
问题5:e) Explain in English what the query below is doing.
select actor.actorID,actor.firstname,actor.lastname, genre.mvid, genre.genre
from cast, genre, actor
where cast.actorID = actor.actorID
and cast.mvID = genre.mvID
and actor.actorID not in (
select actorID
from cast, genre
where cast.mvID =genre.mvID
and genre.genre not in ('Drama','Comedy'));
解决方案 »
- 【求助】oracle 11g Flashback data archive支持带blob字段的表吗??
- 一个select的问题、
- oracle发送邮件
- 求一个行列转换的写法(ORACLE),多谢
- 请问update句法如何实现全字段更新,而不需要罗列字段。
- 请问哪位是oracle7.3的数据库啊,求core34.dll,收到后马上结贴!
- oracle字段如何自动增加1
- 请问,oracle导出整个数据库应该怎么做
- Database Link与Socket那种方式更好?
- Oracle8.1.7本地可以SYS以NORMAL身份登录,但是不能用SYSDBA登录,怎么办???
- 关于表连接时索引的使用
- sqlplus问题求助
select firstname, lastname
from actor
where not exists
(select * ---////可以把这个“*”号改成'1'。
from cast
where cast.actorid = actor.actorID);
2.引用:Your output should include directors who have not directed any movies.
需要关联,MovieInfo 为主表
3.子查询(似乎开窗函数也可以实现,但我不常用忘了;-))
4.引用:You must use a subquery.
5.先看not in 里面的吧
Explain in English !!!!??( ⊙ o ⊙ )啊!
还是原样给你复制回去吧,paste 哈哈
-----------------------
先想清楚这几个表(想成实物)的关系在做吧
自己动手,丰衣足食