Exam 1 - Queen Anne Curiosity Shop

Instructions

• Answer Questions A through U found on pages 133-140 in Chapter 2 for the Queen Anne Curiosity Shop.
• Deliver a single sql file, along with a document that contains all the content from the sql file.
• Each query should start with a comment line that looks like:
  /* *** EX1-<QACS.Q> *** */
  Example for Question A:
  /* *** EX1-QACS.A*** */
• Do not include the result table unless specifically directed to.
• Include at least one line of white space between answers.

Database Information

Assume that The Queen Anne Curiosity Shop designs a database with the following tables:

• CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone, ReferredBy)
• 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:

• ReferredBy in CUSTOMER must exist in CustomerID in CUSTOMER
• 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

Questions A-U

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 their 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 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 ItemDescription 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. HINT: In Microsoft Access 2016 you will either need to use a UNION statement or a sequence of two queries to solve this, because Microsoft Access disallows nesting an INNER join inside a LEFT or RIGHT join. The other DBMS products can do it with one query (not a UNION statement).