1,列出至少有一个员工的所有部门。
select dname from dept where deptno in (select distinct(deptno) from emp);
//列出部门人数不少于3个人的部门和人数
错误的查询方法：
注意dname不是关键字，可能存在多个重复的，所以不能以dname分组，所以下面的查询是错误的。
select dname,count(*) from emp a join dept b on a.deptno=b.deptno group by dname having count(*)>=3;
正确的查询方法：
select b.dname,a.count from (select deptno,count(*) count from emp group by deptno having count(*)>=3) a join dept b on a.deptno=b.deptno;
2,列出薪水比"SMITH"多的所有员工。
select ename from emp where sal>(select sal from emp where ename='SMITH');
3,列出所有员工的姓名及其直接上级的姓名。
select a.ename emp_name,b.ename mag_name from emp a,emp b where a.mgr=b.empno;
4,列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。
select empno,ename,dname from emp a join dept b on a.deptno=b.deptno where hiredate in (select a.hiredate from emp a,emp b where a.mgr=b.empno and a.hiredate<b.hiredate);
同上
select empno,ename,dname from emp a join dept b on a.deptno=b.deptno where a.empno in (select a.empno from emp a,emp b where a.mgr=b.empno and a.hiredate<b.hiredate);
5,列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select a.dname,b.ename,b.empno,b.job from dept a left join emp b on a.deptno=b.deptno;
6,列出所有job=CLERK的姓名及其部门名称，部门的人数。
//查询工作是CLERK的姓名及其部门的人数
select a.ename,b.count,c.dname from emp a join (select deptno,count(*) count from emp group by deptno) b on a.deptno=b.deptno join dept c on c.deptno=b.deptno where a.job='CLERK';
7,列出最低薪水大于1500的各种工作及此从事此工作的全部雇员人数。
select job,min(sal),count(*) from emp group by job having min(sal)>1500;
8,列出在部门"SALES"（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp where deptno=(select deptno from dept where dname='SALES');
9,列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，公司的工资等级。
方法一:
//7839没有上级领导
select a.empno,a.ename,b.ename,c.dname,a.sal,case when a.sal >=700 and a.sal<=1200 then 1
when a.sal >=1201 and a.sal<=1400 then 2
when a.sal >=1401 and a.sal<=2000 then 3
when a.sal >=2001 and a.sal<=3000 then 4
when a.sal >=3001 and a.sal<=9999 then 5 else 10000 end "grade" from emp a join emp b on a.mgr=b.empno join dept c on b.deptno=c.deptno where a.sal>(select avg(sal) from emp);
方法二:
select a.ename emp_name,c.dname, b.ename mag_name,a.grade
from
(select a.empno,a.deptno,a.mgr,b.grade,a.ename
from emp a,salgrade b
where sal > (
select avg(sal) sal from emp )
and sal >= losal and sal <= hisal) a,
emp b,
dept c
where a.mgr=b.empno and a.deptno=c.deptno;
10,列出与"SCOTT"从事相同工作的所有员工及部门名称。
select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno where job=(select job from emp where ename='SCOTT') and ename!='SCOTT';
11,列出薪水等于部门号=30中员工的薪水的所有员工的姓名和薪水。
select ename,sal from emp where sal in (select sal from emp where deptno=30);
12,列出薪水高于在部门号=30工作的所有员工的薪水的员工姓名和薪水,部门名称。
select a.ename,a.sal,b.dname from emp a join dept b on a.deptno=b.deptno where sal >all (select sal from emp where deptno=30);
13,列出在每个部门工作的员工数量,平均工资和平均服务期限。
select count(*) count,avg(sal) avg_sal,avg(sysdate-hiredate) avg_hiredate from emp group by deptno;
14,列出所有员工的姓名,部门名称和工资。
select a.ename,a.sal,b.dname from emp a join dept b on a.deptno=b.deptno;
15,列出所有部门的详细信息和部门人数。
方法一：
select a.*,b.counts from dept a left join (select deptno,count(*) counts from emp group by deptno) b on a.deptno=b.deptno;
方法二：
select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) deptcount from dept a;
16,列出各种工作的最低工资及从事此工作的雇员姓名。
select ename,sal,job from emp where sal in (select min(sal) from emp group by job);
17,列出各个部门的MANAGER（经理）的最低薪水。
select ename,mgr,sal,deptno from emp where sal in (select min(sal) from emp group by job);
18,列出所有员工的年工资，按年薪(salary+comm)从高到底排序。
注意年薪(salary+comm)都需要乘以12
select sal*12+nvl(comm,0)*12 sum_salary from emp order by sum_salary desc;
//列出每个部门的平均薪金(salary+comm)
方法一:
select avg(sal*12+nvl(comm,0)*12) avg_salary,deptno from emp group by deptno;
方法二:
select (sum(sal+nvl(comm,0))*12)/count(*) salary from emp grep by deptno;
19,查出某个员工的上级主管，并要求出这些主管中的薪水超过3000
select a.ename emp_name,b.ename mag_name,b.sal from emp a,emp b where a.mgr=b.empno and b.sal>3000;
20,求出部门名称中，带‘S’字符的部门员工的部门名称,工资合计,部门人数
select dname,count(dname) count,sum(sal) sum_sal from emp a join dept b on a.deptno=b.deptno where dname like '%S%' group by b.dname;
21.求平均薪水最高的部门，显示部门名称和部门平均薪水。
select a.avg_sal,c.dname from (select max(avg(sal)) avg_sal from emp group by deptno) a join
(select avg(sal) avg_sal,deptno from emp group by deptno) b on a.avg_sal=b.avg_sal join dept c on c.deptno=b.deptno;
注意下面虽然跟上面的结果相同，但是是有问题的，其中不能出现平均薪水最高的部门存在多个
select a.dname,b.avg_sal from dept a join (select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc) b on a.deptno=b.deptno where rownum=1;
########################
1,请查询表DEPT中所有部门的情况。
select * from dept;
2,查询表DEPT中的部门号,部门名称两个字段的所有信息。
select deptno,dname from dept;
3,请从表EMP中查询10号部门工作的雇员姓名和工资。
select ename,sal from emp where deptno=10;
4,请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名,工资。
select ename,sal from emp where job in('CLERK','MANAGER');
5,请在EMP表中查找部门号在10－30之间的雇员的姓名,部门号,工资,工作。
select ename,deptno,sal,job from emp where deptno between 10 and 30;
6,请从表EMP中查找姓名以J开头所有雇员的姓名,工资,职位。
select ename,sal,job from emp where ename like 'J%';
7,请从表EMP中查找工资低于2000的雇员的姓名,工作,工资，并按工资降序排列。
select ename,job,sal from emp where sal<2000 order by sal desc;
8,请从表中查询工作是CLERK的所有人的姓名,工资,部门号,部门名称以及部门地址的信息。
select a.ename,a.sal,a.deptno,b.dname,b.loc from emp a join dept b on a.deptno=b.deptno where job='CLERK';
9,查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。
select a.ename emp_name,b.ename mag_name from emp a join emp b on a.mgr=b.empno where a.sal>2000;
10,在表EMP中查询所有工资高于JONES的所有雇员姓名,工作和工资。
select ename,job,sal from emp where sal>(select sal from emp where ename='JONES');
11,列出没有对应部门表信息的所有雇员的姓名,工作以及部门号。
select ename,job,deptno from emp where deptno is null;
12,查找工资在1000～3000之间的雇员所在部门的所有人员信息。
select * from emp where deptno in(
select distinct(deptno) from emp where sal between 1000 and 3000);
13,雇员中谁的工资最高。
select ename from emp where sal>=all(select sal from emp);
14,雇员中谁的工资第二高(考虑并列第一的情况，如何处理)
select ename from emp where sal=(select max(sal) from emp where sal in(select sal from emp minus select max(sal) from emp));
方法二：
select * from (select ename,sal,dense_rank() over (order by sal desc) rn from emp) where rn=2;
15,查询所有雇员的姓名,SAL与COMM之和。
select ename,sal*12+nvl(comm,0) sum_salary from emp;
16,查询所有81年7月1日以前来的员工姓名,工资,所属部门的名字
select a.ename,a.sal,b.dname from emp a join dept b on a.deptno=b.deptno where
hiredate<'01-JAN-81';
17,查询各部门中81年1月1日以后来的员工数
方法一：
注意：不同的数据库日期格式可能不是‘1-JAN-81’，所以最好采用第二种方法
select deptno,count(*) from emp where hiredate>'1-JAN-81' group by deptno;
方法二：
select deptno,count(*) emp_total from emp where hiredate>to_date('1981-01-01','yyyy-mm-dd') group by deptno;
18,查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名,工资
select ename,sal from emp where job in ('MANAGER','SALESMAN') and deptno in (select deptno from dept where loc='CHICAGO');
19,查询列出来公司就职时间超过24年的员工名单
方法一：
select ename from emp where (sysdate-hiredate)/365>24;
方法二：
288是24年总共是288个月
where条件的意思是来的日期
select ename from emp where hiredate<=add_months(sysdate,-288);
20,询于81年来公司所有员工的总收入(包括SAL和COMM)
方法一：
select sum(sal*12+nvl(comm,0)*12) sum from emp where hiredate between '1-JAN-81' and '31-DEC-81';
方法二：
select sum(sal*12+nvl(comm,0)*12) sum from emp where to_char(hiredate,'yyyy')='1981';
21,查询显示每个雇员加入公司的准确时间，按yyyy-mm-dd hh24:mi:ss显示。
select to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
22,查询公司中按年份月份统计各地的录用职工数量
select count(*),to_char(hiredate,'yyyy') year,to_char(hiredate,'mm') month,loc from emp a join dept b on a.deptno=b.deptno group by loc,hiredate;
//下面其实与上面同
select count(*),to_char(hiredate,'yyyy-mm') year_month,loc from emp a join dept b on a.deptno=b.deptno group by loc,hiredate;
23查询列出各部门的部门名和部门经理名字
select a.ename man_name,dname from emp a join dept b on a.deptno=b.deptno where job='MANAGER';
24,查询部门平均工资最高的部门名称和最低的部门名称
select dname avg_sal from dept where deptno=(select deptno from emp group by deptno having avg(sal)>=all(select avg(sal) from emp a join dept b on a.deptno=b.deptno group by a.deptno))
union all
select dname avg_sal from dept where deptno=(select deptno from emp group by deptno having avg(sal)<=all(select avg(sal) from emp a join dept b on a.deptno=b.deptno group by a.deptno));
25,查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名
select b.ename,c.dname from (select min(hiredate) hiredate from emp where hiredate>(select hiredate from emp where empno='7521')) a,emp b,dept c where a.hiredate=b.hiredate and b.deptno=c.deptno;
扫一扫
1500
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
