SQL View

Contents hide

1 Introduction

2 Syntax

3 Querying a View

4 Types of Views in MySQL

4.1 Simple View

4.2 Updatable View

4.3 Complex View

5 Interview Questions

5.1 Basic View SQL Interview questions

5.2 Intermediate View SQL Interview questions

5.3 Advanced View SQL Interview questions

6 Quiz

Introduction

A view is created based on the result of a SELECT statement from one or more tables. This SELECT statement forms the definition of the view. Once the view is created, it can be queried and treated just like any other table in the database. Views can include joins, aggregate functions, filtering conditions, and more, allowing users to abstract the complexity of underlying data structures and provide a simplified interface. Enhance data security, simplify complex queries, and improve performance with views.

Syntax

To create a view in MySQL, you use the CREATE VIEW statement followed by the view name and the AS keyword, which specifies the SELECT statement that defines the view.

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

Let’s start with a simple example to understand how to create a basic view in MySQL. Suppose we have two tables in our database: “employees” and “departments,” with the following structures:

employees

emp_id	emp_name	department_id
1	John	101
2	Jane	102
3	Alex	101
4	Lisa	103

employees

departments

department_id	department_name
101	HR
102	Finance
103	Marketing

departments

We want to create a view that combines employee information with their respective department names.

CREATE VIEW employee_details AS
SELECT e.emp_id, e.emp_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

In this example, we’ve created a view named “employee_details” that fetches data from both the “employees” and “departments” tables and presents it as a single virtual table.

Querying a View

Once a view is created, we can query it just like a regular table:

SELECT * FROM employee_details;

This query will display the following result:

emp_id	emp_name	department_name
1	John	HR
2	Jane	Finance
3	Alex	HR
4	Lisa	Marketing

Types of Views in MySQL

MySQL supports different types of views based on their updateability and whether they reference a single table or multiple tables. Let’s explore the common types of views:

Simple View

A simple view is based on a single table and does not include any GROUP BY, HAVING, or aggregate functions in its definition. They are the most straightforward type of views and are generally updatable.

Example of a simple view:

CREATE VIEW simple_view AS
SELECT column1, column2
FROM table_name
[WHERE condition];

Updatable View

An updatable view allows modifications to the underlying tables through the view. The view must meet certain criteria to be updatable, such as not including aggregate functions or the DISTINCT keyword and referencing only one table in the SELECT statement. Additionally, the view’s columns must map directly to the columns of the underlying table.

Example of an updatable view:

CREATE VIEW updatable_view AS 
SELECT column1, column2 
FROM table_name 
[WHERE condition];

Complex View

A complex view involves multiple tables, joins, or aggregate functions in its definition. Complex views may or may not be updatable, depending on the complexity of the query and adherence to the updatable view criteria.

Example of a complex view:

CREATE VIEW complex_view AS
SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
[WHERE condition];

Benefits of Views

Data Abstraction: Views help abstract the complexity of underlying table structures, allowing users to interact with a simplified representation of data.
Data Security: Views can act as a security layer by providing access to specific columns or rows of a table, hiding sensitive information from users.
Simplified Querying: Views encapsulate complex queries, making it easier for users to retrieve data without dealing with the joins, filtering, and aggregation.
Performance Optimization: Views can cache the results of queries, improving performance for frequently executed queries.
Ease of Maintenance: If the underlying table schema changes, views can act as a buffer, and users don’t need to modify their queries as long as the view’s definition remains the same.

A views offer a powerful and flexible way to interact with data, providing a simplified interface to the underlying tables. Understand the select statement along with the examples.

Interview Questions

Basic View SQL Interview questions

Can views be indexed in MySQL?
Can you update data from multiple tables through a view?
How do you drop a view in MySQL?
What are the scenarios where using views is not recommended?
How can you check if a view is updatable in MySQL?

Intermediate View SQL Interview questions

Explain the role of the “WITH CHECK OPTION” when creating an updatable view.
Can views be used to improve the performance of complex queries involving multiple tables? Provide an example.
How can you modify data in a view that involves multiple tables with the “INSERT INTO” statement?