Big guys in this video, we will be looking at how we go about removing data from our database tables. So we have three tasks online, we want to delete a student with the ID value one, and this will be our first activity for this video. So the key word used for a delete statement is literally that word delete. And we say from our table students, and then we have to just like with our update, and just like with our select, in some cases specify the condition upon which something should be deleted. So in this case, our condition is that the students with the ID value one should be deleted. So we use our WHERE clause and we specify the column ID and the value that we're looking for.

So we want to delete from our students table where any ID value is equal to one and then considering and I'll just go ahead and select the top 1000 again, considering That our ID column is our unique identifier for any students, it means that only the record with the one in the space for the ID column will be deleted. No other record will be touched when we look for the record with the ID value being one, so we go ahead and execute this command and we will see that one rule gets affected and if we refresh our SELECT statements, we see that we no longer have a student with the ID value one and in the same way, if I try to execute this again, then we should see zero rows affected because there is no record with ID value being one. Next up, we want to remove the students by the name of the G McDermott's so we will say DELETE FROM or table students where and in this situation, we're going to have to use a more advanced WHERE clause here so we want to make sure that we're getting the student with the full Name this is still risky because you could have had to touch a McDermott's in your system.

And if you use this method, then everywhere that the first name is that j and the last name is not very much you would delete all of those records it would not be able to distinguish exactly which one you would want, which is why using the ID value and the ID column is the safest way to know which record you are targeting specifically. However, for example sake, we will go ahead and say first name is equal to and our value for j and literally and because now we're seeing the condition hinges on this being true and last name equals McDermott's. So here we see where we can have more than one parts to our condition a condition is not limited to one column and one valuable we can have and and you can also have for the bar would see if the first name is is equal to touch it and I want it or if you don't find that one and you find something with the last name equal not very much and I will also take it you can experiment with your where clauses and multiple conditions.

And I would suggest that you start experimenting with those on your SELECT statements as opposed to your delete statements. But for now, we want any students that has the first name being touchy and the last name being McDermott. So if we have a student with touchy and read then touchy read would not get heated. And because her name is not big and not derma, so we can execute this and we'll see that we will have one row affected because it went into the data set and looked for first name to G and last name Duramax, which was ID number seven. If we execute again, we see that she's no longer there also notes that there is no way to recover deleted records, so be very careful and deliberate when deleting them. Records there is no Recycle Bin that you can just go back to and recover.

And lastly, we want to delete all students with names containing the word students. So we'll just write another lead command DELETE FROM students, where and this time our condition will have to employ the use of a wildcard because we said the names containing the word students, which means student, student, one student seeks test students, all of those should be taken into consideration. So we'll say duty from students where and we can see first name, like, which we know is our wildcard expression. And then we see mandalas students and modelers. So once again, this is going to see look for any students where the first name has the word students in it. I don't care what comes before the word students and I don't care what comes after the word students, but then we said name we didn't spend If I whether it's the first name or the last name, so in this case, we just follow the instructions and we said name, so we have to see first name or last name like students.

So what this will do is see delete any students that might have a first name containing the word students or my taba last name containing the word students. So when we execute this, the expectation is that every record that we have that currently has the word students in it will be erased. So we can go ahead and do this. And we see that four rows were affected. So when we execute, you should see 1234 being removed from our resources. And there we go.

And we're down to five students and take note of the fact that the ID column it does not re assess itself and recall and say, Oh, this is no one and this is two and this is three, whatever value was assigned at the time of creation will be there going forward. So that's the end. This video once again, we went through some statements to delete some data and you can go ahead and experiment with different conditions and be very careful because if you see DELETE FROM students without our condition this will actually remove every single record from your table. So be very deliberate and very careful when executing these commands.