Why views matter in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple view query

Given a table employees with columns id, name, and salary, and a view high_earners defined as CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 50000;, what will be the output of SELECT * FROM high_earners; if the table contains:

id | name | salary
1 | Alice | 60000
2 | Bob | 45000
3 | Carol | 70000

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, salary INT);
INSERT INTO employees VALUES (1, 'Alice', 60000), (2, 'Bob', 45000), (3, 'Carol', 70000);
CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 50000;
SELECT * FROM high_earners;

A [{"id": 1, "name": "Alice", "salary": 60000}, {"id": 3, "name": "Carol", "salary": 70000}]

B []

C [{"name": "Bob", "salary": 45000}]

D [{"name": "Alice", "salary": 60000}, {"name": "Carol", "salary": 70000}]

💡 Hint

Remember the view filters employees with salary greater than 50000.

The view selects only the name and salary columns for employees earning more than 50000. Bob earns less, so he is excluded.

🧠 Conceptual

intermediate

Why use views for security?

Why are views useful for controlling access to sensitive data in PostgreSQL?

A Views prevent users from running any queries on the database.

B Views automatically encrypt data to protect it from unauthorized access.

C Views can restrict which columns and rows users see without giving direct access to the base tables.

D Views delete sensitive data after a set time to protect privacy.

💡 Hint

Think about how views can limit what data is visible.

Views act like windows showing only specific parts of data, so users can see only what the view allows, protecting sensitive columns or rows.

📝 Syntax

advanced

Identify the correct syntax to create a view with a join

Which option correctly creates a view named employee_departments that shows employee names and their department names from tables employees (columns: id, name, dept_id) and departments (columns: id, dept_name)?

A CREATE VIEW employee_departments AS SELECT employees.name, departments.dept_name FROM employees JOIN departments ON employees.dept_id = departments.id;

B CREATE VIEW employee_departments SELECT employees.name, departments.dept_name FROM employees INNER JOIN departments WHERE employees.dept_id = departments.id;

C CREATE employee_departments VIEW AS SELECT name, dept_name FROM employees, departments WHERE employees.dept_id = departments.id;

D VIEW employee_departments AS SELECT employees.name, departments.dept_name FROM employees JOIN departments ON employees.dept_id = departments.id;

💡 Hint

Remember the correct order: CREATE VIEW view_name AS SELECT ...

Option A uses the correct syntax with CREATE VIEW, AS, and a proper JOIN clause. Other options have syntax errors or missing keywords.

❓ optimization

advanced

Performance impact of materialized views

What is the main advantage of using a materialized view instead of a regular view in PostgreSQL?

A Materialized views allow users to write data back to the base tables through the view.

B Materialized views store the query result physically, improving query speed at the cost of needing manual refresh.

C Materialized views compress data to save disk space compared to regular views.

D Materialized views automatically update in real-time without any performance cost.

💡 Hint

Think about how storing results affects query speed and freshness.

Materialized views save the query output physically, so queries run faster but require manual refresh to update data.

🔧 Debug

expert

Why does this view fail to update?

Consider a view employee_info created as CREATE VIEW employee_info AS SELECT id, name, salary * 1.1 AS adjusted_salary FROM employees;. When trying to run UPDATE employee_info SET adjusted_salary = 70000 WHERE id = 1;, it fails. Why?

A Because the view contains a computed column, PostgreSQL cannot update it directly without an INSTEAD OF trigger.

B Because the view does not include the primary key column.

C Because views cannot be queried, only updated.

D Because the UPDATE statement syntax is incorrect for views.

💡 Hint

Think about what happens when a view has columns calculated from expressions.

Views with computed columns are not automatically updatable because the database cannot map changes back to base tables without special rules.