60道部门和员工的查询

DROP TABLE emp;
drop table dept;

CREATE TABLE dept(

deptno INT PRIMARY KEY,

dname VARCHAR(20),

loc VARCHAR(20)

)



CREATE TABLE emp(

empno INT PRIMARY KEY,

ename VARCHAR(20) NOT NULL,

job VARCHAR(20) CHECK (job IN ('CLERK','SALESMAN','MANAGER','SALESMAN','ANALYST')),

mgp INT ,

hiredate date ,

sal DECIMAL(10,2),

comm DECIMAL(10,2),

DEPTNO int

)

INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK');

INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

insert into emp values(7369,'SMITH','CLERK',7902,'17-12月-1980',1640,NULL,20);

insert into emp values(7499,'ALLEN','SALESMAN',7698,'20-2月-1981',11400,300,30);

insert into emp values(7521,'WARD','SALESMAN',7698,'22-2月-1981',5200,500,30);

insert into emp values(7566,'JOENS','MANAGER',7839,'2-4月-1981',7015,NULL,20);

insert into emp values(7654,'MARTIN','SALESMAN',7698,'28-9月-1981',5200,1400,30);

insert into emp values(7698,'BLAKE','MANAGER',7839,'1-5月-1981',5900,NULL,30);

insert into emp values(7782,'CLARK','MANAGER',7839,'9-6月-1981',2470,NULL,10);

insert into emp values(7788,'SCOTT','ANALYST',7566,'19-4月-1981',3040,NULL,20);

insert into emp values(7844,'TURNER','SALESMAN',7698,'17-12月-1980',6200,0,30);

insert into emp values(7876,'ADAMS','CLERK',7788,'8-9月-1981',2240,NULL,20);

insert into emp values(7900,'JAMES','CLERK',7698,'23-5月-1987',4000,NULL,30);

insert into emp values(7902,'FORD','ANALYST',7566,'3-12月-1981',3040,NULL,20);

insert into emp values(7934,'MILLER','CLERK',7782,'3-12月-1982',2620,NULL,10);

SELECT * FROM emp;

select * from dept;



（1） 查询20部门的所有员工信息。
 select * from emp where deptno = 20

（2） 查询所有工种为CLERK的员工的员工号、员工名和部门号。
select empno,ename,deptno from emp where job = 'CLERK'

（3） 查询奖金（COMM）高于工资（SAL）的员工信息。
select * from emp where comm>sal


（4） 查询奖金高于工资的20%的员工信息。
select * from emp where comm>sal*0.2


（5） 查询10号部门中工种为MANAGER和20部门中工种为CLERK的员工的信息。
select * from emp where (deptno =10 and job = 'MANAGER') 
or (deptno = 20 and job = 'CLERK')

（6） 查询所有工种不是MANAGER和CLERK，
select * from emp where job not in('MANAGER','CLERK');

--且工资大于或等于2000的员工的详细信息。
select * from emp where job not in('MANAGER','CLERK')
and sal >=2000



（7） 查询有奖金的员工的不同工种。
select distinct job from emp where comm>0


（8） 查询所有员工工资与奖金

的和。
select ename,sal+nvl(comm,0) 总和 from emp


（9） 查询没有奖金或奖金低于100的员工信息。
select * from emp where comm is null or comm<100


--（10） 查询各月倒数第3天(倒数第2天)入职的员工信息。
select last_Day(hiredate)-2 from emp


（11） 查询工龄大于或等于25年的员工信息。
select * from emp where 
to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')>=25

--（12） 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
select initcap(ename)  from emp
（13） 查询员工名正好为6个字符的员工的信息。
select * from emp where ename like '______'; 

（14） 查询员工名字中不包含字母“Ｓ”的员工。
select * from emp where ename not like '%S%';

（15） 查询员工姓名的第二字母为“M”的员工信息。
select * from emp where ename like '_M%';

