One
1、选择部门30中的所有员工.
SQL> select * from emp where deptno = 30;
2、列出所有办事员(CLERK)的姓名,编号和部门编号
SQL> select empno,ename,deptno from emp where job='CLERK';
3、找出奖金高于工资的员工
SQL> select * from emp where comm>sal;
4、找出奖金高于工资的60%的员工
SQL> select * from emp where comm>(sal*0.6);
5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料
SQL>select * from emp
where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料
select * from emp
where (deptno=10 and job='MANAGER')
or (deptno=20 and job='CLERK')
or ((Job!='MANAGER' and job!='CLERK') and sal > =2000);
7、找出收取奖金的员工的不同工作
SQL> select job from emp where comm is not null;
SQL> select job from emp where not comm is null;
SQL> select distinct job from emp where not comm is null;
8、找出不收取奖金或者收取的奖金低于100的员工
SQL> select * from emp where comm is null or comm <100;
9、找出各月倒数第三天受雇的所有员工
select to_char(hiredate,' dd')day1 from emp;
select to_char(last_day(hiredate),' dd')day2 from emp;
select * from emp where ((to_char(last_day(hiredate),' dd') - to_char(hiredate,' dd')) =2);
10、找出早于12年前受雇的员工
select to_char(hiredate,'yyyy')year1 from emp;
select to_char(sysdate,' yyyy')year2 from emp;
select * from emp where (to_char(sysdate,' yyyy') - to_char(hiredate,'yyyy')) > 13 ;
11、以首字母大写的方式显示所有员工的姓名
SQL> select initcap(ename) from emp;
12、显示正好为5个字符的员工的姓名
SQL> select ename from emp where length(ename)=5;
13、显示不带有“R”的员工姓名
SQL> select ename from emp where ename not like '%R%';
14、显示所有员工姓名的前三个字符
SQL> select ename,substr(ename,1,3) from emp;
15、显示所有员工的姓名,用“a”替换所有的“A”
SQL> select replace(ename,'A','a') from emp;
16、显示满10年服务年限的员工的姓名和受雇日期
SQL>select ename,hiredate from emp where months_between(sysdate,hiredate)>120;
17、显示员工的详细资料,按姓名排序(默认升序)
SQL> select * from emp order by ename;
SQL> select * from emp order by ename asc;
18、显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面
SQL> select ename,hiredate from emp order by hiredate asc;
19、显示所有员工的姓名、工作和工资,按工作的降序排序,若工作相同则按工资排序
SQL> select ename,job,sal from emp order by job desc,sal asc;
20、显示所有员工姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面
select ename,to_char(hiredate,'yyyy') year,to_char(hiredate,' mm') month from emp
order by month,year asc;
21、显示在一个月为30天的情况所有员工的日工资,忽略余数
select ename,trunc(sal/30)day_sal from emp;
22、找出(任何年份的)2月受聘的所有员工
select ename from emp where ((to_char(hiredate,' mm') -02)=00);
23、对于每个员工,显示其加入公司的天数
select ename,hiredate,sysdate-hiredate from emp;
24、显示姓名字段的任何位置包含“A”的所有员工的姓名
SQL> select ename from emp where ename like '%A%';
25、以年月日的方式显示所有员工的服务年限
SQL> select ename,hiredate,
trunc(months_between(sysdate,hiredate)/12)year,
trunc(mod(months_between(sysdate,hiredate),12))month,
trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) day
from emp;
Two
1、列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资
select d.deptno, d.dname, avg(e.sal) , min(e.sal), max(e.sal),count(e.empno)
from emp e right join dept d on e.deptno = d.deptno
group by d.deptno, d.dname
having count(e.empno)>0;
2、列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名
select e.empno , e.ename, d.dname ,m.ename
from emp e join dept d on e.deptno = d.deptno
join emp m on e.mgr = m.empno
where e.sal > (select max(sal) from emp where ename in ('SMITH','ALLEN'))
3、列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列
select e.empno, e.ename , m.empno, m.ename,(m.sal + ifnull(m.comm,0))*12 年薪
from emp e left join emp m on e.mgr = m.empno
order by (m.sal + ifnull(m.comm,0))*12 desc;
4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
select e.empno , e.ename , d.dname , d.loc , n.ct
from emp e join dept d on e.deptno = d.deptno
left join emp m on e.mgr = m.empno
join (select deptno , count(empno) ct
from emp
group by deptno) n on e.deptno = n.deptno
where e.hiredate < m.hiredate;
5、列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门
select d.deptno, d.dname , count(e.empno) , avg(e.sal)
from emp e right join dept d on e.deptno = d.deptno
group by d.deptno, d.dname
6、列出所有CLERK的姓名及其部门名称,部门的人数,工资等级
select e.ename , d.dname , n.ct , s.grade
from emp e join dept d on e.deptno = d.deptno
join salgrade s on e.sal between s.losal and s.hisal
join (select deptno , count(empno) ct
from emp
group by deptno) n on e.deptno = n.deptno
where e.job = 'CLERK';
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资
select n.job, n.ct, n.avg_sal , d.dname , d.loc
from emp e join dept d on e.deptno = d.deptno
join (select job, count(e.empno) ct , avg(sal) avg_sal
from emp e
group by e.job
having min(e.sal) > 1500) n on e.job = n.job
8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称,假定不知道该部门的部门编号
select e.ename, e.sal , e.hiredate, d.dname
from emp e join dept d on e.deptno = d.deptno
where d.dname = 'SALES'
9、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级
select e.ename , d.dname , m.ename , s.grade
from emp e , dept d, emp m, salgrade s
where e.deptno = d.deptno and e.mgr = m.empno and e.sal between s.losal and s.hisal
and e.sal > (select avg(sal) from emp)
10、列出与SCOTT从事相同工作的所有员工及部门名称,部门人数
select e.*, d.dname, n.ct
from emp e , dept d , (
select deptno , count(empno) ct
from emp
group by deptno) n
where e.deptno = d.deptno and e.deptno = n.deptno
and e.job = (select job from emp where ename = 'SCOTT');
11、列出公司各个工资等级雇员的数量、平均工资
select s.grade,count(empno), avg(sal)
from emp e , salgrade s
where e.sal between s.losal and s.hisal
group by s.grade
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
select sal from emp where deptno = 30
select e.ename, e.sal , d.dname
from emp e join dept d on e.deptno = d.deptno
where sal > all(select sal from emp where deptno = 30);
13、列出在每个部门工作的员工数量、平均工资和平均服务期限
select deptno, count(empno),avg(sal) , avg(datediff(now(),hiredate)/365)
from emp
group by deptno
14、列出所有员工的姓名、部门名称和工资
select ename, dname, sal from emp e join dept d on e.deptno = d.deptno
15、列出所有部门的详细信息和部门人数
select d.*, nt.ct
from dept d left join (select deptno , count(1) ct from emp group by deptno) nt
on d.deptno = nt.deptno
16、列出各种工作的最低工资及从事此工作的雇员姓名
select ename from emp where sal in (select min(sal) from emp group by job);
17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数
select nt_sal.min_sal, e.ename, d.dname,nt_ct.ct
from emp e,dept d,
(select deptno, min(sal) min_sal from emp where job = 'MANAGER' group by deptno) nt_sal ,
(select deptno , count(1) ct from emp group by deptno) nt_ct
where e.deptno = d.deptno
and e.deptno = nt_sal.deptno
and e.deptno = nt_ct.deptno
and e.job = 'MANAGER'
18、列出所有员工的年工资,所在部门名称,按年薪从低到高排序
select e.sal + ifnull(comm,0) 年薪, d.dname
from emp e join dept d on e.deptno = d.deptno order by 年薪;
19、列出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过3000
select e.ename, m.ename, d.dname
from emp e,emp m,dept d
where e.mgr = m.empno and m.deptno = d.deptno and m.sal >= 3000
20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
select d.deptno , sum(e.sal) , count(1)
from emp e right join dept d on e.deptno = d.deptno
where d.dname like '%S%'
group by d.deptno;
21、给任职日期超过30年或者87年雇佣的雇员加薪,加薪原则:10部门增长10%,20部门增长20%,30部门增长30%,以此类推
select sal, sal*(1+ deptno/100)
from emp
where adddate(curdate(), interval -30 year) > hiredate or year(hiredate) = 1987;