Coding_Ninjas_DBMS/groupingdata/sqlquery15.sql at main · thatsabhishek/Coding_Ninjas_DBMS · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Information about the table:
Table Employee_data:

+---------+----------+----------+-------------------+---------+------------+--------+----------+
| EmpCode | EmpFName | EmpLName | Job               | Manager | HireDate   | Salary | DeptCode |
+---------+----------+----------+-------------------+---------+------------+--------+----------+
|    9369 | TONY     | STARK    | SOFTWARE ENGINEER | 7902    | 1980-12-17 |   2800 |       20 |
|    9499 | TIM      | ADOLF    | SALESMAN          | 7698    | 1981-02-20 |   1600 |       30 |
|    9566 | KIM      | JARVIS   | MANAGER           | 7839    | 1981-04-02 |   3570 |       20 |
|    9654 | SAM      | MILES    | SALESMAN          | 7698    | 1981-09-28 |   1250 |       30 |
|    9782 | KEVIN    | HILL     | MANAGER           | 7839    | 1981-06-09 |   2940 |       10 |
|    9788 | CONNIE   | SMITH    | ANALYST           | 7566    | 1982-12-09 |   3000 |       20 |
|    9839 | ALFRED   | KINSLEY  | PRESIDENT         | 7566    | 1981-11-17 |   5000 |       10 |
|    9844 | PAUL     | TIMOTHY  | SALESMAN          | 7698    | 1981-09-08 |   1500 |       30 |
|    9876 | JOHN     | ASGHAR   | SOFTWARE ENGINEER | 7788    | 1983-01-12 |   3100 |       20 |
|    9900 | ROSE     | SUMMERS  | TECHNICAL LEAD    | 7698    | 1981-12-03 |   2950 |       20 |
|    9902 | ANDREW   | FAULKNER | ANALYST           | 7566    | 1981-12-03 |   3000 |       10 |
|    9934 | KAREN    | MATTHEWS | SOFTWARE ENGINEER | 7782    | 1982-01-23 |   3300 |       20 |
|    9591 | WENDY    | SHAWN    | SALESMAN          | 7698    | 1981-02-22 |    500 |       30 |
|    9698 | BELLA    | SWAN     | MANAGER           | 7839    | 1981-05-01 |   3420 |       30 |
|    9777 | MADII    | HIMBURY  | ANALYST           | 7839    | 1981-05-01 |   2000 |     NULL |
|    9860 | ATHENA   | WILSON   | ANALYST           | 7839    | 1992-06-21 |   7000 |       50 |
|    9861 | JENNIFER | HUETTE   | ANALYST           | 7839    | 1996-07-01 |   5000 |       50 |
+---------+----------+----------+-------------------+---------+------------+--------+----------+

Problem Statement
List down the minimum salaries offered for each job role in each department, also list them in descending order based on the max salaries being offered for that role.
Note: The result set should have the job name, its department code and its corresponding minimum salary.

Solution:
SELECT job, deptcode, MIN(salary) FROM employee_data GROUP BY job, deptcode ORDER BY MAX(salary) DESC;

Output:
+-------------------+----------+-------------+
| job               | deptcode | MIN(salary) |
+-------------------+----------+-------------+
| ANALYST           |       50 |        5000 |
| PRESIDENT         |       10 |        5000 |
| MANAGER           |       20 |        3570 |
| MANAGER           |       30 |        3420 |
| SOFTWARE ENGINEER |       20 |        2800 |
| ANALYST           |       20 |        3000 |
| ANALYST           |       10 |        3000 |
| TECHNICAL LEAD    |       20 |        2950 |
| MANAGER           |       10 |        2940 |
| ANALYST           |     NULL |        2000 |
| SALESMAN          |       30 |         500 |
+-------------------+----------+-------------+