Deletion

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:
   1. Delete all of Smith's account records.

       delete from depositor
      
      		 where cname=``Smith''
      
      

   2. Delete all loans with loan numbers between 1300 and 1500.

       delete from loan
      
      		 where loan# between 1300 and 1500
      
      

   3. 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 first 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!