Views

Views are fundamental in the world of data, serving as a foundational concept. Although it may seem basic, understanding views is crucial as we will delve into more advanced topics such as Materialized Views and External Tables.

Let's break it down...

In the realm of databases, we are familiar with tables, structured with columns and rows, each representing a specific dataset post-normalization (a topic we'll cover separately). Examples include tables for employees, departments, and salaries.

Now, imagine the need to combine information from these tables, like obtaining a comprehensive view of a customer's department and salary. To achieve this, we would typically construct a query that involves joining these tables, providing us with a consolidated result.

However, if we find ourselves running this query frequently, perhaps on a daily or monthly basis, creating a "view" becomes advantageous. A view allows us to encapsulate the query and, when needed, execute a simple SELECT command on the view to retrieve the combined results from the employee, department, and salary tables.

It's crucial to note that a view itself doesn't store any data; instead, it dynamically fetches data from the underlying tables whenever the view is queried.

In this pseudo code, we create a view named " EmployeeProfile" that combines data from the Employee, Department, and other details based on their relationships. Subsequently, querying this view allows us to obtain the consolidated information without having to rewrite the complex join query every time.

Let's illustrate this with a simplified example:

CREATE VIEW EmployeeProfile

AS

SELECT Employee.*, Department.*

FROM Employee, Department

WHERE Employee.DeptID = Department.DeptID

‘In nutshell, view the virtual representation of a table.’