SQL Sequels
Although it’s commonly thought that "nothing is better than the original" when it comes to films, some sequels perform better than their predecessors in terms of box-office returns (i.e., revenue). Indeed, consider the sequels below.[1] Note, for instance, how Toy Story 2 earned 28% more than Toy Story.
title | year | predecessor’s title | predecessor’s year | increase |
---|---|---|---|---|
Austin Powers: The Spy Who Shagged Me |
1999 |
Austin Powers: International Man of Mystery |
1997 |
282% |
Harry Potter and the Goblet of Fire |
2005 |
Harry Potter and the Prisoner of Azkaban |
2004 |
16% |
Return of the Jedi |
1983 |
The Empire Strikes Back |
1980 |
21% |
Skyfall |
2012 |
Quantum of Solace |
2008 |
81% |
Terminator 2: Judgment Day |
1991 |
The Terminator |
1984 |
434% |
The Dark Knight |
2008 |
Batman Begins |
2005 |
160% |
Toy Story 2 |
1999 |
Toy Story |
1995 |
28% |
Toy Story 3 |
2010 |
Toy Story 2 |
1999 |
69% |
Suppose that these sequels are stored in a SQLite table called films
per the schema below.
CREATE TABLE "films" (
"title" TEXT NOT NULL,
"year" INTEGER NOT NULL,
"predecessor_title" TEXT NOT NULL,
"predecessor_year" INTEGER NOT NULL,
"increase" NUMERIC NOT NULL
)
Answer the below in sequels.txt
.
Questions
-
(2 points.) With what SQL query could you select from
films
the titles of all sequels to Toy Story, including Toy Story 2, Toy Story 3, and any future (similarly numbered) sequels? -
(2 points.) With what SQL statement could you insert Hannibal (2001), the sequel to The Silence of the Lambs (1991), which earned 16% more than its predecessor? We leave it to you to decide how to insert a percentage.
-
(2 points.) It turns out that each column in a SQLite database has a "type affinity," the recommended type for data stored in that column which can be any of
TEXT
,NUMERIC
,INTEGER
,REAL
, andBLOB
. But each value stored in a SQLite database has a "storage class" (i.e., actual type, which determines how the value is stored on disk), which can be any ofNULL
,INTEGER
,REAL
,TEXT
, andBLOB
. It would seem thatNUMERIC
, then, is not an actual type, just an affinity. According to SQLite’s documentation, what storage class would SQLite use to store the 16% you just inserted? Why? -
(4 points.) Notice how this table contains some data redundantly. Not only does Toy Story 2 (1999) appear as a film, it also appears as a predecessor, with its title and year repeated (in other columns). Suppose that this table must be dropped and replaced with (at least) two tables that, together, eliminate that redundancy. With what SQL statements could you create those new tables? We leave the tables' names to you as well as the names of the tables' columns and types thereof. No need to insert any rows into the new tables.
Debrief
-
Which resources, if any, did you find helpful in answering this problem’s questions?
-
About how long did you spend on this problem’s questions?