sql
  1. sql-creating-views
PreviousNext

SQL View Creating Views

A SQL view is a virtual table that displays data from one or more tables in a database. Views are used to simplify the complexity of queries by combining multiple tables, filtering data, and displaying only specific columns of interest. In this page, we will cover how to create views in SQL with syntax, examples, and important points.

Syntax

The syntax for creating a view in SQL is as follows:

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

Example

Let's say we have a table named "employees" with columns "id", "name", "department", and "salary". We want to create a view that displays only the name and salary columns of employees who work in the Sales department and have a salary greater than 50000. The view can be created with the following SQL query:

CREATE VIEW sales_employee_salary AS
SELECT name, salary
FROM employees
WHERE department = 'Sales' AND salary > 50000;

Output

Once the view is created, we can query it just like a regular table. For example, to display the names and salaries of sales employees, we can run the following query:

SELECT * FROM sales_employee_salary;

This will display the names and salaries of Sales employees who have a salary greater than 50000 in a tabular format.

Explanation

The view is created by selecting specific columns from the "employees" table using a SELECT statement. We are filtering the data by specifying the "Sales" department and a salary greater than 50000 using a WHERE clause. The view is named "sales_employee_salary" with the CREATE VIEW statement.

Use

Views are useful for simplifying complex queries, improving performance by reducing the amount of data retrieved, and providing a layer of abstraction between the database schema and the end-users. They are also useful for providing secure access to data by restricting access to sensitive data through views.

Important Points

  • Views are read-only and cannot be modified directly.
  • Only the SELECT statement is used in creating a view.
  • Views can be used in place of a table in most SQL statements.
  • Views can be nested, with one view calling another view.
  • Views can improve query performance by reducing the amount of data accessed.

Summary

SQL views are virtual tables that provide a simplified interface to database tables. Views are created using the CREATE VIEW statement, and they can be used to simplify complex queries, improve query performance, and provide secure access to data. Views are read-only and cannot be directly modified.

Types of SubqueriesDropping Views
Published on: