1. For each employee, display the employee’s last name, and calculate the number of months
between today and the date the employee was hired. Label the column MONTHS_WORKED. Order
your results by the number of months employed. Round the number of months up to the closest
whole number.
SELECT LAST_NAME,ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS "MONTHS_WORKED" FROM
EMPLOYEES ORDER BY MONTHS_WORKED;
2. Write a query that produces the following for each employee:
<employee last name> earns <salary> monthly but wants <3 times
salary>. Label the column Dream Salaries.
SELECT LAST_NAME,SALARY,(SALARY*3) AS "DREAM SALARY" FROM EMPLOYEES;
3. Create a query to display the last name and salary for all employees. Format the salary to be 15
characters long, left-padded with $. Label the column SALARY.
SELECT LAST_NAME,LPAD(SALARY,15,'$') AS "SALARY" FROM EMPLOYEES;
4. Display each employee’s last name, hire date, and salary review date, which is the first Monday
after six months of service. Label the column REVIEW. Format the dates to appear in the format
similar to “Monday, the Thirty-First of July, 2000.”
WITH E1 AS (SELECT LAST_NAME,HIRE_DATE,ADD_MONTHS(HIRE_DATE,6) AS "REVIEW_DATE" FROM
EMPLOYEES)
SELECT LAST_NAME,HIRE_DATE,TO_CHAR((NEXT_DAY(REVIEW_DATE,'MONDAY')),'DAY') AS
"DAY",NEXT_DAY(REVIEW_DATE,'MONDAY') AS "SALARY REVIEWED DATE" FROM E1 ;
5. Display the last name, hire date, and day of the week on which the employee started. Label
the column DAY. Order the results by the day of the week starting with Monday.
SELECT LAST_NAME,HIRE_DATE,TO_CHAR(HIRE_DATE,'DAY') AS "DAY" FROM EMPLOYEES ORDER BY
DAY ASC;
6. Create a query that displays the employees’ last names and commission amounts. If an
employee does not earn commission, put “No Commission.” Label the column COMM.
