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.sal<=salgrade.hisal;
26、显示雇员“SCOTT”的管理者名
select dept.loc from emp,dept
where emp.ename='SCOTT' amd emp.deptno=dept.deptno;
where emp.ename='SCOTT' amd emp.deptno=dept.deptno;
27、显示获得补助的所有雇员名、补助额以及所在部门名
select emp.ename,bonus.sal,dept.dname
from emp,dept,bonus
where emp.ename = bonus.ename and emp.deptno=dept.deptno;
28、查询EMP表和SALGRADE表，显示部门代码为20的雇员名、工资及其工资级别
select emp.ename,emp.sal,salgrade.grade
from emp,salgrade
where emp.deptno=20 and emp.sal>salgrade.losal and emp.sal<=salgrade.hisal;
29、显示部门代码为10的所有雇员名、部门名，以及其他部门名
select e.ename,d.dname from emp e left join dept d on e.deptno=10 and d.deptno=10 order by d.dname
30、显示部门代码为10的所有雇员名、部门名，以及其他雇员名
select emp.ename,dept.dname
from emp right join dept on emp.deptno=10 and emp.deptno=dept.deptno order by emp.ename;
31、显示部门代码为10的所有雇员名、部门名，以及其他部门名和雇员名
select emp.ename,dept.dname
from emp
full join dept
on emp.deptno=10 and emp.deptno=dept.deptno order by emp.ename;
32、显示"BLAKE"同部门的所有雇员，但不显示"BLAKE"
select * from emp where emp.deptno=(select emp.deptno
from emp
where emp.ename='BLAKE' )and emp.ename <>'BLAKE' ;
33、按以下格式显示下面的信息，条件是工资大于1500的。
部门名称 姓名 工资
select dept.dname as 部门名称,emp.ename as 姓名,emp.sal 工资
from emp,dept where emp.deptno=dept.deptno and emp.sal>1500;
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<=2500
group by deptno
)a
where cn=(select max(cn)
from (select count(empno) as cn
from emp
where SAL<=2500
group by deptno
)b
)
);
43、查询出没有下属员工的人的名字和他的职位
select a.ename as 员工,a.job as 职位 from emp a
left join emp b
on a.empno=b.mgr
where b.empno is null;
44、查询出人数最多的那个部门的部门编号和部门名称
select deptno as 部门编号,dname as 部门名称
from dept
where deptno=(select deptno
from emp
group by deptno
having count(empno)=(select max(cn) as cn
from (select count(empno) as cn
from emp
group by deptno
) a
)
);
45、查询出没有员工的那个部门的部门编号和部门名称(要求用两种方法,其中一种要用集合运算)
方法一：
select a.deptno as 部门编号,dname as 部门名称
from dept a left join emp b on a.deptno=b.deptno
where b.empno is null;
方法二：
select deptno as 部门编号,dname as 部门名称
from dept a
where not exists (
select 1 from emp b
where a.deptno=b.deptno
)；
46、查询出员工名字以A打头的人数最多的部门名称和员工名字
select a.deptno as 部门编号,a.dname as 部门名称, b.ename as 员工名字
from dept a, emp b
where a.deptno in (select deptno from emp
where ename like 'A%'
group by deptno
having count(empno)=(select max(cn) as cn
from (select count(empno) as cn
from emp
where ename like 'A%'
group by deptno
) a
)
)
and a.deptno = b.deptno and b.ename like 'A%';
47、现在公司要给员工增加工龄工资,规则是:30*工作年限，请按以下格式显示下面结果:
部门名称 员工姓名 原工资 增加额度 新工资
select c.dname 部门名称,a.ename 员工名称,a.sal 原工资,
((sysdate - hiredate)/365) as 工作年限,
30*((sysdate - hiredate)/365) as 增加额度,
a.SAL+30*30*((sysdate - hiredate)/365) as 新工资
from emp a,dept c
where exists (select 1 from dept b
where a.deptno=b.deptno)
and a.deptno=c.deptno;
48、针对DEPT和EMP表，查询出下面格式的结果并要求按部门编号和工资降序排列。
部门名称 员工姓名 工资
select a.dname as 部门名称,b.ename as 员工姓名,b.sal as 工资
from dept a ,emp b
where a.deptno=b.deptno
order by a.deptno,b.sal desc;
49、针对DEPT和EMP表，查询出下面格式的结果。
部门编号 部门名称 部门工资最小值 部门工资最大值 部门工资平均值 部门工资合计值
select a.deptno as 部门编号 ,a.dname as 部门名称,
min(b.sal) as 部门工资最小值, max(b.sal) as 部门工资最大值,
avg(b.sal) as 部门工资平均值, sum(sal) as 部门工资合计值
from dept a ,emp b
where a.deptno=b.deptno
group by a.deptno, a.dname;
50、针对DEPT和EMP表，查询出SMITH所在部门的部门名称、部门工资平均值。（要求使用子查询）
select b.dname as 部门名称,avg(a.sal)as 部门工资平均值 from emp a,dept b
where a.deptno=b.deptno
and a.deptno=(select deptno from emp where ename='SMITH')
group by b.dname;
51、针对DEPT和EMP表，查询出下面格式的结果。（要求使用外连接，没有员工的部门名也要显示。员工姓名如果是空值，要求用“不存在”代替；如果工资是空值，要求用0代替。）
部门名称 员工姓名 工资
select a.dname as 部门名称,
case when b.ename is null then '不存在'
else b.ename
end as 员工姓名,
case when b.SAL is null then 0
else b.sal
end as 工资
from dept a left join emp b on a.deptno=b.deptno;
52、针对DEPT和EMP表，查询出没有员工的部门号和部门名称（要求用两种方法）
方法一：
select deptno,dname
from dept
where deptno not in (select deptno from emp);
方法二：
select deptno,dname
from dept
where not exists
( select deptno deptno
from emp
where dept.deptno = emp.deptno );
53、查询出平均工资最高的部门编号、部门名称和平均工资。
select d.deptno,d.dname,avg(e.sal)
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname
having avg(e.sal) >= all ( select avg(sal) from emp group by deptno);
54、查询出工资高于全体平均工资人数最多的部门编号、部门名称和员工姓名、工资。
select d.deptno,d.dname,e.ename,e.sal
from dept d,emp e
where d.deptno = e.deptno
and d.deptno in ( select 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
)
);
转载于:https://blog.51cto.com/joket/1045646
1848
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
