Sql 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 :

• • Distinct must not be specified ; that is, duplicate rows must not be eliminated from the query results.
• • The from clause must specify only one updateable table. The view must have a single underlying source table.
• • The select list cannot contain expressions, calculated columns, or column functions.
• • The where clause must not include a sub-query. Only simple row by row search conditions may be used.
• • The select list must include all the columns specified with the not null constraint.

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 :

• • Security : A user can be permitted to access the database, only through a small set of views that contain the specific data the user is authorized to see.
• • Query simplicity : A view can draw data from several different tables and present it as a single table, thus effectively turning multi-table queries into single-table queries. Internally RDBMS uses multi-table queries.
• • Structural simplicity : Views can give a user, a personalized view of the database structure, presenting the database as a set of virtual tables that make sense to the user.
• • Data integrity : If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets specified integrity constraints.

Disadvantages of views :

• • Performance : The DBMS translates the queries against the view into queries against the underlying source tables. If a view is defined by a multi-table query, then even a simply query against a view becomes a complicated join, and it may take a long time to complete. This is in reference to insert, delete and update operations.
• • Update restrictions : When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complicated views cannot be updated.