Title:
Check out the name of each employee, salary, department name, salary at the company level and the name of the leader, the salary of the leader, and the corresponding rank of the leader
This is the EMP below the default Scott user of Oracle. A study questions between the Dept and the Salgrade table.
Employee table (EMP)
Records the basic information of each employee
Description of NO field type
1 empno Number (4) Employee ID
2 ename VARCHAR2 (10) Employee name
3 Job Varchar2 (9) Job title
4 Mgr Number (4) employee's lead (manager) No.
5 Hierdate Date Entry dates
6 sal Number (7,2) Salary/salary
7 Comm Number (7,2) bonus
8 Deptno Number (2) ID of the employee's department
Department Table (EMP)
Record the basic information of each department
Description of NO field type
1 Deptno Number (2) Department ID (unique)
2 dname VARCHAR2 (14) Department name
3 Loc Varchar2 (13) address
Salary scale (Salgrade)
A company's wage hierarchy, used to indicate the lower and upper limits of the same level of wages
Description of NO field type
1 Grade number Rank name
2 losal number minimum wage for this level
3 Hisal Number maximum wage for this level
Analysis: First understand the contents of the salary scale table (Salgrade)
SELECT * from Salgrade;
Check out each employee's name, salary, department name, salary at the company level (Salgrade)
SELECT distinct E.ename,e.sal,d.dname,g.grade e_grade from emp e,dept d,salgrade g,emp swhere (E.deptno=d.deptno) and (E.S Al between G.losal and G.hisal);
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
On this basis add find boss name
SELECT e.ename,e.sal,d.dname,g.grade,s.ename from EMP e,dept d,salgrade g,emp s WHERE (E.deptno=d.deptno) and (E.sal BETWE EN Losal and Hisal) and (s.empno=e.mgr);
Finally determine the employee's manager's name, the manager's salary, and the corresponding level of the manager's salary.
SELECT e.ename,e.sal,d.dname,g.grade e_grade,s.ename mgr_name,g2.grade m_gradefrom emp e,dept d,salgrade g,emp S, Salgrade G2where (E.deptno=d.deptno) and (E.sal between G.losal and G.hisal) and (S.empno=e.mgr) and (S.sal BETWEEN G2.los Al and G2.hisal);
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
From this example, it can be seen that for complex multi-table queries, step-by-step implementation. Don't be impatient.
Further thinking:
Suppose you want to show the result of the above as the level of salary as a style, how should it be implemented
1: Fifth-level wages
2: fourth-level wages
3: third-rate wages
4: Second-rate wages
5: first-rate wages
This can only be accomplished with the Decode () function.
SELECT E.ename, E.sal, D.dname,decode (g.grade,5, ' first rate ', 4, ' second rate ', 3, ' third wages ', 2, ' fourth salary ', 1, ' fifth wages ') E_grade, S.ename Mgr_name, S.sal mgr_sal,decode (g2.grade,5, ' first rate ', 4, ' second rate ', 3, ' third wages ', 2, ' fourth wages ', 1, ' fifth wages ') M_gradefrom emp e,dept D, Salgrade g,emp s,salgrade G2where (E.deptno=d.deptno) and (E.sal between G.losal and G.hisal) and (S.empno=e.mgr) and (s.sa L between G2.losal and G2.hisal)
Check out the name of each employee, salary, department name, salary at the company level and the name of the leader, the salary of the leader, and the corresponding rank of the leader