SQL Exercise: Salary ranges for jobs with a minimum and maximum

Last update on December 20 2024 12:04:27 (UTC/GMT +8 hours)

35. From the following table, write a SQL query to find those job titles where maximum salary falls between 12000 and 18000 (Begin and end values are included.). Return job_title, max_salary-min_salary.

Sample table : jobs

+------------+---------------------------------+------------+------------+
| JOB_ID     | JOB_TITLE                       | MIN_SALARY | MAX_SALARY |
+------------+---------------------------------+------------+------------+
| AD_PRES    | President                       |      20080 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| SA_MAN     | Sales Manager                   |      10000 |      20080 |
| SA_REP     | Sales Representative            |       6000 |      12008 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
| ST_CLERK   | Stock Clerk                     |       2008 |       5000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
+------------+---------------------------------+------------+------------+

Sample Solution:

-- Selecting 'job_title' and calculating the salary differences as 'max_salary - min_salary'
SELECT job_title, max_salary - min_salary AS salary_differences 
-- Specifying the table to retrieve data from ('jobs')
FROM jobs 
-- Filtering the results to include only those with 'max_salary' between 12000 and 18000
WHERE max_salary BETWEEN 12000 AND 18000;

Sample Output:

      job_title       | salary_differences
----------------------+--------------------
 Finance Manager      |               7800
 Accounting Manager   |               7800
 Sales Representative |               6000
 Purchasing Manager   |               7000
 Marketing Manager    |               6000
(5 rows)

Code Explanation:

The said query in SQL which selects the "job_title" and a calculated column "salary_differences", which is the difference between the "max_salary" and "min_salary". The query retrieves data from the 'jobs' table and only includes rows where the value in the "max_salary" column is between 12000 and 18000. There will be a result table with the "job_title" and the calculated salary differences for each job title, where the maximum salary is between 12000 and 18000.

Relational Algebra Expression: