sqlzoo练习系列(十二)——合并之电影数据库
【Tips】
- sqlzoo官网链接:
- 每个系列开头介绍所用表及其信息
- 每道题含有题目描述、代码和结果,本系列第15题测试未通过
- 题目为自己简写,最好在官网查看具体题目
- 部分测试结果不完整,仅为一部分截图
合并之电影数据库练习链接:
SELECT id,title FROM movie WHERE yr=1962
SELECT yr FROM movie WHERE title= 'Citizen Kane'
SELECT id,title,yr FROM movie WHERE title LIKE 'Star Trek%' ORDER BY yr
SELECT title FROM movie WHERE id IN (11768,11955,21191)
SELECT id FROM actor WHERE name='Glenn Close'
SELECT id FROM movie WHERE title='Casablanca'
SELECT name FROM actor JOIN casting ON id=actorid WHERE movieid=11768
SELECT name FROM actor JOIN casting ON id=actorid WHERE movieid=(SELECT id FROM movie WHERE title='Alien')
SELECT title FROM movie JOIN casting ON movie.id=casting.movieid JOIN actor ON casting.actorid=actor.id WHERE actor.name='Harrison Ford'
SELECT title FROM movie JOIN casting ON movie.id=casting.movieid JOIN actor ON casting.actorid=actor.id WHERE actor.name='Harrison Ford' AND ord!=1
SELECT title,name FROM movie JOIN casting ON movie.id=casting.movieid JOIN actor ON casting.actorid=actor.id WHERE yr=1962 AND ord=1
SELECT yr,COUNT(title) FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id where name='John Travolta' GROUP BY yr HAVING COUNT(title)=(SELECT MAX(c) FROM (SELECT yr,COUNT(title) AS c FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id where name='John Travolta' GROUP BY yr) AS t)
SELECT title,name FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id WHERE movie.id IN (SELECT movie.id FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id WHERE name='Julie Andrews') AND ord=1
SELECT name FROM actor JOIN casting ON id=actorid WHERE ord=1 GROUP BY name HAVING COUNT(name)>=30 ORDER BY name
SELECT title,COUNT(actorid) FROM movie JOIN casting ON id=movieid WHERE yr=1978 GROUP BY title ORDER BY COUNT(actorid) DESC
SELECT name FROM movie JOIN casting ON movie.id=casting.movieid JOIN actor ON actorid=actor.id WHERE title IN (SELECT DISTINCT title FROM movie JOIN casting ON movie.id=casting.movieid JOIN actor ON actorid=actor.id WHERE name='Art Garfunkel') AND name != 'Art Garfunkel'