Create a new database and name it with your last name then 1

Create a new database and name it with your last name then

1. 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

Dr.

Tracy

Mikes

12345 South Main Road

New York

74364-1123

(918) 555-1234

Mikes

Baron

Ludwig

vonWunderkind

12345 West Tillameeko Street

Chicago

74366-1123

(918) 555-0123

Mrs.

Ann

Berlin

12345 West Third Street

Dyer

73460-1123

(918) 555-2345

Berlin

Dr.

John

Druitt

12345 East Main Street

Spring Field

73005-1123

(918) 555-3456

Druitt

Ms.

Annabelle

Smith

12345 North Rodeo Street

Tulsa

74101-1123

(918) 555-4567

Smith

Lord

Duke

Earl

12345 South Elvis Boulevard

Adair

74330-1123

(918) 555-5678

Ear

Duke

Earl

Smith

12345 West Sycamore Street

Langley

74350-1123

(918) 555-6789

Duchess

Fergie

Giepher

12345 East Second Street

Ketchum

74349-1123

(918) 555-7890

NULL

Jack

Rabbit

12345 North South Street

Grove

74344-1123

(918) 555-8901

com

NULL

Jill

Hill

12345 South Grandma Way

Strang

74367-1123

(918) 555-9012

Hill

2. 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

8/22/2012

101

Clock

19.99

6/13/2012

102

Printer

49.99

244

9/1/2012

103

Okama GameSphere

Wintendo

29.99

8/22/2012

104

Crockpot

Equate

99.99

2/14/2012

105

Widget

Bony

10.99

5/30/2012

106

Map

Fony

119.99

4/15/2012

107

Donkey

Brony

22.99

1/1/2012

108

Toaster

Tony

35.99

3/27/2012

109

Beef Wellington

Phat

19.99

6/16/2012

3. 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

4. Write an sql statement to add a column to the customer table specifying the customer type which can be either individual (I) or business (B)- constraint.

5. Write an sql statement to add a check constraint on the price of the item. Choose a value that you think appropriate. Example between .01 <= Price <=10,000.00

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
10	Baron	Ludwig	vonWunderkind	12345 West Tillameeko Street	Chicago	IL	74366-1123	(918) 555-0123
2	Mrs.	Ann	Berlin	12345 West Third Street	Dyer	IN	73460-1123	(918) 555-2345	Berlin
3	Dr.	John	Druitt	12345 East Main Street	Spring Field	MO	73005-1123	(918) 555-3456	Druitt
4	Ms.	Annabelle	Smith	12345 North Rodeo Street	Tulsa	OK	74101-1123	(918) 555-4567	Smith
5	Lord	Duke	Earl	12345 South Elvis Boulevard	Adair	OK	74330-1123	(918) 555-5678	Ear
6	Duke	Earl	Smith	12345 West Sycamore Street	Langley	OK	74350-1123	(918) 555-6789
7	Duchess	Fergie	Giepher	12345 East Second Street	Ketchum	OK	74349-1123	(918) 555-7890
8	NULL	Jack	Rabbit	12345 North South Street	Grove	OK	74344-1123	(918) 555-8901	com
9	NULL	Jill	Hill	12345 South Grandma Way	Strang	OK	74367-1123	(918) 555-9012	Hill

CREATE DATABASE:-
The CREATE DATABASE statement can be used to create a database.
QUERY:-
CREATE DATABASE LASTNAME;

1.CREATE TABLE:-
The CREATE TABLE statement can be used to create a table in a database.
QUERY:-
CREATE TABLE customers
(
C_ID int,
Title varchar(10),
FirstName varchar(10),
LastName varchar(10),
Street_Address varchar(10),
City varchar(10),
State varchar(10),
Zip_Coode int,
PhoneNumber int,
email varchar(10)
);

DATA INSERTION TO THE TABLE:-
INSERT INTO statement can be used to insert the new data to the table.
QUERY:-
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (1,Mr,Sachin,Tendulkar,121 Gandhi Street,Mumbai,Maharastra,530031,9911223381);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (2,Mr,Ganguly,Saurav,122 Gandhi Street,Kolkata,West Bengal,530032,9911223382);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (3,Mr,Sehwag,Virendra,123 Gandhi Street,Delhi,Delhi,530033,9911223383);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (4,Mr,Dravid,Rahul,124 Gandhi Street,Bangalore,Karnataka,530034,9911223384);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (5,Mr,Dhoni,Mahendrasingh,125 Gandhi Street,Ranchi,Jarkhand,530035,9911223385);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (6,Mr,Irfan,Patan,126 Gandhi Street,Vadodara,Gujarath,530036,9911223386);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (7,Mr,Yusuf,Patan,127 Gandhi Street,Vadodara,Gujarath,530037,9911223387);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (8,Mr,Munaf,Patel,128 Gandhi Street,Vadodara,Gujarath,530038,9911223388);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (9,Mr,Rohith,Sharma,129 Gandhi Street,Visakhapatnam,AndhraPradesh,530039,9911223389);
INSERT INTO customers(C_ID,Title,FirstName,LastName,Street_Address,City,State,Zip_Code,PhoneNumber,email)
values (10,Mr,Yuvaraj,Singh,130 Gandhi Street,Haryana,Punjab,530040,9911223390);

2.PRODUCT TABLE:-
CREATE TABLE product
(
ProductID int,
ProductName varchar(255),
Brand varchar(255),
Price decimal(p,s),
Quabtity int,
DateAdded date
);

DATA INSERTION TO THE PRODUCT TABLE:-
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (111,TV,Apple,11,1,2017-01-01);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (112,Watch,Apple,12,2,2017-01-02);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (113,Mobile,Apple,13,3,2017-01-03);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (114,Laptop,Sony,14,4,2017-01-04);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (115,Pendrive,Sony,15,5,2017-01-05);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (116,Bag,Sony,16,6,2017-01-06);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (117,Fan,LG,17,7,2017-01-07);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (118,Mouse,Dell,18,8,2017-01-08);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (119,Keyboard,Dell,19,9,2017-01-09);
INSERT INTO product(ProductID,ProductName,Brand,Price,Quantity,DateAdded)
values (120,PowerBank,HP,20,10,2017-01-10);

3.Business_Transaction TABLE:-
CREATE TABLE Business_Transaction
(
C_ID int,
P_ID int,
PurchaseDate date,
QuantityPurchased int,
PaymentMethod varchar(10)
);

DATA INSERTION TO THE Business_Transaction:-
INSERT INTO Business_Transaction(C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
values (1,111,2017-01-01,1,COD);
INSERT INTO Business_Transaction(C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
values (2,112,2017-01-02,2,Visa);
INSERT INTO Business_Transaction(C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
values (3,113,2017-01-03,NetBanking);
INSERT INTO Business_Transaction(C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
values (3,115,2017-01-05,5,COD);
INSERT INTO Business_Transaction(C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
values (8,118,2017-01-08,8,Visa);

4.An SQL Statement to Add a Column to The CUSTOMER Table:-
ALTER TABLE customer ADD customer_type varchar(15);

5.An SQL Statement to Add a CHECK Constraint on the PRICE of the item:-
ALTER TABLE product ADD CONSTRAINT my_constraint CHECK(Price>=0);

OUTPUT:-
CONSTRAINT_NAME
COLUMN_NAME
my_constraint
Price