Exercise 1 Find the average salary of the male and female employees in each department.
Exercise 2 Find the lowest department number encountered in the 'dept_emp' table. Then, find the highest department number.
Exercise 3 Obtain a table containing the following three fields for all individuals whose employee number is not greater than 10040:
- employee number
- the lowest department number among the departments where the employee has worked in (Hint: use a subquery to retrieve this value from the 'dept_emp' table)
- assign '110022' as 'manager' to all individuals whose employee number is lower than or equal to 10020, and '110039' to those whose number is between 10021 and 10040 inclusive. Use a CASE statement to create the third field. If you've worked correctly, you should obtain an output containing 40 rows. Here’s the top part of the output. Does it remind you of an output you’ve obtained earlier in the course?
Exercise 4 Retrieve a list of all employees that have been hired in 2000.
Exercise 5 Retrieve a list of all employees from the ‘titles’ table who are engineers.
Exercise 6 How many contracts have been registered in the ‘salaries’ table with duration of more than one year and of value higher than or equal to $100,000? Hint: You may wish to compare the difference between the start and end date of the salaries contracts.
Exercise 7 Create a trigger that checks if the hire date of an employee is higher than the current date. If true, set the hire date to equal the current date. Format the output appropriately (YY-mm-dd).
Exercise 8 What is the lowest and highest contract salary value of the same employee?