Lesson video

Hello and welcome to lesson five in what is our final lesson in our databases and SQL unit.

I'm Ben, and this lesson is all about continuing with your swim challenge tasks and challenges that you started in lesson four.

And put into practise all the things that we learned about in lessons one to three.

So, all you'll need for this lesson is your computer, a web browser and you'll also need your DB browser for SQLite, and the resources that you downloaded from lesson four.

That was your worksheet with the tasks on as well as the database that got you started.

So if you can download all of those things, or make sure they're ready.

And then once you've done that, do clear away any distractions that you might have.

Hopefully, you've got a nice quiet place to work, turn off your mobile phone, and when you're all set, let's get started.

Okay, so just like last lesson, this lesson is about completing the swim challenge but to do that, you need to bring together all of the knowledge that you've gained about databases and SQL to interrogate and update an existing database.

And that's the database that you started with last lesson, which was the swim challenge database.

So, to get us started and remind us of some key concepts that we might need to use in this lesson, I come up with an exercise for you to do.

So first of all, I'm going to give you a query for you to look at, so a SQL statement.

And I'd like you to describe the changes that the following query would make.

So if you look at this one, so it says UPDATE tblDownloads, SET Date to 25/12/2019, where Member ID equals 21.

So just take a look at that database on the right hand side there and see if you can just pause this video and see what change you think this query would make, if any.

Okay, so have you come up with the answer? What would it have changed? So let's have a look.

Well, it actually would have changed every time it sees the member ID number 21.

It would change the date to the 25/12/2019.

So the date that was previously there, it overwrites it with that new string that you've set, okay? So the next query, that's an UPDATE query.

So let's have a look at the next one then.

Now this one, so again, look at this data set that you've got.

So I'd like you to decide what this, what change this would make.

So, DELETE FROM tblDownloads, WHERE time is greater than 18:00.

Okay, so again, you might want to pause the video for a moment to see if you can look at this data and see what changes will be made.

Okay, so if you've got an answer for that, what would that DELETE query do? Well, hopefully you've worked out it would delete some data.

But the question is, which data would it delete? So it would have deleted these records here because both of these times are greater than 6:00.

So 6:22 and 8:33 are greater than 6:00.

Therefore, those two records would be removed from my database, okay.

So then finally, describe the changes the following query would make.

So we looked at two queries, this is the third one.

So this is INSERT INTO tblDownloads.

Member ID, Date, VALUES.

So can you guess what would that do? In fact, don't guess, you should know.

What do you think that's going to do? Okay well, hopefully you've worked out that it would create an additional record into my database.

But what data would it have entered? Well, we've told it that we only want to enter the values, Member ID and date and not a time.

So it should have entered this into the database.

So, it should have added a new Member ID 23, the date 18/04/2020, but left the time blank, because we didn't specify that we wanted a value in the time field.

Okay, so how do you get on with that? Now, if that was all really alien to you, then I recommend that you go back to lesson three of this unit, and just remind yourself of some of those key concepts.

But if that just kind of triggered your memory enough, then you should be good to go.

So just a reminder of the swim challenge that you started in lesson four that you're going to complete in this lesson.

So the context was, a local leisure centre is in the process of building a new swimming pool and they've asked you to help them set up a database that'll help them manage their swimming lessons.

Okay, so that was the context.

So all I need you to do now is work through the remaining tasks on your worksheet that you started in lesson four.

So go ahead, open that worksheet from lesson four, and continue the task.

So you'll need to write, execute, and test SQL scripts to complete the database.

So open that worksheet from lesson four.

Remember, you also need to open up your DB browser for SQLite and then open the database that you downloaded, again, in lesson four.

So really good luck with that.

Remember, the important thing with this is that you do have fun, but you also, you're not expected just to remember these things off the top of your head at this stage.

Remember, the more practise you get at this, the more these things will just feel a bit more natural.

But as we've only been doing this for a few hours now, a few lessons, then it's totally normal for you to go back to lesson two, lesson three, and remind yourself of some of those concepts that we covered in those lessons.

Okay, so good luck, enjoy that and when you finish the task, come back and we'll move on to the end of this lesson.

Okay, so how did you get on with that? Now, if you were able to complete tasks one to four, you've done an absolutely outstanding job.

And you should feel really confident that you know the key concepts of databases, whether it be the structure of databases.

We looked at data types, we looked at flat file databases versus relational databases.

We also looked and implemented a range of SQL statements, SELECT queries, INSERT, UPDATE, and DELETE queries.

You've done a fantastic job and you should be really proud of yourself.

Now, we would really love to see your completed swim challenge databases.

So if you can take some screenshots of it and you're willing to share that with us, that would be wonderful.

So please ask your parent or carer to share your work on Instagram, Facebook or Twitter, tagging @OakNational and using the #LearnwwithOak.

So I hope that you've enjoyed that.

That's all for me.

I'm Ben.

I'm looking forward to seeing you in some future units on Oak Academy.