/workshop-9

MYSQL WORKSHOP

Workshop prerequisites installations

  1. Download and install docker
    1. Verify installation: run "docker ps" in console.
  2. Download and install mysql workbench
    1. After pressing download you'll be asked to login or register, just skip it using the "No thanks, just start my download." click (see pic)

Workshop DB setup

Execute the following command to run the container with new DB:

docker run --name mysql_workshop -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -e MYSQL_ROOT_HOST=% -e MYSQL_DATABASE=mysql_workshop -d mysql/mysql-server

This will create a docker container running a mysql db named "mysql_workshop".

Let's connect to the DB using mysql workbench.

Create new connection in mysql workbench, configure and test your connection:

Now You should be able to see the mysql_workshop DB and run queries on it.

Download workshop_create_table.sql and execute it in workbench. This will create the tables for our Football DB.

Download workshop_instert_data.sql and execute it in workbench. This will insert data to the tables in our DB.

Workshop exercises

Write SQL queries:

  1. How many players from Serbia exists in our DB?
  2. Which country has the most players in our DB?
  3. Name all players who assist a goal in match 41 (without a join)
  4. Name all players who assist a goal in match 41 (using join)
  5. Name all players who are attackers and didn’t score any goal (using left join)
  6. Use explain to get information about the last query (screenshot your results)
  7. Add index to players table on position column
  8. Run explain again - what are the differences? meanings?
  9. Using transaction, write a query to get the youngest manager and move him to a new club