AppDividend
Latest Code Tutorials

SQL Views Example | Views In SQL Tutorial Explained In Detail

0

SQL Views Example | Views In SQL Tutorial is today’s topic. SQL views is a kind of virtual tables which have rows and columns as they are in a real database. A 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 a data as if that data were coming from a single table.

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

Consider two tables:

Student:

ID NAME CITY
1 Shubh Kolkata
2 Karan Allahabad
3 Suraj Kota
4 Akash Vizag

 

Marks:

ID NAME MARKS Age
1 Shubh 90 21
2 Rohit 91 21
3 Suraj 92 22
4 Akash 93 22

 

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

NAME CITY
Shubh Kolkata
Karan Allahabad
Suraj Kota
Akash Vizag

 

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

NAME CITY Marks
Shubh Kolkata 90
Suraj Kota 92
Akash Vizag 93

 

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

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

NAME CITY Marks Age
Shubh Kolkata 90 21
Suraj Kota 92 22
Akash Vizag 93 22

 

#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;
NAME CITY
Shubh Kolkata
Karan Allahabad
Suraj Kota
Akash Vizag
Aman Patna

 

We have considered here the view details which was 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;
NAME CITY
Shubh Kolkata
Karan Allahabad
Suraj Kota
Akash Vizag

 

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