Question

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.

Answer 1

Solution:

1. SELECT title, premiere_year FROM shows WHERE premiere_year > 2005

Here we are selecting the Title, Premiere year of the shows whose Premiere Year is greater than 2005 it means shows that are created after 2005.

2. SELECT COUNT(W.episodeID), W.custID, C.fname, C.lname FROM watched W JOIN customer C ON W.custID = C.custID GROUP BY W.episodeID ORDER BY C.lname

In this second query we have to join two tables because the data on which the sorting will be done belongs to another table i.e. last name of the customer. so first we have selected the episodeID from the watched table and grouped it for no repetition of records. after that we sorted the data by the last name of the customer.

3. SELECT A.fname, A.lname, MC.role FROM main_cast MC JOIN actor A ON MC.actID = A.actID JOIN shows S ON MC.showID = S.showID WHERE S.title = 'Friday Night Lights'

here we have to join 3 tables to produce the output i.e. main_cast, actor and shows

4. SELECT A.fname, A.lname, S.title,  MC.role, RC.role FROM actor A JOIN shows S on S.actorID = A.actorID JOIN main_cast MC ON MC.showID = S.showID JOIN recurring_cast RC on RC.showID = S.showID WHERE S.actorID IN (SELECT actorID FROM main_cast) OR S.actorID in (SELECT actorID FROM recurring_cast)

here we have to fire nested queries to provide the actor id to the where clause if it occurs in either main_cast table or  recurring_cast if it occurs then we are selecting first name last name and title of the show.

5. SELECT COUNT(title) FROM episode WHERE title LIKE %'good'%

Above query will find count of episodes whose title include "good" in it. we used like clause for that.

6. SELECT S.title, E.episodeID, E.date, E.title FROM shows S JOIN episodes E WHERE S.titile LIKE %'good'% ORDER BY E.airdate

Above query will select the records which include "good" in title and sort the result by airdate.

7. SELECT S.title, E.title, E.airdate FROM episodes E JOIN shows S ON E.showID = S.showID JOIN watched W ON E.episodeID = W.episodeID WHERE E.airdate between '01/01/2005' AND '31/12/2005'

Above query will select the required episode data from tables which episode originally aired in 2005

8. SELECT A.name, S.title, S.premiere_year, R.role From recurring_cast R JOIN shows S ON R.showID = S.showID JOIN actor A WHERE S.network = 'NBC'

This query will select the data from recurring_cast and join with show and actor table which is broadcasted on 'NBC'

9. SELECT S.title FROM shows S JOIN main_cast MC ON S.showID = MC.showID JOIN actor A ON MC.actorID = A.actorID WHERE A.fname = 'Amy' AND A.lname = "Poehler"

The above query will list the shows in which Amy Poehler is appeared.

10. SELECT C.fname, C.lname, S.title FROM customer C JOIN watched W ON C.custID = W.custID JOIN shows S ON W.showID = S.showID WHERE W.datewatched = (SELECT MIN(datewatched) FROM watched WHERE custID = W.custID)

this query will select the data as per the requirement specifiled in problem no 10

11. SELECT S.* FROM shows S JOIN episodes E ON S.showID = E.showID WHERE E.episodeID > 50 ORDER BY E.episodeID

the above query will select all the details of the show which has more than 5 seasons.

12. SELECT S.title FROM shows S WHERE S.showID NOT IN (SELECT showID from watched where datewatched BETWEEN '01/08/2013' AND '31/08/2013')

this query will fetch all the shows which are not watched in august 2013.

13. SELECT S.title, COUNT(S.showID) FROM shows S WHERE S.showID = max((SELECT COUNT(*) FROM watched GROUP BY showID))

the above query will show you the most watched show from the database.

14. SELECT S.title, (SELECT count(*) FROM cust_queue WHERE showID= S.showID) AS NumberOfCustomers FROM shows S ORDER BY S.title

This query first display the title of the show then the inner query will fetch how many customers have this show in queue and assign it to NumberOfCustomers and show the O/P