1AKIFT DBI Test (Group A)

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

1. Answer the following statements indicating whether they are True or False.
   
   0-2 correct: 0 points, 3 correct: 2 points, 4 correct: 4 points.

   Statement	True	False
   select count(*) from orders yields 5.
   select count(*) from orders cross join employees yields 20.
   "select * from employees natural join orders;" and "select * from employees join orders using (employee_id);" have the same result.
   In MariaDB, aggregate functions cannot be used without group by.

2. Given the current state of the data shown above, how many rows and how many columns do the results of following queries have? 2 points for each correct answer.

   1. select * from employees e
      natural join orders o;

      4 rows, 9 columns

   2. select o.order_id, o.filed, o.gross_price,
             concat(e.first_name, ' ', e.last_name) as Employee,
             concat(c.first_name, ' ', c.last_name) as Customer
      from employees e
      natural join orders o
      right outer join customers c using (customer_id);

      4 rows, 5 columns
3. Your boss asks you to provide a report allowing her to immediately see how the business is developing over time. She's only interested in the current year's data (2023). The best way to do this is to provide her with a data table that shows the total revenue per day. To make the table look nicer, the headers must be capitalized. Display the two relevant columns. Make sure to sort the table ascending by date. (10 points)
   
   2 points for the capitalized header aliases
   1 point for selecting from the correct table
   2 points for filtering this year's data
   2 points for grouping the data by `filed`
   2 points for ordering ascendingly
   1 point for the semicolon

   -- mariadb solution
   select filed as 'Filed', sum(gross_price) as Total from orders o
   where o.filed >= "2023-01-01"
   group by filed
   order by filed;

4. The sales department wants to get a better overview of the best customers. They ask you to provide them with the neccesary information. It would be great if you could generate them a spreadsheet that shows how much each customer has spent on the company's products. They need the customer's full name and their total spending, ideally sorted by the total amount spent in descending order. That way, they immediately see the most important customers. They also kindly ask to have the data generated in a professional way. They really don't like that techie stuff. When asked what they mean by that, they explain that, for example, headers should not have underscores and should be properly capitalized. Write an SQL query that solves their problem. (14 points)
   3 points for showing the correct columns
   1 point for showing proper aliases for the column headers
   2 points for aggregating each group's sum
   2 points for using a working join
   2 points for grouping by customer
   3 points for sorting the table by total spending in descending order
   1 point for the semicolon

   select c.first_name, c.last_name, sum(o.gross_price) as "Sum"
   from customers c
   natural join orders o
   group by c.customer_id
   order by sum(o.gross_price) desc;

5. At the beginning of each month, payroll needs to know how much commission they need to pay to each employee in sales. At the beginning of February, they hence need the commission for each of the employees for all orders filed in January. The commission is the product of an order's gross price multiplied by the matching employee's commission rate and then divided by 100. You need to provide payroll with the correct data so that all employees can be paid fairly. Payroll does not have special requirements on the formatting. They only need to know the employee's last name and the total commission. Use a meaningful name for the column containing the total commission. To make life easier for them, sort the table by last name and, of course, show each employee only once with the amount they should receive for the last month. Don't forget that your query will be run in February, so there will hopefully be many new orders until then. (18 points)
   
   2 points for showing the correct columns
   1 point for showing a proper alias for the total commission
   2 points for calculating each order's commission
   2 points for aggregating each employee's total commission
   2 points for using a working join
   4 points for filtering the orders from January
   2 points for grouping by employee
   2 points for sorting the table by last name
   1 point for the semicolon
   

   select e.last_name , sum(o.gross_price * e.commission_rate / 100) as commission
   from employees e
   natural join orders o
   where o.filed BETWEEN "2023-01-01" and "2023-01-31"
   group by e.employee_id
   order by e.last_name