Aggregation and Groups
What is Aggregation and Groups?
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.
Activity Outcomes:
After performing this lab students should be able to:
- Design SQL queries to retrieve consolidated data by applying aggregate
- Implement grouping in the SQL queries and use it in combination with WHERE, JOIN and other
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 open.
- Try running following SQL queries discussed during the lecture.
- 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 for rentals.
- 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 clauses.
Activity 2:
- Write SQL queries for the following information needs. You should execute your attempt and make necessary corrections if needs.
- 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.