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.
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.
Suppose we have a table named employees with the following columns:
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:
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.