SQL Exercise: Sort employees by designation, joining after 1991

SQL employee Database: Exercise-81 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

81. From the following table, write a SQL query to list the employees in ascending order of designations of those joined after the second half of 1991.

Pictorial Presentation:

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE hire_date>('1991-6-30')
  AND date_part('year',hire_date)=1991
ORDER BY job_name ASC;

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(5 rows)

Explanation:

The said query in SQL that selects all columns (*) of employee from the employees table where the hire_date is after June 30th, 1991, and the year of the hire_date is 1991.

The date_part() function extracts the year component of the hire_date column, and the resulting value is compared to the integer 1991.

The WHERE clause filters the results to include only those employees who were hired after June 30th, 1991 and includes who were hired in 1991. The ORDER BY job_name ASC clause sorts the results in ascending order by the job_name column.

Practice Online

Sample Database: employee

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: List employees in ascending order on their experiences.
Next SQL Exercise: Employees under a given department ORDER BY dep_id ASC.