Computer Science · Class 12

Active learning ideas

SQL DDL Commands: ALTER TABLE

Students often struggle to visualise how table structures evolve without losing existing data. Active learning through SQL commands lets them practise safe modifications on sample tables, turning abstract syntax into tangible results they can query and verify immediately.

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

• 25–45 min
• Pairs → Whole Class
• 4 activities

Activity 01

• Experiential Learning
• 30 min · Pairs

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.

Explain the various uses of the ALTER TABLE command.

Facilitation Tip

During Pair Programming, ask students to switch roles after every two commands so both partners actively write and explain the changes.

What to look for

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.

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

Activity 02

• Experiential Learning
• 45 min · Small Groups

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.

Construct SQL queries to add a new column with a default value to an existing table.

Facilitation Tip

In Small Groups, provide a printed scenario sheet with error-filled sample queries; teams must debug and rewrite them before running on the live database.

What to look for

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.

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

Activity 03

• Experiential Learning
• 40 min · Whole Class

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.

Analyze the potential impact of modifying a column's data type on existing data.

Facilitation Tip

For the Guided Demo Relay, prepare a slide with the first ALTER command and blank lines; after each student types the next part, run the command and show the updated schema.

What to look for

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.

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

Activity 04

• Experiential Learning
• 25 min · Individual

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.

Explain the various uses of the ALTER TABLE command.

Facilitation Tip

In Error Hunt Challenges, include a hidden note in one query that uses a reserved keyword as a column name, so students learn to scan syntax carefully.

What to look for

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.

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

A few notes on teaching this unit

Teach ALTER TABLE by starting with a one-column table and gradually adding constraints, ensuring students see how each clause affects inserts and updates. Avoid teaching all clauses at once; focus on ADD COLUMN, DROP COLUMN, and MODIFY COLUMN first, as these cover 80% of real use cases. Research shows that students grasp DDL better when they compare before-and-after schemas side by side, so always display the table structure before and after each command.

By the end of the session, every student should confidently execute ALTER TABLE commands that add, modify, or remove columns without corrupting data, and justify each change with a brief explanation of its impact on the table’s integrity.

---

Watch Out for These Misconceptions

• During Pair Programming, some students may write 'ALTER TABLE *' thinking it affects all tables. Ask them to run the command and note the syntax error, then explicitly write 'ALTER TABLE table_name ...' on the board.

  During Small Groups, give each team a list of three table names and ask them to write identical ALTER commands for each; students soon notice the error when they try to run 'ALTER TABLE *' on the third table.

---

Methods used in this brief

• Experiential Learning