1AKIFT DBI Test (Group A)

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

1. List the customer names of all customers that have not posted any orders. Name the column "Customer Name".
   1 point for selecting the correct column
   1 point for the alias
   1 points for filtering
   2 points for the subquery
   1 point for the semicolon

   -- mariadb solution
   select CustomerName as "Customer Name" from Customers c
   where c.CustomerID not in
   (
   select CustomerID from Orders o
   );

2. List all details of all Customers that have ordered products that cost more than twice the average price per product. Matching customers must be listed exactly once.
   1 point for distinct
   1 point for selecting all other columns
   3 points for all joins
   1 point for filtering
   2 points for the subquery
   1 point for the semicolon

   select distinct(c.CustomerID), c.*  from Products p
   natural join Orders
   natural join OrderDetails
   natural join Customers c
   where Price >
   (
   select 2 * avg(Price) from Products
   );