Hey guys.
Welcome to this lesson on querying data using Postgres.
Now in the last lesson we looked at how to read the data from our database, how to view our database.
In this lesson, we want to do a deeper dive into this process and see all of the different ways that
we can use that select keyword in SQL, and how we can query our data and search through our database
to find a specific piece of information that we want to use in our applications.
The data we're going to be using to practice our SQL skills is the total amount of food produced by
various countries in 2021, and the two foods that are focused on are rice and wheat.
And if you take a look in the current lesson, you'll see a file that's downloadable called World Food
Dot CSV.
Go ahead and download that file.
And what we're going to do is we're going to put this data into a Postgres database.
So we're going to create a table for the world food production data.
And if you take a look at this quick graph that I created in Excel, it's basically a bunch of different
countries, nine of them.
And we've got data on the amount of rice and the amount of wheat produced in million metric tons.
So it's a lot of food.
And we can see some of the big players like China, India, US.
And we can see also the breakdown of different types of grain that they produce.
For example, the US produces way more wheat than it does rice and India produces way more rice than
it does wheat and so on.
So this is the raw data that we're going to be working with.
And of course, it's kind of interesting to know which types of foods are produced in different countries.
The first exercise is a bit of revision.
I want you to use pgadmin and the CSV file that you just downloaded to create a new table in the database
that we already created previously, which was called world.
And inside that database you're going to create this new table called world Underscore food.
So you're going to have to write some SQL in order to create this table.
And you can take a look at the different specifications and the different columns that we're going to
need.
So we're going to need an ID that is going to be the primary key.
The country which is going to be character varying 45 in terms of data types.
The only one that we haven't seen so far is this one, which we're going to declare as a float.
And this is going to allow us to store numbers with decimal places after them.
Pause the video and see if you can remember how to do this inside Pgadmin.
All right.
So the first thing we're going to do is we're going to open up our query tool and we're going to write
our SQL.
So we're going to say create table.
And then the name of the table.
So we're going to call it world underscore food.
And then we open up a set of parentheses.
And inside here we can create some columns.
So the first one is going to be the ID which is going to be cereal and primary key.
The next one is going to be our country, and this is going to be a Varchar 45.
And that's just presuming that none of our countries are greater than 45 and character length.
And then we've got our rice production, which is going to be a float floating point number, and we've
got our wheat production, which is also going to be a floating point number.
So remember, no comma after the final column, close off the entire command with a semicolon.
And let's hit run.
And hopefully you'll see that query returns successfully.
So now if we refresh our tables we will see our table that is now called world food.
So if we try to view all the rows then we'll see a whole bunch of empty columns.
So now to import some data in we're going to right click on that table.
Click Import Export Data.
Select the location of the CSV that you downloaded just now.
In my case it's in my downloads folder.
And then making sure that you've got import selected and not export, making sure that in the options
you've got the header selected.
One important thing to notice before we hit import is if you open up the World Food CSV inside VS code,
you'll see that there is no column for the ID of each of these records.
Instead, we want Postgres to autogenerate the ID for our primary key.
When you're importing, make sure that in the columns to import, we cross out the ID because that one
we actually cannot import.
We don't have the data for it.
Instead we're relying on Postgres to autogenerate it.
So now click okay.
And that should all be successful if we go ahead and take a look at our data, you'll see that all of
the IDs were automatically generated from 1 to 9 because we set it to serial.
You can see that we've got nine countries in total, and we've got their rice and wheat production imported
into here.
So if you have a play Catan, the board game, then these are all very important pieces of data.
So now that we've added our data into a database and created that new table, we can take a look at
all the different ways that we can work with select to select or query different values from the table.
The first one you've already seen, which is to select everything from a table, and everything in SQL
is represented by an asterisks.
This means select all from a particular.
And then in this case we insert the name of our table.
The goal is to go ahead and select everything from the table.
And if you want to try this out, then you should click on the query tool and write your query in here.
See if you can get hold of all of the rows and columns from our table called World Food.
All right.
So it's pretty easy.
We use the select keyword the asterisks.
And then we say from and then the name of our table.
Now let's hit run.
And we can see all of the rows and columns.
That's pretty easy.
The next one is what if we wanted to select only one column?
We want to isolate a column from the table.
And the reason why you might want to do this is let's say you had a database where you had a table of
customers.
You want to get hold of every customer's phone number, so you can call each and every one of them up
wishing them Merry Christmas.
So then you might want to select the telephone column.
Right.
And the way you do this is you use the select keyword.
And then you insert the name of the column and then the name of your table.
In this exercise, I want you to select the country column from our World Foods table.
Pause the video and see if you can figure out how to get this to work.
All right.
So the code is pretty simple.
We insert the name of the column here and the name of the table here.
And then we end up with only that particular column being pulled up.
Now what if you want to select multi columns all you have to do is separate each of those columns with
a single comma.
Select as the keyword name of a column, name of another column separated by a comma, and then specifying
the table.
Try this out yourself.
In an exercise, select the country column and the wheat production for that country so we can drill
this down and get the data for how much wheat each of these countries produce.
All right.
So this is again relatively simple.
We use the select keyword.
And then we put in country comma wheat underscore production to match the names of the columns.
And then specify the table.
And we end up with all of the data collated as we wished.
What if you wanted to go a little bit deeper?
What if you wanted to provide some conditions to check against before we pull up our data?
So we don't just want all the columns or all of the rows, we actually want to check for a condition.
Well, in this case you're going to need the where keyword where it comes from SQL, and it's used at
the very end.
Usually we'll select a column from a table up to this part.
You're already pretty familiar.
Now the last part is just to filter or to check against a particular condition.
And the easiest condition to check against is the equals, which is represented by an equal sign.
Go ahead and use this formula to try and check for the rice production of the United States.
And if everything goes well, you should get a table with just one single row and one column that tells
you that the rice production is 8.7 million metric tons.
Pause the video.
Give that a go.
All right.
So a couple things to note here.
We need that equal sign in order to specify what to check against.
What.
In this case we're checking a particular column's values.
In this case the country column for a match that equals United States.
And it is included in these single quotes to represent a string in SQL.
And then we tell our SQL what we actually want from that entire row of data.
We only want its rice production.
So we specify rice production in the select part.
And at the end all going well, you end up with a result that pulls up a single value.
And this is useful when you imagine you're using a database that is backing up your web app.
So you wanted to check a user's balance in your app.
Maybe you have a calling application like Skype and you want to know, well, how much credit have they
got?
So then you would need to select the credit from the user payment table, maybe where a username is
equal to a particular value.
This really drills down deep into getting specific values that match a particular criteria similar to
equals.
You can of course use all of the wide array of comparisons.
So lesser than, greater than, greater than or equal, lesser than or equal, greater than or equal.
All of these conditions that you're already familiar with from basic programming are also valid here
in SQL.
Again, the formula looks exactly the same, but the condition is just a little bit different.
In this exercise, I want you to go into that world food table and find me all of the countries.
So we want to list all the countries where their production of wheat is over 20.
Now I'm saying 20 because that's the scale of the numbers that we've stored in our table.
But actually the units are of course million metric tons.
But all you want to check is what is the wheat production and find the ones where that number is greater
than 20.
Pause the video.
Give this a go.
If everything worked out, this is the table that should come up and we'll go through the solution together.
All right.
So first we selected on country.
That's the column we're interested in.
Then we specify the table.
And then in terms of the condition we're looking at wheat production greater than 20.
And we end up with this shortened and refined table.
You can go one step further for a loose match.
Sometimes you might want to match only a specific part of your data.
For example, if we had a country name United States, then this is a valid name for the US, but it
could also be called the United States of America, right?
These are both ways that this particular country could be stored in our database.
Now let's say that we don't actually remember which one we stored.
And we just want to search for the United States.
If it was this case and we used an equal, so we said something like where country equals United States,
then this is going to be a no match.
But we can use fuzzy logic using the like keyword to check whether if a pattern exists in the data.
What do I mean by this?
In SQL, we can use the percentage sign to represent a wild card.
If we were to check for United and then we use a double pipe.
So two straight lines.
If you can't get hold of these characters on your keyboard or you can't find them, then just Google
for the pipe symbol and you can copy and paste it from Google.
If we have to strings united double pipe and then a string that represents the percentage sign, what
the double pipe is going to do is it's going to merge the two of them when it checks it.
So it'll merge it into united percentage.
And this is telling SQL that we want to look for a pattern where it is united.
And then it could be anything.
It could be United States.
It could be United States of America.
It could be United Kingdom in this case.
So just be careful with using these patterns and you have to understand how it works.
What I want you to check is in our table.
Look inside the countries column and find all the countries that start off with a letter U.
That is Ukraine and that is United States.
Pause the video, see if you can apply this pattern into a where like SQL command.
All right.
This is the first time you've seen this.
Don't beat yourself up too much.
If you didn't manage to get it to work, the answer should look something like this.
First part easy.
Similar to before now.
Next part is our condition where the country column is similar to this particular pattern.
And that pattern is you as a string double pipe, which is a merge or a concatenate.
And what we want to merge is the you with the percentage sign.
So now when it checks in our table, it will check for all the countries that have a string that begins
with a U, and then it doesn't really care what happens afterwards.
Ukraine or United States.
If you didn't manage to do it this time, don't worry, I've got another opportunity for you to try
out where like now that you've seen it in action.
Now, in this case, we want to find all the countries that end in A.
The A is in lowercase, and you're going to apply what you learn about where and like to be able to
pull up all the countries from our table that end in a which should look something like this Australia,
China, Ethiopia and India.
Pause the video.
Give this a go.
All right.
Hopefully the second time it's a little bit easier.
The code looks something like this.
The condition is the country is like this particular pattern.
And this pattern has the percentage sign going at the front.
And then the combine operator or the double pipe and then the letter A.
And if you did this then what we end up with is this limited table.
Don't be confused by the double pipe, because in many modern programming languages it means or in this
case it means concatenate.
It means merge these two things together so that they become like this.
And you can, in fact, in this situation, replace this part with just this.
But as you'll see later on, when we start working with code in our back end, we're going to be using
a expression.
So we're going to be evaluating a particular variable or a particular programming expression.
And we want to insert that into the like structure.
You're going to default back into this situation.
So I just wanted you to be aware of how to use the double pipe in SQL to combine two things, or two
strings to concatenate them.