Computer science Beginner Lab

CS1100: Access Lab 2

Creating Simple Database Queries

To complete this assignment you must submit an electronic copy to Blackboard by the due date. Download the file cs1100.a2.accdb and save a copy of it. Do not open Access databases directly from Blackboard or the course website – 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 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 (such as COUNT and SUM) • Calculated fields • Selection criteria and WHERE clauses with aggregate functions • Functional dependencies and the number of rows resulting from a join

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

LabA2 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. For example, my solution for question 3 must be saved under the name QRazzaq3.

1. Write a query to find the first name, last name and zip code and contact ID of all contacts.

Your query result should not have duplicate entries for the same contact.

2. Write a query to find the first name, last name and zip code of all contacts that placed one or more orders and the order ID for each order. If a customer placed more than one order, that customer should appear in the result more than once.

3. Write a query to find the first name, last name and zip code of all contacts that placed

one or more orders. Your query result should not have duplicate entries for the same contact.

4. Write a query to find the dates on which orders were placed. Your query result should

not have duplicate entries for a date.

5. Write a query to find all the products that were ordered. List the product ID and product name. Your query result should not have duplicate entries for that product.

6. Write a query to find all the states where at least one customer from that state placed

an order. Your query result should not have duplicate states.

7. Write a query to find all orders that contain a line item with a quantity of at least 2 for the product of that line item. List the order ID. Your query result should not have duplicate entries for that order.

8. Write a query to find the customers who placed one or more orders containing a line

item with a quantity of at least 2 for a particular item. List the customer’s First Name, Last Name and Contact ID. Your query result should not have duplicate entries for that customer.

9. Write a query to find the dates for which at least one customer placed one or more orders

containing a line item with a quantity of at least 2 for a particular item. Your query result should not have duplicate entries for that date.

10. Write a query to find the customers who placed one or more orders containing a line

item with a quantity of at least 10 for a particular item. List the customer’s First Name, Last Name and Contact ID. Your query result should not have duplicate entries for that customer.

11. How many contacts are there from each state? List the state and the number from that state.

LabA2 CS1100

3 of 3

12. How many orders did each contact place? List the contact ID, first name, last name and the number of orders placed.

13. Which contacts placed at least 2 orders? List the contact ID, first name, last name and

the number of orders placed.

14. What is the total cost for each order? List the order ID and the total for each order. 15. How much did each contact that placed an order spend on all of their orders combined?

List the contact ID, first name, last name and the amount spent. 16. What is the max extended price for each order? List the order ID and the max extended

price for each order. 17. What is the max extended price of all orders? (Give a single number)

18. Which orders include a line item with an extended price of more than $1000? List the

order IDs with no duplicates.

19. Which contacts placed an order that included a line item with an extended price of more than $1000? List the contact IDs with no duplicates.

20. Which orders have a total value of more than $1000? List the order IDs and the

totals. 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

-2% for each incorrectly named query

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