Lesson video

Hello, and welcome to lesson three of databases and SQL.

Now, I'm Ben.

And in this lesson, you're going to look at some new SQL queries that.

We're going to build upon the experience that you got from lesson two, where we looked at some select queries.

So this lesson, we're going to look at insert, update and delete queries.

We're also going to explore data types in SQL as well.

Okay.

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

You'll also need the software that we loaded in lesson one and two, which is DB Browser for SQLite.

And you'll also need that database that we've been working on in lesson one and two, which is the music database, okay.

So once you've got all of that, and you can clear away any distractions that you might have, turn off your mobile phone.

And if you've got a nice quiet place to work, that would be perfect, okay.

And when you're ready, let's get started.

Okay.

So like we mentioned, in this lesson, we're going to explore SQL data types, and we're also going to use SQL to insert, update and to delete some data.

So let's start off by looking at some data types.

Okay.

Now you may have heard the term data types before, because fields in a database, as with variables in a programming language, have data types associated with them.

So if you've had any programming experience before, you'd probably be familiar with this term, data types, or the concepts of what they are.

Now, data types specify the type of value the field can hold.

Now, we talked about before as well the popular varieties of SQL.

So the one that we've be looking at is SQLite.

We've been working with SQLite, but there's also another popular one, which is MySQL, and they use slightly different data types.

So let's explore that a little bit further.

So MySQL examples would be.

If you wanted a field to be an integer, you would use the three letters, I-N-T, so INT.

If you wanted there to be a floating point number, so a number with a decimal place, that we'd use a data type, FLOAT.

Now, VARCHAR.

VARCHAR is a string of a variable length.

And you would place.

You can see on the example there, we've got brackets and an X.

And so you would change the X with a number that you would set.

So you set it with a maximum field size.

So for example, if you were doing postcode, for example.

That's quite a good example.

Now, postcodes can be different lengths, but there can be a maximum of, I think, seven, okay.

So you'd set seven in there to make sure none goes over that number.

And we can't use more than seven characters worth of data to allocate that field, but it may well be that it's shorter than that.

And that therefore would allow that, okay.

Now, we have another one which is DATE.

So if we wanted somebody to enter in a date, we'd DATE, and it needs to be in a specific format.

You can manipulate it so you can put it in different formats.

But the default format is year, month, and day.

And then we also have TIME as well.

So if you wanted a time, you'd use TIME, and that should be an hour, minute and seconds format.

Okay.

So that's MySQL.

Now, what we've working with is SQLite.

Now, SQLite uses slightly different data types.

You can see there's not as many.

So if we wanted to use an integer, we would use the word INTEGER.

If we wanted use a floating point number, we use the word REAL.

And then if we wanted a string of a variable length, we use the word TEXT.

And we don't need to specify the maximum size there.

We just put in the word TEXT.

Okay.

So your first activity for this lesson is to head over to your worksheet, and complete task one.

And that's where you're going to select an appropriate data type, sorry, for each field in a patient table for a doctor's surgery.

So the activity requires you to select the appropriate MySQL and SQL data type for each field.

So if you can pause the video now, head over to task one, complete task one, and we'll go through the answers once you've done that.

Okay.

So how did you get on with that? Now, you can see that with your worksheet, we had some fields from a patient table.

So with the scenario that it's a doctor's surgery.

So we have PatientID, Firstname, Surname, Date_of_birth and Height measured in centimetres.

So which data type did you pick for each one? Okay.

So PatientID.

If you're using MySQL, you would have used INT.

And if it was SQLite, you would have used INTEGER.

Because remember we.

From the previous examples that we use so far, the patient or anything with an ID tends to be a whole number.

So we would use INT if it's MySQL, and INTEGER if it was a SQLite.

So what about Firstname? Now, if you're using MySQL, you would have used VARCHAR.

And if you are using SQLite, you're using a TEXT.

So remember with SQLite, you needed to put a number inside those brackets.

Now, don't worry.

If you didn't put 20, that doesn't matter.

