w3resource

SQL SORTING and FILTERING on HR Database: Display the details of jobs in descending sequence on job 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


Sample Solution:

SELECT * 
	FROM jobs 
		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|

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: From the following table, write a SQL query to count the number of employees worked under each manager. Return manager ID and number of employees.
Next: 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.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

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