EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL Views

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL Views

SQL Views

Introduction to SQL Views

Views in SQL are defined as those result sets that work as a stored query on data, and acts as a pre-established query command which is stored by the SQL server in the database dictionary. A view representing a subset of the data present in a table, and performing functions such as joining and simplification of multiple tables into a single table, data aggregation, handling data complexity, providing security etc., The most important aspect being that they taking very minimal memory as a storage space.

Syntax to Create View

A View can be created using CREATE VIEW statement as below:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

CREATE VIEW VIEW_NAME AS
SELECT column1, column2, column3.......
FROM table_name WHERE [condition];

Creating a view in SQL with Examples:

Let us consider the below table EMPLOYEE:

SQL Views-1

We can create a view from the above table as below.

CREATE VIEW EMLOYEE_VW AS
SELECT NAME, AGE, SALARY
FROM EMPLOYEE;

In the above view EMPLOYEE_VW, the below columns will be created.

SELECT * FROM EMPLOYEE_VW;

SELECT * FROM EMPLOYEE_VW; -2

We can create views from more than one table.

Let us consider another table DEPARTMENT as below.

SQL Views-3

We can create a view from the above EMPLOYEE and DEPARTMENT tables as below:

CREATE VIEW DEPT_VIEW AS
SELECT EMPLOYEE.ID, EMPLOYEE.NAME, DEPARTMENT.DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.ID = DEPARTMENT.ID;

The view DEPT_VIEW will have the below result.

SELECT * FROM DEPT_VIEW;

SELECT * FROM DEPT_VIEW;-4

Different view Operations in SQL Views

Below are various view operations are as follows:

Update in View

Though a view can be updated, we need to keep a few conditions in the notice. Such as, while updating a view the select statement should not contain a DISTINCT keyword, set functions, order by clause, Group By or Having, sub-queries, etc. Also, the FROM clause should not contain multiple tables. In addition to the above, the view should have NOT NULL values if it needs to be updated. So when we want to update the view EMPLOYEE_VW keeping the above points in focus, the table EMPLOYEE will be updated.

CREATE OR REPLACE VIEW statement is used to add or remove fields from a view.

SYNTAX for Update

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2....
FROM table_name
WHERE [condition];

Let us update the view DEPT_VIEW as below to add the location column.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,636 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

CREATE OR REPLACE VIEW DEPT_VIEW AS
SELECT EMPLOYEE.ID, EMPLOYEE.NAME, DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.ID = DEPARTMENT.ID;

SELECT * FROM EMPLOYEE;

From the above query, we will get the below result.

SELECT * FROM EMPLOYEE;-5

INSERTING into VIEW

Syntax to insert into a view

INSERT INTO view_name(column1, column 2, column3,....) VALUES(value1, value2, value3,...);

A row can be inserted to view EMPLOYEE_VW by using insert into a statement as below:

INSERT INTO EMPLOYEE_VW (NAME, AGE, SALARY) VALUES(‘RAM’, ‘24’, ‘27000.00’);

After insertion, we can see the result by the below select query.

SELECT * FROM EMPLOYEE_VW;

SELECT * FROM EMPLOYEE_VW; -6

DELETING FROM A VIEW

Deleting a row from a view deletes the row from the table on which the view was created.

The syntax for deleting from a view

DELETE FROM view_name WHERE [condition];

We can delete the row from view as below:

DELETE FROM EMPLOYEE_VW WHERE NAME = ‘MALAY’;

After deletion, the result can be displayed by the below query.

SELECT * FROM EMPLOYEE_VW;

SQL Views -7

DROPPING A VIEW

The views can be dropped by using the below syntax:

DROP VIEW view_name;

If we want to delete the view EMPLOYEE_VW, it can be deleted as below:

DROP VIEW EMPLOYEE_VW;

Advantages and Disadvantages of  SQL Views

Below are some pros and cons as follows:

Advantages

Below are the advantages of using views:

  • If we need to maintain any sensitive information by providing limited access to the users, views are used for that purpose. Views are used to only display the required data to the users by keeping sensitive data safe.
  • As a database view is associated with many tables upon which the view is created, it simplifies the complexity of the query.
  • The view is used to hide the complexity of the underlying tables used in a database from the end-users.
  • Views are useful in case of re-designing the database so as not to affect any other applications using the same database.
  • The data of the computed columns can be calculated very easily when we query the data from the view, as views enable computed columns.

Disadvantages

Despite the many advantages that the views offer, it still has some disadvantages stated as below:

  • One of the major disadvantages of using view comes into the picture when we change the table structures frequently upon which the view is created. So when the table structures are changed, the view also needs to be changed.
  • Also, the usage of view slows down the performance of the queries.

Conclusion

Views are widely used for their many advantages. They add an extra security layer to the database which is very essential for any relational database management system. Views are flexible in case of exposure of the data to the end-users by showing only the data necessary for example using read-only views to limit the privileges to the users. But also views can be disadvantageous if the underlying table structures change much frequently thereby increasing the complexity of changing the views according to the table structures.

So with many advantages in the picture, views come with a bit less recommended when the performance of the query of data is vital to the business. It depends upon us to choose the usage of view in our database by validating the business requirement properly so as to gain more advantages from views in order to increase the performance of the system.

Recommended Articles

This has been a guide to SQL Views. Here we have discussed Creating a view in SQL and Syntax to Create View along with the Advantages and Disadvantages. You can also go through our other suggested articles to learn more –

  1. Oracle PL/SQL Interview Questions
  2. What is MySQL Schema?
  3. What is SQL Injection?
  4. What is T-SQL?
  5. Learn the INSERT Statement in Oracle
  6. Different View Options in MySQL

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

2 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQLAlchemy Filter
    • SQLAlchemy create_engine
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Server Versions
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite?functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • NoSQ
    • NoSQL Databases List
    • NoSQL Injection
    • NoSQL vs SQL Databases
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More