The DELETE operator is used to erase records (not table structure). The number of records deleted may be 0, 1, or many, depending on how many satisfy the predicate.
Format:
DELETE FROM table/view WHERE delete_criteria;
Example 1
Erase the record of student ‘S1020’ (Delete a single tuple)
DELETE FROM STUDENT WHERE STUID = ‘S1020’;
Example 2
Erase all enrollment records for student ‘S1020’. (Delete several tuples).
DELETE FROM ENROLL WHERE STUID = ‘S1020’;
Example 3
Erase all the class records. (Deleting all the tuples from a table)
DELETE FROM CLASS;
Note that the table CLASS still exists, but is empty. To remove the data and the table you use the DROP TABLE operator.
Example 4
Erase all enrollment records for Owen McCarthy. (Delete with a subquery)
DELETE FROM ENROLL WHERE STUID = (SELECT STUID FROM STUDENT WHERE FNAME = ‘Owen’ AND LNAME = ‘McCarthy’);
In our database there is no such student, so no records are deleted from ENROLL.