Lesson video

Hello, and welcome to lesson two of Databases and SQL.

I'm Ben and this lesson is all to do with SQL and specifically using SQL to complete some searches, to search for data that already exists in our database.

So all you'll need for this lesson, is your computer and a web browser.

You'll also need to use that software, that we downloaded in lesson one which was DB browser for SQLite and also have access to that database, that you started looking at in lesson one, with the music database.

If you can clear away any distractions that you might have, maybe turn off your mobile phone, and when you've done all of that and you are ready, let's get started.

So specifically in this lesson, we're going to describe the function of SQL, and we're going to use SQL to retrieve data that exists in our database.

Our first exercise I'd like to complete, is to go to task one on your worksheet, and label the database to fill in the missing blanks and that'll help you remind yourself of a bit of the key concepts that we learned about in lesson one.

So if you can pause this video, head over straight away to your worksheet, complete lesson one, sorry not lesson one, task one, sorry, on your worksheet and when you've do not, I'll be here when you get back.

How did you get on with that activity? Let's go through some of the answers, I'm just going to skip back a slide.

If we had a look at those boxes, what word should we have filled in.

with these boxes? So let's start off with this box at the top here.

So this should have been the keyword fields, because remember Track ID, Title, Artists and Genre are all fields, they are like our column headings as such.

The other one here, let's go to the bottom right on the side.

That's all of that data about that one track, that four us the feast R&B.

And what was the key word for that? That makes opaque, Can you say it for me? A record.

That was a record.

And then if we look at this one here on the left hand side we're also referring to those, ones that highlighted in the track ID column.

So one, two, three, four, what's specific about those ones or the track ID was the primary key.

So all the one, two, three, four track ID was a primary key and therefore, all of those have to be unique.

And then finally this one was meant to be, highlighting the whole table.

So the other keyword missing there was table.

The words we wanted to remind ourselves were, table, fields, record and primary key.

Let's get back to where we were.

So what exactly is SQL? Now SQL stands for Structured Query Language and it was a language used to communicate specifically with a database.

Now you can use SQL to manipulate databases and retrieve records.

For example, put it in like a real life context.

When you shop for items online, you'll be typically be searching a database that uses SQL.

So those search terms will be added to SQL statements and then relevant records, about what will be retrieved from the database.

For example, if you were searching a popular online store and you typed in the search bar for toy, then that word toy will be inputted into an SQL query.

So then produce the results of the toys.

Anything typed with, the keyword toy would then be displayed to you.

And you can see the SQL for that on the screen there, but we're going to explore that in just a moment.

Now SQL is widely used as a standard language that comes in many varieties.

So like I said, there are different varieties of SQL and the most common ones are my SQL or SQlite.

But the one that we're going to use for the remainder of this unit will be a SQlite.

Now SQL uses recognisable commands that are similar to the English language.

And we can see that on the screen.

So we know the word select and from and where.

So there are words that are similar to the English language and they help us depict or understand, what this query is trying to do.

So let's, explore exactly what this query is trying to do.

The example shown is a select query.

Now it is used to retrieve data from the database.

Now you can see the word select there is being used.

So what's been selected.

What is selecting the item price and description fields.

So it's finding those fields.

That's what it's going to return to us, but it's going to return those fields from the TBL product.

So from the products table where the criteria has been met.

So what it means is when we search for word toy, what is going to do, it's going to show us the item, price and description of anything from the TBL Products, from the product table, where the key word is toy.

So there'll be another field in TBL product called keyword.

And it's going to to search that field for toy.

Anything that's going to match it's going to return as the item, price and description, data about each one of the products.

So hopefully that makes sense.

What we're going to do is we're going to to go to our, music database now, and we're going to explore, exactly how to do some basical, simple select queries so you can have a go yourself.

I'm just going to head over now to my DB browser of SQlite and I'm going to load up my database.

So here's my database.

Now, there's nothing in it.

So I've just opened up the software.

So this is my, remember, this is my DBMS, my database management system of a software.

So DB browser for SQlite.

Now my first protocal is to open up that database that we started looking at last lesson.

I'm just going to click on open database here.

So now I'm going to to select DB music, which is what I was using.

And then once I've selected that I can then click open.

