SQL Reference

VIEW

VIEW in SQL is a virtual table, based on the result-set of an SQL statement. It contains rows and columns, similar to real tables. The fields in a VIEW are fields from one or more real tables in the database. You can add functions, join, etc., in a VIEW and it doesn't physically store the data itself, but fetches the data from the tables on which it is based when used.

Example

CREATE VIEW employee_view AS
SELECT employee_ID, first_name, last_name
FROM employees;

Output

Query OK, 0 rows affected (0.00 sec)

Explanation

The VIEW employee_view was created to simplify querying. The view includes employee_ID, first_name, and last_name from the employees table. You now query employee_view instead of the employees table for these specific fields.

Example

CREATE VIEW student_view AS
SELECT student_name, student_age
FROM student;

Output

CREATE VIEW

Explanation

This example captures the creation of a VIEW named student_view. A VIEW is a virtual table based on the result-set of an SQL statement. In this scenario, the student_view returns the student_name and student_age from the student table. The displayed output message, CREATE VIEW, confirms the successful creation of the VIEW.

Example

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName
FROM Employees;

Output

View 'EmployeeView' has been created.

Explanation

In the given example, a view named ‘EmployeeView’ has been created using the CREATE VIEW statement. This view includes the ‘EmployeeID’, ‘FirstName’, and ‘LastName’ columns from the ‘Employees’ table.

Example

CREATE VIEW sample_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 20;

Output

View "SAMPLE_VIEW" has been created.

Explanation

The above example creates a view named sample_view which consists of three columns: employee_id, first_name, and last_name selected from the employees table where the department_id equals 20. Only the rows fulfilling this condition will be part of the new view. The view does not store the data itself but is a reflection of the data in the original employees table, making it a virtual table.

Example

CREATE VIEW my_view AS
SELECT date_of_birth, COUNT(employee_id) as employee_count
FROM employees
GROUP BY date_of_birth;

Output

date_of_birth	employee_count
1980-01-01	2
1981-02-02	3
1983-03-03	1

Explanation

A VIEW in SQLite is a virtual table that is based on the result-set of an SQL statement. In this example, a VIEW named ‘my_view’ is created. It consists of the ‘date_of_birth’ and ‘employee_count’ columns. The ‘employee_count’ is generated by counting the employee IDs corresponding to each unique ‘date_of_birth’. The VIEW can then be used to interact with this grouped and aggregated data as though it was an ordinary table.