Data Representation

Computer Systems

Networks

Security

Impacts on society

Algorithms

Programming 1: Sequence

Programming 2: Selection

Programming 3: Iteration

Programming 4: Subroutines

Programming 5: Strings and lists

Programming 6: Dictionaries and datafiles

Databases and SQL

HTML

Object-oriented programming

Computing

Y11 Computing

Key Stage 4

Year 11

Computer Science

THEME

UNIT

In this lesson, we will explore the key terminology required to be able to use SQL to search and update a relational database. Together we will step through the key concepts using a music database as an example. We will complete the lesson by gaining some hands on experience of using a database management system.

Database essentials

In this lesson, we will use the music database from the previous lesson, 'Database essentials', to write our first SQL commands to access and manipulate the data.

SQL searches

In this lesson, we will explore INSERT, UPDATE, and DELETE queries, before being given the opportunity to implement these queries using our music database.

Insert, update, delete

In this lesson, we will use our SQL skills to build, interrogate, and update a database to manage swimming lessons.

Swim challenge (Part 1)

﻿Hello and welcome to lesson four of our databases and SQL unit. Now, I'm Ben and in this lesson is all to do with putting to practise all of the skills that you've developed in lessons one to three and putting them into practise by completing a set of tasks, using a different context and a different database to the one that we've been working on so far. So all you'll need for this lesson is your computer. You'll also need that DBMS that we've been working with so far, which was DB browser for SQL Lite. And there's also a downloadable resource for this lesson. That includes a zip folder that has inside it, a database that you can use to complete the tasks for this lesson. So once you've downloaded that and you ready, if you can clear any distractions that you might have, maybe turn off your mobile phone and if you've got a nice quiet place to work, that will be perfect. Okay. So when you're ready, let's get started. Okay. So as mentioned in this lesson, we'll bring together all the knowledge that you've gained on databases and SQL and interrogate and update an existing database. Okay. So let's start off with an activity where it remind you of some of those key database structure, concepts that we learned about right back in lesson one. Okay. So new contacts then. Okay. So our local leisure centres in the process of building a new swimming pool, now they have asked you to set up the database for them and help them manage their swimming lessons. So my first question is how would you make the links between the following three tables? Because there could be an argument that without the links or the relationships, this is a flat file database, but we've got data, data stored in three separate tables. So we need to make the links between them or the relationships between them. So how would you make those relationships? So if you pause the video now and think about what fields would you link between which tables. Okay, so hopefully you've got an answer now. So this is the answer that I came up with. So it is this. So what I've done is I've made a link between the course ID in the TBL courses to course ID in TBL lessons. And I've also made the link between the member ID in TBL members and the member ID in TBL lessons. Okay. So that's how I made the links. So hopefully you did something similar. So the next question is, what type of relationships have I displayed on these three tables? Now it might help you to remind you a couple of things. So first of all, this is crow's feet notation. So the question is what does that crow's foot actually notate and with the three lines. I'll also give you another clue that we're there. We discussed three separate types of relationships. So, in lesson one. So that was a, one-to-one a relationship a one to many relationship or many to many relationships. So which one do you think it is? Okay. So hopefully you shouting out something that's screened to me right now and you've got the right answers. So the answer is eight, a one to many relationship. Okay. So hopefully you got that. So just to remind you of what that means, there's an example here, where if we look at member and the member ID in the members table, a member can only appear in the member's table once. So that members ID will only ever appear in the member table once, however, that member can take part in many lessons. So the question... next question is which fields in below are considered to be primary keys? Okay. If you got an answer for me, so we should have hopefully course ID. So in the TBL courses, course ID was really a primary key in TBL lessons, it was lesson ID and the TBL members, it was member ID. So those are our three primary keys. So next question is, which fields below a considered foreign keys? have you got an answer for me? Save and shout out it out on three, two, one, three, two, one, they were course ID and member ID. Okay. And they are in TBL lessons because if you remember, we have a primary key in TBL member's is member ID. So that ID will only ever appear once in my TBL members, but because that member, can take part in multiple lessons, the member ID for the tables from TBL lessons can appear in many times, but therefore, it's a foreign key because it's referencing the primary key in another table. So this brings us onto our swim challenge. So I've kind of already set the scene for you. So let's just set the scene a little bit more. Okay. So a local leisure centre in the process of building a new swimming pool, and you've been asked to set up a database for them that will help them manage their swimming lessons. Now part of this database has been made for you. Okay? So this database has been made for you. You need to download as it comes with this lesson, you'll download a zip file. And once you extract that zip file, there's that database that you can work with. Okay. Now you need to work through the tasks on your worksheet that require you to write, execute, and test SQL scripts to complete the database. So you should use this lesson and part of next lesson to complete tasks, one to four. So spend roughly about 30 to 40 minutes in this lesson, knowing that you'll also have part of lesson five to complete these task. So don't worry about it. If you haven't finished all the task after you spent about 30 to 40 minutes, work on this, because it will be time allocated in lesson five to complete this. Now I'd like you to pause the video and like to go ahead and download that file. If you haven't done already, I'd like to open up a DB browser for SQL Lite. And remember then once you've done that open that new database and you'll be ready to start the tasks on your worksheet. Okay. Now the key to success here is number one, have fun. Okay. This is meant to be a nice way to kind of switch contexts, but use the skills that you already have developed over the last three lessons. There's nothing that's going to test you here that you haven't already covered. So be a great problem solver. If you can't remember how to do it, then that's absolutely perfectly normal. I fully expect you to go back to the work that you did in lessons one, two, and three, and remind yourself about some of those queries that you were, that you learned how to create. Okay. So once you've done all of that, so, like I say, spend about 30 to 40 minutes on it. Don't worry if you don't get up to task four because there will be next lesson. And I really hope that you enjoy it. So good luck. And I'll be here when you get back. Okay. So how did you get on with that? and that's all for this lessons. If you've completed task one to four, you've done an excellent job because not only have you completed the task, but you've also been a great problem solver by going through and remembering all those tasks that we've done in lesson one to three, and putting these skills into practise. So hopefully you feel a really nice sense of achievement with that. Okay. Now I would really love to see the work that you've done so far in this lesson. I'd really like to see the databases that you're starting to build and manipulate. That would be really fantastic. So 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 #LearnwithOak. So hopefully you're looking forward to lesson five, which is the last lesson in this unit where you continue with those tasks and complete your swim challenge database. Okay. So I'm looking forward to seeing you then, and I'll see you in less than five.

Swim challenge (Part 2)

﻿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.

Unit Overview: Databases and SQL

Lessons:

Database essentials

SQL searches

Insert, update, delete

Swim challenge (Part 1)

Swim challenge (Part 2)