图片 3

分组查询练习

Posted by
  1. 查询各job_id的员工工资的最大值,最小值,平均值,总和

查询练习

1.查询工资大于12000的员工姓名和工资

select last_name, salary
from employees
where salary > 12000

2.查询员工号为176的员工的姓名和部门号

select last_name, department_id
from employees
where employee_id = 176

3.选择工资不在5000到12000的员工的姓名和工资

select last_name, salary
from employees
where salary not between 5000 and 12000

4.选择雇用时间在1998-02-01到1998-05-01之间的员工姓名,job_id和雇用时间

select last_name, job_id, hire_date
from employees
where hire_date between '1-2月 -1998' and '1-5月 -1998'

5.选择在20或50号部门工作的员工姓名和部门号

select last_name, department_id
from employees
where department_id in (20, 50)

6.选择在1994年雇用的员工的姓名和雇用时间

select last_name, hire_date
from employees
where hire_date like '% -94'

7.选择公司中没有管理者的员工姓名及job_id

select last_name, job_id
from employees
where manager_id is null

8.选择公司中有奖金的员工姓名,工资和奖金级别

select last_name, salary, commission_pct
from employees
where commission_pct is not null

9.选择员工姓名的第三个字母是a的员工姓名

select last_name 
from employees
where last_name like '__a%'

10.选择姓名中有字母a和e的员工姓名

select last_name
from employees
where last_name like '%a%' and last_name like '%e%'

11.显示系统时间

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

12.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)

select employee_id,last_name,salary,salary*1.2 "new salary" from employees;

13.将员工的姓名按首字母排序,并写出姓名的长度(length)

select last_name,length(last_name)
from employees
order by last_name

14.查询各员工的姓名,并显示出各员工在公司工作的月份数(worked_month)。

select last_name,months_between(sysdate,hire_date) worked month from employees

15.查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列

select last_name, months_between(sysdate, hire_date) worked_month
from employees
order by worked_month desc

16.做一个查询,产生下面的结果

<last_name> earns <salary> monthly but wants
<salary*3>

图片 1

结果.PNG

select last_name || ' earns ' || salary || ' monthly but wants ' || salary * 3
from employees

17.使用decode函数,按照下面的条件:

图片 2

decode函数.PNG

select last_name, job_id, decode(job_id, 'AD_PRES', 'A',
                                         'ST_MAN', 'B',
                                         'IT_PROG', 'C',
                                         'SA_REP', 'D',
                                         'ST_CLERK', 'E',
                                         'F') GRADE
from employees

18.将第7题的查询用case函数再写一遍。

select last_name, job_id, case job_id when 'AD_PRES' then 'A'
                                      when 'ST_MAN' then 'B'
                                      when 'IT_PROG' then 'C'
                                      when 'SA_REP' then 'D'
                                      when 'ST_CLERK' then 'E'
                                      else 'F'
                           end            
from employees

19.查询公司员工工资的最大值,最小值,平均值,总和

Select max(salary), min(salary), avg(salary), sum(salary)
From employees

20.查询各job_id的员工工资的最大值,最小值,平均值,总和

Select job_id, max(salary), min(salary), avg(salary), sum(salary)
From employees
Group by job_id

21.选择具有各个job_id的员工人数

Select job_id, count(employee_id)
From employees
Group by job_id;

22.查询员工最高工资和最低工资的差距(DIFFERENCE)

Select max(salary) – min(salary) difference
From employees

23.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内

Select manager_id, min(salary)
From employees
Where manager_id is not null
Group by manager_id
Having min(salary) >= 6000

24.查询所有部门的名字,location_id,员工数量和工资平均值

Select department_name, location_id, count(employee_id), avg(salary)
From employees e join departments d
On e.department_id = d.department_id
Group by department_name, location_id

一、找到员工表中工资最高的前三名:

关于行号
1. rownum永远按照默认的顺序生成
2. rownum只能使用 < <=; 不能使用> >=

SQL> select rownum,empno,ename,sal
  2  from (select * from emp order by sal desc)
  3  where rownum<=3;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         1       7839 KING             5000                                                                                                                                                             
         2       7788 SCOTT            3000                                                                                                                                                             
         3       7902 FORD             3000 

2.rownum只能使用 < <=; 不能使用> >=

