What is View in SQL - Naukri Code 360

Introduction

Hey, Ninjas! Have you ever heard of View in SQL and its importance in databases? The view is like the doors of the houses, which helps us see some information from the database without showing every detail.

Views are very important for presenting the data in a specific way that is easy to understand. Here in this blog, we will understand in detail about Views in SQL and learn its importance.

View in SQL

View in SQL is a kind of Virtual Table, which means that it does not exist in the database but is created by a query. View shows specific information from different tables or a single table in a database. It is created by selecting rows and columns from different tables and then saving them as a View.

The View is just like a regular table in SQL, but a table is an object which stores data in rows and columns, whereas View is a virtual table. It helps maintain data integrity, keeps the data secure and simplifies complex queries.

Creating View

To create a View in SQL, we use CREATE VIEW statement.

Syntax

Here is the basic Syntax for Creating Views in SQL.

CREATE VIEW view_name AS  
SELECT column1, column2...
FROM table1, table2...
WHERE condition;

Now, we use the SELECT statement to see the View data.

SELECT * FROM view_name;

Explanation

CREATE statement helps us create a new View.
AS keyword tells us what the View is made up of.
SELECT statement selects different columns from different tables.
FROM table_name statement tells us the table from which we have selected the column.
WHERE condition is an optional statement that customises the data based on a condition.

Example

Let's say we have two tables stating student information and grades. We want to create a View with the student's first name, last name and average grades.

SQL Query

--Creating the students table

CREATE TABLE students (
 student_id INT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 email VARCHAR(100)
);

--Inserting data into the students table

INSERT INTO students VALUES
 (1, 'Aditya', 'Gupta', 'aditya.gupta123@gmail.com'),
 (2, 'Rohan', 'Agrawal', 'roham12@gmail.com'),
 (3, 'Aryan', 'Vyas', 'aryan.vyas@gmail.com'),
 (4, 'Aman', 'Singh', 'aman34@gmail.com');

--Creating the grades table

CREATE TABLE grades (
 grade_id INT PRIMARY KEY,
 student_id INT,
 course_name VARCHAR(50),
 grade DECIMAL(4,2)
);

--Inserting data into the grades table

INSERT INTO grades VALUES
 (1, 1, 'Math', 85.00),
 (2, 1, 'English', 90.00),
 (3, 2, 'Math', 92.50),
 (4, 2, 'English', 87.50),
 (5, 3, 'Math', 80.00),
 (6, 3, 'English', 95.00),
 (7, 4, 'Math', 70.00),
 (8, 4, 'English', 82.50);

STUDENTS TABLE

GRADES TABLE

SQL Query for creating View

Note: You have to run the below command in one go.

-- Creating a new view called student_grades_avg
CREATE VIEW student_grades_avg AS

-- Selecting the first name, last name, and the average grade for each student
SELECT students.first_name, students.last_name, AVG(grades.grade) AS average_grade

-- Joining the students and grades tables based on student_id
FROM students
JOIN grades
ON students.student_id = grades.student_id

-- Grouping the results by student_id, first_name, and last_name
GROUP BY students.student_id, students.first_name, students.last_name;

To see the data inside student_grades_avg

SELECT * FROM student_grades_avg;

Query Output

Explanation

We created a View called student_grades_avg which shows the first name, last name and average grades for all their courses. JOIN is used to combine two tables of students and grades based on student_id.

Also see, Natural Join in SQL

Updating View

We can update the View by CREATE OR REPLACE VIEW statement, but we must consider some conditions before updating View.

Syntax

DROP VIEW IF EXISTS view_name;
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Explanation:

DROP VIEW IF EXISTS statement drops the View if it already exists.
CREATE VIEW statement creates a new view with the updated query.
SELECT statement describes the new definition of the selected View.
FROM describes the tables from which View will take the data.
WHERE condition is a conditional statement that filters the data based on a condition.
HAVING condition is a conditional statement to filter the data based on aggregate functions.

Example

Let us consider the SQL query where we have created two tables stating student information and grades. We also created a View with the student's first name, last name and average grades.

Now we can update the View to include students with grades above 80.

SQL Query for updating View

Note: You have to run the below command in one go.

-- Dropping the view if it already exists
DROP VIEW IF EXISTS student_grades_avg;

-- Creating a new view called student_grades_avg
CREATE VIEW student_grades_avg AS

-- Selecting the first name, last name, and the average grade for each student
SELECT students.first_name, students.last_name, AVG(grades.grade) AS average_grade

-- Joining the students and grades tables based on student_id
FROM students
JOIN grades ON students.student_id = grades.student_id

-- Grouping the results by student_id, first_name, and last_name
GROUP BY students.student_id, students.first_name, students.last_name