As long as you put in a number that was reasonably sensible.

So I was trying to think of a long first name.

And I think there can be some really long first names, but not overly long.

So I'm going to go for a maximum field size of 20 for that.

But like I said, you might've come up with a different number there.

But SQLite needed just TEXT.

We didn't need to specify the length.

So the same with Surname.

They're exactly the same, but the difference being that I've changed that number to 30, because some surnames can be quite long.

We've kind of double barrel surnames.

So therefore I think 30 would be about appropriate for that.

But like I said, if you put a slightly smaller number or larger number, that doesn't necessarily mean you were wrong.

So what did you put for Date_of_birth? Okay.

Now, the SQL data type would have been DATE, but the SQLite date type would have been TEXT, okay.

So we have a very nicely fitting one for MySQL, sorry, which is DATE.

But SQLite is just TEXT because Date_of_birth is not like a whole number.

And because we want the slashes in there and things like that, we will just have to use TEXT.

And then Height, we would use FLOAT because height can be in centimetres, but we have millimetres as well.

So it might be a decimal place that we need there.

So we have a float for MySQL, and REAL for SQLite.

Okay.

So hopefully you've got those answers.

And like I said, it doesn't really matter if the numbers in the VARCHAR are slightly different.

As long as they were sensible, and you'd thought about it.

Then let's now move to some new SQL queries.

So like I said before, we've done SELECT queries before, which is extracting data from a data.

Or searching a database and showing you the results.

So now what we're going to do is use some queries that actually manipulate the data that's already stored inside the database, okay.

So let's start off with the INSERT query.

So an SQL insert statement allows you to add data to pre-existing tables.

So this statement, if it go through it line by line.

Number one is INSERT INTO tblFriends.

So that's what we're doing, is we're specifying which table we intend to insert data into.

So you do need to put INSERT INTO.

They're kind of default keywords, and you can see the way I put them in capitals to make that obvious.

So INSERT INTO, and then you specify the table that you want to use.

The next line down, you can see which fields we're going to use.

So once we've said which table we're going to put it into, we need to then say what fields we want to insert the data into.

So I'm specifying firstname and surname.

And then the third line says, well, what values are we going to use? So again, I've used capital letter, VALUES 'cause that's one of my command words.

So which values are going to enter? So you can see I'm entering in Rachel, Green, Phoebe, Buffay and Chandler Bing, okay, because they're going to be entered into the firstname and surname.

Now, I've got a question for you.

And the question is, what SQL query could we run to check that this was successful? Because we might put this SQL statement into.

Sorry, type it out and execute this query.

But how are we going to actually know it's worked? Well, one thing we can do to check that is to do a type of query.

So see if you can think about what query we would use.

So hopefully, you've come up with a SELECT query.

So the one I would use, and this isn't the only way which you could've done it, but I'm going to.

SELECT * FROM tblFriends.

So what will the star do? Can you remember? Well, the star is my wildcard, which means select all.

So show me all fields from tblFriends.

So if I was to run that query, I should be able to see that those three new people have appeared into tblFriends.

Okay.

So that's a necessary query.

So let's now move on to an updating query.

So an SQL UPDATE statement allows you to amend existing records in a table.

So to update data in a table, you're required to specify the following.

So remember this isn't deleting anything.

It's not creating anything new.

It's amending a record that's already there, okay.

So the three lines of this statement are, number one, so the first one using that command word, UPDATE, capital letters, and then we're specifying which table we want to update, okay.

So tblEmployees is the example that I've got there.

Number two, the new value that you want to set.

So I've got, UPDATE tblEmployees.

Next line, SET.

So what do we want to set? What do we want to change? So set the field password to equal that value there, that string.

So we're changing what is currently on the password to that there.

But we don't want to do this for all records, because what we want to do is use that WHERE.

Now, you should be familiar with the WHERE command from lesson two.

So we're going to say, WHERE employeeid equals eight.

So what we're saying there is we're changing employee ID number eight's password to that new string.

Okay.

Now again, what type of query will we run to check that this was successful? Now, I'm going to give the answer to say select query.

