SQL Exercise: List the employees who joined in June 1991
SQL employee Database: Exercise-43 with Solution
[An editor is available at the bottom of the page to write and execute the scripts.]
43. From the following table, write a SQL query to identify the employees who joined the company in June 1991. Return complete information about the employees.
Sample table: employees
SELECT * FROM employees WHERE hire_date BETWEEN '1991-06-01' AND '1991-06-30';
SELECT * FROM employees WHERE to_char(hire_date,'mon-yyyy')='jun-1991'
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 (1 row)
The said query in that selects all columns and rows from the 'employees' table where the "hire_date" column, after being formatted as a string with the 'mon-yyyy' format, is equal to the string 'jun-1991'.
The WHERE clause includes those employees hired in June 1991.
The "to_char" function is used to convert the hire_date column into a string with the specified format, 'mon-yyyy'. The 'mon' format code represents the abbreviated month name (e.g. Jan, Feb, Mar), while 'yyyy' represents the year in four digits.
Relational Algebra Expression:
Relational Algebra Tree:
Sample Database: employee
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: List the employees who joined in the year 91.
Next SQL Exercise: List the employees with annual salaries in a ranges.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- 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