1AKIFT DBI Test (Group B)

To solve the tasks in this test, use the following ERD.

1. Write a query that only shows the category with the lowest average price. Show the name of this category and call the column "Cheapest Category" as well its average price.
   (6 points)
   1 point for selecting the correct columns
   1 point for the alias
   1 point for the grouping
   1 point for the aggregation function (in select and order by)
   1 point for limiting output to a single category
   1 point for the semicolon

   -- mariadb solution
   select c.CategoryName as "Cheapest Category", avg(price) from Categories c
   natural join Products p
   group by c.CategoryID
   order by avg(price)
   limit 1;

2. Which suppliers only provided goods cheaper than the average product price? Show each of their IDs and names exactly once.
   (9 points)
   1 point for distinct
   1 point for selecting correct columns
   1 point for the join
   1 point for grouping
   2 points for filtering incl. the aggregation
   2 points for the subquery
   1 point for the semicolon

   select distinct(SupplierID), SupplierName from Suppliers s
   natural join Products p
   group by s.SupplierID
   having max(p.Price) <
   (select avg(Price) from Products);