Chap2-组函数
Chap2 Oracle中的函数
key point：
单行函数
组函数
group by
having
--单行函数练习
--1.把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
select first_name from EMPLOYEES where to_char(hire_date,'mm')=to_char(sysdate,'mm');
--2.把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
select first_name from EMPLOYEES where to_char(hire_date,'mm')=to_char(add_months(sysdate,1),'mm');
--3.请用三种以上的方式打印2002年入职的员工(考察知识点:单行函数)
select * from EMPLOYEES where to_char(hire_date, 'yyyy') = '2002';--1
select * from EMPLOYEES where hire_date between to_date('2002-1-1', 'yyyy-mm-dd') and to_date('2002-12-30', 'yyyy-mm-dd');--2
select * from EMPLOYEES where hire_date >= to_date('2002-1-1', 'yyyy-mm-dd') and hire_date <= to_date('2002-12-30', 'yyyy-mm-dd');--3
--4.求2002年下半年入职的员工(考察知识点:单行函数)
select * from EMPLOYEES where hire_date between to_date('2002-7-1', 'yyyy-mm-dd') and to_date('2002-12-30', 'yyyy-mm-dd');
--5.打印自己出生了多少天
select to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') - to_date('1999-7-25', 'yyyy-mm-dd') from dual;
--6.判断今年是不是闰年(单行函数，case...when)
select case when (mod(to_char(sysdate,'yyyy'),4) = 0 and mod(to_char(sysdate,'yyyy'),100)!=0) or (mod(to_char(sysdate,'yyyy'),400) = 0)then 'yes' else 'no' end result from dual;
--7.请用两种的方式查询所有名字长度为8的员工的员工编号,姓名
select employee_id,first_name from EMPLOYEES where length(first_name)=8;--1
select employee_id,first_name from EMPLOYEES where first_name like '________';--2
--8.打印下半年入职的员工信息(单行函数)
select * from EMPLOYEES where hire_date between to_date('2002-7-1', 'yyyy-mm-dd') and to_date('2002-12-30', 'yyyy-mm-dd');
--9.打印本周的星期一时间信息
Select to_char(sysdate,'d') from dual;--周六结束，周日开始
--10.打印入职时间超过10年的员工信息
select * from EMPLOYEES where to_char(sysdate,'yyyy') - to_char(hire_date,'yyyy') >= 10;
--组函数练习
--1.显示各种职位的最低工资(组函数)
select job_id,MIN(salary) FROM EMPLOYEES group by job_id;
--2.求1997年各个月入职的的员工个数(考察知识点:组函数)
select EXTRACT(MONTH FROM hire_date) as months, count(1) FROM EMPLOYEES where extract(year from HIRE_DATE)=1997 GROUP BY EXTRACT(MONTH FROM HIRE_DATE) ORDER BY months;
--3.查询每个部门的最高工资(考察知识点:分组)
select department_id, MAX(salary) FROM EMPLOYEES GROUP BY department_id;
--4.查询每个部门,每种职位的最高工资(考察知识点:分组)
select department_id, job_id, MAX(salary) FROM EMPLOYEES GROUP BY department_id, job_id;
--5.查询各部门的总工资
select department_id, SUM(salary) FROM EMPLOYEES GROUP BY department_id;
--6.查询各个部门的平均工资
select department_id, AVG(salary) FROM EMPLOYEES GROUP BY department_id;
--7.查询50号部门,60号部门,70号部门的平均工资
select department_id, AVG(DISTINCT SALARY) FROM EMPLOYEES WHERE department_id IN(50,60,70) GROUP BY department_id;--1
select department_id, AVG(salary) FROM EMPLOYEES where department_id in (50,60,70) group by department_id;--2
select department_id, AVG(SALARY) FROM EMPLOYEES GROUP BY department_id HAVING department_id IN(50,60,70);--3
--8.查询各部门的最高工资,最低工资.
select department_id,MAX(SALARY), MIN(SALARY) FROM EMPLOYEES GROUP BY department_id;
--9.查询各部门的员工总数.
select department_id, COUNT(1) FROM EMPLOYEES GROUP BY department_id;
--10.查询各岗位的员工总数.
select job_id, COUNT(1) FROM EMPLOYEES GROUP BY job_id;
--11.查询各部门中各个岗位的平均工资.
select department_id, job_id, AVG(SALARY) FROM EMPLOYEES GROUP BY department_id, job_id;
--12.查询平均工资高于8000元的部门的最高工资.
select department_id, MAX(SALARY) FROM EMPLOYEES GROUP BY department_id HAVING AVG(SALARY)>8000;
--13.统计公司里经理的人数
select manager_id, COUNT(1) FROM EMPLOYEES GROUP BY manager_id;
--14.查询同月入职中工资最高的员工信息(用到子查询)
