View limitations in MySQL - Mini Project: Build & Apply

Understanding View Limitations in MySQL

📖 Scenario: You are managing a small bookstore database. You want to create a view to simplify access to book sales data, but you need to understand what limitations exist when using views in MySQL.

🎯 Goal: Create a view that shows book titles and their total sales, then explore the limitations of updating data through this view.

📋 What You'll Learn

Create a table called books with columns book_id, title, and author.

Create a table called sales with columns sale_id, book_id, and quantity.

Create a view called book_sales that shows title and total quantity sold for each book.

Attempt to update the view and observe the limitations.

💡 Why This Matters

🌍 Real World

Views help simplify complex queries and present data in a user-friendly way, but knowing their limitations prevents errors in database operations.

💼 Career

Database administrators and developers must understand view limitations to design efficient and maintainable databases.

1

Create the books and sales tables

Create a table called books with columns book_id as INT primary key, title as VARCHAR(100), and author as VARCHAR(100). Then create a table called sales with columns sale_id as INT primary key, book_id as INT, and quantity as INT.

MySQL

-- Your code here to create the books and sales tables

Need a hint?

Use CREATE TABLE statements with the specified columns and types.

2

Insert sample data into books and sales

Insert these exact rows into books: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'). Insert these exact rows into sales: (1, 1, 3), (2, 2, 5), (3, 1, 2).

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  book_id INT,
  quantity INT
);

-- Your code here to insert sample data

Need a hint?

Use INSERT INTO with the exact values given.

3

Create a view book_sales showing titles and total sales

Create a view called book_sales that selects title from books and the sum of quantity from sales grouped by title.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  book_id INT,
  quantity INT
);

INSERT INTO books (book_id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2);

-- Your code here to create the view book_sales

Need a hint?

Use CREATE VIEW with a JOIN and GROUP BY to sum sales per book title.

4

Attempt to update the book_sales view and observe limitations

Write an UPDATE statement that tries to change the total_quantity in the book_sales view for the book titled '1984' to 10. Note that MySQL will not allow this update because the view contains aggregation.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  book_id INT,
  quantity INT
);

INSERT INTO books (book_id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2);

CREATE VIEW book_sales AS
SELECT b.title, SUM(s.quantity) AS total_quantity
FROM books b
JOIN sales s ON b.book_id = s.book_id
GROUP BY b.title;

-- Your code here to attempt updating the view

Need a hint?

Try to update the view using UPDATE book_sales SET total_quantity = 10 WHERE title = '1984' and observe the error MySQL gives.