Using Views

Shivani Kumari
Last Updated: May 13, 2022

Introduction

Views are virtual table that contains data from one or multiple tables. It can have rows and columns like the actual table in a database. Based on certain conditions, the view can contain either the whole table or some specific table rows.

Create a view

We use CREATE VIEW statement to create a view. We can create views from single or multiple tables.

CREATE VIEW view_name AS

SELECT column1, column2……..

FROM table_name

WHERE [condition];

Example

We will create a view using the customer table given below in this example. The view will contain the name and age from the customer table.

SQL > CREATE VIEW CUSTOMERS_VIEW AS

SELECT name,age

FROM CUSTOMERS;

 

Like table queries, we can query the view to see the data.

SQL> SELECT * FROM CUSTOMERS_VIEW

 

Output

Update a View

Certain conditions are needed to update a view. If any requirements are not satisfied, the view is not updated.

Conditions:

  1. The view must have all NOT NULL values.
  2. The SELECT statement should not contain the DISTINCT keyword.
  3. The view should not be generated using nested queries or complex queries.
  4. The SELECT clause used to create the view should not include the GROUP BY clause or ORDER BY clause.
  5. We should create the view from a single table. If we create a view using multiple tables, we will not update the view.

To update a view, we use CREATE OR REPLACE VIEW statement. 

CREATE OR REPLACE VIEW view_name AS

SELECTcolumn1, column2,....

FROM table_name

WHERE condition;

 

Example

SQL > UPDATE CUSTOMERS_VIEW

SET AGE = 35

WHERE name = ‘Ramesh’ ;

 

The above statement will ultimately update the base table CUSTOMERS and the changes reflected in the CUSTOMERS_VIEW. We can see the difference by the SELECT statement.

Insert into a View

The same rules to the UPDATE command also apply to the INSERT command. We use INSERT INTO statement to insert a row in a view.

Syntax

INSERT INTO view_name(column1, coulmn2, column3,..)

VALUES(value1, value2, value3..);

 

Example

SQL > INSERT INTO CUSTOMERS_VIEW(ID, NAME, AGE,ADDRESS, SALARY)

VALUES(8,’Rahul’,26,’Banglore’, 15000);

 

Delete in a View

Syntax

DELETE FROM view_name

WHERE condition;

 

Example

In this example, we will delete records having AGE = 22

SQL > DELETE FROM CUSTOMERS_VIEW

         WHERE age = 22;

 

We can use the SELECT statement to see the updated CUSTOME_VIEW.

The WITH CHECK OPTION

The use of the WITH CHECK OPTION ensures that all UPDATE and INSERTs will satisfy the conditions in the view definition.

If they do not satisfy the conditions, the UPDATE or INSERT returns an error.

Example

In this example, the WITH CHECK OPTION will prevent the entry of any NULL values in the view's AGE column because the view is defined such that there is no NULL value in the age column.

CREATE VIEW CUSTOMERS_VIEW AS

SELECT name,age

FROM CUSTOMERS

WHERE age is NOT NULL

WITH CHECK OPTION;

Drop a view

We can delete a view using the DROP VIEW statement followed by the view name.

Syntax

DROP VIEW view_name;

Reasons to use views:

Views are primarily used for the following reasons.

  1. SQL knowledge is required to create applications, which use a database for handling data. Views of the original table should be used in the application instead of using the tables themselves. In this way, when we refactor our database, our legacy code will see the original schema via the view without breaking the application.
  2. Views enhance reusability. We have not to write complex queries using joins repeatedly. Views convert all the complexity in a single line of query. Shorted and concise code are easier to integrate into our application. This will eliminate the chances of typos, and your code will be more readable.
  3. Views help in data security. We can use views to show only authorized information to users and hide sensitive data like credit card numbers.

Frequently Asked Questions

Q1. What are the uses of views?

Views are used for restricting data access and hiding data complexity. It also simplifies commands for the user. It allows users to select information from multiple tables without requiring them to know how to perform a join.

Q2. What are the disadvantages of views?

Views are virtual tables. So, it is not possible to create an index on views.

Q3.What are the types of views in DBMS?

Static views and dynamic views are two types of views present in DBMS. Dynamic views contain data from one or more tables and have all the columns from the specified tables. When related objects or extended objects are created or changed, dynamic views are automatically updated.

Key Takeaways

We learned about creating, updating, and deleting views in this article. We have also known about the conditions required for updating views. We also discussed WITH CHECK OPTION and why views are important to use.

Visit here to learn more about different topics related to database and management systems. Ninjas don’t stop here check out the Top 100 SQL Problems to master frequently asked questions in big companies and land your dream job. Also, try  CodeStudio to practice a wide range of DSA questions asked in lots of interviews.

Was this article helpful ?
1 upvote