Syntax:

To create a view, you use the CREATE VIEW statement followed by a SELECT query.

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

Example:

Let's say we have a table named employees:

                                  
                                    CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
    (1, 'John', 'Doe', 'HR', 50000.00),
    (2, 'Jane', 'Smith', 'IT', 60000.00),
    (3, 'Bob', 'Johnson', 'Finance', 55000.00);
                                  
                                

Now, let's create a view that selects specific columns from the employees table:

                                
                                    CREATE VIEW hr_employees AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department = 'HR';
                                
                              

In this example:

• hr_employees is the name of the view.
• It includes columns employee_id, first_name, and last_name.
• It only includes rows where the department is 'HR'.

Querying a View:

Once a view is created, you can query it like a regular table:

                                  
                                    SELECT * FROM hr_employees;
                                  
                                

In this example:

This will retrieve the data from the hr_employees view, displaying only the specified columns and rows based on the defined condition.

Updating a View:

Views can be updated just like tables if the underlying tables allow it. However, there are certain conditions that must be met. For example, you can update a view if it consists of a single table and does not contain expressions, aggregates, or joins.

Dropping a View:

To remove a view, you use the DROP VIEW statement:

                                      
                                        DROP VIEW hr_employees;
                                      
                                    

This deletes the hr_employees view.

Tips:

• Views do not store data themselves; they are virtual and represent the result of a query.
• Use views to simplify complex queries or to provide a simplified and controlled interface to the data.
• Views can be used to encapsulate complex logic, making it easier to manage and maintain.