Creating a View Based on The Summary Data of other Tables
The following view is used to get the count of the employees based on the department. We use the COUNT( ) to get the number of employees which we group by department name. To join the employees and department table we use the INNER JOIN keyword to display result by matching the employees id to the respective department.
CREATE VIEW employee_count_by_department AS
SELECT d.name AS department, COUNT(*) AS employee_count
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
GROUP BY d.name;
SELECT * FROM employee_count_by_department;
Output:
Explanation: In the above query, we have creates a virtual table named employee_count_by_department
that counts the number of employees in each department. It selects the department name (d
.
name
) and the count of employees in that department from the employees
and departments
tables, joining them on the department_id
field. The results are grouped by department name. The SELECT * FROM employee_count_by_department
statement then retrieves the data from this view.
MariaDB Create View
Views in MariaDB are powerful tools that allow us to create virtual tables based on the result set of a SELECT query. They provide a way to simplify complex queries, improve security by limiting access to certain columns,, and enhance performance by precomputing expensive operations. In this article, we will explore how to create views in MariaDB along with multiple examples.
Contact Us