What is a View?

A View is a virtual table in SQL Server.

A View does not store data physically. Instead, it stores only

the SELECT query. Whenever someone queries the View, SQL Server
executes the stored query and displays the latest data from the
underlying table(s).

In Simple Way,

A View is a saved SELECT statement that behaves like a table.

Real-Life Analogy

Imagine a school has a master register containing:

 Roll Number

 Student Name

 Address

 Phone Number

 Parent Details

 Marks

The Principal only wants to see:

 Roll Number

 Student Name

 Marks

Instead of creating another table, the school creates a Principal View.

Whenever the principal opens the View, only those three columns are
displayed.

The original data still remains in the Students table.

This is exactly how a SQL Server View works.

Why Do We Need Views?

Suppose an Employees table has 30 columns.

Most employees only need to see:

  Employee ID

 Name

 Department

Without a View, they would write:

SELECT EmpID, Name, Department

FROM Employees;

every single day.

Instead, create a View once.

Then users simply execute:

SELECT * FROM EmployeeBasicInfo;

The View saves time and makes queries easier.

Advantages of Views

1. Simplicity

Complex queries can be written once and reused multiple times.

Instead of writing a long SELECT statement every day, users simply query
the View.

2. Security

Suppose the Employees table contains:

 Salary

 PAN Number

 Aadhaar Number

Regular employees should not see these confidential details.

Create a View that only shows:

 Employee ID

 Name

 Department

Users access the View instead of the actual table.

This protects sensitive information.

3. Reusability

If a query is frequently used, save it as a View.

Everyone can reuse it.

4. Data Abstraction

Users do not need to know whether the data comes from:

 One table

 Two tables

 Five tables

They simply query the View.

5. Always Shows Latest Data

Views never store data.

Whenever data changes in the base table, the View automatically shows
the updated data.

Syntax

CREATE VIEW ViewName

AS

SELECT column_list

FROM TableName;

Practical Example

Step 1 - Create Table

CREATE TABLE Employees

EmpID INT,
 Name VARCHAR(30),

Department VARCHAR(30),

Salary INT

);

Step 2 - Insert Data

INSERT INTO Employees

VALUES

(101,'Rahul','IT',60000),

(102,'Amit','HR',45000),

(103,'Neha','Finance',70000),

(104,'Priya','IT',65000);

Step 3 - Display Table

SELECT * FROM Employees;

Output

EmpID | Name | Department | Salary

101 | Rahul | IT | 60000

102 | Amit | HR | 45000

103 | Neha | Finance | 70000

104 | Priya | IT | 65000

Creating a View

Suppose users should only see:

 Employee ID

 Name

 Department

Create a View.

CREATE VIEW EmployeeBasicInfo

AS

SELECT EmpID, Name, Department

FROM Employees;

Accessing the View

SELECT * FROM EmployeeBasicInfo;

Output

EmpID | Name | Department

101 | Rahul | IT

102 | Amit | HR

103 | Neha | Finance

104 | Priya | IT

Notice that Salary is hidden.

Automatic Data Refresh

Update the table.

UPDATE Employees

SET Department='Marketing'

WHERE EmpID=102;

Again execute:

SELECT * FROM EmployeeBasicInfo;

Output

EmpID | Name | Department

101 | Rahul | IT

102 | Amit | Marketing

103 | Neha | Finance

104 | Priya | IT

The View automatically reflects the latest data.

Can Users Insert Data Through a View?

Yes.

If the View is a simple View based on a single table, SQL Server allows
INSERT, UPDATE and DELETE operations through the View.

Remember:

Users are not inserting data into the View.

They are actually inserting data into the underlying table through the
View.

Think of the View as a window to the original table.

Example

The View contains only:

 EmpID

 Name

 Department

CREATE VIEW EmployeeBasicInfo

AS

SELECT EmpID,

Name,

Department

FROM Employees;

Now insert data.

INSERT INTO EmployeeBasicInfo

VALUES

(105,'Riya','HR');

