Oracle Practice Question on HR Schema

Oracle practice question on HR schema:

HR user needs to be unlocked using below DDL ALTER USER HR IDENTIFIED BY < password> ACCOUNT UNLOCK; Login using HR user and try e!o" #uestion 1. Display Display details details of jobs jobs where where the minimum minimum salary salary is greater greater than than 10000. 10000. 2. Display Display the first first name and join join date of the employees employees who who joined between between 2002 2002 and 2005. 3. Display Display first first name and join date date of the employees employees who is either either IT Program Programmer mer or ales !an. ". Display Display employe employees es who joined joined after after 1st 1st #anuary #anuary 200$. 200$. 5. Display Display detail details s of emplo employee yee with ID 150 or 1%0. 1%0. %. Display first first name& salary& salary& 'ommission p't& and hire date for employees employees with salary less than 10000. (. Display Display job Title& Title& the differen'e differen'e between between minimum minimum and ma)imum ma)imum salarie salaries s for jobs with ma) salary in the range 10000 to 20000. $. Display Display first first name& salary salary&& and round round the salary salary to thousand thousands. s. *. Display Display details details of jobs jobs in the the des'endin des'ending g order order of the title. title. 10. Display employees where where the first name or last name starts with . 11. Display employees who who joined in the month of !ay. 12. Display details of the employees where where 'ommission per'entage is null and salary salary in the range 5000 to 10000 and department is 3 0. 13. Display first name and date of first salary of the employees. employees. 1". Display first name and e)perien'e of the employees. employees. 15. Display first name of employees who who joined in 2001. 1%. Display first name and last name after 'on+erting 'on+erting the first letter of ea'h name to upper 'ase and the rest to lower 'ase.

1(. Display the first word in job title. 1$. Display the length of first name for employees where last name 'ontain 'hara'ter ,bafter 3rd position. 1*. Display first name in upper 'ase and email address in lower 'ase for employees where the first name and email address are same irrespe'ti+e of the 'ase. 20. Display employees who joined in the 'urrent year. 21. Display the number of days between system date and 1st #anuary 2011. 22. Display how many employees joined in ea'h month of the 'urrent year. 23. Display manager ID and number of employees managed by the manager. 2". Display number of employees joined after 15th of the month. 25. Display the 'ountry ID and number of 'ities we ha+e in the 'ountry. 2%. Display a+erage salary of employees in ea'h department who ha+e 'ommission per'entage. 2(. Display job ID& number of employees& sum of salary& and differen'e between highest salary and lowest salary of the employees of the job. 2$. Display job ID for jobs with a+erage salary more than 10000. 2*. Display years in whi'h more than 10 employees joined. 30. Display departments in whi'h more than fi+e employees ha+e 'ommission per'entage. 31. Display employee ID for employees who did more than one job in the past. 32. Display job ID of jobs that were done by more than 3 employees for more than 100 days. 33. Display department ID& year& and umber of employees joined. 3". Display departments where any manager is managing more than 5 employees. 35. /hange salary of employee 115 to $000 if the e)isting salary is less than %000. 3%. Insert a new employee into employees with all the reuired details. 3(. Delete department 20. 3$. /hange job ID of employee 110 to ITP4 if the employee belongs to department 10 and the e)isting job ID does not start with IT.

3*. Insert a row into departments table with manager ID 120 and lo'ation ID in any lo'ation ID for 'ity Toyo. "0. Display department name and number of employees in the department. "1. Display job title& employee ID& number of days between ending date and starting date for all jobs in department 30 from job history. "2. Display department name and manager first name. "3. Display department name& manager name& and 'ity. "". Display 'ountry name& 'ity& and department name. "5. Display job title& department name& employee last name& starting date for all jobs from 2000 to 2005. "%. Display job title and a+erage salary of employees "(. Display job title& employee name& and the differen'e between ma)imum salary for the job and salary of the employee. "$. Display last name& job title of employees who ha+e 'ommission per'entage and belongs to department 30. "*. Display details of jobs that were done by any employee who is 'urrently drawing more than 15000 of salary. 50. Display department name& manager name& and salary of the manager for all managers whose e)perien'e is more than 5 years. 51. Display employee name if the employee joined before his manager. 52. Display employee name& job title for the jobs employee did in the past where the job was done less than si) months. 53. Display employee name and 'ountry in whi'h he is woring. 5". Display department name& a+erage salary and number of employees with 'ommission within the department. 55. Display the month in whi'h more than 5 employees joined in any department lo'ated in ydney. 5%. Display details of departments in whi'h the ma)imum salary is more than 10000. 5(. Display details of departments managed by ,mith-. 5$. Display jobs into whi'h employees joined in the 'urrent year.

5*. Display employees who did not do any job in the past. %0. Display job title and a+erage salary for employees who did a job in the past. %1. Display 'ountry name& 'ity& and number of departments where department has more than 5 employees. %2. Display details of manager who manages more than 5 employees. %3. Display employee name& job title& start date& and end date of past jobs of all employees with 'ommission per'entage null. %". Display the departments into whi'h no employee joined in last two years. %5. Display the details of departments in whi'h the ma) salary is greater than 10000 for employees who did a job in the past. %%. Display details of 'urrent job for employees who wored as IT Programmers in the past. %(. Display the details of employees drawing the highest salary in the department. %$. Display the 'ity of employee whose employee ID is 105. %*. Display third highest salary of all employees

ER Diagra$