select * from emp;　　　　　　　　　　--emp全表

select * from dept;　　　　　　　　　　--dept全表

--查询10号部门中工资大于20号部门最高工资的员工信息（子查询，聚合函数）
select * from emp where deptno=10 and sal>(select max(sal) from emp where deptno=20);

select * from emp where deptno=10 and sal>all(select sal from emp where deptno=20);

 

--查询最低工资低于2000的部门及其员工信息。（别名，联合查询join on，分组，having条件）

--最低工资低于2000的部门和其中工资低于2000的员工信息
select e.*,d.* from emp e join dept d on e.deptno=d.deptno
where exists (select e2.deptno from emp e2 where e.empno=e2.empno group by e2.deptno having min(sal)<2000);
--最低工资低于2000的部门和其中所有的员工信息
select e.*,d.* from emp e join dept d on e.deptno=d.deptno
where exists (select e.deptno from emp group by e.deptno having min(sal)<2000);

 

--查询员工工龄大于或等于10年的员工信息。（to_char（sysdate,'yyyy-mm-dd'）类型转换，sysdate当前日期）
select hiredate,(sysdate-hiredate)/365,to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')
from emp where (sysdate-hiredate)/365>=10;

select hiredate,(sysdate-hiredate)/365 from emp where (sysdate-hiredate)/365>=10;

 

--查询每个部门中的员工数量、平均工资和平均工作年限（to_char类型转换，全连接，分组，聚合函数）
select d.deptno,count(job),avg(sal),avg(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy'))
from emp e full join dept d on e.deptno=d.deptno group by d.deptno;

--查询各个部门中工资最高的员工信息（开窗函数dense_rank，rank，row_number区分，partition by分组排序）
select * from emp where sal=any(select max(sal) from emp group by deptno);（不建议，处理同部门工资并列第一的逻辑有问题）
select * from (select e.*,dense_rank() over(partition by deptno order by sal desc) r from emp e) where r=1;（rank也可处理此问题，但不能处理类似取前三名并列的情况）

--查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；



--较简单，即c001课程成绩大于c002任何一名同学的成绩就成立（内连接，any）　


select sno, sname　　　　　
　　from student
　　join sc
　　 on sc.sno = student.sno
　where cno = 'c001'
　　and score > any (select score from sc where cno = 'c002');

--（每位同学的成绩一一对应，本同学的c001成绩大于他自己的c002成绩才成立（内连接，自连接）
select s.sno, student.sname
　　from student
　　join (select s1.sno
　　　　　　from sc s1
　　　　　　join sc s2
　　　　　　 on s1.sno = s2.sno
　　　　　where s1.cno = 'c001'
　　　　　　and s2.cno = 'c002'
　　　　　　and s1.score > s2.score) s
　　on s.sno = student.sno;




 --列出各种工作的最低薪金，并让最低薪金加上1500；




select job,min(sal)+1500 from emp_bak group by job;　　--（只查询并显示，不修改表）

update emp_bak　　　　　　　　　　　--更新表中数据
　　set sal = sal + 1500　　　　　　--根据员工编号调整工资
 where empno in (select empno　　　--查出各项工作的最低工资，与原表联合查询得到员工编号
　　　　　　　　　　　from (select job, min(sal) m from emp_bak group by job) a
　　　　　　　　　　　join emp_bak e　　　　--避免出现工资与其他工作最低工资相同的情况　　
　　　　　　　　　　　　on e.sal = a.m　　　　
　　　　　　　　　　　and a.job = e.job);