For this assignment, we will be using the following relational schema for our qu
ID: 3902159 • Letter: F
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.
Determine the SQL Queries
8. Write a query which will list the order number and the total cost of the back-ordered books, for any order which is not fully shipped.
10. Write a query to generate a list of customer names and book titles of the customers who currently have a book on back order which was previously shipped on a different order.
11. Write a query which will list the ISBN and title of book which has sold the most copies in the history of our book store.
12. Write a query which will list the customer name, order number and total value for any order which had more than 10 books ordered on it.
Explanation / Answer
Answer 8:
SELECT orderlist.Ordernum, SUM(book.Price * orderlist.Qty) as total_price FROM orderlist LEFT JOIN book ON orderlist.ISBN=book.ISBN GROUP BY orderlist.Ordernum;
Answer 10:
CREATE view customer_book as SELECT customer.Name, book.Title, orderlist.Ship_date FROM customer LEFT JOIN orders ON orders.CID = customer.CID LEFT JOIN orderlist ON orderlist.Ordernum=orders.Ordernum LEFT JOIN book ON orderlist.ISBN=book.ISBN
SELECT customer_book.Name as 'customer', customer_book.Title as 'book' FROM customer_book WHERE customer_book.Ship_date IS NOT NULL
Answer 11:
SELECT book.Title, book.ISBN FROM book WHERE book.ISBN IN (SELECT orderlist.ISBN FROM orderlist GROUP BY orderlist.ISBN ORDER BY COUNT(*) DESC) LIMIT 1
Answer 12:
CREATE VIEW order_qty_amount AS SELECT orderlist.Ordernum, SUM(orderlist.Qty) as bookQuantity, SUM(book.Price*orderlist.Qty) as totalCost FROM orderlist
LEFT JOIN book ON orderlist.ISBN=book.ISBN
GROUP BY orderlist.Ordernum ORDER BY SUM(orderlist.Qty)
SELECT customer.Name, order_qty_amount.totalCost FROM order_qty_amount LEFT JOIN orders ON order_qty_amount.Ordernum=orders.Ordernum
LEFT JOIN customer ON orders.CID=customer.CID WHERE order_qty_amount.bookQuantity > 10
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.