DELETE , ALTER & DROP Command

DELETE

While working with tables, one may reach a situation where he/she no longer needs some rows of data. In such a case one would like to remove such rows. This can be done by using the DELETE command. The DELETE command removes rows from a table. This removes the entire rows, not individual field values so no, field argument is needed or accepted. The DELETE statement takes the following general form:

DELETE FROM <tablename>
WHERE condition;

delete from students
where Name = 'Emily';

The first row having the name Emily has been deleted.

The entire data can be deleted from the table using the following query.

delete from students;

The table would now be empty and could be destroyed using the DROP Table command.

DROP TABLE <tablename>;

ALTER TABLE

When we define a system, we define what data we need to store, the size and data type of the data. But what to do when the requirements change? Alter table command comes to the rescue. The command is used to change definitions of existing tables. Usually it can add columns to a table. Sometimes it can delete columns or change their sizes.

It can be used to :

1. Add a column
2. Add an integrity constraint
3. Redefine a column

Adding columns

ALTER TABLE <table name> 
ADD <Column name> <data type> <size> [<constraint name>];

alter table students
add (Marks integer);

insert into students values(8, 'Clark', 9 , 'A', 'History', 100);

select * from students;

update students
set Marks = 90
Where Roll = 3;

Modify column

ALTER TABLE <table name> 
MODIFY (column name newdatatype (newsize))

Change column name

ALTER TABLE <table name> 
CHANGE [Column] old_name new_name new_definition;

alter table students
change Marks Score integer;

Removing components

ALTER <tablename> DROP <column name>;