SQL for Data Analysis – Tutorial for Beginners – ep2

This is the second episode of my SQL for Data Analysis (for beginners) series and today I’ll show you every tiny little details of the SQL WHERE clause. It’s not by accident that I dedicate a whole article to this topic: SQL WHERE clause is essential, if you want to select the right bit of your data from your datatable!

In the first half of this article I’ll show you the different operators. In the second half, we will finally import our favorite 7.000.000+ rows dataset (the one with the airplane delays) and eventually I’ll give you a few assignments to test your SQL knowledge and practice a bit!

If you are new here, let’s start with these articles first:

1. How to install Python, SQL, R and Bash (for non-devs)
2. SQL for Data Analysis – Tutorial for Beginners – ep1
3. How to install SQL Workbench for postgreSQL

Make sure your SQL data environment works

If you have already done the above mentioned articles, you most probably have these in place, but just in case, please double-check if you have these 3 things:

1. Your fully-functioning data server (from this article)
2. SQL Workbench with an established connection to your data server (from this article)
3. The zoo data set (from this article)

Check-check-check? Okay, you are ready to learn new things!

SQL WHERE Clause

In SQL for Data Analysis episode 1 we did this from the Terminal, but this time please re-run this query from SQL Workbench:
SELECT * FROM zoo WHERE animal = 'elephant';

This is the most basic example of how to use SQL WHERE clause. But you can be more sophisticated…

SQL WHERE with comparison operators

Well first of all – you can do more than “something equals to something”. It can be “greater than”, “less than”, “not equal”, etc…

Here’s a short list of the comparison operators, that works with the SQL WHERE clause:

Let’s see some concrete examples:
1. How would you select all the animals, that are not zebras?
Solution:
SELECT * FROM zoo WHERE animal <> 'zebra';

2. How would you select all the animals, where the water_need is less than 300?
Solution:
SELECT * FROM zoo WHERE water_need < 300;

Okay, I think you got it!
So let’s take this to the next level!

SQL WHERE clause with LIKE operator

Sometimes you want to do your selection based on a pattern. Take a look at this fictional data set:

Let’s say, you want to select all the rows where the user_name starts with “frank”.
Now it’s time to boost your WHERE clause with a LIKE operator:
SELECT * FROM franks WHERE user_name LIKE 'frank%';

How does it work?
The LIKE logical operator tells to SQL, that you want to do filtering based on a pattern. And then you specify that the pattern is 'frank%'. The tricky character is the % – it means that after frank any and as much character can come as you want. Hence your result will be:

Let’s go back to our zoo dataset and try to answer these questions:
1. How would you select all animals, that’s name contains at least one e character?
Solution:
SELECT * FROM zoo WHERE animal LIKE '%e%';

It needed a % at the beginning and also at the end of the pattern, because the ‘e’ character could be anywhere in the word, not just at the end or the beginning. (If you don’t get it, try to remove, the first % and you will understand it immediately!)

2. How would you select all animals, that’s name ends with ‘roo’?
Solution:
SELECT * FROM zoo WHERE animal LIKE '%roo';

There is another type of tricky character: _. (Oh, by the way, from now on I’ll just use its official name: wildcard.)
% means any and as much characters as you want. _ means any character you want, but only one.
Eg. the word tiger can be expressed by wildcards like 't%'. But you can use 't____' too.

Knowing this trick, here’s another short question:
3. How would you select all animals, where the species is exactly five characters long?
Solution:
SELECT * FROM zoo WHERE animal LIKE '_____';

This is how SQL WHERE clause works with the LIKE operator.

How to combine the different conditions in SQL WHERE clause?

You can combine conditions with logical operators! There are more, but here I’ll highlight the two most important: AND and OR.

Let’s say we want to select all the animals, that’s name is exactly five characters long, but: not ‘tiger’. We have two conditions and we can do that with SQL WHERE boosted with the AND logical operator.

SELECT
  *
FROM
  zoo
WHERE
  animal LIKE '_____'
  AND animal <> 'tiger';

Oh, zebras only!

Now twist it a bit and select only those animals, that:

• have a name exactly five characters long
• are not tigers
• have a water_need more than 200

Solution:

SELECT
  *
FROM
  zoo
WHERE
  animal LIKE '_____'
  AND animal <> 'tiger'
  AND water_need > 200;

Yes: you can use multiple AND operators.

Finally, let’s see an example of the OR operator!

SELECT
  *
FROM
  zoo
WHERE
  water_need < 300
  OR animal = 'lion';

All the animals are returned to your screen, that are lions, plus all the animals that has less than 300 water_need.

Get it?

AND returns every row where all the conditions are true.
OR returns every row where at least one of the conditions is true.

The SQL IN operator

Imagine a situation where you want to select all the animals, that’s unique id is any of these: 1001, 1008, 1012, 1015, 1018.

You have already learned a way to do that! Can you find it out?
It’s the SQL WHERE clause with multiple OR operators. Something like this:

SELECT
  *
FROM
  zoo
WHERE
  uniq_id = 1001
  OR uniq_id = 1008
  OR uniq_id = 1012
  OR uniq_id = 1015
  OR uniq_id = 1018;

While it’s a valid solution, it’s certainly not the most elegant way to get the job done! If you want to reduce the number of ORs in your SQL WHERE clause, you should use the IN operator instead.

This SQL query returns the exact same result as the previous one:

SELECT
  *
FROM
  zoo
WHERE
  uniq_id IN (1001,1008,1012,1015,1018);

But it’s a little bit shorter.

The SQL NOT operator

