• 查询部门平均工资在2500元以上的部门名称及平均工资。

select d.dname,avg(e.sal)
from emp e,dept d
where e.deptno = d.deptno
group by d.dname
having avg(e.sal)>2500;

• 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select e.job,avg(e.sal)
from emp e
where e.job not like 'SA%'
group by e.job
having avg(e.sal)>2500
order by avg(e.sal) desc;

• 查询部门人数在2人以上的部门名称、最低工资、最高工资, 平均工资, 并对求得平均工资工资进行四舍五入到整数位

select d.dname,min(e.sal),max(e.sal), ROUND(AVG(e.SAL),0)
from emp e,dept d
where e.DEPTNO=d.DEPTNO
GROUP BY e.DEPTNO,d.dname
having count(e.empno)>2

查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select job,sum(sal)
from emp 
where job <>'salesman'
GROUP BY job
having sum(sal)>=2500

• 显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序

select m.empno,min(e.sal)
from emp e
left join emp m
on e.mgr = m.empno
group by m.empno
having min(e.sal)>=3000
order by min(e.sal) desc;

select manager.ename,manager.empno,min(worker.sal)
 from emp worker
left outer join emp manager
on manager.empno=worker.MGR 
group by manager.EMPNO,manager.ename
HAVING  min(worker.sal)>=3000 
ORDER BY 3 DESC

• 查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

SELECT deptno,ename,sal
from emp
where job = 
(select job
from emp
where deptno = 7369)and sal>
(select sal
from emp
where deptno = 7782)

• 查询工资最高的员工姓名和工资。

select ename,sal from emp
where sal= (select max(Sal) from emp)

-查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

select min(e.sal),d.dname,e.DEPTNO from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,d.dname
having min(sal)>(select min(Sal) where e.deptno=10)

• 查询员工工资为其部门最低工资的员工的编号和姓名及工资。

SELECT e.deptno,e.ename,e.sal
from emp e join 
(SELECT deptno,min(sal) abc from emp GROUP BY deptno) a
on e.deptno = a.deptno
where sal = abc

• 显示经理是KING的员工姓名，工资。

select worker.ename, worker.sal from emp worker,emp manager
where  worker.mgr=manager.EMPNO and manager.ENAME='king'

SELECT ename,sal
from emp e
where e.mgr = 
(select empno
from emp
where ename = 'king')

• 显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

select sal ,ename,hiredate from emp
where hiredate> (select hiredate  from emp where ename='Smith')

数据库的操作

INSERT

INSERT INTO    table [(column [, column...])]
VALUES        (value [, value...]);

栗子

INSERT into dept VALUES(90, '市场部', 'Beijing');

• 插入空值

INSERT into dept VALUES(88, '市场部', NULL);

• 插入日期值
  插入数据库系统当前时间SYSDATE()

INSERT INTO  emp (empno, ename, job,mgr, hiredate, sal, comm,deptno)
 VALUES (8888, 'lisi', 'clerk', 7693, SYSDATE(), 2222, NULL, 10)

插入指定日期

INSERT INTO  emp (empno, ename, job,mgr, hiredate, sal, comm,deptno)
 VALUES (8888, 'lisi', 'clerk', 7693, '2002-12-01', 2222, NULL, 10)

update语句

update 表名 set (列)

UPDATE emp SET SAL = 1800 WHERE EMPNO = 7369;

DELETE语句

DELETE from 表名 约束条件

DELETE FROM emp WHERE empno=8888;