Even though the INSERT is performed on the View, SQL Server actually
inserts the record into the Employees table.

Now check the table.

SELECT * FROM Employees;

Output
EmpID | Name | Department | Salary

101 | Rahul | IT | 60000

102 | Amit | Marketing | 45000

103 | Neha | Finance | 70000

104 | Priya | IT | 65000

105 | Riya | HR | NULL

Notice that Salary becomes NULL because that column was not included
in the View.

Updating Data Through a View

UPDATE EmployeeBasicInfo

SET Department='Sales'

WHERE EmpID=105;

Now check the Employees table.

SELECT * FROM Employees;

Department is updated in the original table.

Deleting Data Through a View

DELETE FROM EmployeeBasicInfo

WHERE EmpID=105;

The record is deleted from the Employees table.

When Can We Perform INSERT, UPDATE and DELETE Through a

View?

Generally, DML operations are allowed when the View:

• Is based on a single table.

• Does not contain GROUP BY.

• Does not contain aggregate functions like SUM(), AVG(), COUNT(),

MAX(), MIN().

• Does not use DISTINCT.

• Does not use UNION.

• Does not contain complex JOINS.

If these features are used, SQL Server usually does not allow modifications
through the View.

Example of a Filtered View

Suppose only IT employees should be visible.

CREATE VIEW ITEmployees

AS

SELECT *

FROM Employees

WHERE Department='IT';

Execute

SELECT * FROM ITEmployees;

Only IT employees are displayed.

View Using JOIN

Create Department table.

CREATE TABLE Departments

DeptID INT,

DepartmentName VARCHAR(30)

);

Insert data.

INSERT INTO Departments

VALUES

(1,'IT'),

(2,'HR'),

(3,'Finance');
Create another employee table.

CREATE TABLE EmployeeDept

EmpID INT,

Name VARCHAR(30),

DeptID INT

);

Insert data.

INSERT INTO EmployeeDept

VALUES

(101,'Rahul',1),

(102,'Amit',2),

(103,'Neha',3);

Create View.

CREATE VIEW EmployeeDepartmentView

AS

SELECT

[Link],

[Link],

[Link]

FROM EmployeeDept E

INNER JOIN Departments D

ON [Link]=[Link];

Execute.

SELECT * FROM EmployeeDepartmentView;

Users get department names without writing JOIN queries.

Altering a View

ALTER VIEW EmployeeBasicInfo

AS

SELECT EmpID,

Name,

Department,

Salary

FROM Employees;

Dropping a View

DROP VIEW EmployeeBasicInfo;

Dropping a View removes only the View.

The Employees table and its data remain unchanged.

Difference Between Table and View

Table View

Stores data physically Does not store data

Occupies storage Stores only the query definition

Data is inserted
Displays data from table(s)
directly

Independent object Depends on underlying table(s)

Can exist without any Cannot exist without its base

View table(s)

Real-Life Use Cases

1. HR employees should not see Salary or PAN details.

Create a View that hides confidential columns.

2. Managers only want to see IT department employees.

Create a filtered View.

3. Developers frequently write long JOIN queries.

Create a View containing the JOIN.

 4. Reports are generated every day using the same query.

Save the query as a View and reuse it.

5. Users should work only with selected columns instead of the

complete table.

Create a View for controlled access.

Interview Questions

Q1. What is a View?

A View is a virtual table based on a stored SELECT statement. It does not

store data physically.

Q2. Does a View store data?

No. It stores only the SQL query. The data always comes from the
underlying table(s).

Q3. Why are Views used?

 Simplify complex queries

 Improve security

 Hide sensitive columns

 Reuse query logic

 Provide data abstraction

Q4. Can we insert data into a View?

Yes, if it is a simple View based on a single table and does not contain
GROUP BY, aggregate functions, DISTINCT, UNION, or complex JOINs.

Q5. Where is the data actually stored when inserting through a

View?

The data is stored in the underlying table. The View acts only as a window
to the table.

Q6. What happens if the base table changes?

The View automatically displays the latest data because it always

retrieves data from the base table.