学习oracle的SQL语句 练习

--1.查询emp表，显示薪水大于2000，且工作类别是MANAGER的雇员信息
select * from emp 
where sal > 2000
and job = 'MANAGER';--2.查询emp表，显示年薪大于30000，工作类别不是MANAGER的雇员信息
select * from emp 
where (sal+nvl(comm , 0 ))*12 > 30000 
and job != 'MANAGER';--3.查询emp表， 显示薪水在1500到3000之间，工作类别以“M”开头的雇员信息
select * from emp 
where sal between 1500 and 3000 
and job like 'M%'; 
--4.查询emp表，显示佣金为空并且部门号为20或30的雇员信息
select * from emp 
where comm is null 
and deptno in ( 20 , 30 ); 
--5.查询emp表，显示佣金不为空或者部门号为20的雇员信息，要求按照薪水降序排列
select * from emp 
where comm is not null 
or deptno = 20 
order by sal desc ; 
 
--6.查询emp表，显示年薪大于30000工作类别不是MANAGER，且部门号不是10或40的雇员信息，要求按照雇员姓名进行排列
select * from emp 
where (sal+nvl(comm , 0 ))*12 > 30000 
and job != 'MANAGER' 
and deptno 
not in ( 10 , 40 ) 
order by ename ; 
--7.查询EMP、DEPT表，输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.
select e.ename , e.sal , e.deptno , d.dname , d.loc
from emp e , dept d
where e.deptno = d.deptno ; 
--8.使用自连接查询EMP表，输出的列包含员工姓名、主管姓名.
select e.ename employeename,m.ename managername 
from emp e,emp m 
where e.mgr = m.empno; 
--9.在上一题的基础上，思考下为什么输出结果没有KING的信息？ 如果要输出KING的信息，如何修改？
select e.ename employeename,m.ename managername 
from emp e,emp m 
where e.mgr = m.empno(+);  
--10.使用左连接查询员工部门，包括没有员工的部门信息，输出列：部门编号、部门名称、位置。 --（左表为dept表，emp为右表）
select e.deptno , d.dname , d.loc
from dept d left join emp e
on e.deptno = d.deptno ;--11.查询EMP表，输出每个部门的平均工资，并按部门编号降序排列.
select deptno , avg(sal)
from emp
group by deptno
order by deptno desc ; 
--12.查询EMP表，输出每个职位的平均工资，按平均工资升序排列.
select job , avg(sal)
from emp
group by job 
order by avg(sal) ; 
--13.查询EMP表，输出每个部门的各个职位的平均工资，并按部门编号升序、平均工资降序排序。
select deptno , job , avg(sal)
from emp 
group by deptno , job 
order by deptno ,avg(sal) desc ; 
--14.使用子查询，找出哪个部门下没有员工
select deptno
from dept
where deptno != all
(select deptno
from emp );--15.使用子查询，找出那些工资低于所有部门的平均工资的员工
select ename , sal
from emp 
where sal < all
(select avg(sal) de_sal
from emp
group by deptno) ; 
--16.使用子查询，找出那些工资低于任意部门的平均工资的员工，比较一下与上一题输出的结果是否相同？
select ename , sal
from emp
where sal < any
(select avg(sal) de_sal
from emp
group by deptno) ; 
--17.在EMP表中，增加一名员工，员工信息参照现有员工构造.
insert into emp
values (1111 , 'aa' , upper('salesman') , 7698 , sysdate , 8000 , 1000 , 40); 
 
