SQL CREATE VIEW Tutorial

Example Table

We will use the following table named employees for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        position VARCHAR(50),
        salary DECIMAL(10, 2),
        age INT
    );

    INSERT INTO employees (employee_id, name, position, salary, age) VALUES
    (1, 'John Doe', 'Manager', 60000.00, 45),
    (2, 'Jane Smith', 'Developer', 55000.00, 30),
    (3, 'Emily Johnson', 'Designer', 50000.00, 25);
    

Employees Table

employee_id	name	position	salary	age
1	John Doe	Manager	60000.00	45
2	Jane Smith	Developer	55000.00	30
3	Emily Johnson	Designer	50000.00	25

Using SQL CREATE VIEW

The CREATE VIEW statement creates a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table.

Creating a Simple View

Let's create a view named employee_view that includes the name and position of employees:

    CREATE VIEW employee_view AS
    SELECT name, position
    FROM employees;
    

Result:

Command	CREATE VIEW employee_view AS SELECT name, position FROM employees;
Result	A view named employee_view is created. The view includes the name and position columns from the employees table.

Querying the View

We can query the view just like a regular table:

    SELECT * FROM employee_view;
    

Result:

Command	SELECT * FROM employee_view;
Result	name position John Doe Manager Jane Smith Developer Emily Johnson Designer

Creating a View with a Condition

Let's create a view named high_salary_view that includes employees with a salary greater than 55000:

    CREATE VIEW high_salary_view AS
    SELECT name, salary
    FROM employees
    WHERE salary > 55000;
    

Result:

Command	CREATE VIEW high_salary_view AS SELECT name, salary FROM employees WHERE salary > 55000;
Result	A view named high_salary_view is created. The view includes the name and salary columns from the employees table where the salary is greater than 55000.

Querying the Conditional View

We can query the conditional view just like a regular table:

    SELECT * FROM high_salary_view;
    

Result:

Command	SELECT * FROM high_salary_view;
Result	name salary John Doe 60000.00