If you remember from last lesson, you can see we've got the tables here.

So I've got my download table, my members and my tracks.

And we explored that last lesson.

So you can click on browse data and have a look at some of the data that's in there.

But what we're learning about today is some SQL.

What we're going to do is click on this option here, which is some execute SQL.

Now, we've got three windows here.

So the first window is our kind of window, where we're going to write our SQL statements.

And then the window underneath, is where the results are going to be displayed and then some information below it there, which you'll see when we execute it.

Okay.

Now the first query we're going to write is a select query.

So I'm going to type in the word select.

Now it doesn't necessarily, well, it doesn't matter to my SQL environment, my DB browser for Sqlite, whether or not I use a selected lowercase or uppercase.

So it's not case sensitive.

However, it is a recognised standard to make sure that you do put these key words, such as select and from and where, in capital letters.

Now, that makes it more readable for you and anybody else that you're looking at your code.

So try and put any of those command words, in capital letters and although it will execute, even if you don't.

I'm just going to select something called, Oh.

Now this is a wild card that I'm going to put, which means find me all the data.

I'm going to put a star in here, which is shift and above the the eight on my keyboard.

I'm going to select all or also select star, which means all.

Then the next line, I'm going to put in from.

And yet again it actually doesn't matter if you put these on different lines or not.

But again from a readability perspective, it is very important that you put them on different lines.

So it makes it a lot easier for you to read and remember what exactly this code is doing.

Now, we selected all the fields, from.

Now we need to specify which table.

So I'm just going to put TBL Now, as soon as you start typing this in, my database management software, is now giving me the option, showing me what tables I have available.

So let's select tracks.

So I'm going to put TBL tracks.

Now the last line if you remember is where.

And in fact, we don't actually need to do that.

We can just do select or from.

Now anytime we finish an SQL query, you must finish it with a semi-colon like this.

And that shows it it's finished writing the quote.

let's just see what that does.

Let's run that query and see if it works.

I'm just going to run it by clicking the play button.

And there we go.

So we can see the results and you can see its return me, all the fields inside my TBL tracks.

And there we go.

We can scroll down, have a look, and it showing me that this bottom window that it's returned 60 results and proudly showing off it did it in 89 milliseconds.

And there we go, right.

So let's just change that slightly from all.

And let's be more specific.

Let's see if we can just get it to show me the title, for example, I'm just going to put in title and run that.

And now this time it should just show me the title and it does.

Rather than show me all the fields from the database, it shows me just title.

So let's just try a comma and it's going to also put in genres in there.

And it should now show me title and genre.

Now lets get it to change a little bit.

I'm going to add this new line, which is where, so get rid of my semi colon.

And I'm just going to put in where.

And I'm going to put genre, equals pop like that.

Now let's run this now and see what happens.

I have some problems with my database.

Did you spot what I did wrong? Now there might be a few things going through your mind, but I remember just telling you a minute ago that I should finish this off with a semi-colon like that.

So let's see if that's made any difference.

Now, it still hasn't made any difference.

So why do you think it's not made a difference? That was a problem.

Now if you think about it, if you think about some of your knowledge that you might have from any Python lessons that you might have done, then this is a text string.

So if a text string and not a field name, we need to make sure we use our speech marks.

So I'm just going to put in.

Oh, that's not speech.

I've put in speech mark there and a speech mark there.

And lets run it again.

And there we go.

Now its worked.

Because we're searching for a string.

We need to make sure we put it inside those quotation marks.

So now that has worked.

So it may well be that, although this works in SQlite, a double equals that you would use in a Python, you don't need to do that in SQL.

Although you can see it still work, you don't need to.

And if we want to find anything where it's just not pop, so exclude the results, pop.

What we can do is put these braces like that around it.

And it shows me anything with genre doesn't equal pop.

So that's a different way of doing it like that.

Its going to change it.

What I've shown you a demonstration about how we can do that.

So remember you need to go to the SQL browser here.

We need to use, our select and from where but I want you to have a go at doing this, but follow all the tasks and instructions on your worksheet, because that will guide you through this.

It's kind of things you need to type in and record what results that you're getting.

So let's head back over to the slides now.

