SQL VIEWs
Virtual Tables
What.... Why.... How....
SQL VIEWs
A view is a “virtual table”
Creating views (access to data) as per the requirements for
different set of users
https://ecomputernotes.com/fundamental/what-is-a-database/advantages-and-disadvantages-of-dbms
SQL VIEWs
A view is a “virtual table”
1. SQL View is like a window through which we see a limited section of the actual table or we
give access to a certain section of the table to a database user. e.g. allowing a user to see or
modify only contact information about the students, the other fields will be hidden from
the user.
1. SQL Views are seen as virtual tables. Views do not contain data of their own. To the user,
the view appears like a table with columns and rows but in reality, the SQL VIEW doesn’t
exists in the database as a stored set of values like a table.
2. The rows and columns that we find in a view are actually the results generated by a SQL
SELECT query that defines the view. The table from where the data is obtained is called the
source table
3. VIEWs are used to restrict access to the database (security purpose) or to hide data
complexity (simplicity in viewing required data).
4. A view is a database object like a table and stored as a SELECT statement in the database.
5. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table
upon which the view is based.
SQL VIEWs
A view is a “virtual table”
Different Table Views (windows) created by partitioning the table horizontally, vertically, etc..
Database VIEW : create view and then display information
Create View view1
Selecting records from View
Select * from view1
SQL VIEWs
A view is a “virtual table”
View_IT View_MECH
SQL VIEWs
A view is a “virtual table”
Emp_tab
• We can use views in select
statements like
View_developer
• Select * from view_developer
where age > 23;
• DBMS translates the request to an
equivalent request to the source
table
SQL VIEWs
A view is a “virtual table”
Creating SQL VIEW
• Creating a VIEW : General Syntax
CREATE VIEW ViewCustomerDetails
AS SELECT * FROM Customer_Details;
• Name of VIEW : ViewCustomerDetails
• Content : All records from table Customer_Details
• Attribute names for VIEW are optional, if not mentioned, the
attribute names from the source table are copied as it is.
SQL VIEWs
A view is a “virtual table”
SQL VIEW Types
• Horizontal views
• Vertical views
• Row/column subset views
• Grouped views
• Joined views
SQL VIEWs
A view is a “virtual table”
Horizontal VIEW
SQL VIEWs
A view is a “virtual table”
Vertical view
SQL VIEWs
A view is a “virtual table”
Row/column Subset View
SQL VIEWs
A view is a “virtual table”
Group BY based VIEW
SQL VIEWs
A view is a “virtual table”
SQL JOIN based VIEW
SQL VIEWs
A view is a “virtual table”
A view is updatable if :
• DISTINCT is not specified in the query used to create the view
• The FROM clause specifies only one source table
• The select list doesn’t contain expressions/calculated columns
• The WHERE clause doesn’t include a subquery
• The query doesn’t include a GROUP BY or HAVING
SQL VIEWs
A view is a “virtual table”
Advantages and Disadvantages
• Advantages of views
– Security
– Query simplicity
– Structural simplicity
• Disadvantages of views
– Performance
– Restrictions
