SQL Views: How to work with Views in SQL?

Any database operations that you perform should have a proper view. Views in SQL are basically the virtual tables. When I say table, it must contain rows and columns. So, this article will help you learn about how to create a view and the different operations that you can perform on them.

The topics discussed in this article are:

• What is a View?
• How to create a View?
• Operations
  • Update
  • Insertion
  • Deletion
  • Drop
• Advantages

Let’s begin!

What is a View?

Views in SQL are the virtual tables. Even these have rows and columns like they are present in the normal database tables. These are the tables through which a selective portion of the data from one or more tables can be viewed.

Views do not contain data of their own. They are mainly used to restrict access to the database or to hide data complexity. A view is stored as a Select statement in the database. A view is based on the DML operations on a view like Insert, Update, Delete affects the data in the original table.

Now, let’s move ahead and understand how to create a View.

How to create a View?

Creating a View is a simple task. Just follow the syntax and know the table contents.

Syntax

CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition];

Here,

view_name is the name of the view and
The select command is used to define the rows and columns.

Now, an example of this would be:

CREATE VIEW view_product
AS
SELECT product_id, product_name
FROM product;

Here, the view_name is product and select product_id and name from the table product.

Creating a View from the Multiple tables

View from multiple tables can be created by simply including multiple tables in the SELECT statement.

CREATE VIEW MarksView
AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

Here, you can select the ViewMarks

Select *From MarksView

Here, Marks, address and name are selected. And, we are going to look for a condition where the MarksName =StudentName, this means the views can be selected. Now to display the data, use the query Select *From MarksView;

Immerse yourself in the world of NoSQL databases with our MongoDB Developer Certification.

Now, let’s move on and understand about the Operations that are performed

Operations

Update

You can update a view by following these rules:

• The view is defined based on one and only one table.
• The view must include the PRIMARY KEY of the table based upon which the view has been created.
• It should not have any field made out of aggregate functions.
• A View must not have any DISTINCT clause in its definition.
• Must not have any GROUP BY or HAVING clause in its definition.
• The view must not have any SUBQUERIES in its definition.
• If the view you want to update is based upon another view, it should be updated later.
• Any of the selected output fields of the view must not use constants, strings or value expressions.

Syntax:

UPDATE < view_name > SET<column1>=<value1>,<column2>=<value2>,.....
WHERE <condition>;

Insertion

Rows of data can be inserted into a View. The same rules that apply to the Update command also apply to the Insert command. You can Insert the views just like you would do in the Database tables.

Deletion

Once you’ve learned how to insert and update the Views in SQL, let’s understand how to delete the views.

Rows of data can be deleted from a view. The same rules that apply to the Update and Insert commands apply to the Delete command.

Example:

Consider you have a table of the list of customers that has ID, name, age, address, and salary. This query here will help you delete a particular row from the table.

SQL > DELETE FROM CUSTOMERS_VIEW
WHERE age = 20;

This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the View itself.

Now, how to drop the Views in SQL?

Drop

Whenever you have a view, it is obvious that you need a way to drop the view if it is no longer needed. The following is the syntax of how to drop a View in SQL.

Syntax:

DROP VIEW view_name;

Just select the View and add this command to drop it.

Now, let’s see what are the advantages of using the Views in SQL.

Advantages

• Security: You can restrict the users to access a table directly and allow them to access a subset of data via views.
• Simplicity: It is many relations and tables.
• Consistency: You can hide the complex queries logic and calculations in views.

With this, we come to the end of this article on Views in SQL. I hope you are clear about the topics discussed in this blog.

You can even check out the details of relational databases, functions, queries, variables, etc with the SQL Course.

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”Views in SQL” and I will get back to you.