The UPDATE TABLE Statement in SQL : Data Science Code

Key Takeaways

hey welcome back everyone in this video we'll look at two other operations on entire tables so we'll look at first altering a table how do I add a column to a table if I later on decide that there's not enough columns in there and then we'll look at how to drop a table entirely so that it is no longer in the database it's gone including all video that was in it so first let's look at altering a table let's first look at what is living in our students table we have these seven students here let's say that at some point in the future we decide that but we want to also keep track of whether the student is in-state or out-of-state so right now we don't have that capability because we only have these four columns so how do we add a column to this database turns out it's pretty simple we just use this alter table statement put the name of the table you would like to alter the keyword add column put the name of the column you would like to add and the data type of the column you would like to add in this case since in-state is either true or false it can only take two values basically we're going to be using this bit 1 which basically acts like a true/false since you can only set bit 1 as 0 or 1 so we can use this in-state column to keep track of whether the student is an in-state or out-of-state student so if we do that and then we again select everything from students we see that we have this in-state column and since we haven't said what data to fill in to it it just sets at all as none or null in sequel until such time that you fill these values in and you can fill them in using the update statement in sequel which we looked at before now another way to alter a table it's not just that you can add a call and you can also rename the entire table so let's say that we have this table called students but for some reason we'd like to rename it to student data so we just use this alter statement alter table students rename to student data so we do that now if we try to select everything from students we get an error because it says there's no such table as students because we just renamed that table to student data if we instead do select everything from student data we get exactly the same table that we had up here now before I move on to dropping a table I do want to mention that some sequel environments will have support for different alter table operations like if you want to delete a column like let's say I no longer want the in-state column so sometimes you can do alter table students drop column in state and that'll work it doesn't work in this environment and it won't work in several environments so you have to use kind of creative workarounds like how would I use only what I have to get rid of this column if I had to do it workaround way I would probably create a new table which only has these four columns that I want to keep and then just copy the existing data into that new table and then drop my old table and then rename my new table as my old stable name so I know that sounds a little bit convoluted but sometimes you have to do it that way and sometimes you'll get lucky and you'll be in a sequel environment where you can just drop columns and it'll be fine so make sure to check on that now the last part is this infamous drop table statement in sequel this statement gets a lot of attention even if you're not super into computer science just because it's used in sequel injection which we'll see in the next video but before talking about sequel injection here's the basic way to drop a table let's say you no longer want student data so you just do drop table student data couldn't get simpler than that we do that if we try to select everything from student data we cannot because it is gone so that's how you drop a table in sequel alright so next we'll look at sequel injection