marcia wilson owns and operates marcia’s dry cleaning, which is

Project Description:

marcia wilson owns and operates marcia’s dry cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood. marcia makes her business stand out from the competition by providing superior customer service. she wants to keep track of each of her customers and their orders. ultimately, she wants to notify them that their clothes are ready via e-mail. to provide this service, she has developed an initial database with several tables. three of those tables are the following:customer (customerid, firstname, lastname, phone, email)invoice (invoicenumber, customernumber, datein, dateout, totalamount)invoice_item (invoicenumber, itemnumber, item, quantity, unitprice)in the database schema above, the primary keys are underlined and the foreign keys are shown in italics. the database that marcia has created is named mdc, and the three tables in the mdc database schema are shown in figure 2-33.the column characteristics for the tables are shown in figures 2-34, 2-35, and 2-36. the relationship between customer and invoice should enforce referential integrity, but not cascade updates nor deletions, while the relationship between invoice and invoice_item should enforce referential integrity and cascade both updates and deletions. the data for these tables are shown in figures 2-37, 2-38, and 2-39. we recommend that you create a microsoft access 2010 database named mdc-ch02.accdb using the database schema, column characteristics, and data shown above, and then use this database to test your solutions to the questions in this section. alternatively, sql scripts for creating the mdc-ch02 database in sql server, oracle database, and mysql are available on our web site at www.pearsonhighered. com/kroenke.write sql statements and show the results based on the mdc data for each of the following:a. show all data in each of the tables.b. list the phone and lastname of all customers.c. list the phone and lastname for all customers with a firstname of ‘nikki’.d. list the customernumber, datein, and dateout of all orders in excess of $100.00.e. list the phone and firstname of all customers whose first name starts with ‘b’.f. list the phone and firstname of all customers whose last name includes the characters ‘cat’.g. list the phone, firstname, and lastname for all customers whose second and third numbers of their phone number are 23.h. determine the maximum and minimum totalamount.i. determine the average totalamount.j. count the number of customers.k. group customers by lastname and then by firstname.l. count the number of customers having each combination of lastname and firstname.m. show the firstname and lastname of all customers who have had an order with totalamount greater than $100.00. use a subquery. present the results sorted by lastname in ascending order and then firstname in descending order.n. show the firstname and lastname of all customers who have had an order with totalamount greater than $100.00. use a join. present results sorted by lastname in ascending order and then firstname in descending order.o. show the firstname and lastname, of all customers who have had an order with an item named ‘dress shirt’. use a subquery. present results sorted by lastname in ascending order and then firstname in descending order.p. show the firstname and lastname of all customers who have had an order with an item named ‘dress shirt’. use a join. present results sorted by lastname in ascending order and then firstname in descending order.q. show the firstname, lastname, and totalamount of all customers who have had an order with an item named ‘dress shirt’. use a join with a subquery. present results sorted by lastname in ascending order and then firstname in descending order.
Skills Required:
Project Stats:

Price Type: Negotiable

Expired
Total Proposals: 3
1 Current viewersl
38 Total views
Project posted by:

Proposals

Proposals Reputation Price offered
  • 3.8
    30 Jobs 19 Reviews
    $0 in 0 Day
  • 4.8
    60 Jobs 32 Reviews
    $0 in 0 Day