MySQL Join queries (employees, salaries, departments, titles)

The motivation for this article is the failure of clearing paytm written round. The concern of working in a web development framework is, everything is made easy by the framework. For example in Rails framework life of a web developer is made so easy that one doesn't have to bother about going into SQL console execute the query and then integrate it in the lifecycle of the API request. ActiveRecord ORM of Rails is just so awesome that it takes away so much of work and make our life comfortable just by learning how to use and play around with Ruby programming language you can bypass all SQL statements. This will be good only until you attend the interview. In the interview, one needs to be questioned in one or another way to get an insight into what you are. Though it is not relevant for a Ruby on rails developer this question is being asked. Nowadays any framework out there will have its own ORM to support its framework and bosts its ability.

So thinking from an interviewer perspective made me dig it into to the SQL queries and wanted to solve the questions I had got. Maybe not exactly what I have got, but at least on the same concept of employees. Thanks to this link where I got ready made script to run so that my local is ready to experiment.

table structure for this article

From the above tables structures, we will be answering the following questions.

Questions (Even Simple queries are included so all readers are benefited):

1. Employee count:

mysql> Select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.07 sec)

2. Employees with the only first_name:

mysql> select first_name from employees limit 5;
+------------+
| first_name |
+------------+
| Georgi     |
| Bezalel    |
| Parto      |
| Chirstian  |
| Kyoichi    |
+------------+
5 rows in set (0.00 sec)

3. Employee to get the first entry:

mysql> select * from employees limit 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

4. Few queries on salaries table for understanding:

######## Particular employee salary details: #########
mysql> select * from salaries where salaries.emp_no = '10001';
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.00 sec)
######## Particular employee obtaining maximum salary details: #########
mysql> select emp_no, MAX(salary) as salary, from_date from salaries where salaries.emp_no = 10001;
+--------+--------+------------+
| emp_no | salary | from_date  |
+--------+--------+------------+
|  10001 |  88958 | 1986-06-26 |
+--------+--------+------------+
1 row in set (0.00 sec)
######## Particular employee maximum salary details(reference): #########
mysql> SELECT sal1.* FROM salaries AS sal1 LEFT JOIN salaries AS sal2 ON (sal1.emp_no = sal2.emp_no AND sal1.from_date < sal2.from_date) WHERE sal2.from_date IS NULL and sal1.emp_no = 10001;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)

5. Employee and Salary table join to display the salary of the individual

mysql> select emp.emp_no, emp.first_name, emp.last_name, sal.salary, sal.from_date from employees emp inner join (select emp_no, MAX(salary) as salary, from_date from salaries group by emp_no) sal on (emp.emp_no = sal.emp_no) limit 10;
+--------+------------+-----------+--------+------------+
| emp_no | first_name | last_name | salary | from_date  |
+--------+------------+-----------+--------+------------+
|  10001 | Georgi     | Facello   |  88958 | 1986-06-26 |
|  10002 | Bezalel    | Simmel    |  72527 | 1996-08-03 |
|  10003 | Parto      | Bamford   |  43699 | 1995-12-03 |
|  10004 | Chirstian  | Koblick   |  74057 | 1986-12-01 |
|  10005 | Kyoichi    | Maliniak  |  94692 | 1989-09-12 |
|  10006 | Anneke     | Preusig   |  60098 | 1990-08-05 |
|  10007 | Tzvetan    | Zielinski |  88070 | 1989-02-10 |
|  10008 | Saniya     | Kalloufi  |  52668 | 1998-03-11 |
|  10009 | Sumant     | Peac      |  94443 | 1985-02-18 |
|  10010 | Duangkaew  | Piveteau  |  80324 | 1996-11-24 |
+--------+------------+-----------+--------+------------+
10 rows in set (1.16 sec)

6. List of all employees with the designation, joining three tables;

