VIEW Statement

SQL CREATE VIEW Statement

In SQL, a VIEW is a virtual table that is based on the result set of a SELECT query. It doesn't store data physically but represents data from one or more tables. The CREATE VIEW statement is used to define a new view in the database.

Here's the basic syntax of the CREATE VIEW statement:

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

Explanation of each part:

• CREATE VIEW: Keywords indicating that you want to create a new view.
• view_name: The name you want to give to the view.
• AS: Keyword indicating that you're defining the view as the result set of a SELECT query.
• SELECT column1, column2, ...: The columns you want the view to contain. You can specify any columns from one or more tables.
• FROM table_name: The table(s) from which you're selecting data.
• WHERE condition: Optional condition(s) to filter the data. It's not required; you can omit it if you want the view to contain all rows from the specified table(s).

Example:

Let's say we have two tables: employees and departments. We want to create a view that shows the names of employees along with their departments.

CREATE VIEW employee_department_view AS
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

In this example:

• The view is named employee_department_view.
• It contains the columns employee_id, first_name, last_name, and department_name.
• Data is selected from both the employees and departments tables, joined on the department_id column.

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

SELECT * FROM employee_department_view;

This query will return the data from the employee_department_view view, which will look like a combined table of employee details with their respective department names.