Find the name, salary, Department name, salary level in the company and name of the leadership, salary of the leadership, level corresponding to the leadership, and employee name of each employee.

Find the name, salary, Department name, salary level in the company and name of the leadership, salary of the leadership, level corresponding to the leadership, and employee name of each employee.

Example:

The employee's name, salary, Department name, salary level in the company and the name of the leader, the salary of the leader, and the level corresponding to the leader are displayed.

This is a thinking question between emp, dept, and salgrade tables under oracle's default Scott user.

Analysis: first, identify the content in the salary level table (salgrade ).

select * from salgrade;

Query the name, salary, Department name, and salary of each employee in the company's 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.sal BETWEEN g.losal AND g.hisal);

Add the name of the Supervisor

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 BETWEEN losal AND hisal) AND (s.empno=e.mgr);

Finally, determine the employee's manager name, the manager's salary, and the level corresponding to 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.losal AND g2.hisal);

From this example, we can see that for complex multi-table queries, we need to implement it step by step.

Further consideration:

How can I achieve this if I want to display the salary level according to the above result in a style such

1: pay-as-you-go

2: fourth class salary

3: Third-level salary

4: second-level salary

5: First class salary

You can only use the DECODE () function to implement this function.

SELECT e. ename, e. sal, d. dname, decode (g. grade, 5, 'first-level wage ', 4, 'second-level wage', 3, 'third-level wage ', 2, 'Fourth-level wage', 1, 'Wait 5') e_grade, s. ename mgr_name, s. sal mgr_sal, decode (g2.grade, 5, 'First wage ', 4, 'second wage', 3, 'third wage ', 2, 'Fourth wage ', 1, 'fifth-level wage ') 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.losal AND g2.hisal)