What I Know About SQL

A review for me and maybe for you too!

Introduction

SQL stands for Structured Query Language. It is a common language for analysts, data engineers, data scientists or really anyone who works with data. It’s easy to pick up and important to know about, but gaining actual experience can range in trying to work with the language yourself with 1,000 rows and a few tables or being at a full-time position working with millions of rows. The point is no matter the number of rows, the queries would be very similar.

Structure

MySQL, MS SQL Server, Oracle, etc are different types of relational database management systems (RDBMs) that use SQL. A RDBMs has multiple tables (collection of rows and columns of data) that are related to each other in some way. Two major terms to understand are Primary Key and Foreign Key. The first is a unique, non-null identifier for an entry in a table and the latter is same value in another table that allows the two to be related. Unlike the primary key, the foreign keys don’t have to be unique.

Manipulation

To actually query our data, we want to use a SELECT statement followed by which columns are of interest FROM which table. If we want to select all the columns we can use the ‘ * ’ symbol like below.

SELECT * FROM my_table

If we had two tables we want to combine, then we want to use a type of join. The picture below is from Reddit and does an amazing job of visualizing the types of joins and the code it belongs to.

Link

The only one missing is a cross join which takes all possible combinations of both tables. I’ve never done a cross join and don’t think you would really need it, but you should be aware of it.

Moreover, when working with two tables, there is a concept called “Aliasing”. For example, if we had two tables A and B, with a primary key and foreign key like ID, we need to specify which ID we are selecting and from which table. We would do this by saying A.ID or B.ID followed by other columns of B.value1, B.value2.

Filtering

After selecting the data of interests, we want to add filters to our data using GROUP BY, HAVING, and WHERE. This is where we start using logic on our data on our selects. A key fact to remember is HAVING and WHERE work similarly when there is no GROUP BY present, but in a GROUP BY, the GROUP BY and HAVING will take place first before the WHERE.

We can also order our data by using ORDER BY in ascending (ASC) or descending (DESC) order. Two honorable mentions to remember are LIMIT and TOP, which are used to limit the amount of returned rows.

SELECT id, name, sick_days FROM Employees
WHERE sick_days = 0
ORDER BY id ASC
LIMIT 5;

The statement above will go to the employees table and select id, name, sick_days of employees with no more sick days. It will order our results by their id and we would return the first 5 lowest id numbers.

Conclusion

Overall, these are some of the fundamentals of SQL that I wanted to write down to help me remember. There is much more to SQL, like creating databases, inserting, deleting and updating data and more, but with just the knowledge above, you can solve a lot of SQL problems!

If you’re familiar with Python, check out my article on SQLite3 where I go over examples of creating, connecting and manipulating a database!