SQL Dojo

TLDR:
Imagine just before your DS interview - you are NEO your coach is Morpheus, and you will be practice SQL in rapidly changing schemas.


Now here is a little project I thought up:

Despite any number of excellent SQL based projects I have created I tend to get rusty in SQL as I don't use it on a regular basis. I decided it might be worthwhile  to setup a virtual space to practice, hence the dojo.

The dojo lets a student practice analytical sql primarily queries analysts use.
Ultimately I'd like to to use it in an agile manner as an LMS with a minimal UI.  This would require creating a story for each query and a test that the query returns a good answer. Also to make things interesting the tasks should be related and proceed from easy to more challenging and cover a number of techniques like filtering, aggregation and subqueries.

However, initially I want to have things up and running quickly and to collect questions and answers that reflect how to do create views on a small number of databases from courses or books. Also this system can also be used to see how well things work on different dbms with a goal of doing things in a portable fashion.

I thought I might share some specifics. The POC features should be:

1. Run server in docker - easy to install/restart/migrate (done)
2. Agile access - e.g. using visual studio code + pluging. (done)
3. Rich clients - MySQLWorkBench (done)
4. SquirelSQL - supports more RDBS systems. (done)
5. Access from Jupyter (done - but less agile)

Beyond the POC

1. Migrate db to AWS (more & bigger databases).
2. Create a web interface to
1. Awitch RDBMS
2. Log in,
3. Enter, and run queries
4. Show the output log
5. and the query output. 
6. store queries history
7. keep score 
8. indicate progress in units.
9. Feedback and discussion.
10. allow users to add stories and queries.
11. support non-sql dbs as well like
3. Develop small learning units to practice techniques.
1. [OK] basics
2. [OK] filtering
3. [OK] aggregation
4. [OK] subqueries
5. [] cleaning data & SQL wrangling
6. [] OLAP
7. [] design and ddl
8. [OK] CRUD + stored procedures python.
9. [] CRUD + stored procedures R.
10. [] CRUD + stored procedures Java.
11. [] transaction
12. [] create queries for a bi dashboard.
13. [] create queries for a marketing automation project.
4. Migrate queries to database
5. Show schema for the database.
6. Make things secure.
7. Isolated user.
8. reset DB.
9. Use serverless backends too
1. aws athena.
2. google bigquery.
10. Use noSQL dbs - mongo, neo4,
11. Connect to a dedicated environment like MySQLWorkBench 
12. Connect to a BI environment or Tableau / Power BI.
13. Use a freemium-hosted database like bigquery.
14. 
    

First snag:

Accessing MySQL v>8.0 requires a new protocol. I had to re-enable the old one using some obscure command to allow user + password connection or change to the mysql.connector.connect connector instead

TODO: find this, snag it, and record.
TODO: add this hack to the MySQL docker image.
TODO: automate the docker image to run a script to create and load data from a folder.
TODO: add a docker image for Postgres with equivalent capabilities.
TODO: put the docker images @ AWS
TODO: Get a docker image with the MySQL sample database, which is used in many tutorials.
TODO: migrate the project to Trello.

Updates:

• I installed Squirrel SQL to access multiple DBS via rich client.
• I installed GraalVM to do polyglot data science in a notebook.
• I created a Jupyter Notebook to access the MySQL database. 
• This is good for accessing a local database.
• I plan to update this to practice Polyglot data wrangling. i.e., get data from db into R and Python data frames and do some quick explorations.
• Spidering & Indexing.

Spinoff DOJOs

ETL 

ELK stack 

DB  

BIG DATA