求助Oracle的几个SQL语句

[欢乐极客]

有这样几个表，红色表示主键，蓝色表示外键。
MovieInfo(mvID,title,rating,year,length,studio)
Director(directorID,firstname,lastname)
Memeber(username,email,password)
Actor(actorID,firstname,lastname,gender,birthplace)
Cast(mvID,actorID)
Direct(mvID,directorID)
Genre(mvID,genre)
Ranking(username,mvID,score,voteDate)

1、找出有相同数量导演Director和相同数量演员的电影，输出这些电影的id（mvID）。
2、找出12个月中哪个月的票数最高，输出月份和总票数。（PS：应该是在Ranking中查询，要

求使用to_char）
3、列出仅仅对Drama（导演）的电影投了票的Memebers的username，要求使用MINUS。

PS:不是作业，是本人想学习Oracle，但不知从何下手。希望高手解决。

[iqlife]

1:
select ca.mvID 
from Cast ca,Direct dr
where ca.mvID =dr.mvID 
group by ca.mvID
having count(ca.actorID)=count(dr.directorID)

2:
select to_char(voteDate,'yyyy-mm') as yyyymm ,sum(score)
from Ranking 
where rownum=1
group by to_char(voteDate,'yyyy-mm')

3:select username
from Ranking rk,Director dr
where rk.mvID=dr.mvID
and dr.lastname='Drama'

[tangren]

--1.
SELECT mvID, actCount, dirCount
  FROM (SELECT mvID, COUNT(*) actCount FROM CAST GROUP BY mvID) a,
       (SELECT mvID, COUNT(*) dirCount FROM Direct GROUP BY mvID) b
 WHERE a.mvID = v.mvID
 GROUP BY mvID, actCount, dirCount
HAVING COUNT(1) > 1
 ORDER BY mvID, actCount, dirCount;
--2.
SELECT *
  FROM (SELECT rownum rn, ym, totalscore
          FROM (SELECT to_char(voteDate, 'yyyymm') ym, SUM(score) totalscore
                  FROM Ranking
                 GROUP BY to_char(voteDate, 'yyyymm')
                 ORDER BY SUM(score) DESC))
 WHERE rn = 1;
--3. 
SELECT username
  FROM Ranking r, direct d, director d1
 WHERE d1.directorID = d.directorID AND
       d1.firstname = 'Drama' AND
       r.mvID = d.mvID
MINUS
SELECT username
  FROM Ranking r, direct d, director d1
 WHERE d1.directorID = d.directorID AND
       d1.firstname != 'Drama' AND
       r.mvID = d.mvID

[欢乐极客]

[Quote=引用 2 楼 java3344520 的回复:]

SQL code
1:
select ca.mvID
from Cast ca,Direct dr
where ca.mvID =dr.mvID
group by ca.mvID
having count(ca.actorID)=count(dr.directorID)

2:
select to_char(voteDate,'yyyy-mm') as yyyymm ,sum(score)……
[/Quote]

第三个你没用minus哈

不过还是非常感谢你们！！

[欢乐极客]