Drop view statement

The drop view statement is used to drop a view.

Drop view view-name <drop view statement syntax>

Joined views :

Joined views are used to simplify multi-table queries. A joined view draws its data from two or three different tables and presents the query results as a single virtual table. Once the view is defined, one can use a single-table query against the view for requests that would otherwise each require a two-table or three-table join.

Create view cust_view AS
Select customer_details.cust_last_name, customer_details.cust_first_name,fixed_deposit_no, amount_in_dollars
From customer_details, customer_fixed_deposit
Where customer_details.cust_id=customer_fixed_deposit.cust_id ;
<joined views>

A view can be referenced like a real table in a select, insert, delete, or update statement. However, more complex views cannot be updated ; they are read only views.

View updates :

A view can be updated if the query that defines the view meets all of the following restrictions :

Checking view updates (check option) :

If a view is defined by a query that includes the where clause, only rows that meet the search criteria are visible in the view. Other rows may be present in the source table(s) from which the view is derived, but they are not visible through the view.

Create view view_customer AS
Select cust_id, cust_last_name, account_no, account_type, bank_branch
From customer_details
Where bank_branch=Downtown ;
<creation of a simple view>
Insert into view_customer
Values(101, Costner, 105, savings, Bridgewater ) ;
<insertion in a simple view>

Note :

This is a perfectly valid SQL statement, and the RDBMS inserts a new row with the specified column values into the customer_details table. However, the newly inserted row does not meet the search condition for the view. As a result, if one runs this query immediately after the insert statement the newly added row does not show up in the view.

Select cust_id, cust_last_name, bank_branch
From view_customer ;

SQL can allow DBMS to detect and prevent this type of insert or update from taking place through the view with the CHECK OPTION. The CHECK OPTION is specified the create view statement, as shown below :

Create view view_customer AS
Select cust_id, cust_last_name, account_no, account_type, bank_branch
From customer_details
Where bank_brach=Nayapalli
With CHECK OPTION ;
<create view with CHECK OPTION>

Advantages of views :

Disadvantages of views :



  Tutorials

Popular Links

Contact Us

address Plot No-741,2ND Floor
Opp. Bhagabati Temple,Jayadev Vihar
      Bhubaneswar-751013
      Email: info@silantechnology.com
      Phone: 0674-2361252
address
39877, sundale dr, apt#101, Fremont, California 94538, USA
       Phone: +1(262)388-7619

facebook twitter google linked in

© 2018 Silan Technology. All Rights Reserved