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

Question

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

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email)
EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)

VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName,Address, City, State, ZIP, Phone, Fax, Email)
ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID)
SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total)
SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)

The referential integrity constraints are:
CustomerID in PURCHASE must exist in CustomerID in CUSTOMER
VendorID in ITEM must exist in VendorID in VENDOR
CustomerID in SALE must exist in CustomerID in CUSTOMER
EmployeeID in SALE must exist in EmployeeID in EMPLOYEE
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, EmployeeID of EMPLOYEE, 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
EmployeeID Start at 1 Increment by 1
VendorID Start at 1 Increment by 1
ItemID Start at 1 Increment by 1
SaleID Start at 1 Increment by 1

A vendor may be an individual or a company. If the vendor is an individual, the CompanyName field is left blank, while the ContactLastName and ContactFirstName fields must have data values. If the vendor is a company, the company name is recorded in the CompanyName field, and the name of the primary contact at the company is recorded in the ContactLastName and ContactFirstName fields.

For each SQL statement you write, show the results based on your data.

1) Write an SQL statement to list LastName, FirstName, and Phone of customers who have purchased an item that has an ItemPrice of $500 or more. Use a subquery.

2) Answer part (1) but use a JOIN ON syntax.

3) Write an SQL statement to show all customers and the items these customers have purchased. The output from this statement should include any items (if any) that have not been purchased by a customer. The SQL statement output should list CustomerID, LastName, FirstName, SaleID, SaleItemID, and ItemDescription. (Hint: Use JOIN ON syntax).

4) Write an SQL statement to show all customers and the items these customers have purchased. The output from this statement should include any customers (if any) that have not purchased any items. The SQL statement output should list CustomerID, LastName, FirstName, SaleID, SaleItemID, and ItemDescription. (Hint: Use JOIN ON syntax).

5) Write an SQL statement to list LastName, FirstName, and Phone of customers who have purchased an item that was supplied by a vendor with a CompanyName that begins with the letter L.  Do not use a sub-query.

6) Write an SQL statement to show the sum of SubTotal for each customer. List CustomerID, LastName, FirstName, Phone, and the calculated result. Name the sum of SubTotal as SumOfSubTotal and sort the results by CustomerID, in descending order.

Explanation / Answer

Assume that The Queen Anne Curiosity Shop designs a database with the following tables:
CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email)
EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)
VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName,Address, City, State, ZIP, Phone, Fax, Email)
ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID)
SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total)
SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)

1.
select Lastname, Firstname, phone from customer where customerid in
   select a.customerid from sale a, sale_item b where a.saleid = b.saleid and b.itemprice>500;

3.
select a.customerid, a.lastname, a.firstname, b.saleid, c.sale_itemid from customer a, sale b, sale_item c where c.sale_id=b.sale_id and b.customerid=a.customerid;

4.
select a.customerid, a.lastname, a.firstname, b.saleid, c.sale_itemid, d.itemdescription from customer a, sale b, sale_item c, item d where c.sale_id=b.sale_id and b.customerid=a.customerid and c.itemid=d.itemid;

5.
select lastname, firstname, phone from customer a, vendor b, item c, sale d, sale_item e where b.vendorid=c.vendorid and e.sale_id =d.saleid and d.customerid = a.customerid;

6.
select sum(b.subtotal) sumofsubtotal, a.lastname, a.firstname, a.phone from customer a, sale b where a.customerid=b.customerid group by b.customerid oderby desc a.customerid;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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