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

Source: Internet
Author: User
Tags dname


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.