Sub query in the UPDATE command:

Example:  //Double  the  commission  for  employees,  who  have  got  at  least  2 increments.

UPDATE EMP

SET COMM = COMM * 2

WHERE 2 <= (   SELECT COUNT (*) FROM INCR WHERE INCR.EMPNO = EMP.EMPNO

GROUP BY EMPNO);

Be advised that the use of subquery that counts the number of increments given to every employee stored in the INCR table. Please note the comparison, instead of ......>=2, we have written reverse of it as 2 <= .....

DELETE Command

In the following example, the deletion will be performed in EMP table.No deletion will be performed in the INCR table.

Example: Delete the records of employees who have got no increment.

DELETE FROM EMP

WHERE EMPNO NOT IN (SELECT EMPNO FROM INCR);