SQL>  select *
  2   from  (select rownum r,e1.*
  3      from (select * from emp order by sal) e1
  4      where rownum <=8
  5     )
  6   where r >=5;

临时表:

1. create global temporary table *****
2.自动创建: order by
特点:当事务或者会话结束的时候,表中的数据自动删除

所以 oracle中事务提交了 数据不一定保存下来了(临时表),数据不在,但表还在

SQL> create global temporary table test2
  2  (tid number,tname varchar2(20))
  3  on commit delete rows;

表已创建。

SQL> insert into test2 values(1,'Tom');

已创建 1 行。

SQL> select * from test2;

       TID TNAME                                                                                                                                                                                        
---------- --------------------                                                                                                                                                                         
         1 Tom                                                                                                                                                                                          

SQL> commit;

提交完成。

SQL> select * from test2;

未选定行

SQL> desc test2
 名称                                                                                                              是否为空? 类型
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 TID                                                                                                                        NUMBER
 TNAME                                                                                                                      VARCHAR2(20)

二、找到员工表中薪水大于本部门平均薪水的员工:

相关子查询:将主查询中的值作为参数传递给子查询:

SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
  2  from emp e
  3  where sal > (select avg(sal) from emp where deptno=e.deptno);

     EMPNO ENAME             SAL     AVGSAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
      7499 ALLEN            1600 1566.66667                                                                                                                                                             
      7566 JONES            2975       2175                                                                                                                                                             
      7698 BLAKE            2850 1566.66667                                                                                                                                                             
      7788 SCOTT            3000       2175                                                                                                                                                             
      7839 KING             5000 2916.66667                                                                                                                                                             
      7902 FORD             3000       2175  

解:

SQL> select e.empno,e.ename,e.sal,d.avgsal
  2  from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
  3  where e.deptno=d.deptno and e.sal > d.avgsal;

     EMPNO ENAME             SAL     AVGSAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
      7698 BLAKE            2850 1566.66667                                                                                                                                                             
      7499 ALLEN            1600 1566.66667                                                                                                                                                             
      7902 FORD             3000       2175                                                                                                                                                             
      7788 SCOTT            3000       2175                                                                                                                                                             
      7566 JONES            2975       2175                                                                                                                                                             
      7839 KING             5000 2916.66667  

三、统计每年入职的员工个数

图片 3

每年入职的员工个数.PNG

SQL> select count(*) Total,
  2         sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
  3         sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
  4         sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
  5         sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
  6  from emp;

     TOTAL       1980       1981       1982       1987                          
---------- ---------- ---------- ---------- ----------                          
        14          1         10          1          2

1.查询和Zlotkey相同部门的员工姓名和雇用日期

select last_name, hire_date
from employees
where department_id = (
      select department_id
      from employees
      where last_name = 'Zlotkey'
)

2.查询工资比公司平均工资高的员工的员工号,姓名和工资。

select employee_id, last_name, salary
from employees
where salary > (
      select avg(salary)
      from employees
)

3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

select employee_id, last_name, salary
from employees e
where salary > (
      select avg(salary)
      from employees
      where department_id = e.department_id
)

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select employee_id, last_name
from employees
where department_id in (
      select department_id
      from employees
      where last_name like '%u%'
)

5.查询在部门的location_id为1700的部门工作的员工的员工号,

select employee_id
from employees
where department_id in (
      select department_id
      from departments
      where location_id = 1700
)

6.查询管理者是King的员工姓名和工资

select last_name, salary
from employees
where manager_id in (
      select employee_id
      from employees
      where last_name = 'King'
)
  1. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内

select max,min,avg,sum from employees;
select manager_id,min from employees where manager_id is not null having min >=6000 group by manager_id;
  1. 组函数处理多行返回一行
  2. 组函数不计算空值
  3. where子句在分组之前对检索进行过滤
  4. 查询公司员工工资的最大值,最小值,平均值,总和
select job_id,count from employees group by job_id;
select count(employee_id) total,sum(decode(to_char(hire_date,'YYYY'),1995,1,0))"1995",sum(decode(to_char(hire_date,'YYYY'),1996,1,0))"1996",sum(decode(to_char(hire_date,'YYYY'),1997,1,0))"1997",sum(decode(to_char(hire_date,'YYYY'),1998,1,0))"1998"from employees;
  1. 查询公司的人数,以及在1995-1998年之间,每年雇用的人数,结果类似下面的格式

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注