SQL Exercise: Sort employees by designation, joining after 1991
[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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.