You get a bonus - 1 coin for daily activity. Now you have 1 coin

questions and answers for the MYSQL interview in English

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
If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

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