Aggregate functions, group by and set operations

Instructions

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: How many tracks are in album 'War'? For verification: there should be 10.

   Task: Find out how many tracks were sold from album 'War' made by U2. For verification: there should be 11.

   Task: 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.
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: List countries and number of costumers for each of them. For verification: most costumers (13) are from USA.

   Task: List summary incomes from invoices for each year. For verification: total income for 2009 was $449.46.

   Note: Remember, GROUP BY is not restricted to just columns, you can use any expression even functions or their combination.

   Task: List summary incomes from invoices for each year and its months. For verification: income for April 2011 is $51.62.

   Task: 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: 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.

   Note: Do NOT use set operations like UNION for this solution.

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: Find out names of artists who are not also composers. For verification: there should be 228.

   Task: 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: 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.

   Note: Be aware that some tracks may have the same name and composer, but they are not the same.