Page 97 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

UPDATE EXAMPLE (more than one column)
UPDATE Persons SET Name = ‘Jayson Cabanglan’, Age = 23 WHERE ID=3;
ID Name Age
1 Christian 22
2 John Mark 22
3 Jayson 23
4 Nigel 22

SQL DELETE Statement
The SQL DELETE statement is used to delete existing records from a table. We can use the
WHERE clause with a DELETE query to delete the selected rows, otherwise all the records
would be deleted.
Syntax: DELETE FROM table_name WHERE condition;

You can combine n number of conditions using the AND or the OR operators.

Example: Consider the “Persons” table having the following records.
ID Name Age
1 Christian 22
2 John Mark 22
3 Jayson 23
4 Nigel 22

DELETE EXAMPLE with WHERE Clause
DELETE FROM Persons WHERE Name=’Christian’;
Now the “Persons” table would have the following records.
ID Name Age
2 John Mark 22
3 Jayson 23
4 Nigel 22

DELETE EXAMPLE without WHERE Clause (Delete all Records)
DELETE FROM Persons;
Now the “Persons” table would become as follow:
ID Name Age

SQL ALTER Statement
The SQL ALTER statement is used to add, delete or modify columns in an existing table. The
ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER ADD Column
Syntax: ALTER TABLE table_name ADD column_name datatype;

ALTER ADD Multiple Column
Syntax: ALTER TABLE table_name ADD (column1_name datatype, column2_name
datatype);

ALTER/MODIFY Column
Syntax: ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Example: Consider the “Persons” table having the following records.

Page | 13