Many people are pursuing data science as a career (to become a data scientist) choice these days. With the recent data deluge, companies are voraciously headhunting people who can handle, understand, analyze, and model data.
Be it college graduates or experienced professionals, everyone is busy searching for the best courses or training material to become a data scientist. Some of them even manage to learn Python or R, but still can’t land their first analytics job!
What most people fail to understand is that the data science/analytics industry isn’t just limited to using Python or R. There are several other coding languages which companies use to run their businesses.
Among all, the most important and widely used language is SQL (Structured Query Language). You must learn it.
I’ve realized that, as a newbie, learning SQL is somewhat difficult at home. After all, setting up a server enabled database engine isn’t everybody’s cup of tea. Isn’t it? Don’t you worry.
In this article, we’ll learn all about SQL and how to write its queries.
Note: This article is meant to help R users who wants to learn SQL from scratch. Even if you are new to R, you can still check out this tutorial as the ultimate motive is to learn SQL here.
Good question! When I started learning SQL, I asked this question too. Though, I had no one to answer me. So, I decided to find it out myself.
SQL is the de facto standard programming language used to handle relational databases.
Let’s look at the dominance / popularity of SQL in worldwide analytics / data science industry. According to an online survey conducted by Oreilly Media in 2016, it was found that among all the programming languages, SQL was used by 70% of the respondents followed by R and Python. It was also discovered that people who know Excel (Spreadsheet) tend to get significant salary boost once they learn SQL.
Also, according to a survey done by datasciencecentral, it was inferred that R users tend to get a nice salary boost once they learn SQL. In a way, SQL as a language is meant to complement your current set of skills.
Since 1970, SQL has remained an integral part of popular databases such as Oracle, IBM DB2, Microsoft SQL Server, MySQL, etc. Not only learning SQL with R will increase your employability, but SQL itself can make way for you in database management roles.
SQL (Structured Query Language) is a special purpose programming language used to manage, extract, and aggregate data stored in large relational database management systems.
In simple words, think of a large machine (rectangular shape) consisting of many, many boxes (again rectangles). Each box comprises a table (dataset). This is a database. A database is an organized collection of data. Now, this database understands only one language, i.e, SQL. No English, Japanese, or Spanish. Just SQL. Therefore, SQL is a language which interacts with the databases to retrieve data.
Following are some important features of SQL:
Currently, businesses worldwide use both open source and proprietary relational database management systems (RDBMS) built around SQL.
Let’s try to understand SQL commands now. Most of these commands are extremely easy to pick up as they are simple “English words.” But make sure you get a proper understanding of their meanings and usage in SQL context. For your ease of understanding, I’ve categorized the SQL commands in three sections:
Before we start, you must know that SQL functions recognize majorly four data types. These are:
That’s all ! If SQL finds a variable whose type is anything other than these four, it will throw read errors. For example, if a variable has numbers with a comma (like 432,), you’ll get errors. SQL as a language is very particular about the sequence of commands given. If the sequence is not followed, it starts to throw errors. Don’t worry I’ve defined the sequence below. Let’s learn the commands. In the following section, we’ll learn to use them with a data set.
=
, !=
, <
, >
, <=
, >=
). They are used in conjunction with the WHERE
command.
rbind()
in R. Use it to combine two tables where both the tables have identical variable names.In addition, you can also write complex join commands by using comparison operators or WHERE or ON command to specify a condition.
substr()
function in R.These commands are not case sensitive; i.e., you need not write them in capitals always. But make sure consistency is maintained. SQL commands follow this standard sequence:
For writing SQL queries, we’ll use sqldf package. It is one of the most versatile package packages available these days which activate SQL in R. It uses SQLite (default) as the underlying database and is often faster than performing the same manipulations in base R. Besides SQLite, it also supports H2 Java database, PostgreSQL database, and MySQL.
Yes, you can easily connect database servers using this package and query data. For more details on this package, I suggest you read this github repo created by its author.
When using SQL in R, think of R as the database storage machine. The process is simple. You load the data set either using read.csv
or read.csv.sql
and start querying data. Ready to get your hands dirty? Let’s begin! I request you to code every line as you scroll the page. The more you write, the more confident you’ll become at writing SQL queries.
We’ll be using multiple data sets to understand different SQL functions. If you haven’t installed R yet, I request you to download here. For now, we’ll use the babynames data set.
> install.packages("babynames")
> library(babynames)
> str (babynames)
This data set contains 1.8 million observations and 5 variables. I suppose all the variable names are easy to understand except prop
. prop is calculated as n divided by the total number of applicants in that year ,i.e., the proportion of a name given year. Now, we’ll start working with sqldf package.
> install.packages("sqldf)
> library(sqldf)
Let’s check the number of rows in this data.
> sqldf("select count(*) from mydata")
#1825433
Ignore the warnings here. Next, let’s look at the data, we’ll look at the first 10 rows.
> sqldf("select * from mydata limit 10")
*
sign is used to select all the data available. As I said above, SQL commands aren’t case sensitive, so you can write them in caps or small, but keep them consistent. To select some variables instead of all, we’ll write:
> sqldf("select year, sex, name from mydata limit 10")
To rename a column in the output table, we can use the AS
command:
> sqldf("select year,sex as 'Gender' from mydata limit 10")
Let’s filter tables based on conditions. Since we are exploring SQL commands, we’ll be trying different combinations of filtering. Don’t get swayed. Concentrate on understanding how these commands manipulate data sets:
#filtering data
> sqldf("select year,name, sex as 'Gender' from mydata where sex == 'F' limit 20")
> sqldf("select * from mydata where prop > 0.05 limit 20")
> sqldf("select * from mydata where sex != 'F' ")
> sqldf("select year,name,4*prop as 'final_prop' from mydata where prop <= 0.40 limit 10")
As you would have noticed, we can perform arithmetic operations with the columns selected using the select
command. How about ordering the data and understanding the layout of data?
> sqldf("select * from mydata order by year desc limit 20") #order by 1 condition
> sqldf("select * from mydata order by year desc,n desc limit 20") #order by 2 conditions
> sqldf("select * from mydata order by name limit 20") #order alphabetically
We can order the table using one or multiple criteria as shown above. I’ve used the limit
command often to ensure the quick execution of queries. Let’s work with strings now. Many a time, we are required to filter data based on name patterns, i.e., name starting with man, Ben, etc. It’s quite easy to write such queries in SQL:
> sqldf("select * from mydata where name like 'Ben%' ") #name starts with Ben
> sqldf("select * from mydata where name like '%man'limit 30") #name ends with man
> sqldf("select * from mydata where name like '%man%' ") #name must contain man
> sqldf("select * from mydata where name in ('Coleman','Benjamin','Bennie')") #using IN
> sqldf("select * from mydata where year between 2000 and 2014") #using BETWEEN
Let’s proceed. Now, we’ll learn to apply multiple filters using logical operators. I’ve complicated some queries to demonstrate how these different commands complement each other.
#multiple filters - and,or,not
> sqldf("select * from mydata where year >= 1980 and prop <0.5")
> sqldf("select * from mydata where year >= 1980 and prop <0.5 order by prop desc")
> sqldf("select * from mydata where name != '%man%' or year > 2000") #no man in name
> sqldf("select * from mydata where prop > 0.07 and year not between 2000 and 2014")
> sqldf("select * from mydata where n > 10000 order by name desc ")
Now, we are familiar with basic data selection functions. Let’s look at data aggregation commands and calculate summary statistics. You’ve already used count
above.
#basic aggregations
> sqldf("select sum(n) as 'Total_Count' from mydata")
> sqldf("select min(n), max(n) from mydata")
> sqldf("select year,avg(n) as 'Average' from mydata group by year order by Average desc") #average by year
> sqldf("select year,count(*) as count from mydata group by year limit 100") #count by year
> sqldf("select year,n,count(*) as 'my_count' from mydata where n > 10000 group by year order by my_count desc limit 100") #multiple filters
As an interesting fact, where
command doesn’t work on aggregated columns, we use having
columns.
#use having
> sqldf("select year,name,sum(n) as 'my_sum' from mydata group by year having my_sum > 10000 order by my_sum desc limit 100")
Similarly, we can calculate unique counts for a variable using distinct
:
#unique count for a variable
> sqldf("select count(distinct name) as 'count_names' from mydata ")
As learned above, SQL also offers enough space to implement if/else rules. For a data set, such rules can be used to create binary features as shown:
> sqldf("select year, n, case when year = '2014' then 'Young' else 'Old' end as 'young_or_old' from mydata limit 10")
> sqldf("select *, case when name != '%man%' then 'Not_a_man' when name = 'Ban%' then 'Born_with_Ban' else 'Un_Ban_Man' end as 'Name_Fun' from mydata")
Always remember, the number of variables returned in the table will depend on the number of variables specified after the select
command. If you write *
, you’ll get all the variables. Now, let’s look at data joining in SQL. For this exercise, we’ll use the crashes and roads data set, which is a dummy data set good enough to help us understand the following concepts. You can download the data here: crashes.csv and roads.csv.
A crucial thing to understand is that after the select
command, whichever table you specify first would become Table A and the other one becomes Table B as shown above. This time we’ll load the data set using sqldf read command:
> crash <- read.csv.sql("crashes.csv",sql = "select * from file")
> roads <- read.csv.sql("roads.csv",sql = "select * from file")
This is your exercise. Check both the data sets and find out which column is common in both files. That common column will be used as the key to joining these data sets.
#join the data sets
> sqldf("select * from crash join roads on crash.Road = roads.Road ")#inner join
> sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road") #left join
#joining while aggregation
> sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road order by 1")
> sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road where roads.Road != 'US-36' order by 1")
> sqldf("select Road, avg(roads.Length) as 'Avg_Length', avg(N_Crashes) as 'Avg_Crash' from roads join crash using (Road) group by Road")
Similarly, you can do right join as well. Though I feel that knowing left join is enough, since all right joins can be done as left joins too. We can join data on multiple keys also. As we just have one key, let’s create another:
#besides Road, adding Year variable as a key
> roads$Year <- crash$Year[1:5]
> sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road and crash.Year = roads.Year order by 1") #multiple keys
With multiple keys, most of the values turned out to be NA, since the keys combinations weren’t found. Finally, let’s look at some string commands in SQL. The string functions in sqldf package are implemented under different function names; i.e., you can’t use the left
command to extract characters from the left. Don’t worry because these commands are available in this package. To access the commands, use
> library(RSQLite)
> help("initExtension")
and check out the string functions. Let’s try out of these string operations:
#some string functions
> sqldf("select name,leftstr(name,3) as 'First_3' from mydata order by First_3 desc limit 100")
> sqldf("select name,reverse(name) as 'Rev_Name' from mydata limit 100")
> sqldf("select name,rightstr(name,3) as 'Back_3' from mydata order by First_3 desc limit 100")
This bring us to the end of this tutorial. From here, what should you do next ? If you have followed this tutorial, you might be prepared to enhance this newly learned knowledge. Now, I suggest you complete the SQL tutorial1 and tutorial2 hosted by Codecademy. They are absolutely free and interactive. It would further strengthen your knowledge of these commands and how data sets are manipulated using SQL.
The aim of this article was to help you get started writing queries in SQL using a blend of practical and theoretical explanations. Beyond these queries, SQL also allows you to write subqueries aka nested queries to execute multiple commands in one go. We shall learn about those in future tutorials.
As I said above, learning SQL will not only give you a fatter paycheck but also allow you to seek job profiles other than that of a data scientist. As I always say, SQL is easy to learn but difficult to master. Do practice enough.
In this article, we learned the basics of SQL. We learned about data selection, aggregation, and string manipulation commands in SQL. In addition, we also looked at the industry trend of SQL language to infer if that’s the programming language you will promise to learn in your new year resolution. So, will you?
If you get stuck with any query written above, do drop in your suggestions, questions, and feedback in comments below!
Introduction In today's dynamic workplaces, a strong HR department is no longer a luxury –…
Job task analysis is a crucial process for understanding the specific duties and skills required…
In today's competitive talent landscape, attracting top candidates requires going beyond traditional job board postings.…
Finding the perfect fit for your team can feel like searching for a unicorn. But…
Recruitment forms a strong foundation to build an effective team. However, do you know if…
Introduction Performance appraisal has seen a tremendous change over the years. It is no longer…