oracle查询题

--oracle测试语句

select /*
student (学生表)
学生编号：sno
学生姓名：sname
学生年纪：sage
学生性别：ssex
---------------------
teacher(老师表)
老师编号：tno
老师姓名：tname
-----------------------
course(课程表)
课程编号：cno
课程名字：cname
对应的老师编号：tno
------------------------
sc(学生成绩表)
学生编号：sno
课程编号：cno
学生成绩，score


*/

--7.23号查询题
--查询没有学全所有课的同学的学号、姓名；
--分析，需要学生表，课程表，学生成绩表
--minus的用法：A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录
select * from student where sno in
(select sno from
(select stu.sno,c.cno from student stu
cross join course c
minus
select sno,cno from sc)
)
--2 查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
select st.* from student st,
(select distinct a.sno from
(select * from sc) a,
(select * from sc where sc.sno='s001') b
where a.cno=b.cno) h
where st.sno=h.sno and st.sno<>'s001'
--查询计划如下
SELECT STATEMENT, GOAL = ALL_ROWS			5	4	104
 MERGE JOIN			5	4	104
  TABLE ACCESS BY INDEX ROWID	SCOTT	STUDENT	2	9	171
   INDEX FULL SCAN	SCOTT	SYS_C0012491	1	9	
  SORT JOIN			3	4	28
   VIEW	SCOTT		2	4	28
    HASH UNIQUE			2	4	80
     NESTED LOOPS			1	5	100
      INDEX FULL SCAN	SCOTT	PK_SC	1	8	80
      INDEX UNIQUE SCAN	SCOTT	PK_SC	0	1	10




--左连接，以左表为主，返回的是左表所有数据，和右表匹配道的数据
select  * from emp left join dept on emp.deptno = dept.deptno
--右连接，以右表为主，返回的是右表所有的数据，和左表匹配到的数据，如果右表行数大与左表，则左表数据用null来代替
select * from emp right join dept on emp.deptno = dept.deptno
--内连接，返回的是两个表的公共部分数据
select * from emp inner join dept on emp.deptno = dept.deptno
select * from dept--40(部门40.)
select * from emp--14



--哪些人的薪水在部门平均薪水之上
--分析：先求出部门的平均薪水，再求哪些人的薪水在部门平均薪水之上
select deptno,avg(sal) from emp group by deptno 
select ename,sal from emp
select b.deptno,emp.ename,emp.sal,b.avgsal from emp inner join (select deptno,avg(sal)as avgsal  from emp group by deptno order by deptno desc ) b on emp.sal > b.avgsal and emp.deptno=b.deptno

--取得部门中所有人的平均的薪水等级
--分析，先求出每个人的薪水等级,再求平均部门薪水等级，是部门薪水等级。
select e.deptno,avg(grade) ,e.ename from emp e join salgrade b on e.sal between b.losal and b.hisal
  group by e.deptno --按照部门进行分组
  
select * from salgrade 

--不准使用组函数Max，给出最高薪水（给出两种解决方案）
select sal,ename from emp  where rownum < 2 order by sal asc
select * from emp order by sal asc

--  取得平均薪水最高的部门和部门编号
--先求出每个部门的平均薪水，再和dept表进行关联，进行排序和抽取
select deptno,avg(sal) from emp group by deptno 
--也有可能多个部门的平均薪水等级相同。再次进行分析查询 通过having
select deptno,avg(sal)l from emp 
group by deptno 
having 
avg(sal)=(select deptno,avg(sal) from emp 
where rownum<2  group by deptno 
order by deptno desc)

--取得平均薪水最高的部门名称
select e.deptno,avg(e.sal),d.dname from emp e join dept d on e.deptno = d.deptno  group by e.deptno

select  b.dname,avg(a.sal) as avgsal  from emp a join dept b on a.deptno=b.deptno group by b.dname


select avg(a.sal),b.dname  from emp a join dept b on a.deptno = b.deptno  group by b.dname 

select * from dept

--7 求平均薪水的等级最高的部门的部门名称
 select avg(d.grade) as avgsal ,a.deptno,b.dname from emp a 
 join salgrade d on a.sal between d.losal and d.hisal 
 group by a.deptno
 join dept b on a.deptno=b.deptno
 

--取得每个薪水等级有多少员工
--先求每个薪水等级的员工，需要emp表和salgrade表
select count(e.ename),s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade
 
 
--列出所有员工及领导的姓名
--分析，因为有最高领导有空值，需要用到左外连接
select * from emp
select e.ename as empname,a.ename as leadname from emp e left join emp a on a.mgr=e.empno 
 
-- 列出薪水比simith多的所有员工信息
select * from emp where sal >(
select sal from emp where ename ='simith')

-- 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
--先列出最低薪水的人
select ename,min(sal) as minsal ,job  from emp  group by job having minsal >1500


--查询题目：
--1 取得每个部门最高薪水的人员名称
--分析，需要分组，取每个部门薪水最高的人
select max(sal),deptno ,ename   from emp group by deptno,ename

--查询佣金为0或为null的员工的信息
select * from emp where comm =0 or comm is null 
--查询入职日期在1981-5-1到1981-12-31之间的所有员工的信息
--to_date
select * from emp where 
 hiredate >to_date('1981-5-1','yyyy-mm-dd')
and
hiredate<=to_date('1981-12-31','yyyy-mm-dd')

--查询所有名字长度为4的员工的员工的编号，姓名
select * from emp where ename like '____' --like 后面跟引号，引号里面只写_。有几个下划线就代表匹配几个字符长度的值
--显示10号部门饿所有经理和20号部门的所有员工 
--使用union进行拼接使用
select ename from emp where deptno =20
union all 
select ename from emp where job = 'MANAGER' and deptno = 10

--示姓名没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from emp where ( ename like '%SM%') or ( ename not like '%L%')

--把hiredate列看做是员工的生日，求下月过生日的员工
--使用时间日期函数   extract:截取时间（年月日）类型数据的函数 
select  * from emp 
Where extract(month from hiredate)=extract(month from sysdate)+1;

--求1982年入职的员工
select *  from emp where extract(year from hiredate)=1982

select count(*),to_char(hiredate,'mm') from emp
where to_char(hiredate,'yyyy')=1981 group by to_char(hiredate,'mm')

select *  from emp where extract(year from hiredate)=1982
union  
select *  from emp where to_char(hiredate,'yyyy')=1982

--union 和union all的区别，去重不去重的问题
--查询各个部门的平均工资 
select deptno, avg(sal) from emp  group by deptno 

--显示各种职位的最低工资
select job ,min(sal) as minsal from emp group by job 
--示工资比’ALLEN’高的所有员工的姓名和工作
select ename,job,sal from emp where sal >(
select sal from emp where ename ='ALLEN')

--显示与scott从事相同工作的员工的信息（子查询） 
select * from emp where job = (select job from emp where ename ='SCOTT')

--显示销售部(‘SALES’)员工的姓名
--可以用join 连接来做
select * from emp where deptno = (select deptno from dept where dname = 'SALES')

--以上结果亲测有效