For this assignment, we will be using the following relational schema for our qu

Question

For this assignment, we will be using the following relational schema for our queries. Customer(CID, Name, Address) Orders(Ordernum, CID, Order_date, Cardnum) Orderlist(Ordernum, ISBN, Qty, Ship_date) Book(ISBN, Title, Author, Qty_in_stock, Price) When an order is placed, there is one entry added to Orders which contains the customer information and order details. Then there is one entry per book entered into Orderlist with "NULL" in the ship date which will be replaced once that line item has been shipped. Each book item will only be shipped when all the copies are present (ie: we have sufficient Qty_in_stock for the Qty of the order, that way there is a unique ship_date for each line item). Note: you can also take advantage of renaming (RA) and views (SQL) to break down some of the queries into smaller parts.
——————— QUESTION HERE ———————
Relational Algebra Queries
5. Write a query which will list the customer name and book title for the customer who has purchased the most expensive book.
For this assignment, we will be using the following relational schema for our queries. Customer(CID, Name, Address) Orders(Ordernum, CID, Order_date, Cardnum) Orderlist(Ordernum, ISBN, Qty, Ship_date) Book(ISBN, Title, Author, Qty_in_stock, Price) When an order is placed, there is one entry added to Orders which contains the customer information and order details. Then there is one entry per book entered into Orderlist with "NULL" in the ship date which will be replaced once that line item has been shipped. Each book item will only be shipped when all the copies are present (ie: we have sufficient Qty_in_stock for the Qty of the order, that way there is a unique ship_date for each line item). Note: you can also take advantage of renaming (RA) and views (SQL) to break down some of the queries into smaller parts.
——————— QUESTION HERE ———————
Relational Algebra Queries
5. Write a query which will list the customer name and book title for the customer who has purchased the most expensive book.
For this assignment, we will be using the following relational schema for our queries. Customer(CID, Name, Address) Orders(Ordernum, CID, Order_date, Cardnum) Orderlist(Ordernum, ISBN, Qty, Ship_date) Book(ISBN, Title, Author, Qty_in_stock, Price) When an order is placed, there is one entry added to Orders which contains the customer information and order details. Then there is one entry per book entered into Orderlist with "NULL" in the ship date which will be replaced once that line item has been shipped. Each book item will only be shipped when all the copies are present (ie: we have sufficient Qty_in_stock for the Qty of the order, that way there is a unique ship_date for each line item). Note: you can also take advantage of renaming (RA) and views (SQL) to break down some of the queries into smaller parts.
——————— QUESTION HERE ———————
Relational Algebra Queries
5. Write a query which will list the customer name and book title for the customer who has purchased the most expensive book.

Explanation / Answer

select distinct Name,Title from Customer,Orders,Orderlist,Book where customer.CID=Orders.CID and Orders.Ordernum=Orderlist.Ordernum and Orderlist.ISBN=Book.ISBN and Price=(select max(Price) from Book)

this query joins tables Customers and Orders using CID

Orders and Orderlist using ordernum

Orderlist and Book using ISBN

thus we can find all information regarding customers orders and using max(price), first find MAX Price of all books

and compare the customers who have ordered the book equal to max price.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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