Lesson video

Welcome to this Oak National Academy lesson, which is called Using Search Tools and is taken from the unit Flat-File Databases.

I'm delighted you've chosen to join me for this lesson today, so let's get started.

The outcome of today's lesson is that I can explain that tools can be used to select specific data.

We've got two keywords in this lesson.

So the first keyword is AND, and this is used in a search to find records that meet all the given criteria.

And the second keyword is OR.

So, OR is used in a search to find records that meet at least one of the given criteria.

So the keywords are AND and OR.

We've got two learning cycles we're gonna go through today.

So the first one is to search with single and combined criteria, so that's searching with AND, and the second one is search with OR and compare results.

So let's get started with that first learning cycle, which is search with single and combined criteria using AND.

This is an example of a record.

A record has multiple fields.

The fields in this record are Mini Beast, Picture, How many legs, Does it have wings, and Does it have a shell? So there's our five fields And fields are filled with various data types, like numbers or words, or even pictures in this case.

So our data types, we've got the word Butterfly, a picture of a butterfly, the number 6, and then Does it have wings, is checked, and Does it have a shell is not checked.

So have a look at this example of a record from a database.

What are the fields in this record? And what types of data can you see in the fields? Okay, so there's our fields.

We've got Country Name, Area, Population, Language, Flag and Currency.

And here's our values.

So we've got the country name, which is South Africa, that's the text field.

You've then got the area, which is a number field, the population, which is also another field.

Then we've got a text field for the language, a picture field for the flag, and another text field for the currency.

So there we have them: text, numeric, numeric, text, image, and text.

Look at these records.

Decide which people meet each question using the criteria given.

So we'll start with an example.

Which people did not walk to school.

So this time we're looking for, in the travel column, anyone that hasn't got walk as a value.

So that's Jacob and Laura.

So Jacob got the bus and Laura took the car.

So those two did not walk to school.

So there we see the answer Jacob and Laura.

Now try this one.

Which people are left-handed? Well done, you should have found that Aisha and Sam are the two left-handed people.

So we're looking at the handedness column and we're looking for anyone with the value left.

Which people had toast for breakfast? Well done again.

So this time we're looking at the breakfast column and anyone with the value toast.

So that gives us Sofia, Laura, and Sam.

This is very similar to how a search works within a database.

Searching select records that meet a certain criteria and that's what you've just been doing in those three examples.

So Sofia's noticed "You can search a database to put records that meet a certain criteria into a group." And Jacob says, "You can also sort the results of a search to show records in a certain order." So now you're gonna have a go for yourself.

So open the Titanic database.

So the link for that is oak.

link/titanic.

That's oak.

link/titanic.

And once you're in the Titanic database you will see some records which look a bit like this one.

So this record has the fields: Surname, First Name, Title, Age, Gender, Boarded, Survivor or Victim and Extra Information.

And you can see we've got a mixture of text and numeric fields in this record.

You can search a database for records that meet a specific criterion.

The search returns only those records that meet that criterion.

So for example, in the Titanic there were 22 passengers in the database who were 16.

So the criteria for that would be Age = 16.

And that would return 22 records.

So here's some of the records it would return and here's how you do it.

So to find out how many passengers aged 16 were on board the Titanic, you search for age = 16.

And this will create a group.

So there's our search, Age is equal to 16.

And you can see on the right hand side it says, record 1 to 20 of 22 records.

So we know there are 22 results for that search.

Now you're gonna try and do another search and this time you're going to use AND.

So this time, you'll find out which people meet both criteria.

So which person wore a jumper and ate cereal? So we're looking for yes in the jumper column and cereal in the breakfast column.

And that tells us that Jacob is the person who wore a jumper and ate cereal.

Let's have another example.

Which person is left-handed and ate cereal? So we're looking for cereal in the breakfast column and left-handedness in the handedness column.

So that gives us Aisha as the person who is left-handed and ate cereal.

Which people are right-handed and ate toast? So this gives a Sofia who's right-handed and had toast for breakfast and Laura, who's also right-handed and also had toast for breakfast.

So this time the search would return two results, Sofia and Laura.

The modifier aNd can be used in a search to make it more specific.

Using AND in a search finds only the records that meet both criteria.

To use the AND modifier in a search, select this box.

So we click on the box at the end and you choose the AND modifier.

This opens up a second set of search boxes you can use.

So it's a search for passengers who boarded the Titanic in Southampton.

You first select Boarded in the first box, then keep the equals sign in the second box, and third, select Southampton in the value box.

So I'll go through that together.

You select Boarded in the first box, keep the equals sign, and then select Southampton in the value box.

By using the AND modifier, the search has become more specific.

So we can say that a total of 12 records have the criteria age 16 and boarded in Southampton.

So 12 passengers were age 16 and boarded the Titanic in Southampton.

And we can only find that out by using the AND modifier.

So we're searching for age 16 and boarded in Southampton.

Open the Titanic database.

So the link there is oak.

link/titanic, that's oak.

link/titanic, and answer these questions.

So use the AND modifier to search for: How many male passengers were in first class? How many female passengers died? How many female passengers boarded in Belfast? And how many male passengers under 10 years old were on board? Okay, here's the answers you should have found.

So how many male passengers were in first class? That was 180.

How many female passengers died? That's 143.

How many female passengers boarded in Belfast? So that's zero.