--18.员工SMITH部门调动到SALES部门，请编写SQL语句更新员工信息.
update emp
set deptno = '30'
where ename = 'SMITH';--19.员工JAMES已经离职，请编写SQL语句更新数据库.
delete from emp
where ename = 'JAMES'; 
--20.用户执行delete from emp;语句删除了EMP表的记录，但没有提交，请问有办法恢复EMP原来的数据吗？ 
rollback 
--21.得到平均工资大于2000的工作职种
select job , avg(sal)
from emp
group by job
having avg(sal)>2000; 
--22.分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500
方法一：
create or replace view emp_sal
as select ename , sal ,deptno
from emp
where sal>2000;
select deptno,avg(sal) dept_sal
from emp_sal 
group by deptno
having avg(sal)>2500;方法二：
select deptno , avg(sal) deptno
from emp
where sal >2000
group by deptno 
having avg(sal)>2500;--23.得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 
select *
from 
(
select d.deptno , d.dname , d.loc ,sum(sal)
from dept d , emp e
where e.deptno = d.deptno 
group by d.deptno , d.dname , d.loc 
order by sum(sal)
)
where rownum = 1 ; 
--24.分部门得到平均工资等级为2级（等级表）的部门编号 
select * from salgrade;
select deptno , avg(sal) dsal
from emp 
group by deptno
having avg(sal) between 1201 and 1400; 
--25.查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置
select * 
from
(select ename , dname , loc ,sal , rownum rn
from(
select e.ename , d.dname , d.loc ,e.sal
from emp e , dept d 
where e.deptno!=(30) and e.deptno = d.deptno
order by sal desc))
where rn between 3 and 5--26.查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/
select empno , ename , shouru
from
(
select a.ename , a.empno , a.sal+nvl(a.comm,0) shouru , b.sal+nvl(b.comm,0) shouru_2
from emp a , emp b
where b.empno = a.mgr
)
where shouru >shouru_2 
--27.查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */
select avg(sal)
from emp
where job = (select job from emp where ename = upper('smith'))
or 
job = (select job from emp where ename = upper('martin')); 
--28.查找出不属于任何部门的员工
select ename
from emp
where deptno is null; 
--29.按部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字，部门位置）
select dname ,loc
from (select *
from (select rownum rn , deptno
from(select deptno , count(*)
from emp
group by deptno
order by count(*) desc))
where rn between 2 and 5) a ,dept b
where a.deptno = b.deptno 
--30.查询出king所在部门的部门号\部门名称\部门人数
select denum , a.deptno ,dname
from
(select count(deptno) denum ,deptno
from emp 
where deptno = 
(select deptno
from emp
where ename = 'KING')
group by deptno) a ,dept b
where a.deptno = b.deptno ; 
--31.查询出king所在部门的工作年限最大的员工名字
select ename 
from(
select * 
from emp
where deptno = (
select deptno 
from emp
where ename = 'KING')
order by hiredate )
where rownum = 1 
--32.查询出工资成本最高的部门的部门号和部门名称 
select a.deptno ,b.dname
from
(select sum(sal),deptno
from emp
group by deptno 
order by sum(sal) desc) a , dept b
where a.deptno = b.deptno 
and rownum = 1 ; 
--33.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. 
select ename , job , sal
from emp
order by job desc , sal desc; 
--34.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面. 
select ename , to_char( hiredate , 'YYYY MM' )
from emp
order by to_char( hiredate , 'MM' ) ,to_char (hiredate , 'dd'); 
--35.显示在一个月为30天的情况所有员工的日薪金,忽略余数
select ename , trunc(sal/30)
from emp ; 
--36.找出在(任何年份的)2月受聘的所有员工。
select ename
from emp 
where to_char ( hiredate , 'mm' ) = 2 ; 
--37.对于每个员工,显示其加入公司的天数. 
select ename , ceil(to_number (to_char(sysdate - hiredate))) hireday
from emp ; 
--38.显示姓名字段的任何位置包含"A"的所有员工的姓名.
select ename
from emp
where ename like '%A%'; 
--39.以年月日的方式显示所有员工的服务年限. 
--年
select ename , round(to_number (to_char(sysdate - hiredate))/365 ) year
from emp ;--月
select ename , round(to_number (to_char(sysdate - hiredate))/30 ) month
from emp ;--日
select ename , ceil(to_number (to_char(sysdate - hiredate)) ) month
from emp ; 
--40.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面. 
select ename , hiredate
from emp
order by hiredate 
--41.
假设order_status2 表结构如下：
Name Type Nullable Default Comments
------------------------------- ------------------------- ------------ ------------ --------
ID INTEGER
STATUS CHAR(10) Y
LAST_MODIFIED DATE Y SYSDATE
MODIFIED_BY INTEGER Y
INITIALLY_CREATED DATE sysdate
TEST VARCHAR2(15) Y
TESTB VARCHAR2(10) Y 'testb'--按表结构信息在数据中创建order_status2表
create table order_status3
(
ID INTEGER not null,
STATUS CHAR(10) , 
LAST_MODIFIED DATE default SYSDATE , 
MODIFIED_BY INTEGER , 
INITIALLY_CREATED DATE default sysdate not null , 
TEST VARCHAR2(15), 
TESTB VARCHAR2(10) default 'testb'); 
--修改字段test, 使其不允许为空且给它赋予一个缺省的值testing;
alter table order_status2 modify(test default 'testing' not null);
 
