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

 

1. customer:

10010, Johnson, Albert, A, 612, 8442573

10011, Edwards, Leona, K, 763, 8941238

10012, Smith, Walter, W, 612, 8942285

10013, Roberts, Paul, F, 612, 2221672

10014, Orlando, Myla, NULL, 615, 2971228

 

1. invoice:

1001, 10011, 2008-08-03

1002, 10014, 2008-08-04

1003, 10012, 2008-03-20

1004, 10014, 2008-09-23

 

1. vendor:

232, Bryson, Smith, 615, 2233234

235, Walls, Anderson, 615, 2158995

236, Jason, Schmidt, 651, 2468850

 

1. product:

12321, hammer, 189 ,20, 232

65781, chain, 12, 45, 235

34256, tape, 35, 60, 236

12333, drill, 200 ,10, 232

 

1. line:

1001,  12321, 1

1001,  65781, 3

1002,  34256, 6

1003,  12321, 5

1002,  12321, 6

 

 

 

 

 

Question 3: Checking entity and referential integrity:

 

Jot(Write) an SQL statement to do each of the following tasks in the given order (try 1, 2, 3, 4, then 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

 

1. Insert the following entry in  INVOICE

1005, 10017, '2008-11-30'

 

1. Insert the following entry in PRODUCT

12322, 'hammer', 189, 20, 231

 

1. Insert the following entry to the VENDOR table

231,'Adam', 'Eric', 615, 2158995

 

1. Insert the following entry in PRODUCT

12322, 'coil', 189, 20, 231

 

Question 4: SQL Queries

 

Write SQL statements to answer each of the following questions. 

 

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

 

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

 

1. List the product code, product description and product quantity for products with invoice number 1001.

 

1. List all product description and product price supplied by vendor whose vendor contact is 'Somebody'.

 

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

 

1. For each product bought by a customer, list product description,  customer's first name and last name.
Make a database using your name. Then 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. 

 

Customer		Invoice		Vendor
cus_code        : integer		inv_number  :integer		vend_code        : integer
cus_lname      : varchar(20)		cus_code        :integer		vend_name        : varchar(30)
cus_fname      : varchar(20)		inv_date         :date		vend_contact     :   varchar(30)
cus_initial        : char(1)		foreign key cus_code		vend_areacode  : integer
cus_areacode : integer		references customer table		vend_phone      : integer
cus_phone      : integer
Product		Line
prod_code  : integer		inv_number   : integer
prod_desc    : varchar(50)		prod_code      : integer
prod_price   : integer		line_units           : integer
prod_quant  : integer		foreign key inv_number
vend_code    : integer		references Invoice table
foreign key vend_code		foreign key prod_code
references Vendor table		references Product table