SQL: The Art of Wrangling Data Like a Pro. Part 1

SELECT
  SQL
FROM KNOWLEDGE
WHERE STUDYING = 'EASY'

Photo by Ben White on Unsplash

SQL is like a magic spell that lets you “ask” a database all sorts of questions and get back answers. Think of it as a wizard who knows everything about all the information stored in the database, and you can ask it any question, and it’ll give you the answer in a snap! Amazing right?

But how do you ask the questions to the database correctly?

First, we need to know the basics of SQL: its structure!

SELECT
FROM
(JOIN)
WHERE
GROUP BY
HAVING
ORDER BY 

Each of the words above is like rule statements used in SQL language to talk with the Database; you can think of it like you asking what you need but mapping the way to gather your needs.

In Part 1, we will see the SELECT, FROM, and WHERE statements.

Let’s use the following data tables so we can guide us in the following steps

users_uni

users-uni table

programs

programs table

I created these two tables as random data so we can use them as an exercise. We can say that is data from Sebastian University, where the first table is the users' data and the second table is the academic programs offered by Sebastian University.

Let's start with the SELECT statement

SELECT statement is the one that orders the SQL engine what is what you want to see; in other words, what are the columns that you are asking to the database?

For example, if you want to know the first and last names of the users in the University, you will write ‘name’ and “last_name” inside the SELECT statement like the following:

SELECT
  name
  , last_name
FROM users_uni

This will bring you the name and last name of the users in the university. As I told you, you are talking and asking the database your needs, and SQL will be your translator to the database.

Image by Author

You can do many things inside the SELECT statement, like aggregating numeric data, making any IF (CASE in SQL) statements, changing dates, and so on. These type of thing we will take a look in part 2.

FROM statement

The FROM statement is you telling the database where you need the data, mapping the database to the table you are trying to pull data from. In the last example:

SELECT
 name
 , last_name
FROM users_uni

We are telling SQL that pull the data from the users_uni table. So, we are telling the database: “Hey, I need the name and last name of the users in the university from users_uni, which is the table that stores that information”

Or, let's say you want to know the name of the programs like: “Hey, I need to know the name of all the programs”

SELECT
  name
FROM programs

Image by Author

SELECT and FROM

These two statements are obligatory on a SQL query and are the principal statements for you to ask the database for data; this is how the SQL engine will search for your needs; it's the mapping. As I said before, the SELECT statement is what you want, and the FROM is from where you need it.

WHERE statement

Now let's talk of a very important clause in SQL, the WHERE clause. This statement is your filter. So, let us say from Sebastian University, you only need the name, last name, and birthday of those users that are students. You will run a SQL like this one:

SELECT
  name
  , last_name
  , birthday
FROM users_uni
WHERE type = 'Student'

If you see, I use the “type” column with an = this is for me to tell the DB:

Hey SQL tell the DataBase: I need you to SELECT the name, last name, and birthday FROM the users_uni table WHERE the type of user is a (=) Student.

Result:

Image by the author — I just added “type” to the SQL query so you can see it brings only the Students.

Now, let us say we want to see all those female students. Can we do more than one filter in the WHERE clause? Yes! We use the “and” inside the where clause and the query will look like this:

SELECT
  name
  , last_name
  , birthday
FROM users_uni
WHERE type = 'Student'
AND sex = 'Female'

Same as the one before, I use the = sign to manifest to the SQL engine that I need those students that are Females in the Sex column.

Result:

Image by the author —Like the last one, I add “Sex” and “Type” columns too in the query so you can see the result of only bringing Female Students.

As I said before, the WHERE clause is one of the most powerful clauses in SQL; it will help you narrow your data of what you need without doing any manual data cleaning; as I said, it is asking your needs of the database.

So, as a summary, we saw SELECT, FROM, and WHERE statements in this article:

• SELECT: as its name says, select the columns and data you want to see.
• FROM: Name the table from where the data comes, from where you want to pull the columns you name in your SELECT.
• WHERE: filter. Filter the data that you want to see. You filter the rows of the table, giving rules to the column.

This was part 1 of a series of SQL language stuff that I will start sharing from now on. I love SQL, and I think is a very important skill if you are around data; it will help you a lot to analyze data faster and, most important, get the data that you want without struggling.

In the next parts, I will cover the other clauses I named in the SQL structure (JOIN, GROUP BY, HAVING, and ORDER BY). Also, I will cover the CASE WHEN clause and some of the aggregated functions we can make inside the SELECT statement for numeric, string, and date data types.

I hope you like this, and let me know if you want to know specific things in SQL that I could help you with; if I don’t know how to, I will try to learn it to help you!

Photo by Matt Jones on Unsplash