1. Deletion is expressed in much the same way as a query. Instead of displaying, the selected tuples are removed from the database. We can only delete whole tuples.

2. A deletion in SQL is of the form

delete from r
where P

Tuples in r for which P is true are deleted. If the where clause is omitted, all tuples are deleted.

3. The request delete from loan deletes all tuples from the relation loan.

4. Some more examples:

(a) Delete all of Smith's account records.

delete from depositor
where cname="Smith"

(b) Delete all loans with loan numbers between 1300 and 1500.

delete from loan
where loan# between 1300 and 1500

(c) Delete all accounts at branches located in Surrey.

delete from account
where bname in
(select bname
from branch
where bcity="Surrey")

5. We may only delete tuples from one relation at a time, but we may reference any number of relations in a select-from-where clause embedded in the where clause of a delete.

6. However, if the delete request contains an embedded select that references the relation from which tuples are to be deleted, ambiguities may result.

For example, to delete the records of all accounts with balances below the average, we might write

delete from account
where balance < (select avg(balance) from account)

You can see that as we delete tuples from account, the average balance changes!

Solution: The delete statement rst test each tuple in the relation account to check whether the account
has a balance less than the average of the bank. Then all tuples that fail the test are deleted. Perform
all the tests (and mark the tuples to be deleted) before any deletion then delete them en masse after the evaluations!