SQL Assessment

Preparation

Create and populate the DVD Rental database on your laptop using its database dump. The database dump contains SQL statements that will restore the database when the file is executed (e.g., by opening the file in pgAdmin’s Query Tool).

Review the schema of the DVD rental database, including its ER diagram, to familiarize yourself with it. You may also complete the queries provided below to assist with this process.

Download the solution file to complete during the assessment.

Queries

The following practice queries are intended to familiarize you with the DVD Rental database. These queries are not related to the assessment itself. There is no penalty for not completing them, and you may work on them with others. (solutions)

1. Retrieve the first name, last name, and email address of all customers living in London, United Kingdom. Sort the results by email address in ascending order.

   SELECT first_name, last_name, email
   FROM customer INNER JOIN address USING (address_id)
                 INNER JOIN city USING (city_id)
                 INNER JOIN country USING (country_id)
   WHERE city = 'London'
     AND country = 'United Kingdom';
   ORDER BY email ASC;
   

2. Retrieve the category of all films rated G. Do not include duplicates in the results. Sort the results by category in ascending order.

   SELECT DISTINCT category.name
   FROM film INNER JOIN film_category USING (film_id)
             INNER JOIN category USING (category_id)
   WHERE film.rating = 'G'
   ORDER BY category.name ASC;
   

3. Retrieve the first name, last name, and total amount spent by each customer who has spent at least $100. Sort the results in decreasing order of the amount spent, using the customer id to break ties.

   SELECT first_name, last_name, SUM(amount) AS total
   FROM customer INNER JOIN payment USING (customer_id)
   GROUP BY customer_id, first_name, last_name
   HAVING SUM(amount) >= 100
   ORDER BY total DESC, customer_id ASC;
   

4. Retrieve the title, rating, and language of all films that have never been rented. Sort the results by title in ascending order.

   SELECT film.title, film.rating, language.name
   FROM film INNER JOIN language USING (language_id)
   WHERE film_id NOT IN (SELECT film_id
                         FROM inventory INNER JOIN rental
                                 USING (inventory_id)
   ORDER BY film.title ASC;
   

5. Retrieve the first and last name of the actors and actresses who appear in a film that contains “water” in its title and who appear in a film that contains “bow” in its title. Either “water” or “bow” may be capitalized. Sort the results by the actor id in ascending order.

   SELECT actor.first_name, actor.last_name
   FROM film f1 INNER JOIN film_actor fa1 ON f1.film_id = fa1.film_id
                INNER JOIN actor ON fa1.actor_id = actor.actor_id
                INNER JOIN film_actor fa2 ON actor.actor_id = fa2.actor_id
                INNER JOIN film f2 ON fa2.film_id = f2.film_id
   WHERE f1.film_id <> f2.film_id  -- do not match the same film twice
     AND (f1.title LIKE '%water%' OR f1.title LIKE '%Water%')
     AND (f2.title LIKE '%bow%' OR f2.title LIKE '%Bow%);
   

Notes

Authorized resources during the assessment are as follows:

• DVD Rental database and corresponding ER diagram
• PostgreSQL and pgAdmin
• PostgreSQL documentation
• Solution file to record your answers
• Comp Sci 364 textbook

Your laptop’s wireless network connection must be turned off during the in-class assessment.

Submission Instructions

When you complete the assessment, do the following:

1. Save your solution file (save frequently throughout the assessment!)
2. Return the assessment to your instructor
3. Request permission from the instructor to enable your laptop’s wireless network connection
4. Upload your solution file to Blackboard