Computer Science · Class 12 · Computer Networks and Connectivity · Term 1

SQL DDL Commands: ALTER TABLE

Students will practice using ALTER TABLE commands to modify existing table structures, including adding, dropping, and modifying columns.

• CBSE Learning Outcomes
• CBSE: Database Management - Structured Query Language - Class 12

About This Topic

The ALTER TABLE command in SQL enables precise modifications to existing table structures, a key skill in database management for Class 12 CBSE students. They practise adding columns with data types and defaults, such as ALTER TABLE students ADD COLUMN email VARCHAR(50) DEFAULT '[e-mail]'; dropping columns with ALTER TABLE students DROP COLUMN phone; and modifying attributes like data types or constraints. These operations maintain data integrity while adapting to evolving requirements.

This topic connects to the broader Structured Query Language unit, reinforcing CREATE TABLE knowledge and preparing students for Data Manipulation Language. They analyse impacts, for instance, converting VARCHAR to INT may truncate strings or fail, risking data loss. Constructing queries and predicting outcomes builds analytical thinking essential for real-world database administration in Indian IT sectors.

Active learning suits this topic perfectly. Students execute commands on sample databases like school records, observe real-time errors, and verify changes with SELECT statements. Collaborative debugging turns syntax challenges into shared successes, making abstract DDL concepts tangible and retention stronger.

Key Questions

1. Explain the various uses of the ALTER TABLE command.
2. Construct SQL queries to add a new column with a default value to an existing table.
3. Analyze the potential impact of modifying a column's data type on existing data.

Learning Objectives

• Construct SQL queries to add a new column, specifying its data type and a default value, to an existing table.
• Analyze the consequences of altering a column's data type, predicting potential data loss or conversion errors.
• Modify an existing table structure by dropping a specified column using the ALTER TABLE command.
• Explain the purpose and practical applications of the ALTER TABLE command in database schema management.

Before You Start

SQL CREATE TABLE Command

Why: Students must understand how to create tables initially to be able to modify them later.

SQL Data Types

Why: Knowledge of various data types (e.g., INT, VARCHAR, DECIMAL) is essential for specifying column definitions when adding or modifying them.

Key Vocabulary

ALTER TABLE	An SQL command used to modify the structure of an existing table in a database. It allows for changes like adding, deleting, or modifying columns.
ADD COLUMN	A clause within the ALTER TABLE command used to introduce a new column to a table, specifying its name, data type, and optional constraints.
DROP COLUMN	A clause within the ALTER TABLE command used to remove an existing column from a table, along with all its associated data.
MODIFY COLUMN	A clause within the ALTER TABLE command used to change the definition of an existing column, such as its data type, size, or constraints.
DEFAULT constraint	A rule that assigns a default value to a column when no explicit value is provided during the insertion of a new row.

Watch Out for These Misconceptions

Common Misconception

ALTER TABLE deletes all data in the table.

What to Teach Instead

ALTER TABLE changes only the structure; data persists unless a DROP COLUMN removes it entirely. Small group executions on test data reveal this, as students query before and after to compare row counts and values.

Common Misconception

Any data type change works without issues.

What to Teach Instead

Incompatible changes, like VARCHAR to INT, cause truncation or errors on existing data. Hands-on trials with varied sample data help students observe failures and learn to use safe conversions or backups first.

Common Misconception

ALTER commands apply to every table in the database.

What to Teach Instead

Commands target a specific table named in the syntax. Pairs testing on multiple tables clarify this, reducing confusion through immediate feedback from error messages.

Active Learning Ideas

Pair Programming: ALTER Modifications

Pairs share a computer with a sample 'employees' table. One partner writes an ALTER TABLE query to add or modify a column based on a prompt, the other predicts the outcome. They execute, check with SELECT, and switch roles after three queries.

30 min·Pairs

Small Groups: Scenario-Based Table Updates

Provide groups with a 'library_books' table and printed scenarios, such as adding a 'publish_date' column with default or changing 'price' to DECIMAL. Groups write and test queries, document impacts, then share one challenge with the class.

45 min·Small Groups

Whole Class: Guided Demo Relay

Teacher demonstrates an ALTER on a shared projected database. Class calls out next steps to add/drop/modify, executes as a group, and discusses errors. Students replicate independently on their systems.

40 min·Whole Class

Individual: Error Hunt Challenges

Distribute worksheets with flawed ALTER queries on a 'students' table. Students identify errors, correct them in SQL tools, and note data impacts before submitting screenshots.

25 min·Individual

Real-World Connections

• Database administrators at e-commerce companies like Flipkart regularly use ALTER TABLE to add new product attributes (e.g., 'warranty_period') or remove outdated ones ('old_discount_code') as business needs evolve.
• Software developers working on banking applications use ALTER TABLE to update customer record schemas, perhaps adding a new field for 'preferred_communication_channel' or modifying the 'account_balance' data type to accommodate larger values.

Assessment Ideas

Quick Check

Present students with a scenario: 'A school database has a 'students' table with columns 'student_id', 'name', and 'marks'. Add a new column 'grade' of type VARCHAR(2) with a default value of 'N/A'.' Ask students to write the SQL query to achieve this.

Exit Ticket

Give students a pre-existing table definition (e.g., 'CREATE TABLE employees (emp_id INT, emp_name VARCHAR(50));'). Ask them to write two separate ALTER TABLE commands: one to add a 'salary' column (DECIMAL(10,2)) and another to drop the 'emp_name' column. Collect these for review.

Discussion Prompt

Pose this question: 'If you have a table with a 'phone_number' column stored as VARCHAR(10) and you need to change it to INT to perform calculations, what potential problems might arise with the existing data? Discuss the risks.' Facilitate a class discussion on data type conversion impacts.

Frequently Asked Questions

What are the main uses of ALTER TABLE command in SQL?

ALTER TABLE modifies existing tables by adding columns (ADD COLUMN), dropping columns (DROP COLUMN), changing data types (MODIFY COLUMN), adding defaults, or renaming. Students use it to adapt schemas without data recreation, vital for dynamic databases. Practice ensures they handle real scenarios like updating student records with new fields such as Aadhaar number.

How to add a new column with default value using ALTER TABLE?

Use syntax: ALTER TABLE table_name ADD COLUMN column_name datatype DEFAULT value;. For example, ALTER TABLE students ADD COLUMN grade CHAR(1) DEFAULT 'A';. This adds the column with the default for existing rows. Verify with SELECT to confirm, a step that prevents oversight in class exercises.

What is the impact of modifying a column's data type on existing data?

Modifying data type with ALTER TABLE MODIFY COLUMN may truncate, convert, or error on incompatible data, like shortening VARCHAR(100) to VARCHAR(10). Always back up data first. Students analyse this through test queries, learning to check compatibility and use temporary columns for safe migrations.

How does active learning help students master ALTER TABLE commands?

Active learning involves executing ALTER queries on live databases, providing instant feedback on syntax errors and data effects. Pairs or groups debug collaboratively, predict outcomes before running, and verify with SELECT, turning passive memorisation into practical skill-building. This approach boosts confidence for CBSE practical exams and real database tasks.