高级查询练习
/*--------------------------------------------- 分组查询 -------------------------------------*/
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
