[Solved] Consider a view CREATE VIEW vip_customers AS SELECT * FROM customers WHERE vip = true WITH CHECK... Which of the following statements correctly inserts a VIP customer and rejects a non-VIP customer? | PostgreSQL

PostgreSQL - Views and Materialized Views

Consider a view CREATE VIEW vip_customers AS SELECT * FROM customers WHERE vip = true WITH CHECK OPTION;
You want to allow inserting new customers through this view only if they are VIPs. Which of the following statements correctly inserts a VIP customer and rejects a non-VIP customer?

A

INSERT INTO vip_customers (id, name, vip) VALUES (1, 'John', false); -- fails<br>INSERT INTO vip_customers (id, name, vip) VALUES (2, 'Jane', false); -- fails

B

INSERT INTO vip_customers (id, name, vip) VALUES (1, 'John', true); -- succeeds<br>INSERT INTO vip_customers (id, name, vip) VALUES (2, 'Jane', false); -- fails

C

INSERT INTO vip_customers (id, name, vip) VALUES (1, 'John', true); -- fails<br>INSERT INTO vip_customers (id, name, vip) VALUES (2, 'Jane', false); -- succeeds

D

INSERT INTO vip_customers (id, name, vip) VALUES (1, 'John', false); -- succeeds<br>INSERT INTO vip_customers (id, name, vip) VALUES (2, 'Jane', true); -- fails

Step-by-Step Solution

Solution:

Step 1: Understand the view filter and CHECK OPTION
The view only allows rows where vip = true. WITH CHECK OPTION blocks inserts with vip = false.
Step 2: Analyze each insert statement
Inserting John with vip = true succeeds. Inserting Jane with vip = false fails due to CHECK OPTION.
Final Answer:
Only inserts with vip = true succeed; others fail -> Option B
Quick Check:
Insert vip=true allowed, vip=false blocked = D [OK]

Quick Trick: Only insert rows matching view filter pass CHECK OPTION [OK]

Common Mistakes:

Trying to insert non-VIP through VIP view
Assuming all inserts succeed
Confusing which values pass CHECK OPTION

Master "Views and Materialized Views" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Consider a view CREATE VIEW vip_customers AS SELECT * FROM customers WHERE vip = true WITH CHECK OPTION;

Step 1: Understand the view filter and CHECK OPTION

Step 2: Analyze each insert statement

Final Answer:

Quick Check:

Want More Practice?