mysql> select emp.emp_no, emp.first_name, emp.last_name, sal.salary, titles.title from employees emp inner join (select emp_no, MAX(salary) as salary from salaries group by emp_no) sal on sal.emp_no = emp.emp_no inner join titles on titles.emp_no = emp.emp_no limit 10;
+--------+------------+-----------+--------+-----------------+
| emp_no | first_name | last_name | salary | title           |
+--------+------------+-----------+--------+-----------------+
|  10001 | Georgi     | Facello   |  88958 | Senior Engineer |
|  10002 | Bezalel    | Simmel    |  72527 | Staff           |
|  10003 | Parto      | Bamford   |  43699 | Senior Engineer |
|  10004 | Chirstian  | Koblick   |  74057 | Engineer        |
|  10004 | Chirstian  | Koblick   |  74057 | Senior Engineer |
|  10005 | Kyoichi    | Maliniak  |  94692 | Senior Staff    |
|  10005 | Kyoichi    | Maliniak  |  94692 | Staff           |
|  10006 | Anneke     | Preusig   |  60098 | Senior Engineer |
|  10007 | Tzvetan    | Zielinski |  88070 | Senior Staff    |
|  10007 | Tzvetan    | Zielinski |  88070 | Staff           |
+--------+------------+-----------+--------+-----------------+
10 rows in set (1.16 sec)

7. Salaries for each designation ordered in descending:

mysql> select new_table.title, MAX(new_table.salary) as salary from (select emp.first_name, emp.last_name, sal.salary as salary, titles.title as title from employees emp inner join (select emp_no, MAX(salary) as salary from salaries group by emp_no) sal on sal.emp_no = emp.emp_no inner join titles on titles.emp_no = emp.emp_no) new_table group by new_table.title order by salary desc;
+--------------------+--------+
| title              | salary |
+--------------------+--------+
| Senior Staff       | 158220 |
| Staff              | 158220 |
| Technique Leader   | 144434 |
| Senior Engineer    | 140784 |
| Engineer           | 140784 |
| Assistant Engineer | 133712 |
| Manager            | 108407 |
+--------------------+--------+
7 rows in set (2.73 sec)

8. Listing employee and current department they are working and their respective manager:

select emp.emp_no, CONCAT(emp.first_name, ' ', emp.last_name) AS Employee, emp.gender as gender, \
       dept.dept_name as department, dept.dept_no, \
       CONCAT(manager.first_name, ' ', manager.last_name) AS Manager\
    from current_dept_emp cdept \
        inner join employees emp \
            on emp.emp_no = cdept.emp_no \
        inner join departments dept \
            on dept.dept_no = cdept.dept_no \
        inner join (select dept_no as dn, emp_no as en from dept_manager where from_Date in (select MAX(from_date) from dept_manager group by dept_no)) dm \
            on dm.dn = cdept.dept_no
        inner join employees manager \
            on manager.emp_no = dm.en \
        limit 10
;
+--------+--------------------+--------+--------------------+---------+-----------------+
| emp_no | Employee           | gender | department         | dept_no | Manager         |
+--------+--------------------+--------+--------------------+---------+-----------------+
|  10001 | Georgi Facello     | M      | Development        | d005    | Leon DasSarma   |
|  10002 | Bezalel Simmel     | F      | Sales              | d007    | Hauke Zhang     |
|  10003 | Parto Bamford      | M      | Production         | d004    | Oscar Ghazalie  |
|  10004 | Chirstian Koblick  | M      | Production         | d004    | Oscar Ghazalie  |
|  10005 | Kyoichi Maliniak   | M      | Human Resources    | d003    | Karsten Sigstam |
|  10006 | Anneke Preusig     | F      | Development        | d005    | Leon DasSarma   |
|  10007 | Tzvetan Zielinski  | F      | Research           | d008    | Hilary Kambil   |
|  10008 | Saniya Kalloufi    | M      | Development        | d005    | Leon DasSarma   |
|  10009 | Sumant Peac        | F      | Quality Management | d006    | Dung Pesch      |
|  10010 | Duangkaew Piveteau | F      | Quality Management | d006    | Dung Pesch      |
+--------+--------------------+--------+--------------------+---------+-----------------+
10 rows in set (0.69 sec)

9. List of employees whose salary is more than a manager:

