ALTER TABLE operations in MySQL - Mini Project: Build & Apply

ALTER TABLE Operations in MySQL

📖 Scenario: You are managing a small online bookstore database. The books table currently has basic information, but you need to update its structure to add new features and improve data management.

🎯 Goal: Learn how to use ALTER TABLE statements to modify an existing table by adding a new column, changing a column's data type, renaming a column, and dropping a column.

📋 What You'll Learn

Create a books table with specified columns

Add a new column published_year of type INT

Modify the price column to have a precision of 6,2

Rename the author column to author_name

Remove the stock column from the table

💡 Why This Matters

🌍 Real World

Database administrators and developers often need to update table structures to accommodate new requirements without losing existing data.

💼 Career

Knowing how to use ALTER TABLE is essential for managing and evolving databases in real-world applications.

1

Create the initial books table

Write a SQL statement to create a table called books with these columns: id as an integer primary key, title as VARCHAR(100), author as VARCHAR(50), price as DECIMAL(5,2), and stock as INT.

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Add a new column published_year

Write a SQL ALTER TABLE statement to add a new column called published_year of type INT to the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(50),
  price DECIMAL(5,2),
  stock INT
);

-- Your code here to add published_year column

Need a hint?

Use ALTER TABLE books ADD COLUMN published_year INT;

3

Modify the price column data type

Write a SQL ALTER TABLE statement to change the price column in the books table to have the data type DECIMAL(6,2).

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(50),
  price DECIMAL(5,2),
  stock INT
);

ALTER TABLE books ADD COLUMN published_year INT;

-- Your code here to modify price column

Need a hint?

Use ALTER TABLE books MODIFY COLUMN price DECIMAL(6,2); to change the column type.

4

Rename author column and drop stock column

Write two SQL ALTER TABLE statements: one to rename the author column to author_name, and another to drop the stock column from the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(50),
  price DECIMAL(5,2),
  stock INT
);

ALTER TABLE books ADD COLUMN published_year INT;

ALTER TABLE books MODIFY COLUMN price DECIMAL(6,2);

-- Your code here to rename author column and drop stock column

Need a hint?

Use CHANGE COLUMN to rename and DROP COLUMN to remove a column.