Hello, this question is for Oracle SQL. Table: Employees Employee First Name Las

Question

Hello, this question is for Oracle SQL.

Table: Employees Employee First Name Last Name Dept Hire Date Code Credit Limit Phone Manager_.id Ext 201 202 203 204 205 206 207 208 209 210 Susan im Martha E11en Hen Caro Dan Fred Paula Nancy Brown Kern Woods Owens Perkins Rose Smith Exe Sal Sh Sa Sal Act 01-Jun-1998 16-Aug-1999 02-Feb-2004 01-Jul-2003 01-Mar-2000 null 01-Dec-2004 01-Apr-2003 17-Mar-1999 16-Feb-2004 30.00 $25.00 $25.00 $15.00 $25.00 3484 (null) 8722 7591 201 6830 202 5286 202 nul1 (null1 2259 203 1752 203 3357 201 2974 203 201 Campbe11 Jacobs Hoffman Shp Mkt sal $25.00 $30.00 $15.00 $25.00 Task#4: Find how many employees have joined between the year 2002 and 2005 in the accounting department and list all the employees, their hire dates and the number of days each person will have worked for the company as of January 1, 2005

Explanation / Answer

Answer : The mentioned date format is of dd-MMM-yyyy, therefore, to perform any date operation it has to be converted to standard date YYYY-MM-DD, of which conversion code is 102 and Date Variable is "DATE". DATEDIFF function is used to get the difference in number of days between two dates mentioned in parameters.

Part (a) : The Oracle SQL query for finding the number of employees who have joined between the year 2002 and 2005 in the accounting department :

SELECT COUNT(EmployeeId) from Employees
where CONVERT(DATE, Hire_Date, 102) between '2002-01-01' and '2005-12-31'

Part (b) : The Oracle SQL query to list all the employees, their hire dates and the number of days each person will have worked for the company as of January 01, 2005:

SELECT First_Name, Last_Name, Hire_Date,
DATEDIFF(d, Hire_Date, CONVERT(DATE, '01-Jan-2005', 102)) as Difference

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.