Objectives
- Aggregate function for aggregating collection of values into one value.
- Grouping of records into groups by same value in selected column.
- Set operations.
Motivation
We are working on Chinook database with the following schema.
Instructions
Step 1
Aggregate functions allows us to aggregate more values into one value, for example: count() return number of records:
SELECT count(*) FROM album;
Thanks to aggregate functions we can can count number of specific records (for example how many artists does not have any album), calculate average value, maximal value, and so on...
Task 1.1
How many tracks are in album 'War'? For verification: there should be 10.
Task 1.2
Find out how many tracks were sold from album 'War' made by U2. For verification: there should be 11.
Task 1.3
Find out time in seconds of the longest track, shortest track and an average time of tracks in album War by U2. For verification: average time should be 252,99 seconds.
Step 2
Using of aggregate functions is particularly interesting when combined with grouping of results. Using GROUP BY allows us to get summary values for groups of data based on same values in selected column. For example we can get count of tracks for each composer:
SELECT composer, COUNT(*)
FROM track
GROUP BY composer
ORDER BY COUNT(*) desc;
Task 2.1
List countries and number of costumers for each of them. For verification: most costumers (13) are from USA.
Task 2.2
List summary incomes from invoices for each year. For verification: total income for 2009 was $449.46.
Comment
Remember, GROUP BY is not restricted to just columns, you can use any expression even functions or their combination.
Task 2.3
List summary incomes from invoices for each year and its months. For verification: income for April 2011 is $51.62.
Task 2.4
Modify SELECT from previous task, so it would show only months which had income at least $50. For verification: there should be only 3 months with such income.
Task 2.5
List summary incomes from invoices for each year and its months but include even subtotals for each of year and include total income for all years. Once again it is the modification of pre-previous task, so you can start from there.
Comment
Do NOT use set operations like UNION for this solution.
Step 3
Set operations allows us to work with tables and results of SELECT as a sets and allows us to create unions, intersection of set-diference. Thanks to the set operations we can for example find out if we have in system a customer with same lastname as some of employees:
SELECT lastname
FROM employee
INTERSECT
SELECT lastname
FROM customer;
Task 3.1
Find out names of artists who are not also composers. For verification: there should be 228.
Task 3.2
Get a list of artists and composers (without duplicates), which can be found in tables Artist and Track. Do not include null value in results as there can be such value in tracks if composer is not known. Order results alphabetically. For verification: there should be 1080 rows.
Task 3.3
Get a list of all tracks (id, name, composer), which does not are part of playlist 'Heavy Metal Classic'. For verification: there should be 3477.
Comment
Be aware that some tracks may have the same name and composer, but they are not the same.
Resources
Additional Tasks
Task A.1
Chinook
List summar count of tracks in each playlist, show playlist id, name and track count. For verification: in playlist "TV Shows" there are 213 tracks and in playlist "Audiobooks" there is no track (count is 0). Resulting list order by name of playlist in ascending order.
Comment
Be aware that some playlists have the same name, although they are not the same playlists.
Comment
Do not forget playlist without any tracks.
Task A.2
Chinook
List summary incomes grouped by genre. Write out genre name even if there is no income for it. For verification: best earning genre is "Rock" with income $826,65.
Task A.3
Chinook
The last task from step number 2 required usage of ROLLUP. Rewrite solution of that task without usage of ROLLUP and only with usage of set operations.
Comment
Text of the original task: List summary incomes from invoices for each year and its months but include even subtotals for each of year and include total income for all years.
Task A.4
Chinook
Get a list of all tracks (name and composer), which are not part of 'Heavy Metal Classic' playlist.
Comment
This task is same as the last task in step 3, but this time you only use joining of tables for solving it.
Task A.5
Company
Find out how many different statuses are there for employees from Presov and Kosice city. Show name of city and number of distinct statuses. For verification: there should by record "Kosice - 1", because all employees from Kosice are married.
Task A.6
Company
Get count of employees, their average salary, maximal salary and minimal salary, but only from cities where number of employees is larger than 2.
Task A.7
Company
List sum of remaining vacation for employees in 2015 grouped by city. For verification: employees from Kosice have 10 days of remaining vacation.