SQL Exercise: Details of jobs in descending order by title

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

27. From the following table, write a SQL query to find all jobs. Sort the result-set in descending order by job title. Return all fields.

+------------+---------------------------------+------------+------------+
| 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 all columns from the 'jobs' table
SELECT * 
-- Specifying the table to retrieve data from ('jobs')
FROM jobs 
-- Ordering the results in descending order based on the 'job_title' column
ORDER BY job_title DESC;

Sample Output:

job_id    |job_title                      |min_salary|max_salary|
----------|-------------------------------|----------|----------|
ST_MAN    |Stock Manager                  |      5500|      8500|
ST_CLERK  |Stock Clerk                    |      2000|      5000|
SH_CLERK  |Shipping Clerk                 |      2500|      5500|
SA_REP    |Sales Representative           |      6000|     12000|
SA_MAN    |Sales Manager                  |     10000|     20000|
PU_MAN    |Purchasing Manager             |      8000|     15000|
PU_CLERK  |Purchasing Clerk               |      2500|      5500|
PR_REP    |Public Relations Representative|      4500|     10500|
AC_ACCOUNT|Public Accountant              |      4200|      9000|
IT_PROG   |Programmer                     |      4000|     10000|
AD_PRES   |President                      |     20000|     40000|
MK_REP    |Marketing Representative       |      4000|      9000|
MK_MAN    |Marketing Manager              |      9000|     15000|
HR_REP    |Human Resources Representative |      4000|      9000|
FI_MGR    |Finance Manager                |      8200|     16000|
AD_VP     |Administration Vice President  |     15000|     30000|
AD_ASST   |Administration Assistant       |      3000|      6000|
AC_MGR    |Accounting Manager             |      8200|     16000|
FI_ACCOUNT|Accountant                     |      4200|      9000|

Code Explanation:

The said query in SQL that retrieves all columns of data from the 'jobs' table and orders the rows by the values in the "job_title" column in descending order.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find employees managed by the manager.
Next SQL Exercise: Employees who are Sales Representatives or Sales Men.