oracle几个经典题目

select * from scott.emp where deptno in (select deptno from scott.dept where dname='SALES');
select * from scott.emp where exists (select 1 from scott.dept where emp.deptno=dept.deptno and dname='SALES')

--删除表中的重复值（主要是弄清rowid）
 delete from emp_bak e
  where e.rowid >
        (select min(x.rowid) from emp_bak x where x.empno = e.empno)

  delete from emp_bak
   where rowid not in (select max(rowid) from emp_bak group by empno)

  select *  from emp_bak
   where empno  in (select empno from emp_bak group by empno)

select t.rowid, t.* from emp_bak t


select * from emp_bak

create table emp_bak1
as select * from emp_bak


select * from emp_bak1

update emp_bak1 set sal = sal + 1000

update emp_bak1 set comm = nvl(comm ,0 ) + 500

 

把一个表的值覆盖另一个表中的值
  update emp_bak b
     set b.sal =
         (select sal from emp_bak1 a where a.empno = b.empno),
         b.comm =
         (select comm from emp_bak1 a where a.empno = b.empno)