Lab 1.2 Instructions

(1 point for each question)

Suppose you have two tables: Table Product:

PROD_ID	PROD_NAME	PROD_PRICE	PROD_PROD_DATE	PROD_VENDOR
1101	Table	100	1-Jan-18	2
1102	Chair	80	3-Mar-18	3
1103	Armchair	90	6-May-18	2
1104	Nightstand	110	4-Apr-18	1
1105	Bed	200	3-Mar-18	3
1106	Dresser	150	5-May-18	3
1107	Daybed	190	10-Feb-18	2

Table Vendor:

VEND_ID	VEND_NAME	VEND_ST
1	Green Way Inc	GA
2	Forrest LLC	NC
3	AmeriMart	NC

Identify primary and foreign keys. Draw the relational schema to show the relationship between two tables using Crow Foot Notation https://www.codeproject.com/Articles/878359/Data-modelling-using-ERD-with-Crow-Foot-Notation You can use any software

Create tables. Do not forget about primary and foreign keys. Provide SQL code and screenshot of DESC statements.

Insert data. Provide SQL code and screenshot of select statements.

Suppose you wanted quick lookup capability to get a listing of all products supplied by a given vendor. Which table would be the basis for the INDEX table, and what would be the index key? Create the index. Provide SQL code.

Create a view that lists all products in alphabetical order and names of their vendors. Select all rows from the view and provide a screenshot. Hint: Use join.

Find all products that were manufactured between February 15 and April 15, 2018. Provide SQL code and the screenshot of the result.

Delete vendor AmeriMart form Vendor table. Provide SQL code and the result of select statement for both tables before deletion and after deletion. If you still have bed, dresser and chair in your product table, go back to #3 and fix foreign keys.

Update table Products. Change the name of #1101 to Cherry Table.

Find all vendors who supplied less than two products. Provide SQL code and the screenshot of the results.