Creation of experiment table

Table field description:

id:Employee number
name: Employee name
ano: Manager No

create table admin(id varchar2(4),name varchar2(10),ano varchar2(4));
insert into admin values('001','XiongDa','004');
insert into admin values('002','XiongEr','004');
insert into admin values('003','ZhangSan','003');
insert into admin values('004','ZhaoSi','004');

View table

SQL> select * from admin;

ID       NAME               ANO
------------ ------------------------------ ------------
001      XiongDa                004
002      XiongEr                004
003      ZhangSan               003
004      ZhaoSi             004

SQL> 

problem

By querying the admin table, display the number, name and name of the administrator

Experimental steps

Main idea: how to find out the name of an
The correspondence between id and ano

When we query two tables, in fact, all the rows of the two tables are cross linked

SQL> select * from admin a ,admin b;

ID       NAME               ANO      ID       NAME               ANO
------------ ------------------------------ ------------ ------------ ------------------------------ ------------
001      XiongDa                004      001          XiongDa                004
001      XiongDa                004      002          XiongEr                004
001      XiongDa                004      003          ZhangSan               003
001      XiongDa                004      004          ZhaoSi                 004
002      XiongEr                004      001          XiongDa                004
002      XiongEr                004      002          XiongEr                004
002      XiongEr                004      003          ZhangSan               003
002      XiongEr                004      004          ZhaoSi                 004
003      ZhangSan               003      001          XiongDa                004
003      ZhangSan               003      002          XiongEr                004
003      ZhangSan               003      003          ZhangSan               003
003      ZhangSan               003      004          ZhaoSi                 004
004      ZhaoSi             004      001          XiongDa                004
004      ZhaoSi             004      002          XiongEr                004
004      ZhaoSi             004      003          ZhangSan               003
004      ZhaoSi             004      004          ZhaoSi                 004

16 rows selected.

We can see the data we need through human eyes. Just write the name of the second table in the ano of the first table to get the information we want

001      XiongDa                004      004          ZhaoSi                 004
002      XiongEr                004      004          ZhaoSi                 004
003      ZhangSan               003      003          ZhangSan               003
004      ZhaoSi             004      004          ZhaoSi                 004

Through the above results to find the corresponding relationship, we find that as long as no = ID, then we can get the result

SQL> select a.id,a.name,b.name as aname from admin a ,admin b where a.ano=b.id;

ID       NAME               ANAME
------------ ------------------------------ ------------------------------
003      ZhangSan               ZhangSan
004      ZhaoSi             ZhaoSi
002      XiongEr                ZhaoSi
001      XiongDa                ZhaoSi

SQL>