SQL Essentials Training & Certification
- 10k Enrolled Learners
- Self Paced
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:
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.
Creating a View is a simple task. Just follow the syntax and know the table contents.
CREATE VIEW view_name AS SELECT column_list FROM table_name [WHERE condition];
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;
You can update a view by following these rules:
UPDATE < view_name > SET<column1>=<value1>,<column2>=<value2>,..... WHERE <condition>;
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.
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;
Now, how to drop the Views in SQL?
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.
DROP VIEW view_name;
Now, let’s see what are the advantages of using the Views in SQL.
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.
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.