SQLZOO MORE JOIN reference answer (with table style and solution)

By doing the SQL questions during this time, I found that there are three main difficulties in solving the questions:
1: Reverse thinking requires you to think backwards from the desired answer to how to take it out of many tables in what way.
2: So the program is written from the inside out.
3: Correctly find each table joinprimary key, applying JOIN and GROUP BY in a reasonable order
在这里插入图片描述
This time it is a practice problem written for multi-table queries in SQLZOO.

I started writing from question 6 and omitted the first 5 questions.

Cast list for Casablanca

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)

Thoughts:
1: Find the actorid corresponding to movieid from casting table
2: Find the name corresponding to actorid from the actor table

SELECT ac.name
FROM actor AS ac inner join casting AS ca
ON ac.id=ca.actorid
AND ca.movieid=11768

This kind of since there are not many tables to join, we can also use subqueries:

SELECT name 
FROM actor 
WHERE id in 
(SELECT actorid
FROM casting 
WHERE movieid = 11768)

You can notice that even though the columns have different names (actorid & id) they can be auto-matched in the subquery

Alien cast list

Obtain the cast list for the film ‘Alien’

Thoughts:
1: Find the id of 'Alien' in the movie table.
2: Find the actorid corresponding to movieid(1's id) from casting table.
3: Find the name corresponding to actorid from the actor table

Select a.name from actor as a inner join (
Select c.actorid
From movie as m inner join casting as c
On m.id=c.movieid
And m.title Like 'Alien') as tmp
On tmp.actorid=a.id

Harrison Ford movies

List the films in which ‘Harrison Ford’ has appeared

Thoughts:
1: Find the id of 'Harrison Ford' in the actor table.
2: Find the movieid corresponding to actorid(1's id) from casting table.
3: Find the title corresponding to id(movieid of 2) from the movie table.

Confusion Point:
1: After the SELECT, you should know whether it is movieid or actorid that you are proposing.


SELECT m.title
FROM movie m INNER JOIN
(SELECT c.movieid 
FROM actor a INNER JOIN casting c
ON a.id=c.actorid
AND a.name LIKE "Harrison Ford")tmp
ON tmp.movieid=m.id

Harrison Ford as a supporting actor

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]

Thoughts:
1: Add an ord!=1 filter to the previous one.

SELECT m.title
FROM movie m INNER JOIN
(SELECT c.movieid 
FROM actor a INNER JOIN casting c
ON a.id=c.actorid
AND a.name LIKE "Harrison Ford"
AND c.ord!=1)tmp
ON tmp.movieid=m.id

Lead actors in 1962 movies

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

Thoughts:
1. This is a simple conditional query on a table with the following answers .

SELECT title ,directore
FROM movie
WHERE yr=1962

Harder Questions

Busy years for 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.

SELECT yr
FROM movie m INNER JOIN
(SELECT a.name,b.movieid
FROM actor a INNER JOIN casting c
ON a.id=c.actorid
WHERE a.name LIKE 'Rock Hudson' ) tmp
ON m.id=tmp.movieid
GROUP BY yr
HAVING count(title)>2

Simpler way to write can also be first by JOIN to create a large table, and then directly in the search for the conditions you want.

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

Lead actor in Julie Andrews movies

List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.

#pick outfilm title & leading actor
SELECT distinct(title)，name
FROM movie m JOIN casting c
ON m.id=c.movieid
JOIN actor a 
ON a.id=c.actorid
WHERE c.ord=1
AND movieid IN
#Movieid of all the movies he's been in
(SELECT movieid
FROM casting c JOIN actor a
ON c.actorid=a.id
AND a.name='Julie Andrews')

Actors with 15 leading roles

Obtain a list, in alphabetical order, of actors who’ve had at least 15 starring roles.

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

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

Exam point:

JOIN
GROUP BY
order by

Thoughts:
1: need title & # of actor Description needs to be taken from 2 tables, movie and casting (JOIN)
2: # of actor needs to be derived after group summing (GROUP BY)

take note ofDue to the special nature of GB, he has a limited number of column names that can be listed after a SELECT, so I tend to start by listing all the information we need in a large table with a JOIN as much as possible, and grouping it with GB.

3: Finally the NUM needs to be sorted in descending order (desc)

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

List all the people who have worked with ‘Art Garfunkel’.

Exam point:
Existential issues(Much like question 2 in Harder, applying it to everyday work would be to pick a specific project that someone is involved in, a purchase, etc.)
We usesubquerymethod of obtaining the required data.
Then combine it with another condition to splice it together

Thoughts:

First find the movieid with him in it.
Finally, find the actorid that corresponds to the movieid and connect it to the corresponding name.

#List all these found data with ! = to exclude myself
SELECT a.name
FROM actor a 
JOIN casting c
on a.id=c.actorid
WHERE a.name!='Art Garfunkel'
#Find out what movieid he's in #
AND c.movieid IN
( SELECT m.id
FROM movie m
JOIN casting c 
ON m.id=c.movieid
JOIN actor a
ON a.id=c.actorid
WHERE a.name='Art Garfunkel')