Alterations & Maintenance

This section demonstrates how to dynamically modify an existing database blueprint using Data Definition Language (DDL) statements like ALTER TABLE.

---

1. Structural Modifications Lifecycle

USE college;

CREATE TABLE student2 (
    rollno INT PRIMARY KEY,
    name VARCHAR (50),
    marks INT NOT NULL,
    grade VARCHAR(1),
    city VARCHAR(20)
);

-- Rename operations
ALTER TABLE student2 RENAME TO students;

-- Column renaming & layout transformation
ALTER TABLE students CHANGE COLUMN rollno id INT;
ALTER TABLE students CHANGE COLUMN name full_name varchar(20);

-- Populate modified framework
INSERT INTO students
(id, full_name, marks, grade, city)
VALUES
(101, "anil", 78, "C", "Pune"),
(102, "bhumika", 93, "A", "Mumbai"),
(103, "chetan", 85, "B", "Mumbai"),
(104, "dhruv", 96, "A", "Delhi"),
(105, "emanuel", 12, "F", "Delhi"),
(106, "farah", 82, "B", "Delhi");

2. Dynamic Column Control

-- Add structural columns with fallbacks
ALTER TABLE students ADD COLUMN age int not null default (19);

-- Modify existing definitions
ALTER TABLE students MODIFY age int default (19);

-- Drop components
ALTER TABLE students DROP COLUMN age;
3. Data Purging Mechanisms
SQL
-- Targeted conditional row removal
DELETE FROM students WHERE marks < 80;

-- Fast, complete structural data wipe
TRUNCATE TABLE students;

-- Verification query
SELECT * FROM students;