This module is the second part to the introduction to the SQLite database and the SQL database query language. We will discover how we can start analysing a real dataset using SQL.
- Understand how you can use SQL to do analysis
- Learn intermediate-level SQL queries
- SQLite – a simple, self-contained, serverless relational database. Comes preinstalled on Mac OSX, for Windows Download it here
- The enhetsreistered SQLite database – the Norwegian company register.
Let’s now go and do some data analysis using SQL. We’ll ask a series of questions and try to answer it using SQL – the queries we build will get more and more complex.
Opening the database
In your sql prompt type
- this will open the database.
If you have an older version of sqlite that does not have support for the
.opencommand, you need to quit sqlite (
.quit) and start sqlite with the name of the file you would like to open:
If you look which tables there are (with the command
.tables) there is one: ‘companies’ – look at the schema (with the command .schema companies) and see what fields are there.
How many companies do we deal with?
Next, let’s find out how many companies we have. In SQL language, our query will count the entries in a specific column.
tip: working with the ID column is most of the time a safe bet, as it should always be filled. Remember when we talked about “primary keys” earlier? In this example the ID column is our primary key.
SELECT count(id) FROM companies;
This will tell you we have roughly one million companies.
Which type of companies do we have?
In the data you’ll see a column called type – this is the type of the company that is registered. Let’s see which different types we have. We can do this with the SQL
SELECT distinct(type) FROM companies;
To find out how many types there are we can combine expressions such as count and distinct:
SELECT count(distinct(type)) FROM companies;
Now an you find out how many different cities we talk about?
Which are the most common company types?
The next thing we might ask ourselves is which companies are the most common? For this we will need to count the ids in each category. SQL does this with the GROUP BY statement:
SELECT type,count(id) FROM companies GROUP BY type;
Did you notice that we told the computer to select two different variables? The type and the count of numbers by type? You can specify multiple columns you want to get (and what operations you want to perform on them) if you separate them by commas.
Can we sort the results? – Yes:
SELECT type,count(id) AS count FROM companies GROUP BY type ORDER BY count DESC;
We introduced two new concepts here: Naming of the return column (
count(id) AS count) and the ordering of results (
ORDER BY). The ordering can be descending (
DESC) or ascending (
Now let’s get the top 5:
SELECT type,count(id) AS count FROM companies GROUP BY type ORDER BY count DESC LIMIT 5;
How many companies of each company type is in bankruptcy?
The Company database is nice and gives us information whether or not a company is in bankruptcy (the bankruptcy column). Let’s first look at how this is distributed.
SELECT bankruptcy,count(id) FROM companies GROUP BY bankruptcy;
We see that only about 3700 companies in the register are bankrupt.
Which types are those? We can add another GROUP BY statement.
SELECT type,bankruptcy,count(id) FROM companies GROUP BY type, bankruptcy;
This will give us the number of companies in bankruptcy (and those who are not) by category. If we only want to see one of those – we could of course filter.
SELECT type,count(id) FROM companies WHERE bankruptcy=’J’ GROUP BY type;
Where are the companies?
Let’s find out where the companies are – let’s find the top cities. We already know how to do this by finding the top types:
SELECT city, count(id) FROM companies GROUP BY city;
There are many cities with weird names, certainly because they were errors when entering the data in the database.
Let’s find the Top 10:
SELECT city, count(id) AS count FROM companies GROUP BY city ORDER BY COUNT DESC LIMIT 10;
Fantastic – now we can start doing statistics on how many companies are where!
Which City has the biggest bankruptcy rate?
Although bankruptcies are generally rare – let’s look at which city has the biggest bankruptcy rate…
First we’ll need to find the number of companies per city.
SELECT city, count(id) AS count FROM companies GROUP BY city;
Now let’s get complicated: we want the total of companies and the number of bankrupt ones, for the top20 cities with the most bankruptcies
SELECT companies.city, count(companies.id) AS total,bankrupt.count FROM companies LEFT OUTER JOIN ( SELECT city, count(id) AS count FROM companies WHERE bankruptcy='J' GROUP BY city) AS bankrupt ON bankrupt.city=companies.city GROUP BY companies.city ORDER BY count DESC LIMIT 20;
OK what happened here? We joined two queries. The one for total counts :
SELECT companies.city, count(companies.id) AS total,bankrupt.count FROM companies
and the one for bankrupt counts:
SELECT city, count(id) AS count FROM companies WHERE bankruptcy='J' GROUP BY city) AS BANKRUPT
We nested those two queries together by using the JOIN statement:
LEFT OUTER JOIN (...)
Because we needed to link the number of companies per city and the number of bankrupt companies per city, we suddenly had to use the column identifying the city twice, which wouldn’t work. So we artificially created a table corresponding to the number of bankruptcies per city, that we called “bankrupt’:
Now that we have a table
companies and a table
bankrupt, we tell the program which column to use for the linking:
So the column “city” of the table bankrupt is identical to the column “city” of the table companies, which allows for joining. This method explains why in the first line we wrote
Lastly, we ordered by the number of bankruptcies and looked at the top20:
ORDER BY count DESC LIMIT 20
Let’s now calculate the percentage.
SELECT companies.city, count(companies.id) AS total,bankrupt.count, 100.0*bankrupt.count/count(companies.id) AS percentage FROM companies LEFT OUTER JOIN ( SELECT city, count(id) AS count FROM companies WHERE bankruptcy='Y' GROUP BY city ) AS bankrupt ON bankrupt.city=companies.city GROUP BY companies.city ORDER BY count DESC LIMIT 20;
Here we simply added a new variable, which calculates the percentage of companies bankrupt:
100.0*bankrupt.count/count(companies.id) AS percentage
Nothing exceptional, just basic math. But notice the number
100.0: we added a decimal to make sure that the numbers are not rounded. If they were, all the cities under 1% bankruptcy rate would display 0.
To find the cities with the highest bankruptcy rate is a simple matter of changing
ORDER BY count DESC into
ORDER BY percentage DESC.
SQL is very powerful for data analysis.
You can train yourself using the website SQLZoo: http://sqlzoo.net/wiki/SQL_Tutorial or read more about it
How do you get the number of cities in the dataset used above? Fill the gap
SELECT _______ FROM companies;
- count(distinct(city)) X
How could we determine the average age in the dataset about people?
(fill out the gap)
SELECT _____ from people;
- sum(age)/(count(age)*1.0) X
Which of the following modes is not supported by sqlite? (for exporting data)
- SQL insert statements
- json X
How can you aggregate company counts by city? (fill the gap)
SELECT city,count(id) from companies ________;
- WHERE city = ‘Oslo’
- ORDER BY count ASC
- LIMIT 10
- GROUP BY city X