Assume that The Queen Anne Curiosity Shop designs a database with the following
ID: 3585157 • Letter: A
Question
Assume that The Queen Anne Curiosity Shop designs a database with the following tables. CUSTOMER(CustomerID,LastName,FirstName,City,Phone) VENDOR(VendorID,CompanyName,ContactLastName,ContactFirstName,City,Email) ITEM(ItemID,ItemDescription,PurchaseDate,ItemCost,VendorID ) SALE(SaleID,CustomerID,ItemID,SaleDate,SubTotal,Tax,Total) The referential integrity constraints are: • VendorID in ITEM must exist in VendorID in VENDOR • CustomerID in SALE must exist in CustomerID in CUSTOMER • ItemID in SALE must exist in ItemID in ITEM Write SQL statements and answer questions for this database as follows. Answer the questions in order. Please use comment /*** Question X ***/ before each query to separate your answers (X should be question number).
11. For ITEM table, group by VendorID. Show VendorID and corresponding sum of ItemCost
12. Add one constraint to question above: only show the vendors with sum of ItemCost more than $2000. 1
13. List ItemDescription and ItemCost of items that were purchased from company with CompanyName as “Linens and Things”. Use subquery. Note: you will need two tables ITEM and VENDOR to answer this query
14. Answer the question above using join.
15. List LastName, FirstName, and Phone of customers who have purchased an item with Total more than $1000. Use subquery. Note: you will need two tables CUSTOMER and SALE to answer this query
16. Answer the question above using join.
17. We want to show two more columns from the question above: also show the SaleDate and Total. You can only use join to answer this question.
18. Show ItemDescription for items sold to customer from Seattle. You can choose to use join or subquery. Note: you will need three tables CUSTOMER, SALE, and ITEM to answer this query
19. We want to show 3 more columns from the question above. Besides ItemDescription, also show customer name (FirstName and LastName) and Total in SALE.
Bones Questions (10 points)
1. Show the customers who purchased the most expensive items (highest Total value in SALE). Note: use join
2. Sort customers by the total amount of their purchases (sum of total in SALE)
3. Show the customer name (in CUSTOMER), Total (in SALE), ItemDescription (in ITEM), and CompanyName (in VENDOR) for Total (in SALE) is more than 2000. Note: use join
Explanation / Answer
/*** Question 11 ***/
SELECT VendorId, SUM(ItemCost) FROM ITEM
GROUP BY VendorId
/*** Question 12 ***/
SELECT VendorId, SUM(ItemCost) FROM ITEM
GROUP BY VendorId
HAVING SUM(ItemCost) > 2000.1
/*** Question 13 ***/
SELECT ItemDescription, ItemCost FROM ITEM
WHERE VendorId IN (
SELECT VendorId FROM VENDOR
WHERE CompanyName = 'Linens and Things'
)
/*** Question 14 ***/
SELECT ItemDescription, ItemCost FROM ITEM I INNER JOIN VENDOR V
ON I.VendorId = V.VendorId
AND V.CompanyName = 'Linens and Things'
/*** Question 15 ***/
/*** As Par my understanding, this will look for each item which
might result multiple records as rdundant data. ***/
SELECT C.FirstName, C.LastName, C.Phone FROM CUSTOMER C
WHERE CustomerId IN (
SELECT CustomerId FROM SALES
WHERE Total > 1000
)
/*** Question 16 ***/
/*** As Par my understanding, this will look for each item which
might result multiple records as rdundant data. ***/
SELECT C.FirstName, C.LastName, C.Phone FROM CUSTOMER C INNER JOIN SALES S
ON C.CustomerId = S.CustomerId AND S.Total > 1000
/*** Question 17 ***/
/*** As Par my understanding, this will look for each item which
might result multiple records as rdundant data. ***/
SELECT C.FirstName, C.LastName, C.Phone, S.SaleDate, S.Total FROM CUSTOMER C INNER JOIN SALES S
ON C.CustomerId = S.CustomerId AND S.Total > 1000
/*** Question 18 ***/
SELECT I.ItemDescription FROM ITEM I INNER INNER JOIN SALES S
ON I.ItemId = S.ItemId INNER JOIN CUSTOMER C
ON S.CustomerId = C.CustomerId
AND C.City = ‘Seattle’
/*** Question 19 ***/
SELECT C.FirstName, S.LastName, I.ItemDescription, S.Total FROM ITEM I INNER INNER JOIN SALES S
ON I.ItemId = S.ItemId INNER JOIN CUSTOMER C
ON S.CustomerId = C.CustomerId
AND C.City = ‘Seattle’
/*** Question 1 ***/
SELECT C.CustomerID, C.FirstName, C.LastName FROM
CUSTOMER C INNER JOIN ( SELECT CustomerID, Total FROM SALES ) T
ON C.CustomerID = T.CustomerID
WHERE T.Total = (SELECT max(Total) FROM SALES)
/*** Question 2 ***/
SELECT * FROM (SELECT S.CustomerID, SUM(S.Total) AS sumTot FROM SALES S) T
ORDER BY sumTot
/*** Question 3 ***/
SELECT C.FirstName, C.LastName, S.Total, I.ItemDescription, V.CompanyName FROM
CUSTOMER C INNER JOIN SALES S
ON C.CustomerID = S.CustomerID
/*** End Of Solutions ***/
I wrote appropriate comments about the Question, if required.
Thank You.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.