SQL CREATE VIEW

Introduction

In this chapter, we will focus on the CREATE VIEW statement in SQL. A view is a virtual table based on the result set of an SQL query. Views can simplify complex queries, enhance security by limiting data access, and provide a layer of abstraction over the underlying tables. This chapter will cover the definition, syntax, and provide examples to help you understand how to use the CREATE VIEW statement effectively.

What is a View?

A view is a virtual table that provides a way to present data in a specific format or structure. Unlike a regular table, a view does not store data physically; instead, it stores a query that dynamically retrieves data from one or more tables whenever the view is accessed.

Syntax for CREATE VIEW

Basic Syntax

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;

• view_name: The name of the view to be created.
• columns: The columns to be included in the view.
• table: The table from which the view will retrieve data.
• conditions: The conditions for selecting data.

Step-by-Step Example

Sample Tables

First, let's create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);

Creating a Simple View

To create a view that displays employee names and their corresponding department names:

CREATE VIEW employee_department_view AS
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Querying the View

To retrieve data from the employee_department_view:

SELECT * FROM employee_department_view;

Output

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
Ravi	Verma	NULL

In this example, the view employee_department_view provides a simplified representation of the data, combining information from both the employees and departments tables.

Creating a View with Conditions

To create a view that only includes employees with a salary greater than 55000:

CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 55000;

Querying the View with Conditions

To retrieve data from the high_salary_employees view:

SELECT * FROM high_salary_employees;

Output

first_name	last_name	salary
Sita	Patel	60000
Priya	Sharma	62000

In this example, the view high_salary_employees filters the data to include only employees with a salary greater than 55000.

Updating Data Through a View

Views can sometimes be used to update data in the underlying tables, depending on the complexity of the view and the database system. Here is an example of updating data through a simple view:

CREATE VIEW employee_salary_view AS
SELECT id, first_name, last_name, salary
FROM employees;

-- Update salary through the view
UPDATE employee_salary_view
SET salary = 65000
WHERE id = 1;

Deleting a View

To delete a view when it is no longer needed:

DROP VIEW employee_department_view;

Conclusion

The CREATE VIEW statement is used for simplifying complex queries, enhancing data security, and providing a layer of abstraction over the underlying tables. This chapter covered the basic syntax for CREATE VIEW, provided examples to illustrate its use, and demonstrated how to create, query, update, and delete views. Understanding how to use views effectively will greatly enhance your ability to manage and analyze data in a relational database. In the next chapter, we will explore more about UPDATE View.