Part 1. Query Writing

[4 Marks] Write SQL queries to accomplish the following tasks for HR database:

Show the names of all the employees (first name must contain the letter S), together with their job titles, department names, salary, street address, postal code and country names. Sort the result in the decreasing order according to salary.

Show all the department, names and salary in which the avg salary is larger than 5000.

Show all the departments in which the maximum salary is higher than the average salary of the company.

Show the first and last name, hire date and email of employees and the name of their department.

Show the first and last name of the highest earner employee in each Department.

Show maximum information about all the employees using all the tables.

Show the first name, last name, department_id and salary of highest earners.

Show the first name and salary of Employees and their managers first names and their salary, provided that, both should not be hired in the same year.

Part 2. Report Building

Create a Report that contains answers for the following questions, using report builder:

1. A report (generate tables) that displays the result of the following queries (given in part 1):

[1 Mark] Query 4

[1 Mark] Query 5

[1 Mark] Query 6

[1 Mark] Query 7

2. [1 Mark] A pie chart showing the total amount of salary paid in each department.

3. [1 Mark] A bar chart showing the number of employees managed by each manager.