What are the Views in SQL? Types of Views in SQL

What are the views in SQL?

Views in SQL are virtual tables that have rows and columns like a real table in the database. They are created by selecting fields from one or more tables present in the database. A view can either have all the rows of a table or specific rows based on certain conditions. Views do not form part of the database schema, but they reflect all the changes being made in the concerned tables.

Also Read:

• A Detail Guide on How to Rename a Column Name in SQL

• SQL Joins: A Comprehensive Guide to SQL and SQL Join Types

Types of views in SQL

There are two different types of views in SQL, namely system-defined views and user-defined views. Within user-defined views, the two types of views in MySQL that are widely known are simple views and complex views. Given below are the types of views in SQL with examples:

Simple views

Simple views are views that are created on a single table. Simple views in SQL are limited to basic operations and do not support analytical or aggregate operations involving grouping or sets. While it is possible to execute insert, update, and delete operations directly on a simple view, it is essential to have the primary key column included in the view for such actions to be successful.

Here is an example of a simple view:

CREATE VIEW simple_view AS
SELECT column1, column2
FROM table1
WHERE column1 = 'value';

Also Read:

• SQL vs Python: Which is better for a career in Data Science?

• SQL Functions: A Guide to Writing Functions in SQL

Complex views

Complex views, as the name suggests, are a bit complicated compared to simple views. Complex views in SQL are constructed from multiple database tables. They enable the execution of analytical and aggregate operations. However, unlike simple views, direct manipulation operations such as insert, delete, and update cannot be performed on complex views.

CREATE VIEW complex_view AS
SELECT column1, column2, column3
FROM table1
INNER JOIN table2 ON table1.column1 = table2.column1
WHERE table1.column1 = 'value';

System-defined views

In some databases like SQL server, we have some system-defined views too. They are views for routines, schemas, table_privileges, table_privileges, and check_constraints. They are automatically created when we create a database.

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

Advantages of views in SQL

Views in SQL have several advantages. Here are some of them:

Security

Individual users can be granted access to the database exclusively through a limited set of views, each containing only the specific data that aligns with the user's authorization. This approach effectively restricts a user's access to the stored data, ensuring controlled and tailored information visibility.

Query Simplicity

A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.

Structural simplicity

Views have the capability to provide a user with a personalised perspective of the database structure. This involves presenting the database as a collection of virtual tables specifically tailored to make sense for that particular user.

Also Read: Free SQL Courses & Certifications

Consistency

A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.

Data Integrity

If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

Encapsulation

SQL helps to encapsulate business rules in the database. Instead of enforcing rules in each application, you can implement them in a view. This ensures that the rules are consistently applied whenever the view is queried.

Logical data independence

Views contribute to a degree of independence between the application and database tables. In the absence of views, applications are typically reliant on direct interactions with tables. However, by incorporating views, the program can be designed to separate its functionality from the underlying database table, promoting a more modular and flexible approach.

What is the use of view in SQL?

Views in SQL are used to simplify complex queries, enhance data security, and provide a personalised view of the database structure. They can be used to limit access to sensitive data, allowing only authorised users to view certain information. Views can also be used to present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed Here is an example.

-- Creating an employees table

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

employee_name VARCHAR(50),

department_id INT

);

-- Inserting some sample data into the employees table

INSERT INTO employees VALUES (1, 'Amit’', 101);

INSERT INTO employees VALUES (2, 'Raj’', 102);

INSERT INTO employees VALUES (3, 'Ron’', 101);

-- Creating the departments table

CREATE TABLE departments (

department_id INT PRIMARY KEY,

department_name VARCHAR(50)

);

-- Inserting some sample data into the departments table

INSERT INTO departments VALUES (101, 'HR');

INSERT INTO departments VALUES (102, 'IT’');

-- Creating a view to show employee names along with department names

CREATE VIEW employee_department_view AS

SELECT e.employee_name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id;

Related: SQL Certification Courses by Top Providers

• edX SQL Certification Courses

• Vskills SQL Certification Courses

• Udemy SQL Certification Courses

• Coursera SQL Certification Courses

• Intellipaat SQL Certification Courses

• Simplilearn SQL Certification Courses

• LearnQuest SQL Certification Courses

• FutureSkills SQL Certification Courses

• SAS Institute SQL Certification Courses

• Great Learning SQL Certification Courses

Conclusion

Views in SQL are an essential feature that can help simplify complex queries, enhance data security, and provide a personalised view of the database structure. They are virtual tables that are created by selecting fields from one or more tables present in the database.

In this article, we discussed what views are in SQL, how many types of views in SQL, their advantages, and use cases. By using views, we can make our SQL queries more efficient, secure, and easy to understand.