SQL provides a mechanism for the nesting of sub-queries. A sub-query is a select-from-where expression that is nested within another query. It can be used in a condition of the WHERE clause or within the HAVING clause (e.g. for set membership) or as a “table” of the  FROM clause.

Activity Outcomes:

After performing this lab students should be able to:

1. Design SQL queries to retrieve consolidated data by using sub-queries.
2. Implement correlated and non-correlated sub-queries using various clauses such as SOME, ALL, EXISTS, EXCEPT, and

Tools/Software Requirement

• MySQL Community Server 6
• MySQL Workbench 1
• Sakila Database

Introduction

SQL provides a mechanism for the nesting of sub-queries. A sub-query is a select-from-where expression that is nested within another query. It can be used in a condition of the WHERE clause or within the HAVING clause (e.g. for set membership) or as a “table” of the  FROM clause.

Lab Activities:

Activity 1:

1. This lab assumes that MySQL Community Server is running, Sakila database has been loaded using MySQL Workbench, and the query window is
2. Try running following SQL query discussed during the last
3. Find ratio of rental revenue to number of actors for each film. You are supposed to list number of actors per film as well its accumulative rental revenue. Sort the records in descending order of rental revenue to number of actors ratio (revenue/actors).

select f.title, act.actors, rev.revenue, rev.revenue/act.actors as ‘revenue per actor’ from (select film_id, count(actor_id) as ‘actors’ from film_actor group by actor_id) as act join

(select i.film_id, sum(p.amount) as ‘revenue’ from inventory i join rental r using (inventory_id) join payment p using (rental_id) group by i.film_id) as rev using (film_id) join film f using (film_id) order by rev.revenue/act.actors desc;

 Solution:

Activity 2:

1. Write SQL queries for the following information needs. You should execute your attempt and make necessary corrections if needed. You are expected to provide two versions of the queries: one that uses sub-queries constructs and the other that doesn’t. In case the second query cannot be implemented, explain the reason.
2. Information needs:

• Number of customers who never rented BREAKING HOME. [3]
• List accumulative replacement cost of the movies that were never rented. [3]
• Find out customers who have rented movies from each category. [3]
• List total revenue of each film with 10 or more actors playing the act. Sort the output on descending order of the revenue. [3]

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.