19th Oct - SQL View

SQL provides the concept of VIEW, which hides the complexity of the data and restricts unnecessary access to the database. It permits the users to access only a particular column rather than the whole data of the table.

The View in the Structured Query Language is considered as the virtual table, which depends on the result-set of the predefined SQL statement.

Like the SQL tables, Views also store data in rows and columns, but the rows do not have any physical existence in the database.

Any database administrator and user can easily create the View by selecting the columns from one or more database tables. They can also delete and update the views according to their needs.

A view can store either all the records of the table or a particular record from the table using the WHERE clause.

Create a SQL View

You can easily create a View in Structured Query Language by using the CREATE VIEW statement. You can create the View from a single table or multiple tables.

Syntax to Create View from Single Table

CREATE VIEW View_Name AS SELECT Column_Name1, Column_Name2, ....., Column_NameN FROM Table_Name WHERE condition;

In the syntax, View_Name is the name of View you want to create in SQL. The SELECT command specifies the rows and columns of the table, and the WHERE clause is optional, which is used to select the particular record from the table.

Syntax to Create View from Multiple Tables

You can create a View from multiple tables by including the tables in the SELECT statement.

REATE VIEW View_Name AS SELECT Table_Name1.Column_Name1, Table_Name1.Column_Name2, Table_Name2.Column_Name2, ....., Table_NameN.Column_NameN FROM Table_Name1, Table_Name2, ....., Table_NameN WHERE condition;

Example to Create a View from Single table

Let's consider the Student_Details table, which consists of Stu_ID, Stu_Name, Stu_Subject, and Stu_Marks columns. The data of the Student_Details is shown in the following table:

Student_ID	Stu_Name	Stu_Subject	Stu_Marks
1001	Akhil	Math	85
1002	Balram	Science	78
1003	Bheem	Math	87
1004	Chetan	English	95
1005	Diksha	Hindi	99
1006	Raman	Computer	90
1007	Sheetal	Science	68

Table: Student_Details

Suppose, you want to create a view with Stu_ID, Stu_Subject, and Stu_Marks of those students whose marks are greater than 85. For this issue, you have to type the following query:

CREATE VIEW Student_View AS SELECT Student_ID, Stu_Subject, Stu_Marks FROM Student_Details WHERE Stu_Marks > 85;

Select * FROM Student_View;

Output:

Student_ID	Stu_Subject	Stu_Marks
1001	Math	85
1003	Math	87
1004	English	95
1005	Hindi	99
1006	Computer	90

View: Student_View

Example to Create a View from Multiple tables

Let's consider two tables, Student_Details and Teacher_Details. The Student_Details table consists of Stu_ID, Stu_Name, Stu_Subject, and Stu_Marks columns. And, the Teacher_Details table consists of Teacher_ID, Teacher_Name, Teacher_Subject, Teacher_City columns. The data of the Student_Details and Teacher_Details is shown in the following two tables:

Student_ID	Stu_Name	Stu_Subject	Stu_Marks
1001	Akhil	Math	85
1002	Balram	Science	78
1003	Bheem	Math	87
1004	Chetan	English	95
1005	Diksha	Hindi	99
1006	Raman	Computer	90
1007	Sheetal	Science	68

Table: Student_Details

Teacher_ID	Teacher_Name	Teacher_Subject	Teacher_City
2001	Arun	Math	Gurgaon
2002	Manoj	Science	Delhi
2003	Reena	SST	Noida
2004	Parul	English	Gurgaon
2005	Nishi	Hindi	Noida
2006	Anuj	Computer	Delhi
2007	Ram	Physical Education	Delhi

Table: Teacher_Details

Suppose, you want to create a view with Stu_ID, Stu_Name, Teacher_ID, and Teacher_Subject columns from the Student_Details and Teacher_Details tables.

CREATE VIEW Student_Teacher_View AS SELECT Student_Details.Student_ID, Student_Details.Stu_Name, Teacher_Details.Teacher_ID, Teacher_Details.Teacher_Subject FROM Student_Details, Teacher_Details WHERE Student_Details.Stu_Subject = Teacher_Details.Teacher_Subject;

