﻿ SQL: Sort employees by designation, joining after 1991

# 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

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