subqueries-xtreme.md

Workshop - Subqueries : Xtreme

In this workshop you'll learn the use of subqueries, using the the relational database called Xtreme:

Prerequisites

• SQL Server 2017+ Installed;
• SQL Server Management Studio Installed;
• A running copy of the database xtreme.

  You can download the database by using this link, information on how to restore a database can be found here.

Getting started

Below you'll find multiple exercises, for each exercise do the following:

1. Investigate the database schema of the xtreme database;
2. Write the query;
3. Check your results.

---

Database schema - xtreme

Exercises

1. Give the id and name of the products that have not been purchased yet.
2. Select the names of the suppliers who supply products that have not been ordered yet.
3. Select the products (all columns) with a price that is higher than the average price of the "Bicycle" products. Order the results by descending order of the price.
4. Show a list of the orderID's of the orders for which the order amount differs from the amount calculated through the ordersdetail.
5. Which employee has processed most orders? Show the fullname of the employee and the amount of order he/she processed.
6. Give per employee and per orderdate the total order amount. Also add the name of the employee and the running total per employee when ordering by orderdate, an example can be seen below. Note that the running total is the sum of all orders where the employee is responsible at the order date's time.

   EmployeeId	Lastname	Firstname	Orderdate	Sum (€)	Running (€)
   1	Davolio	Nancy	19/02/2000	848.00	848.00
   1	Davolio	Nancy	26/02/2000	69.00	916.00
   1	Davolio	Nancy	27/02/2000	5308.00	6224.00
   1	Davolio	Nancy	2/12/2000	42.00	6266.00
   ...	...	...	...	...	...

Solution

A possible solution for these exercises can be found here.