SQL view Instance

A view is a set of SQL statements that stores the associated names in the database. A view is actually a form of a table in a predefined SQL query.

A view can contain all the rows of a table, or select rows in a table. Create a view from one or more tables to write a SQL query, and the view can be created.

This is a view of a virtual table that allows the user to do the following:

• The way to discover the structure data of a natural or intuitive user or user class.

• Restricting access to data, for example, users can see and (sometimes) modify exactly what they need and no more.

• Summarize the data from the various tables that can be used to generate reports.

Create a View :

Creates a database view using the CREATE VIEW statement. You can also create a view from a single table, multiple tables, or another view.

To create a view, the user must have appropriate system permissions depending on the implementation.

The basic CREATE VIEW syntax is as follows:

CREATE VIEW  as SELECT column1, Column2 .....  from table_name WHERE [condition];

In a normal SQL select query that is used, you can include multiple tables in a SELECT statement in a very similar manner.

Instance:

Consider the following records in the Customers table:

+----+----------+-----+-----------+----------+|Id|NAME|Age|ADDRESS|SALARY|+----+----------+-----+-----------+----------+|  1 |Ramesh|   + |Ahmedabad|  2000.00 ||  2 |Khilan|   - |Delhi|  1500.00 ||  3 |Kaushik|   at |Kota|  2000.00 ||  4 |Chaitali|   - |Mumbai|  6500.00 ||  5 |Hardik|   - |Bhopal|  8500.00 ||  6 |Komal|   A |Mp|  4500.00 ||  7 |Muffy|   - |Indore| 10000.00 |+----+----------+-----+-----------+----------+

Now, here's an example of creating a view from the Customers table. This view will be used to have the customer's name and age from the Customers table:

> CREATE VIEW  as SELECT name, age  from  CUSTOMERS;

You can now query Customers_view in a similar way as querying an actual table. The following example:

> SELECT *  from Customers_view;

This will produce the following results:

+----------+-----+|Name|Age|+----------+-----+|Ramesh|   + ||Khilan|   - ||Kaushik|   at ||Chaitali|   - ||Hardik|   - ||Komal|   A ||Muffy|   - |+----------+-----+

With CHECK OPTION:

With CHECK option in the view definition is a CREATE VIEW statement option. The purpose of with CHECK option is to ensure that the conditions are met in all update and INSERT statements.

If they do not meet the criteria, update or insert returns an error.

Use with CHECK option to create the same idea Customers_view below is an example:

CREATE VIEW  as SELECT name, age  from   CUSTOMERSWHEREis isn'tNULLwithCHECK OPTION;

Data that does not have a null value in the Age column because the view defines with CHECK option in this case, any null values should be rejected in the view's age column write.

Update View :

You can update a view under certain conditions:

• The SELECT clause does not contain the keyword distinct.

• The SELECT clause does not contain summary functions.

• The SELECT clause does not contain the SET function.

• The SELECT clause does not contain set operators.

• The ORDER BY clause is not included in the SELECT clause.

• The FROM clause can contain more than one table.

• A subquery is not included in the WHERE clause.

• The query does not contain group by or have.

• Computed columns may not be updated.

• The view must be included from the base table so that all the not NULL columns of the INSERT query function.

Therefore, if a view satisfies all of the above rules, you can update the view. Here is an example of updating the age of Ramesh:

> UPDATE Customers_view       SET =  *      WHERE Name='Ramesh';

This will eventually update the base table for the customer, which will also be reflected in the view itself. Now try to query the base table, the SELECT statement will produce the following result:

+----+----------+-----+-----------+----------+|Id|NAME|Age|ADDRESS|SALARY|+----+----------+-----+-----------+----------+|  1 |Ramesh|   * |Ahmedabad|  2000.00 ||  2 |Khilan|   - |Delhi|  1500.00 ||  3 |Kaushik|   at |Kota|  2000.00 ||  4 |Chaitali|   - |Mumbai|  6500.00 ||  5 |Hardik|   - |Bhopal|  8500.00 ||  6 |Komal|   A |Mp|  4500.00 ||  7 |Muffy|   - |Indore| 10000.00 |+----+----------+-----+-----------+----------+

Insert a Line data to view:

Rows of data can be inserted into a view. The same rule applies to the update command as well as to the Insert command.

Here, we can not insert rows because we do not have all of the NOT NULL columns in the view in Customers_view, otherwise you can insert them into the table in a similar way.

Delete data in a view :

The rows of data that can be deleted from the view. The same rules apply to the update and insert commands for the Delete command.

Here is an example of deleting the age = 221 record.

> DELETE  from Customers_view       WHERE = ;

Eventually, this will delete the customers from the base table and the same rows reflected in the view itself. Now try to query the base table, the SELECT statement will produce the following result:

+----+----------+-----+-----------+----------+|Id|NAME|Age|ADDRESS|SALARY|+----+----------+-----+-----------+----------+|  1 |Ramesh|   * |Ahmedabad|  2000.00 ||  2 |Khilan|   - |Delhi|  1500.00 ||  3 |Kaushik|   at |Kota|  2000.00 ||  4 |Chaitali|   - |Mumbai|  6500.00 ||  5 |Hardik|   - |Bhopal|  8500.00 ||  7 |Muffy|   - |Indore| 10000.00 |+----+----------+-----+-----------+----------+

Delete a view :

Obviously, when a discard view is needed, if it is no longer needed. Its syntax is simple, as follows:

DROP VIEW view_name;

The following is an example of discarding customers_view from the Customers table:

DROP VIEW Customers_view;

SQL view Instance