AppDividend
Latest Code Tutorials

SQL Views Example | Views In SQL Tutorial Explained

0

SQL view is a kind of virtual table that has rows and columns as they are in a real database. The view can be accompanied with all the rows of a particular table or selected rows based on a certain condition. In Structured Query Language, a view is a virtual table based on the result-set of an SQL statement.

SQL VIEWS

SQL view contains rows and columns, just like the real table. The fields in the view are fields from one or more real tables in the database. You can add the SQL functions, WHERE, and JOIN statements to the view and present data as if that data were coming from a single table.

Let’s learn to create, deleting, and updating views.

Consider two tables:

Student:

IDNAMECITY
1ShubhKolkata
2KaranAllahabad
3SurajKota
4AkashVizag

 

Marks:

IDNAMEMARKSAge
1Shubh9021
2Rohit9121
3Suraj9222
4Akash9322

 

#CREATING VIEWS

Let’s create views using single tables or multiple tables.

#Syntax

Create View View_name AS
Select column(s)
From table_name
Where condition;

#Parameters

  1. View_name: Name of the View.
  2. Column(s): Name of the column.
  3. Table_name: Name of the table.
  4. Where: This is used for specifying some conditions.

Let’s clear this with the help of an example.

#Query: (Creating View With a single table)

Create view Details AS
Select name, city
From Student
Where id>=1;

#Output

Type the following query to get the output.

Select * from Details;

See the below output.

NAMECITY
ShubhKolkata
KaranAllahabad
SurajKota
AkashVizag

 

#Query: (Creating Views from Multiple Tables)

Create View Marks AS
Select Student.Name, Student.City, Marks.Marks
From Student, Marks
Where Student.Name=Marks.Name;

#Output

Fire the following query.

Select * from Marks;

See the output.

NAMECITYMarks
ShubhKolkata90
SurajKota92
AkashVizag93

 

So, until now, we have learned how to create views.

Let’s Learn how to delete views.

#DELETING VIEWS

Views can be removed with the help of the DROP Statement.

#Syntax

Drop VIEW view_name;

See the following parameters.

#Parameters

View_name: This is the name of the View which you want to delete.

#Query

Drop VIEW Details;

So, the View Details will be deleted which we had created above.

So, we have learned how to delete views.

Let’s learn how to update views.

#UPDATING VIEWS

Views can be updated with the help of Create or replace statements.

#SYNTAX:

Create or Replace View View_Name AS
Select column(s)
From table_name
Where condition;

#PARAMETERS:

  1. View_name: Name of the View.
  2. Column(s): Name of the column.
  3. Table_name: Name of the table.
  4. Where: This is used for specifying some conditions.

Let’s clear this with the help of an example.

Suppose, we want to add student Age as well in Marks view then the following queries has to be written.

#QUERY

Create or replace View MARKS AS
Select Student.Name, Student.City, Marks.Marks, Marks.Age
From Student, Marks
Where Student.Name=Marks.Name;

#Output

Select * from MARKS;

See the output.

NAMECITYMarksAge
ShubhKolkata9021
SurajKota9222
AkashVizag9322

 

#Inserting a row in a view.

We can insert values in view as that of we insert into the table.

#Syntax

Insert into view_name (column(s)) VALUES (values);

#Parameters

View_name: This is the name of the View which you want to delete.

#Query

INSERT INTO DETAILS (Name, City) VALUES(‘Aman’,’Patna’);

#Output

Select * from DETAILS;
NAMECITY
ShubhKolkata
KaranAllahabad
SurajKota
AkashVizag
AmanPatna

 

We have considered here the view details which were previously created in CREATE VIEW Query.

#Deleting a row from a view

We can Delete the details from the view using the DELETE statement.

#Syntax

DELETE FROM view_name where Condition;

#Parameters

View_name: This is the name of the View which you want to delete.

#Query

Delete from details where name=”Aman”;

#Output:

Fire the following query.

Select * from Details;
NAMECITY
ShubhKolkata
KaranAllahabad
SurajKota
AkashVizag

 

# SQL Views Keynote:

One should keep in his/her mind to consider these following points before updating the views.

  1. Group by Clause and Order By clause should not be included with the create statement.
  2. DISTINCT keyword should not be present with the select statement.
  3. The view should not contain any NULL values.
  4. The views should not be created with Nested Queries or Complex Queries.
  5. Views should not be created with multiple views.

Finally, SQL Views Example | Views In SQL Tutorial Explained In Detail is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.