What I'd like to do is go to task two on your worksheet to try out a variety of SQL searches.

You're going to learn how to, you're also going to learn on top of what I've just shown you.

You're going to learn how to sort and retrieve data as well.

So if you can pause the video now and have fun with this, work through the tasks that, and it'll explain what to do.

Just record your findings.

And when you've done all of that, I'll be here when you get back.

So hopefully you've completed all of those tasks and you're starting to get familiar with, some structure of SQL select queries.

Now, so far we've been searching for data that only comes from a single table at a time.

Now it might be more useful to a retrive data from more than one table at the same time.

For example, it might be useful to know, which members downloaded which tracks.

So if we're going to do that, we need to extra pieces of code, actually lines of code, sorry to make links between those tables.

So let's explore that in a little bit more detail.

Now, if we were going to do that, like I described, then you need to specify in that select row.

So which table the data is going to come from.

Which table the fields are going to come from.

So we can see here rather than doing select surname, select download.

What we need to do is say which table they're coming from.

So select TBL members.

surname.

And then also TBLDownloads.

downloadID.

So we're selecting two fields there that come from two separate tables.

We're specifying what those tables are going to be.

Now, if we're going to do that, what do you think the front line's going to be like? Well, the front line is going to be the same thing.

If we're selecting it for more than one table, we need to say or specify which tables they're coming from.

So TBLMember.

surname, TBLDownloads.

download.

We need to say that they come from those tables.

So from TBL members from TBL downloads.

Now when we're going to the where line, we need to say it how the table is going to be joined together.

So where TBLMembers.

memberID equals TBLDownloads.

MemberID.

Now just stop and think about that line for a second.

Why are we linking those two things? Now, if you remember that the TBLMembers.

MemberID, is the primary key from the members table and that links to member ID in the download table, which is a foreign key in the downloads table.

So that's how we made the links between the two tables.

And like we described in lesson one.

TBLMembers.

MemberID links to TBLDownloads.

MemberID.

So those two IDs are linking together because one's a primary key in the member's table and another one's the foreign key in the downloads table.

And then you can add a new line to say, well, specifically what we're going to search for.

So we're using the and operator here.

Where we made that link between the two tables and then, and TBLMembers.

Surname equals Bibi.

So question is, what do you think will be retrieved when we execute this code? So you might want to pause this, or you might follow through and already have a good idea of what it's going to do.

If you've got an answer for that, what do you think is going to do? So hopefully you've got answered that, it searched for all records that have the surname Bibi from the members table.

And show me what the download ID was.

Was the ID for the tracks that Sarah had downloaded.

Let's now go through a teacher demonstration and I'll show you a little bit more about how we construct these queries And what we can do with them as well.

I'm going to go ahead over to my DBL browser for Sqlite, with that data that the music database, sorry, loaded up.

Let's move over to that now.

This was an example query that we did for a previous task.

I'm just going to get rid of this now.

And I'm just going to to paste in the query that I've just explained to you how we made, so there's the showing me the members and the downloads from the members and downloads table.

And we're going to link the primary key and foreign keys together, And we're going to show us where the member ID equals one.

That's going to show me that.

And actually it was slightly different in the slide deck.

I'm just going to change how it was because it wasn't a member ID.

It was member surname.

Equals and it was Bibi, wasn't it? So let's just put that in.

Here we go.

And there we go.

So now it's now showing me the surname Bibi and member ID.

Well, we could have used member ID.

That's fine.

Or we could just do the surname.

It doesn't really matter, but either way, it's showing me a "Bibi" and the download ID.

Let's say we want to see, maybe some more useful information.

For example, we'd like also to show the track information as well, because we want to know a little bit more about what download ID 23 is, what track is it? What we need to do there is make another link between two tables.

We've got link between the members table and the downloads table.

Now, if you remember, there was also a link between the tracks table and the downloads table.

Now, if you think about how we did it between the members table and downloads, how are we going to do it for the track's table? Well, it's in the same way.

What we're going to do just, we're going to slot another and here and we're going to put TPL so TPL tracks.

Now, what we need to put in here is that primary key for the tracks table.

So what was the primary key for the tracks table, out of that list that you can see on the screen? What is the track ID.

I'm just going to pop in the track ID here and then we're going to put equals.

