Beginner Acces Lab 2

CS1100: Access Lab 3

Nested Database Queries

To complete this assignment you must submit an electronic copy to Blackboard by the due date. Download the starter file and save a copy of it. Do not open the Access database directly from Blackboard – save it to your desktop first!

This database represents customer and order information for the WizBang computer store. We have been asked to help the owner understand his sales better and therefore have been given certain queries. For each of the problems assume that the data in the database might change. Your query should give the correct results for whatever data happens to be in the database. If the problem says that your query result should not have duplicate entries for a particular field, it is not sufficient that your there are no duplicate entries for the particular data in the database. Your query must give unique values for that field whatever data happens to be in the database.

Knowledge Required

• Aggregate functions (especially COUNT, AVG, MIN, MAX, and SUM) • Calculated fields and expressions • Selection criteria and WHERE clauses with aggregate functions • Nested queries using multiple subqueries

Database Structure To help you in designing queries, the database structure is shown below:

LabA3 CS1100

2 of 3

Queries (5 Points Each)

Save your queries using the naming pattern QLastnameX, where Lastname is your last name and X is the question number. Name your subqueries using the naming pattern SubQLastnameXA, where X is the question number and A is a letter. For instance, if I wrote a subquery for Query 1 it should be named SubQRazzaq1a. If I needed a second subquery for Query 1, it would be called SubQRazzaq1b. You may reuse subqueries in other questions without renaming them however.

1. How many orders are there which include a line item (order detail) with an extended price of less than $500? Recall that the extended price of a line item is Quantity * UnitPrice. Your output must be a single number.

2. How many orders are there which have a total value of less than $500, i.e., the number

of orders which have an order total less than $500? Give a single number.

3. How many contacts placed an order which included a line item with an extended price of less than $500? Give a single number.

4. What is the maximum order total across all orders? Give a single number.

5. Which order or orders has the maximum order total? List the OrderID only, making

sure there are not duplicate rows.

6. How many states have one or more contacts that placed an order for more than $1000? Give a single number.

7. Who placed the order for the largest total amount? List First Name, Last Name and

Contact ID. (No duplicate entries for the name)

8. How many contacts placed more than 3 orders? Give a single number.

9. How many contacts placed at least one order with more than 4 line items? Give a single number.

10. How many contacts placed at least 2 orders, each of which has a total value of more than

$1000? Give a single number.

11. What is the total inventory value, i.e., what is the retail value of all of the products? The retail value is the RetailPrice * UnitsInStock. Give a single number.

12. What is the most expensive product? Provide the ProductID and the ProductName.

13. What is the total weight of each order? List the OrderID and the weight. Be careful to

take into account the quantity ordered of the product, i.e., the LineItem’s [Quantity].

14. Which is the lightest order, i.e., the order with the lowest weight? List the OrderID only.

LabA3 CS1100

3 of 3

15. Which orders weigh between 50 and 100 lbs (inclusive)? List all of the OrderIDs and their weights.

16. Which orders weigh above average, i.e., which orders have a weight that is larger than

the average weight? List the OrderIDs and their weights. 17. Which products were ordered by contacts from only one state? (i.e. products where all

of the contacts that ordered them came from the same state) 18. Who placed an order for the most expensive product (recall that you found the most

expensive product in question 12)? List the ContactID, First Name, and Last Name. There may be more than one customer, but each customer should only appear once in the result.

19. How many units of Product P0015 (ProductID = 15) did we ship? Give a single number.

Count only the number of units that were shipped, i.e., that were part of an order.

20. Write a query to find how many units of a given product was shipped. Hint: It’s like the query for question 19 but Access should prompt for a product number rather than “hard coding” a specific product ID.

After you have completed these tasks, your modified version of the database should contain a number of additional queries. Close Access and then submit this modified Access database to Blackboard.

Grading Rubric This rubric is intended to guide graders in their evaluation of the students’ submissions.

Criterion Discussion Grading

Queries named according to naming pattern

All queries must follow the pattern QLastnameX and subqueries follow the pattern SubQLastnameXA

-2% for each incorrectly named query and subquery.

Queries produce correct output and are designed correctly

Each query must produce the correct result.

-5% for each query that does not produce correct result or is not designed to produce correct output for all data possibilities