We all know that is a Structured Query Language used for storing, updating, manipulating and retrieving data from databases or tables. We can simply say that it is used to communicate with a database. Views in Structured Query Language (SQL) are simply a virtual table which is formed based on results obtained from the SQL Query can be created using both single tables and also with multiple tables. SQL Scope of the Article: In this article we will discuss the views, Views can be created and used as virtual tables. Views can also be updated, deleted, renamed, and inserted in rows. We will discuss the Creation of views with a single table and multiple tables. We will know the different types of views and discuss the uses of Views in SQL. Introduction: As said before, views in SQL are something like a virtual table or we can say it is a logical table which means views don't exist in reality, so not require any space in the database as we said views are tables, Views also have rows and columns same as the original table. Views can be created using the tables in the , we can use one table or more than one table to create the views depending on the condition we want to make the views. database We can specify which rows and which columns should be present in the views, which also depends on the condition we specify. We can store the data in many/multiple tables, but the problem here is retrieving data, which needs the specification of multiple tables. We use these views to avoid using multiple tables and save space in the database. These views manage data very easily from relevant tables quietly. Now let us see the creation of Views. Creation of views in SQL: can be created using the keyword as the syntax. Views in SQL CREATE VIEW Syntax to create a view in SQL: CREATE VIEW viewname AS SELECT column_1, column_2, .....column_n FROM table_1, table_2, .....table_n WHERE some_condition; To retrieve data or to view data present in the created view use the : SELECT statement SELECT * FROM viewname; Let us discuss the syntax to create a view in brief. → Firstly, we start with CREATE VIEW which is the keyword to start the creation of view then we have to keep a name to the view we are creating (viewname). → Next, Select the particular columns from particular tables and then use some conditions. To define them use the WHERE clause. Let us discuss the example to create a view from the employee table. employees Table name: Id Employee_name Age Address Salary 1 Ramu 25 Delhi 2000 2 Sita 23 Mumbai 3000 3 John 36 Hyderabad 15000 4 Raju 27 Bangalore 6500 5 Muffy 22 Bhopal 8500 6 Roshan 32 Maharashtra 8000 7 Komal 28 Kota 2000 8 Mary 25 MP 4500 Creation of view from employees’ table, CREATE VIEW view1 AS SELECT * FROM employees; We created a view that has the same data as the employee’s table, let us check the view using the SELECT statement: SELECT * FROM view1; The result would be: Id Employee_name Age Address Salary 1 Ramu 25 Delhi 2000 2 Sita 23 Mumbai 3000 3 John 36 Hyderabad 15000 4 Raju 27 Bangalore 6500 5 Muffy 22 Bhopal 8500 6 Roshan 32 Maharashtra 8000 7 Komal 28 Kota 2000 8 Mary 25 MP 4500 We can create a view with selected columns as well for example, Creation of view from employees’ table, CREATE VIEW view2 AS SELECT Employee_name, Age FROM employees; Let us check the view using the : SELECT statement SELECT * FROM view2; The result would be: Employee_name Age Ramu 25 Sita 23 John 36 Raju 27 Muffy 22 Roshan 32 Komal 28 Mary 25 Creation of view can also be done using , WITH CHECK OPTION Use of is to ensure update, and insert statements satisfy the conditions in the view creation statement. WITH CHECK OPTION Example of creating same view view2 with , WITH CHECK OPTION CREATE VIEW view3 AS SELECT Employee_name, Age FROM employees WHERE Age IS NOT NULL WITH CHECK OPTION; Here as per the query written, it checks whether Age is null or not and returns Age which is not null i.e., ignores the rows which have Age as NULL. Till now we saw the creation view with a single table, Let us now see the creation of a view with multiple tables. Views can be created using multiple tables, simply using multiple tables in the select statement. Example: Table1 students Id Student_Name Address Branch 1 Ramu Delhi CSE 2 Sita Mumbai ECE 3 John Hyderabad EEE 4 Raju Bangalore CSE 5 Muffy Bhopal IT Table2 marks Id Student_Name Branch Marks 1 Ramu CSE 97 2 Sita ECE 95 3 John EEE 85 4 Raju CSE 77 5 Muffy IT 79 Creation of view from students’ table and marks table, CREATE VIEW marks_view AS SELECT students.Student_Name, students.Address, marks.Marks FROM students, marks WHERE students.Student_Name = marks.Student_Name; Let us check the view using the : SELECT statement SELECT * FROM marks_view; The result would be, Student_Name Address Marks Ramu Delhi 97 Sita Mumbai 95 John Hyderabad 85 Raju Bangalore 77 Muffy Bhopal 79 For managing views, we have different aspects, Creating views Updating views Deleting views Renaming the views Inserting rows and more operations. With these operations, we can easily manage the views. Types of views: System Defined View Information Schema View Catalog View Dynamic Management View User Defined View Simple View Complex View Let us know about the uses of view, Uses of Views in SQL: Views in SQL are used to reduce the usage of tables. A Proper database should contain views for - Restricting data access Storing Complex queries Hiding the data complexity occurred due to usage of multiple tables. Renaming Columns Views in SQL allow the user to select required information from multiple tables without using the . join operation Views in SQL provide additional Security by restricting access. Views hide the complexity which comes from usage of multiple tables. Views have the capability to store the large queries. Renaming columns mean without affecting the original table used to create a view, we can rename columns in view which can be done by specifying in the statement. SELECT We can create different views by using the same table for different reasons or for different users. Views in SQL use ZERO Space in the database, so that our database is free of space, it doesn't copy the data into the database. It can easily update, delete the rows from the view which is called a virtual table. Views help in improving performance and to partition data.
