Analysis 2 & Spotify kick-off

Preparation:

Sub Queries

Exerices 1: Individual

• Retrieve the average salary of all employees

• Retrieve the names of employees with a salary above the average

• Retrieve the department name and average salary of all departments

• Retrieve the department name and average salary of all departments with an average higher than the average department salary

• Employees and Departments

  • Retrieve all departments and their average salary with an salary average higher than the average departments salary excluding commission?

  • Same question: Including commission?

  • Hint: IFNULL

Very advanced queries (Optional)

If you solve these queries below - coffee is on me

• Pokemon

  • Retrieve a resultset of a pokemons name, their speed and their primary_types average speed:

    

    image-20220909101152569

Hint: https://bernardoamc.github.io/sql/2015/05/04/group-by-non-aggregate-columns/

• Retrieve a resultset with pokemon whos secondary type speed is faster than their primary type speed on average.

image-20220909102428219