Field nameTypeNotes id INTEGER An arbitrary unique identifier title CHAR(70) The name of the film – usually in the language of the first release. yr DECIMAL(4) Year of first release. director INT A reference to the actor table. budget INTEGER How much the movie cost to make (in a variety of currencies unfortunately). gross INTEGER How much the movie made at the box office.
example of Movie
10003 “Crocodile” Dundee II 1988 38 15800000 239606210 10004 ‘Til There Was You 1997 49 10000000
Field nameTypeNotes id INTEGER An arbitrary unique identifier name CHAR(36) The name of the actor (the term actor is used to refer to both male and female thesps.)
example of actor
20 Paul Hogan 50 Jeanne Tripplehorn
Field nameTypeNotes movieid INTEGER A reference to the movie table. actorid INTEGER A reference to the actor table. ord INTEGER The ordinal position of the actor in the cast list. The
star of the movie will have ord value 1 the co-star will have
value 2, …
example of casting
10003 20 4 10004 50 1
1、 1962 movies（1962年电影）
List the films where the yr is 1962 [Show id, title]
获取1962年上映的电影id和名称。
2、 When was Citizen Kane released?（公民凯恩什么时候上映的）
Give year of ‘Citizen Kane’.
获取《公民凯恩》上映的年份。
3、 Star Trek movies（星际迷航系列电影）
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.
获取《星际迷航》系列的id，电影名称，上映年份；按照年份生序排名。
4、 id for actor Glenn Close（演员Glenn Close的id）
What id number does the actor ‘Glenn Close’ have?
5、id for Casablanca（《卡萨布兰卡》电影的id）
What is the id of the film ‘Casablanca’
6、Cast list for Casablanca（《卡萨布兰卡》的演员表）
Obtain the cast list for ‘Casablanca’.
what is a cast list?
Use movieid=11768, (or whatever value you got from the previous question)
ps：在不知道《卡萨布兰卡》电影id的前提下，还需要用select in select查询moviedid
7、Alien cast list（《异形》演员表）
Obtain the cast list for the film ‘Alien’
8、Harrison Ford movies（Harrison Ford出演的电影名称）
List the films in which ‘Harrison Ford’ has appeared
9、Harrison Ford as a supporting actor（Harrison Ford是配角的电影名称）
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]
10、Lead actors in 1962 movies（获取1962年，主演演员的姓名，出演电影名称）
List the films together with the leading star for all 1962 films.
解答思路：本次考察的是多表连接。
11、Busy years for Rock Hudson（Rock Hudson的大热年）
Which were the busiest years for ‘Rock Hudson’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.
解题思路：1⃣️多表连接；2⃣️having+聚合函数。
12、Lead actor in Julie Andrews movies（获取Julie Andrews出演的电影名称以及主演名称）
List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.
Did you get “Little Miss Marker twice”?
Original: https://www.cnblogs.com/ruoli-121288/p/16289120.html
Author: 徐若离
Title: SQL练习六–More JOIN operations
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/621722/
转载文章受原作者版权保护。转载请注明原作者出处！