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

Question

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

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

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

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

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

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.