ORACLE经典查询语句
scott/tiger登录数据库，sqlplus
conn scott/scott
1、显示所有部门名
select dept.dname from dept ;
2、显示所有雇员名及其全年收入(工资+补助)，并指定列别名“年收入”
select emp.ename as 雇员名,nvl2(comm,comm+sal,sal)*12 as 年收入from emp;
3、显示存在雇员的所有部门号
select emp.deptno from emp;
4、显示工资超过2850的雇员名和工资
select emp.ename as 雇员名,emp.sal as 工资 from emp where emp.sal>2850;
5、显示工资不在1500到2850之间的所有雇员名及工资
select emp.ename as 雇员名,emp.sal as 工资
from emp where emp.sal not between 1500 and 2850;
6、显示雇员代码为7566的雇员名及所在部门号
Select empno,ename from emp where empno=7566;
7、显示部门代码为10和30中工资超过1500的雇员名及工资
select emp.ename as 雇员名,emp.sal as 工资 from emp where emp.deptno
in(10,30) and emp.sal>1500;
8、显示无管理者的雇员名及岗位
select emp.ename,emp.job from emp where mgr is null;
9、显示所有雇员的平均工资、总计工资、最高工资、最低工资
select round(avg(emp.sal),2) as 平均工资,count(emp.sal) as 总计工资,
max(emp.sal) as 最高工资,min(emp.sal) as 最低工资,
from emp;
10、显示每种岗位的雇员总数、平均工资
select count(empno) as 总人数,round(avg(sal),2) as 平均工资
from emp group by job;
11、显示雇员总数，以及获得补助的雇员数
select count(emp.empno) as 总人数,count(bonus.ename) as 补助总人数
from emp left join bonus on emp.ename=bonus.ename;
12、显示管理者的总人数
select count(distinct mgr) as 管理者总人数 from emp;
13、显示雇员工资的最大差额
select max(sal)-min(sal) as 最大差额 from emp;
14、显示部门代码为20的部门号，以及该部门的所有雇员名、雇员工资及岗位
select emp.ename,emp.sal,emp.job from emp where emp.deptno=20;
15、显示获得补助的所有雇员名、补助额以及所在部门号
select emp.ename,bonus.sal,emp.deptno
from emp inner join bonus on emp.ename=bonus.ename;
16、显示所有雇员的姓名、部门编号、工资，并且列名要显示为中文
select ename as 姓名,deptno as 部门编号,sal as 工资 from emp;
17、显示每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资
select a.deptno,a.job,a.deptno_job_avg,b.deptno_avg,c.job_avg
from (select deptno,job,avg(sal) as deptno_job_avg
from emp group by deptno,job) a left join (select deptno,avg(sal)
as deptno_avg from emp group by deptno) b on a.deptno=b.deptno
left join (select job,avg(sal) as job_avg from emp group by job) c
on a.job=c.job
order by deptno;
18、显示工资大于1500的雇员名和工资，并且按照工资的降序排列
select emp.ename as 雇员名,emp.sal as 工资
from emp where emp.sal>1500 order by emp.sal desc;
19、显示雇员部门编号为10或20的信息（要求使用IN关键字）
select * from emp where emp.deptno in(10,20);
20、显示雇员名的第二个字母为A的信息
select * from emp where emp.ename like '_A%';
21、显示没有发放补助的雇员信息
select * from emp where ename not in(select ename from bonus);
22、显示雇员表中记录总数
select count(*) from emp;
23、显示所有雇员名、雇员工资及所在部门名
select emp.ename 雇员名,emp.sal 雇员工资,dept.deptno 部门
from emp,dept where emp.deptno=dept.deptno;
24、显示部门代码为20的部门名，以及该部门的所有雇员名、雇员工资及岗位
select dept.dname,emp.ename,emp.sal,emp.job
from emp,dept
where emp.deptno=dept.deptno and emp.deptno=20;
25、显示所有雇员名、雇员工资及工资级别
select emp.ename,emp.sal,salgrade.grade
from emp,salgrade
where emp.sal>salgrade.losal and emp.salsalgrade.losal and emp.sal1500;
34、按以下格式显示下面信息，条件是此人工资在所有人中最高。
部门姓名工资
select dept.dname as 部门,emp.ename as 姓名,emp.sal as 工资
from emp,dept where emp.deptno=dept.deptno and emp.sal=(select max(emp.sal) from emp);
35、按以下格式显示下面信息
某人 为 某人 工作
select a.ename as 某人为 ,b.ename as 某人 ,a.job as 工作
from emp a,emp b where a.empno = b.mgr;
36、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%（要求用DECODE函数）
select round(decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18)),2) as 工资 from emp
update emp
set sal=decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18));
37、根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。
select round(sal * (1+(sysdate - hiredate)/365/12/100),2) from emp
update emp
set sal= round(sal * (1+(sysdate - hiredate)/365/12/100),2);
38、查询出king所在部门的部门号\部门名称\部门人数
select e.deptno as 部门号,d.dname as 部门名称, (select count(*)
from (select deptno
from emp where deptno in (select deptno
from emp where ename='KING') ) ) as 部门人数
from emp e, dept d
where e.deptno = d.deptno and e.ename = 'KING';
39、查询出king所在部门的工作年限最大的员工名字
select ename, hiredate from emp
where hiredate in (select min(hiredate)
from (select hiredate from emp where deptno in
(select deptno from emp where ename='KING') ));
40、查询出管理员工人数最多的人的名字和他管理的人的名字
select a.ename 员工,b.ename as 管理员
from emp a join emp b on a.mgr=b.empno
where a.mgr=(
select mgr from emp
group by mgr
having count(mgr)=(
select max(cn)
from (
select count(mgr) as cn,mgr
from emp
group by mgr
) a
)
)
order by a.mgr;
41、查询出工资成本最高的部门的部门号和部门名称
select DEPTNO 部门编号,DNAME 部门名称
from Dept
where DEPTNO=(select deptno
from Emp
group by deptno
having sum(sal)=( select max(SSAL)
from (select sum(sal) as SSAL, deptno
from emp
group by deptno) a
)
);
42、查询出工资不超过2500的人数最多的部门名称
select deptno as 部门编号,DNAME as 部门名称
from dept
where deptno=(select deptno
from (select count(empno) as cn,deptno
from Emp
where SAL (select avg(sal) from emp)
group by deptno
having count(*) >= all (select count(*)
from emp e
where e.sal > (select avg(sal) from emp)
group by e.deptno
)
);
页:
[1]