-- Only including students with an average grade greater than 80
HAVING AVG(grades.grade) > 80;

To see the data inside student_grades_avg

SELECT * FROM student_grades_avg;

Query Output

Explanation

We use the DROP VIEW statement to delete the previously created View.
We create a View called student_grades_avg which shows the first name, last name and average grades for all their courses.
JOIN is used to combine two tables of students and grades based on student_id.
GROUPBY statements group the data based on student ID, student first name and student last name.
HAVING statement filters the rows of View based on the average of grades greater than 80.

Inserting Rows in View

A View is a virtual table, so we cannot update it directly by considering it as a physical table. But we can insert the rows in the tables from which View is made up and those rows will be shown in View.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Explanation

INSERT INTO: This statement is used to select the table where the row should be inserted.
VALUES: This statement inserts the values in the new row which is created.

Example

We will again consider the SQL query where we have created two tables stating student information and grades. We also created a View with the student's first name, last name and average grades.

Let us insert a row in the students table.

We will insert a row in the grades table to insert a row of new student in View.

SQL Query for students table:

INSERT INTO students (student_id, first_name, last_name, email)
VALUES (5, 'Abhinav', 'Nagar', 'abhi23@gmail.com');

SQL Query for grades table:

INSERT INTO grades (grade_id, student_id, course_name, grade) VALUES 
(9, 5, 'Math', 90.00),
(10, 5, 'English', 85.00);

Updated Students Table

Updated Grades Table

To see the data inside student_grades_avg

SELECT * FROM student_grades_avg;

Query Output

Explanation

We created a new row in student_grades_avg by creating new rows in students and grades tables respectively.

Dropping View

To delete a View in SQL, we can write a DROP statement simply to delete View in SQL.

Syntax

DROP VIEW view_name;

Explanation

DROP VIEW statement deletes a specific View in SQL.

Example

Let us consider the previously created View named student_grades_avg.

SQL Query

DROP VIEW student_grades_avg;

To see the data inside student_grades_avg

SELECT * FROM student_grades_avg;

Query Output

Explanation

DROP VIEW statement deletes or erases the View which we previously created.

Types of Views

In SQL, we have mainly two types of Views.

Simple Views: These kinds of Views are created from a single table. It is used to show specific data from a table to the user.
Complex Views: These kinds of Views are created from multiple tables. Complex Views are used to create customised data from different tables.

Subtypes of Views

Different subtypes of Views are used depending on the situation given to the user.

Materialized Views: These views store the result of creating a View query in SQL in Physical tables. The significant difference between a View and Materialized View is that Materialized View can increase the performance of a Query.
Index Views: These are the Views in which the index is created on complex queries, which makes the View easy to read and understand.
Recursive Views include Recursive SQL queries and traverse data structures like trees and graphs.
Updatable Views: These are the Views used to update the tables from which a particular View is made.

Pros of View

There are several pros of creating a View in SQL.

Views simplify complex SQL queries, making it easier for the user to work with complex data.
Views can be used to restrict specific data from a table or multiple tables as it shows specific data from a table. This helps to improve data security.
Views can increase query performance by precomputing or aggregating the data from multiple tables.

Cons of View

While Views have many pros, there are some cons we need to consider.

Creating a View takes a lot of processing power, particularly for large datasets and complex queries.
Only certain types of operations can be used on Views directly. For example, it is not possible to directly add a row in View.
Views can increase security risks if not correctly created or designed.
Views can add a layer of Complexity to SQL queries, making the underlying data challenging to understand and debug.

Must Read SQL Clauses

Frequently Asked Questions

What is a View in SQL?

View in SQL is a kind of Virtual Table that shows specific information from different tables or a single table in a database.

How is View different from tables?

Tables store data in rows and columns, while a View in SQL is a virtual table that shows specific information from different tables or a single table in a database.

Are Views stored in the database?

Views are not stored in the database but virtual objects created in a database.

How do I drop a View?

To delete or drop a View in SQL, we can write a DROP statement followed by View name (DROP VIEW view_name;).

Can I make a View that includes data from several databases?

It is possible to create a View that includes data from several databases if the database system supports cross-database queries.

Conclusion

This article discusses the topic of what is View in SQL. In this blog, we have discussed View in SQL, its main operations on View, and its advantages and disadvantages. We hope this blog has helped you enhance your knowledge of View in SQL. If you want to learn more, then check out our articles.

And many more on our platform Coding Ninjas Studio.

Refer to our Guided Path to upskill yourself in DSA, Competitive Programming, JavaScript, System Design, and many more! If you want to test your coding ability, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio!

But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. In that case, you must look at the problems, interview experiences, and interview bundles for placement preparations.

However, you may consider our paid courses to give your career an edge over others!

Happy Learning!