Question

1. Create the following queries on the provided database - attached file (Applicationdb.mdb).

a) Create a query to find the customer(s) whose last name is Smith. Show the last name, first name, and customer ID. Save your query file as “query1” (10%).

b) Create a query to find the customer(s) who purchased before Jan. 15, 2003. Show the customer ID, last name, first name, and order date. Save your query file as “query2” (10%).

c) Create a query to find product(s) that have less than 3,000 units in stock. Show the product name, product ID, units in stock, and supplier company name. The list should be sorted by the units in stock in ascending order (small quantity first and large quantity last). Save your query file as “query3” (10%).

d) Create a query to find the customer(s) who have purchased a Viewsonic 17 Monitor since Dec. 30, 2002. Show the customer ID, last name, and first name, (but do not show order date and product name). The same customer name should appear only once. Save your query file as “query4” (10%).

e) Create a query that allows you to input the type of credit card of customers, and which will then give you an output list of the product names and total quantities of those products that were ordered by customers with that credit card type since Jan. 1, 2005. For example, when you run the query, the computer should ask you which type of credit card you wish to inquire about. If you input “M” for this assignment, then the computer will show you the orders that were placed by customers using the credit card type “M” since Jan. 1, 2005. The query should display the summary results by including the following two fields only: product name and quantity (i.e. total quantity of the product ordered using the credit card type “M”). The quantity should be sorted in descending order (large quantity first and small quantity last). The same product name should appear only once. Save your query file as “query5” (20%).

Solution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Query1)
SELECT Customer.[Customer Lname], Customer.[Customer Fname], Customer.[Customer ID]
FROM Customer
WHERE (((Customer.[Customer Lname])="smith"));...

This is only a preview of the solution. Please use the purchase button to see the entire solution

$25.00

or free if you
register a new account!

Assisting Tutor

Related Homework Solutions

Get help from a qualified tutor
Live Chats