Shaq Somersby Shaq Somersby - 4 months ago 11
SQL Question

Query- SQL Developer

I have a question that says Write a SELECT statement to get a list of employees hired in the period from July 1998 to December 1999 . The list must contain the employee id the last and first name and the monthly salary – given that the table contains the yearly salary and that employees get 12 salaries per year.

I wrote this,but does not work:

SELECT FIRST_NAME, LAST_NAME, EMPLOYEE_ID, SALARY/12 as MONTHLY_SALARY
FROM HR.EMPLOYEES
WHERE HIRE_DATE BETWEEN JULY-1998 AND DECEMBER-1999 ;

Answer

Could be you have a wrong date format (for mysql)

SELECT FIRST_NAME, LAST_NAME, EMPLOYEE_ID, SALARY/12 as MONTHLY_SALARY
FROM HR.EMPLOYEES
WHERE HIRE_DATE BETWEEN STR_TO_DATE('07-01-1998, '%m-%d-%Y)  
       AND STR_TO_DATE('12-31-1999, '%m-%d-%Y) D ;

For sqlserver

SELECT FIRST_NAME, LAST_NAME, EMPLOYEE_ID, SALARY/12 as MONTHLY_SALARY
FROM HR.EMPLOYEES
WHERE HIRE_DATE BETWEEN Cast('07-01-1998'as datetime)  
       AND Cast('12-31-1999' as datetime) ;

And for ORACLE

SELECT FIRST_NAME, LAST_NAME, EMPLOYEE_ID, SALARY/12 as MONTHLY_SALARY
FROM HR.EMPLOYEES
WHERE HIRE_DATE BETWEEN TO_DATE('07-01-1998' , 'MM-DD-YYYY')  
       AND TO_DATE('12-31-1999' , 'MM-DD-YYYY') ;