SQL For Spreadsheet Users — Part 2

How To Excel in SQL With Spreadsheet Skills

Photo by Markus Winkler on Unsplash

If you are new here, I recommend you go through SQL For Spreadsheet Users — Part 1 before you continue.

Retrieving information

You can’t analyse what you don’t see. Seeing precedes analysis. Access to data comes before data analysis. In this part, we are going to learn, with examples, how to retrieve information from the database and make it available for analysis purposes.

Of course, you cannot retrieve information out of thin air. We retrieve information from its storage. Information has to be stored in the database before we can retrieve it. As you might have guessed, we are working with the data stored in a structured database.

1.1 Understanding Database, Its Structure and Tables

In SQL we use databases in place of spreadsheets, and tables in place of sheets. A database is a collection of related tables, the same way a spreadsheet is a collection of related sheets. Think of a database as an umbrella under which there are multiple related tables.

You retrieve data from tables.

Tables are the physical storage for the data you want to retrieve and analyse. Tables are within a database. To reach tables, you must go through the database. You cannot access tables without first accessing the database. The sameway sheets won’t exist outside spreadsheets, tables won’t exist outside databases.

Let’s see a visual comparison.

In the image above, the spreadsheet represents a database, while the sheets represent tables. SQL tables are composed of columns like in a spreadsheet and data is stored in rows.

With the above structure, we can ask(query) information from the tables, filter them, turn them around and apply different functions to extract a meaning from it, a thing which Structured Query Language(SQL) excel at. SQL is a language or a means of communications, used to ask questions from structured data storages.

The difference between a spreadsheet and the table in SQL database is that a spreadsheet has a dynamic number of columns while SQL tables have a predefined and fixed number of columns. In SQL tables, instead of having letters as column names, you have tables with the names describing the content of the columns. In a customer table, you’ll find a column titled “names”. It is case insensitive. You can name it in uppercase or lowercase letters.

SQL requires well defined and described column names so that you can precisely retrieve data.

1.1 The First Query

Before we proceed to retrieving data from a database, I’d like to show you that you already have enough foundation to work with SQL, even if you might not have realised before.

To make your life easier, I have created SQL For Spreadsheet Users Google sheet so that we can get started with data already prepared for you. Go ahead and make a copy so that we can work together.

If you don’t have a gmail account, you’ll have to create it first to make a copy.

1.1.1 Using Structured Query To Retrieve Customer Data

Before we dive into actual SQL, I’d like to show you how SQL works without leaving the comfort of your spreadsheet. Of course, modern spreadsheets give you even an advanced way of querying the database from your spreadsheet so that you can combine, both the power of SQL and the power of the spreadsheet.

Let’s retrieve data from a spreadsheet using a Structure Query.

Google spreadsheet has a function called =QUERY().

This functions accepts 3 parameters:

1. Data range: Table range in your spreadsheet that has data you want to query.
2. Query: Your structured query to the data.
3. Headers: To define the headers for the retrieved data. → Optional

From the spreadsheet copy you’ve done, navigate to the customers table. Then in a cell to the right of the table type the following: =QUERY(B2:I11, “SELECT B, I”) then hit enter.

Voila! You just wrote your first SQL query and you got the results!

Let me break it down for you. In this function:

• B2:I11: The data range. The table that contains data to analyse.
• SELECT B, I : SQL query instructing the spreadsheet to retrieve the data from the table mentions. B and I are just the column names you want to return in your table name.

In SQL we use the SELECT keyword to specify the columns of the data we want to return. You have to specify what columns. In our case it was column B and Column I. If you want to return all columns in a table, use * known as “all” instead of column name.

For example:we read SELECT * as select all from the table you have specified. Go to the same cell and put =QUERY(B2:I11, “SELECT *”) to see the outcome.

1.1.2 Filtering using Where Conditions

Now that we know how to return data, what if you want to query data with filters and certain conditions? For example, what if you only want names and the amount of customer who are paying using cash?

Don’t worry. I got you covered. Replace the previous query with the following =QUERY(B2:I11, “SELECT B, I WHERE E=’CASH’ AND I > 1000”)

The results will change, because you’ve applied the filters. Can you see what the filters are?

• WHERE E=’CASH’ AND I > 1000

This filter has 3 main parts:

• WHERE to mark the beginning of conditions and filters.
• E=’CASH’ and I > 1000 filter conditions
• AND Logic operators used when using multiple conditions. You can use OR as well.

As you might have guessed, it’s totally fine to use one condition, it all depends on the results you want your query to return.

Now that we have an idea of how a structured query language looks, next we’ll begin to work with the database.

1.1.1 The Need for Authentication

Due to security reasons, you must authenticate yourself to access a database. You can get your authentication credentials from your database administrator, database engineer, DevOps engineer or anyone who is in charge of your database that hosts data you want to analyse.

There are also many tutorials online to help you download and install a relationship database management system(RDBMS) such as MySQL, MSSQL, Oracle, Postgresql.

Even if we are going to use an online database that requires a browse and internet. It’s important to know that for you to use an actual database in your organisation you will need to authenticate.

Your database authentication credentials is typically composed by:

1. Database host: A computer network address on which your database is hosted.
2. Database name: The container with tables that has information you want to query
3. Username: To authenticate you into a database.
4. A password to allow you in.
5. Port: to connect to.

Next: SQL for Spreadsheet users — Part 3.