CREATE VIEW

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.