Must be completed in Access and include screenshots to show the steps.

Books
ISBN	Title	Author	PubDate	Cost	Retail	Category	PubID
0131873253	Database Systems	Hector Garcia	6/15/2008	$100.00	$128.68	Information Systems	1
0307352153	Quiet: The Power of Introverts in a World That Can't Stop Talking	Susan Cain	1/29/2013	$7.25	$9.60	Non-Fiction	4
0307954560	Meatless: More Than 200 of the Very Best Vegetarian Recipes	Martha Stewart	1/8/2013	$10.15	$15.00	Cooking	5
0743273567	The Great Gatsby	F. Scott Fitzgerald	9/30/2004	$6.23	$8.25	Fiction	3
0811877825	Goodnight, Goodnight Construction Site	Sherry Rinker	5/4/2011	$6.36	$9.24	Childrens	1
1590597699	Beginning Database Designq	Clare Churcher	1/24/2007	$19.22	$23.53	Information Systems	2
1591845521	Bend, Not Break: A Life in Two Worlds	Ping Fu	12/31/2012	$14.00	$16.02	Biography	4

SQL STATEMENTS

Write an SQL statement to display the publication ID, publication date, cost, and category of each book planned for publication ID number 4.

SELECT PubID, PubDate, Cost, Category

FROM Books

WHERE PubID=4;

Query1
PubID	PubDate	Cost	Category
4	1/29/2013	$7.25	Non-Fiction
4	12/31/2012	$14.00	Biography

Repeat the previous task but include ID and Date as column aliases for the first two columns.

SELECT PubID AS ID, PubDate AS Date, Cost, Category

FROM Books

WHERE PubID=4

Write an SQL statement to display the least expensive retail price book in each book category.

SELECT Retail

FROM Books

WHERE Retail=8.25

Query2

Retail

$8.25

Repeat the previous task but sort the displayed categories in the descending order.

SELECT Retail

FROM Books

ORDER BY Retail=DESC ORDER;

Orders
OrderID	CustomerID	OrderDate	ShippingDate	ISBN
1	1	12/3/2012	12/10/2012	0131873253
2	1	12/11/2012	12/13/2012	1591845521
3	2	12/12/2012	1/1/2013	0307352153
4	3	1/15/2007	3/15/2007	0743273567
5	4	5/19/2010	5/21/2010	0811877825
6	5	1/8/2013	1/10/2013	0131873253

Write an SQL statement to display the customer number, city of destination, and shipping date for all shipments with order number above 2. (Hint: Use the Orders table.)

SELECT CustomerID, ShippingDate

FROM Orders

WHERE OrderID Number>2;

Repeat the previous task but further restrict the output to shipping dates after January 1, 2013. (Hint: The date restriction will require a format identical to the one displayed in the previous task, and it must be surrounded by pound signs#01/01/2013# for example.)

SELECT CustomerID, ShippingDate

FROM Orders

WHERE ShippingDate>#01/01/2013#;

Write an SQL statement to display the publication ID, average retail price, and average cost of all books. Group the output by publication ID.

SELECT AVG(Retail), PubID,

FROM Books

GROUP BY PubID

Write INSERT statements to add the following data to the Publisher table:

INSERT INTO Books(Publisher)

Write a single SELECT statement that will verify the records inserted.

UPDATE Books(Records)

Write a DELETE statement to remove only the new BOOK BUSTER records.

DELETE FROM Books

WHERE Book Buster