delete all employees who are working with employee(employee name given by user)

Question

Delete all the who are working with that employee, except that Employee.(Prompt for the ENAME). the employees will have same department number.can anyone help me out.

delete from emptest e where e.deptno IN 
(select  f.deptno from emptest f where e.deptno=f.deptno AND
 e.empno<>f.empno AND f.ename='&ename' );

Answer 1

Sample data:

SQL> set ver off
SQL> undefine par_ename
SQL> select * from employee order by deptno, ename;

    DEPTNO ENAME      JOB              SAL
---------- ---------- --------- ----------
        10 CLARK      MANAGER         2450  --> department 10: if KING is a parameter,
        10 KING       PRESIDENT       5001  --> then CLARK and MILLER will be
        10 MILLER     CLERK           1300  --> deleted, but KING won't
        20 ADAMS      CLERK           1100
        20 FORD       ANALYST         3000
        20 JONES      MANAGER         2975
        20 SCOTT      ANALYST         3000
        20 SMITH      CLERK           1000
        30 ALLEN      SALESMAN        1600
        30 BLAKE      MANAGER         2850
        30 JAMES      CLERK            950
        30 MARTIN     SALESMAN        1250
        30 TURNER     SALESMAN        1500
        30 WARD       SALESMAN        1250

14 rows selected.

Query:

SQL> delete from employee e
  2    where e.deptno = (select a.deptno
  3                      from employee a
  4                      where a.ename = '&&par_ename'
  5                     )
  6      and e.ename <> '&&par_ename';
Enter value for par_ename: KING

2 rows deleted.

Result:

SQL> select * from employee order by deptno, ename;

    DEPTNO ENAME      JOB              SAL
---------- ---------- --------- ----------
        10 KING       PRESIDENT       5001  --> only KING remains in dept. 10
        20 ADAMS      CLERK           1100
        20 FORD       ANALYST         3000
        20 JONES      MANAGER         2975
        20 SCOTT      ANALYST         3000
        20 SMITH      CLERK           1000
        30 ALLEN      SALESMAN        1600
        30 BLAKE      MANAGER         2850
        30 JAMES      CLERK            950
        30 MARTIN     SALESMAN        1250
        30 TURNER     SALESMAN        1500
        30 WARD       SALESMAN        1250

12 rows selected.

SQL>