CREATE VIEW – SQL Tutorial

SQL CREATE VIEW statement is used to create a virtual table that is based on the result set of a SELECT statement. A view does not store any data of its own; instead, it references data from one or more tables in the database. Views are used to simplify complex queries and to provide a consistent interface to the underlying data.

Syntax

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

In the above syntax, view_name is the name of the view that you want to create. The AS keyword is used to indicate that you are defining a view. The SELECT statement specifies the columns that you want to include in the view. The FROM clause specifies the table(s) from which the view will retrieve data. The WHERE clause is optional and is used to specify a condition that filters the rows returned by the SELECT statement.

Example

Suppose we have a table named employees with the following columns:

id	name	department	salary
1	John	HR	50000
2	Jane	Marketing	60000
3	Sarah	Sales	55000
4	Michael	HR	45000
5	David	Marketing	65000

create table employees
(
id int, 
name varchar(50), 
department varchar(50), 
salary int
);

insert into employees(id, name, department, salary) values 
(1,'John','HR',50000), (2,'Jane','Marketing',60000),
(3,'Sarah','Sales',55000), (4,'Michael','HR',45000), 
(5,'David','Marketing',65000);

If we want to create a view that includes only the employees from the Marketing department, we can use the following CREATE VIEW statement:

CREATE VIEW marketing_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'Marketing';

This will create a view named marketing_employees that includes only the id, name, and salary columns of the employees from the Marketing department. We can then query this view just like we would query a table:

SELECT * FROM marketing_employees;

This will return the following result set:

id	name	salary
2	Jane	60000
5	David	65000

Note that the view does not store any data of its own; it simply provides a virtual table that references the data in the original table. Any changes made to the original table will be reflected in the view.