SQL练习911

-- 找出包含song(歌曲)'Alison'的专辑title(名称)和artist(作者)
SELECT a.title,a.artist FROM album a ,track t WHERE a.albumCode=t.album AND t.song='Alison';


--  哪个artist（作者）录制了song(歌曲)'Exodus'
SELECT a.artist FROM album a,track t WHERE a.albumCode=t.album AND t.song='Exodus';


-- 列出track(曲目表)中所有属于'Blur' album(专辑)的song(歌曲)


SELECT t.* FROM album a,track t WHERE a.albumCode=t.album 
AND a.artist='Blur';


-- 显示每张album(专辑)的title(名称)和包含track曲目数
SELECT a.title,COUNT(t.song) FROM album a,track t WHERE a.albumCode=t.album GROUP BY a.title


-- 显示每张album(专辑)的title(名称)，以及曲目名称中包含'Heart'的曲目总数
SELECT a.title  COUNT(t.song) FROM album a,track t WHERE a.albumCode=t.album 
AND t.song LIKE '%Heart%'
GROUP BY a.title;




SELECT a.title COUNT(t.song) FROM album  a LEFT JOIN track t ON a.albumCode=t.album 
AND t.song LIKE '%Heart%'
GROUP BY a.title;
-- 找出所有song(歌名)和title(专辑名)相同的曲目


SELECT t.song FROM album a,track t WHERE a.albumCode=t.album 
AND a.title= t.song;




-- 找出所有专辑名(title)和artist(作者名)相同的专辑
SELECT *FROM album a WHERE a.title=a.artist;




-- 找出在两张以上专辑中出现过的歌曲，同时包含出现的次数
SELECT t.song ,COUNT(t.song) FROM album a ,track t WHERE a.albumCode=t.album 
GROUP BY t.song HAVING COUNT(t.song)>2


-- 找出每首歌曲的定价低于0.5的专辑，显示专辑标题、价格和曲目总数
SELECT a.title,a.price,COUNT(t.song) FROM album a,track t WHERE a.albumCode=t.album  GROUP BY a.title
HAVING a.price/COUNT(t.song)<0.5


-- 按专辑包含曲目多少从多到少依次列出专辑名称（title）和包含曲目总数
SELECT a.title,COUNT(t.song) FROM album a,track t WHERE a.albumCode=t.album

GROUP BY a.title ORDER BY COUNT(t.song) DESC;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- 显示电影id=1中演员名单
SELECT a.* FROM  actor a,casting c 
WHERE a.id=c.actorid  AND c.movieid=1


-- 显示电影'Alien'中演员名单
SELECT a.* FROM actor a,casting c ,movie m
WHERE a.id=c.actorid AND m.id=c.movieid AND m.title='Alien';




-- 显示有'Harrison Ford'参演的电影名称
SELECT m.title FROM  actor a,casting c ,movie m
WHERE a.id=c.actorid AND m.id=c.movieid AND a.NAME='Harrison Ford';


-- 显示有'Harrison Ford'参演的电影名称，但'Harrison Ford'角色排名不是第一
SELECT m.title FROM  actor a,casting c ,movie m
WHERE a.id=c.actorid AND m.id=c.movieid AND a.NAME='Harrison Ford' AND c.movieord !=1;


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


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


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


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


-- 列出'Julie Andrews'参演的所有电影名称以及该片的主角
SELECT m.title,a.NAME FROM  actor a,casting c ,movie m
WHERE a.id=c.actorid AND m.id=c.movieid AND m.id IN(
SELECT m1.id FROM casting c1,movie m1,actor a1 WHERE m1.id=c1.movieid AND
a1.id=c1.actorid AND a1.NAME='Julie Andrews'
) AND c.movieord=1;


-- 列出在10部电影中出任过主角的演员
SELECT a.NAME FROM actor a,casting c ,movie m WHERE a.id=c.actorid AND m.id=c.movieid
AND c.movieord=1
GROUP BY a.NAME HAVING COUNT(m.title)=10




-- 按演员人数的多少依次列出1978的电影以及演员数。
SELECT m.title ,COUNT(a.NAME) FROM actor a,casting c ,movie m WHERE a.id=c.actorid AND m.id=c.movieid
AND m.yr=1978 GROUP BY m.title ORDER BY COUNT(a.NAME) DESC;


-- 列出所有和'Art Garfunkel'合作过的演员
SELECT DISTINCT a.NAME FROM actor a,casting c ,movie m WHERE a.id=c.actorid AND m.id=c.movieid
AND m.id IN(
SELECT c1.movieid FROM actor a1,casting c1 WHERE a1.id=c1.actorid AND a1.NAME='Art Garfunkel'
)AND a.NAME !='Art Garfunkel';