The Queen Anne Curiosity Shop is an upscale home furnishings store in a well-to-do urban neighborhood. It sells both antiques and current-production household items that complement or are useful with the antiques. For example, the store sells antique dining room tables and new tablecloths. The antiques are purchased from both individuals and wholesalers, and the new items are purchased from distributors. The store’s customers include individuals, owners of bed- and-breakfast operations, and local interior designers who work with both individuals and small businesses. The antiques are unique, though some multiple items, such as dining room chairs, may be available as a set (sets are never broken). The new items are not unique, and an item may be reordered if it is out of stock. New items are also available in various sizes and colors (for example, a particular style of tablecloth may be available in several sizes and in a variety of colors).
Assume that the Queen anne curiosity shop designs a database with the following of tables:
CUSTOMER (CustomerID. LastName, FirstName, Address, City, State, ZIP, Phone, Email)
ITEM (ItemID. ItemDescription, CompanyName, PurchaseDate, ItemCost, ItemPrice)
SALE (SaleID, CustomerID, SaleDate, SubTotal, Tax, Total)
SALE_ITEM (SaleID. SaleItemID. ItemID, ItemPrice)
The referential integrity constraints are:
CustomerID in SALE must exist in CustomerID in CUSTOMER
SaleID in SALE_ITEM must exist in SaleID in SALE
ItemID in SALE_ITEM must exist in ItemID in ITEM
Assume that CustomerID of CUSTOMER, ItemID of ITEM, SaleID of SALE, and SaleItemID of SALE_ITEM are all surrogate keys with values as follows:
CustomerID Start at 1 Increment by 1
ItemID Start at 1 Increment by 1
SaleID Start at 1 Increment by 1
The database that The Queen Anne Curiosity Shop has created is named QACS, and the four tables in the QACS database schema are shown in Figure 2-53.
The column characteristics for the tables are shown in Figures 2-54, 2-55, 2-56, and 2-57. The relationships CUSTOMER-to-SALE and ITEM-to-SALE_ITEM should enforce referential integrity, but not cascade updates nor deletions, while the relationship between SALE and SALE_ITEM should enforce referential integrity and cascade both updates and deletions. The data for these tables are shown in Figures 2-58, 2-59, 2-60 and 2-61.
You will need to create and setup a database named QACS_CH02 for use with The Queen Anne Curiosity Shop project questions. A Microsoft Access 2013 database named QACS_CH02.accdb, and SQL scripts for creating the QACS_CH02 database in Microsoft SQL Server, Oracle Database, and MySQL are available on our Website at www.pearsonhighered/kroenke.
If you are using the Microsoft Accedd 2013 QACS_CH02.accdb database, simply copy it to an appropriate location in your Documents folder. Otherwise, you will need to use the discussion and instructions necessary for setting up the QACS_CH02 database in the DBMS product you are using:
■ For Microsoft SQL Server 2014, see online Chapter 10A.
■ For Oracle Database 12c or Oracle Express Edition 11g Release 2, se online Chapter 10B.
■ For MySQL 5.6 Community Server, see online Chapter 10C.
Once you have setup your QACS_CH02 database, create an SQL script named QACS-CH02-CQ.sql, and use it to record and store SQL statements that answer each of the following questions
A. Show all data in each of the tables.
B. List the LastName, FirstName, and Phone of all customers.
C. List the LastName, FirstName, and Phone for all customers with a FirstName of 'John’.
D. List the LastName, FirstName, Phone, SaleDate, and Total of all sales in excess of $100.00.
E. List the LastName, FirstName, and Phone of all customers whose first name starts with ’D'.
F. List the LastName, FirstName, and Phone of all customers whose last name includes the characters 'ne'.
G. List the LastName, FirstName, and Phone for all customers whose eighth and ninth digits (starting from the left) of then phone number are 56. For example, a phone number ending in “567” would meet the criteria.
H. Determine the maximum and minimum sales Total.
I. Determine the average sales Total.
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 LastName, FirstName, and Phone of all customers Who have had an order with Total 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 LastName, FirstName, and Phone of all customers who have had an order with Total greater than $ 100.00. Use a join, but do not use JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order.
O. Show the LastName, FirstName, and Phone of all customers who have had an order with Total greater than $ 100.00. Use a join using JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order.
P. Show the LastName, FirstName, and Phone of all customers who who have bought an Item named 'Desk Lamp'. Use a subquery. Present results sorted by LastName in ascending order and then FirstName in descending order.
Q. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a join, but do not use JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order.
R. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a join using JOIN ON syntax. Present results sorted by LastName in ascending order and then FirstName in descending order.
S. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery. Present results sorted by LastName in ascending order and then FirstName in descending order.
T. Show the LastName, FirstName, and Phone of all customers who have bought an Item named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery that is different from the combination used for question S. Present results sorted by LastName in ascending order and then FirstName in descending order.
U. Show the LastName, FirstName, Phone, and Item for customers who have bought an Item named 'Desk Lamp'. Also show the LastName, FirstName, and Phone of all the other customers. Present results sorted by Item in ascending order, then LastName in ascending order, and then FirstName in descending order.
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.