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