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.
Sample table: employees
SELECT * FROM employees WHERE hire_date>('1991-6-30') AND date_part('year',hire_date)=1991 ORDER BY job_name ASC;
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)
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.
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:
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;
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
- 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
- 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