scott用户下
1.显示emp表中员工的名字和工资
select ename,sal from emp;
2.显示emp表中员工的编号,名字,提成
select empno,ename,comm from emp;
3.显示部门编号和部门名称
select deptno,dname from dept;
4.显示员工名字和入职日期
select ename,hiredate from emp;
5.显示员工编号 名字 经理编号
select empno,ename,mgr from emp;
6.显示员工编号 名字 部门编号
select empno,ename,deptno from emp;
7.显示工资等级 等级最低工资 等级最高工资
select grade,losal,hisal from salgrade;
8.查询名字是BLAKE的人的编号,名字,工资
select empno,ename,sal from emp where ename='BLAKE';
9.查询编号是7782的员工的编号,名字,工资,提成
select empno,ename,sal,comm from emp where empno=7782;
10.查询入职日期是1981-2-20的员工的名字,入职日期,部门编号
select ename,hiredate,deptno from emp where hiredate=to_date(19810220,'YYYY-MM-DD');
11.查询职位是销售(SALESMAN)的人的名字,职位,入职日期
select ename,job,hiredate from emp where job='SALESMAN';
12.查询部门是10的人的编号,名字,部门编号
select empno,ename,deptno from emp where deptno=10;
13.查询工资大于1500,并且小于2500的人的编号,名字,工资
select empno,ename,sal from emp where sal>1500 and sal<2500;
14.查询工资小于2000的人的名字,工资,提成
select ename,sal,comm from emp where sal<2000;
15.查询提成是0或是空的人的编号,名字,提成,部门编号
select empno,ename,comm,deptno from emp where comm is null or comm=0;
16.查询工资大于1000或者部门是30的员工的编号,姓名,工资
select empno,ename,sal from emp where sal>1000 or deptno=30;
17.查询部门10的部门名称,位置等
select dname,loc from dept where deptno=10;
18.查询职位(JOB)为'PRESIDENT'的员工的工资
select sal from emp where job='PRESIDENT';
19.查询有提成的员工信息
select * from emp where comm is not null;
20.查询名字长度为4 的员工的员工编号,姓名
select empno,ename from emp where length(ename)=4;
21.显示10 号部门的所有经理('MANAGER')和20 号部门的所有职员('CLERK')的详细信息
select * from emp
where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
22.显示各个部门经理('MANAGER')的工资
select deptno,sal from emp where job='MANAGER';
23.显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
select * from emp where nvl(comm,0)>sal;
24.检索以S结尾的员工姓名及月收入( 月收入是工资和提成之和 )
select ename,sal+nvl(comm,0) from emp where ename like '%S';
25.查询员工姓名的第三个字母是A的员工姓名
select ename from emp where ename like '__A%';
26.查询工资不在1000到2000之间的员工的姓名和工资
select ename,sal from emp where sal not between 1000 and 2000;
27. 查询公司中没有管理者的员工姓名及job
select ename,job from emp where mgr is null;
28.求ename列的长度,显示员工编号,名字,长度
select empno,ename,length(ename) 长度 from emp; --length()
29.将ename转成小写,显示员工编号,名字,小写名字
select empno,ename,lower(ename) from emp; --lower()
30.将ename中的A,替换成ABC,显示编号,替换前的名字,替换后的名字
select empno,ename,replace(ename,'A','ABC') from emp; --replace( , , )
31.取ename的第二个字符,显示编号,名字,第二个字符
select empno,ename,substr(ename,2,1) 第二个字符 from emp; --substr( , , )
32.取ename的第二个字符到末尾的所有字符,显示编号,名字,截取后的字符
select empno,ename,substr(ename,2) from emp;--substr( , , )
33.取ename的第三个到第五个字符,显示编号,名字,截取后的字符
select empno,ename,substr(ename,3,5) from emp; --substr( , , )
34.找出ename中A第一次出现的位置,显示编号,名字,A的位置
select empno,ename,instr(ename,'A') from emp; --instr( , , )
35.从ename中第一个A的位置开始截取,取2个字符,显示名字,截取后的字符
select ename,substr(ename,instr（ename,'A'）,2) from emp; --instr( , , ) substr( , , )
--其中如果没有A则从第一个位置开始截取
36.将'abc,bdc,def'变成'abc bdc def',显示变之前的字符串,变之后的字符串
select 'abc,bdc,def',replace('abc,bdc,def',',',' ') from dual; --replace( , , )
select 'abc,bdc,def',translate('abc,bdc,def',',',' ') from dual; --translate( , , )
37.将'ABCDEFADE'变成'mBCDEFmDE',显示变之前的字符串,变之后的字符串
select 'ABCDEFADE',translate('ABCDEFADE','A','m') from dual;--translate( , , )
select 'ABCDEFADE',replace('ABCDEFADE','A','m') from dual; --replace( , , )
38.将' abcdef '变成'abcdef',显示变之前的字符串,变之后的字符串
select ' abcdef ',trim(' abcdef ') from dual; --trim( )
39.将' mnop'变成'mnop',显示变之前的字符串,变之后的字符串
select ' mnop',ltrim(' mnop') from dual; --ltrim( )
40.将'nqwd '变成'nqwd',显示变之前的字符串,变之后的字符串 --rtrim( )
select 'nqwd ',rtrim('nqwd ') from dual;
41.将'hi jk mno'变成'hijkmno',显示变之前的字符串,变之后的字符串
select 'hi jk mno',translate('hi jk mno','a ','b') from dual; --translate( , , )
42.求D和d的 ascii码.
select 'D',ascii('D') from dual --ascii()
union
select 'd',ascii('d') from dual;
select * from emp;
43.将员工名字从M开始截取,显示员工姓名和截取之后名字
select ename,substr(ename,instr(ename,'M')) from emp; --substr( , ) instr( , )
44.将ename中的M替换成AAA,显示替换前后的名字
select ename,replace(ename,'M','AAA') from emp; --replace（ , ）
45.将506.9变成‘五零六点九’
select '506.9',translate('506.9','.1234567890','点一二三四五六七八九零') from dual; --translate( , , )
46. 将 'a#c12%45ABC!c*ef@89'中的除了字母和数字以外的字符都去掉
select 'a#c12%45ABC!c*ef@89',translate( 'a#c12%45ABC!c*ef@89','ac1245ABCcef89#%!*@','ac1245ABCcef89') from dual; --translate
47.将工资保2位小数，显示为美元格式，显示员工编号，姓名，工资
select empno,ename,to_char(trunc(sal,2),'$999,999.99') from emp;
48.将员工工资显示千分位形式，显示员工编号，姓名，工资
select empno,ename,to_char(sal,'999,999') from emp;
49.将员工工资增加500之后，求比原 工资增加了百分之几，四舍五入保留两位小数，显示员工编号， 名字，工资，百分比（比如：20%）
select empno,ename,sal,round(500/sal*100,2)||'%' 百分比 from emp;
50.查询入职日期在1981-5-1到1981-12-31至间的所有员工信息
select * from emp where hiredate between to_date(19810501,'YYYY-MM-DD') and to_date(19811231,'YYYY-MM-DD');
51.求1981你年下半年入职的员工
select * from emp where hiredate between to_date(19810731,'YYYY-MM-DD') and to_date(19811231,'YYYY-MM-DD');
52.查询当前月有多少天
select to_char(sysdate,'DD') from dual;
53.如果工资<1000，显示成'1级',1001至2000显示成'2级'，2001至3000显示成'3级'，其他显示成'4级'
select sal,case when sal<1000 then '1级'
when sal<2000 then '2级'
when sal<3000 then '3级'
else '4级'
end case
from emp;
54.如果有提成，显示成'有提成',没提成，显示'没提成'分别用case和decode
select comm, case when nvl(comm,0)>0 then '有提成'
else '没有提成'
end case
from emp;
select comm,decode(nvl(comm,0),0,'没有提成','有提成') from emp;
55.如果job是PRESIDENT显示成'老板'，job是MANAGER，显示成'经理'，其他显示成员工，分别用
case when 和 decode 实现
select job,case job when 'PRESIDENT' then '老板'
when 'MANAGER' then '经理'
else '员工'
end case
from emp;
select job,decode(job,'PRESIDENT','老板','MANAGER','经理','员工') from emp;
56.1981年及以前入职的，显示为'老员工',1982年及以后入职的，显示为'新员工'
select hiredate,case when hiredate<to_date(19810101,'YYYY-MM-DD') then '老员工'
else '新员工'
end case
from emp;
57.随便写一个年份（比如:1981),判断这个年份是不是闰年，是闰年，显示为'闰年'，否则显示为'平年'
select to_char(sysdate,'YYYY'),case to_date(to_char(sysdate,'YYYY')||'12'||'31','YYYY-MM-DD')-trunc(sysdate,'YYYY')+1 when 365 then '平年'
else '闰年'
end case
from dual
58.emp表中hiredate，求星期，如果是星期一，显示为'周一'，是星期二，显示为'周二',依次类推，星期日显示为'周日'
select hiredate,decode(to_char(hiredate,'day'),'星期一','周一','星期二','周二','星期三','周三','星期四','周四','星期五','周五','星期六','周六','星期日','周日')
from emp;
scott
59.列出至少有一个雇员的所有部门
select deptno,count(*)
from emp
group by deptno
having count(*)>=1;
60.列出薪金比‘SMITH’多的所有雇员
select * from emp where sal>(select sal from emp where ename='SMITH');
61.列出所有雇员的姓名及其直接上级的姓名
select a.ename,b.ename
from emp a inner join emp b on a.mgr=b.empno;
62.列出入职日期早于其直接上级的所有雇员
select a. *
from emp a inner join emp b on a.mgr=b.empno
where a.hiredate<b.hiredate
63.列出所有‘CLERK’(办事员)的姓名及其部门名称
select a.ename,b.dname
from emp a inner join dept b on a.deptno=b.deptno
where a.job='CLERK';
64.列出薪金高于公司平均水平的所有雇员
select *
from emp
where sal>(select avg(sal) from emp);
65.列出与‘SCOTT’从事相同工作的所有雇员
select * from emp where job=(select job from emp where ename='SCOTT');
66.列出某些雇员的姓名和佣金，条件是他们的薪金等于部门30中任何一个雇员的薪金
select ename,sal from emp where sal in (select sal from emp where deptno=30 );
67.列出某些雇员的姓名和佣金，条件是他们的薪金高于部门30中所有雇员的薪金
select ename,sal from emp where sal>all(select sal from emp where deptno=30 );
68.列出每个部门的编号以及该部门中雇员数量、平均工资和平均服务期限
select b.deptno,count(a.empno),avg(a.sal),avg(months_between(sysdate,hiredate)/12) year
from emp a inner join dept b on a.deptno=b.deptno
group by b.deptno
69.列出所有雇员的雇员名称，部门名称和薪金
select a.ename,b.dname, a.sal
from emp a inner join dept b on a.deptno=b.deptno;
70.列出从事同一种工作但属于不同部门的雇员数量
select job,deptno,count(*)
from emp
group by job,deptno
order by job;
71.列出各种类别工作的最低工资
select min(sal) from emp group by job
72.列出各个部门的MANGER （经理)的最低薪金
select min(sal)
from emp
where job='MANAGER'
group by deptno;
73.列出按年薪排序的所有雇员的年薪
select sal*12 ysal
from emp
order by ysal desc
74.列出薪金水平处于第四位的雇员
select empno,ename
from (select rownum num,empno,ename
from emp
order by sal desc)
where num=4
75.不用组函数求出薪水的最大值
select sal
from (select sal from emp order by sal desc)
where rownum<2
76.查询员工的基本信息，附加其上级的姓名
select a.*,b.ename from emp a inner join emp b on a.mgr=b.empno;
77.显示工资比‘ALLEN’高的所有员工的姓名和工资
select ename,sal from emp where sal>(select sal from emp where ename='ALLEN');
78.显示与‘SCOTT’从事相同工作的员工的详细信息
select * from emp where job=(select job from emp where ename like 'SCOTT');
79.显示与30部门‘MARTIN’员工工资相同的员工的姓名和工资
select ename,sal
from emp
where sal=( select sal from emp where deptno=30 and ename='MARTIN');
80.查询所有工资高于平均工资（平均工资包括所有员工）的销售人员（‘SALESMAN’）
select *
from emp
where sal>(select avg(sal) from emp ) and job='SALESMAN';
81.显示所有职员的姓名及其所在部门的名称和工资
select a.ename,b.dname,a.sal
from emp a inner join dept b on a.deptno=b.deptno;
82.查询在研发部（‘RESEARCH’）工作员工的编号，姓名，工作部门，工作所在地
select a.empno,a.ename,b.dname,b.loc
from emp a inner join dept b on a.deptno=b.deptno
where b.dname='RESEARCH';
83.查询各个部门的名称和员工人数
select b.dname,count(*)
from emp a inner join dept b on a.deptno=b.deptno
group by b.dname;
84.查询各个职位员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位
select job,count(*)
from emp
where sal>(select avg(sal) from emp )
group by job;
85.查询工资相同的员工的工资和姓名
select sal,ename
from emp
where sal in （select sal
from emp
group by sal
having count(*)>=2）
86.查询工资最高的3名员工信息
select *
from (select * from emp order by sal desc)
where rownum<4
87.求入职日期相同的（年月日相同）的员工
select a.empno
from emp a inner join emp b on a.hiredate=b.hiredate
group by a.empno
having count(a.empno)>=2
select * from emp where hiredate=( select hiredate
from emp
group by hiredate
having count(empno)>=2)
88.查询每个员工的信息及工资级别
select a.empno,a.ename,b.grade
from emp a inner join salgrade b on a.sal between losal and hisal;
89.查询工资最高的第6-10名员工
select empno,ename,sal
from (select rownum nu,empno,ename,sal from emp order by sal desc)
where nu between 6 and 10;
90查询各部门工资最高的员工信息
select *
from emp
where sal in (select max(sal) from emp group by deptno)
91.查询出有3个以上下属的员工信息
select * from emp where empno in (select mgr from emp group by mgr having count(*)>=3）
92.查询所有大于本部门平均工资的员工信息
select *
from emp a inner join (select deptno,avg(sal) ag from emp group by deptno) b on a.deptno=b.deptno
where a.sal>b.ag;
93.查询平均工资最高的部门信息
select * from dept where deptno=(
select deptno
from (select deptno,avg(sal) av from emp group by deptno order by avg(sal) desc)
where rownum<2);
94.查询部门平均工资大于所有人平均工资的部门信息
select * from dept where deptno in (
select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp));
95.查询没有员工的部门信息
select * from dept where deptno not in (select deptno from emp)
96.哪些部门的人比90部门人数多
with dep as (select department_id deptno,count(*) cou from employees group by department_id)
select deptno
from dep where cou>(select cou from dep where deptno=90);
97.Den(first_name)、Ernst(LAST_NAME)的领导分别是谁
select a.first_name,b.first_name
from employees a inner join employees b on a.manager_id=b.employee_id
where a.first_name='Den' or a.last_name='Ernst';
98.Den(first_name)、Ernst(LAST_NAME)的下属分别有谁
select b.first_name,a.first_name
from employees a inner join employees b on a.manager_id=b.employee_id
where b.first_name='Den' or b.last_name='Ernst';
99.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字 工资 入职日期
select a.first_name,a.salary,a.hire_date
from employees a inner join employees b on a.department_id=b.department_id
where a.hire_date>b.hire_date and a.salary>b.salary;
100.Finance 部门有哪些职位
select a.job_id
from employees a inner join departments b on a.department_id=b.department_id
where b.department_name='Finance';
101.查询入职日期比10部门任意一个员工晚的员工姓名，入职日期，不包括10部门员工
select *
from emp
where hiredate>all(select hiredate from emp where deptno=10) ;
102.查询比自己职位平均工资高的员工姓名，职位，部门名称，职位平均工资
select a.ename,a.job,c.dname,b.av
from emp a inner join (select job,avg(sal) av from emp group by job) b on a.job=b.job
inner join dept c on a.deptno=c.deptno
where a.sal>b.av;
103.查询不是经理的员工姓名
select ename
from emp
where empno not in (select distinct nvl(mgr,0) from emp );
104.查询入职日期最早的前五名员工姓名，入职日期
select ename,hiredate
from (select ename,hiredate from emp order by hiredate asc)
where rownum<6;
105.查询工作在CHICAGO并且入职日期最早的前两名员工姓名，入职日期
select name,hrdate
from (select a.ename name,a.hiredate hrdate
from emp a inner join dept b on a.deptno=b.deptno
where b.loc='CHICAGO'
order by a.hiredate asc)
where rownum<3
106.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号
select empno
from emp
where sal>(select sal from emp where empno=7782)
and job=(select job from emp where empno=7369);
107.查询工资最高的员工姓名和工资
select ename,sal
from (select ename,sal from emp order by sal desc)
where rownum=1;
108.查询部门最低工资高于30号部门最低工资的部门编号，名称及部门最低工资
with temp as (select deptno,min(sal) min from emp group by deptno)
select a.deptno,a.dname,b.min
from dept a inner join temp b on a.deptno=b.deptno
where b.min<(select min from temp where deptno=30);
109.查询员工工资为其部门最低工资的员工的编号和姓名及工资
select empno,ename,sal
from emp
where (deptno,sal) in (select distinct deptno,min(sal) from emp group by deptno);
110.显示经理是KING的员工姓名，工资
select ename,sal
from emp
where mgr=(select empno from emp where ename='KING');
111.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间
select ename,sal,hiredate
from emp
where hiredate>(select hiredate from emp where ename='SMITH');
112.使用子查询的方式查询哪些职员在NEW YORK 工作
select * from emp where deptno=(select deptno from dept where LOC='NEW YORK');
113.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇佣日期，查询结果排除SMITH
select ename,hiredate
from emp
where deptno=(select deptno from emp where ename='SMITH') and ename!='SMITH';
114.查询显示其工资比全体职员平均工资高的员工编号姓名
select empno,ename
from emp
where sal>(select avg(sal) from emp )
115.显示所有工作在RESEARCH部门的员工姓名工资
select ename,sal
from emp
where deptno=(select deptno from dept where dname='RESEARCH');
116.要求部门的平均工资高于20部门平均工资的平均工资
with temp as (select deptno,avg(sal) av from emp group by deptno)
select avg(av)
from temp where av>(select av from temp where deptno=20);
117.查询大于自己部门平均工资的员姓名，工资，所在部门平均工资
with temp as (select deptno,avg(sal) av from emp group by deptno)
select a.ename,a.sal,b.av
from emp a inner join temp b on a.deptno=b.deptno
where a.sal>b.av
118.列出至少有一个雇员的所有部门
select deptno
from emp
group by deptno
having count(*)>=1;
119.列出薪金比SMITH多的所有雇员
select * from emp where sal>(select sal from emp where ename='SMITH');
120.列出入职日期早于其直接上级的所有雇员
select *
from emp a inner join emp b on a.mgr=b.empno
where a.hiredate<b.hiredate;
121.显示部门名称和人数
with temp as (select deptno,count(*) cou from emp group by deptno)
select a.dname,nvl(b.cou,0)
from dept a left join temp b on a.deptno=b.deptno;
122.显示每个部门的最高工资的员工
select *
from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)
123.显示出和员工号7369部门相同的员工姓名，工资
select ename,sal
from emp
where deptno=(select deptno from emp where empno=7369);
124.显示出和员工姓名中包含W的员工相同部门的员工姓名
select ename
from emp
where deptno in (select deptno from emp where ename like '%W%')
125.显示出工资大于平均工资的员工姓名，工资
select ename,sal
from emp
where sal>(select avg(sal) from emp);
126.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
select ename,hiredate
from emp
where hiredate>(select hiredate from emp where sal=(select max(sal) from emp));
127.显示出平均工资最高的部门的平均工资及部门名称
with temp as (select deptno,avg(sal) av from emp group by deptno order by avg(sal) desc),
tmp as (select deptno,av from temp where rownum=1)
select a.dname,b.av
from dept a inner join tmp b on a.deptno=b.deptno
128、找出奖金高于工资的员工
select * from emp where nvl(comm,0)>sal;
129、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工
select *
from emp
where deptno=10 and job not in ('MANAGER','CLERK') and sal>2000;
130、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename,months_between(sysdate,hiredate)/12 year
from emp
order by year desc;
131.显示的8月8日为星期几
select to_char(to_date(0808,'YYYY-MM-DD'),'day') from dual;
132.将入职日期显示为如下格式： 23-01-
select empno,to_char(hiredate,'DD-MM-YYYY') from emp
133.取出每位员工在分别在哪个季度入职
select empno,ename,to_char(hiredate,'q') from emp
134.显示每位员工在本月哪周入职
select ename,to_char(hiredate,'w') from emp;
135.显示日期 '-01-12 12:13:14'
select to_date(0112,'YYYY-MM-DD')+12/24+13/24/60+14/24/60/60 from dual;
136、要求查询出雇员的编号，姓名，工作，但是显示的格式：编号是：7369的雇员，姓名是：SMITH，工作是：CLERK（emp表中所有数据，只显示一列）
select '员工编号是：'||empno||'，雇员姓名是：'||ename||'，工作是：'||job from emp
137、要求查询基本工资不大于1500，同时不可以领取奖金的雇员信息
select * from emp where sal<=1500 and comm is null;
138、查询出名称中第2位字母出现在职位中，任意位置的员工信息
select a.*
from emp a inner join (select empno,instr(job,substr(ename,2,1)) nu from emp) b on a.empno=b.empno
where b.nu!=0;
139、时间是’-10-26’中，显示当天所在周的周一是几号，这天是一年的多少周，是当前月中的第几周
select to_date(1026,'YYYY-MM-DD'),trunc(to_date(1026,'YYYY-MM-DD'),'ww'),to_char(to_date(1026,'YYYY-MM-DD'),'ww'),to_char(to_date(1026,'YYYY-MM-DD'),'w') from dual;
140、写出将时间‘-10-26’显示成‘-10-29 12-13-14’ 需要加几天
select to_date(1029,'YYYY-MM-DD')+12/24+13/24/60+14/24/60/60-to_date(1026,'YYYY-MM-DD') FROM DUAL;
141、显示员工表中，员工编号是奇数的员工信息 或 工资是奇数但是无奖金的员工信息
select * from emp
where mod(empno,2)=1 or (mod(sal,2)=1 and comm is null);
142、要求查询出每个雇员的姓名，工资，部门名称，工资在公司的等级（salgrade）,及其领导的姓名及工资等级
with temp as (select a.empno empno,a.ename ename,a.mgr mgr,a.sal sal,c.dname dname,b.grade grade
from emp a inner join salgrade b on a.sal between losal and hisal
inner join dept c on a.deptno=c.deptno)
select a.ename,a.sal,a.dname,a.grade,b.ename,b.grade
from temp a inner join temp b on a.mgr=b.empno;
143、按工资降序求第5到10位员工的姓名及工资
select name,sal
from (select rownum num,ename name,sal from ( select ename,sal from emp order by sal desc))
where num between 5 and 10;
