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 - Syntax:
CREATE VIEW view_name [(column_name [, column_name]...)]
AS
SELECT_statement
Create a view that lists Customers with shipping address and phone but only for those that have a 'Work' phone:
SQL server, MySQL, Oracle, PostgreSQL :
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'
You can try the view as the view already is created:
select * from CustomerInfoView;
The result should be:
CustomerId Customer Name Shipping Address CITY ZIP WorkPhone
10001 Nixon Ricard 4415 Metro Parkway Fort Myers Calford 32420 (978) 667-94
10003 Bowman Judith 2581 Richmond Terrace Staten Island Mappleton 14008 (371) 143-54

Remove VIEW

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

Remove VIEW - Syntax:
DROP VIEW view_name;
View example:
SQL server, MySQL, Oracle, PostgreSQL :
DROP VIEW CustomerInfoView;
Prev. in menu SQL/Join/View.php Next in menu
Prev. in menu SQL/Join/View.php Next in menu
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.