And then again, we're going to to link that to the foreign key in the downloads table.

So TBLDownloads.

and again, what's the foreign key.

Well, it's the same field name, only as a foreign key.

So it's track ID there.

Now I also, that's at the moment, is not going to make any difference.

So other than that, it's going to say, All right, you wanted to use a table, that we're not using it so that's a bit confusing.

Let's put in here.

We've got an error as you can see.

The error is, we're saying, well, we're linking these two tracks.

The table here, but we're not actually looking at that table.

We've said from TBL Members and TBL Downloads.

I also need to add in TBL tracks.

And hopefully that will work.

And it does.

But it's the same results.

Not doing anything new here.

So let's play around with the select now.

We've got TBLMembers.

Surname, TBLDownload.

DownloadID.

I'm just going to put in TBLTracks.

and what should we select.

Let's have the title? We might also have the artists.

Let's put that in as well, why not? So TBLTracks.

artists.

So now this should show me, hopefully, it should show me Bibi, it should show me the download ID, the track, the title and the artist.

Let's have a look.

And there we go.

Perfect.

Now actually, if you think about it, we can play around this even more.

We don't really need the, as though, if you're presenting this to somebody, who was just looking at the data, for example, let's say Sarah Bibi, had looked at her own results and wanted to know exactly what they've downloaded or history of what they've done loaded.

We don't necessarily need to show the the download ID.

So I'm just going to get rid of that 'cause we don't need it.

We need it later on.

We need it to show the link 'cause that link's important, but we don't need to display that result to the user.

And there we go and it changes it slightly.

We might also want to add the first name in for niceness.

So TBLMembers.

first name, And there we go.

So it's shared with me, some of the results, title and artist.

That's how to construct, and to play around with the query that we built together.

Your next task now, is to head over to task three on your worksheet and do the same thing again.

So follow the instructions.

It gives you a good idea about what you can type in and play around with it.

Have fun with it, really explore, how to construct these queries.

Don't be afraid to get the things wrong.

Just remember those key things that I showed you, in that demonstration.

Make sure you are adding in the extra table.

All the links have been made that you need to make.

And you can always head back to this video if you need a little bit of help with it as well.

Don't be afraid to make mistakes is what I'm trying to say.

This is an opportunity to really get used to it, and see what you can achieve using SQL query, select queries.

So pause the video and when you've done that, unpause the video and we'll continue.

So how did you get on with that? Now, if you've managed to do that, you've really been able to construct some quite complex, SQL select queries.

Which is really, really good.

So let's put your skills to the test.

What I'd like to do now is go to task four on your worksheet and complete a Parson's puzzle for the SQL code.

Now what a Parsons puzzle requires you to do, is to you get some code, there's all the correct code that you need, but it's jumbled up in the wrong order.

So your job is to put it in the correct order that would form a working segment of SQL code.

That if you were to execute, it would actually run.

I'd like to, again, pause the video now.

Head over to task four.

See if you can reorder the lines of code to make it a complete working set of code.

And when you think you've done that, unpause and we'll have a look at the answers, So hopefully you manage that and you know, it might have been a good idea if you didn't already, is once you thought you had the right order to go ahead and put that query right into your own database and see if it actually run.

So here's the correct answer.

So you should have had the select line, first of all.

It should have been the select.

And then the next line was the from line, where you select your three tables, then we have the where line and then two of the ands.

And remember you always finish it off with that semi-colon as well.

So semi-colon was a good indicator that, had to be the last line.

Hopefully the, you've got them in your mind now, a good structures or correct structure, sorry.

Is select from where and if you need to use an, and, that would also come next.

So hopefully you've enjoyed that.

And that's the end of this lesson.

So we'd really love to see some of the work that you've done and I hope you've enjoyed it too.

If you'd like to share with us the queries that you were able to produce, that'd be really great.

If you'd like to do that, please ask your parent or carer to share your work on Instagram, Facebook, or Twitter, tagging @OakNational and using the hashtag LearnwithOak.

So next lesson, we're going to look at some other type of SQL queries, such as insert, update and delete.

So that's the next set of queries we'll look out and hope you're looking forward to that as much as I am.

So I will see you then.