Dropping and altering views in MySQL - Mini Project: Build & Apply

Dropping and Altering Views in MySQL

📖 Scenario: You are managing a small online bookstore database. You have created a view to show the list of books with their authors and prices. Now, you need to update this view to include the book's publication year and also learn how to remove the view when it is no longer needed.

🎯 Goal: Build and modify a MySQL view step-by-step: first create a view, then alter it by dropping and recreating it with additional columns, and finally drop the view completely.

📋 What You'll Learn

Create a view named book_list showing title, author, and price from the books table

Create a variable view_name holding the name of the view

Drop the existing book_list view

Recreate the book_list view including publication_year along with the original columns

Drop the book_list view completely at the end

💡 Why This Matters

🌍 Real World

Views help organize and simplify complex queries in real-world databases, making it easier for users to access specific data without writing complex SQL every time.

💼 Career

Database administrators and developers often create, modify, and drop views to maintain efficient and clear data access layers in applications.

1

Create the initial view

Write a SQL statement to create a view called book_list that selects title, author, and price columns from the books table.

MySQL

-- Create the view book_list with title, author, and price
-- Your code here

Need a hint?

Use CREATE VIEW view_name AS SELECT ... FROM table_name; syntax.

2

Set the view name variable

Create a SQL variable called @view_name and set it to the string 'book_list'.

MySQL

CREATE VIEW book_list AS
SELECT title, author, price
FROM books;

-- Set the variable @view_name to 'book_list'
-- Your code here

Need a hint?

Use SET @variable_name = 'value'; to assign a string to a variable.

3

Drop and recreate the view with publication year

Write SQL statements to drop the view named book_list and then recreate it to include publication_year along with title, author, and price from the books table.

MySQL

CREATE VIEW book_list AS
SELECT title, author, price
FROM books;

SET @view_name = 'book_list';

-- Drop the view book_list
-- Recreate the view book_list with publication_year included
-- Your code here

Need a hint?

Use DROP VIEW view_name; to remove a view before recreating it.

4

Drop the view completely

Write a SQL statement to drop the view named book_list completely from the database.

MySQL

CREATE VIEW book_list AS
SELECT title, author, price
FROM books;

SET @view_name = 'book_list';

DROP VIEW book_list;

CREATE VIEW book_list AS
SELECT title, author, price, publication_year
FROM books;

-- Drop the view book_list completely
-- Your code here

Need a hint?

Use DROP VIEW view_name; to remove the view.