Problem Assume that The Queen Anne Curiosity Shop designs a database with the fo

Question

Problem

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).

1. Show all customer information (Note: When we say show all customers, we mean show all columns for customers. Same for the questions below).

2. Show ItemID and ItemDescription for all items that cost more than $1000.

3. Count how many items cost more than $1000 Note: using COUNT(*)

4. Count how many distinct ItemDescription in ITEM table Note: using DISTINCT

5. Show ItemDescription and ItemCost for the item with maximum cost Note: using subquery

6. Show items with ItemDescription containing word “Table” and the ItemCost is more than $1000.

7. Show all customers NOT from city “Seattle” or “Bellevue” Note: use NOT IN

8. Show all the items in descending order of their ItemCost

9. Show all the vendors with CompanyName as NULL

10. Show the sum of Total in SALE

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.

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

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 1. select * from CUSTOMER;

Question2. select ItemID,ItemDescription from ITEM where ItemCost>1000;

Question3. select count(ItemID) from ITEM where ItemCost>1000;

Question4. select count(distinct(ItemDescriptiom)) from ITEM;

Question5. select ItemDescription,ItemCost from ITEM where ItemCost in (select max(ItemCost) from ITEM);

Question6. select ItemID,ItemDescription from ITEM where ItemDescription like '%Table%' and ItemCost>1000;

Question7. select * from CUSTOMER where City not in ('Seattle','Bellevue');

Question8. select * from ITEM order by ItemCost desc;

Question9. select * from VENDOR where CompanyName is null;

Question10. select sum(Total) from SALE;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.