[Music] hello friends subscribe welcome again learn sql the right way session 4 till session 3 we have already done a lot of hard work and i believe that you would be enjoying this playlist one thing is that you need to keep on practicing videos alone won't help i am repeating this multiple times because as a mentor i just do not want to give you content without any effectiveness i want to make sure that you practice that well so that you get the best out of it if you have not subscribed to my sessions do subscribe and let's start without any further delay thank you this is the session 4 in the sequel playlist that we have on youtube now in the previous session we have talked about few important things we have talked about what is the primary key we talked about what is a unique key and we talked about auto increment it is a very good candidate for a primary key which you have understood okay so having covered all of this let's quickly focus on few other aspects let's jump our focus on crud operations which we started with earlier and as you know that crud stands for create read let me increase the font create read update and delete this is what grad means create is actually insert we have seen quite extensively in the previous sessions the insert statements on how to insert the data read is basically to read the data using select commands update is to update the records which we will be seeing definitely in this session and delete is to delete the records so we have till now seen insert but now we have to see little bit on the select update and delete so let's focus on this now by this time you know how to create a table and by this time you know how to insert the records and i have a sample create table structure let me quickly explain this i think we have covered this but still let me quickly go through we want to create a table employee where id is the primary key and the type is integer type we have various other columns like first name middle name last name age right salvi and location all of this we have and by default location is bangalore if we do not provide it will consider as bangalore okay now middle name can be null because we have not given given the not null constraint and you pretty much understand this definition by this time so let me go to my goom ide and i will say show databases just to see what all databases i have trendypack is the database which i have created in previous session let me delete that and recreate just to make sure we start from beginning so drop database friendly tag okay drop now i will recreate it create database create database just a second create database trendy tag okay i have created a new database what i want is i want to create a table structure named employee first i should say use trendy tag that means i should be first connected to my database then i should create a table i said use trendytag it says database change let me give this definition and we have created the table if i want to see the structure bsc employee you can see this id is the primary key and for location we have a default value of bangalore and middle name can be null apart from that all other things have to be not null okay let me insert a few records insert into employee and i am giving the value for id first name last name i am skipping the middle name can i skip or not yes i can because by default the value is null and i am not giving the value for location because by default it will be treated as bangalore let me take all these three statements and run it so we have inserted three records now okay let me say select star from employee and we will be able to see the data you can see location as bangalore middle name as null fine so all our prep work is done now and let us see how to have variants of select this is a simple select where we are getting all the columns star means please get me all the columns so i will write here so we are talking about select it is nothing but reading the data so we talked about simple select selecting all columns and the command for this is select star from employee which you very well know okay now what if i just want to get first name and last name how to get only two columns i can say select first name comma last name from employee we will get two columns so this is pretty straightforward i don't think it would require much explanation you can give the column names whatever you want selecting specific columns okay now what if i want to get only a few rows in the previous case in this case we got a few columns only but what if out of three we want only two rows based on some condition based on some filter how do we do that let's say i want people whose age is greater than 29 how many records are there whose regist age is greater than 29 2 records couple and manish rajesh is less than 29 so we won't see how to run that select select star from star means give me all the columns start from employee we can give a where clause where h is greater than 29 and we can see that two records just have a look so we can give a filter clause like this let me write here or let me just copy this so select by applying a where clause where clause is like a filter filter condition select star from employee where is greater than 29 now i can say select star from employee where first name equal to manish that can also be done here first name equal to anish we can see the record we can give any condition whatever we want but do you see one thing here that in our table we have given manish m in capital and now when we are searching for manish where m is small the first letter is small then also it's picking up that means by default it is case insensitive it does not matter whether you give it in uppercase or lowercase i hope that you understood what i am trying to convey so let me just take this and copy here and by default by default it is case insensitive what if for some reason you want to make it case sensitive you can have a business use case definitely but what if you want that then what you need to do the same statement you have to run but you just have to use a keyword called as binary select star from employee where binary first name equal to manish in this case it will look or match the specific case and you can see it has not pulled out any records why because there is no data where the name is monition m is lowercase and if i give m capital here it will be able to retrieve the records i hope this is clear quite easy to understand that's why i'm going a bit fast okay so this will the above statement will match the exact case it is sensitive so this we have understood now for example you have to get the first name and last name but you want to name it as name and surname like that then how do you do so we can say select first name comma last name from employee in this case you can see it shows first name and last name but what we want to give it some other name alias how to do what we can do is we can say select first name as name whatever we want to give aaliyah's name and last name as surname from employee you can see it has changed here so if we want to have a alias we have to use the as keyword so yes use the s keyword i hope this is fine so we have seen how to get all columns select all columns using the star select specific columns we can mention or specify the column names we can give a where clause to filter the records by default it is case instantiative if we want to make it case sensitive we can use the binary keyword okay okay we can use the binary keyword and then we have checked alias so that the column names show differently fine with this kind of we have understood that how select works and it's nothing but the read operation in crud now with this let us talk about update update now consider a scenario so first of all let me say select star from employee okay now consider a scenario where uh let's say you want to update the last name of manish we understood that we by mistake entered as sharma but it is manish uh sinha let's say then how to do that we can run the update command how to run update and what is the table name employee update employee set that is a keyword again set last name equal to last name equal to what name we want to give sinha where first name equal to manish that means we are giving aware condition that means we do not want to update all records but we want to update a specific record where first name is manish where first name equal to manish okay let me run this semicolon it says this command is able to match one record we can see here and it changed one record which is true let me say select star from employee and we can see in this case manish sinha rather than mani sharma i hope this is clear now consider we understood that couple moved from bangalore to hyderabad and we want to change location for couple how to do update employee set location equal to hyderabad where first name equal to what was the first name we were talking about let me just see what was the first name we talked about couple let's say couple we will move from angular to hyderabad it says it is able to match one record and retain that let me run select command and we see trouble sharma has moved to hyderabad i hope this is clear now let's say what if i say update employee set location equal to hyderabad what will happen if i do not give aware clause then in that case it will do the update for the entire table right it will me it will match all the records in this case it will be able to match all the three records because there is no where clause and it will update two records because one record already has hyderabad so we can see rows mesh 3 change 2 because when we do not give aware clause it will meet or it will kind of get all records to update and in this case two records will be updated because third one is already done i hope this is clear now consider one more use case where let's say i want to increase the salary of each person by 5000 i want to increase the salary of each person by 5000 what i can do is update employee set salary equal to sally plus 5000 i'm not giving any weird class that means it will be applicable for all records done match 3 records 23 and if i say select star you will be able to see the salary is increased for all the employees i hope this is clear now what if let's say you want to you want to for example [Music] give a clause where in the where clause you give end condition that means you want to match two clauses for example set let's say i want to increase or change the location of monies to bangalore but i want to i might have multiple records with the name money so i want to go for the complete name so set location equal to bangalore sorry update employee thread location equal to bangalore where first name equal to manish and last name equal to and last name equal to okay i think uh this bangalore i have i mean let me just get error first and then i'll correct this single quote is missing in bangalore oops you can see rows match one change one so all what i wanted to say is that we can use this logical end or operators when we want to filter based on multiple conditions i hope this is clear so with this we kind of understood how update works we have talked about update commands okay now let's talk about delete delete so while doing the update you have to be very careful because if let's say you are weird clause or you are not filtering it properly you might end up doing changes to the records which you do not want so always a good practice is first run a select command with the same where clause filter and then once you feel that you are able to pull only the necessary records then you put the update command because you do not want to run into an issue where you end up updating unnecessary records which you do not want i hope this is clear now let's talking about delete so in your credit operation delete or d stands for delete and let's see how to delete a record so let me say first select star from employee what i want to do is i want to delete the record where employee id is 3 why because that person is let's say leaving the company so what we can do delete from employee here delete from employee where id equal to 3. just a second let me check what has happened delete from employee check the manual correspond i didn't give any space looks like okay okay from syntax issue delete from employee where id equal to 3 delete it now let me say select star from employee we won't be able to see the record now again when you are doing a delete make sure first you kind of check it with the select statement like it is pulling out the right record for you or not select star from employee where i d equal to three and then you see okay that record is only pulled out then you put delete because you do not want to run into an issue where you end up deleting something which you do not want now what if i say delete from employee and i am not giving aware clause what do you expect now in this case all the records will be deleted and it says two rows affected now when we say select star from employee what do you expect nothing of course because we have deleted all the records i hope this is clear so let me just put this command also here with this we are kind of done with the crud operations which are very important create read insert oh sorry create read update and delete grid operations now let me talk about alter command this is not a part of credit just to let you know so it's a separate topic in itself alter command for example now let me do one thing let me say besc employee i want to see the structure of employee table now i realized that this location or let's say this first name some of the people have very big first name and we want to increase the size of it right this can be one structural change where we are not dealing or changing the data we are not updating the data but we are updating the structure either the data type of it or we might be willing to add one more field to it the column such structural changes we have to do using alter command and update was to update the data actually because you might get confused then what is the difference between alter and update update deals with data manipulation author deals with structure manipulation right so altar is to alter the structure of the table when we say structure i mean to say schema schema of the table for example in this table i want to add another column named as job title how do i do that i can say alter table employee add column because i want to add a new column add column job title and what will be the uh data type it will be string so where care of let's say 20 20 might be less let's say where care of 50. let me take this and run okay now i expect that in my table definition i should be able to see job title column worker 50 which is here i hope this is clear so what have we done we have changed the structure of this table how using the alter command okay now what if i want to drop this column so let me go here copy this alter table employee drop column job title here data type of course is not required if i do this the column will be dropped altered table table name drop column block title done and let me say describe table you won't be able to see that column again okay now consider i want to increase the length of this first name by default it is 20 and we realize some of the people have first name which is greater than 20 characters for some instance then how to do that change alter table employee modify column because we are modifying a column rather than adding or dropping we want to modify existing column alter table table name modify column but which column we want to modify first name first name and what we want to make change where cap 30 earlier it was work at 20 you can see here we want to make it work at 30 now so let me just copy this and let me say describe table you can see now this data type has changed it has become vertcap 30 i hope this is clear now there is a primary key on id let's say for some reason you want to remove this primary key how do you drop this primary key this also can be done with alter command alter table employee top primary key as simple as that if i run this the primary key will be dropped let me right now it shows primary key let me run this and then show you the structure the primary key is dropped now we can even have duplicates in this now what if again i want to put a primary key on this let's say you have a table where there is no primary key and you realize you want to keep one column as the primary key how to do that alter table table name add primary key and you need to tell on which column you want let's say id we can see this command alter table table name at primary key on id column now if i show you the structure you will see this says primary key i hope this is pretty clear to you okay now you have understood the alter command we are at that stage where you should be able to understand making changes to data or schema these are two different things and that is where we have two things ddl and bml edl versus dml what is ddl might be if it sounds tough to you believe me it's not tough just listen and you will be able to understand what is ddl dds stands for data definition language data definition language and bml stands for data manipulation manipulation language data definition language and data manipulation language what are these ddo statements are the ones which change the table structure they do not manipulate the data only the definition definition means the schema or the structure it changes okay so basically ddl or data definition language consists of sql commands that can be used to define the database schema or alter it so this with people structure and data manipulation language here the commands deal with changing the data or deal with exact data so here we deal with the data directly okay so now in data manipulation language we have commands like insert update delete all this actually deals with data because we end up inserting a record updating a record deleting a record and things like that all of this are part of data manipulation language or called as dml commands when we talk about data definition create table alter table drop table all such things deal with the structure rather rather than data itself so these all are part of ddl commands i'm not saying these are the only commands there will be more but to differentiate i would give these examples ddl versus dml i hope this is clear just have a look at it you should understand what commands are ddl commands and what operations are dml operations so as i said create alter drop all these are dtl and insert update delete all these are dml now you understand i i talked about one thing that delete from employee what will this do this will delete all the records from the employee table what command is this is this a ddl command or dml does it deal with data or does it deal with structure this deals with data and it deletes the data that is why delete is nothing but a dml command because we are manipulating the data now same thing we can even do using truncate i won't talk much but i am giving you a rough idea there is a command called truncate for example let me see if there are some records inside this select star from employee now so let me quickly put some records inside this just give me a second okay now let me run the select command i have inserted few records if i want to remove all these three records which command should i use you will say we can use a delete command there is a delete command delete from employee and this will remove all the records from this table and this is a dml command because it's dealing with data it has something to do with the data rather than structure now alternatively what we can do is we can truncate this table we can say truncate table employee what this will do if i do a select star you can see it has removed all the records or truncated all the records so truncate also removes all records but deletes all records but this is a ddl command you might not be expecting this you might think that okay it's removing the records then how come it has something to do with the structure but not with data i'm saying it's a ddl command so you might be confused that okay what is happening when we are doing a delete and what is happening when we are doing a truncate because the results that you see are the same there is no difference in the result for you what you see is that even delete is removing all the cards truncate is also removing all records then what is so different about it let me give you a rough idea on this when you do delete which is a dml operation it deletes the records individually one by one so it has something to do with the data itself but when we do truncate truncate actually drops the table and recreate the table so truncate internally drops the table and recreates it but do you feel it has something to do with data now yes of course and this is a much more efficient operation let's say you have billions of records in a table so rather if you want to remove all the records you would like to do a truncate rather than delete why because delete will be inefficient and will take a lot of time but truncate will be quick because all it has to do is drop the table and recreate it that is why truncate is a ddl command there will be other differences between delete and truncate but for now i will leave it at here and we will talk about other differences sometime later so all what i wanted to convey is delete is a dml command and truncate is a ddl command because of the way both command works eventually you see the same results but if you have a lot of records in a table and you want to delete all of them you should prefer truncate because it will be more efficient i hope this is clear so what all have we seen in this session we saw how to use select and select all columns using star select specific columns by specifying the column name list we saw how to get only few records by applying a where clause filter condition also we saw how to get alias using as keyword then after that we talked about update where we say how to set something based on setting condition we talked about delete where we saw how to delete a record based on setting condition and we talked about alter command where we are dealing or changing the table structure we are either adding a column dropping a column adding a primary key dropping a primary key or we are changing the data type anything of that sort it is something to deal with structure and then based on whatever we have learned we understood what dml is and what ddl is and we differentiated both and we concluded where we understood delete is a dml command and truncate is a ddl command because of the nature they work internally so this was quite a lengthy session informative session i believe and i hope you have liked the session so in the next session we will continue our discussion on further topics and i hope you liked it so friends i hope you have liked the session do mention in comments if you have any feedback i would incorporate that in order to make the sessions better if you have still not subscribed what are you waiting for do subscribe now [Music] you