SQL
Start practicing SQL for your next Data Science interview right now!
No need to install, set anything up or pay
There are several data courses and other platforms where you can start learning and practicing SQL right away. Some of them are really useful, the others not so much. I’ll share the one that I like the most in the end of the article. So, you’d usually get a blank window on the right, problem description on the left, and you’re ready to dive in. But for me the disadvantage of this setup is that it’s way too artificial: all the nuts and bolts are completely hidden from you. And when you get to work with the real thing you can get lost. On the other hand, if you’re totally new to it and you start figuring out which Database Engine to use, then installing it, then choosing a Database Manager and finally connecting it all together, you might get stuck in the details and never get to write any SQL query.
Here I prepared for you something completely different — the best of both worlds. You’ll get a much more realistic setup that’s still running in your browser (and on the cloud of course!!!). And it’s fully prepared to be able to follow along this wonderful article by Farzad Mahmoodinobar
I think it’s an amazing summary and totally worth reviewing whether you’re just starting your SQL journey or need an upcoming interview refresher.
Ok, let’s start with a spoiler — the end result, and then I’ll walk you through the steps to get there.
What you see on the screenshot below is Cloudbeaver web interface. Cloudbeaver is a little brother of DBeaver — extremely popular Database Management tool.
Behind the scenes it’s connected to an instance of PostgreSQL which in turns is a very widely used open source object-relational database system. The setup already has the tables from the article I mentioned above and it’s totally ready for a follow-along.
All the code that we use to spin this up on Play With Docker is in this github repo created by yours truly.
Now put your seatbelt on, we’re about to take off.
Prerequisites
All you need to get this up and running is a Docker account to be able to use Play With Docker (PWD).
If you don’t have one yet, you can register it on the Docker Hub.
Set up
- Log into Play With Docker (PWD) and press “Start”
2 . Press “ + ADD NEW INSTANCE”
3. In the terminal that appears clone the repo and launch docker compose
$ git clone https://github.com/aaalexlit/sql-concepts.git
$ cd sql-concepts/
$ docker compose up
4. When the logs stop appearing, press hyperlinked “8080” next to the “OPEN PORT” button on the upper row
5. Cloudbeaver’s web interface will appear in a new tab
6. You need to log in with predefined user and password — learner/learner (don’t do this on production systems please)
We’re doing this to access the preconfigured connections and sql sheet.
7. Press the “Tools” button and enable “SQL scripts manager” option
8. Double-click “sql_for_data_science.sql” on the appeared panel and press “Restore”
Congrats! Now you’re ready to start exploring the tables, running SQL queries and follow along the article.
Important notes
Search path
Welcome to the real world, hehe!
The tables with the data are not in the default schema, so if you ever forget to set the search_path you’ll run into relation doesn't exist error.
SET search_path TO examples;Running locally
Of course, if you have Git and Docker installed on your machine, you can follow the steps above locally.
The only difference is that you’d need to go to http://localhost:8080/ to see the Cloudbeaver’s web interface.
What’s next
In the next article I’ll explain how you can create your own tables in this setup and share my thoughts on its other practical use cases.
More interview prep
In the beginning I promised to share a good resource of Data Science SQL interview questions. Please welcome Data Lemur
created by Nick Singh one of the authors of the famous Ace the Data Science Interview book.
Please note that the link provided is a referral link. If you follow it to register on the resource the author of this story might eventually get some small perks.
Kudos for getting this far! I hope it was useful.
Do not hesitate to leave a comment or ask a question, I’m happy to help you setting things up.
