Question

Write just one SQL statement per question

1. Display Publisher ID, Publisher Name and Total Number of Books published by each publisher. 2. List Publisher ID, Publisher Name, Title and price of the highest priced book. 3. List Order ID, Customer ID, Order Date, and Number of Items in each order. Order the data by Customer ID in ascending (A – Z) order. 4. Display Title, Category and Profit for each book in the children and computer category. 5. Display Last Name & First Name of Authors who has the same last name of a Customer. 5.Display ShipCity, ShipState and Total Sales. Determine ShipCity and ShipState with Lowest total Sales [Total Sales = SUM(QUANTITY*Retail)].

Answer 1

1.SELECT Publisher_ID,Publisher_Name,Books FROM Publisher GROUP BY Publisher;
2.SELECT Publisher_ID,Publisher_Name,Title,price WHERE price IN(SELECT MAX(price) FROM Publisher);
3.SELECT Order_ID,Customer_ID,Order_Date,Number_Of_Items FROM Order ORDER BY Customer_ID ASC;
4.SELECT Title,Category,Profit FROM Book WHERE Category= "Children" OR Category="Computer" GROUP BY Category;
5.SELECT LastName,FirstName FROM Author WHERE LastName IN (SELECT LastName FROM Customer);
6.SELECT ShipCity,ShipState,TotalState FROM Ship WHERE TotalSales IN (SELECT MIN(SUM(QUANTITY*Retail)) FROM Ship);