The following database is maintained by a bookstore: the database structure is shown below. AUTHOR(AuthorName) BOOK(ISBN, AuthorName, Title, PublisherName, PublicationYear, RetailPrice) AuthorName foreign key to AUTHOR PublisherName foreign key to PUBLISHER PUBLISHER(PublisherName) CUSTOMER(CustomerNumber, Name, Address, Phone, Email, CreditCardNumber, ExpiryDate) ORDER(OrderNumber, CustomerNumber, OrderDate) CustomerNumber foreign key to CUSTOMER ORDERDETAIL(OrderNumber, ISBN, QuantitySold, RetailPrice, TotalCost) OrderNumber foreign key to ORDER Write SQL code to implement the following separate queries numbered 1 to 4: 1. Find all orders placed in 2018-2019. 2. Report the following information: ISBN, AuthorName, Title, RetailPrice for all books written by Alexandre Dumas or by Mark Twain, published by ClassicsPublishingCo, with a retail price at least $24.99.
The following
AUTHOR(AuthorName)
BOOK(ISBN, AuthorName, Title, PublisherName, PublicationYear, RetailPrice)
AuthorName foreign key to AUTHOR
PublisherName foreign key to PUBLISHER
PUBLISHER(PublisherName)
CUSTOMER(CustomerNumber, Name, Address, Phone, Email, CreditCardNumber, ExpiryDate)
ORDER(OrderNumber, CustomerNumber, OrderDate)
CustomerNumber foreign key to CUSTOMER
ORDERDETAIL(OrderNumber, ISBN, QuantitySold, RetailPrice, TotalCost)
OrderNumber foreign key to ORDER
Write SQL code to implement the following separate queries numbered 1 to 4:
1. Find all orders placed in 2018-2019.
2. Report the following information: ISBN, AuthorName, Title, RetailPrice for all books written by
Alexandre Dumas or by Mark Twain, published by ClassicsPublishingCo, with a retail price at least
$24.99.
3. Create a table which shows the value of orders by each customer who actually placed an order
in 2019. Group in order by customer names. (Hint: use an appropriate outer join structure.)
4. Create a query which shows the total book sales revenues received from all orders for each
author. Show the results for each author, starting from the largest total value of books sold to
the smallest. (Hint: use an appropriate outer join structure.)
Step by step
Solved in 2 steps with 2 images
