Database Management System

View

What is a View?

A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary.

Syntax for creating a view

CREATE[OR REPLACE]

VIEW view_name [(column_list)]

AS select_statement ;

Updatable Views:

• A simple view is one that:
• Derives data from only one table
• Contains no functions or groups of data
• Can perform DML operations through the view

Non-updatable Views:

• A complex view is one that:
• Derives data from many tables
• Contains functions or groups of data
• Does not always allow DML operations through the view

The Syntax of CREATE VIEW statement:

Documentation: https://dev.mysql.com/doc/refman/8.0/en/createview.html

Note: The MySQL error code 1064 is a syntax error. This means the reason theres a problem is because MySQL doesnt understand what youre asking it to do.

Switch to SQL Editor

• You should specify the classicmodels database before writing SQL statements using the following command:

USE db_name;

The USE statement tells MySQL to use the named database as the default (current) database for subsequent statements. This statement requires some privilege for the database or some object within it.

Note: The MSRP is Manufacturers suggested retail price ( ).

MySQL Workbench:

• You can see details of a table by clicking i button below:
• You can see the existing view by clicking Views menu below:

Task 1: Using the classicmodels database and write SQL statements to answer the following questions.

use classicmodels;

1. Create a view named mini_customer_view to display the customer name of all customers whose names start with the word Mini. Please verify by querying data from this view.

2. Create a view named prod_stock_view to display the product name and quantity in stock of the product that has the minimum quantities in stock. Please verify by querying data from this view.

3. Create a view named totalamount_orders_view to display the order number, order date and the total amount of sales of all orders and sort the results in descending order by the total amount of sales. Name three columns of the view to orderno, orderdate and total_amount, respectively. Please verify by querying data from this view.

4. Create a view named customer_samecity_view to display the customer name and city of all customers who live in the same city of their sales rep employees office city. Name two view columns to cust_name and cust_city, respectively. Please verify by querying data from this view.

5. Create a view named maxcredit_city_view to display the city and the maximum credit limit of all customers in each city. Please verify by querying data from this view.

6. Create a view named maxcredit_london_view to display the city and the maximum credit limit of all customers who live in London city. You should create this view from the maxcredit_city_view view in Question 5. Please verify by querying data from this view.

7. Create a table named offices_copy with copying the structure and data from the offices table using the following commands:

create table offices_copy as select * from offices;

Create a view named usa_office_view to display office code, city and state of the country USA from the offices_copy table. Please verify by querying data from this view.

8. Try to insert a new row into the offices_copy table through the usa_office_view view created in Question 7. What happens about the data insertion? Please explain.

9. To resolve the problem found in Question 8, Please modify the usa_office_view view to ensure that you can insert a new row through this view (an updatable view). Please show the data insertion of the offices_copy table.

Hint: You can create a new row by yourself.

10. Please delete both the structure and data of the offices_copy table. What happens to an existing view that references the offices_copy table? Please explain.