sqlzoo 练习7——More JOIN operations

最新推荐文章于 2022-07-30 20:22:46 发布

Karson.L

最新推荐文章于 2022-07-30 20:22:46 发布

阅读量336

点赞数 2

分类专栏： MySQL 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/yellow_violet/article/details/119189282

版权

本文详细介绍了如何在SQLZoo中进行多个JOIN操作，以连接多个表格并获取所需信息。从1962年的电影到演员Harrison Ford的电影作品，通过实例展示了JOIN的使用，包括嵌套JOIN和同时使用多个JOIN的情况。强调理解不同JOIN之间的逻辑关系以及在处理复杂查询时的注意事项。

摘要由CSDN通过智能技术生成

sql-练习7——More JOIN operations

sqlzoo-More JOIN operations: https://sqlzoo.net/wiki/More_JOIN_operations

这期主要是 多个 JOIN 一起使用，将多个表联系起来。

1、List the films where the yr is 1962 [Show id, title]

SELECT id, title
FROM movie
WHERE yr=1962

2、Give year of ‘Citizen Kane’.

SELECT yr
FROM movie
WHERE title = 'Citizen Kane'

3、List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

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

4、What id number does the actor ‘Glenn Close’ have?

SELECT id
FROM actor
WHERE name = 'Glenn Close'

5、What is the id of the film ‘Casablanca’

SELECT id
FROM movie
WHERE title =  'Casablanca'

6、Obtain the cast list for ‘Casablanca’.
The cast list is the names of the actors who were in the movie.
Use movieid=11768, (or whatever value you got from the previous question)

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

以为要用两个JOIN。但是看了别的答案，都是用嵌套。

//做了第10题后，看了别的答案，发现还真的可以使用两个 JOIN 的
SELECT name 
FROM actor AS a 
	JOIN casting AS c ON (a.id=c.actorid)
	JOIN movie As m ON (c.movieid=m.id)
WHERE title =<