Hey guys, in this lesson, we will be looking at how we can go about removing records from our database. Now this is basically the last letter in the word crud. And I think this is the first time I'm actually mentioning that word crud, but the lesson section would have been entitled CRUD operations. And crud is essentially an acronym for create, read, update, and delete. So we've looked at how we insert Records, which is create, we have looked at how we select these records, which is reading the data, we've looked at how we update or make changes. So that's the you and so now we look at how we remove or delete which is the D. Now much like the other commands in SQL, the Delete command starts off with the word that characterizes the command itself, which is delete.

So you see at this point that SQL really just looks like Standard English. They're just certain keywords and they're is a specific pattern which with each type of command, that if you just follow that pattern and know those keywords, you will have no problem actually writing these commands. Now the Delete command takes a bit more to it says DELETE FROM and then we specify a table. So in this case, we only have data in the students table, or at least I only have data in the students table. And then we say we're and so we notice that the Where is going to become far more prominent. So when we filter reading data or selecting data we use where when we want to update specific records we use where and when we want to delete it is even more important to use the weird because without the where it will actually just remove everything from the database meaning if you were to execute this right now, everything from the table sorry, if you were to execute this command right now as it is, this will just remove every single student from the students table.

Which is not what we want, we want to probably remove specific records from this table. So we have to specify our where, and then give a condition. Now let's quickly refresh ourselves with what is in the students table. And you would have slightly different data from me right now because you would have completed the update challenge from the previous lesson. And so you would have updated the enrollment dates and changed some of the values for first name and last name, but I'll just the principles will remain the same as long as you know how to get the ID, which is the best way to target a rule as opposed to using any other column. Now let's say I wanted to delete student test, Id student ID and test it.

Alright, so student ID test ID, which is student with ID nine, I want to remove that students. So I just say DELETE FROM students, where and then I specify My column and the actual column condition which would be equals nine. So I want where ID equals nine, delete that record, and it with a semicolon. And then once I execute, this will again just execute this entire script, then we see here that one row was affected. So if I refreshed my query to select all from the students table, then I see I no longer have a student with ID nine. Once again, if I leave this filter or this condition off, then it will remove every single thing from this table, which is not necessarily what you desire, at least not right now.

Of course, everything is done on a case by case basis. Now, what if I wanted to remove more than one rows and I only want to direct one command? So in the activity and the answer sheet that I would have uploaded with the update activity, you'd have seen where in the comments I included statements using a keyword in. So even if you didn't pay much attention to that, that's fine, we can look at using the keyword in right here. So if I wanted to remove the students from three to six with IDs three through six, so that means I want to remove 345, and six, all of those students and I'm writing one command to do that. Then instead of writing Well, I could write DELETE FROM and then this and then have a delete from statement for each of those ideas.

That will be fine. But then I can also use the keyword in and then in brackets or parentheses actually lists out all of the values that could be met or could possibly meet this condition evaluates to true. So in other words, I want to delete from students where Id, the value, Id can be found somewhere in a list of values and I did say I wanted 345 and six So this command will actually just say, look for all the records with an ID value that matches something in this list, essentially, that's what it's saying. And so all of these will come back, all of these will evaluate to true. And the expectation is that if I execute this, all of those rules will be deleted. And I'm going to execute it with just a theory.

And we see here that four rules have been affected. If I refresh my original query, then I see that I no longer have students with IDs, three through six. Now expanding on the capabilities of this condition, we can actually specify more than one columns in our condition. And here we see where we are seeing lead from students where and the conditions is first name must be equal to this value, and last name be equal to that value. And I left all my underscores here. But essentially what this is doing is evaluating To columns and only returning a matching condition when both columns meet this criteria.

So, in other words, if one if I find a j in a first name column, but her last name is read, then that this condition will not hold true because that means both first name and last name are not equal to the values that have been specified. So, the and the key word actually says both both condition and value both column and value pairs must be true. And that is when all of this evaluates to true. All right, in the same breath, if I said I wanted either or then I have the keyword or and this filtering these keywords and and or also apply to selecting so anytime you have a where clause, you can actually specify as many column and value pairs as you need to and you can always just join them as and meaning everything must match both sides or as many sides as there are most much for to evaluate to true.

And if you use an or means that any one of these could be true, so if I execute this and it finds finds a student with a first name j doesn't matter what the last name is, then it will delete it and if it finds somebody with the last name, Dermott, then it will delete it. Alright, so that's what the art does. So you have that. So in this case, where I want a student named Tajima dura mater, I have to make sure that both the first name and the last name match the specified conditions. Another example to look at is with using our wildcards. So the same way we can use the wild card when selecting we can use the wild card, once again, when we're deleting and even when we're updating.

The fact is that or WHERE clause can be used when we're selecting updating or deleting and essentially, it helps us to narrow down the exact conditions that need to be met, whenever we are about to execute. So in this scenario, we're actually using the or clause because I'm seeing delete all students with the names containing students. So that means the first name could probably contain students, or the last name could probably contain students. So literally, I'm seeing lead from students where the first name and we already established that the modelers means I don't care what comes before the word student or after the word students, but it contains the word students, or the last name can contain the words students. And that's essentially how that works. Now once again, workbench actually gives us an interface to help us to manipulate or edit or even remove the data that we are looking at when we run a query.

So the final step to this is how to remove our record without writing or needing to write a script. So if we look at This results grid, we can actually just highlight a column. And then we can remove. And I think we did something like this before, we can remove that row by just right clicking that row and clicking Delete rows. Or we can just click this icon here that says delete selected rows. So if you have to select multiple at once, and then you just delete selected rows, that will work.

So I'm just going to remove student one, test one, and I just right click and say delete rows. And of course we have to apply and applying will actually generate the code that matches the operation that we're carrying on. So you see here that they're very intent on putting in that condition to make sure that they're getting that right record. So I click Apply, and it would have executed successfully and so that students would no longer be among the lot. So essentially, that's how we go about deleting records from our database. I will include this script file, as part of this The resources for this lesson, and I encourage you to just practice find some data, insert it and try and delete it and look at many different ways to use up your conditions.

And if you need any assistance or guidance, feel free to hit me up via comments message or q&a.