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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Concatenate strings of a string field in a PostgreSQL 'group by' query:
Input:
ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave
SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;
Output:
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
Database: PostgreSQL
Ref: https://bit.ly/2XTiRjq
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook