w3resource

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.

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 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 Expression: Display the details of jobs in descending sequence on job title.

Relational Algebra Tree:

Relational Algebra Tree: From the following table, write a SQL query to find all those employees who are either Sales Representative or Salesman. Return first name, last name and hire date.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the details of jobs in descending sequence on job title - Duration

Rows:

Query visualization of Display the details of jobs in descending sequence on job title - Rows

Cost:

Query visualization of Display the details of jobs in descending sequence on job title - 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.

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.