Which Of The Following Are Correct Restrictions For An Updatable View?

SQL - Using Views

A view is cypher more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.

A view tin can comprise all rows of a table or select rows from a tabular array. A view can be created from one or many tables which depends on the written SQL query to create a view.

Views, which are a type of virtual tables allow users to do the following −

Construction information in a way that users or classes of users find natural or intuitive.
Restrict access to the data in such a fashion that a user tin see and (sometimes) change exactly what they need and no more.
Summarize information from diverse tables which can be used to generate reports.

Creating Views

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables or another view.

To create a view, a user must have the appropriate system privilege according to the specific implementation.

The basic CREATE VIEW syntax is as follows −

CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

Yous can include multiple tables in your SELECT statement in a similar manner as yous employ them in a normal SQL SELECT query.

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+ | ID | NAME     | Age | Accost   | SALARY   | +----+----------+-----+-----------+----------+ |  i | Ramesh   |  32 | Ahmedabad |  2000.00 | |  two | Khilan   |  25 | Delhi     |  1500.00 | |  3 | kaushik  |  23 | Kota      |  2000.00 | |  4 | Chaitali |  25 | Mumbai    |  6500.00 | |  5 | Hardik   |  27 | Bhopal    |  8500.00 | |  6 | Komal    |  22 | MP        |  4500.00 | |  7 | Muffy    |  24 | Indore    | 10000.00 | +----+----------+-----+-----------+----------+

Following is an example to create a view from the CUSTOMERS tabular array. This view would be used to have customer name and age from the CUSTOMERS table.

SQL > CREATE VIEW CUSTOMERS_VIEW As SELECT proper noun, historic period FROM  CUSTOMERS;

Now, you can query CUSTOMERS_VIEW in a similar way every bit you query an bodily table. Following is an case for the same.

SQL > SELECT * FROM CUSTOMERS_VIEW;

This would produce the following outcome.

+----------+-----+ | name     | age | +----------+-----+ | Ramesh   |  32 | | Khilan   |  25 | | kaushik  |  23 | | Chaitali |  25 | | Hardik   |  27 | | Komal    |  22 | | Muffy    |  24 | +----------+-----+

The WITH Check OPTION

The WITH Bank check OPTION is a CREATE VIEW statement choice. The purpose of the WITH CHECK Option is to ensure that all UPDATE and INSERTs satisfy the condition(south) in the view definition.

If they exercise not satisfy the condition(s), the UPDATE or INSERT returns an error.

The following lawmaking block has an example of creating same view CUSTOMERS_VIEW with the WITH Check Pick.

CREATE VIEW CUSTOMERS_VIEW As SELECT proper name, age FROM  CUSTOMERS WHERE age IS NOT Aught WITH CHECK OPTION;

The WITH Cheque Selection in this case should deny the entry of any NULL values in the view's Age cavalcade, because the view is defined by data that does not have a Zero value in the Historic period column.

Updating a View

A view tin exist updated nether certain conditions which are given below −

The SELECT clause may non contain the keyword DISTINCT.
The SELECT clause may not contain summary functions.
The SELECT clause may not contain fix functions.
The SELECT clause may non contain set operators.
The SELECT clause may non contain an Social club BY clause.
The FROM clause may not comprise multiple tables.
The WHERE clause may not contain subqueries.
The query may not contain Group By or HAVING.
Calculated columns may not be updated.
All Not NULL columns from the base tabular array must be included in the view in order for the INSERT query to function.

So, if a view satisfies all the higher up-mentioned rules then you can update that view. The following code block has an example to update the age of Ramesh.

SQL > UPDATE CUSTOMERS_VIEW    Ready Historic period = 35    WHERE name = 'Ramesh';

This would ultimately update the base of operations table CUSTOMERS and the same would reverberate in the view itself. Now, endeavour to query the base tabular array and the SELECT statement would produce the following consequence.

+----+----------+-----+-----------+----------+ | ID | NAME     | Historic period | ADDRESS   | SALARY   | +----+----------+-----+-----------+----------+ |  one | Ramesh   |  35 | Ahmedabad |  2000.00 | |  two | Khilan   |  25 | Delhi     |  1500.00 | |  three | kaushik  |  23 | Kota      |  2000.00 | |  iv | Chaitali |  25 | Mumbai    |  6500.00 | |  5 | Hardik   |  27 | Bhopal    |  8500.00 | |  6 | Komal    |  22 | MP        |  4500.00 | |  7 | Muffy    |  24 | Indore    | 10000.00 | +----+----------+-----+-----------+----------+

Inserting Rows into a View

Rows of data tin be inserted into a view. The aforementioned rules that apply to the UPDATE command also apply to the INSERT command.

Hither, nosotros cannot insert rows in the CUSTOMERS_VIEW because we have not included all the Not Nix columns in this view, otherwise you can insert rows in a view in a similar way every bit you insert them in a tabular array.

Deleting Rows into a View

Rows of data can be deleted from a view. The aforementioned rules that utilize to the UPDATE and INSERT commands apply to the DELETE command.

Post-obit is an example to delete a tape having AGE = 22.

SQL > DELETE FROM CUSTOMERS_VIEW    WHERE age = 22;

This would ultimately delete a row from the base tabular array CUSTOMERS and the same would reverberate in the view itself. At present, try to query the base of operations table and the SELECT argument would produce the post-obit result.

+----+----------+-----+-----------+----------+ | ID | NAME     | AGE | ADDRESS   | Salary   | +----+----------+-----+-----------+----------+ |  i | Ramesh   |  35 | Ahmedabad |  2000.00 | |  ii | Khilan   |  25 | Delhi     |  1500.00 | |  three | kaushik  |  23 | Kota      |  2000.00 | |  four | Chaitali |  25 | Mumbai    |  6500.00 | |  five | Hardik   |  27 | Bhopal    |  8500.00 | |  seven | Muffy    |  24 | Indore    | 10000.00 | +----+----------+-----+-----------+----------+