SQL Outer/Inner Join Operations
Introduction:
Aggregate functions operate on the multi-set of values from a column of a relation and return a consolidated value. This achieves scalar aggregation. Sometimes, we want to apply aggregation to each of several groups of records to achieve vector aggregation.
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 queries discussed during the
- List accumulative payments per customer in descending order of the amount. The following command will spit out customer id and sum of all amounts paid for
- select customer_id, sum(amount) as sum_amount from payment group by customer_id order by sum_amount desc;
- Note: the renamed attribute can NOT be referred in where clause but it may be referred in order by because select clause and hence the rename is executed after where and having
Solution:
Activity 2:
- Write SQL queries for the following information needs. You should execute your attempt and make necessary corrections if
- Information needs:
- Customers are from different countries. Find out how many customers are listed from each country. Sort the output on country names. [3]
- Find number of films listed in each category. List the most populated category on top. [2]
- Find out which film category has given most business to the rental company. List the total earning along with the category name. The result set should also include the second and third categories in the sorted order. [3]
- Find the most popular film category. Popular is defined as most number of cumulative rentals. [3]
- List titles of films with 5 or more actors playing the act. Sort the output on descending order of actors per film. [2]
- 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). [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.