- [Craig] In this video, we take a look at the use of SQL to search for data.
(uplifting piano jingle)
So, information in a database is stored in records, and we see one here.
Each record contains a number of fields,
and we can manipulate the data in these fields using a programming language.
Here is a simple Python program
which opens a connection to a database and finds a record within it.
Now, most of the code we've shown you here is well beyond the specification for GCSE, so don't worry.
What we're showing you here is simply an example
of how it would actually work in a specific programming language.
The part you do need to know about is highlighted here.
You'll see in green there's an SQL command to the database
which is sending the database an instruction.
It's saying select population from the world table where the field name matches the string Germany.
So you need to be aware of various SQL commands.
The three you need to know from the exam are select,
and that tells us which fields are to be returned,
and you can use an asterisk here to indicate all fields.
The command from,
which says which table of the database do we want to extract information from
because databases can have more than one table and each has their own unique name.
And finally, where,
which is some kind of condition we have to meet.
So I don't want to extract all records from a table,
only records that match a certain condition.
And we can also use a keyword, like, here with wild cards.
Now, this will all be made a lot simpler if we go through a few examples,
and you'll notice they all follow a very similar format.
We start with select.
So we're saying select population.
So we're going to be selecting the population field from the world table,
but only where the name field equals Albania.
And then down the bottom right, you can see the output.
So from this entire database,
regardless of how many records and fields it had,
it would return that one single value,
where name equals Albania and the population field.
So let's look at another example.
Here we've got select name, comma, continent, comma, area, comma,
population, comma, GDP, comma, capital.
So that's listing all the fields in the world table.
So select all the fields from the world table, but only where the name field equals Algeria.
So of course, what this is going to do is
return all those fields you've listed in the select clause to the screen
and we can see that in the bottom-right-hand corner.
Now, as mentioned briefly earlier,
because we want to select all the field names in the world table,
we can shorten that first line and say select asterisk,
which is the same as saying select all.
Okay, so let's look at one with a slightly more complex where clause.
So we start off the same, select star from the world table.
So select all fields from the world table.
Then we say where the name field is like,
and then we've opened double quotes and we've gone
capital A, percent sign, double quotes.
So that percent sign is a wild card.
It means any string in the name field which starts with a capital letter A
and then has any sequence of characters following it.
So currently, we're going to be returning fields that have Afghanistan, Albania,
anything that starts with an A.
And we've highlighted those in the table with a dotted box.
But then we add on the and clause.
So we don't just want to return all the rows or records that start with an A.
There's also a second criteria that has to be met.
So the population field has to have a value greater than,
and I think that number there is a million.
So we're now filtering,
we're filtering, we're sub-selecting.
We've already said select all the records that start with an A, but within that,
now only return the ones where the population field has a value greater than the one specified.
And then finally, knowing the selection of records to return,
we will say we want to order the result in ascending order of the name field.
And we can see the output down there at the bottom.
Now here's one that might confuse you at first.
It says select name from world
where population is greater than select population from world
where name equals Algeria.
But this isn't complicated, it's just using brackets.
And just like you would with a normal mathematical expression,
you do the bits in brackets first.
So let's break this down.
Let's ignore the outer select statement
and just focus on the inner one that's in brackets.
So that says select population from world where name equals Algeria.
Well, that's easy.
It's the figure 38,700,000.
And that's what that inner select statement would return.
We can effectively replace that inner select statement, therefore,
with that value.
And now just execute the outer select statement.
Select name from world where population is greater than 38,700,000.
And that just returns Argentina.
(uplifting piano jingle)