--01 按以下格式显示下面的信息，条件是工资大于1500的。?
--? 部门名称? 姓名? 工资?
select dname,ename,sal?
from dept,emp?
where dept.deptno = emp.deptno and sal > 1500?

--2 按以下格式显示下面信息，条件是此人工资在所有人中最高。?
--? 部门? 姓名? 工资?
select dname,ename,sal?
from dept,emp?
where dept.deptno = emp.deptno?
? and emp.sal = (select max(sal) from emp)?

--3 按以下格式显示下面信息?
--? 某人 为 某人 工作?
select e.ename || '为' || m.ename || '工作' as 描述?
from emp e,emp m?
where e.mgr = m.empno?

--4 为所有人长工资，标准是：10部门长10%；20部门长15%；?
--?? 30部门长20%其他部门长18%（要求用DECODE函数）?
select ename,deptno,sal,sal*(1+nvl(decode(deptno,10,0.1,20,0.15,30,0.2),0.18)) as newsal?
from emp?

--5?
--根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。?
select ename,hiredate,sal,sal*(1+round(months_between(sysdate,hiredate)/100.0)) as newsal?
from emp?

--6 查询出king所在部门的部门号\部门名称\部门人数?
--ex1?
select d.deptno,d.dname,count(*)?
from dept d,emp e,emp m?
where d.deptno = e.deptno?
? and e.deptno = m.deptno?
? and m.ename = 'KING'?
group by d.deptno,d.dname?

--ex2?
select d.deptno,d.dname,count(*)?
from dept d,emp e?
where d.deptno = e.deptno?
? and e.deptno = (select deptno from emp where ename = 'KING')?
group by d.deptno,d.dname?

--7 查询出king所在部门的工作年限最大的员工名字?
select ename,hiredate?
from emp?
where (deptno,hiredate) in (select deptno ,min(hiredate)?
?????????????????????????? from emp?
?????????????????????????? where deptno in (select deptno?
?????????????????????????????????????????? from emp?
?????????????????????????????????????????? where ename = 'KING')?
?????????????????????????? group by deptno)?

--8 查询出管理员工人数最多的人的名字和他管理的人的名字?
with m as?
( select empno,ename?
? from emp?
? where empno in ( select mgr?
?????????????????? from emp?
?????????????????? group by mgr?
?????????????????? having count(*)>= all ( select count(*)?
?????????????????????????????????????????? from emp?
?????????????????????????????????????????? group by mgr)?
????????????????? )?
)???????????????????????????????????????????
select ename,'manager' as type from m?
union?
select e.ename, 'emp' as type from m,emp e where e.mgr = m.empno?


--9 查询出工资成本最高的部门的部门号和部门名称?
select d.deptno,d.dname?
from dept d,emp e?
where d.deptno = e.deptno?
group by d.deptno,d.dname?
having sum(e.sal) >= all (select sum(sal)??
????????????????????????? from emp?
????????????????????????? group by deptno)?
????????????????
--10 查询出工资不超过2500的人数最多的部门名称?
select d.deptno,d.dname?
from dept d,emp e?
where d.deptno = e.deptno?
??? and e.sal <= 2500?
group by d.deptno,d.dname?
having count(*) >= all (select count(*)?
??????????????????????? from emp?
??????????????????????? where sal <= 2500?
??????????????????????? group by deptno)?

--11 查询出没有下属员工的人的名字和他的职位?
select ename,job?
from emp?
where empno not in ( select distinct nvl(mgr,0)?
???????????????????? from emp)?

--12 查询出人数最多的那个部门的部门编号和部门名称?
select d.deptno,d.dname?
from dept d,emp e?
where d.deptno = e.deptno?
group by d.deptno,d.dname?
having count(*) >= all (select count(*)?
??????????????????????? from emp?
??????????????????????? group by deptno)?

--13 查询出没有员工的那个部门的部门编号和部门名称(要求用两种方法,其中一种要用集合运算)?
--ex1?
select deptno,dname?
from dept?
where deptno not in (select deptno from emp)?