SQL Using Views.

Create and Remove a VIEW

• A view creates a virtual table from a SELECT statement and opens up a world of flexibility for data analysis and manipulation.
• You can create a view using any of these objects or combinations of objects:
  • A single table.
  • More than one table.
  • Another view.
  • Multiple views.
  • Combinations of views and tables.
• The SELECT statement on the view can have almost any complexity and
• In multiple-object views, you can use joins, subqueries or combinations of the two.
• They do not exist as independent entities in the database as "real" tables do.
• You can query views much as you query tables.
• Modifying data through views is restricted.

Create VIEW

CREATE VIEW view_name [(column_name [, column_name]...)]
AS
SELECT_statement

Create view CustomerInfoView (CustomerId, CustomerName,
   ShippingAddress, CITY, ZIP,  WorkPhone )
AS
select c.customer_id, CONCAT(Last_name,CONCAT(' ',First_name)),
   Addr_line, city, zip,  telephone_number
from customers c , addresses a,  telephones t
WHERE c.customer_id=a.customer_id
  AND c.customer_id=t.customer_id
  and Address_type='Shipping'
  and telephone_type='Work'

select * from CustomerInfoView;

Remove VIEW

Removal of views are performed in the same way as for tables.

DROP VIEW view_name;

DROP VIEW CustomerInfoView;