But just pause this video for a moment to think, how would you construct that select query to show me that that one particular record has changed? Okay.

So this is the query that I came up with.

So let's see if you came up with the same.

Okay.

So I put, SELECT password FROM tblEmployees WHERE employeeid equals eight.

So that would then show me the password for that employee, And hopefully, it would match that string that I set it to earlier on.

Okay.

So that's one way in which I could have checked it.

And then the final query that we need to look at in this lesson is deleting data.

So the delete query, okay.

So an SQL delete statement allows you to remove existing records from a table, okay.

So to delete data from a table, you're required to specify the following.

So let's work through each line in this query that you can see there.

So number one is to say where you're going to delete it from.

So DELETE FROM, those are essential command words that we're going to use.

And then which table do we want to delete from.

So we're specifying the table.

So DELETE FROM tblStudents, and then all you need to do is add your condition.

So I'm going to delete from students table where date of birth is less than 31st, the eighth, 2002.

Okay.

So therefore, any date of birth it comes across where the record has the date of birth less than 31st, the eighth, 2002, it would then remove it from the table.

Okay.

So again, what SQL query could we run to check that this delete query run successfully and deleted the record that we wanted it to? Okay.

All record, Sorry.

Okay.

So what have you come up with? Well, it is obviously a select query again, but what's our criteria going to be? So this is what I came up with.

I put, SELECT date_of_birth FROM tblStudents WHERE date_of_birth is less than that date specified.

And if you run that query, hopefully it would return no results anymore, okay.

So therefore, we know that it's been successful if it doesn't produce any results.

Okay.

So what we're going to do is I'm going to have a go at demonstrating this to you.

We're going to have a look at an insert, update and delete query.

We're going to implement those together.

Well, I'm going to show.

You can do this alongside me if you'd like to.

Just watch and pause.

Or it might be better just to watch this and then have a go yourself.

Because there's a worksheet for you to complete after this demonstration that will walk you through some of these queries.

Okay.

So I'm just going to head over to my software now, and we'll have a go.

Okay.

So I've opened up DB Browser for SQLite.

And my first task is to open the database that we should have saved right back from lesson one, okay.

So I'm going to click on Open Database, and then I can select where I selected my database and click on it, and just click open, okay.

So now it's showing me the database structure and we haven't really made any changes to this so far.

All we did last lesson was use a select query to look at the data and to extract some of the data that's already held in there.

So in this lesson, we are going to look at changing some of the data.

So we might need to save our database afterwards, okay, if we want to keep those changes.

So we're going to look at creating the three types of queries that we looked at, so an insert and update and then delete query, okay.

So we need to go to Execute SQL, and let's start off by using that insert query, okay.

Now, if we're using the insert query, remember we needed to use the keyword which is.

Oh, key two words, sorry, INSERT INTO.

And I'm going to use capital letters to show that they're my command words, okay.

Now, I'm going to insert data into my Member's table.

So if I just start typing in tbl, it should show you that the table so you can carry on typing, or you can select down to it and hit Tab, and it will put it into the rest of the text for you.

Now I'm going to put a new line, what fields I'm going to use.

So I'm just going to put in firstname and then surname like this, okay.

So those are the fields that I'm going to insert data into.

And then I need to put the word VALUES and then open brackets, and put what values I'm going to use.

So I'm only going to enter one record.

In fact, let us just put in two, actually.

So I'm going to put in two values.

So I'm going to put in Nicole, which I put in there.

Nicole.

So that Member is a string, so I use my quotation marks, and then comma, and then my surname.

So it was Battle I was going to use, Nicole Battle.

Excellent.

Okay.

And there we go.

Now that should enter that one person's details in here.

Now, let's say we wanted to enter in two records, and that's absolutely fine.

You can do that, but you do need to close your brackets.

You put a comma in, and then we need to open our brackets again and put a new one in there, okay.

So I'm just going to put in my name.

So I'm going to put in Ben, then Garside.

I think I'm already in the database.

