SQLZOO more JOIN

最新推荐文章于 2025-11-02 19:28:35 发布

原创最新推荐文章于 2025-11-02 19:28:35 发布 · 263 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

sql 专栏收录该内容

6 篇文章

订阅专栏

这个博客展示了多个SQL查询示例，涉及从电影数据库中提取特定信息，如特定年份的电影、指定电影的年份、以'Star Trek'开头的电影ID、特定演员的ID、电影'Casablanca'和'Alien'的主要演员、'Harrison Ford'出演的所有电影、'Rock Hudson'主演的电影数量大于2的年份、由'Julie Andrews'参演的电影中主要角色的电影、拥有最多演员的1978年电影，以及与'Art Garfunkel'合作但不包括'Art Garfunkel'本身的演员。这些查询涵盖了JOIN、LIKE、WHERE、GROUP BY和HAVING等SQL操作。

链接：https://sqlzoo.net/wiki/More_JOIN_operations
在这里插入图片描述
1.

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%'

在这里插入图片描述

SELECT id
FROM actor
WHERE name='Glenn Close'

在这里插入图片描述

SELECT id
FROM movie
WHERE title='Casablanca'

在这里插入图片描述

SELECT actor.name
FROM actor
JOIN casting ON actor.id=casting.actorid
JOIN movie ON casting.movieid=movie.id
WHERE movie.title='Casablanca'

在这里插入图片描述

SELECT actor.name
FROM actor
JOIN casting ON actor.id=casting.actorid
JOIN movie ON casting.movieid=movie.id
WHERE movie.title='Alien'

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

SELECT movie.title, actor.name FROM 
 movie JOIN casting ON movie.id=casting.movieid
       JOIN actor ON casting.actorid=actor.id
WHERE movie.yr=1962 AND casting.ord=1

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

SELECT actor.name FROM
 actor JOIN casting ON actor.id=casting.actorid
WHERE casting.ord=1
GROUP BY actor.name
HAVING COUNT(actor.id)>=15

在这里插入图片描述

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

在这里插入图片描述

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