Introduction to SQL Exercises

Answer these questions using the Northwind database provided on your couse homepage.

1. Basic Database Queries
   
   1. Our company is rapidly growing. The board of directors wants to know in how many countries we already have customers. Please provide them with an accurate figure.
   2. In how many different countries do we have supplier contacts.
   3. We currently have only few employees. In which countries do they work?
   4. How many customers do we have in the USA?
   5. Trying to assess the risk of the current crisis in Ukraine, how many customers do we have in Poland, Finland and Sweden combined?
   6. Do we have any suppliers in Scandinavia outside of Norway?
   7. Which CUSTOMER_IDs did post the top three orders with the largest freight?
   8. Which products are currently out of stock?
   9. Are all of our employees responsible for at least one order since the start of 2016?
2. Adding Data
   1. Add yourself and your neighbour to the Employees table.
   2. Enter a new region "EMEA" into the regions table.
   3. Add "Styria" to the Territories so that it belongs to "EMEA".
3. Update Data
   1. Nobody really uses fax anymore. Update the customers table to delete the value in the fax column for each customer.
   2. Customer "Berglunds snabbköp" has a new address. Their headquarters are now in "Olof Palmes gata 17" in 90323 Umeå. Update the customer table accordingly using SQL.
   3. Update all employees in the "British Isles" region to henceforth be in the "Great Britain" region.
4. Remove Rows
   We need to perform maintenance work and delete lots of old orders. Remove all rows from the Orders table that have an order date before 2013-01-01.
5. For reporting to senior management, generate a table containing order details with the 10 highest unit prices. Create a second query to highlight the 10 highest discounts.
6. Aggregations
   
   1. Create a query that only shows the product id and unit price of the product with the highest unit price.
   2. Calculate the turnover of all orders ever made (the sum of the product of unitprice and quantity of all items ever ordered). Call this value Turnover using an alias.