INNER JOIN (movies2.db) Flashcards
SPECIAL ANNOUNCEMENT
Are you getting the feeling someone is trying to trick you? No no NO! All these questions are very typical questions that you can be expected to find in real-world situations. Some people will be able to answer them more quickly than other people - not everyone is equally fast - but please do not feel like you are being tricked. These are very typical SQL questions . . . so just practice them until they feel “natural” to you!
Please use these cards in good health and good happiness!
Print out a long two-column list that looks like this
[actor name or actress name, actor movie or actress movie]
You can do this with UNION, like this
SELECT actor_name, actor_movie FROM actors UNION SELECT actress_name, actress_movie FROM actresses;
Print out a unique list of all movies that either starred our actors or our actresses or both
There are a couple of ways to do this. One is via a FULL JOIN which SQLite does not support.
Another way is to first produce a long list usion UNION, then to use that list as a subquery.
Here’s the long list:
SELECT actor_name AS person, actor_movie AS movie FROM actors UNION SELECT actress_name AS person, actress_movie AS movie FROM actresses;
It may contain some duplicates, so we can print out the unique list by treating this as a subquery:
SELECT DISTINCT movie FROM ( SELECT actor_name AS person, actor_movie AS movie FROM actors UNION SELECT actress_name AS person, actress_movie AS movie FROM actresses );
Print out a total list of unique movies which contained our actors or actresses or directors, or any combination
Since we just want a long list, we can think about using UNION.
Let’s print out the three separate lists first, using an ALIAS to give the columns a generic name
SELECT
actor_movie AS movie,
actor_name AS person
FROM actors;
SELECT
actress_movie AS movie,
actress_name AS person
FROM actresses;
SELECT
director_movie AS movie,
director_name AS person
FROM directors;
Now we join them up into one long table:
SELECT actor_movie AS movie, actor_name AS person FROM actors UNION SELECT actress_movie AS movie, actress_name AS person FROM actresses UNION SELECT director_movie AS movie, director_name AS person FROM directors;
Now, we’ve done nothing to filter out duplicates. So we can use this as a sub-query in a fairly easy query, that looks like this:
SELECT DISTINCT movie FROM ( SELECT actor_movie AS movie, actor_name AS person FROM actors UNION SELECT actress_movie AS movie, actress_name AS person FROM actresses UNION SELECT director_movie AS movie, director_name AS person FROM directors );
Print a list of all the movies and next to each movie, if one of our actors starred in it.
Since we need the list of ALL movies, we need to create this by UNION. Then, because we need the actors next to ALL the movies, we need LEFT JOIN:
SELECT DISTINCT movie, actor_name FROM ( SELECT actor_movie AS movie, actor_name AS person FROM actors UNION SELECT actress_movie AS movie, actress_name AS person FROM actresses UNION SELECT director_movie AS movie, director_name AS person FROM directors ) LEFT JOIN actors ON movie=actor_movie;
Print a list of all the movies, and next each movie a column containing an actor (if he was in the film) and another column containing an actress (if she was in the film).
Like this:
[movie, actor if in it, actress if in it]
We first need the master list of all the movies, which we create by UNION. Then we need two LEFT JOINS, one each to attach the actors and the actresses, like this:
SELECT DISTINCT movie, actor_name, actress_name FROM ( SELECT actor_movie AS movie, actor_name AS person FROM actors UNION SELECT actress_movie AS movie, actress_name AS person FROM actresses UNION SELECT director_movie AS movie, director_name AS person FROM directors ) LEFT JOIN actors ON movie=actor_movie LEFT JOIN actresses ON movie=actress_movie;
Print a list of all the movies, with a column containing the actor (if he was in it), the actress (if she was in it), and the director (if he directed it)
SELECT DISTINCT movie, actor_name, actress_name, director_name FROM ( SELECT actor_movie AS movie, actor_name AS person FROM actors UNION SELECT actress_movie AS movie, actress_name AS person FROM actresses UNION SELECT director_movie AS movie, director_name AS person FROM directors ) LEFT JOIN actors ON movie=actor_movie LEFT JOIN actresses ON movie=actress_movie LEFT JOIN directors ON movie=director_movie;
Print out a table where the actors and actresses appeared in the same movies
SELECT actor_name, actress_name, actor_movie FROM actors INNER JOIN actresses ON actor_movie = actress_movie;
For all the movies where the actors and actresses both appeared, print out a table that looks like
[ movie name, actor name, actor salary, actress name, actress salary ]
I hope nobody feels confused or challenged, because this is very, very straightforward. Because it asks for movies where both actors and actresses have appeared, this is clearly INNER JOIN. It’s all about getting the columns correct. The only part to watch out for is that both actors and actresses have a column entitled “salary,” so in your query writing “salary” is not enough, you need to make it more precise with “actors.salary” or “actresses.salary.”
SELECT actor_movie, actor_name, actors.salary, actress_name, actresses.salary FROM actors INNER JOIN actresses ON actress_movie = actor_movie;
For all the movies where the actors and actresses both appeared, print out a table that looks like
[ “Super Movie”, “Mr. Actor”, “His Big Money”; “Ms. Actress”, “Her Big Money” ]
I hope nobody feels confused or challenged, because this is very, very straightforward.
Because it asks for movies where both actors and actresses have appeared, this is clearly INNER JOIN. It’s all about getting the columns correct. The only part to watch out for is that both actors and actresses have a column entitled “salary,” so in your query writing “salary” is not enough, you need to make it more precise with “actors.salary” or “actresses.salary.”
The second bit is that it wants special column names; you set your own column names using the ALIAS feature, as shown here:
SELECT actor_movie AS "Super Movie", actor_name AS "Mr. Actor", actors.salary AS "His Big Money", actress_name AS "Ms. Actress", actresses.salary AS "Her Big Money" FROM actors INNER JOIN actresses ON actress_movie = actor_movie;
This time, for all the movies where the actors and actresses both appeared, print out the movie name, his name, her name, his salary, her salary, and the sum of both of their salaries
To save a bit of time we can just copy-and-paste one of the previous questions, where the answer was this:
SELECT actor_movie AS "Super Movie", actor_name AS "Mr. Actor", actors.salary AS "His Big Money", actress_name AS "Ms. Actress", actresses.salary AS "Her Big Money" FROM actors INNER JOIN actresses ON actress_movie = actor_movie;
But this is clearly missing the sum of their salaries, so we add it here:
SELECT actor_movie AS "Super Movie", actor_name AS "Mr. Actor", actors.salary AS "His Big Money", actress_name AS "Ms. Actress", actresses.salary AS "Her Big Money", actors.salary + actresses.salary AS "combined" FROM actors INNER JOIN actresses ON actress_movie = actor_movie;
