SQLZOO习题详解(08)——The JOIN operation

我会带着你远行 2023-01-19 14:50 3阅读 0赞

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

*  本文原创作者：谷哥的小弟
 *  作者博客地址：http://blog.csdn.net/lfdfhl

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

# 表及其关系 #

movie表如下：  
![在这里插入图片描述][20210413174727883.png]  
![在这里插入图片描述][20210413175012155.png]

actor表如下：  
![在这里插入图片描述][20210413174739282.png]  
![在这里插入图片描述][20210413175052202.png]

casting表如下：  
![在这里插入图片描述][2021041317475444.png]  
![在这里插入图片描述][20210413175122970.png]  
表的关系如下：  
![在这里插入图片描述][20210413175427260.png]

# 链接地址 #

[https://napier.sqlzoo.net/wiki/More\_JOIN\_operations][https_napier.sqlzoo.net_wiki_More_JOIN_operations]

# SQL练习 #

01、请查询1962首映的电影的id和title

SELECT id, title FROM movie WHERE yr=1962

02、请查询电影Citizen Kane’的首影年份

SELECT yr FROM movie WHERE title= 'Citizen Kane'

03、查询电影名以Star Trek开头的电影的id、title、yr

SELECT id, title, yr FROM movie WHERE title LIKE 'Star Trek%' ORDER BY yr

04、查询演员Glenn Close对应的编号id

SELECT id FROM actor WHERE name = 'Glenn Close'

05、查询电影Casablanca对应的编号id

SELECT id FROM movie WHERE title LIKE 'Casablanca'

06、请查询参演电影编号为11768的电影的演员名

SELECT name
    FROM actor INNER JOIN casting ON actor.id = casting.actorid
    WHERE casting.movieid = 11768

SELECT name
    FROM actor
    WHERE id IN (
    	SELECT actorid
    	FROM movie INNER JOIN casting ON movie.id = casting.movieid
    	WHERE movie.id = 11768
    )

07、查询电影名为Alien的电影的演员

SELECT name FROM actor
    WHERE id IN (
    	SELECT actorid
    	FROM movie INNER JOIN casting ON movie.id = casting.movieid
    	WHERE movie.id = (
    		SELECT id
    		FROM movie
    		WHERE title LIKE 'Alien'
    	)
    )

08、查询Harrison Ford参演过的所有电影的名称

SELECT title
    FROM movie JOIN casting ON movie.id = casting.movieid
    WHERE casting.actorid = (
    	SELECT id
    	FROM actor
    	WHERE name = 'Harrison Ford'
    )

09、查询Harrison Ford参演过但是不是第一主角的电影的名称

SELECT title
    FROM movie JOIN casting ON (movie.id= casting.movieid)
    WHERE casting.actorid =(
      SELECT id 
      FROM actor
      WHERE name = 'Harrison Ford') AND casting.ord!=1

10、请查询1962年首映的电影以及它的第一主角的名字

SELECT m.title, a.name
    FROM casting AS c 
    INNER JOIN movie AS m ON (m.id= c.movieid)
    INNER JOIN actor AS a ON (a.id= c.actorid)
    WHERE c.ord = 1 AND yr = 1962

11、请查询演员Rock Hudson年度参演电影数量大于2部的年份及该年份参演的电影的具体数量

SELECT yr, COUNT(title) FROM movie
    INNER JOIN casting ON movie.id = movieid
    INNER JOIN actor ON actorid = actor.id
    WHERE name = 'Rock Hudson'
    GROUP BY yr HAVING COUNT(title) > 2;

![在这里插入图片描述][20210413185501403.png]  
12、请查询Julie Andrews参演过的电影的名称已经该电影的第一主角姓名

SELECT title, name
    FROM casting
    	JOIN movie ON movie.id = casting.movieid
    	JOIN actor ON casting.actorid = actor.id
    WHERE movieid IN (
    		SELECT movieid
    		FROM casting
    		WHERE actorid = (
    			SELECT id
    			FROM actor
    			WHERE name = 'Julie Andrews'
    		)
    	)
    	AND ord = 1;

13、查询至少15次当第一主角的演员的名字并按照名字排序

SELECT name
    FROM actor
    	JOIN casting ON casting.actorid = actor.id
    	JOIN movie ON movie.id = casting.movieid
    WHERE casting.ord = 1
    GROUP BY actor.name
    HAVING count(title) >= 15
    ORDER BY actor.name

14、请查询1978首映的电影。请将结果按照电影参演人数和电影名字排序

SELECT title, COUNT(actorid) AS num
    FROM casting JOIN movie ON id = movieid
    WHERE yr= 1978
    GROUP BY movieid, title
    ORDER BY num DESC, title;

SELECT title, count(actorid)
    FROM movie INNER JOIN casting ON casting.movieid = movie.id
    WHERE yr = '1978'
    GROUP BY title
    ORDER BY count(actorid) DESC, title

15、请查询与Art Garfunkel合作过的演员的名字

SELECT name
    FROM actor INNER JOIN casting ON actor.id = casting.actorid
    WHERE movieid IN (
    		SELECT movieid
    		FROM casting
    		WHERE actorid IN (
    			SELECT id
    			FROM actor
    			WHERE name = 'Art Garfunkel'
    		)
    	)
    	AND name != 'Art Garfunkel'

SELECT name
    FROM actor INNER JOIN casting ON actor.id = casting.actorid
    WHERE movieid IN (
    		SELECT movieid
    		FROM casting INNER JOIN actor ON casting.actorid = actor.id
    		WHERE name = 'Art Garfunkel'
    	)
    	AND name != 'Art Garfunkel'

[20210413174727883.png]: /images/20221021/d634dce5c4aa47c1925eefa202a1d2f6.png
[20210413175012155.png]: /images/20221021/093d34f15e6548b08eb54335281a07be.png
[20210413174739282.png]: /images/20221021/44d2c1a9434e478a9341114a9aa8320e.png
[20210413175052202.png]: /images/20221021/8c1e0009713f4547bc5fdefddf73b3da.png
[2021041317475444.png]: /images/20221021/09cf4b5c46f24411a10c67762d400244.png
[20210413175122970.png]: /images/20221021/86ea7a89e65445b79294e0ef87e35c2c.png
[20210413175427260.png]: /images/20221021/2fee27423ff343289eea5013ccff038f.png
[https_napier.sqlzoo.net_wiki_More_JOIN_operations]: https://napier.sqlzoo.net/wiki/More_JOIN_operations
[20210413185501403.png]: /images/20221021/8562b2a318d147359ec82d4b1df35ce4.png