Use SQL Like a Badass – Part 1/4

After you gain confident in excel, the next analytical muscle to build is some basic database skills. Often this happens by force when you get a huge data set that crushes excel and you need an alternative.This leads you into the world of databases and SQL. So what the heck is SQL and why should you bother?

SQL (pronounced ess-que-el) stands for Structured Query Language and it is how you talk to a database to access and manipulate data. Learning some super basic SQL goes a long way in being able to handle Big Data and allows you to interrogate transactional data that often gets dumped out of an enterprise system.

You will hear database applications such as MS Access, MS SQL Server Express, PostgreSQL, MySQL, SQLite etc. They all use the SQL language and they have their different use cases, but I have found that MS SQL Server Express Management Studio is the best application for business analytic. If you want to read through how to installing SQL Express and to review some hacks for SQL Management Studio, please see the post Get Started with Big Data With SQL Express.

Databases look a little scary when you first start. There is coding and joins and other unfamiliar database nerd stuff. However, there are only 7 SQL commands that you need for basic data manipulation for analytics. Once you have a grip on these commands, you can quickly open up your skill set to include Big Data. These core SQL skills are:

  1. Select – used to read data
  2. Alter – used to add or drop a column
  3. Update – used to change existing data
  4. Insert – used to add new data
  5. Joins – used to combine tables
  6. Delete – used to delete data
  7. Views – a virtual table which is the result of a query (this is useful when you do not want to write new tables to your database)

As an aside, a valuable website to check syntax for SQL can be found at w3schools.

Ok, let’s start with the SELECT statement.

1. Select

When you import a flat file into a database or if you are using an existing database, you will want to be able to see what data is in there and be able to filter the data. Viewing the data is achieved via a Select statement. Note that a * can be used as a wild card to bring back all columns.  In the following examples we will use a table of Chuck Norris quotes called ‘Chuckquotes’. Note that SQL is not case sensitive, so ‘SELECT’ is the same as ‘select’.

I have attached a download link for the data used below if you want to follow along.

Download Chuck Norris Quotes

 

1.1 SELECT *

If you want to select all columns and all data you would execute the following.

In our example, we would execute a select from the table ‘Chuckquotes’.

This gives us a table of 3 columns ‘id’, ‘quotes’ and ‘comment_qty’.

idquotecomment_qty
1When Chuck Norris does a pushup he isn't lifting himself up he''s pushing the Earth down.4
2Chuck Norris' facial hair is known to cut diamonds.3
3Chuck Norris once robbed a gun store with a knife.2
4Chuck Norris abducts aliens.15
5Chuck Norris once fought superman. The loser had to wear his underwear over his pants.7
6Chuck Norris can make a happy meal cry.13
7Chuck Norris counted to infinity. Twice.8
8In space Chuck Norris can hear you scream.0
9Chuck Norris is the reason Waldo is hiding.11
10M.C. Hammer learned the hard way that Chuck Norris can touch this.3

1.2 SELECT with specific columns

You can also select specific columns from a table.

In our example, if you wanted to select the ‘quote’ column only you would use the below statement. Note that you usually only use a semi-colon at the end of the statement when you have multiple statements one after another.

This yields just the single column ‘quote’.

quote
When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.
Chuck Norris' facial hair is known to cut diamonds.
Chuck Norris once robbed a gun store with a knife.
Chuck Norris abducts aliens.
Chuck Norris once fought superman. The loser had to wear his underwear over his pants.
Chuck Norris can make a happy meal cry.
Chuck Norris counted to infinity. Twice.
In space Chuck Norris can hear you scream.
Chuck Norris is the reason Waldo is hiding.
M.C. Hammer learned the hard way that Chuck Norris can touch this.

We could add more columns if we add a comma after the first column e.g. SELECT quote,id FROM Chuckquotes.

1.3 SELECT and WHERE

That is great we can show all our data, but how do we add a filter? This is done by adding a WHERE clause to the end of our select.

In our example, we may want to see only rows where the comment_qty is more than 10. To do this we add:

idquotecomment_qty
4Chuck Norris abducts aliens.15
6Chuck Norris can make a happy meal cry.13
9Chuck Norris is the reason Waldo is hiding.11

The WHERE statement can take a number of variables.

  • = – Equal to.
  • > – Greater than.
  • < – Less than.
  • >= – Greater than or equal to.
  • <= – Less than or equal to.
  • <> –  Not equal to.
  • BETWEEN – Between two values.
  • LIKE – Search for a pattern.
  • IN – Multiple possible values for a column.

A common use for the WHERE clause is for string wild cards. You may want to look for any quotes that mention the name Waldo. Note that for a wildcard, you add in the % key within the string quotes. To do this you would write:

idquotecomment_qty
9Chuck Norris is the reason Waldo is hiding.11

Lastly, you can combine WHERE clause with conditional statements like AND/OR. For example:

idquotecomment_qty
1When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.4
2Chuck Norris' facial hair is known to cut diamonds.3
3Chuck Norris once robbed a gun store with a knife.2
9Chuck Norris is the reason Waldo is hiding.11

1.3 SELECT and ORDER BY

The last important part of a SELECT statement is to sort the data. We do this using the ORDER BY clause. For example, if you wanted to order the quotes by the column ‘comment_qty’ descending, you could write:

Note that the default is ascending (ASC), so the expressions ‘ORDER BY comment_qty’ gives the same result as’ ORDER BY comment_qty ASC’.

idquotecomment_qty
4Chuck Norris abducts aliens.15
6Chuck Norris can make a happy meal cry.13
9Chuck Norris is the reason Waldo is hiding.11
7Chuck Norris counted to infinity. Twice.8
5Chuck Norris once fought superman. The loser had to wear his underwear over his pants.7
1When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.4
2Chuck Norris' facial hair is known to cut diamonds.3
10M.C. Hammer learned the hard way that Chuck Norris can touch this.3
3Chuck Norris once robbed a gun store with a knife.2
8In space Chuck Norris can hear you scream.0

That will do for the first installment of ‘SQL for Analytics like a Badass’. We will pick it up next with UPDATE in Use SQL Like a Badass – Part 2/4.

Share on LinkedInEmail this to someoneTweet about this on TwitterShare on FacebookPrint this page

Leave a Reply

Your email address will not be published. Required fields are marked *

*