questions and answers for the MYSQL interview in English

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