【Oracle】练习与作业2

港控/mmm° 2023-10-08 09:11 6阅读 0赞

【1710252291–实验二】  
根据Oracle数据库Scott方案下的emp表和dept表，完成以下操作：  
（1）查询所有工种为CLERK的员工的姓名及其部门名称。  
查询方式1： select ename 员工姓名，dname 工作部门 from emp e, dept d where e.deptno=d.deptno and job = ‘CLERK’;  
查询方式2：select ename 员工姓名，dname 工作部门 from emp e join dept d on e.deptno=d.deptno and job = ‘CLERK’;  
（2）查询所有部门及其员工信息，包括哪些没有员工的部门。  
查询方式1： select \* from dept d left join emp e on e.deptno = d.deptno;  
（3）查询所有员工及其部门信息，包括哪些还不属于任何部门的员工。  
查询方式1： select \* from emp e left join dept d on e.deptno = d.deptno;  
（4）查询在Sales部门工作的元的姓名。  
查询方式1：select ename 员工姓名 from emp e join dept d on e.deptno = d.deptno where dname = ‘SALES’;  
查询方式2：select ename 员工姓名 from emp e,dept d where e.deptno = d.deptno and dname in(select dname from dept where dname = ‘SALES’);  
（5）查询所有员工的姓名及其直接上级的姓名。  
查询方式1：select a.ename 员工姓名 ，b.ename 直接上司 from emp a,emp b where a.mgr=b.empno;  
查询方式2：select a.ename 员工姓名 , b.ename 直接上司 from emp a join emp b on a.mgr=b.empno;  
（6）查询入职日期早于其上级领导的所有员工的信息。  
查询方式1： select a.\* from emp a,emp b where a.hiredate<b.hiredate and a.mgr = b.empno;  
select a.ename 员工姓名,a.hiredate 入职日期,b.ename 直接上司,b.hiredate 上司的入职日期 from emp a,emp b where a.hiredate<b.hiredate and a.mgr = b.empno;  
查询方式2： select a.\* from emp a,emp b where a.hiredate<b.hiredate and a.mgr = b.empno;  
select a.ename 员工姓名,a.hiredate 入职日期,b.ename 直接上司,b.hiredate 上司的入职日期 from emp a left join emp b on a.mgr = b.empno where a.hiredate<b.hiredate;  
（7）查询从事同一种工作但是不属于同一部门的员工信息。  
查询方式1： select a.*,b.* from emp a,emp b where a.job=b.job and a.deptno <> b.deptno and a.empno <> b.empno;  
查询方式2：  
（8）查询10号部门员工及其领导的信息。  
查询方式1： select a.*,b.* from emp a,emp b where a.deptno =10 and a.mgr = b.empno;  
查询方式2： select a.*,b.* from emp a join emp b on a.mgr=b.empno where a.deptno =10;  
（9）使用UNION将工资大于2500的雇员信息与工作为ANALYST的雇员信息合并。  
查询方式：select \* from emp where sal > 2500 union select \* from emp where job = ‘ANALYST’;  
（10）通过INTERSECT集合运算，查询工资大于2500，并且工作为ANALYST的雇员信息。  
查询方式：select \* from emp where sal >2500 instersect select \* from emp where job = ‘ANALYST’;  
（11）使用MINUS集合查询工资大于2500，但工作不是ANALYST的雇员信息。  
查询方式：select \* from emp where sal >2500 minus select \* from emp where job = ‘ANALYST’;  
（12）查询工资高于公司平均工资的所有员工信息。  
查询方式： select \* from emp where sal > (select avg(sal) from emp );  
（13） 查询与SMITH员工从事形同工作的所有员工信息。  
查询方式：select \* from emp where job = (select job from scott.emp where ename = ‘SMITH’);  
（14）查询工资比SMITH高的所有员工信息。  
查询方式：select \* from emp where sal > (select sal from emp where ename = ‘SMITH’);  
（15）查询比所有在30号部门中工作的员工的工资都搞得员工的姓名和工资。  
查询方式： select ename 员工姓名,sal 工资 from emp where sal > all(select sal from emp where deptno = 30);  
（16）查询部门人数大于5的部门员工的信息。  
查询方式： select \* from emp where deptno in(select deptno from emp group by deptno having count(*)>5);  
（17）查询所有员工工资大于900的部门的信息。  
查询方式： select \* from dept where deptno in (select deptno from emp group by deptno having min(sal)>900);  
（18）查询人数最多的部门信息。  
查询方式：select \* from dept where deptno in (select deptno from (select deptno,count(*) as 人数 from emp group by deptno) where 人数 = (select max(人数) from (select deptno,count(*) as 人数 from scott.emp group by deptno)));  
（19）查询至少有4个员工的部门信息。  
查询方式：select \* from dept where deptno in (select deptno from emp group by deptno having count(*)>=4)；  
（20）查询工资高于本部门平均工资的员工信息。  
查询方式：select \* from emp e where sal > (select avg (sal) from emp group by deptno having e.deptno = deptno);  
（21）查询工资高于本部门平均工资的员工信息及其部门的平均工资。  
查询方式：select \*  
2 from((select \* from emp e where sal > (select avg(sal) from emp group by deptno having e.deptno = deptno)) t1  
3 inner join  
4 (select avg(sal), deptno from emp  
5 group by deptno) t2  
6 on t1.deptno = t2.deptno);  
（22）查询每个员工的领导所在部门的信息。  
查询方式： select \* from dept where deptno in (select b.deptno from emp a join emp b on a.mgr = b.empno group by b.deptno);  
（23）查询平均工资抵御2000的部门及其员工信息。  
查询方式：select \*  
2 from emp a,dept b  
3 where a.deptno = b.deptno and a.deptno in  
4 (select deptno from emp group by deptno having avg(sal)<2000)  
5 ;