Simplify Data Access with a Basic Employee View

Create a Simple View

Write a PostgreSQL query to create a view that selects specific columns from the Employees table.

Solution:

-- Create a view to display employee ID, name, and department.
CREATE VIEW EmployeeInfo AS
SELECT employee_id, name, department
FROM Employees;

Explanation:

• Purpose of the Query:
• The goal is to create a virtual table (view) that simplifies data access by showing only selected columns from the Employees table.
• This demonstrates how to use the CREATE VIEW statement to encapsulate a query.
• Key Components:
• CREATE VIEW EmployeeInfo AS : Names and defines the view.
• SELECT employee_id, name, department FROM Employees : Specifies the data to be included.
• Real-World Application:
• Useful for providing end-users with a simplified interface to complex data without exposing all underlying details.

Notes:

• Views do not store data physically; they represent stored queries.
• Ensure that the underlying table(s) remain consistent with the view definition.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a view that selects distinct records from the Employees table based on a composite of first_name and last_name.
• Write a PostgreSQL query to create a view that includes only the top 10 highest paid employees from the Employees table.
• Write a PostgreSQL query to create a view that shows employee details along with a computed column for annual salary (assuming salary is monthly).
• Write a PostgreSQL query to create a view that filters employees who joined within the last 12 months using a date function.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Creating and managing views Home.
Next PostgreSQL Exercise: Create a View with a Join.