--（16） 查询所有员工姓名的前三个字符。
select substr(ename,1,3) from emp 
--（17） 查询所有员工的姓名，如果包含字母“S”，则用“s”替换。
select replace(ename,'S','s')  from emp
--返回被替换了指定子串的字符串。


（18） 查询员工的姓名和入职日期，并按入职日期从先到后进行排序。
select ename,hiredate from emp order by hiredate;

（19） 显示所有员工的姓名、工种、工资和奖金，按工种降序排序，
select ename,job,sal,comm from emp order by job desc, sal;
--若工种相同则按工资升序排序。


（20） 显示所有员工的姓名、入职的年份和月份，
--按入职日期所在的月份排序，若月份相同则按入职的年份排序。
select ename,to_Char(hiredate,'yyyy') 年,
to_Char(hiredate,'mm') 月 from emp

（21） 查询在2月份入职的所有员工信息。
select * from emp where to_char(hiredate,'mm')='02'

（22） 查询所有员工入职以来的工作期限，用“XX年XX月XX日”的形式表示。

select 
floor(months_between(sysdate,hiredate)/12) ||'年'||
floor(mod(months_between(sysdate,hiredate),12)) ||'个月'||
ceil(sysdate-add_months(hiredate,floor(months_between(sysdate,hiredate)))) ||'日'
,hiredate from emp
1.先取得两个时间的月份差，用月差去除12向下取整得到整年份
2.再用两个时间的月份差%12得到多出来的月份，向上取整
3.把两个时间的月份整的差添加在原本的日期上得到新的时间
再用当前系统时间去减去那天的时间得到一个日，向上取整





（23.1） 查询至少有一个员工的部门信息。
select * from dept where deptno in
(select  deptno from emp 
group by deptno having count(*)>=1)

（23.2） 查询至少有两个员工的部门信息。
select deptno
from emp group by deptno having count(*)>=2


（24） 查询工资比
SMITH员工工资
高的所有员工信息。

select * from emp where sal >
(select sal from emp where ename= 'SMITH') 


（25） 查询所有员工的姓名及其直接上级的姓名。
select e.ename,m.ename from emp e, emp m where e.mgp= m.empno

（26） 查询入职日期早于其直接上级领导的所有员工信息。

select e.* from emp e, emp m where e.mgp= m.empno
 and e.hiredate<m.hiredate
（27） 查询所有部门及其员工信息，包括那些没有员工的部门。
select dept.* ,emp.* from dept ,emp where dept.deptno
=  emp.deptno(+)
（28） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。
select dept.* ,emp.* from dept ,emp where dept.deptno(+)
=  emp.deptno

（29） 查询所有工种为CLERK的员工的姓名及其部门名称。
select ename,
(select dname from dept where e1.deptno = dept.deptno ) 
from emp e1 where job = 'CLERK'
in适合做外表数据大的
exists做内部数据大的

（30） 查询最低工资大于2500的各种工作。
select job
from emp group by job having min(sal)>2500

-----------------------------------------------------------------
（31） 查询平均工资低于2000的部门及其员工信息。
select * from emp where deptno in
(select deptno 
from emp group by deptno having avg(sal)>3000)

（32） 查询在SALES部门工作的员工的姓名信息。
select * from dept

select b.dname from emp a, dept b where a.deptno=b.deptno and b.dname='SALES'

（33） 查询工资高于公司平均工资的所有员工信息。
select * from emp where sal>
(select avg(sal) from emp)

（34） 查询出与SMITH员工从事相同工作的所有员工信息。
select * from emp where job in
(select job from emp where ename ='SMITH' )

（35） 列出工资等于30部门中某个员工的工资的所有员工的姓名和工资。
select * from emp where sal in
(select sal from emp where deptno = 30)

（36） 查询工资高于30部门工作的所有员工的工资的员工姓名和工资。
select * from emp where sal >
(select max(sal) from emp where deptno = 30)

