We will discuss advanced SQL and OLAP queries. Please refer to the HR data model below when you answer those questions.

1.(Assessed) Find all the departments where the total salary of all employees belong to that department is higher than the the average salary of all employees. Show the department_id, the department_name and the total salary in the output in descending order of total salary.（Use WITH statement）

 

(Assessed) Using top down approach, show all subordinates of Lex De Haan. (hints: use recursive with)

 

Use bottom up approach, show all superordinates of William Gietz.

 

1 2 3 4 5 6 7 8 9 10 1 department_id ADC department_name 80 Sales 50 Shipping 90 Executive 100 Finance 60 IT 30 Purchasing 110 Accounting 20 Marketing 70 Public Relations 40 Human Resources 123 total salary 304,500 156,400 58,000 51,608 28,800 24,900 20,308 19,000 10,000 6,500