--给order_status2表添加注释, 并为其每一个字段添加相应的注释.
comment on table order_status2 is '1';
comment on column order_status2.id is '1';
comment on column order_status2.status is '1';
comment on column order_status2.last_modified is '1';
comment on column order_status2.initially_created is '1';
comment on column order_status2.test is '1';
comment on column order_status2.testb is '1';
comment on column order_status2.MODIFIED_BY is '1'; 
--42.在41题创建的order_status2表的基础上完成以下练习：
--给order_status2表的status列添加一个check约束, 使其只允许输入Male和Female;alter table order_status
add constraint order_status_check
check (status in ('male' , 'female')); 
--为这个表的id列添加一个外键约束, 外键约束的表为employees, 对应的列为employee_id;
alter table order_status
add constraint order_status_f_id
foreign key (id) references employees(employee_id); 
--43.对emp表中sal、comm进行加计算，并使用别名命令为员工的月总收入，同时展示出员工部门编号、员工姓名信息。
select ename , empno , sal+nvl(comm , 0) "月总收入"
from emp; 
--44.使用连接符查询emp表中员工的姓名和工资，并以如下格式列出且字段名展示为 TOTAL INCOME：
--SMITH total income is XXXXXselect ename||' total income is'||sal as "TOTAL INCOME"
from emp ;--45.使用distinct排重查询emp中的job类型
select distinct job from emp;
 
--46.从emp表中找出奖金高于 薪水60%的员工
select ename
from emp
where nvl(comm , 0 ) >nvl(comm , 0 )*0.6 ;--47.找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。
select * 
from emp
where deptno = 10 and job ='MANAGER' or deptno = 20 and job = 'CLERK'; 
--48.从emp和dept中联合查询，并将员工编号、姓名、职位、地址信息列出。
select e.empno , ename ,job , loc
from emp e , dept d
where e.deptno = d.deptno; 
--49.统计各部门的薪水总和。
select sum(sal) ,deptno
from emp
group by deptno; 
--50.找出部门10中所有理（MANAGER），部门20中所有办事员（CLERK）以及既不是经理又不是办事员但其薪水大于或等2000的所有员工的详细资料。
select * 
from emp
where deptno = 10 and job ='MANAGER' 
or deptno = 20 and job = 'CLERK'
or job <> all('MANAGER' , 'CLERK') and sal >= 2000; 
 
--51.列出各种工作的最低工资。
select job , min(sal)
from emp 
group by job; 
--52.列出各个部门的MANAGER（经理）的最低薪水。
select min(sal)
from
(select * 
from
(select sal, job , deptno
from emp)
where job = 'MANAGER')
group by deptno--53.列出有奖金的员工的不同工作。 
select ename , job , comm
from
(select * 
from emp 
where comm is not null); 
--54.找出无奖金或奖金低于300的员工。
select * from emp where comm is null or nvl(comm , 0)<300;
--55.显示所有员工的姓名,并使姓名首字母大写。
select initcap(ename) from emp;
 
--56.显示正好为5个字符的员工的姓名。
select ename from emp where length(ename) = 5;
 
--57.显示不带有“R”的员工姓名。
select ename from emp where ename not like '%R%';
 
--58.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水。
select ename ,sal 
from emp
where sal >all (select sal from emp where deptno = 30 ) ; 
 
