1015课后作业

emp 表练习

1、查询各部门的员工的人数，显示部门编号 ，人数；

select deptno
      ,count(1) as count_emp
from emp 
group by deptno;

2、查询每个部门的平均工资；

select deptno
      ,avg(sal) as avg_sal
from emp
group by deptno;

3、查询大于3人的部门编号 ，员工数量；

select deptno
      ,count(1) as count_emp
from emp
group by deptno
having count(1)>3;

4、查询每种工作的最高工资、最低工资、人数；

select job
	  ,max(sal) as max_sal
	  ,min(sal) as min_sal
	  ,count(1) as count_emp
from emp
group by job;

5、查出平均工资高于2000的部门；

select deptno
      ,avg(sal) as avg_sal
from emp
group by deptno
having avg(sal)>2000;

7、查出部门人数大于4人的部门编号和部门平均工资；

select deptno
     ,avg(sal) as avg_sal
     ,count(1) as count_emp
from emp 
group by deptno
having count(1)>4;

8、计算部门人数,计算最高工资、最低工资、平均工资，按照人数升序，最高工资降序展示查询结果。

select deptno
      ,max(sal) as max_sal
      ,min(sal) as min_sal
      ,avg(sal) as avg_sal
      ,count(1) as count_emp
from emp
group by deptno
order by count(1) asc, max(sal) desc;

9、查出月收入在1000元到2000元这个范围的员工的最高工资，最低工资，平均工资；

select max(sal) as max_sal
      ,min(sal) as min sal
      ,avg(sal) as avg_sal
from emp 
where sal + nvl(comm,0) between 1000 and 2000;

10、查出最低工资大于1200的部门名称和部门的最高工资，平均工资，按部门降序，最高工资升序排列；

select deptno
	  ,max(sal) as max_sal
	  ,avg(sal) as avg_sal
      ,min(sal)
from emp 
group by deptno
having min(sal) > 1200
order by deptno desc, max(sal) asc;

sal_table 表练习

create table sal_table(
                       sal_man varchar2(20)
                      ,sal_date  varchar2(20)
                      ,sal_product varchar2(20)
                      ,sal_amt number
                      );
---往表中插入数据
insert into sal_table values('张三','1210','苹果手机',10000);
insert into sal_table values('张三','1210','华为手机',15000);
insert into sal_table values('李四','1210','苹果手机',20000);
insert into sal_table values('李四','1210','华为手机',8000);
insert into sal_table values('张三','1211','苹果手机',12000);
insert into sal_table values('张三','1211','华为手机',15600);
insert into sal_table values('李四','1211','苹果手机',20030);
insert into sal_table values('李四','1211','华为手机',8900);
commit;

--提交数据
commit;

--查看数据
select * from sal_table;

主键：联合主键 ，多个字段的数据才能确定唯一的一条数据

–sal_table 表练习

1、计算出每一天总销售额

select sal_date
      ,sum(sal_amt) as sum_amt
from sal_table
group by sal_date;

2、计算出每一种手机的总销售额

select sal_product
      ,sum(sal_amt) as sum_amt      
from sal_table
group by sal_product;

3、计算出每个销售员的总销售额

select sal_man
      ,sum(sal_amt) as sum_amt    
from sal_table
group by sal_man;

4、计算出 李四 卖出的每一种手机总金额

select sal_product
      ,sum(sal_amt) as sum_amt   
from sal_table
where sal_man='李四'
group by sal_product;

5、计算出每一天，卖出的每一种手机的总销售额
提示：分组按照 多个字段分组
group by 分组字段1,分组字段2

select sal_date
      ,sal_product
      ,sum(sal_amt) as sum_amt
from sal_table
group by sal_date,sal_product;