How to Create, Update, Insert and Delete SQL Views?
This article will give you a detailed insight on SQL views. You will learn the method to create, update, insert, drop and delete SQL views.
Databases often have multiple tables interrelated to each other. But if a user wants to view data from multiple tables, then it gets complicated. To resolve the same, SQL Views were introduced. SQL Views create virtual tables for the users to view data and perform operations. In this article, you will get a detailed walkthrough of what views are and how to use them.
The following topics are covered in this article:
- What are SQL Views?
- How to create a View?
- SQL View Operations:
In this article, I am going to consider the following table to explain to you the operators in SQL.
Patients Table
| Patient ID | Patient Name | Sex | Age | Address | Postal Code | State | Country | Haemoglobin |
| 01 | Sheela | F | 23 | Flat no 201, Vasavi Heights, Yakutapura | 500023 | Telangana | India | 11.321 |
| 02 | Rehan | M | 21 | Building no 2, Yelahanka | 560063 | Karnataka | India | 13.231 |
| 03 | Anay | M | 56 | H No 1, Panipat | 132140 | Haryana | India | 94.567 |
| 04 | Mahira | F | 42 | House no 12, Gandhinagar | 382421 | Gujarat | India | 78.567 |
| 05 | Nishant | M | 12 | Sunflower Heights, Thane | 400080 | Maharashtra | India | 65.234 |
CheckupDetails Table
| Patient ID | AnnualCheckupMonth | Fees |
| 01 | Feb2022 | 6700 |
| 02 | Apr2022 | 8900 |
| 03 | Feb2022 | 6700 |
| 04 | Apr2022 | 8900 |
Let us get started with what views are.
What are SQL Views?
SQL Views as mentioned above are virtual tables used to view data from one or more tables. Each of the views consists of rows and columns. Views do not contain their own data and are used to limit data access to the 3rd party users.
Next, let us understand how to create views in SQL
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
How to create a View?
You can use the following syntax to create a view.
Syntax:
CREATE VIEW ViewNameASSELECT Column1, Column2, ..., ColumnNFROM TableNameWHERE [conditions];
Creating views from a single table:
CREATE VIEW PatientsView AS SELECT PatientID, PatientName, Haemoglobin FROM Patients;
Output:
| Patient ID | Patient Name | Haemoglobin |
| 01 | Sheela | 11.321 |
| 02 | Rehan | 13.231 |
| 03 | Anay | 94.567 |
| 04 | Mahira | 78.567 |
Check out more articles on SQL
Creating SQL views from multiple tables
AS SELECT Patients.PatientID, Patients.PatientName, Patients.Hemoglobin, CheckupDetails.AnnualCheckupMonth FROM Patients, CheckupDetailsWHERE Patients.PatientID = CheckupDetails.PatientID;
Output:
| Patient ID | Patient Name | Haemoglobin | AnnualCheckupMonth |
| 01 | Sheela | 11.321 | Feb2022 |
| 02 | Rehan | 83.231 | Apr2022 |
| 03 | Anay | 94.567 | Feb2022 |
| 04 | Mahira | 78.567 | Apr2022 |
Next, let us understand the various data operations we can perform with SQL Views.
SQL View Operations
In this section of the article, we will cover the following operations that can be performed on views.
- Update a View
- Insert a View
- Delete a View
- Drop a View
Update a View
To update a view you must remember that
- Every view must include a primary key of the table
- It is preferable not to use subqueries while views
- DISTINCT, GROUP BY and HAVING clauses cannot be used while defining a view
- No field made out of aggregate functions can be included
Syntax:
UPDATE ViewName SET Column1=Value1,Column2=Value2,.....,ColumnN=ValueNWHERE [Condition];
Example:
UPDATE PatientsView SET PatientName = SahitiWHERE PatientID =1;
Output:
| Patient ID | Patient Name | Haemoglobin |
| 01 | Sahiti | 11.321 |
| 02 | Rehan | 83.231 |
| 03 | Anay | 94.567 |
| 04 | Mahira | 78.567 |
Insert a View
To insert records in a view you must remember that:
- Every view must include a primary key of the table
- It is preferable not to use subqueries while views
- DISTINCT, GROUP BY and HAVING clauses cannot be used while defining a view
- No field made out of aggregate functions can be included
Syntax:
INSERT INTO ViewName (Column1, Column2,..., ColumnN) VALUES (Value1,Value2,..., ValueN);
Example:
INSERT INTO PatientsView (PatientID, PatientName, Haemoglobin)VALUES (‘05’,’Suhana’, ‘56.12’);
Output:
| Patient ID | Patient Name | Haemoglobin |
| 01 | Sheela | 11.321 |
| 02 | Rehan | 83.231 |
| 03 | Anay | 94.567 |
| 04 | Mahira | 78.567 |
| 05 | Suhana | 56.12 |
Check out more articles on MySQL
Delete a View
To insert delete records from a view you must remember that
- Every view must include a primary key of the table
- It is preferable not to use subqueries while views
- DISTINCT, GROUP BY and HAVING clauses cannot be used while defining a view
- No field made out of aggregate functions can be included
Syntax:
DELETE FROM ViewNameWHERE [Condition];
Example:
DELETE FROM PatientsView WHERE PatientID = ‘01’;
Output:
| Patient ID | Patient Name | Haemoglobin |
| 02 | Rehan | 83.231 |
| 03 | Anay | 94.567 |
| 04 | Mahira | 78.567 |
Explore Free Online Courses with Certificates
Drop a View
The drop command is used to delete the view including its data and structure.
Syntax:
DROP VIEW ViewName
Example:
DROP VIEW PatientsView
With this, we end this article on SQL Views. We hope you found it informative. You can refer to the article on popular MySQL Commands for a detailed walkthrough of all commands.
Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions | Features of Java | Basic Linux Commands | Amazon Interview Questions
Recently completed any professional course/certification from the market? Tell us what you liked or disliked in the course for more curated content.
Click here to submit its review with Shiksha Online.
This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio
Top Picks & New Arrivals
