HR Schema Question and Answer in Oracle Database 11g part 2

Hi Friends Today i am sharing  Oracle 11g Database Some Question and Answer.


16   Display the first name and join date of the employees who joined between 2002 and 2005.



SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') BETWEEN 2002 AND 2005 ORDER BY HIRE_DATE.


17  Display job title and average salary of employees.

SELECT JOB_TITLE, AVG(SALARY) FROM EMPLOYEES

NATURAL JOIN JOBS GROUP BY JOB_TITLE.


18  Display job title, employee name, and the difference between maximum salary for the job and salary of the employee.

SELECT JOB_TITLE, FIRST_NAME, MAX_SALARY SALARY

DIFFERENCE FROM EMPLOYEES NATURAL JOIN JOBS.

19   Display details of jobs that were done by any employee who is currently drawing more than 13000 of salary.


SELECT JH.*
FROM JOB_HISTORY JH JOIN EMPLOYEES E ON (JH.EMPLOYEE_ID = E.EMPLOYEE_ID)

WHERE SALARY > 13000.


20   Display country name, city, and department name.


SELECT COUNTRY_NAME, CITY, DEPARTMENT_NAME
FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID)

JOIN DEPARTMENTS USING (LOCATION_ID)

21  Display department name and manager first name.

SELECT DEPARTMENT_NAME, FIRST_NAME FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)

22   Insert a row into departments table with manager ID 130 and location ID in any location ID for city Delhi.

INSERT INTO DEPARTMENTS (160,'SPORTS',130,1300)


23   Change job ID of employee 110 to IT_PROG if the employee belongs to department 10 and the existing job ID does not start with IT.

UPDATE EMPLOYEES SET JOB_ID= 'IT_PROG'

WHERE EMPLOYEE_ID=110 AND DEPARTMENT_ID=10 AND NOT JOB_ID LIKE 'IT%'