A word document consisting of:

Your SQL statements written as text where I can test your work by copying and paste your statement into the SQL server.

Standard size (not small) screen capture for executing your SQL statements in SQL server

In homework 2, you wrote SQL statements to do the following:

Create a new table and name it customers

Use SQL to create at least 10 different rows consisting of CustomerId (primary key), FirstName, LastName (required) , Street Address (required) , City (required), State (required), ZipCode (required), PhoneNumber (optional), email (required) ; Title (e.g., Mr. Ms. Optional ). Make sure that one of your customers has the same name like you. At least one of the customers has an and underscore as part of their name _

Below is an example, please make your own data(create your own data)

C_ID	Title	First_Name	Last_Name	Street_Address	City	State		Zip_Code	Phone_Number	email
1	Dr.	Tracy	Mikes	12345 South Main Road	New York	NY	74364-1123		(918) 555-1234	Mikes@il.com
10	Baron	Ludwig	vonWunderkind	12345 West Tillameeko Street	Chicago	IL	74366-1123		(918) 555-0123	Hotpants@il.com
2	Mrs.	Ann	Berlin	12345 West Third Street	Dyer	IN	73460-1123		(918) 555-2345	Berlin@yaho.com
3	Dr.	John	Druitt	12345 East Main Street	Spring Field	MO	73005-1123		(918) 555-3456	Druitt@google.com
4	Ms.	Annabelle	Smith	12345 North Rodeo Street	Tulsa	OK	74101-1123		(918) 555-4567	Smith@bing.com
5	Lord	Duke	Earl	12345 South Elvis Boulevard	Adair	OK	74330-1123		(918) 555-5678	Earl@mapquest.com
6	Duke	Earl	Smith	12345 West Sycamore Street	Langley	OK	74350-1123		(918) 555-6789	Smith@aol.com
7	Duchess	Fergie	Giepher	12345 East Second Street	Ketchum	OK	74349-1123		(918) 555-7890	Giepher@dogpile.com
8	NULL	Jack	Rabbit	12345 North South Street	Grove	OK	74344-1123		(918) 555-8901	Rabbit@infoquest.com
9	NULL	Jill	Hill	12345 South Grandma Way	Strang	OK	74367-1123		(918) 555-9012	Hill@yourmom.com

Create a second table called product with at least 10 different rows of product ID, Product Name, Brand, Price, Quantity at Hand, Date Product was added

Sample data below: please make your own data:

ProductID	ProductName	Brand	Price	Quantity	DateAdded
100	Radio	Sony	29.99	30	8/22/2012
101	Clock	LG	19.99	15	6/13/2012
102	Printer	HD	49.99	244	9/1/2012
103	Okama GameSphere	Wintendo	29.99	46	8/22/2012
104	Crockpot	Equate	99.99	25	2/14/2012
105	Widget	Bony	10.99	25	5/30/2012
106	Map	Fony	119.99	63	4/15/2012
107	Donkey	Brony	22.99	20	1/1/2012
108	Toaster	Tony	35.99	37	3/27/2012
109	Beef Wellington	Phat	19.99	47	6/16/2012

Create a business transactions table with at least 5 different records showing what customersID bought what productsID, on what date, and what was the quantity they bought, and the method of payment. Make sure that you add at least 3 different transactions based on you being the customer.

Sample data below: please make your own data

C_ID P_ID PurchaseDate QuantityPurchased PaymentMethod

1 101 2012-04-13 1 Visa

1 105 2012-07-22 2 Visa

1 109 2012-09-22 15 Visa

4 106 2012-02-22 7 AmEx

1 103 2012-06-09 3 Amex

3 108 2011-07-17 6 Cash

Use the tables created previously (in homework 2) to answer the following homework # 4 questions:

Make sure that your statements produce at least one row that satisfies each query requirements. If you dont have data to match the query below, add needed data before running the query.

Write an SQL query to display the customers name, email address, the item they bought, quantity purchased, price paid, and method of payment listed in ascending order by customers full name.

Write an SQL query to display customers full name, email address, the item they bought, listed in ascending order by customers full name for customers who bought Radio or printer.

Write an SQL query that uses aggregate functions to list total amount of money spent by each customer, and the number of items sold to each customer grouped by customer name and sorted according to the customer names

Write an SQL query that uses nested query to list all customers who didnt buy a radio or a printer.

Use aggregate functions to list the name of customers r who bought more than one item and the number of items the customers bought.