• 组函数和表关系


    1. 组函数练习题

    --单行函数练习

    --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;

     

    2. 组函数练习

    --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.查询同月入职中工资最高的员工信息(用到子查询)

     

     

  • 相关阅读:
    bzoj1861 [Zjoi2006]Book 书架
    bzoj1208 [HNOI2004]宠物收养所
    bzoj1588 [HNOI2002]营业额统计
    bzoj3295 [Cqoi2011]动态逆序对
    bzoj2716 [Violet 3]天使玩偶
    bzoj1176 [Balkan2007]Mokia
    bzoj3262 陌上花开
    spoj FTOUR2
    bzoj2152 聪聪可可
    poj1741 Tree
  • 原文地址:https://www.cnblogs.com/ZXDZXD/p/12401502.html
Copyright © 2020-2023  润新知