IT360 Lab 3: More on SQL SELECT

 

DUE: January 31, 2013, 2359 (paper copy BEFORE start of lab next day)

 

In this lab you will expand your database implementation skills by querying a MySQL database using SQL.

 

Follow the tutorial on the website to connect to the MySQL server.

 

To write SQL queries in MySQL Workbench:

  1. Write the SQL statements in the top-right pane, each SQL statement ended with ";". You can write one-line comments by starting the line with --, or multi-line comments by using C-style comments /*  multi-line comment here */.
  2. Go to Query -> Execute Current Statement (Ctrl+Enter) to execute the current SQL query (on the cursor line).
  3. Go to Query ->Execute (All or selection) (Ctrl+Shift+Enter) to execute all SQL statements in your script, or only the highlighted statements.

To save SQL queries: From the menu à Fileà Save Script As...  Make sure you save queries in a file on your X drive, not the local drive!

 

In this lab, you will work on the Movie Ratings database: we have a table with information about movies, one with information about reviewers, and one table with the ratings given by reviewers for movies. Here is the schema for these tables, with primary key underlined and foreign keys in italic:

 

Movie ( mID, title, year, director )  -- The movie with id mID has a title, a release year and a director
Reviewer ( rID, name ) – The reviewer with id rID has a name
Rating ( rID, mID, stars, ratingDate ) -- The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.
 

(Prepare the database)

Download the createMovieRatings.sql file from the course website, and open it in MySQL Workbench (File->Open SQL Script ...). Change the first line of the file to be “use mXXXXXX;” where XXXXXX is your alpha, and execute the entire file (Ctrl+Shift+Enter). It should create the tables for Movie Reviews, with some data in them. In the object browser, you should see the new tables if you right-click on your database name and select “Refresh all”.

 

Now, write SQL queries to accomplish the following tasks. Save all queries in a file called yourlastname_yourfirstname_lab3.sql.  Write the Question number and English question as a comment before each SQL query (In MySQL Workbench, go to File-> New Query/Script tab. Copy-paste the queries below into the new script tab. Comment each question using /* */ and then write the SQL query below it. Save your script as yourlastname_yourfirstname_lab3.sql).

 

Important Note: Your queries should run correctly on any data, not just the sample data currently in the database.

 

1.  What were some good years for movies? Find all years that have a movie that received a rating of 4 or 5. Each year should appear only once in the result, and the results should be sorted by year, in increasing order.

2.  Let’s make the Rating table human-readable. Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. Also, sort the data first by reviewer name, then by movie title, and lastly by number of stars.

 

3.  What movies have poor coverage in our database? Find the titles of all movies that have no ratings (they are not in the Rating table).

4.  Harshest ratings: Find the mID and number of stars for all movies that have received the lowest rating in the database.

 
5.  Harshest ratings details: Find the reviewer name, movie title, and number of stars for all movies that have received the lowest rating in the database.

6.  Keep the best, ignore the rest: For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title.


7.  Let’s compute some statistics: List movie titles and average ratings. Display the results sorted, from highest-rated to lowest-rated.

8. Highest average rating: Find the highest of all average ratings for movies.


9.  What are the most controversial movies? For each movie, return the title and the ‘rating spread’, that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title. Hint: You can do simple calculation in the SELECT statement, and you can rename columns (or give names to computed columns); you can use a renamed column during sorting.

10.  Power users: Find the names of all reviewers who have made 3 or more ratings.

11.  Busy directors: Some directors directed more than one movie. For all such directors, return the director name and the title of the movies directed by them. Sort by director name, then movie title.

 
12.  What should I watch? Find the titles of all movies not yet reviewed by Chris Jackson.


13.  (Extra Credit) Sometimes when you see a movie again, you like it better the second time. For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer’s name and the title of the movie.

14. (Extra Credit) Best on average: Find the movie(s) with the highest average rating. Return the movie title(s) and average rating. (Hint: You might think of this query as finding the highest average rating and then choosing the movie(s) with that average rating.)

15. (Extra Credit) Power users (same as 10, but different solution): Find the names of all reviewers who have made 3 or more ratings. Give a different answer (different select clauses used) than you gave for 10.

 

To help you with debugging, below are the results I obtained from running the queries on the sample data. (Blank spaces represent NULL in the database) You should get something similar, but obtaining the same results as I did on the sample data does not guarantee that your SQL is correct. Your SQL query should work for ANY data in the database.

 

1

 

1937

 

1939

 

1981

 

2009

2

 

Ashley White

E.T.

3

2011-01-02

 

Brittany Harris

Raiders of the Lost Ark

2

2011-01-30

 

Brittany Harris

Raiders of the Lost Ark

4

2011-01-12

 

Brittany Harris

The Sound of Music

2

2011-01-20

 

Chris Jackson

E.T.

2

2011-01-22

 

Chris Jackson

Raiders of the Lost Ark

4

 

Chris Jackson

The Sound of Music

3

2011-01-27

 

Daniel Lewis

Snow White

4

 

Elizabeth Thomas

Avatar

3

2011-01-15

 

Elizabeth Thomas

Snow White

5

2011-01-19

 

James Cameron

Avatar

5

2011-01-20

 

Mike Anderson

Gone with the Wind

3

2011-01-09

 

Sarah Martinez

Gone with the Wind

2

2011-01-22

 

Sarah Martinez

Gone with the Wind

4

2011-01-27

3

 

Star Wars

 

Titanic

4

 

101

2

 

103

2

 

108

2

 

104

2

5

 

Sarah Martinez

Gone with the Wind

2

 

Brittany Harris

The Sound of Music

2

 

Brittany Harris

Raiders of the Lost Ark

2

 

Chris Jackson

E.T.

2

6

 

Avatar

5

 

E.T.

3

 

Gone with the Wind

4

 

Raiders of the Lost Ark

4

 

Snow White

5

 

The Sound of Music

3

7

 

Snow White

4.5000

 

Avatar

4.0000

 

Raiders of the Lost Ark

3.3333

 

Gone with the Wind

3.0000

 

The Sound of Music

2.5000

 

E.T.

2.5000

8

 

4.5000

9

 

Avatar

2

 

Gone with the Wind

2

 

Raiders of the Lost Ark

2

 

E.T.

1

 

Snow White

1

 

The Sound of Music

1

10

 

Brittany Harris

 

Chris Jackson

11

 

James Cameron

Avatar

 

James Cameron

Titanic

 

Steven Spielberg

E.T.

 

Steven Spielberg

Raiders of the Lost Ark

12

 

Gone with the Wind

 

Star Wars

 

Titanic

 

Snow White

 

Avatar

13

 

Sarah Martinez

Gone with the Wind

14

 

Snow White

4.5000

15

 

Brittany Harris

 

Chris Jackson

 

 

Turn in BOTH electronic (due January 31, 2013 before 2359) and paper copies (due before start of lab on February 1, 2013):

 

Electronic:

  1. Upload the yourlastname_yourfirstname_lab3.sql file containing all the questions and the SQL answers to Lab 3 assignment on Blackboard.

Hard-copies:

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of the yourlastname_youfirstname_lab3.sql.