In this video we take a look at the use of SQL to search for data. 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 actually works 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 wildcards.
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 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 asterisks 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 sort of an A, there's also a second criteria that has to be meet. So the population field has to have a value greater than and I think that number there is is a million.
So we're now filtering, we're filtering, we're sub-selecting. we've already said select all the records at start of 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. We can see the output down there on 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 387 00000. 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 387,000. And that just returns Argentina.