University of Nairobi Finish Small Access Database Project Worksheet

Answer all the Assignment Questions shown below using the Access Database that you downloaded above. Be sure to save each query. Each query should be named e.g. for question 1 name the query Question 1, for question 2 name the query Question 2 for question 3 name the query that you create Question 3 etc.

Assignment Questions

Create a query that shows a list of all the products (i.e. product id and product description) and the number of times that each product has been ordered. How many products have been order less than 15 times?

Create a query that shows the customer id, customer name, and order ID for all customers with the word “Country” in their name. How many records are there in your result set (Hint: for this one, you need to use the wildcard *)?

Create a query that shows the order number and order quantity for all customer orders for which the order quantity is greater than the average order quantity of that product. How many such orders are there?

Create a query that shows the name of the sales person who sold the most number of computer desks. (Hint: for this one, the challenge is to figure out which tables you need for the query. Make sure that your tables on the query grid are connected.) Submit his or her First name and Last name below.

Create a query that shows the names of the supervisors of the employees with the skill “10in Table Saw”

Create a query that shows all employees who have more than two skills. How many different employees are there in the result set?

Create a query that shows the territory_id and the territory_name of all territories where 8-Drawer Desks have been ordered. What are the territory names?

Create a query that shows the total quantity of Computer Desks that have been ordered in each sales territory. What is the quantity of Computer Desks that have been ordered in North West Region?

Create a query that shows the total quantity of desks ordered in each sales territory. How many desks were ordered in the Central sales territory?

Create a query that shows the total dollar value of orders for each sales territory from 1/1/2006 to 12/31/2011. What is the total dollar value of orders for the Central sales territory during this period?