Please use following table information to answer the questions? SQL> DESC DEPARTMENTS Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)     SQL> DESC EMPLOYEES Name                   Null?          Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6)

Please use following table information to answer the questions?
SQL> DESC DEPARTMENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)

 

SQL> DESC EMPLOYEES
Name                   Null?          Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

Exercises:
1. Display the last name and current job title of each employee, along with the department name they are
currently working in. Label the columns "LAST NAME", "JOB TITLE", and "DEPARTMENT".
2. Display the average salary for each department, along with the department name and the number of
employees in that department. Label the columns "DEPARTMENT", "AVERAGE SALARY", and
"NUMBER OF EMPLOYEES".
3. Display the last name, hire date, and current salary for all employees who have received a promotion
since their hire date. Label the columns "LAST NAME", "HIRE DATE", "CURRENT SALARY", and
"PROMOTION DATE".
4. Display the first name, last name, and salary of all employees who have a salary that is higher than the
average salary for their department. Label the column "EMPLOYEE", "SALARY", and
"DEPARTMENT".
5. Display the last name, hire date, and salary for all employees who have been with the company for
more than 5 years. Label the columns "LAST NAME", "HIRE DATE", and "SALARY".
6. Display the concatenation of employees’ last name and salary (separated by comma) under the column
heading "EMPLOYEES AND THEIR SALARIES ".
7. Display the last name, hiredate, salary and salary increased by 10% for each employee. Label the new
column NEW SALARY.
8. Display the full name, salary and add a column that subtracts the old salary from the new salary. Label
the column INCREASE. The new salary is increased by 10%.
9. Display the first name and last name of employees whose first name contain ‘N’ in the last second
character.
10. 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.
11. Display the first name and salary of all employees in the following format:
<employee_name> earns $<employee_salary> but wants $<3* employee_salary>
Label the column “Dream Salaries”