Submit SQL Script that includes SQL statements to answer Part 1, Part 2, and Part 4. Make sure that your SQL script runs without any errors.

Create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements.

1. customer: (cus_code:integer, cus_lname:varchar(20), cus_fname:varchar(20), cus_initial:char, cus_areacode:integer,cus_phone:integer).

2. invoice: (inv_number:integer, cus_code:integer, inv_date:date,

foreign key cus_code references customer(cus_code))

3. vendor:(vend_code:integer,vend_name:varchar(30),vend_contact:varchar(30),vend_areacode:integer,vend_phone:integer)

4. product:(prod_code:integer, prod_desc:varchar(50), prod_price:integer, prod_quant:integer,vend_code:integer,

foreign key (vend_code) referenecs Vendor(vend_code))

5. line: (inv_number:integer, prod_code:integer ,line_units:integer,

foreign key (inv_number) references Invoice(inv_number),

foreign key (prod_code) references Product (prod_code) )

Question 2 (5 Points): Inserting data:

Insert the following data in the tables using insert into statements:

1. customer:

10010, Ramas, Alfred, A, 615, 8442573

10011, Dunne, Leona, K, 713, 8941238

10012, Smith, Kathy, W, 615, 8942285

10013, Olowski, Paul,F, 615, 2221672

10014, Orlando, Myron, NULL, 615, 2971228

2. invoice:

1001, 10011, 2008-08-03

1002, 10014, 2008-08-04

1003, 10012, 2008-03-20

1004, 10014, 2008-09-23

3. vendor:

232, Bryson, Smith, 615, 2233234

235, SuperLoo, Anderson, 615, 2158995

236, Jason, Schmidt, 651, 2468850

4. product:

12321, hammer, 189 ,20, 232

65781, chain, 12, 45, 235

34256, tape, 35, 60, 236

12333, hanger, 200 ,10, 232

5. line:

1001, 12321, 1

1001, 65781, 3

1002, 34256, 6

1003, 12321, 5

1002, 12321, 6

Question 3 (5 Points): Checking entity and referential integrity:

Write an SQL statement to do each of the following tasks in the given order (try 1, 2, 3, 4, than 5). Explain whether and why the statement is correctly executed or not

1. Insert the following entry in CUSTOMER

10012, Juan, Rodriguez, J, 612, 7788776

2. Insert the following entry in INVOICE

1005, 10017, 2008-11-30

3. Insert the following entry in PRODUCT

12322, hammer, 189, 20, 231

4. Insert the following entry to the VENDOR table

231,Adam, Eric, 615, 2158995

5. Insert the following entry in PRODUCT

12322, coil, 189, 20, 231

Question 4 (9 Points): SQL Queries

Write SQL statements to answer each of the following questions.

1. List the Customer Code, Last name, and First Name for all customers.

2. List the invoice number and invoice date for all invoices of customer number 10014.

3. List the product code and product quantity for products in invoice number 1001.

4. List all product description and product price supplied by vendor whose vendor contact is Nobody.

5. Produce a list of product description, vendor name, and vendor phone for all products with quantity less than or equal to 60.

6. For each product bought by a customer, list product description, customers first name and last name.