sqlzoo练习答案--More JOIN operations

最新推荐文章于 2021-07-28 22:45:55 发布

咸鱼的梦想

最新推荐文章于 2021-07-28 22:45:55 发布

阅读量870

点赞数

分类专栏：基础--2.数据库（Mysql）文章标签： sqlzoo sql练习 sql

基础--2.数据库（Mysql）专栏收录该内容

11 篇文章

订阅专栏

本文提供了一系列针对电影数据库的SQL查询示例，包括如何查找特定年份的电影、演员参与的电影列表、繁忙年份的演员作品数量等。通过这些查询，读者可以了解如何有效地从包含电影、演员和角色分配信息的数据库中检索数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Movie Database

This tutorial introduces the notion of a join. The database consists of three tables movie , actor and casting .

movie	actor	casting
id	id	movieid
title	name	actorid
yr		ord
director
budget
gross

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

[sql]view plaincopy 
    
 SELECT id, title  
  FROM movie  
  WHERE yr=1962  

2、 Give year of 'Citizen Kane'.

[sql]view plaincopy 
    
 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.

[sql]view plaincopy 
    
 select id,title,yr from movie where title like '%Star Trek%' order by yr  

4、 What are the titles of the films with id 11768, 11955, 21191

[sql]view plaincopy 
    
 select title from movie where id in (11768,11955,21191)  

5、 What id number does the actress 'Glenn Close' have?

[sql]view plaincopy 
    
 select id from actor where name='Glenn Close'  

6、 What is the id of the film 'Casablanca'

[sql]view plaincopy 
    
 select id from movie where title='Casablanca'  

7、

Obtain the cast list for 'Casablanca'.

what is a cast list?

The cast list is the names of the actors who were in the movie.

Use movieid=11768, this is the value that you obtained in the previous question.

[sql]view plaincopy 
    
 select name from actor inner join casting on actor.id=casting.actorid and movieid=11768  

8、 Obtain the cast list for the film 'Alien'

[sql]view plaincopy 
    
 select name from actor inner join movie on title='Alien' inner join casting on actor.id=casting.actorid and movie.id=casting.movieid  

9、 List the films in which 'Harrison Ford' has appeared

[sql]view plaincopy 
    
 select title from movie inner join actor on name='Harrison Ford' inner join casting on movie.id=casting.movieid and actor.id=casting.actorid  

10、 List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

[sql]view plaincopy 
    
 select title from movie inner join actor on name='Harrison Ford' inner join casting on movie.id=casting.movieid and actor.id=casting.actorid and ord!=1  

11、 List the films together with the leading star for all 1962 films.

[sql]view plaincopy 
    
 select movie.title,actor.name from movie inner join actor on yr=1962 inner join casting on movie.id=casting.movieid and actor.id=casting.actorid and ord=1  

12、 Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

[sql]view plaincopy 
    
 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  
 )  

13、 List the film title and the leading actor for all of the films 'Julie Andrews' played in.

[sql]view plaincopy 
    
 SELECT DISTINCT(title),name FROM movie inner join casting on movie.id=movieid  
 inner join actor on actorid=actor.id and ord=1  
 WHERE movieid IN (  
   SELECT t2.movieid FROM actor t1 inner join casting t2 on t2.actorid=t1.id and name='Julie Andrews')  

14、 Obtain a list, in alphabetical order, of actors who've had at least 30 starring roles.

[sql]view plaincopy 
    
 SELECT name  
     FROM casting JOIN actor  
       ON  actorid = actor.id  
     WHERE ord=1  
     GROUP BY name  
     HAVING COUNT(movieid)>=30  

15、 List the films released in the year 1978 ordered by the number of actors in the cast.

[sql]view plaincopy 
    
 select title,count(actorid) from movie inner join casting on movie.id=movieid and yr=1978 group by title order by 2 desc  

16、 List all the people who have worked with 'Art Garfunkel'.

[sql]view plaincopy 
    
 select name from actor inner join casting on actor.id=casting.actorid and casting.movieid in (select movieid from actor inner j