orcl SQL简单查询语句

取消重复行
selcet distinct deptno from emp;


带条件查询
查询2000年3月20日之后雇佣的雇员


selcet empno,hiredate,
from emp
where hiredate>'20-3-00'


查询工资在11000元以上，病授予了奖金的雇员
select ename 姓名，sal 工资,comm 奖金
from emp
where sal >= 11000 and comm is not null;


查询10部门和20号部门的故们员工信息


select ename 
from emp
where deptno in (10,20);


查询enamel中以sc开头的雇员信息


select ename
from emp
where ename like 'sc%';


查询工资在11000-13000之间的雇员信息，要求查询结果按工资
降序排列，然后再按雇佣日期生序排列。
select ename,sal,hiredate
from emp
where sal between 2000 and 1000 order by sal desc,hiredate;




按部门查询员工的最低工资和最高工资
select deptno,max(sal),min(sal)
from emp
group by deptno


查询部门编码在50以下且平均工资高于6000的各部门编码、最高工资和最低工资、
平均工资
selcet deptno,max(sal),avg(sal)
from emp
where deptno <= 50
group by deptno
having avg(sal) >= 6000




查看工资高于2500的雇员及所在部门名
select ename,sal,emp.deptno,dname
from emp,dept
where sal > 2500 and emp.deptno = dept.deptno


查看部门30中每个雇员的上司的名字
select wkr.ename 员工，mgr.ename 经理名
from emp wkr,emp mgr
and wdr.deptno = 30;


修改emp表，讲7654雇员调整为与7834雇员一个部门
update part_emp
set deptno = (select deptno from part_emp where empno = 7934)
where empno = 7654;


删除工资高于平均工资的雇员


Delete from emp
where sal > (select avg(sal) from emp);


Delete from emp
where dal > (select sag(sal) from emp);


Delete from emp
where sal > (select sag(sal) from emp);


查询工资低于所有salesman的人员信息
selcet emptno,emname,jon,sal from emp
where sal < all(select avg(sal) from emp where job='salesman')
and job = 'salesman'


查看工资高于他所在部门平均工资的雇员
select deptno,ename,sal 
from emp outer
where sal > (select avg(sal) from emp where deptno = outer.deptno)
order by deptno;