this assignment is to improve your skills in writing declarative queries on a relational database

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

The goal of this assignment is to improve your skills in writing declarative queries on a relational database, in general, and also to improve your fluency in SQL (and SQLite).

You have been provided with the following relational schema.

• moviePeople(pid, name, birthYear)

• movies(mid, title, year, runtime)

• casts(mid, pid, role)

• recommendations(watched, recommended, score)

• customers(cid, name)

• sessions(sid, cid, sdate, duration)

• watch(sid, cid, mid, duration)

• follows(cid, pid)

The tables are derived from the specification of Assignment 1 and the names of the tables and columns should give the semantics, except minor differences which are explicit in table definitions, insert statements or queries. In particular, the badge names are unique and some tables and attributes may have been either removed or renamed for simplicity.

 

 

Creating the database

Using the SQL statements provided, create the above tables in SQLite3 on Lab machines with some data. Here is a small initial data to get you started (right click to save both files on your local machine).

 

 

(90 marks) Queries

Write down the following queries in SQL and run them in SQLite3 over the database created. You will be writing ONE SQL statement for every query (here One SQL statement starts with a SELECT and ends with a semicolon but may include multiple select statements combined in the form of subqueries and/or using set operations). Your SQL queries for questions 1-3 cannot use any of aggregation, grouping, or nesting (set operations are ok). 

1. For each customer, find all recommended movies based on the movies they have watched. For this and all other queries in this assignment, a movie is considered watched if the customer has watched at least 50% of it based on the duration that is recorded. Return the customer id and the id of recommended movies and order the result based on the recommendation scores with the highest score shown first.

2. Find the (id and name of) cast members of The Shawshank Redemption who have acted in at least two other movies. The matches should be case insensitive. Hint: Check out built-in scalar functions in SQLite.

3. Of the movies produced this year, which ones are recommended for people who have watched a movie acted by Morgan Freeman. For example, if m1 is movie acted by Morgan Freeman and m2 is recommended for people who have watched m1, we want to return the id and title of m2 if it is produced this year. The matches again should be case insensitive. Hint: Check out Date and Time functions in SQLite.

4. For every customer and every month within the past 365 days, find the number of movies watched within the month. A movie is within a month if the session in which the movie is watched starts within the month. The result includes the id and name of the customer and the month for which the quantity is reported.

5. Find the (id and name of) customers whose total session time is more than 20min but every one of their session is less then 5min.

6. For every customer, find the id of the customer, the number of sessions, the total duration of the sessions, and the number of movies watched. If a customer has no sessions, the customer will be reported with those quantities set to zero. Hint: you may find outer join and subqueries in the from clause useful.

7. For each cast member, find the id and the name of the cast member and the number of customers who follow them and have watched all movies by that cast member. Order the results based on the number of such customers.

8. Find the (id and title of) movies with at least 3 cast members and all their cast members having at least two followers.

9. Create a view called hotMovies with columns mid, title, year, runtime, watchCnt, recommended, and score. The view includes for every movie that is watched by more than 5 customers, the movie id, title, year, the number of people who watched it, and the set of all recommended movies with their scores. If a movie has no recommended movies, it will appear in the result with null values in the last two fields.

10. Using the view created in Q9, find for each customer who have watched at least two hot movies, the id and name of the customer and all other recommended movies concatenated and separated by commas. Hint: Check out built-in aggregate functions in SQLite.

 

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of