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

  1. (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. (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.

  3. (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, and BLOB. 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 of NULL, INTEGER, REAL, TEXT, and BLOB. It would seem that NUMERIC, 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. (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

  1. Which resources, if any, did you find helpful in answering this problem’s questions?

  2. About how long did you spend on this problem’s questions?


1. Source: Wikipedia