Skip to main content

SQL Journey: Blog#7

Challenge: Sequels in SQL

Given data:

CREATE TABLE movies (id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    released INTEGER,
    sequel_id INTEGER);
INSERT INTO movies 
    VALUES (1, "Harry Potter and the Philosopher's Stone", 2001, 2);
INSERT INTO movies 
    VALUES (2, "Harry Potter and the Chamber of Secrets", 2002, 3);
INSERT INTO movies 
    VALUES (3, "Harry Potter and the Prisoner of Azkaban", 2004, 4);
INSERT INTO movies 
    VALUES (4, "Harry Potter and the Goblet of Fire", 2005, 5);
INSERT INTO movies 
    VALUES (5, "Harry Potter and the Order of the Phoenix ", 2007, 6);
INSERT INTO movies 
    VALUES (6, "Harry Potter and the Half-Blood Prince", 2009, 7);
INSERT INTO movies 
    VALUES (7, "Harry Potter and the Deathly Hallows – Part 1", 2010, 8);
INSERT INTO movies 
    VALUES (8, "Harry Potter and the Deathly Hallows – Part 2", 2011, NULL);

Step 1

We've created a table with all the 'Harry Potter' movies, with a sequel_id column that matches the id of the sequel for each movie. Issue a SELECT that will show the title of each movie next to its sequel's title (or NULL if it doesn't have a sequel).

As I was writing the code for this one, I encountered an error which signifies that I do not understand the concept fully.

Here is my code:

SELECT movies.title, sequel.title AS sequel_title 
    FROM movies 
    LEFT OUTER JOIN movies sequel 
    ON sequel.sequel_id = movies.id;

Query results:

titlesequel_title
Harry Potter and the Philosopher's StoneNULL
Harry Potter and the Chamber of SecretsHarry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of AzkabanHarry Potter and the Chamber of Secrets
Harry Potter and the Goblet of FireHarry Potter and the Prisoner of Azkaban
Harry Potter and the Order of the PhoenixHarry Potter and the Goblet of Fire
Harry Potter and the Half-Blood PrinceHarry Potter and the Order of the Phoenix
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Half-Blood Prince
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Deathly Hallows – Part 1
The problem is I have read some of the Harry Potter books and also watched the films so I know all of the sequels and the movies that precede it. The above table is wrong. So there is a need for me to correct it.

Looking at my code, I specified to show the title of each movie in the movies table in the first column. Next, I specified the sequel_title in the second column. My mistake lies in the "ON sequel.sequel_id = movies.id" though I cannot exactly remedy the bug. So what I did, is to take this step-by-step.

First, I tried to find a way so that I can show the movie title and the sequel_id in one table.

Code:
SELECT movies.title, sequel_id
    FROM movies;

Query results:
titlesequel_id
Harry Potter and the Philosopher's Stone2
Harry Potter and the Chamber of Secrets3
Harry Potter and the Prisoner of Azkaban4
Harry Potter and the Goblet of Fire5
Harry Potter and the Order of the Phoenix6
Harry Potter and the Half-Blood Prince7
Harry Potter and the Deathly Hallows – Part 18
Harry Potter and the Deathly Hallows – Part 2NULL
What's left for me to do, is to convert each of the sequel_id in the second column to its corresponding movie title. This is where I will use "LEFT OUTER JOIN" since I want to ensure that all Harry Potter movie titles are included in the first column while also displaying its corresponding sequel titles.

Code:

SELECT movies.title, sequel_movies.title
    FROM movies
    LEFT OUTER JOIN movies sequel_movies;

Query results:
titletitle
Harry Potter and the Philosopher's StoneHarry Potter and the Philosopher's Stone
Harry Potter and the Philosopher's StoneHarry Potter and the Chamber of Secrets
Harry Potter and the Philosopher's StoneHarry Potter and the Prisoner of Azkaban
Harry Potter and the Philosopher's StoneHarry Potter and the Goblet of Fire
Harry Potter and the Philosopher's StoneHarry Potter and the Order of the Phoenix
Harry Potter and the Philosopher's StoneHarry Potter and the Half-Blood Prince
Harry Potter and the Philosopher's StoneHarry Potter and the Deathly Hallows – Part 1
Harry Potter and the Philosopher's StoneHarry Potter and the Deathly Hallows – Part 2
Harry Potter and the Chamber of SecretsHarry Potter and the Philosopher's Stone
Harry Potter and the Chamber of SecretsHarry Potter and the Chamber of Secrets
Harry Potter and the Chamber of SecretsHarry Potter and the Prisoner of Azkaban
Harry Potter and the Chamber of SecretsHarry Potter and the Goblet of Fire
Harry Potter and the Chamber of SecretsHarry Potter and the Order of the Phoenix
Harry Potter and the Chamber of SecretsHarry Potter and the Half-Blood Prince
Harry Potter and the Chamber of SecretsHarry Potter and the Deathly Hallows – Part 1
Harry Potter and the Chamber of SecretsHarry Potter and the Deathly Hallows – Part 2
Harry Potter and the Prisoner of AzkabanHarry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of AzkabanHarry Potter and the Chamber of Secrets
Harry Potter and the Prisoner of AzkabanHarry Potter and the Prisoner of Azkaban
Harry Potter and the Prisoner of AzkabanHarry Potter and the Goblet of Fire
Harry Potter and the Prisoner of AzkabanHarry Potter and the Order of the Phoenix
Harry Potter and the Prisoner of AzkabanHarry Potter and the Half-Blood Prince
Harry Potter and the Prisoner of AzkabanHarry Potter and the Deathly Hallows – Part 1
Harry Potter and the Prisoner of AzkabanHarry Potter and the Deathly Hallows – Part 2
Harry Potter and the Goblet of FireHarry Potter and the Philosopher's Stone
Harry Potter and the Goblet of FireHarry Potter and the Chamber of Secrets
Harry Potter and the Goblet of FireHarry Potter and the Prisoner of Azkaban
Harry Potter and the Goblet of FireHarry Potter and the Goblet of Fire
Harry Potter and the Goblet of FireHarry Potter and the Order of the Phoenix
Harry Potter and the Goblet of FireHarry Potter and the Half-Blood Prince
Harry Potter and the Goblet of FireHarry Potter and the Deathly Hallows – Part 1
Harry Potter and the Goblet of FireHarry Potter and the Deathly Hallows – Part 2
Harry Potter and the Order of the PhoenixHarry Potter and the Philosopher's Stone
Harry Potter and the Order of the PhoenixHarry Potter and the Chamber of Secrets
Harry Potter and the Order of the PhoenixHarry Potter and the Prisoner of Azkaban
Harry Potter and the Order of the PhoenixHarry Potter and the Goblet of Fire
Harry Potter and the Order of the PhoenixHarry Potter and the Order of the Phoenix
Harry Potter and the Order of the PhoenixHarry Potter and the Half-Blood Prince
Harry Potter and the Order of the PhoenixHarry Potter and the Deathly Hallows – Part 1
Harry Potter and the Order of the PhoenixHarry Potter and the Deathly Hallows – Part 2
Harry Potter and the Half-Blood PrinceHarry Potter and the Philosopher's Stone
Harry Potter and the Half-Blood PrinceHarry Potter and the Chamber of Secrets
Harry Potter and the Half-Blood PrinceHarry Potter and the Prisoner of Azkaban
Harry Potter and the Half-Blood PrinceHarry Potter and the Goblet of Fire
Harry Potter and the Half-Blood PrinceHarry Potter and the Order of the Phoenix
Harry Potter and the Half-Blood PrinceHarry Potter and the Half-Blood Prince
Harry Potter and the Half-Blood PrinceHarry Potter and the Deathly Hallows – Part 1
Harry Potter and the Half-Blood PrinceHarry Potter and the Deathly Hallows – Part 2
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Philosopher's Stone
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Prisoner of Azkaban
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Goblet of Fire
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Order of the Phoenix
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Half-Blood Prince
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Philosopher's Stone
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Prisoner of Azkaban
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Goblet of Fire
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Order of the Phoenix
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Half-Blood Prince
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Deathly Hallows – Part 2Harry Potter and the Deathly Hallows – Part 2
The query results are so long since it mapped out every single Harry Potter movie title to each of the 8 Harry Potter movies. This is not our intention. We still lack a proper "ON" clause. And here is the following:

Code:
SELECT movies.title, sequel_movies.title AS sequel_title
    FROM movies
    LEFT OUTER JOIN movies sequel_movies
    ON movies.sequel_id = sequel_movies.id;

We select two titles - a movie title and a sequel title. The movie title, located in the first column of our output table, is taken from the first table called movies while the sequel title will be taken from a second table called sequel_movies. Since this is a SELF-JOIN exercise, we expect the contents of the first table to be the same as the second table. Only their names are different to properly execute a self-join. In other words, we need to rename the second table for us to be able to convert the sequel_id from the first table to its corresponding movie title in the second table. Therefore, only when the sequel_id in the first table is equal to the movie id in the second table, we can effectively extract the sequel movie title from the second table.

Query Results:
titlesequel_title
Harry Potter and the Philosopher's StoneHarry Potter and the Chamber of Secrets
Harry Potter and the Chamber of SecretsHarry Potter and the Prisoner of Azkaban
Harry Potter and the Prisoner of AzkabanHarry Potter and the Goblet of Fire
Harry Potter and the Goblet of FireHarry Potter and the Order of the Phoenix
Harry Potter and the Order of the PhoenixHarry Potter and the Half-Blood Prince
Harry Potter and the Half-Blood PrinceHarry Potter and the Deathly Hallows – Part 1
Harry Potter and the Deathly Hallows – Part 1Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Deathly Hallows – Part 2NULL
Labels:

Comments

Post a Comment

Popular posts from this blog

Privacy Policy of ShinStats: descriptives calc

Privacy Policy Shin Nix built the ShinStats app as an Ad Supported app. This SERVICE is provided by Shin Nix at no cost and is intended for use as is. This page is used to inform visitors regarding my policies with the collection, use, and disclosure of Personal Information if anyone decided to use my Service. If you choose to use my Service, then you agree to the collection and use of information in relation to this policy. The Personal Information that I collect is used for providing and improving the Service. I will not use or share your information with anyone except as described in this Privacy Policy. The terms used in this Privacy Policy have the same meanings as in our Terms and Conditions, which are accessible at ShinStats unless otherwise defined in this Privacy Policy. Information Collection and Use For a better experience, while using our Service, I may require you to provide us with certain personally identifiable information. The information that I request will be retaine...
Post a Comment
Read more

Gears Update

It has been a while since my last post and many things have happened since then. For one, I decided to upgrade my laptop as I saw it fit for the direction I am moving towards particularly on data analytics. It's been almost 10 years since I bought "Julian", my first work laptop, and there were so many milestones that we shared together. I bought my first laptop during my second job in Taguig City. It served as an extension of myself as I work to earn for my family particularly in helping my siblings with their education as I am the eldest and breadwinner of the family. That laptop was able to create a joint personnel reporting system excel file which was used by the Philippine Army to be able to account their personnel on a national level during my stint as an Engineer / Researcher in the aforementioned organization. Julian was my laptop when I finished my Master's degree at the Ateneo de Davao University where I also created the Programmable Logic Controller Trainer ...
Post a Comment
Read more

Tutor Shin Signing off

It has been quite a while since I wrote an update regarding my Coursehero tutor account. I am personally grateful for the platform as it gave me the opportunity to earn during the trying times of the pandemic and provided the means for me to improve my work-from-home setup. I still remember working with just my laptop sitting on a plastic chair answering questions whenever I have the opportunity. I loved the flexibility and the nature of the work where I got to select the questions that I would answer based on my expertise. It served as an avenue for focused work. During my stint as a tutor, frustrations motivated me to study coding, especially statistics and probability problems that get asked quite often with just different givens. This prompted me to enroll in a Udemy Course entitled, "R Programming for Statistics and Data Science". Using this knowledge, I was now able to answer questions in Statistics and Probability that require R as a programming language.  Then, I stud...
Post a Comment
Read more