INT2005-Lab04: View Solved

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/create-view.html

Note: The MySQL error code 1064 is a syntax error. This means the reason there’s a problem is because MySQL doesn’t understand what you’re 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 “Manufacturer's 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;

 

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.
 

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.
 

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.
 

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 employee’s office city. Name two view columns to cust_name and cust_city, respectively. Please verify by querying data from this view.
 

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.
 

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.
 

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.

 

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.
 

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.

 

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.