Sql Server Creating Views Complete Guide

Example 1: Creating a Simple View

Suppose you have two tables: Employees and Departments.

Structure of Employees Table:

| EmployeeID | FirstName | LastName | DepartmentID | Salary | |------------|-------------|-----------|--------------|--------| | 1 | John | Doe | 1 | 50000 | | 2 | Jane | Smith | 2 | 60000 | | 3 | Michael | Johnson | 1 | 55000 |

Structure of Departments Table:

| DepartmentID | DepartmentName | |--------------|----------------| | 1 | HR | | 2 | IT | | 3 | Finance |

Now, let's create a view that lists all employees along with their department names.

Step 1: Create the Tables

First, you need to create the tables with some sample data.

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10,2)
);

-- Insert sample data into Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary) VALUES
(1, 'John', 'Doe', 1, 50000.00),
(2, 'Jane', 'Smith', 2, 60000.00),
(3, 'Michael', 'Johnson', 1, 55000.00);


-- Create Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Insert sample data into Departments table
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');

Step 2: Create a Simple View

Create a view named EmployeeDetails that selects employee first name, last name, department name, and salary.

CREATE VIEW EmployeeDetails AS
SELECT 
    E.FirstName,
    E.LastName,
    D.DepartmentName,
    E.Salary
FROM 
    Employees E
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID;

Step 3: Query the View

Now, query the view to see the results:

SELECT * FROM EmployeeDetails;

Expected Result:

| FirstName | LastName | DepartmentName | Salary | |-------------|-----------|----------------|--------| | John | Doe | HR | 50000 | | Jane | Smith | IT | 60000 | | Michael | Johnson | HR | 55000 |

Example 2: Creating a View with WHERE Clause

Let's create a view that shows only employees who earn more than $52,000.

Step 1: Create the View

Create a view called HighEarners:

CREATE VIEW HighEarners AS
SELECT 
    E.FirstName,
    E.LastName,
    D.DepartmentName,
    E.Salary
FROM 
    Employees E
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID
WHERE 
    E.Salary > 52000;

Step 2: Query the View

Query the new view HighEarners:

SELECT * FROM HighEarners;

Expected Result:

| FirstName | LastName | DepartmentName | Salary | |-------------|-----------|----------------|--------| | John | Doe | HR | 50000 | | Jane | Smith | IT | 60000 | | Michael | Johnson | HR | 55000 |

Note that John does not appear here because his salary is not greater than $52,000.

Example 3: Creating a View with Aggregation

Let's create a view that provides the total salary and average salary per department.

Step 1: Create the View

Create a view called DepartmentStats:

CREATE VIEW DepartmentStats AS
SELECT 
    D.DepartmentName,
    SUM(E.Salary) AS TotalSalary,
    AVG(E.Salary) AS AverageSalary
FROM 
    Employees E
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID
GROUP BY 
    D.DepartmentName;

Step 2: Query the View

Query the DepartmentStats view:

SELECT * FROM DepartmentStats;

Expected Result:

| DepartmentName | TotalSalary | AverageSalary | |----------------|-------------|---------------| | HR | 105000.00 | 52500.00 | | IT | 60000.00 | 60000.00 |

Example 4: Creating an Updatable View

An updatable view is one that you can insert, update, or delete rows as if you were doing it directly on a table. Not all views are updatable. The view must meet certain criteria, one of them being it should reference only one table.

Step 1: Create the View

Let's create an updatable view for the Employees table that shows only the FirstName, LastName, and DepartmentID.

CREATE VIEW v_EmployeeBasicInfo AS
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    DepartmentID
FROM 
    Employees;

Step 2: Check if the View is Updatable

To check if a view is updatable, you can use sp_helptext 'viewname'.

EXEC sp_helptext 'v_EmployeeBasicInfo';

You'll see that the view is directly referencing the Employees table without any complex joins, filters, etc., which means it's generally updatable.

Step 3: Insert Row using the View

Insert a new row via the view:

INSERT INTO v_EmployeeBasicInfo (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (4, 'Alice', 'Brown', 3);

Step 4: Update Row using the View

Update an existing row via the view:

UPDATE v_EmployeeBasicInfo
SET DepartmentID = 1
WHERE EmployeeID = 4;

Step 5: Delete Row using the View

Delete a row via the view:

DELETE FROM v_EmployeeBasicInfo
WHERE EmployeeID = 4;

Example 5: Dropping a View

To remove a view from the database, you can use the DROP VIEW statement.

DROP VIEW IF EXISTS EmployeeDetails;
DROP VIEW IF EXISTS HighEarners;
DROP VIEW IF EXISTS DepartmentStats;
DROP VIEW IF EXISTS v_EmployeeBasicInfo;

After this statement runs, there will be no views named EmployeeDetails, HighEarners, DepartmentStats, or v_EmployeeBasicInfo in your database.

Summary

• View Creation: Use CREATE VIEW view_name AS SELECT ...
• Complex Queries: Joins, filters, aggregation functions can be used in the SELECT statement.
• Updatable Views: The view references only one table without complex operations.
• View Deletion: Use DROP VIEW IF EXISTS view_name;