DELETE OPERATIONS used in SQL

DELETE OPERATIONS
The verb DELETE in SQL is used to remove rows from table.

To remove
All the rows from a table. Or
A select set of rows from a table.

1) Delete all rows from the table
SYNTAX: -
DELETE FROM tablename;
EXAMPLE: -
SQL> delete from banking;


2) Removal of specified rows from the table.

SYNTAX: -
DELETE FROM tablename WHERE search condition;

EXAMPLE: -
SQL> delete from banking where amount<80000;

UPDATING THE CONTENTS OF A TABLE
The UPDATE command is used to change or modify data values in a table.

To update
All the rows from the table Or
A select set of rows from a table.

Updating of all rows:
SYNTAX: -
UPDATE tablename
SET columnname=expression, columnname=expression;

Updating records conditionally:

SYNTAX: -
UPDATE tablename
SET columnname=expression, columnname=expression…….
WHERE columnname=expression;

MODIFYING THE STRUCTURE OF THE TABLE
This command is used to add new columns or to change the data type and size of columns.
The verb ALTER in SQL is used to modify the table structure.
Adding new columns:

SYNTAX: -
ALTER TABLE tablename
ADD(new columnname datatype(size),new columnname datatype(size));

EXAMPLE: -
SQL> alter table banking add(branchno number(10,0),fathername varchar2(20));

Table altered.

Modifying existing columns:

SYNTAX: -
ALTER TABLE tablename
MODIFY (columnname new datatype (newsize));

EXAMPLE: -
SQL> alter table banking modify (branchno varchar2(10));

RENAMING TABLE

This command is used to rename the table.
SYNTAX: -
RENAME oldtablename TO newtablename;
EXAMPLE: -
SQL> rename banking to banking1;

Table renamed.

DESTROYING TABLES
This command is used to destroy the table.

SYNTAX: -
DROP TABLE tablename;
EXAMPLE: -
SQL> drop table banking1;

Table dropped.