Data analysis - RegEx & SubQueries

Preparation:

Sub Queries

Regular Expressions

Exercise 1: Regular Expressions

Verify each regular expression at: https://regexr.com/

For each exercise - think of the edge cases

A)

• Write a regular expression such that a correct input lives up to the following danish phone number standard: [phone]

  • +45

  • Whitespace

  • 8 digits

Test data for regexr: Success

[phone]

[phone]

[phone]

• Write a regular expression that detects laughter in a text. Laughter is defined as "ha, he, hi, ho" in a sequence of 2 or more repetitions.

  • "hahaha" is accepted as laughter

  • "ha" is not accepted

  • "kekeke" is not accepted

C)

• Write a regular expression such that a correct input lives up to the standard of a danish cpr number

  • Is a valid CPR number 451890-4455 ?

D) advanced (optinal)

• Write a regular expression that checks if a given string is a valid email address

  • The email address must contain exactly one "@" symbol.

  • The "@" symbol should be surrounded by at least one character on each side. (Not @)

  • The email address must have at least one period (".") after the "@" symbol.

  • The period (".") should be followed by at least two characters.

Exercise 2: Regular Expressions in MySQL

In the spotify database

• How many artist have numbers in their artist name?

• How many distinct artists either start with a number or ends with a number?

Advanced (Optional)

• How many songs have features?

Exerice 3 - Subqueries

• 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 a salary higher than the average employee salary

(Advanced - optional)

• Employees and Departments

  • Retrieve all departments and their average salary if the department has an salary average higher than the total average department salary excluding commission?

  • Same question: Including commission?

  • Hint: IFNULL: https://www.mysqltutorial.org/mysql-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 the average speed of their primary_type:

  

  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