How to Create, Update, Insert and Delete SQL Views?

How to Create, Update, Insert and Delete SQL Views?

4 mins read8K Views Comment
clickHere
Updated on Jul 18, 2022 18:04 IST

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.

2022_04_SQL-Views-create-delete-insert-and-drop.jpg

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:

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

How to create a View?

You can use the following syntax to create a view.

Syntax:

 
CREATE VIEW ViewName
AS
SELECT Column1, Column2, ..., ColumnN
FROM TableName
WHERE [conditions];
Copy code

Creating views from a single table:

 
CREATE VIEW PatientsView
AS SELECT PatientID, PatientName, Haemoglobin FROM Patients;
Copy code

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, CheckupDetails
WHERE Patients.PatientID = CheckupDetails.PatientID;
Copy code

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
Difference between SQL and NoSQL
Difference between SQL and NoSQL
This article designed for beginners will teach you difference between SQL and NoSQL. The tutorial includes concepts, such as what is a SQL, what is NoSQL, and more.
Filtering Data with SQL
Filtering Data with SQL
Data is being generated all the time. Hence, it is of utmost importance to understand how to filter data. The previous article was on the ways to select and retrieve...read more
Understanding Subqueries in SQL
Understanding Subqueries in SQL
This blog explains subqueries in SQL. We will explore what are nested queries and sub-queries and how they help retrieve data from complex databases.

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=ValueN
WHERE [Condition];
Copy code

Example:

 
UPDATE PatientsView
SET
PatientName = Sahiti
WHERE PatientID =1;
Copy code

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);
Copy code

Example:

 
INSERT INTO PatientsView (PatientID, PatientName, Haemoglobin)
VALUES (05,’Suhana’,56.12);
Copy code

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 ViewName
WHERE [Condition];
Copy code

Example:

 
DELETE FROM PatientsView
WHERE PatientID =01;
Copy code

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
Copy code

Example:

 
DROP VIEW PatientsView
Copy code

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.

About the Author

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

Comments