select emp.emp_no, CONCAT(emp.first_name, ' ', emp.last_name) AS Employee, emp.gender as gender, \
       dept.dept_name as department, dept.dept_no, sal.salary as Employee_Salary, \
       manager.emp_no as Manager_id, CONCAT(manager.first_name, ' ', manager.last_name) AS Manager, man_sal.salary as Manager_Salary\
    from current_dept_emp cdept \
        inner join employees emp \
            on emp.emp_no = cdept.emp_no \
        inner join departments dept \
            on dept.dept_no = cdept.dept_no \
        inner join (select dept_no as dn, emp_no as en from dept_manager where from_Date in (select MAX(from_date) from dept_manager group by dept_no)) dm \
            on dm.dn = cdept.dept_no
        inner join employees manager \
            on manager.emp_no = dm.en \
        inner join (SELECT sal1.* FROM salaries AS sal1 LEFT JOIN salaries AS sal2 ON (sal1.emp_no = sal2.emp_no AND sal1.from_date < sal2.from_date) WHERE sal2.from_date IS NULL
) sal \
            on sal.emp_no = cdept.emp_no
        inner join (SELECT sal1.* FROM salaries AS sal1 LEFT JOIN salaries AS sal2 ON (sal1.emp_no = sal2.emp_no AND sal1.from_date < sal2.from_date) WHERE sal2.from_date IS NULL
) man_sal \
            on man_sal.emp_no = manager.emp_no \
        where sal.salary > man_sal.salary \
        limit 10
;

10. Each department and its total salary consumption arranged in descending order:

select dept.dept_name as department, dept.dept_no, SUM(sal.salary) as Employee_Salary \
    from current_dept_emp cdept \
        inner join employees emp \
            on emp.emp_no = cdept.emp_no \
        inner join departments dept \
            on dept.dept_no = cdept.dept_no \
        inner join (select dept_no as dn, emp_no as en from dept_manager where from_Date in (select MAX(from_date) from dept_manager group by dept_no)) dm \
            on dm.dn = cdept.dept_no
        inner join employees manager \
            on manager.emp_no = dm.en \
        inner join (SELECT sal1.* FROM salaries AS sal1 LEFT JOIN salaries AS sal2 ON (sal1.emp_no = sal2.emp_no AND sal1.from_date < sal2.from_date) WHERE sal2.from_date IS NULL
) sal \
            on sal.emp_no = cdept.emp_no
        group by department;
;
+--------------------+---------+-----------------+
| department         | dept_no | Employee_Salary |
+--------------------+---------+-----------------+
| Development        | d005    |      5048065795 |
| Production         | d004    |      4383326395 |
| Sales              | d007    |      4072233012 |
| Marketing          | d001    |      1437988067 |
| Customer Service   | d009    |      1424236382 |
| Research           | d008    |      1268759713 |
| Finance            | d002    |      1191910167 |
| Quality Management | d006    |      1159755611 |
| Human Resources    | d003    |       994036263 |
+--------------------+---------+-----------------+
9 rows in set (21.36 sec)

11. Second largest salary:

SELECT MAX(salary) FROM salaries WHERE salary NOT IN ( SELECT Max(salary) FROM salaries);
mysql> SELECT MAX(salary) FROM salaries WHERE salary NOT IN ( SELECT Max(salary) FROM salaries);
+-------------+
| MAX(salary) |
+-------------+
|      157821 |
+-------------+
1 row in set (2.08 sec)

From understanding and trying above examples one will be able to try different queries. Writing more and more queries will get the gist on how query language is developed. It is very simple actually, practice more and you will be in control. For most of the time, the inner join is used don't worry too much about others just understand and keep it in mind. I was worried too much about the same what. Anyway, here it is the list I remembered in the written test. From the above list of queries, many different queries can be constructed.

There you go, link to download sample data is mentioned. The only thing to do is to Download, restore and jump in with your own set of queries. Please do mention if the above queries can be optimized and better way if possible. Further just for improving programming skills as well as query language skill should design an ORM. If the article is helpful please appreciate with claps. Cheers Enjoy Coding!!!