SQL Exercise: Details of jobs in descending order by title
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 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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.