question archive Marcia Wilson owns and operates Marcia's Dry Cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood
Marcia Wilson owns and operates Marcia's Dry Cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood
Subject:Computer SciencePrice:2.89 Bought3
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 or 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'.
Purchase A New Answer
Custom new solution created by our subject matter experts
GET A QUOTE
Answer Preview
Answer A
Select * from CUSTOMER;
Select * from INVOICE;
Select * from INVOICE_ITEM;
Answer B
Select Phone, LastName from Customer;
Answer C
Select Phone, LastName from Customer where FirstName LIKE “Nikki”;
Answer D
Select CustomerNumber , DateIn, DateOut from INVOICE where TotalAmount > $100;
Answer E
Select Phone, FirstName from Customer Where FirstName Like “B%”;
Answer F
Select Phone, FirstName from Customer Where LastName Like “%cat%”;
Answer G
Select Phone, FirstName , LastName from Customer Where SUBSTR(Phone, 2,2) Like “23”;
Answer H
Select max(TotalAmount) , min(TotalAmount) from INVOICE;
Answer I
Select AVG(TotalAmount) , min(TotalAmount) from INVOICE;
Answer J
Select COUNT(CustomerID) from CUSTOMER;
Answer K
Select * from Customer group by LastName, FirstName ;
Answer L
SELECT LastName, FirstName ,COUNT(*) as LastName_FirstName_Combination_Total_Count FROM CUSTOMER GROUP BY LastName, FirstName;
Answer M
SELECT LastName, FirstName, Phone FROM CUSTOMER
WHERE CustomerID IN (SELECT CustomerNumber FROM INVOICE WHERE TotalAmount>100)
ORDER BY LastName ASC, FirstName DESC
Answer N
SELECT LastName, FirstName, Phone FROM CUSTOMER, INVOICE
WHERE CustomerNumber = CustomerID AND TotalAmount > 100
ORDER BY LastName ASC, FirstName DESC
Answer O
SELECT LastName, FirstName, Phone FROM CUSTOMER
INNER JOIN INVOICE ON CustomerNumber = CustomerID
WHERE TotalAmount > 100
ORDER BY LastName ASC, FirstName DESC
Answer P
SELECT LastName, FirstName, Phone FROM CUSTOMER
WHERE CustomerID IN (SELECT CustomerNumber FROM INVOICE AS INV WHERE INV.InvoiceNumber IN
(SELECT INV_IT.InvoiceNumber from INVOICE_ITEM as INV_IT WHERE Item = 'Dress Shirt'))
ORDER BY LastName ASC, FirstName DESC
Answer Q
SELECT LastName, FirstName, Phone FROM CUSTOMER AS CUST, INVOICE AS INV, INVOICE_ITEM AS INV_IT
WHERE CustomerNumber = CustomerID AND INV.InvoiceNumber = INV_IT.InvoiceNumber AND Item = 'Dress Shirt'
ORDER BY LastName ASC, FirstName DESC
