CREATE VIEW syntax in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a VIEW in a database?

A VIEW is a virtual table that shows data from one or more tables. It does not store data itself but displays data based on a query.

beginner

Basic syntax to create a VIEW in PostgreSQL?

Use: <br>CREATE VIEW view_name AS SELECT column1, column2 FROM table_name;<br>This creates a virtual table named view_name based on the SELECT query.

intermediate

Can you update data through a VIEW?

Usually, you cannot update data through a VIEW unless it is a simple view and the database supports updatable views. Complex views often are read-only.

intermediate

What happens if you try to create a VIEW that already exists without using IF NOT EXISTS?

PostgreSQL will return an error saying the view already exists. To avoid this, use CREATE OR REPLACE VIEW to update the existing view or drop it first.

intermediate

Explain the purpose of the CREATE OR REPLACE VIEW statement.

It creates a new view if it doesn't exist or replaces the definition of an existing view without dropping it. This helps update views safely.

Which statement correctly creates a view named 'employee_view' showing 'name' and 'salary' from 'employees' table?

A CREATE VIEW employee_view AS SELECT name, salary FROM employees;

B CREATE TABLE employee_view AS SELECT name, salary FROM employees;

C SELECT name, salary INTO employee_view FROM employees;

D CREATE employee_view VIEW AS SELECT name, salary FROM employees;

Option A uses the correct syntax to create a view in PostgreSQL.

What does a VIEW store in the database?

A Only the query definition

B Actual data rows

C Indexes for fast access

D Temporary data copies

A VIEW stores only the query definition, not the actual data.

How can you modify an existing view without dropping it first?

A CREATE VIEW view_name AS ...

B ALTER VIEW view_name AS ...

C CREATE OR REPLACE VIEW view_name AS ...

D DROP VIEW view_name; CREATE VIEW view_name AS ...

CREATE OR REPLACE VIEW updates the view definition without dropping it.

If you create a view with the same name as an existing one without OR REPLACE, what happens?

A The old view is replaced automatically

B Both views exist with the same name

C The new view is ignored

D An error is raised

PostgreSQL raises an error if a view with the same name exists.

Which of these is NOT a benefit of using views?

A Simplify complex queries

B Store large amounts of data physically

C Improve data security by restricting columns

D Provide a consistent interface to data

Views do not store data physically; they only show data from tables.

Describe how to create a simple view in PostgreSQL and explain what it does.

Think about how a view acts like a saved SELECT query.

Explain the difference between CREATE VIEW and CREATE OR REPLACE VIEW.

Consider what happens if the view already exists.