Create SQL queries on your database based on the following requirements: 1. Dete
ID: 3739438 • Letter: C
Question
Create SQL queries on your database based on the following requirements:
1. Determine the Total Sales to each customer and profit generated from each customer. List CustID, LastName, FirstName, TotalSales and TotalProfit.
2. Display ISBN and Title of each book written by multiple authors.
3. Determine ShipCity and ShipState with Highest total Sales [SUM(QUANTITY*Retail)]. Display ShipCity, ShipState and Total Sales.
4. Display Last Name & First Name of Authors who has the same last name of a Customer.
5. What is the retail price of the least expensive Cooking book? Display ISBN, Title and Retail price of the book.
ERD of JustLeeBooksDatabase Orders BOOKAUTHOR 8 CustiD 8 OrderlD ISBN 8 AuthorlD CustD State Author ShipState AuthorlD ORDERITEMS Order D Fname ISBN promotion Books ISBN PubDate PubiD Cost PublD Contact Phone Category -Explanation / Answer
1. More info Needed such as how to calculate the profit
2. select ISBN, Title from BOOKAUTHOR a join Books b on a.ISBN = b.ISBN GROUP BY a.AuthorID
3. select ShipCity, ShipState, SUM(QUANTIRY * Retail) as ToTal Sales
from Orders A join ORDERITEMS B on A.OrderId = B.OrderId join Books c on B.ISBN = C.ISBN
4. select Lname and Fname from Author A join BOOKAUTHOR B on A.AuthorID = B. AuthorID join ORDERITEMS C on C.ISBN = B.ISBN join Orders on D.OrderID = C.OrderID join Customers E on E.CustID = D.CustId where A.Lname = E.LastName
5. select ISBN, Title, Retail from Books where Title = 'Cooking' ORDER BY Cost DESC LIMIT 1