Sitemap
TDS Archive

An archive of data science, data analytics, data engineering, machine learning, and artificial intelligence writing from the former Towards Data Science Medium publication.

Learn the basics of SQL with fun data from ABC’s hit show The Bachelor

11 min readApr 15, 2020

--

After studying data science last summer and learning to code for the first time, I was using mostly Python and I loved it. I am still more comfortable with Python, but once I began my job hunt, I quickly learned the importance of knowing SQL as a data professional. Of all the entry-level data job descriptions I have read, SQL proficiency has been required more often than not and definitely more often than Python. I learned the language in my data science bootcamp, but only scratched the surface. I knew I needed to go a bit deeper, so I taught myself. Then I got hired as a data analyst and learned even more on the job.

This lesson will be helpful to anyone with little to no SQL experience.

If you would like to follow along, I’m using DB Browser for SQLite which you can download for free here by clicking the link that corresponds to your computer’s operating system. I am also using three CSV files which I created full of Bachelor data, you can download them from my GitHub here, or you can use your own if you have a different topic of interest. Once you have SQLite open, you’ll need to:

  1. Create a New Database by clicking the button in the very top left corner.
  2. Save As whatever name you’d like, in a folder where you can find it later. I called mine bachelor_SQL_tutorial but it really doesn’t matter. It’s going to ask you to create a table next, just click cancel. We will be importing the tables in the next steps.
  3. Go to File > Import > Table From CSV File…
  4. Select one CSV at a time so that each one is its own table. A pop-up screen will appear where you can check that your data looks correct, i.e. columns have headers and are separated by commas before you continue.
  5. Lastly, you can edit the tables by clicking on a table name and then on Modify Table in the Database Structure tab. Here you can revise any data types, assign primary keys, rename or delete columns, and adjust the order of columns within the table.

A few key things to know before getting started:

  • A query is a question, a SQL query is referred to as such because it is communicating a question to the database
  • A database is a set of multiple tables. In this case, 3 tables but often they can be 100s or 1000s. Usually, you should have access to some sort of data dictionary which will explain what the data in each table/column means and/or an ERD (entity relationship diagram) which shows how the tables relate to each other.

Bachelorette — all the female cast members and their attributes

Bachelor — all the male cast members and their attributes

Hometowns — cities, states and countries with id #s

Press enter or click to view image in full size
  • Each table has a primary key which assigns a unique number to every row and no row should ever be identical to another
  • When writing a query, order matters, case does not. You will always start with a SELECT and FROM statement, but it not case-sensitive if you write select or SELECT. Just be prepared, if using SQL in the workplace, many companies will have a style they prefer that you’ll have to adapt to. The way I have seen it most often is capitalized clauses followed by lower case statements.

Please note terms may differ but for the purposes of this tutorial, I’ll refer to the below as clauses and the phrases that follow as statements. The order of clauses MUST always be as below. The mnemonic I learned to help me remember the order is Some French Waiters Grow Healthy Oranges & Lemons. It is kind of weird and takes a few tries to memorize but it works for me!

SELECT

FROM

JOIN

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

You will always need at least the SELECT and FROM statements but it will depend on what you are trying to pull if you will need the rest. SELECT is where you will enter the name of the columns you want returned, and FROM is where you’ll specify from which tables to extract the data.

When you are using a new database for the first time, it is always a good idea to explore and get yourself familiar with the different columns and the data in them. You can view all columns in a table with SELECT *.

Now we are ready to move to the Execute SQL tab and begin writing queries! Let’s look at all columns in the bachelorette table now. Type SELECT * FROM bachelorette into the white top section of your screen. To execute you can click the little blue triangle button or hold command + enter on your keyboard.

Press enter or click to view image in full size

If we only wanted to see name, age, occupation and season columns, it would look like this

As you can see, the season_contestant row has repeating data. Let’s say we want to see how many unique seasons are in the table. We can use DISTINCT to view each value only once.

Adding WHERE

The WHERE clause allows us to filter data by adding specifics about which rows we want to see. The syntax is:

column name + filter operator + specific requirements

The most commonly used filter operators are:

LIKE — for when you want the data in a row to match a string or part of a string. Often used with wildcards (%) at the beginning and/or end.

BETWEEN — for filtering a numeric column within a range

IN — to return any row that is an exact match to a string in a list or subquery

= — Matches exactly to

<> and != — both mean not equal to

> and < — Greater than and Less than

≥ and ≤ — Greater than or equal to, less than or equal to

If you need more than one filter, use AND or OR to chain statements together.

Get Rachel Koenig’s stories in your inbox

Join Medium for free to get updates from this writer.

Let’s do a few examples! Say we want to know which bachelorettes were nurses younger than 30, with brown or black hair.

Press enter or click to view image in full size

Or what if we want to see which contestants are between the ages of 28 and 32 in season 15 which either blonde hair or a name that starts with R.

Press enter or click to view image in full size

Or lastly, let’s see how many winners have not been blonde?

Press enter or click to view image in full size

You will also use the WHERE clause to find null or NaN values, like this:

The GROUP BY clause & Aggregate Functions