There is an extra logical operator, which is by the way extremely simple. NOT will modify your logical operator to do it’s opposite instead. Let’s get back to a previous example with the 5 characters long animals:

SELECT * FROM zoo WHERE animal LIKE '_____';

How to select all the animals that are not 5 characters long?

SELECT * FROM zoo WHERE animal NOT LIKE '_____';

Simple as that.

Import a bigger dataset into postgreSQL

Are you bored with our 22 rows zoo dataset? Me too! It’s time to go a bit bigger! Let’s import the 7.000.000+ air-delays dataset, we have used before! Follow these steps to have it imported in your SQL database!

UPDATE: I’ve realized that this might be a bit complex – not difficult, just complex 😉 – so I put all the stuff into a short video too!  Click here or read below:

Note: we will work in bash. If you haven’t done my bash tutorial series yet, I highly recommend to do at least the first episode of it, but if you don’t want to, it’s also okay to simply follow my lead step by step below.

1. Open Terminal and login (ssh) to your data server!
2. Download the “flight delays” data!
   wget http://stat-computing.org/dataexpo/2009/2007.csv.bz2
   Note: If you have this already, skip forward to 5.
3. Set up dtrx! That’s a command line tool for unzipping stuff!
   (Note: this might have been already set up, if it’s so, skip this step!)
   sudo apt-get install dtrx
4. Unzip the csv file!
   dtrx 2007.csv.bz2
   Note: It will take around ~60 seconds to process the whole file, so don’t worry, your Terminal is not freezing, it just needs some time.
5. Format your data!
   cat 2007.csv |cut -d',' -f1,2,3,4,5,7,10,11,14,15,16,17,18,19 | grep -v ',NA' > sql_ready.csv
6. Now we have to give permission to our postgreSQL user to create tables and load data into them. This will need multiple steps. Here’s a gif first (note: my username is “dataguy” – your’s might be something else.)
   First sudo to the user called “postgres”:
   sudo -u postgres -iThen start postgreSQL:
   psqlThe prompt will change to this: postgres=#! Type:
   alter user [your_user_name] superuser;This turns your original user into a super user! Go back to your user!
   Exit from postgreSQL:
   \q
   Then exit from the user called “postgres”:
   exitFinally access your original user’s postgreSQL database from the command line:
   psql -d postgresOkay, this was the hard part.
7. Now all you need to do is create the table by simply copy-pasting these lines into your terminal:

   CREATE TABLE flight_delays (
     year INTEGER,
     month INTEGER,
     dayofmonth INTEGER,
     dayofweek INTEGER,
     deptime INTEGER,
     arrtime INTEGER,
     flightnum INTEGER,
     tailnum VARCHAR,
     airtime INTEGER,
     arrdelay INTEGER,
     depdelay INTEGER,
     origin VARCHAR,
     dest VARCHAR,
     distance INTEGER);
   

8. And finally, copy the data from the csv file you have just downloaded!
   COPY flight_delays FROM '/home/tomi/practice/sql_ready.csv' DELIMITER ',' CSV HEADER;
9. Go back to SQL Workbench and make a simple SELECT statement… but make sure that you use the LIMIT clause too. Now you have over 7.000.000 rows of data – even though postgreSQL can handle it easily, your computer might be frozen if you try print all that data on your screen. So try something like this first:
   SELECT * FROM flight_delays LIMIT 10;
   

Test yourself #1

Here’s assignment number one:
How many flights did the plane with the tail-number ‘N253WN’ take on 23th, April 2007?
(Hint: as we didn’t learn the COUNT function yet, it’s enough if you print line by line all the flights with the given conditions and count it for yourself.)
.
.
.
Done? Here’s my solution:

SELECT
  *
FROM
  flight_delays
WHERE month = 4
  AND year = 2007
  AND dayofmonth = 23
  AND tailnum = 'N253WN';

The only trick is, that the day, the month and the year are in different columns, so you have to use multiple AND operators to filter simultaneously for all the conditions.
(Note: since the month, year and dayofmonth fields are numbers, you don’t need single quotes. But you need it for the tailnum field, because it contains letters too. I’ll come back to this topic later.)

Test yourself #2

Select all the rows with these conditions:

• the flight was in April, 2007
• it was an even weekday (2nd, 4th or 6th day of the week)
• the flight distance was less than 50 miles
• either the departure or the arrival delay was less than 0 (means the plane took off or landed earlier, than it was planned)

(Hint: you will have to use OR and AND operators together!)
.
.
.
And my solution is:

SELECT
  *
FROM
  flight_delays
WHERE month = 4
  AND dayofweek IN (2,4,6)
  AND distance < 50
  AND (arrdelay < 0
  OR depdelay < 0);

Can you see the trick here? In SQL WHERE clauses AND is stronger by default than OR – thus if you don’t use the parentheses, it would work like this:

WHERE (month = 4
  AND dayofweek IN (2,4,6)
  AND distance < 50
  AND arrdelay < 0)
  OR (depdelay < 0);

And that would have returned wrong results!

Conclusion

Knowing, understanding and using SQL WHERE clauses with the right operators is crucial! In this article you have learned the most important parts of it! Now – having a good base knowledge – it’s time to continue with the next important SQL topic! In my next article I’ll introduce the basic SQL functions (MAX, MIN, SUM, COUNT) and I’ll show you some more important clauses (ORDER BY, GROUP BY, DISTINCT). You can continue by clicking here.

And if you want to be notified about my new articles, videos, webinars and other cool data stuff, subscribe to my weekly Newsletter (0% spam, 100% useful data content).

Cheers,
Tomi Mester