每天学点Oracle——查询基础练习

表结构如图：

select * from EMP;
select * from DEPT;

1.1 列出至少有两个员工的部门。

select Deptno,count(1)  from EMP E
group by E.Deptno
having count(1)>1

1.2 查询工资大于或者等于2000 的员工信息

select * from emp
where SAL >= 2000

1.3 查询至少有两个员工的工资不低于2000的部门编号

select deptno  from emp 
where deptno in (select t2.de from
(select t1.deptno de, count(1) from emp t1
where t1.sal >= 2000
group by t1.deptno
having count(1) > 1 ) t2)
group by deptno

1.4 查询各部门两个最低工资的员工部门、名字、薪水。

select deptno, ename, sal      
from emp t1     
where      
   (  
    select count(*)     
    from emp t2     
    where t2.deptno=t1.deptno and t2.sal<t1.sal  
   ) <2 

2.1 列出薪金比“SMITH”多的所有员工。

select * from emp
where sal > (select sal from emp where ename = 'SMITH') 

2.2 查询工资 与 SCOTT 相同的员工信息

select * from emp
where sal = (select sal from emp where ename &#