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.

Practice

1. What is the main purpose of using CREATE VIEW in PostgreSQL?

easy

A. To delete rows from a table

B. To save a SELECT query as a virtual table for easy reuse

C. To permanently store data in a new table

D. To create an index on a table column

Solution

1. Step 1: Understand what a view is

   A view is a virtual table that stores a SELECT query, not actual data.

2. Step 2: Identify the purpose of CREATE VIEW

   CREATE VIEW saves the SELECT query so you can reuse it easily without rewriting it.

3. Final Answer:

   To save a SELECT query as a virtual table for easy reuse -> Option B

4. Quick Check:

   CREATE VIEW = virtual table for queries [OK]

Hint: Views save SELECT queries as virtual tables [OK]

Common Mistakes:

• Thinking views store data permanently
• Confusing views with tables
• Using CREATE VIEW to delete data

2. Which of the following is the correct syntax to create a view named active_users showing users with status = 'active'?

easy

A. CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

B. CREATE active_users VIEW AS SELECT * FROM users WHERE status = 'active';

C. VIEW CREATE active_users AS SELECT * FROM users WHERE status = 'active';

D. CREATE VIEW active_users SELECT * FROM users WHERE status = 'active';

Solution

1. Step 1: Recall the correct CREATE VIEW syntax

   The syntax is: CREATE VIEW view_name AS SELECT ...

2. Step 2: Match the syntax with options

   CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'; -> Option A

4. Quick Check:

   CREATE VIEW view_name AS SELECT ... [OK]

Hint: Syntax is CREATE VIEW name AS SELECT ... [OK]

Common Mistakes:

• Swapping CREATE and VIEW keywords
• Omitting AS keyword
• Missing semicolon at the end

3. Given the table orders with columns order_id, customer_id, and amount, what will be the result of this view?

CREATE VIEW big_orders AS SELECT order_id, amount FROM orders WHERE amount > 100;

What rows will SELECT * FROM big_orders; return?

medium

A. No rows, syntax error in view

B. All orders with amount less than or equal to 100

C. All orders regardless of amount

D. All orders with amount greater than 100

Solution

1. Step 1: Analyze the SELECT query inside the view

   The view selects order_id and amount from orders where amount is greater than 100.

2. Step 2: Understand what SELECT * FROM big_orders returns

   It returns all rows from the view, which are orders with amount > 100.

3. Final Answer:

   All orders with amount greater than 100 -> Option D

4. Quick Check:

   View filters orders with amount > 100 [OK]

Hint: View returns rows matching its SELECT query condition [OK]

Common Mistakes:

• Confusing greater than with less than
• Expecting all columns instead of selected ones
• Assuming syntax error without checking carefully

4. Identify the error in this CREATE VIEW statement:

CREATE VIEW recent_customers AS SELECT id, name FROM customers WHERE;

medium

A. Missing condition after WHERE keyword

B. Missing AS keyword before SELECT

C. View name cannot be recent_customers

D. SELECT must include all columns

Solution

1. Step 1: Check the syntax after WHERE

   The WHERE clause is incomplete; it must have a condition after WHERE.

2. Step 2: Verify other parts of the statement

   AS keyword is present, view name is valid, and SELECT columns are allowed to be partial.

3. Final Answer:

   Missing condition after WHERE keyword -> Option A

4. Quick Check:

   WHERE must have a condition [OK]

Hint: WHERE clause must have a condition [OK]

Common Mistakes:

• Leaving WHERE empty
• Forgetting AS keyword
• Thinking view names have restrictions

5. You want to create a view customer_orders that shows each customer's name and the total amount they spent. Which of these is the correct way to create this view assuming tables customers(id, name) and orders(customer_id, amount)?

hard

A. CREATE VIEW customer_orders AS SELECT c.name, o.amount FROM customers c, orders o WHERE c.id = o.customer_id;

B. CREATE VIEW customer_orders AS SELECT name, amount FROM customers JOIN orders ON id = customer_id;

C. CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name;

D. CREATE VIEW customer_orders AS SELECT name, SUM(amount) FROM orders GROUP BY name;

Solution

1. Step 1: Understand the requirement

   We need each customer's name and total amount spent, so we must join customers and orders and sum amounts grouped by customer name.

2. Step 2: Check each option

   CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name; correctly joins tables, sums amounts, and groups by customer name. Others miss grouping or join conditions.

3. Final Answer:

   CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name; -> Option C

4. Quick Check:

   Join + SUM + GROUP BY = correct view [OK]

Hint: Use JOIN with GROUP BY and SUM for aggregation views [OK]

Common Mistakes:

• Missing GROUP BY when using SUM
• Not joining tables properly
• Selecting columns not in GROUP BY