SQL uses views

SQL uses views

---

A view is nothing more than a SQL statement stored in a database with a name, or an component of a data table that exists in the form of a predefined SQL query.

A view can contain all the columns in the table, or only the selected columns. A view can be created from one or more tables, depending on how the SQL statement that created the view is written.

A view, a virtual table that allows users to do the following:

• Organize data in a way that users or certain types of users feel natural or intuitive;
• Restrict access to data so that users can only see or modify (in some cases) the data they need;
• Summarize data from multiple tables to produce reports.

Create a view

---

In SQL, a visual table that is based on the result set of SQL statements.

The database view is created by the CREATE VIEW statement. Views can be created from a single table, multiple tables, or other views.

Fields in a view are fields in a real table in one or more databases.

Views can be considered a "virtual table" when used.

To create a view, the user must have the appropriate system permissions. What permissions are required varies depending on the implementation of the database system.

The basic syntax of the CREATE VIEW statement is as follows:

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

As with normal SQL SELECT queries, you can include multiple data tables in the SELECT statement above.

Note: Views always show the latest data! Whenever a user queries a view, the database engine rebuilds the data using the view's SQL statement.

SQL CREATE VIEW example

---

Example one

Consider the CUSTOMERS table, which shows the following:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | 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 |
+----+----------+-----+-----------+----------+

The following is an example of creating a view from a CUSTOMERS table. This view contains the customer's name and age (age) from the CUSTOMERS table:

SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS;

Now you can query the data table as if it were a normal CUSTOMERS_VIEW data:

SQL > SELECT * FROM CUSTOMERS_VIEW;

The above statement will produce the following running results:

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

Example 2

The following is an example of creating a view from a CUSTOMERS table. This view contains ADDRESS information from customers with an age of 25 in the CUSTOMERS table:

SQL > CREATE VIEW CUSTOMERS_ADDRESS AS
SELECT ADDRESS
FROM  CUSTOMERS;
WHERE AGE=25;

We can query this view above CUSTOMERS_ADDRESS like this:

SQL > SELECT * FROM CUSTOMERS_ADDRESS;

We can also add conditions to the query. N ow, we just need to look at "Delhi" data:

SELECT * FROM CUSTOMERS_ADDRESS
WHERE ADDRESS='Delhi';

WITH CHECK OPTION

---

WITH CHECK OPTION is an option for CREATE VIEW statements.

WITH CHECK OPTION is used to ensure that all UPDATE and INSERT statements meet the criteria in the view definition.

If these conditions are not met, UPDATE or INSERT returns an error.

The following example also creates CUSTOMERS_VIEW view, but this time WITH CHECK OPTION is open:

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

Here WITH CHECK OPTION causes the view to reject any entry where the AGE field is NULL because the AGE field cannot be empty in the definition of the view.

Update the view

---

DML statements that modify data, such as INSERT, UPDATE, and DELETE, can also be used on SQL views.

Views can be updated under certain circumstances:

• The SELECT clause cannot contain the DISTINCT keyword
• Select clauses cannot contain any summary functions (summary functions)
• Select clause cannot contain any set functions (set functions)
• Select clauses cannot contain any set operators (set operators)
• Select clauses cannot contain ORDER BY clauses
• The view cannot contain a connection operator
  
• Views cannot contain pseudo-columns or expressions
  
• There cannot be more than one data table in the FROM clause
• Where clause cannot contain subquery
• There can be no GROUP BY or HAVING in the query statement
• The calculated columns cannot be updated
• The view must contain all the NOT NULL columns in the original data table for the INSERT query to take effect.

If the view meets all of the above criteria, the view can be updated. In the following example, Ramesh's age has been updated:

SQL > UPDATE CUSTOMERS_VIEW
      SET AGE = 35
      WHERE name='Ramesh';

The original data table was eventually updated, but the results were reflected in the view. Now querying the original data table, the SELECT statement will produce the following results:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | 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 |
+----+----------+-----+-----------+----------+

Insert a new row into the view

---

You can insert a new row into a view with the same rules that you follow (using the UPDATE command) to update the view.

Here we can'CUSTOMERS_VIEW rows to the new view because the view doesn't contain all the NOT NULL columns in the original data table. Otherwise, you can insert a new row into the view as if you were inserting a new row into the data table.

Syntactic:

INSERT INTO view_name
VALUES (value1, value2, value3, ...);

Delete the rows in the view

---

The rows of data in the view can be deleted. Removing data rows follows the same rules as updating the view and inserting new rows into the view.

The following example removes CUSTOMERS_VIEW rows of AGE=22 in the view:

SQL > DELETE FROM CUSTOMERS_VIEW
      WHERE age = 22;

The statement eventually deletes the corresponding row of data from the original data table, except that the results are reflected in the view. Now querying the original data table, the SELECT statement will produce the following results:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Delete the view

---

Obviously, when we no longer need a view, there needs to be a way for us to delete it. The syntax for deleting a view is simple, as follows:

DROP VIEW view_name;

The following example shows how to remove a view CUSTOMERS_VIEW customers table:

DROP VIEW CUSTOMERS_VIEW;