To display the data of Student_Teacher_View, you have to type the following SELECT query:

Select * FROM Student_Teacher_View;

Output:

Student_ID	Stu_Name	Teacher_ID	Teacher_Subject
1001	Akhil	2001	Math
1002	Balram	2002	Science
1004	Chetan	2004	English
1005	Diksha	2005	Hindi
1006	Raman	2006	Computer

View: Student_Teacher_View

Update an SQL View

We can also modify existing data and insert the new record into the view in the Structured Query Language. A view in SQL can only be modified if the view follows the following conditions:

1. You can update that view which depends on only one table. SQL will not allow updating the view which is created more than one table.
2. The fields of view should not contain NULL values.
3. The view does not contain any subquery and DISTINCT keyword in its definition.
4. The views cannot be updatable if the SELECT statement used to create a View contains JOIN or HAVING or GROUP BY clause.
5. If any field of view contains any SQL aggregate function, you cannot modify the view.

Syntax to Update a View

CREATE OR REPLACE VIEW View_Name AS SELECT Column_Name1, Column_Name2, ....., Column_NameN FROM Table_Name WHERE condition;

Example to Update a View

If we want to update the above Student_View and add the Stu_Name attribute from the Student table in the view, you have to type the following Replace query in SQL:

CREATE OR REPLACE VIEW Student_View AS SELECT Student_ID, Stu_Name, Stu_Subject, Stu_Marks FROM Student_Details WHERE Stu_Subject = 'Math';

The above statement updates the existing Student_View and updates the data based on the SELECT statement.

Now, you can see the virtual table by typing the following query:

SELECT * FROM Student_View;

Output:

Student_ID	Stu_Name	Stu_Subject	Stu_Marks
1001	Akhil	Math	85
1003	Bheem	Math	87

View: Student_View

Insert the new row into the existing view

Just like the insertion process of database tables, we can also insert the record in the views. The following SQL INSERT statement is used to insert the new row or record in the view:

INSERT INTO View_Name(Column_Name1, Column_Name2 , Column_Name3, ....., Column_NameN) VALUES(value1, value2, value3, ...., valueN);

Example to Insert new record in the view

Suppose, you want to insert the record of a new student in the Student_View, then you have to write the following query in SQL:

INSERT INTO Student_View (Student_ID, Stu_Subject, Stu_Marks) VALUES (1007, Hindi, 89);

Now, you can check that the new record is inserted in the Student_View or not by using the following query:

SELECT * FROM Student_View;

Output:

Student_ID	Stu_Subject	Stu_Marks
1001	Math	85
1003	Math	87
1004	English	95
1005	Hindi	99
1006	Computer	90
1007	Hindi	89

View: Student_View

Delete the existing row from the view

Just like the deletion process of database tables, we can also delete the record from the views. The following SQL DELETE statement is used to delete the existing row or record from the view:

DELETE FROM View_Name WHERE Condition;

Example to Delete the record from the View

Suppose, you want to delete the record of those students from the Student_View whose Subject is Math, then you have to type the following SQL query:

DELETE FROM Student_View WHERE Stu_Subject = 'Math' ;

Now, you can check that the record is removed from the Student_View or not by using the following query:

SELECT * FROM Student_View;

Output:

Student_ID	Stu_Subject	Stu_Marks
1004	English	95
1005	Hindi	99
1006	Computer	90
1007	Hindi	89

View: Student_View

Drop a View

We can also delete the existing view from the database if it is no longer needed. The following SQL DROP statement is used to delete the view:

DROP VIEW View_Name;

Example to Drop a View

Suppose, you want to delete the above Student_View, then you have to type the following query in the Structured Query Language:

DROP VIEW Student_View;

---

Next Topic#

Adblock test (Why?)

You may be interested in:
>> Is a Chromebook worth replacing a Windows laptop?
>> Find out in detail the outstanding features of Google Pixel 4a
>> Top 7 best earbuds you should not miss

Related Posts:
>> Recognizing 12 Basic Body Shapes To Choose Better Clothes
>>Ranking the 10 most used smart technology devices
>> Top 5+ Best E-readers: Compact & Convenient Pen