--59.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select count(*) "员工人数" , avg(sal) "平均工资" ,avg (trunc(sysdate - hiredate)) "平均服务年限"
from emp
group by deptno ; 
--60.列出从事同一种工作但属于不同部门的员工的一种组合。
select a.ename , b.ename
from emp a , emp b
where a.job = b. job and a.deptno <> b.deptno ; 
--61.列出薪水比“SMITH”多的所有员工。 
select ename
from emp
where sal > (select sal from emp where ename = 'SMITH'); 
--62.列出至少有一个员工的所有部门。 
select distinct deptno
from emp
where deptno in (select deptno from emp ); 
--63.对于每个员工，显示其加入公司的天数、月数、年数。
select ename , trunc(sysdate - hiredate) "天数" , 
trunc(sysdate - hiredate)/30 "月数" , trunc(sysdate - hiredate)/365 "年"
from emp; 
--64.对21中的天数、月数、年数取整显示。
select ename , trunc(sysdate - hiredate) "天数" , 
round(trunc(sysdate - hiredate)/30) "月数" , round(trunc(sysdate - hiredate)/365) "年"
from emp; 
--65.找出在每年5月受聘的所有员工。
select ename
from emp
where to_char(hiredate,'mm') = 5 ; 
--66.显示在一个月为30天的情况下所有员工的日薪水，取整。
select ename , round(sal/30) daysal
from emp; 
--67.显示所有员工的姓名和加入公司的年份和月份，并将员工入职年月从低到高排序。
select ename , to_char(hiredate,'yyyy mm')
from emp
order by hiredate ; 
--68.请查SMITH领导的薪水
select sal
from emp
where empno = (select mgr
from emp
where ename = 'SMITH'); 
--69.请查SMITH领导的薪水和所在的部门地址
select sal ,loc
from emp e , dept d
where empno = (select mgr
from emp
where ename = 'SMITH')
and e.deptno = d.deptno; 
--70.请查SMITH领导的薪水和所在的部门地址 以及领导的薪水等级
select a.* , grade
from
(
select sal , loc 
from emp ,dept 
where empno=(select mgr 
from emp 
where ename = 'SMITH') and dept.deptno=
(select deptno 
from emp 
where ename = 'SMITH')) a ,salgrade
where a.sal
between losal and hisal
;
--或
select a.* , grade ,loc
from 
(select b.sal
from emp a,emp b
where a.mgr=b.empno and a.ename ='SMITH') a ,
salgrade ,dept
where a.sal between losal and hisal
and deptno=(select b.deptno
from emp a,emp b
where a.mgr=b.empno and a.ename ='SMITH'); 
--71.查出SMITH的薪水等级
select grade
from salgrade ,emp
where ename ='SMITH'
and sal between losal and hisal;--72.请查出SIMIH的薪水等级和他所在部门所在地
select grade , loc
from dept d, salgrade ,emp e
where ename ='SMITH' and e.deptno = d.deptno
and sal between losal and hisal; 
 
--73.按照职位分组，求出每个职位的最大薪水
select max(sal)
from emp
group by job; 
--74.
--I)求出每个部门中的每个职位的最大薪水select max(sal),deptno , job
from emp
group by deptno ,job ; 
 
--II)在薪水大于1000，并且职位不是MANAGER的员工中，求职哪个职位的平均薪水大于2000
select job
from emp
where sal>1000 and job <> 'MANAGER'
group by job
having avg(sal)>2000;--75.列出SMITH的薪水和职位
select sal , job
from emp 
where ename = 'SMITH'; 
--76.列出SMITH的部门地址和补贴和薪水等级（等级表salgrade)
select ename , loc , comm , grade
from emp e , dept d ,salgrade
where ename = 'SMITH' and e.deptno = d.deptno 
and e.sal between losal and hisal 
--77.列出薪金比"SMITH"多的所有员工
select ename
from emp
where sal>(
select sal
from emp
where ename = 'SMITH'); 
--78.列出所有员工的姓名及其直接上级的姓名
方法一：
select a.ename "直接上级", b.ename "员工姓名"
from emp a , emp b
where a.mgr = b.empno(+);方法二：
select a.ename "直接上级", b.ename "员工姓名"
from emp a left outer join emp b 
on a.mgr = b.empno(+); 
--79.列出部门不是10，职位不是C开头的，薪资比公司平均薪资都高的员工名字
select ename 
from emp
where deptno != 10 and job not like 'C%' 
and sal>(select avg(sal) from emp ); 
--80.哪个部门下面没有员工
select deptno 
from dept
where deptno <>all
(select deptno
from emp); 
--81.谁的薪水比SMITH多，同时部门又是和SCOTT的部门相同
select ename
from emp
where sal > (select sal from emp where ename = 'SMITH')
and deptno = (select deptno from emp where ename ='SCOTT'); 
--82.列出薪资比每个部门每个职位的平均薪资还要高的员工
select ename
from emp 
where sal >all(select avg(sal) from emp
group by deptno, job ); 
--83.列出至少有一个员工的所有部门
select distinct deptno
from emp; 
--84.新员工入职，请新增一个用户
insert into emp(empno , ename , hiredate) 
values(1111 , 'aaaa' , sysdate); 
--85.SMITH的职位变更为SCOTT的职位
update emp 
set job = (select job from emp where ename = 'SCOTT')
where ename = 'SMITH'; 
好久没有写博客了  最近工作比较忙，最近要查的数据挺多的  又回过头来 再练习一下以往的，大家可以把答案先删除掉  然后再做  这样效果会更好一些

    学而不思则罔，思而不学则殆