Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
Views in SQL
● Views in SQL are considered as a virtual table. A view also contains rows and columns.
● To create the view, we can select the fields from one or more tables present in the database.
● A view can either have specific rows based on certain condition or all the rows of a table.
Sample table:
Student_Detail
STU_ID NAME ADDRESS
1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram
Student_Marks
STU_ID NAME MARKS AGE
1 Stephan 97 19
2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
5 John 96 18
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV [Link] Mangore
Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view from a single table
or multiple tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
2. Creating View from a single table
n this example, we create a View named DetailsView from the table Student_Detail.
Query:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;
Just like table query, we can query the view to view the data.
SELECT * FROM DetailsView;
3. Creating View from multiple tables
View from multiple tables can be created by simply include multiple tables in the SELECT statement.
In the given example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.
Query:
CREATE VIEW MarksView AS
SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;
To display data of View MarksView:
SELECT * FROM MarksView;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV [Link] Mangore
Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
4. Deleting View
A view can be deleted using the Drop View statement.
Syntax
DROP VIEW view_name;
Example:
If we want to delete the View MarksView, we can do this as:
DROP VIEW MarksView;
SQL Views – Advantages & Disadvantages
Like stored procedures, SQL views also have a number of advantages. I’ll try to list the most important ones here:
● Security – I’ll put security in the first place because, similarly to procedures, you can define who can use a
view and how. That same user doesn’t have access to tables used in the view, but only to the view. This way,
you can protect sensitive details stored in the table and expose only the ones you want the user to see
● Easy to use (for the end-user) – While you might know how to write cool and complex queries, most
business users are not interested in that. They just want to get the data. Putting your complex query in the
view and allowing business users to use the view, shall hide the complexity of the query and return only the
columns they need. You’ll use views as a way how to store your complex code. Also, be aware that you should
name your views consistently and logically, so anyone can understand what the view does, simply from its’
name
● Following business rules & consistency of business logic – This is related to the previous bullet. If you
have specific reports, business users need, you can create a SQL view for every single report. All who need a
certain number can simply run this view. If something changes in the reporting requirements, you’ll simply
change the view, and all who use it shall immediately feel the effect of that change
● Use them to make database changes – Imagine a situation where you want to remove the table, replace it
with few tables, or simply changing a table name. In case you do that, there is a great chance you’ll mess up
the code somewhere, where this table was used. If you want to prevent that, you could create a view with the
same name as the old table had. While this is a fix, this could prove to spare a lot of time
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV [Link] Mangore
Chapter : 4
Structured Query Language (SQL)
-----------------------------------------------------------------------------------------------------------------------------------------
● Views don’t take space – Views are used to store your code, not complete tables. Each time you call a view,
you’ll run the related query. Therefore, you don’t lose disk space on views
It would be great that we have only advantages, but as it’s usually the case with the most things in life, views also
come with some disadvantages:
● Database changes & views – If you remove an attribute used in the view, the view won’t work. That is the
same thing as if you’re trying to run a query using the name of the non-existing column. This is not a big deal if
you’re using views only for reporting, because end users will pass the info that their report is not working as
expected. In case you’re combining views with insert, update, or delete (some DBMSs allow that) operations,
you’ll have a bigger issue
● Performance – This could theoretically be a problem because business/end users are usually not aware (and
there is no reason why they should be) of what you did. If the query stored in the SQL view is complex and/or
not-optimized, it will use a lot of resources and time, and this will lead to all possible issues long queries can
cause. We’ll talk more about that later in the series. Still, a business user has no idea of that and could be
confused or try to use your view multiple times, etc.
-----------------------------------------------------------------------------------------------------------------------------------------------------
Subject : DBMS Class : SE SEM: IV [Link] Mangore