（37） 查询每个部门中的员工数量、平均工资和平均工作年限。
select deptno,count(*),avg(sal),
avg(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy'))
from emp group by deptno


（38） 查询从事同一种工作但不属于同一部门的员工
信息。
select * from emp where empno in
(select 
 max(dd.empno)
from
(select distinct e1.*
from emp e1,emp e2 where 
e1.job = e2.job and e1.deptno!=e2.deptno) dd
group by dd.deptno,dd.job)

（39） 查询各个部门的详细信息以及部门人数、部门平均工资。
select deptno,
(select dname from dept where dept.deptno=emp.deptno),
count(*),avg(sal)
from emp group by deptno

（40） 查询各种工作的最低工资。
select job,min(sal)
 from emp group by job

（41） 查询各个部门中不同工种的最高工资。
select deptno,job,max(sal)
from emp group by deptno,job

（42） 查询10号部门员工及其领导的信息。
select e.*,b.*
from emp e,emp b where e.deptno = 10
and e.mgp = b.empno

（43） 查询各个部门的人数及平均工资。
select e.deptno,max(e.dname),count(e.ename),avg(e.sal) 
from
(select dept.*,emp.sal,emp.ename
from dept left join emp on dept.deptno = emp.deptno
) e group by e.deptno

select deptno,count(*),avg(sal)
from emp group by deptno 


（44） 查询工资为某个部门平均工资的员工的信息。
select * from emp where sal  in(
select avg(sal) from emp group by deptno )

（45） 查询工资高于本部门平均工资的员工的信息。
select * from emp e1,
(select deptno, avg(sal) pj from emp group by deptno )
e2 where e1.deptno = e2.deptno and e1.sal >e2.pj


（46） 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
select e1.*,e2.pj from emp e1,
(select deptno, avg(sal) pj from emp group by deptno )
e2 where e1.deptno = e2.deptno and e1.sal >e2.pj


（47） 查询工资高于20号部门某个员工工资的员工的信息。
select * from emp where sal >
(select min(sal) from emp where dpetno = 20)

（48）统计各个工种的员工人数与平均工资。
select job ,count(*),avg(sal) from emp group by job 

（49） 统计每个部门中各工种的人数与平均工资。
select job ,deptno,count(*),avg(sal) from emp 
group by deptno,job 

（50） 查询其他部门中工资、奖金与30号部门某员工工资、




（51） 查询部门人数大于5的部门的员工信息。



（52） 查询所有员工工资都大于1000的部门的信息。



（53） 查询所有员工工资都大于1000的部门的

信息及其员工信息。


（54） 查询所有员工工资都在900～3000之间的部门的信息。


（55） 查询有工资在900～3000之间的员工所在部门的员工信息。



（56） 查询每个员工的领导所在部门的信息。


（57） 查询人数最多的部门信息。



（58） 查询30号部门中工资排序前3名的员工信息。
select sc.*,rownum from
(select * from t_score sc order by degree desc) sc
where rownum <4
（59） 查询所有员工中工资排序在5到10名之间的员工信息。
select sc1.*,sc1.r from
(select sc.*,rownum r from
(select * from t_score sc order by degree desc) sc)
sc1 where  r between 5 and 10

（60） 查询指定年份之间入职的员工信息。(1980-1985)


create table testchar(
       cname varchar2(50) 
)

select lower(cname)  from testchar
select upper(cname) from testchar
select translate(cname,'abcABC','123一二三') from testchar

create table testnumber(
       number1  number
)
select power(number1,2) from testnumber
select trunc(number1,-2) from testnumber


select months_between(sysdate,hiredate)/12 from emp
select add_months(hiredate,-1) from emp order by hiredate

insert into emp (empno,ename,hiredate) values (999,'呵呵',sysdate)
select next_day(hiredate,'星期四') from emp
select last_day(hiredate) from emp