w3resource

SQL Exercise: Employees who was hired during given dates

SQL SORTING and FILTERING on HR Database: Exercise-12 with Solution

12. From the following table, write a SQL query to find those employees who were hired between November 5th, 2007 and July 5th, 2009. Return full name (first and last), job id and hire date.

Sample table : employees


Sample Solution:

SELECT first_name ||' '||last_name AS Full_Name, 
  job_id, hire_date 
   FROM employees 
    WHERE hire_date 
     BETWEEN '2007-11-05' AND '2009-07-05';

Sample Output:

    full_name     |   job_id   | hire_date
------------------+------------+------------
 Luis Popp        | FI_ACCOUNT | 2007-12-07
 Kevin Mourgos    | ST_MAN     | 2007-11-16
 Steven Markle    | ST_CLERK   | 2008-03-08
 Ki Gee           | ST_CLERK   | 2007-12-12
 Hazel Philtanker | ST_CLERK   | 2008-02-06
 Eleni Zlotkey    | SA_MAN     | 2008-01-29
 Oliver Tuvault   | SA_REP     | 2007-11-23
 Mattea Marvins   | SA_REP     | 2008-01-24
 David Lee        | SA_REP     | 2008-02-23
 Sundar Ande      | SA_REP     | 2008-03-24
 Amit Banda       | SA_REP     | 2008-04-21
 Sundita Kumar    | SA_REP     | 2008-04-21
 Charles Johnson  | SA_REP     | 2008-01-04
 Girard Geoni     | SH_CLERK   | 2008-02-03
 Randall Perkins  | SH_CLERK   | 2007-12-19
 Douglas Grant    | SH_CLERK   | 2008-01-13
(16 rows)

Code Explanation:

The said query in SQL that retrieves the first name, last name concatenated as "Full_Name", job_id, and hire_date columns from the 'employees' table where the hire_date is between November 5th, 2007 and July 5th, 2009.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the full name, job id and date of hire for those employees who was hired during November 5th, 2007 and July 5th, 2009 - Duration

Rows:

Query visualization of Display the full name, job id and date of hire for those employees who was hired during November 5th, 2007 and July 5th, 2009 - Rows

Cost:

Query visualization of Display the full name, job id and date of hire for those employees who was hired during November 5th, 2007 and July 5th, 2009 - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees whose salary is out of a given range.
Next SQL Exercise: Find employees who works either in department 70 or 90.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





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