Day2 Assignment

Exercise : •

List employees having experience greater than 325 months.

•

Declare certain percentage of bonus based on grades(salgrade table).

•

Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”

•

Print list of employees displaying “Good salary” if sal > 3000, “Average salary” if sal=3000 and “Poor salary” if sal < 3000.

•

Write a query which returns the DAY of the week for any date entered in the format dd.mm.yy

•

Write a query to calculate the length of any employee has been with the company. Display in number of years and months.

7. Employees hired on or before 15th are paid on last Friday of same month. Those hired after 15th are paid on last Friday of next month. Display first pay date of all the employees. 8. List employees hired on Monday. 9. Calculate how many days old you are. 10. Write a query that produces the following for each employee: <employee name> earns <sal> monthly but wants <3 times salary>. Label the column Dream Salaries. 11. Create a query that displays the employees names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.

Exercise : 2. List total salary of each department. 3. List average salary of each job type excluding managers 4. List number of employees in each department. 5. List number of managers. 6. List departments where number of employees are more than 4 7. Display difference between highest and least salary. 8. Display department with average salary greater than 2000. Order on average salary. 9. List least and highest salary in each job type. 10. List how many employees are their under each manager. 11. Write a query to display the number of people with the same job.