Scribd
Upload
3 views12 pages

Introduction To SQL - 2

This document provides an introduction to SQL Data Definition Commands, focusing on the ALTER TABLE command for modifying table structures. It covers adding, modifying, renaming, and dropping columns, as well as the TRUNCATE command for deleting all data in a table. Examples illustrate each command's usage, along with a practice exercise for hands-on application.

Uploaded by

sibongakonkentsele8
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views12 pages

Introduction To SQL - 2

This document provides an introduction to SQL Data Definition Commands, focusing on the ALTER TABLE command for modifying table structures. It covers adding, modifying, renaming, and dropping columns, as well as the TRUNCATE command for deleting all data in a table. Examples illustrate each command's usage, along with a practice exercise for hands-on application.

Uploaded by

sibongakonkentsele8
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Introduction to SQL – 2

Data Definition Commands


Outline
• ALTER TABLE command
• Adding, modifying, dropping columns
• Renaming tables and columns
• TRUNCATE command
• Practice
ALTER TABLE
• Used to change table structure
• Add, drop, rename columns
• Modify data types
ALTER TABLE

• Example:
Let’s create a table first, and then alter its structure
ALTER TABLE + ADD COLUMN
• Used to add a new column to a table
• ALTER TABLE table_name
ADD COLUMN column_name data_type;
• Example:
Let’s add a new column last_name to the table_lab2 table

ALTER TABLE table_lab2


ADD COLUMN last_name varchar(30);
ALTER TABLE + ALTER COLUMN
• Used to change the data type, or the size of a table column
• ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;
• Example:
Let’s change the maximum allowed characters for the last_name column from 30
to 50
ALTER TABLE table_lab2
ALTER COLUMN last_name TYPE varchar(50);
ALTER TABLE + ALTER COLUMN
• Note: Some data type conversions are non-implicit and you need to use
the “USING” clause to specify an explicit cast.
• ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type
USING column_name:: new_data_type;
• Example:
Let’s assume we want to change the first_name data type from varchar(30) to
integer (it does not make sense to do that in real life settings)
ALTER TABLE edited_table_lab2
ALTER COLUMN first_name TYPE integer USING first_name :: integer;
ALTER TABLE + RENAME COLUMN
• Used to change the name of a column or the table itself
• ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
• Example:
Let’s rename the age column to last_birthday_age

ALTER TABLE table_lab2


RENAME COLUMN age TO last_birthday_age;
RENAME TABLE
• ALTER TABLE old_table_name
RENAME TO new_table_name;
• Example:
Let’s rename the table from table_lab2 to edited_table_lab2

ALTER TABLE table_lab2


RENAME TO edited_table_lab2;
ALTER TABLE + DROP COLUMN
• Deletes a column
• ALTER TABLE table_name
DROP COLUMN column_name;
• Example:
Let’s drop the last_birthday_age column

ALTER TABLE edited_table_lab2


DROP COLUMN last_birthday_age;
TRUNCATE
• Deletes all the data/rows from the database table but it does not delete
the table structure.
• TRUNCATE TABLE table_name;
To visualize how this command works, we first insert data into the table
and then view (SELECT) the data.
After executing the TRUNCATE command, all the data entered is deleted.
Practice

• Create table lab2_test


• Add column email VARCHAR(100)
• Change email to VARCHAR(150)
• Rename column email to student_email
• Drop student_email

You might also like