Lecture
SQL We have two tables.
Table: department
Fields:
id Number (pk)
name Varchar (100)
Table: employee
Fields:
id Number (pk)
department_id Number
chief_id Number
name Varchar (100)
salary Number
- Display a list of employees who receive a salary greater than the direct supervisor's salary
Note: if employer do not have then chief = 0;
a) SELECT e.name, e.salary AS salary_em, ch.salary AS salary_ch, e.chief_id, ch.id
FROM employee e
LEFT JOIN employee ch ON e.chief_id = ch.id && e.chief_id> 0
WHERE e.salary> ch.salary
Display a list of employees who receive the maximum salary in his department
b) select em.id, em.salary, em.name
from employee em
inner join (
select department_id, MAX (salary) rev
from employee
group by department_id
) ss on em.department_id = ss.department_id and em.salary = ss.rev
c) A designated supervisor working in the same department.
SELECT e.name, e.id, e.chief_id, ch.department_id, e.department_id
FROM employee e
LEFT JOIN employee ch ON e.chief_id = ch.id && e.chief_id> 0
WHERE ch.department_id! = E.department_id
d) Display salary of employees
If you need to show all departments with SQL:
SELECT SUM (salary), e.department_id FROM employee e
LEFT JOIN department d ON d.id = e.department_id
GROUP BY e.department_id
If you want to see the salary of employees, then SQL:
SELECT SUM (e.salary) AS ssalary, e.department_id, d.name FROM employee e
LEFT JOIN department d ON d.id = e.department_id
GROUP BY e.department_id
ORDER BY ssalary DESC
LIMIT 1
Comments
To leave a comment
Databases, knowledge and data warehousing. Big data, DBMS and SQL and noSQL
Terms: Databases, knowledge and data warehousing. Big data, DBMS and SQL and noSQL