QuestionQuestion

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 PreviewSolution 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"));...
$25.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats