Oracle之关于scott的查询

谁践踏了优雅 2022-05-08 09:24 472阅读 0赞

### 一：scott用户的表结构如下： ###

--------------------

**1.1:部门表dept：**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> &nbsp; </td> 
 <td style="vertical-align:top;"> 名称 </td> 
 <td style="vertical-align:top;"> 类型 </td> 
 <td style="vertical-align:top;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 1 </td> 
 <td style="vertical-align:top;"> DEPTNO </td> 
 <td style="vertical-align:top;"> NUMBER(2) </td> 
 <td style="vertical-align:top;"> 部门编号 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 2 </td> 
 <td style="vertical-align:top;"> DNAME </td> 
 <td style="vertical-align:top;"> VARCHAR2(14) </td> 
 <td style="vertical-align:top;"> 部门名称 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 3 </td> 
 <td style="vertical-align:top;"> LOC </td> 
 <td style="vertical-align:top;"> VARCHAR2(13) </td> 
 <td style="vertical-align:top;"> 部门位置 </td> 
 </tr> 
 </tbody> 
</table>

**1.2:员工表emp：**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> &nbsp; &nbsp;&nbsp; </td> 
 <td style="vertical-align:top;"> 名称 </td> 
 <td style="vertical-align:top;"> 类型 </td> 
 <td style="vertical-align:top;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 1 </td> 
 <td style="vertical-align:top;"> EMPNO </td> 
 <td style="vertical-align:top;"> NUMBER(4) </td> 
 <td style="vertical-align:top;"> 雇员编号 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 2 </td> 
 <td style="vertical-align:top;"> ENAME </td> 
 <td style="vertical-align:top;"> VARCHAR2(10) </td> 
 <td style="vertical-align:top;"> 雇员姓名 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 3 </td> 
 <td style="vertical-align:top;"> JOB </td> 
 <td style="vertical-align:top;"> VARCHAR2(9) </td> 
 <td style="vertical-align:top;"> 雇员职位 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 4 </td> 
 <td style="vertical-align:top;"> MGR </td> 
 <td style="vertical-align:top;"> NUMBER(4) </td> 
 <td style="vertical-align:top;"> 雇员对应的领导编号，领导也是雇员 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 5 </td> 
 <td style="vertical-align:top;"> HIREDATE </td> 
 <td style="vertical-align:top;"> DATE </td> 
 <td style="vertical-align:top;"> 雇员的雇佣日期 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 6 </td> 
 <td style="vertical-align:top;"> SAL </td> 
 <td style="vertical-align:top;"> NUMBER(7,2) </td> 
 <td style="vertical-align:top;"> 基本工资 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 7 </td> 
 <td style="vertical-align:top;"> COMM </td> 
 <td style="vertical-align:top;"> NUMBER(7,2) </td> 
 <td style="vertical-align:top;"> 奖金 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 8 </td> 
 <td style="vertical-align:top;"> DEPTNO </td> 
 <td style="vertical-align:top;"> NUMBER(2) </td> 
 <td style="vertical-align:top;"> 雇员所在的部门编号 </td> 
 </tr> 
 </tbody> 
</table>

** 1.3:工资等级表salgrade:**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> &nbsp; </td> 
 <td style="vertical-align:top;"> 名称 </td> 
 <td style="vertical-align:top;"> 类型 </td> 
 <td style="vertical-align:top;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 1 </td> 
 <td style="vertical-align:top;"> GRADE </td> 
 <td style="vertical-align:top;"> NUMBER </td> 
 <td style="vertical-align:top;"> 工资等级 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 2 </td> 
 <td style="vertical-align:top;"> LOSAL </td> 
 <td style="vertical-align:top;"> NUMBER </td> 
 <td style="vertical-align:top;"> 此等级的最低工资 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 3 </td> 
 <td style="vertical-align:top;"> HISAL </td> 
 <td style="vertical-align:top;"> NUMBER </td> 
 <td style="vertical-align:top;"> 此等级的最高工资 </td> 
 </tr> 
 </tbody> 
</table>

**1.4：工资表bonus：**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> &nbsp; </td> 
 <td style="vertical-align:top;"> 名称 </td> 
 <td style="vertical-align:top;"> 类型 </td> 
 <td style="vertical-align:top;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 1 </td> 
 <td style="vertical-align:top;"> ENAME </td> 
 <td style="vertical-align:top;"> VARCHAR2(10) </td> 
 <td style="vertical-align:top;"> 雇员姓名 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 2 </td> 
 <td style="vertical-align:top;"> JOB </td> 
 <td style="vertical-align:top;"> VARCHAR2(9) </td> 
 <td style="vertical-align:top;"> 雇员职位 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 3 </td> 
 <td style="vertical-align:top;"> SAL&nbsp; </td> 
 <td style="vertical-align:top;"> NUMBER </td> 
 <td style="vertical-align:top;"> 雇员的工资 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 4 </td> 
 <td style="vertical-align:top;"> COMM </td> 
 <td style="vertical-align:top;"> NUMBER </td> 
 <td style="vertical-align:top;"> 雇员的奖金 </td> 
 </tr> 
 </tbody> 
</table>

### 二：关于scott用户的查询如下： ###

--------------------

(1):  查询部门编号是20的员工信息

select * from emp where deptno=20;

(2)：查询工作为CLERK的员工的员工号，员工名和部门号

select empno,ename,deptno from emp where job='CLERK';

(3)：查询奖金COMM高于工资SAL的员工信息。

select * from emp where comm>sal;

(4)：在询奖金高于工资20%的员工信息。

select * from emp where comm>sal*0.2;

(5)：查询部门编号是10并且工作为MANAGER的员工和部门编号是20并且工作为CLERK的员工的信息。

select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

(6)：查询工作不是MANAGER和CLERK,并且工资大于或等于2000的员工信息。

select * from emp where job not in('MANAGER','CLERK') and sal>=2000;

(7)：查询有奖金的员工信息。

select * from emp where comm is not null;

(8)：查询所有员工的人数和他们的平均工资。

select count(empno),avg(sal) from emp;

(9)：查询没有奖金或奖金低于100的员工信息。

select * from emp where comm is null or comm<100;

(10)：查询最近两年人职的员工信息。

select * from emp where (sysdate-hiredate)/365<=2;

(11)：查询工龄大于或等于10年的员工信息。

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

(12)：查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

select initcap(lower(ename)) from emp;

(13)：查询员工名正好为6个字母的员工信息。

select * from emp where length(ename)=6;

(14)：查询员工名字中不包含字母s的员工。

select * from emp where ename not like '%S%';

(15)：查询员工姓名的第二个字母为M的员工信息。

select * from  emp where ename like '_M%';

(16)： 查询所有员工姓名的前三个字符。

select substr(ename,1,3) from emp;

(17)：查询所有员工的姓名，如果包含字母B，则用S替换。

select replace(ename,'B','S') from  emp;

(18)：查询员工的姓名和人职日期，并按人职日期从先到后进行排序。

select ename,hiredate from  emp order by hiredate;

(19)：显示所有员工的姓名、工作、工资，按工作降序排序，若工作相同则按工资升序排序。

select ename,job,sal from emp order by job desc,sal asc;

(20)：显示所有员工的姓名、人职的年份和月份，按人职日期所在的月份排序，若月份相同则按人职的年份排序。

select ename,extract(year from hiredate) as 年份,extract(month from hiredate) as 月份 from emp order by 月份,年份;

(21)：查询每个部门中的员工数量、平均工资和平均工作年限。

select deptno,count(*),avg(sal),avg((sysdate-hiredate)/365) from emp group by deptno;

(22)：查询各个部门的人数及平均工资。

select count(*),avg(sal) from emp group by deptno;

(23)：查询各种工作的最低工资，并输出最低工资低于3000的工作名称。

select min(sal),job from emp group by job having min(sal)<3000;

(24)：查询各个部门中不同工种的最高工资。

select deptno,job,max(sal) from emp group by deptno,job;

(25)：统计各个工种的，员工人数与平均工资。

