Updatable views and limitations in SQL - Mini Project: Build & Apply

Creating and Using Updatable Views in SQL

📖 Scenario: You work in a small bookstore's database team. The store wants to simplify how employees see book information by creating a view that shows only the book title and price. They also want to update prices through this view.

🎯 Goal: Build an updatable view that shows book titles and prices, then update a price through the view.

📋 What You'll Learn

Create a table named books with columns id, title, and price.

Insert three specific books into the books table.

Create a view named book_prices that shows only title and price from books.

Update the price of a book through the book_prices view.

💡 Why This Matters

🌍 Real World

Updatable views simplify data access for users by showing only relevant columns and allowing updates without exposing the full table.

💼 Career

Database developers and administrators often create views to control data visibility and maintain data integrity while allowing controlled updates.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), and price (numeric). Then insert these three rows exactly: (1, 'The Great Gatsby', 10.99), (2, '1984', 8.99), and (3, 'To Kill a Mockingbird', 12.50).

SQL

-- Create the books table and insert three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the book_prices view

Create a view called book_prices that selects only the title and price columns from the books table.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Great Gatsby', 10.99),
  (2, '1984', 8.99),
  (3, 'To Kill a Mockingbird', 12.50);

-- Create the book_prices view selecting title and price
-- Your code here

Hint

Use CREATE VIEW view_name AS SELECT ... syntax.

3

Update a book price through the view

Write an UPDATE statement to change the price of '1984' to 9.99 using the book_prices view.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Great Gatsby', 10.99),
  (2, '1984', 8.99),
  (3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT title, price FROM books;

-- Update the price of '1984' to 9.99 using the book_prices view
-- Your code here

Hint

Use UPDATE view_name SET column = value WHERE condition.

4

Explain a limitation of updatable views

Add a comment explaining one limitation of updatable views in SQL, such as why views with joins or aggregates are not updatable.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Great Gatsby', 10.99),
  (2, '1984', 8.99),
  (3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT title, price FROM books;

UPDATE book_prices SET price = 9.99 WHERE title = '1984';

-- Add a comment explaining a limitation of updatable views
-- Your code here

Hint

Write a SQL comment starting with -- explaining a common limitation.

Practice

1. Which of the following best describes an updatable view in SQL?

easy

A. A view created using multiple tables joined together.

B. A view that only shows data but does not allow any changes.

C. A view that contains aggregate functions like SUM or COUNT.

D. A view that allows you to insert, update, or delete rows through it.

Solution

1. Step 1: Understand what an updatable view is

   An updatable view lets you change data (insert, update, delete) through the view as if it were a table.

2. Step 2: Compare options to definition

   Options B, C, and D describe views that are either read-only or complex and usually not updatable.

3. Final Answer:

   A view that allows you to insert, update, or delete rows through it. -> Option D

4. Quick Check:

   Updatable view = allows data changes [OK]

Hint: Updatable views let you change data through them [OK]

Common Mistakes:

• Thinking all views allow data changes
• Confusing aggregate views as updatable
• Assuming joins always allow updates

2. Which SQL syntax correctly creates a simple updatable view on a single table employees showing id and name?

easy

A. CREATE VIEW emp_view AS SELECT id, name FROM employees GROUP BY id;

B. CREATE VIEW emp_view AS SELECT id, name, COUNT(*) FROM employees;

C. CREATE VIEW emp_view AS SELECT id, name FROM employees;

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

Solution

1. Step 1: Identify simple view syntax

   CREATE VIEW emp_view AS SELECT id, name FROM employees; selects columns directly from one table without grouping or joins, which is valid for an updatable view.

2. Step 2: Check other options for complexity

   Options A and C use GROUP BY or aggregate functions, making views non-updatable. CREATE VIEW emp_view AS SELECT id, name FROM employees JOIN departments ON employees.dept_id = departments.id; uses JOIN, also usually non-updatable.

3. Final Answer:

   CREATE VIEW emp_view AS SELECT id, name FROM employees; -> Option C

4. Quick Check:

   Simple select from one table = updatable view syntax [OK]

Hint: Simple SELECT without joins or aggregates creates updatable views [OK]

Common Mistakes:

• Adding GROUP BY or aggregates in view
• Using JOINs in view definition
• Forgetting to select from only one table

3. Given the view definition:

CREATE VIEW dept_salary AS SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;

Which of the following statements is true when trying to update avg_salary through this view?

medium

A. You can update avg_salary directly and it will change salaries in employees.

B. You cannot update avg_salary because the view uses aggregation.

C. Updating avg_salary will update all salaries in the department equally.

D. The view allows updates only if you use INSTEAD OF triggers.

Solution

1. Step 1: Analyze the view's use of aggregation

   The view uses AVG() and GROUP BY, which creates a summary, not individual rows.

2. Step 2: Understand update limitations on aggregated views

   Aggregated columns like avg_salary cannot be updated directly because they do not map to single rows.

3. Final Answer:

   You cannot update avg_salary because the view uses aggregation. -> Option B

4. Quick Check:

   Aggregated views are not updatable by default [OK]

Hint: Aggregated views block direct updates [OK]

Common Mistakes:

• Trying to update aggregate columns
• Assuming updates affect underlying rows automatically
• Ignoring need for triggers on complex views

4. You have a view defined as:

CREATE VIEW emp_dept AS SELECT e.id, e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;

Attempting to update dept_name through this view causes an error. What is the main reason?

medium

A. Views with JOINs are not updatable by default.

B. You cannot update columns with aliases in views.

C. The view lacks a WHERE clause to filter rows.

D. The view must include all columns from both tables to be updatable.

Solution

1. Step 1: Identify the view's structure

   The view uses a JOIN between employees and departments tables.

2. Step 2: Recall update limitations on joined views

   Views involving JOINs are generally not updatable because the database cannot determine which table to update.

3. Final Answer:

   Views with JOINs are not updatable by default. -> Option A

4. Quick Check:

   JOIN views block updates unless special rules apply [OK]

Hint: JOINs in views block updates by default [OK]

Common Mistakes:

• Thinking alias columns block updates
• Believing WHERE clause affects updatability
• Assuming all columns must be selected

5. You want to create an updatable view that allows changes to employee names and salaries but also shows department names. Which approach is best?

hard

A. Create a view joining employees and departments, then use INSTEAD OF triggers to handle updates.

B. Create a simple view on employees only and join departments in queries outside the view.

C. Create a view with GROUP BY department to summarize salaries and allow updates.

D. Create a view with aggregate functions and update the aggregates directly.

Solution

1. Step 1: Understand the requirement

   You want to update employee data but also show department names, which requires joining tables.

2. Step 2: Recognize limitations of joined views

   Joined views are not updatable by default, but INSTEAD OF triggers can enable updates by defining custom behavior.

3. Step 3: Evaluate other options

   Create a simple view on employees only and join departments in queries outside the view. avoids joins in the view but does not show department names in the view itself. Options C and D use aggregates, which block updates.

4. Final Answer:

   Create a view joining employees and departments, then use INSTEAD OF triggers to handle updates. -> Option A

5. Quick Check:

   Use triggers to update joined views [OK]

Hint: Use INSTEAD OF triggers to update joined views [OK]

Common Mistakes:

• Trying to update aggregates directly
• Ignoring triggers for complex views
• Using simple views that lack needed columns