SQL VIEW AND QUERYVIEW, please read carefully and try not to make it too complex
need 2.6.1 and 2.6.2
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)
);
2.6 Working with views 2.6.1 Create a view 2.6.2 Query view Write and run a SQL query over the earnings View view to answer the following "Misreports for Each Rating" question. You may want to use some tables to write this query, but be sure to use the view. Although the Movies table has a totalEarned field, there's 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 movie's movieID. Each Tickets tuple has a ticketPrice. So the computed Earnings of a movie can be calculated by adding up ticketPrice for all of the Tickets tuples that correspond to Showings of that movie. For some movies, the totalEarnings that appears in the Movies table is not the same as the computedEarnings for that movie in the earnings View view; we'll say that such movies are misreported. For each rating that has at least one misreported movie with that rating, you should output the number of misreported movies with that rating. The attributes in your result should be rating and misreportCount. Create a view called earnings View that has 2 attributes, movieID and computedEarnings This view should have a tuple for each movieID that gives the computedEarnings for that movietID. Your view should have no duplicates in it. However, only include tuples in your result for a rating if all the misreported movies with that rating were made before 2019. No duplicates should appear in your result. And as you've probably already deduced, you'll need to use a GROUP BY in your view. But there's one challenging aspect of this problem: What happens if there's a movieID for which there are no tickets? Well, there still should be a tuple for that movieID in earnings View, and that tuple's computedEarnings should be 0. Important: Before running this query, recreate the Lab3 schema once again using the lab3_create.sql script, and load the data using the script lab3_data_loading.sql. That way, any changes that you've done for previous parts of Lab3 (e.g., Unit Test) won't affect the results of this query. Then write the results of that query in a comment. The format of that comment is not important; it just has to have the right information in it. Save the script for creating the earnings View view in a file called createview.sql Next, write commands that delete just the tuples that have the following primary keys from the Tickets table: the tuple with primary key (111, 1, 2009-06-23, 11:00:00) the tuple with primary key (444, 5, 2020-06-24,15:00:00) Run the "Misreports for Each Rating" query once again after those deletions. Write the output of the query in a second comment. Do you get a different answer? You need to submit a script named queryview.sql containing your query on the views. In that file you must also include: the comment with the output of the query on the provided data before the deletions, the SQL statements that delete the tuples indicated above, and a second comment with the second output of the same query after the deletions
