oracle学习笔记(十二) 查询练习(二) 高级查询

高级查询练习

/*--------------------------------------------- 分组查询 -------------------------------------*/
create table empployee_demo(
   empno     number(4) not null primary key,  --员工编号,主键
   ename     varchar2(10) not null unique,    --员工名,唯一键
   job       varchar2(9),                     --职位、工作
   mgr       number(4),                       --经理编号
   hiredate  date default sysdate,            --入职日期,默认约束
   sal       number(7,2) check(sal>=500 and sal<=10000),   --工资
   comm      number(7,2),                     --资金
   deptno    number(2)                        --部门编号
)
--28.按各部门的'办事员'分别统计薪资情况,且平均大于1000的

select deptno,avg(sal) avgsal from employee
where job ='CLERK'
group by deptno
having avg(sal)>1000 

--29. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,
--并且要满足从事同一工作的雇员的月工资合计大于$5000,输出结果按月工资排序。
select sal from employee
where job !='saleman'
group by job
having sum(sal) >5000
order by sal;
--30. 查询出各部门的部门编号以及各部门的总工资和平均工资。
select deptno,sum(sal),avg(sal) from employee
group by deptno

--31. 按男生和女生统计JAVA和ORACLE成绩的总分和平均分?
--  1)  建表
CREATE table STUDENT2(
  STUNO       CHAR(4) not null primary KEY,
  STUNAME     VARCHAR2(20),
  GENDER      CHAR(2),
  JAVASCORE   INTEGER,
  ORACLESCORE INTEGER
);
--	2)	插入记录
INSERT INTO STUDENT2 VALUES('1000','JAMES','男',88,78);
INSERT INTO STUDENT2 VALUES('1001','JACK','男',86,79);
INSERT INTO STUDENT2 VALUES('1002','ANDY','女',76,78);
INSERT INTO STUDENT2 VALUES('1003','SAMMY','女',77,76);

--	3)按性别统计成绩:平均分,总成绩等

select avg(javascore),avg(oraclescore),gender from student2
group by gender
----高级查询---
--创表
create table employee as select * from scott.emp;
create table department as select * from scott.dept;
create table salgrade as select * from scott.salgrade;

--employee表结构
EMPNO    NUMBER(4)                              
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y

--deparment表结构
DEPTNO NUMBER(2)                              
DNAME  VARCHAR2(14) Y                         
LOC    VARCHAR2(13) Y 

--salgrade表结构
GRADE NUMBER Y                         
LOSAL NUMBER Y                         
HISAL NUMBER Y  


--32. 查询部门在‘NEW YORK’工资低于4000,不是‘CLERK’的员工?
select * from employee e
	left join department d
		on e.deptno = d.deptno
where sal<4000 and job != 'CLERK' and loc ='NEW YORK'

--33. 查询部门在‘CHICAGO’,在1981年入职,工资在2000~4000的员工?
select * from employee e
	left join department d
		on e.deptno = d.deptno
where sal between 2000 in 4000 and loc = 'CHICAGO' and extract( year from hiredate) = 1981

--34:查询员工及所在的部门信息(部门号,部门名,所在城市)
select ename,e.deptno,dname,loc from employee e
	left join department d on e.deptno = d.deptno;
	
--35:查询在10号部门号的员工及部门信息(部门号,部门名,所在城市)
select ename,e.deptno,dname,loc from employee e
	left join department d on e.deptno = d.deptno
where e.deptno = 10
--36:查询工资低于3000,工作是clerk和salman,部门在"芝加哥”的员工基本信息和员工的部门信息。
select e.*,,d.dname,d.loc
	from employee e
	left join department d on e.deptno = d.deptno
where sal <3000 and (job ='CLERK' or job = 'SALMAN') and loc ='CHICAGO'

/*
37: 问题:查看每个员工的工资等级情况
	1等级-->显示为:临时工
	2等级-->显示为:苦力工
	3等级-->??
*/
select e.*,decode(grade,1,'苦力',2,'临时工','其他')from employee e,salgrade
where sal between losal and hisal

-- 38.查询有上级领导的员工信息以及他的上级领导的信息。显示为:谁(工人)为谁(上级)工作
select e.name 员工,boss.name 上司 from employee e,employee boss
where e.mgr = boss.empno
posted @ 2019-05-19 15:12  Stars-one  阅读(203)  评论(0编辑  收藏