SQL Query

Here are the tables:

CREATE TABLE Movies(

movieID INT,

name VARCHAR(30) NOT NULL,

year INT,

rating CHAR(1),

length INT,

totalEarned NUMERIC(7,2),

PRIMARY KEY(movieID),

UNIQUE(name, year)

);

CREATE TABLE Showings(

theaterID INT,

showingDate DATE,

startTime TIME,

movieID INT,

priceCode CHAR(1),

PRIMARY KEY(theaterID, showingDate, startTime),

FOREIGN KEY(theaterID) REFERENCES Theaters,

FOREIGN KEY(movieID) REFERENCES Movies

);

CREATE TABLE Tickets(

theaterID INT,

seatNum INT,

showingDate DATE,

startTime TIME,

customerID INT,

ticketPrice NUMERIC(4,2),

PRIMARY KEY(theaterID, seatNum, showingDate, startTime)

);

Although the Movies table has a totalEarned field, theres another way that we can calculate the total amount that a movie has earned. For each showing (in Showings) of that movie, there may be ticket tuples (in Tickets) for that movies movieID. Each Tickets tuple has a ticketPrice. So the computedEarnings of a movie can be calculated by adding up ticketPrice for all of the Tickets tuples that correspond to Showings of that movie.

Create a view called earningsView that has 2 attributes, movieID and computedEarnings This view should have a tuple for each movieID that gives the computedEarnings for that movietID.

What happens if theres a movieID for which there are no tickets? Well, there still should be a tuple for that movieID in earningsView, and that tuples computedEarnings should be 0