1 Log in to your Oracle ApEx account 2 Create a SQL query in

1. Log in to your Oracle ApEx account.

2. Create a SQL query in the SQL command window showing the empno, ename, job, sal, hiredate, and the number of years that the employee has worked for the company from the emp table. Use the months_between() function along with the SYSDATE to show how many years the employees have been working for the company. Limit your results to employees who have been working for the company more than 35 years.

Copy and paste your SQL query along with a screenshot of your result set into your assignment.

3. Create a SQL query in the SQL command window showing the empno, ename, job, sal, hiredate from the emp table. Use the hiredate column to get all of the employees hired in 1980 and all of the employees hired in the first four months of 1981.

Copy and paste your SQL query along with a screenshot of your result set into your assignment.

Solution

1. select empno,ename,job,sal,hiredate,(months_between(hiredate,SYSDATE)/12) as experience from emp where to_char(SYSDATE,\'YYYY\') - to_char(hiredate,\'YYYY\') >=35;

In the we are displaying the ename,job,sal,hiredate and to know the experience we use the months_betwen function in that we are comparing the hired date of employee with the current system gives the number of months which is divided by 12 gives the number of years and we are aliasing this column as experience and then to display only the employees having more than 35 years we are calculating the difference between the employee hired year and the current year if the difference is more than or equal to 35 it displays the records of the employees.

2. select empno,ename,job,sal,hiredate from emp where hiredate between \'01-01-1980\' and \'30-04-1981\';

In this query we are selecting the empno,ename,job,sal, hiredate and then we imposed a condition that only the employees hiredate between 1st date of year 1980 to last date of first quarter in the year 1981.