CREATE VIEW syntax in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this CREATE VIEW query?

Given the table employees with columns id, name, salary, what will be the result of this view creation and subsequent SELECT?

CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 50000;

Then running:

SELECT * FROM high_earners;

A Returns all employees with salary less than or equal to 50000, showing all columns.

B Returns all employees with salary greater than 50000, showing only their name and salary.

C Returns all employees with salary greater than 50000, showing all columns.

D Causes a syntax error because CREATE VIEW requires AS MATERIALIZED.

💡 Hint

Think about what the WHERE clause filters and which columns are selected in the view.

The view high_earners selects only name and salary columns from employees where salary is greater than 50000. Selecting from the view returns these filtered rows and columns.

📝 Syntax

intermediate

Which CREATE VIEW statement is syntactically correct?

Choose the correct syntax to create a view named dept_summary that shows department and the count of employees in each department from the employees table.

A CREATE VIEW dept_summary AS SELECT department, COUNT(*) FROM employees GROUP BY department;

B CREATE VIEW dept_summary SELECT department, COUNT(*) FROM employees GROUP BY department;

C CREATE VIEW dept_summary AS SELECT department, COUNT(*) FROM employees;

D CREATE VIEW dept_summary AS (SELECT department, COUNT(*) FROM employees GROUP BY department);

💡 Hint

Remember the keyword AS is required before the SELECT statement in CREATE VIEW.

Option A uses correct syntax: CREATE VIEW view_name AS SELECT ... GROUP BY ...; Option A misses AS, C misses GROUP BY, D has unnecessary parentheses causing syntax error.

🔧 Debug

advanced

Why does this CREATE VIEW statement fail?

Consider this statement:

CREATE VIEW recent_orders AS SELECT order_id, order_date FROM orders WHERE order_date > NOW() - INTERVAL '7 days';

It fails with an error. What is the reason?

A The WHERE clause is not allowed in CREATE VIEW statements.

B The INTERVAL syntax is invalid in PostgreSQL views.

C Views cannot use non-deterministic functions like NOW() in their definition.

D The SELECT statement must include all columns from the base table.

💡 Hint

Think about what functions are allowed inside view definitions.

PostgreSQL does not allow volatile or non-deterministic functions like NOW() inside a view definition because the view must be stable and consistent. This causes the error.

❓ optimization

advanced

How to optimize a view that runs slow due to complex calculations?

You have a view sales_summary that aggregates large data with complex calculations. It runs slowly when queried. What is the best way to improve performance?

A Drop the view and query the base tables directly every time.

B Add more columns to the view to reduce joins.

C Rewrite the view using subqueries instead of joins.

D Convert the view to a materialized view and refresh it periodically.

💡 Hint

Think about caching results of expensive queries.

Materialized views store the query result physically and can be refreshed on demand, improving query speed for complex aggregations.

🧠 Conceptual

expert

What happens if you update data through a view without INSTEAD OF triggers?

You have a view employee_info based on multiple tables joined together. You try to run:

UPDATE employee_info SET salary = salary + 1000 WHERE id = 5;

What will happen if no INSTEAD OF triggers are defined on this view?

A The update will fail with an error because the view is not inherently updatable.

B The update will succeed and modify the underlying tables automatically.

C The update will partially succeed, updating only one table silently.

D The update will create a new row in the view without affecting base tables.

💡 Hint

Think about how views handle data modification when based on joins.

Views based on joins are not automatically updatable. Without INSTEAD OF triggers to define how to handle updates, PostgreSQL raises an error.