select count(*),avg(sal) from emp group by job;

(26)：查询所有工种为CLERK的员工的姓名及其部门名称。

select ename,dname from emp t1 inner join dept t2 on t1.deptno=t2.deptno where job='CLERK';

(27)：查询所有部门及其员工信息，包括那些没有员工的部门。

select * from emp t1 right join dept t2 on t1.deptno=t2.deptno;

(28)：查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

select * from emp t1 left join dept t2 on t1.deptno=t2.deptno;

(29)：查询在SALES部门工作的员工的姓名信息。

select * from emp where deptno=(select deptno from dept where dname='SALES');

(30)：查询所有员工的姓名及其直接上级的姓名。

select t1.ename as 员工姓名,t2.ename 经理姓名 from emp t1,emp t2 where t1.mgr=t2.empno;

(31)：查询入职日期早于其上级领导的所有员工的信息。

select t1.ename as 员工姓名,t2.ename 经理姓名 from emp t1,emp t2 where t1.mgr=t2.empno and t1.hiredate<t2.hiredate;

(32)：查询从事同一种工作但不属于同一部门的员工信息。

select t1.ename,t1.job,t1.deptno,t2.ename,t2.job,t2.deptno from emp t1 cross join emp t2 where t1.job=t2.job and t1.deptno <>t2.deptno;

(33)：查询10号部门员工及其领导的信息。

select t1.ename as 员工姓名,t2.ename 经理姓名 from emp t1,emp t2 where t1.mgr=t2.empno and t1.deptno=10;

(34)：使用UNION将工资大于2500的雇员信息与工作为ANALYST的雇员信息合并。

select * from emp where sal>2500 union select * from emp where job='ANALYST';

(35)：通过INTERSECT集合运算，查询工资大于2500，并且工作为ANALYST的雇员信息。

select * from emp where sal>2500 intersect select * from emp where job='ANALYST';

(36)：使用MINUS集合查询工资大于2500，但工作不是ANALYST的雇员信息。

select * from emp where sal>2500 minus select * from emp where job='ANALYST';

(37)：查询工资高于公司平均工资的所有员工信息。

select * from emp where sal>(select avg(sal) from emp);

(38)：查询与SMITH员工从事相同工作的所有员工信息。

select * from emp where job=(select job from emp where ename='SMITH');

(39)：查询工资比SMITH员工工资高的所有员工信息。

select * from emp where sal>(select sal from emp where ename='SMITH');

(40)：查询比所有在30号部门中工作的员工的工资都高的员工姓名和工资。

select ename,sal from emp where sal>all(select sal from emp where deptno=30);

(41)：查询部门人数大于5的部门的员工信息。

select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5);

(42)：查询所有员工工资都大于2000的部门的信息。

select * from dept where deptno in(select deptno from emp group by deptno having min(sal)>2000);

(43)： 查询人数最多的部门信息。

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  emp group by deptno)));

(44)：查询至少有一个员工的部门信息。

select * from dept where deptno in(select deptno from emp group by deptno having count(*)>=1);

(45)：查询工资高于本部门平均工资的员工信息。

select * from emp e where sal>(select avg(sal) from emp group by deptno having e.deptno=deptno);

(46)：查询工资高于本部门平均工资的员工信息及其部门的平均工资。

select * from((select * from emp e where sal>(select avg(sal) from emp 
    group by deptno having e.deptno=deptno)) t1 
    inner join (select avg(sal),deptno from emp group by deptno) t2 on t1.deptno=t2.deptno);

(47)：查询每个员工的领导所在部门的信息。

select * from dept where deptno in(select distinct deptno from emp where empno in(select distinct mgr from emp));

(48)：查询平均工资低于2000的部门及其员工信息。

select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.deptno in(select deptno from scott.emp group by deptno having avg(sal)<2000);

(49)：查找12月入职的员工名

select ename,extract(month from hiredate) 入职月份  from emp  where extract(month from hiredate)=12;

(50)：查找工资前三的三名员工信息

select * from (select * from emp order by sal desc) where rownum<=3;