SQL is required. Note: Chapter 1 use views. SQL is required.

SQL is required. Note: Chapter 1 use views. SQL is required.
18.1 View

A view is a virtual table. Unlike a table that contains data, it attempts to Dynamically Retrieve data queries only when used.
You can encapsulate the entire Query into a virtual table named ProductCustomers. The same data can be easily retrieved as follows.

SELECT cust_name,cust_contactFROM ProductCustomersWHERE prod_id = 'RGAN01';

ProductCustomers is a view that does not contain any columns or data. It contains a query.

18.1.1 why View

(1) Reuse SQL statements
(2) simplify complex SQL operations. After writing a query, you can easily reuse it without having to know its basic query details.
(3) Use the table components instead of the entire table.
(4) protect data. You can grant users the access permission to a specific part of a table instead of the entire table.
(5) change the data operator and representation. The view returns data in a different format than the underlying table.

18.1.2 view rules and restrictions

Some of the most common rules and restrictions for creating and using a view
(1) A view must be uniquely named like a table.
(2) There is no limit on the number of views that can be worn
(3) To create a view, you must have sufficient access permissions. These permissions are usually granted by the database administrator.
(4) A view can be nested, that is, a view can be constructed by querying data from other views.
(5) Many DBMS do not use the order by clause in View queries.
(6) Some DBMS require naming all columns returned. If the column is a calculated field, aliases must be used.
(7) views cannot be indexed or associated triggers or default values.
(8) Some DBMS regard the view as a read-only query, which means that data can be retrieved from the view, but data cannot be written to the underlying table.
(9) Some DBMS allow the creation of such a view. It does not allow insertion or update of rows that no longer belong to the view.

18.2 create a view

You can use the create view statement to CREATE a VIEW. Create view can only be used to CREATE a VIEW that does not exist.
Use DROP to delete a view:

DROP VIEW viewname;

Overwrite (or update) A View: You must DROP it and recreate it.

18.2.1 simplify complex connections using views

CREATE VIEW ProductCustomers ASSELECT cust_name,cust_contact,prod_idFROM Customers,Orders,OrderItemsWHERE Customers.cust_id = Order.cust_id     AND OrderItems.order_num = Order.order_num;

This statement creates a view named ProductCustomers, which returns a list of all customers who have purchased any product.
Search customers who have purchased RGAN01

SELECT cust_name,cust_contactFROM ProductCustomersWHERE prod_id = 'RGAN01';

18.2.2 reformat the retrieved data with the view

Create a formatted View

CREATE VIEW VendorsLocations ASSELECT RTRM(vend_name) || ' ( ' || RTRIM(vend_country) || ' ) ' AS vend_titleFROM Vendors;

18.2.3 use an attempt to filter unwanted data

Define the CustomerEMailList view to filter customers without email addresses.

CREATE VIEW CustomerEMailList ASSELECT cust_id, cust_name, cust_emailFROM CustomersWHERE cust_email IS NOT NULL;

18.2.4 use view and calculated fields

Search for items in a specific order and calculate the total price of each item

CREATE VIEW OrderItemExpanded ASSELECT order_num,     prod_id,     quantity,     item_price,     quantity*item_price AS expanded_price

FROM OrderItems;
Retrieve order 20008 details based on The View

SELECT *FROM OrderItemsExpandedWHERE order_num = 20008;