I don’t care who you are. Learn SQL.

The Ackchyually series

There's a big problem we face in the industry today - there are so many abstraction layers new developers go through when building a new project, that they end up having no idea why you are even using the solutions that you're using.

The foundation of explaining why things are the way they are is what will set apart a new programmer from an experienced one. And this is what we're aiming to do here. I'll be sharing the best resources to go deep into the why behind technologies and detailed but simplified explanations.

By the end of it, I'll give you a small project to practice these ideas and help you remember them better.

Hacktoberfest

Hacktoberfest has started and there's no better way to get real-world experience than diving deep into open-source. If you haven't registered yet, DO IT here.

If you're feeling lost and don't know where to get started, here are 2 steps to get you off the ground:

1. Join communities like EddieHub for open-source guidance or 4C for Hacktoberfest content.

2. Find good issues to get started with by using the Finder by EddieHub or search for the label "good first issue" on GitHub.

Happy hacking! 🎉

Bro, can you even SQL?

I did I tweet back then that a lot of people seemed to relate

So, as many of you agreed, SQL is one of the most basic skills a software engineer or data professional should have. So I'll be sharing some of my favorite resources to guide you through SQL and learn why is this such a fundamental skill for a developer.

Why SQL?

Back in the 60s, data management was a real mess. The systems were very rigid, complex, and not intuitive at all. In the 70s, Edgar Codd published a seminal paper titled "A Relational Model of Data for Large Shared Data Banks". Where he introduced the concept of the "relational model". The idea was to represent data as sets of tables and means to easily manipulate these tables.

After that, Relational Software (now Oracle) started the first SQL-based database for businesses, which IBM later made standard.

We can break SQL down into two main categories:

Data manipulation language (DML): DML statements are used to read, insert, update, and delete data in tables.

Data definition language (DDL): DDL statements are used to create, modify, and delete database objects, such as tables, views, and indexes.

DML statements can be used to change the contents of a database, while DDL statements can only change the structure of a database.

The SQL iceberg

When it comes to SQL, you can go as deep as you want in terms of complexity inside these categories.

If you think this is interesting and want to dive deep into each one of these concepts, Aryan Ebrahimpour made this amazing blog post about it "Explaining The Postgres Meme".

But if you're a software engineer and don't plan to work heavily with data, just stick to the first level and you should be fine.

OMG what should I learn?

Here are the base SQL concepts you should be familiar with as a software engineer:

1. Basic SQL Commands:

   • SELECT: Retrieve data from a table.

   • INSERT: Add new records to a table.

   • UPDATE: Modify records in a table.

   • DELETE: Remove records from a table.

2. Advanced Querying:

   • JOIN: Combine rows from two or more tables based on a related column.

   • GROUP BY: Group rows that have the same values in specified columns into summary rows.

   • HAVING: Filter results of a GROUP BY clause.

   • ORDER BY: Sort the result set in ascending or descending order.

   • DISTINCT: Remove duplicate values in the result set.

3. Subqueries:

   • The ability to embed one query within another, for instance, using a SELECT statement within another SELECT.

4. Data Definition Language (DDL):

   • CREATE: Create a new table, view, or other database object.

   • ALTER: Modify an existing database object.

   • DROP: Delete a database object.

5. Indexes:

   • Understand how indexes work, how to create them, and their importance in optimizing query performance.

6. Normalization:

   • The process of organizing data to minimize redundancy and ensure data integrity.

7. Transactions:

   • Concepts of ACID (Atomicity, Consistency, Isolation, Durability) which ensure data safety in case of errors or failures.

   • Commands like BEGIN, COMMIT, and ROLLBACK.

8. Stored Procedures & Triggers:

   • Create and manage stored procedures to encapsulate a sequence of SQL operations.

   • Understand triggers, which are SQL commands set to automatically perform an action when specific conditions are met.

9. Views:

   • Create and manage views, which are virtual tables based on the result set of an SQL statement.

10. Functions and User-Defined Functions (UDFs):

• Utilize built-in SQL functions and create custom ones to perform operations on data.

1. Data Control Language (DCL):

• Commands like GRANT and REVOKE, which control who can access and manipulate the data.

1. Performance Tuning:

• Optimize SQL queries for better performance.

1. Database Security and Integrity:

• Implement data constraints like primary key, foreign key, unique, check, and not null.

• Implement user roles and permissions.

Resources I like:

One of my favorite places to go when learning a new concept is freeCodeCamp. Their beginner's guide to SQL is amazing and it'll take you step by step into each concept (for FREE). https://www.freecodecamp.org/news/a-beginners-guide-to-sql/

If you like videos, Alex The Analyst has a great one where he explains how to get datasets, import data into SQL, and do some data exploration: https://www.youtube.com/watch?v=qfyynHBFOsM&t=2s

Have fun!