HR Schema Question and Answer in Oracle Database 11g.

Hi Friends Today i am share Oracle 11g Database Some Qusetion and Answer.
                                   

1. Display details of jobs where the minimum salary is greater than 10000.

        SELECT * FROM JOBS WHERE MIN_SALARY > 10000

2  . 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



3. Display details of employee with ID 150 or 160.




       SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID in (150,160)


4. Display first name and join date of the employees who is either IT Programmer or Sales Man.


      SELECT FIRST_NAME, HIRE_DATE

      FROM EMPLOYEES WHERE JOB_ID IN ('IT_PROG', 'SA_MAN')


5. Display employees who joined after 1st January 2008.




SELECT * FROM EMPLOYEES where hire_date > '01jan2008'


6. Display first name, salary, commission pct, and hire date for employees with salary less than 10000.



SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE SALARY < 10000


7. Display job Title, the difference between minimum and maximum salaries for jobs with max salary in the range 10000 to 20000.


SELECT JOB_TITLE, MAX_SALARYMIN_

SALARY DIFFERENCE FROM JOBS WHERE MAX_SALARY BETWEEN 10000 AND 20000


8. Display details of jobs in the descending order of the title.



SELECT * FROM JOBS ORDER BY JOB_TITLE


9. Display employees who joined in the month of May.


SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'MON')= 'MAY'



10 Display employees where the first name or last name starts with S.



SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%' OR LAST_NAME LIKE 'S%'



11. Display details of jobs in the descending order of the title.



SELECT * FROM JOBS ORDER BY JOB_TITLE


12. Display first name and experience of the employees.


SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATEHIRE_

DATE)/365)FROM EMPLOYEES

13  Display details of the employees where commission percentage is null and salary in the range 9000 to 10000 and department is 30.


SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL AND SALARY BETWEEN 9000 AND 10000 AND DEPARTMENT_ID=30



14 Display first name and experience of the employees.

SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATEHIRE_

DATE)/365)FROM EMPLOYEES

15. Display the length of first name for employees where last name contain character ‘b’ after 3rd position.



SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE INSTR(LAST_NAME,'B') > 3