ORACLE SQL PLSQL Views Training Institutes in Kukatpally Hyderabad

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.

Advertisement

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

  1. CREATE VIEW View_Name AS
  2. SELECT Column_Name1, Column_Name2, ....., Column_NameN
  3. FROM Table_Name
  4. 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.

Advertisement
  1. REATE VIEW View_Name AS
  2. SELECT Table_Name1.Column_Name1, Table_Name1.Column_Name2, Table_Name2.Column_Name2, ....., Table_NameN.Column_NameN
  3. FROM Table_Name1, Table_Name2, ....., Table_NameN
  4. 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:

  1. CREATE VIEW Student_View AS
  2. SELECT Student_ID, Stu_Subject, Stu_Marks
  3. FROM Student_Details
  4. WHERE Stu_Marks > 85;
  1. 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

Advertisement
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.

  1. CREATE VIEW Student_Teacher_View AS
  2. SELECT Student_Details.Student_ID, Student_Details.Stu_Name, Teacher_Details.Teacher_ID, Teacher_Details.Teacher_Subject
  3. FROM Student_Details, Teacher_Details
  4. 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:

  1. 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

  1. CREATE OR REPLACE VIEW View_Name AS
  2. SELECT Column_Name1, Column_Name2, ....., Column_NameN
  3. FROM Table_Name
  4. 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:

  1. CREATE OR REPLACE VIEW Student_View  AS
  2. SELECT Student_ID, Stu_Name, Stu_Subject, Stu_Marks
  3. FROM Student_Details
  4. 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:

  1. 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:

  1. 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:

  1. 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:

  1. 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:

  1. 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:

  1. 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:

  1. 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

Advertisement

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:

Advertisement
  1. 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:

  1. DROP VIEW Student_View;
Next TopicConstraints in SQL