No female passengers boarded in Belfast.

And how many male passengers under 10-years-old were on board? And that's 52.

So second question: What does criteria mean in a search? And the third question: Does an AND search give more or fewer results than a regular search? And why? So criteria means the rules or conditions that you use to search for something.

And an AND search will give fewer results because it only shows records that meet both criteria that you searched for.

Therefore it makes the search more precise.

Now we can move on to our second learning cycle, which is to search with OR and compare results.

Laura points out that "AND is a modifier that changes how a search works." Sam asks, "Are there other ways of changing how a search operates?" Let's see.

In a database, the modifier OR can be used in a search for one thing or another.

The results of an OR search will include all records that meet either of the criteria.

So there's two statements here, one from Sofia and one for Jacob.

One of them is right.

Which one? So I'll read them out.

Sofia says, "An AND search gives more results because you are using two criteria instead of one.

That means there should be more records that meet the criteria." And Jacob says, "An OR search gives more results 'cause it shows all the records that meet either of the two criteria." So who's right, Sofia or Jacob? Well done, Jacob is right.

Using AND reduces the number of results.

It narrows the search to records that meet all of the criteria.

So Jacob says, "An OR search gives more results because it shows all the records that meet either of the two criteria." So if you want more results, you'd use an OR search.

So this time, the questions will use OR, and we'll find out which people meet either of the criteria.

So which people wore a jumper or are left-handed? So we're looking at the second and third column.

So handedness, we've got two people who are left-handed, that's Aisha and Sam.

And we've got three people who wore a jumper.

So that's Sofia, Jacob, and Sam.

So which people wore a jumper or are left-handed? We've got Sofia, Aisha, Jacob, and Sam.

If we use AND in that search, we'd only have one result and that would've been Sam.

So you can see how OR returns us more results.

Which people are right-handed or ate toast? So this time we've got Sofia, Jacob, and Laura who are right-handed, and Sofia, Laura and Sam who ate toast.

So Sofia, Jacob, Laura and Sam are right-handed or ate toast.

Who travelled to school by walking or by car? So we're just looking at the travel column this time.

So we've got Sofia and Aisha and Sam who walked, and Laura who went by car.

So Sofia, Aisha, Laura and Sam got to school by walking or by car.

An OR search usually gives more results than an AND search.

An OR search includes everything that meets either condition.

An AND search filters down to only those that match both.

So there's the difference between an OR and an AND search.

So we're gonna return now to the Titanic database.

Laura wants to find something out.

She wants to find out all the passengers who had 1st or 2nd class tickets and she wants to include anyone who had either class of ticket.

Do you think the number of results from the OR search will be higher or lower than the number of people in first class only? Why do you think that? So to search for people who had 1st class tickets, select Class in the first box, keep the equals sign in the second box and select 1st in the value box.

So once again, select Class in the first box, keep the equals sign in the second box and select 1st in the value box.

So there's our selection, Class = 1st, and then we're choosing OR for the modifier.

And then for the second search criteria, we want people who had 2nd class tickets.

So Class in the first box once again, equals again, and this time, 2nd in the value box.

So we'll go through that again, Class, equals and 2nd.

And that gives us both our search criteria linked by an OR modifier.

So Class = 1st, OR class = 2nd, and that returns a total of 608 records.

So 608 records have Class = 1st OR Class = 2nd.

And we now know that 608 passengers had either 1st or 2nd class tickets.

So Sofia says, "The 1st class ticket search had 324 results." And then Jacob says, "The 1st OR 2nd class ticket search had a total of 608 results.

The OR search returned more results." Laura searched for Class = 1st AND Class = 2nd.

The result was no records.

Why? Is it A: because there were no 1st or 2nd class passengers? Was it B: The passengers boarded at Southampton? Or was it C: A passenger could not be in 1st and 2nd class at once? Well done, the answer is C.

So a passenger either had to be in 1st or 2nd class and couldn't be in both at the same time.

So that's why searching for 1st class and 2nd class would return no records.

So Task B, open the Titanic database.

So that's oak.

link/titanic.

And this time, using the OR modifier, search for following questions: How many passengers boarded at Belfast or Queenstown? How many of the passengers were under 18 or over 70? And who was the oldest passenger who boarded at Southampton or Cherbourg? Let's have a look at the answers.

So how many passengers boarded at Belfast or at Queenstown? That's 133.

How many of the passengers were under 18 or over 70? That's 188.

And who was the oldest passenger who boarded at Southampton or Cherbourg? And that was Mr. Johan Svensson who was aged 74.

So the second part of Task B is how could you use AND and OR modifiers when searching online or on a video streaming site? Write at least two sentences.

So here's what you might have written: I could use AND to find films that are funny and suitable for my age group.

So I only see the ones that match both things.

I could use OR to search for action or adventure films. So I get a longer list that includes both types.

Using AND and OR helps me find exactly what I'm looking for.

Here's a summary of today's lesson.

Searches in a database can use criteria to find specific records.

AND is used to narrow a search by showing only the records that meet all the selected criteria.

OR is used to widen a search by showing records that meet at least one of the selected criteria.

Using AND gives fewer results because it's more specific.

Using OR gives more results because it includes multiple possible criteria.

Thank you for choosing to join me today for this Oak Academy lesson.

I hope to see you again very soon.