CSCI 5333 DBMS
Spring 2020
Homework #5

SQL

This homework refers to the Sakila database that comes preloaded with MySQL installation. Refer to the documentation at: https://dev.mysql.com/doc/sakila/en/. Download the database at downloads.mysql.com/docs/sakila-db.zip to review the SQL statements for declaring and populating the database. (Note that XAMPP does not include Sakila and you will need to install it yourself.) You can refer to this handy Sakila's schema.

Write the SQL statements for the following queries.

(1) List the titles of all films with a PG rating and a length of 180 or more.

+----------------+
| title          |
+----------------+
| MONSOON CAUSE  |
| RECORDS ZORRO  |
| STAR OPERATION |
| WORST BANGER   |
+----------------+
4 rows in set (0.01 sec)


(2) List the titles of all films with the actor with id #148.

+---------------------+
| title               |
+---------------------+
| ANONYMOUS HUMAN     |
| BASIC EASY          |
| CHAMBER ITALIAN     |
| CHRISTMAS MOONSHINE |
| DESTINY SATURDAY    |
| FUGITIVE MAGUIRE    |
| GONE TROUBLE        |
| HOLLOW JEOPARDY     |
| INVASION CYCLONE    |
| OCTOBER SUBMARINE   |
| REBEL AIRPORT       |
| SCARFACE BANG       |
| SEA VIRGIN          |
| SHREK LICENSE       |
+---------------------+
14 rows in set (0.00 sec)

(3) List the titles of all films with a length of 180 or more and in the category 'Comedy'.

+----------------+
| title          |
+----------------+
| CONTROL ANTHEM |
| SATURN NAME    |
| SEARCHERS WAIT |
+----------------+
3 rows in set (0.00 sec)


(4) List the titles of films with an actor with id 1 or 17.

+------------------------+
| title                  |
+------------------------+
| ACADEMY DINOSAUR       |
| ANACONDA CONFESSIONS   |
| ANGELS LIFE            |
...
| WAR NOTTING            |
| WARLOCK WEREWOLF       |
+------------------------+
51 rows in set (0.00 sec)


(5) List the titles of films that have not been rented. Include films that are not in the inventory.

+------------------------+
| title                  |
+------------------------+
| ALICE FANTASIA         |
| APOLLO TEEN            |
...
| WAKE JAWS              |
| WALLS ARTIST           |
+------------------------+
42 rows in set (0.01 sec)


(6) List all customer names who have rented 40 or more times in the following format.

+---------------+------------------+
| customer      | Number of rental |
+---------------+------------------+
| ELEANOR HUNT  |               46 |
| KARL SEAL     |               45 |
| MARCIA DEAN   |               42 |
| CLARA SHAW    |               42 |
| TAMMY SANDERS |               41 |
| SUE PETERS    |               40 |
| WESLEY BULL   |               40 |
+---------------+------------------+
7 rows in set (0.02 sec)

(7) For each film, lists the id, film title, the number of copies in the inventory, the number of times the film has been rented. Only list the film rented for 32 or more times.

+---------+---------------------+------------+------------+
| film_id | title               | num_copies | num_rented |
+---------+---------------------+------------+------------+
|     103 | BUCKET BROTHERHOOD  |          8 |         34 |
|     738 | ROCKETEER MOTHER    |          8 |         33 |
|     382 | GRIT CLOCKWORK      |          8 |         32 |
|     767 | SCALAWAG DUCK       |          8 |         32 |
|     489 | JUGGLER HARDLY      |          8 |         32 |
|     730 | RIDGEMONT SUBMARINE |          8 |         32 |
|     331 | FORWARD TEMPLE      |          8 |         32 |
+---------+---------------------+------------+------------+
7 rows in set (0.06 sec)

(8) List the numbers of films that have been rented n times in ascending order of n.

+--------------+--------------+
| Times rented | num of films |
+--------------+--------------+
|            0 |           42 |
|            4 |            3 |
|            5 |           14 |
|            6 |           31 |
|            7 |           38 |
|            8 |           36 |
|            9 |           44 |
|           10 |           38 |
|           11 |           39 |
|           12 |           36 |
|           13 |           52 |
|           14 |           47 |
|           15 |           51 |
|           16 |           54 |
|           17 |           49 |
|           18 |           43 |
|           19 |           45 |
|           20 |           43 |
|           21 |           52 |
|           22 |           39 |
|           23 |           36 |
|           24 |           33 |
|           25 |           27 |
|           26 |           24 |
|           27 |           26 |
|           28 |           11 |
|           29 |           15 |
|           30 |           16 |
|           31 |            9 |
|           32 |            5 |
|           33 |            1 |
|           34 |            1 |
+--------------+--------------+
32 rows in set (0.02 sec)

Submission:

Submit your homework through Blackboard with a sql file <<Yourname>><<YourStudentID>>_h5.sql containing all SQL statements. Your sql file should be executable directly under MySQL.