Oracle初探（二）

基础查询

--1.查询部门编号为30的职务种类（不重复）
select job from emp where deptno=30;--重复

select distinct job from emp where deptno=30;--不重复

--2.创建一个查询，显示员工编号为7788的员工的姓名和部门编号
select ename,deptno from emp where empno=7788;

--3.查询工资高于3000的员工的姓名和薪资
select ename,sal from emp where sal>3000;

--4.查询工资低于3000的员工
select * from emp where sal<3000;

--5.查询在1981年2月20日和1985年5月1日之间入职的员工的姓名、职务和日期
select ename,job,hiredate from emp where hiredate between date'1981-2-20' and date'1985-5-1';

--6.显示在1981年聘用的每位员工的姓名和聘用日期
select ename,hiredate from emp where hiredate between date'1981-1-1' and date'1985-12-31';

--7.查询没有佣金的员工
select * from emp where comm is null;

--8.查询姓以B开头的员工
select * from emp where ename like 'B%';

--9.查询部门号为10或者20或者30的员工
select * from emp where deptno in(10,20,30);

--10.查询没有经理的所有员工的姓名和职称
select ename,job from emp where mgr is null;

--11.显示员工名字中的第三个字母为“A”的所有员工的姓名
select ename from emp where ename like '__A%';

--12.集合
--12.1 并集
--12.1.1 union
select ename,sal from emp where sal>1000
union
select ename,sal from emp where job='MANAGER';
--12.1.2 union all
select ename,sal from emp where sal>1000
union all
select ename,sal from emp where job='MANAGER';

--12.2 交集
select ename,sal from emp where sal>1000
intersect
select ename,sal from emp where job='MANAGER';

--12.3 剪集
select ename,sal from emp where sal>1000
minus
select ename,sal from emp where job='MANAGER';


--13.排序
--13.1 查询员工资料，按薪资升序排列
select * from emp order by sal asc;

--13.2 查询员工资料，在部门号从大到小的情况下按按薪资升序排列
select * from emp order by deptno desc, sal asc;

--13.3 按姓名的字母顺序显示部门20和部门50中的所有员工的姓名和部门编号
select ename,deptno from emp where deptno in(20,50)order by ename; 

--13.4 显示可以赚取佣金的所有员工的姓名、薪金和佣金，按薪金和佣金的降序对数据进行排序
select ename,sal,comm from emp where comm is not null order by sal desc,comm desc;


--14 函数
--14.1、显示当前日期
select sysdate from dual;

--14.2 显示当前日期，格式为****年**月**日，别名为hday
select substr(to_char(sysdate,'dl'),1,11) as hday from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') as hday from dual;

--14.3 编写一个查询，显示姓名以J、A或M开始的所有员工的姓名（第一个字母大写，其余字母小写）和姓名的长度，给每列一个合适的标签；
select concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)))) as lname,length(ename)as len from emp where ename like 'J%' or ename like 'A%' or ename like'M%';

--14.4 计算每位员工截止到当前时间入职的星期数，别名为weeks_worked。按聘用的星期数对结果进行排序。该星期数舍入到最接近的整数。同时显示员工的名字；
select ename,round((sysdate - to_date(hiredate))/7)as weeks_worked from emp order by weeks_worked ;

--14.5 计算每位员工截止到当前时间入职的月数，别名为months_worked。该星期数舍入到最接近的整数。同时显示员工的名字。   
select ename,round(months_between(sysdate,to_date(hiredate)))as months_worked from emp order by months_worked ;

--14.6 查询在1981年2月20日和1987年5月1日之间入职的员工的姓名、职务标识和起始日期
select ename,job,hiredate from emp where hiredate<=to_date('1987-5-1','yyyy-mm-dd') and hiredate>=to_date('1981-2-20','yyyy-mm-dd');

--14.7 创建一个查询。显示所有员工的姓名和薪金。将薪金格式规定为15个字符长，左边填充$
select ename,lpad(sal,15,'$') from emp;                 

--14.8 显示员工的姓名、聘用日期和该员工在星期几开始工作的
select ename,hiredate,to_char(hiredate,'day')as start_worked from emp;

--14.9 使用decode函数编写一个查询，使其按照以下数据根据job列的值显示所有员工的级别，同时显示员工的姓名
select ename,job,decode(job,'PRESIDENT','A','MANAGER','B','ANALYST','C','SALESMAN','D','CLERK','E') as grade from emp;


--15.分组查询
--15.1 求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名
select avg(sal),max(sal),min(sal),sum(sal)
from emp;

--15.2 求每种工作的平均工资
select job,avg(sal) 
from emp
group by job;

--15.3 求每个部门中同一种工作的平均工资,同时显示部门号,按部门号升序显示
select deptno,avg(sal) 
from emp 
group by job,deptno 
order by deptno asc;

--15.4 查询出各部门的部门编号以及各部门的总工资和平均工资,按部门编号升序排列
select deptno,sum(sal),avg(sal) 
from emp 
group by deptno 
order by deptno;

--15.5 显示每种工作的人数
select job,count(*)
from emp 
group by job;

--15.6 显示员工最高工资超过3000的部门的id及其员工最高工资
select deptno,max(sal) 
from emp 
where sal>3000 
group by deptno;

--15.7 显示每种工作的最低、最高、总计和平均工资
select job,min(sal),max(sal),sum(sal),avg(sal) 
from emp 
group by job;

--15.8 确定经理的人数
select count(distinct mgr)
from emp;

--15.9 显示最高薪资和最低薪资的差别，标记为DIFFERENCE
select max(sal)-min(sal)as difference 
from emp;

--15.10 显示非销售人员(SALESMAN)工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资和排序
select job,sum(sal)
from emp 
where job<>'SALESMAN' 
group by job 
having sum(sal)>5000 
order by sum(sal);

--15.11 显示经理编号以及该经理所管理员工的最低薪资。不包括其经理未知的任何员工。排除最低薪资不超过2000的所有组。按最低薪资降序对输出进行排序。
select mgr,min(sal)
from emp 
where mgr is not null 
group by mgr 
having min(sal)>2000 
order by min(sal) desc;

--15.12 显示在1981、1982、1980和1987年份每年聘用的员工总数,并按年号排序
select to_char(hiredate,'yyyy'),count(*)
from emp
group by to_char(hiredate,'yyyy')
order by to_char(hiredate,'yyyy');