Actually, I'm not sure, but let's see what happens.

Okay.

Now, in theory, it shouldn't matter if I do already exist in the database because it should give me a new member ID anyway, because we did set.

In the way that the database is structured, the member ID, it was set to auto increment, which means that new records are added, then automatically the member ID will be included for me and will give me a new unique number, okay.

Now, can you remember, what do we always finish a query with? Hopefully, you're shouting out semi-colon.

And if you are, you were right.

If you didn't, you're wrong.

So we need to finish it with the semi colon like this, okay.

So in theory, this should put in the two records into my database.

So let's run and see what happens.

And there we go.

Now, it's not showing us any results because we didn't ask it to extract anything.

So that's absolutely fine.

But if we look at the bottom here, it says, the query executed successfully, and took six milliseconds with two rows were effective, which sounds great, okay.

So let's now test that this worked, okay.

So I'm going to change my INSERT.

I'm going to put in, SELECT.

Oh.

I'm going to put all, okay, From tblMembers.

But this time, I'm going to put in a, WHERE surname equals Battles.

We'll test this one out.

Okay.

I can use OR using all that.

Let's put this on new line.

Or surname equals Garside.

This will test to see whether or not there are two of me in the database.

So let's put a semi colon there.

Let's finish off.

Let's see.

And look, there was already a Ben Garside in the database.

But you'll notice because of that auto increment there, you can see 53, 57.

It's given me a unique ID, which proves my primary key point that we can have more than one person in the world with one name, and that's normally common.

But what's important, there is something unique in my Member's table about each one of them.

And this is that they have a different member ID.

So that's a perfect example.

But this has proved that it has actually worked in those two records, have been put in there.

Perfect.

So let's now move on to my update query, okay.

So now what I'm going to do is.

In fact, let's change my.

password.

I'm going to change my email address, okay.

So I changed my email address.

And so remember, my member ID 53, this one here.

So 53.

So let's.

UPDATE, and I'm going to put tblMembers.

Oops.

How to spell members, tblMembers.

And then what I'm going to do, I'm going to put.

New line, sorry, SET Email.

And then I'm going to set it to.

Let's put our new email address.

I'm going to put, bengarside@myemail.

co.

uk.

That's not my email address by the way, but I'm just showing you that it's going to update.

Okay.

Set email to that.

I'm going to put, WHERE.

I got MemberID equals 53.

Now it's not a string 53.

Remember, it's just.

I don't need to put the quotation marks around it, but I do need to finish it off with a semi colon.

Now it's really important that, in this case, I do use a member ID because if I put, WHERE surname equals Garside, it would, therefore.

They're are two instances of Garside, or anybody else with a surname Garside, then it would obviously change all their email addresses to the same, which is not what we want.

Okay.

So let's just check out that this works.

I'm going to run it.

Well, it looks like it works.

Well, at the bottom it says, query executed to two milliseconds and one row affected, which sounds good.

So let's test that this works, okay.

So I'm just going to keep that select in there.

Let's just get rid of this.

In fact.

Yeah, let's get rid of it.

So I'm going to keep the WHERE in, but I'm going to put, SELECT.

I'm going to put, surname and email.

And I'm going to put, FROM tblMembers, WHERE member ID equals 53.

So hopefully that will change it, and show my email address has changed.

Let's run it.

And there we go.

It's showing me that that was the email address that I changed it to.

So that has worked.

Perfect.

That's two out of three.

So let's move to the third and final query, which was delete, okay.

So I'm going to, actually.

There were two instances of me, so I'm going to get rid of one of me, okay.

I think my other member ID was 57, but let's just check that, okay.

Let's just check.

I'm going to put 57 in here, run this.

Yeah, it was me.

So 57.

So let's keep that in our mind 'cause I'm going to delete me, okay.

So get rid of all of this.

I'm going to put, DELETE.

Oop.

Ah, DELETE FROM tblMembers.

Sorry, that from should be in capital letters.

It is not essential.

It would work otherwise.

But I said the command words should be in capital letters.