Sometimes you will want the answer to your query to be just one number, such as the average or total of a row. This is called aggregating. The most common aggregate functions are

  • COUNT — returns the total # of rows
  • AVG — returns the average of a column. Sometimes written as AVERAGE depending on which version of SQL you are using.
  • MIN — returns the smallest number in a column
  • MAX — returns the largest number in a column
  • SUM — adds all the numbers in a column and returns the total

For example, say you wanted to know the average age of the bachelor.

Often times, we will want to group the data before running the aggregate. Like if we wanted to know the average age of bachelorettes for each season we would group by season.

We can add other conditions by combining with the WHERE clause. COUNT(*) in the SELECT statement will count every row that meets the specifications of the query. Here we will use it to count how many bachelors over the age of 30 have each color of hair.

Another example is finding the average age of girls who won their season.

If we wanted to know who the tallest or shortest bachelor was, we would use MAX or MIN.

HAVING

HAVING is another way to filter data, like WHERE, except for with aggregate data. Let’s say we want to see which seasons had contestants with a maximum age of 30 or less.

Unfortunately, my data does not have a ton of good examples for HAVING. An example of how you might see it in the workplace though is if you need to find the average monthly sales for only months with an average of less than $100K. Or if you are pulling a list of customers whose total (sum) of sales in the last year is greater than $1000.

ORDER BY

There are many times when you will want the data returned to be in a certain order. ORDER BY is probably one of the more frequently used clauses. It can sort numerically or alphabetically. The default it ascending, smallest to largest or A-Z, but you can add DESC to the end of the statement to sort in reverse/descending order. Some of the examples we’ve already done would have benefited from an ORDER BY statement. For instance when we looked at all the DISTINCT seasons.

Or when we wanted to know which bachelorettes were nurses younger than 30, with brown or black hair, but in alphabetical order by name.

Maybe we want to see all the girls in order by age oldest to youngest, by season. You can order by multiple columns. So in this case, season would come first, then age and the results will sort by season and then within season by age.

We can also sort the men by height and age. Let’s put them in order from tallest to shortest, and any two are the same height, order them youngest to oldest.

JOINs & Aliasing

Do you feel like you have mastered pulling data from one table? Time to JOIN two (or more) together! You may have been wondering why we have not looked at city, state and country fields at all yet. Within the bachelorette and bachelor tables, the city, state and country ids mean nothing to us on their own. In order to find out where each person is from, we need to JOIN to the hometowns table. JOINing is also a good time to bring up aliasing. Sometimes when you join tables, they will have columns with the same names. In order to tell them apart throughout the query, you’ll need to write the table.column name (this is called namespacing). Since the table names can be long, it is easier to assign the tables new names that are only a letter or two. Below you’ll see that I named bachelorette as b and hometowns as h. This is aliasing.

JOINs will always follow the FROM clause. You will state which table you want to join onto the original and then must state which columns match up. In this case, we want to join the hometowns table onto the bachelorette table and match up the city_ids to her_city_id, state_ids to her_state_id and country_id to her_coutnry_id. In the SELECT statement, I left out the id and just chose to look at the locations themselves so we can see the girls’ hometowns easily. I sorted by country alphabetically and then by season, but only if the girls were not from Canada.

We can also do things like COUNT how many girls were from each county in the database. Aliasing works on aggregates as well. Below, I rename our COUNT(*) column to total_girls. You see GROUP BY again because we have an aggregate and ORDER BY because I wanted to see the countries in order from highest to lowest participants.

Press enter or click to view image in full size

We can also JOIN the bachelorette table to the bachelor table to see which girls appeared on which bachelor’s season. For this example, we need to match up b.season_lead = g.season_contestant. In the SELECT statement, I only included their names and ages, but as you can see I was still able to sort by his height. Jake Pavelka was the shortest bachelor so he and his girls are at the top.

Press enter or click to view image in full size

It is important to know JOINs are more complex than this. There are several types, INNER, RIGHT, LEFT, OUTER, UNIONS and multiple joins. I’m not going to go into detail about all of them in this post, but if you like this post and would like one all about JOINs, please feel free to let me know in the comments! For now, just know that INNER is the most common join and the default. The data pulled will include rows from both tables where the joining columns have a match and are not null.

LIMIT

Last but definitely not least is LIMIT. This is probably the most often used clause after SELECT and FROM. Instead of returning every single row, LIMIT allows you to only look at a subset. This is good for speed when exploring very large datasets of 100K+ rows because you can set a limit of 100 or 1000 just to get a look at what you’re working with. It can also be a tool if you only want to see the top results whether that is the top 10 tallest bachelors or the top 25 highest performing stores by sales (in the real world).

Let’s put everything we have learned together now! Below we find the top 5 oldest bachelors with brown hair who had bachelorettes from California, Texas, New York or Florida with an average age of less than 27.

Press enter or click to view image in full size

Here we count how many bachelorettes originated from each state in seasons where the bachelor was at least 6' 2", order them by highest count, then home state alphabetically and limit to the top 10 results.

Press enter or click to view image in full size

Other great resources for practice

I gathered my data from Wikipedia and cleaned and formatted it with excel and python.

--

Rachel Koenig
Rachel Koenig

Written by Rachel Koenig

Marketing Data Analyst and General Assembly graduate

TDS Archive
TDS Archive

Published in TDS Archive

An archive of data science, data analytics, data engineering, machine learning, and artificial intelligence writing from the former Towards Data Science Medium publication.