SQL Join Operations
A SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.
Activity Outcomes:
After performing this lab students should be able to:
- Design SQL queries to retrieve data from multiple tables by using JOIN operation.
Tools/Software Requirement
- MySQL Community Server 6
- MySQL Workbench 1
- Sakila Database
Lab Activities:
Activity 1:
- This lab assumes that MySQL Community Server is running, Sakila database has been loaded using MySQL Workbench, and the query window is
- Try running following SQL
- List rental and return date for the movie VANILLA DAY. Nine entries of renting this movie should be found from the database. Keep in mind that there might be multiple DVD copies of one film. A single copy is represented as an inventory item. For finding rental date of a film, actually the rental date of the DVD of the corresponding film is retrieved. Also note that the below query uses ON clause because an additional comparison is made on the title attribute from one table (in contrast to comparing columns from both sides).
- select rental_date, return_date from film f join inventory i on (f.film_id=i.film_id and f.title like ‘VANILLA DAY’) join rental r using (inventory_id);
Solution:
Activity 2:
- Write SQL queries for the following information needs. You should execute your attempt and make necessary corrections if
- Information needs:
- Find film ID of all movies rented on 2005-05-30. [2]
- Email of customers who rented VANILLA DAY. [3]
- List of movies (sorted on title) in the Horror category having rental rate of more than $4. [3]
- List first and last name of actors who played in the movie VANILLA DAY. [3]
- List full (first and last) name of actors who played in any Horror movie. Names should be in sorted order and make sure the names do not repeat. [3]
- Retrieve all the customers from Pakistan. List their id, name and the city. Sort the result on city. [3]
- For all the customers from Pakistan, retrieve which movies they have rented and the movie category. The result-set should include customer name, city, movie title and category. Sort the result on movie category. [3]
Solution:
Home Activities:
- Answer questions from the task. These questions assume Sakila database hasn’t changed.
Lab Assignment and Viva voce:
Deliverable:
Submit a PDF document including the SQL queries to answer above-mentioned information needs as well as snapshot of their outcome when executed over MySQL using the Workbench.