So delete from members.

I'm going to put, WHERE MemberID equals 57; So that should get rid of the second version of me.

So let's run this.

Again, it looks like it's worked, but let's double check that.

So I'm just going to put, SELECT * FROM tblMembers WHERE MemberID.

Equals 57.

Now, this should return zero results because if I've deleted me, 57 shouldn't exist anymore, okay.

So let's just check that, and there we go.

So it hasn't come up with an error, which means that syntax wise, the SQL query is correct, but it showed me that it hasn't returned any results.

So just to actually triple check this, what we might do is just get rid of this.

Now we know it's 57.

So I'm just going to put, SELECT * FROM tblMembers, and just check that 57 no longer exists.

So I run that.

I'm going to scroll right down to the end, 55, 56.

And you can see that 57 no longer exists, so that we know that that has worked.

Perfect.

So that's my three queries that I have implemented, my insert, update and delete.

So let's head back over to the slides.

Okay.

So what I'd like you to do now is I'd like you to administer DB Music, Okay.

So your job to influence some of those different types of queries that we just learned about to administer the music database.

So I'd like to go to task two your worksheet, complete the exercises and provide the answers on the document, okay.

So you will need to open up the DBMS, so that's DB Browser for SQLite, like I just did.

And then you'll also need a copy of dbMusic.

db file that we used last lesson, okay.

So I'd like to pause the video, go through the exercises on task two.

And then when you've documented your answers, come back and we'll continue with the lesson.

Okay.

So hopefully, you enjoyed that and you enjoyed exploring the database and actually making some changes this time, rather than just extracting data and looking at it.

Now we've actually made some changes to that database.

Okay.

So your final task this lesson is to go to task three on your worksheet to complete another Parson's puzzle for the SQL code.

Now, remember that Parson's puzzle gives you lines of code.

They're all jumbled up.

So what you have to do is put them into the correct order to form a working segment of code.

So I would like you to head over the task three.

I'd like to pause this video, sorry, and have a go at it.

Now, remember it might be a good idea if you're not so sure, or even if you're confident, when you think you've got the answer, why not try and put that into your database and see whether or not it works, okay.

So pause the video, have a go at that.

And when you think you've got the answer, come back and we'll continue.

Okay.

So how did you get on with that? Were you able to reorder the lines of code to form a working segment, well, working SQL query? Okay.

So this is what I came up with.

We had, INSERT INTO tblDownloads.

We knew that would be the first line because INSERT INTO is that line that we would use.

And then the values that we've put into, which was two, five, 2020-09-01 and the time.

And also the 10, 19, 2020 and 17:46, okay.

So that was the correct answer.

Now you may be thinking that there's another line of code that I gave you, and this was this, which was another VALUES line, okay.

Now, how would we know which one was correct because we have two value lines? Now we don't ever use word VALUE twice in the statement.

So that should have maybe set some alarm bells ringing.

And if you tried it out, you would have noticed that.

So how do we know which one is correct? Well, we know the one.

If we're adding two values, then we always finish with a semi colon.

So whilst you could have put that line of code in there and it worked, you would have missed out the second value at the same.

I think the second value, sorry.

So you would have missed adding in that second two, five, 2020-09-02 and 14:32, okay.

So that should have given it away.

That we're adding two values and we don't add the words values twice and we always finish with a semi-colon.

okay.

So that's all for this lesson.

So in the previous three lessons, you've done a really great job, and you should be really proud of yourself because we've learned some fundamental concepts of databases.

We've also learned how to use a variety of different SQL statements to extract data, but also manipulate data inside a database.

So the next two lessons, we're going to be switching context slightly, away from a music database.

I'm going to be giving you a set of challenges in a different context, okay.

So I hope you're looking forward to that as much as I am.

And we would really love to see the work that you've done for this lessons.

So if you'd like to do that, please ask your parents or care to share your work on Instagram, Facebook, or Twitter, tagging @OakNational, and using the #LearnwithOak.

Okay.

So I'm looking forward to seeing you next lesson.