Jonathan Mann

Basic Querying with PostgreSQL

In the previous post, we looked at data normalization in PostgreSQL. In this tutorial, we’ll expand on our work to write queries that answer questions about the data.

This tutorial uses a database of famous people. I make no claims about the reliability of the data, however, the information in the database will be very useful for the purposes of this tutorial. The main table is called [person] which which depends on data in two ancillary tables, [occupation] and [nationality].

Before starting, please download and import this practice database into PostgreSQL.

Data Exploration

The first thing we should do is try to get a better picture of the data we will be working with. To do this, we can join the related tables and look at the first ten rows with the following query:

SELECT * FROM person p
INNER JOIN occupation o ON o.id= p.occupation_id
INNER JOIN nationality n ON n.id = p.nationality_id
ORDER BY estimated_iq_score desc
LIMIT 10

Because we know our data is highly normalized and complete, we can use an INNER JOIN without worrying about losing information. In many cases in the real world however, we would need to use a LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN depending on the situation. We will look at different JOIN types in another tutorial.

Here are the results of our query:

This looks interesting, but it looks like there are a lot of extra identifier columns that will not be useful to us for answer questions. Let’s cut them out and boil our query down to the essentials.

SELECT p.name,n.name,o.name,p.estimated_iq_score 
FROM person p
INNER JOIN occupation o ON o.id= p.occupation_id
INNER JOIN nationality n ON n.id = p.nationality_id
ORDER BY estimated_iq_score desc
LIMIT 10

Creating a View

Now that we have our query in a form that we can use it to answer questions, let’s go ahead and save it as a view since we’ll be referring to it frequently. Note that in creating a view each column will need a distinct name, so we will need to alias the nationality and occupation columns as shown below. Additionally, we can remove the ORDER BY clause from our view since it adds unnecessary processing time to our view.

CREATE VIEW famous_people AS
SELECT p.name,n.name nationality,o.name occupation,p.estimated_iq_score 
FROM person p
INNER JOIN occupation o ON o.id= p.occupation_id
INNER JOIN nationality n ON n.id = p.nationality_id

Answering Questions with SQL

Now that the view is created let’s answer some questions using SQL. Using the view name in place of the table name. Use the following syntax to construct your queries:

SELECT [column name] FROM [table name] WHERE [column name] = [value]

How many people in the group are from the USA?

Consider the following query:

SELECT * FROM famous_people  WHERE nationality = 'USA'

We could apply the query and count the result, but this would be impractical for larger data sets. Instead, let’s apply the count function.

SELECT count(*) FROM famous_people  WHERE nationality = 'USA'

This immediately returns the answer we are looking for: 31.

What is the sum of the estimated IQ scores of people from Germany in the dataset?

We could write a query to list the all the people from Germany and add up their estimates manually, but using the sum function on the estimated_iq_score column will bring us the result we are looking for: 2769.

SELECT sum(estimated_iq_score) FROM famous_people  WHERE nationality = 'Germany'

I almost didn’t want to include this question because we would almost never want to take the sum of IQ scores in real life unless we were trying to find an average (and there is already a function for average called avg), however, this is just for learning purposes and we don’t have any other columns that are summable.

What is the average estimated IQ score for politicians in this dataset?

144.4

SELECT avg(estimated_iq_score) FROM famous_people  WHERE occupation = 'politician'

Which occupation in this dataset has the second highest estimated IQ score on average?

For this question, we will need to write a query to group the results together by occupation and apply the averge aggregator to the estimated_iq_score column. We will want to alias the column so that we can use it for sorting.

SELECT occupation, avg(estimated_iq_score) e_avg_iq 
FROM famous_people
GROUP BY occupation
ORDER BY e_avg_iq DESC

Interestingly, the occupation with the second highest average estimated IQ score in our dataset is bouncing.

Conclusion

Congratulations! This completes the tutorial. You are well on your way to becoming a master of SQL.