Create the Database:

The data is the same as was described in the ER Design Project assignment. In that assignment you were asked to map the ER diagram to relations in the database. Here is a formal description of the relations that you will use in this assignment:

streamTV Database Relations

shows(showID, title, premiere_year, network, creator, category)

episode(showID, episodeID, airdate, title)

showID is a foreign key to shows

actor(actID, fname, lname)

main_cast(showID, actorID, role)

showID is a foreign key to shows

actID is a foreign key to actor

recurring_cast(showID, episodeID, actorID, role)

showID is a foreign key to shows episodeID is a foreign key to episode

actID is a foreign key to actor

customer(custID, fname, lname, email, creditcard,membersince,renewaldate, password, username)

cust_queue(custID, showID, datequeued)

custID is a foreign key to customer

showID is a foreign key to shows

watched(custID, showID, episodeID, datewatched)

custID is a foreign key to customer

showID is a foreign key to shows

(showID, episodeID) is a foreign key to episode

(custID, showID) is a foreign key to cust_queue

Primary keys are in bold.

Question:

SQL Queries:

The management at streamTV needs to retrieve certain information about their the data in the database. Specify the SQL queries for the questions listed here:

1. Find the titles and premiere years of all shows that were created after 2005.

2. Find the number of episodes watched by each customer, sorted by customer last name.

3. Find the names and roles of all actors in the main cast of Friday Night Lights.

4. Find all actors who are in the main cast of at least one show and in the recurring cast of at least one show. Display the actor's first name, last name, the title of the show in which the actor is in the main cast, the title of the show in which the actors is in the recurring cast, and the role the actor plays in each show.

5. How many shows have episodes with the word "good" in the title.

6. List the show title, episode number, date and episode title for all of the shows with the word "good" in the title. Sort the list by airdate.

7. Which episodes that have been watched originally aired in 2005. Display the show title, the episode title and the original air date.

8. Display the names of all actors who have had recurring roles in shows on NBC. Include the name of the actor, the title of the show and the role.

9. A customer wants to add to her queue every show that Amy Poehler has appeared in. List all of these shows by title.

10. For each customer (display first and last name), display which show and episode was the first one watched by that customer. Sort the result by the customer's last name.

11. Find all shows that have more than 5 seasons. Display the title of the show, and the number of seasons. Sort the result by the number of seasons. Note that the first digit of each episode number represents the season number.

12. Find the titles of all shows that were not watched by any customers in August of 2013.

13. List the title of the show that has been watched the most by customers. Also display the number of times it has been watched.

14. For each show, list the number of customers who have that show in their queue. Display the show title and the number of customers. Sort by show title.