JAVA学习数据库7

-- 显示电影id=1中演员名单

SELECT a.NAME ,m.title FROM actor a,movie m,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND m.id=1;

-- 显示电影'Alien'中演员名单

SELECT a.* FROM movie m ,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND m.title='Alien';

-- 显示有'Harrison Ford'参演的电影名称

SELECT m.title,a.NAME FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND a.NAME='Harrison Ford';

-- 显示有'Harrison Ford'参演的电影名称，但'Harrison Ford'角色排名不是第一

SELECT m.title,c.movieord FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND a.NAME='Harrison Ford' AND c.movieord!=1;

-- 显示1962年发行的电影，及该影片的主角(排名第一)

SELECT m.title,a.NAME ,c.movieord FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid

AND m.yr='1962' AND c.movieord=1;

-- 显示'John Travolta'每年出演的电影数量

SELECT m.yr,COUNT(m.title) FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND a.NAME='John Travolta'

GROUP BY m.yr

-- 列出'Julie Andrews'参演的所有电影名称以及该片的主角（先用这个人筛出演过的电影作为条件，

-- 然后用m。title in 这些电影，来重新约束一个id，将这些id连带的主演和电影名导出来)

SELECT m.title ,c.movieord ,a.NAME FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid

AND c.movieord=1 AND m.title

IN(

SELECT m.title  FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid

AND a.NAME='Julie Andrews' )

-- 列出在10部电影中出任过主角的演员

SELECT a.NAME ,COUNT(m.title)mm FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND c.movieord=1 

GROUP BY a.id HAVING mm>10;

-- 按演员人数的多少依次列出1978的电影以及演员数。

SELECT m.title,COUNT(a.id)num FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND m.yr='1978'

GROUP BY m.title ORDER BY num ;

-- 列出所有和'Art Garfunkel'合作过的演员(去除重复记录，还要去掉自己)

SELECT DISTINCT a.NAME ,m.title FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid

AND a.NAME!='Art Garfunkel'

AND m.title IN(SELECT m.title FROM movie m,actor a,casting c WHERE m.id=c.movieid AND a.id=c.actorid AND a.NAME='Art Garfunkel')