Getting started with SQL 🔢
Hey you all!
It’s Josep here, one more week! 👋🏻
Today, I'm writing from Barcelona, getting ready to take a plane next Thursday right to NYC! 🔥
The most important news of the week?
Well, August tends to be a quieter month on the professional front, but that doesn't mean it's been uneventful!
The highlight of my week was meeting
(in-person!!) at a charming café in Barcelona. ☕️We had a fantastic time getting to know each other away from our screens. If you haven’t checked her
newsletter, make sure to follow her to discover what a Data Scientist does at Spotify.I participated in a new X (Twitter) space about Python & DataViz, which you can listen here.
Now that we’ve caught up on life updates, let's dive into the important stuff 👨🏻💻.
Following up on my initial DataBites issue, this week, I’ve crafted a detailed roadmap to help you navigate the complex world of SQL, from the very basics to the most advanced concepts.
Why Learn SQL?
Well, in our data-driven world, data is the new fuel. Every industry relies on it to make informed decisions. You can explore why SQL is essential in my first DataBites issue.
The final question, then, is:
How do we manage all the data we generate?
And the answer is simpler than you might think:
SQL is still the best tool for the job.
Navigate Your Way Through SQL Mastery
Whether you're a data enthusiast, a budding analyst, or an experienced developer looking to sharpen your database management skills, understanding SQL is crucial.
However, we usually find ourselves quite lost when having to get started with a new tool. This is why I prepared a roadmap to smooth the process.
Let’s break down each of the previous steps!
1. Learning the Basics
Before diving deep, it's essential to grasp what is SQL. Begin with:
What is a Relational Database?
Comparing SQL and NoSQL Databases
Understanding SQL Syntax and Basic Commands
2. Exploring Data Types
Once you have a clear image of SQL, you should understand the core data types you will be working with 👇🏻
Integers, Decimals, and Booleans
Character Types: CHAR and VARCHAR
Dates and Binary Data
3. Mastering Basic Keywords
The first real step is to familiarize yourself with the commands that form the backbone of SQL querying:
SELECT, FROM, WHERE
ORDER BY and LIMIT Clauses
You will see that SQL queries are usually quite easy to understand, as their syntax resembles natural language. The most common SQL query you will find is.
Already proficient with basic commands?
Now it’s turn to understand how to manage databases with the following two steps:
4. Data Definition
Learn how to structure and manage your databases with:
CREATE, ALTER, DROP Statements
5. Data Manipulation
Get hands-on with inserting, updating, and deleting data:
INSERT, UPDATE, DELETE Commands
Now you should keep getting better with SQL programming language 👇🏻
6. Understanding Aggregation
Aggregate functions are powerful tools for summarizing data:
SUM, AVG, COUNT, MAX, MIN
This will be one of the most important steps when creating KPIs and specific metrics.
7. Enforcing Constraints
Ensure data integrity through constraints:
PRIMARY KEY, FOREIGN KEY, UNIQUE
NOT NULL and CHECK Constraints
Our data will usually be spread across many different tables, this is why being able to merge data from different sources is crucial in SQL.
8. Mastering JOINs
JOINs are critical for combining data from multiple tables:
INNER, LEFT, RIGHT, and FULL OUTER JOINs
Your SQL skills here are already really good!! 🙌🏻 But learning must go on…
9. Leveraging Subqueries
As queries keep getting more complex, you need to create subqueries.
Nested and Correlated Subqueries
EXISTS and NOT EXISTS
Now you should understand advanced syntax like specific functions and new ways of merging data like UNION.
10. Advanced Syntax
Explore more complex SQL operations:
UNION, INTERSECT, EXCEPT
CASE, COALESCE, and Functions for NULL Handling
Once a result is good, you might want to create a view!
11. Working with Views
Views can simplify complex queries:
Creating, Modifying, and Dropping Views
Now you should focus on crafting better queries by leveraging indexes and following best security practices 💥
12. Optimizing with Indexes
Indexes improve query performance:
Unique, Clustered, and Non-Clustered Indexes
13. Data Integrity and Security
Now that you are here, I strongly recommend you get familiar with security best practices. Maintain the security and integrity of your database:
Using GRANT and REVOKE
Database Security Best Practices
The two final steps is learning how to craft better and more optimized SQL queries. You know I am a big fan of modular code with CTEs, so that’s something we will talk more about in coming issues! 😉
14. Performance Optimization
Techniques to enhance the efficiency of your queries:
Query Analysis, Optimizing JOINS, Reducing Subqueries
15. Advanced SQL Techniques
For seasoned users, explore sophisticated SQL features:
Windows Functions, Recursive Queries
Pivot/Unpivot Operations, Common Table Expressions (CTE)
And this is all for today!
We will see in more detail most of the previous steps, this is just a summary to help you get started with SQL!
Are you still here? 🧐
👉🏻 I want this newsletter to be useful, so please let me know your feedback!
Additionally, you can let me know any preference for future content or any idea you think might add more value to you!
My latest articles 📝
The Complete Guide to File Storage on Azure with Blob & Files in DataCamp.
Why You Should Learn SQL in 2024 in KDnuggets.
The Transformers Architecture - What’s the magic behind LLMs? in AIgents.
Nice articles (my weekly favs)! ♥
Distribution First: Why Audience is the New Product Strategy by
Creating Ideal Data Scientist from Scratch by
Ace Data Science Coding Interviews: A Step-by-Step Guide with the ADDA Framework by
My Favorite Resources! 👨🏻💻
I strongly recommend following AIgents roadmaps if you want to become a full Data Scientist, ML Engineer, or Data Engineer,
Understand ML models following my MLBasics series!
You can follow the Data Engineer Zoomcap on GitHub to become a fully proficient Data Engineer.
Want to learn GCP? You can follow The Cloud Girl and learn using her intuitive illustrations!
Want to get more of my content? 🙋🏻♂️
Reach me on:
LinkedIn, X (Twitter), or Threads to get daily posts about Data Science.
My Medium Blog to learn more about Data Science, Machine Learning, and AI.
Just email me at rfeers@gmail.com for any inquiries or to ask for help! 🤓
Remember now that DataBites has an official X (Twitter) account and LinkedIn page. Follow us there to stay updated and help spread the word! 🙌🏻
Hi Josep,
thank you for participating in the Python Space on X again - it's always great to have your input in the discussion!
Loved the image! It’s so helpful to visually see the steps you should take to learn a skill like SQL. It makes it way more digestible, thanks for the read Josep!