*** Solve using DATABSE ORACLE *** Please show the results for query

1. Create a view named vendors_with_invoices that includes all vendor information for those vendors that have invoices.

2. Now insert the following record through the view. Can you do it?

VENDOR_ID: 150

VENDOR_NAME: 'UK Postal Service'

VENDOR_CITY: 'Madison

VENDOR_STATE: 'WI'

VENDOR_ZIP_CODE: '53707'

DEFAULT_TERMS_ID: 1

DEFAULT_ACCOUNT_NUMBER: 552

Is the record inserted into Vendor table?

3. Create one more view, the same as before only now add the WITH CHECK OPTION clause. Can you insert the following record?:

VENDOR_ID: 151

VENDOR_NAME: Global Postal Service'

VENDOR_CITY: 'Madison

VENDOR_STATE: 'WI'

VENDOR_ZIP_CODE: '53707'

DEFAULT_TERMS_ID: 1

DEFAULT_ACCOUNT_NUMBER: 552

Why- Why not?

4. Create a new table named Customers_Statistics with the following columns:

Customer_id, Number_of_Orders, Number_of_Items

Complete the table definition by choosing the appropriate data types, primary and foreign keys.

5. Now write a query that selects for every customer the number of orders he has places and the number of items he has bought, including zeros for customers with no orders.

6. Using the query above populate the table Customers_Statistics

Hint: Use the statement INSERT INTO . SELECT

Extra: What is going to happen if you rollback?

7. Now add a new column to the table called Customer_Status that is a string of maximum length 20; Extra: What is going to happen if you rollback?

Now lets update the table so that Customer_Status has the following values:

ok when the number of orders is 0 or 1

good when the number of orders is 2 or more

great when the number of orders is > 5 and the number of items is >5

8. Delete from the table Customers_Statistics all the customers that have not placed any orders

9. Truncate table Customers_Statistics

10. Drop table Customers_Statistics