w3resource

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

SQL SORTING and FILTERING on HR Database: Exercise-35 with Solution

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


Sample Solution:

SELECT job_title, max_salary-min_salary AS salary_differences 
	FROM jobs 
		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:

Relational Algebra Expression: Display job Title, the difference of minimum and maximum salaries for those jobs which max salary between 12000 to 18000.

Relational Algebra Tree:

Relational Algebra Tree: Display job Title, the difference of minimum and maximum salaries for those jobs which max salary between 12000 to 18000.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display job Title, the difference of minimum and maximum salaries for those jobs which max salary between 12000 to 18000 - Duration

Rows:

Query visualization of Display job Title, the difference of minimum and maximum salaries for those jobs which max salary between 12000 to 18000 - Rows

Cost:

Query visualization of Display job Title, the difference of minimum and maximum salaries for those jobs which max salary between 12000 to 18000 - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Jobs which average salary is above 8000.
Next SQL Exercise: Employees whose first or last name begins with D.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook