Interview 1-MySQL Interview Question With Solutions- Part 1

Key Takeaways

hello all my name is krishnak and welcome to my youtube channel so guys we are going to continue the discussion with respect to my sequel i've already uploaded nine tutorials where we have seen some key concepts like key topics like joins like stored procedures views you know indexing and many more how to create a primary key foreign key and many more guys now what i was actually thinking is that to improvise the content it is better that we try to solve interview questions and when we are solving the interview questions remember one thing is that what we are going to do we are also going to learn some more in depth if a new topic comes over there we are going to learn in that specific way so um from now onwards inside this particular playlist i will be taking up more amazing important sequel interview questions specifically that are coming in interviews that may be very very much handful for you all you know so we'll go in that specific way who all can actually practice it we will go with respect to different kind of roles like data analyst and data scientist along with that business intelligence developer also right so let's go ahead and let's solve our first interview question and again this will be in many parts guys because see whenever we are trying to solve it we will definitely i'll definitely write the questions over here and apart from that i'll also give you the solution so the first thing that we are probably going to do and this was also asked uh in to one of my subscriber you know basically what we do is that whenever our people clear like our subscribers usually clear the interviews in a specific company we basically ask them okay what were the kind of questions that they have actually asked you and from that i will try to design this particular interview questions for you so first of all what is the first problem statement this first problem statement is with respect to a employee database okay so in this employee database we will be having lot of tables so first table that we are going to have is basically the employee table okay so you have to first of all create a database and this database can be a company database i'll not say employee database but at least a company database so this is what you have to do so first create an employee table create features like employee id okay first name so this is the first task guys okay first name last name then date before that i'll also try to add salary and then joining date and one more feature that is department for which department you are actually hired okay so this is your first task the first task is basically to create a employee table and then create all these things features remember employee id should be int first name can be a varchar okay last name can be a var care salary will obviously be end or float okay i will i'll suggest make it as end and this should be date time and this should be where care okay so this is your first task again i will be solving it in front of you so you don't have to worry about anything then let's go to the next step in the next step create another table in this i will say that i want to basically provide yearly bonus to a employee so i should definitely have some kind of bonus table inside this bonus table i will basically have two things one is employee id and this will be the reference key of employee table then your will be the bonus date okay and then the bonus amount okay so this three features will be basically in this particular table remember employee id is the foreign key from this specific table that is bonus table now similarly i will go ahead and create the third table or which is basically third table which is basically the employee designation okay so employee designation now in this you need to create three features again in this we are going to create employee id again which will be a foreign key then you will basically have the designation and the designation date from which date this particular person is basically having this specific position right now why i'm telling you to create this because we are designing a database let's say and i want all this specific information and after that i will tell you that okay we probably want some details from all these tables will you be able to do it and you have to specifically write a query so as said this sql interview question and solution this is the part one so in the part one this is the problem statement right now you have to basically create a database and then probably create an employee table bonus table uh employee designation and you have to map it this employee id is also foreign key so i hope you have understood the problem statement and it is very much simple because we have already done this now my suggestion will be that pause this video for some time try it for 10 to 15 minutes and then again start the video okay but here i'm going to solve it in front of you and also going to provide you the solution so let's go ahead so first of all what is the first step i need to create a database please see this and guys i'm not going to share you this script okay this script i want you all to practice see if i show you the script we'll just go ahead and execute so i will first of all create a database which i will basically say a company database so this is my first task right so i'll execute this so this has got executed now here if i probably refresh it here you will be able to see there will be a company database that will be created okay now i'm going to remove this i'm going to say use company to use this specific database that is clear now the next thing that i am actually going to create is basically my table so first table that i am going to create is my employee table so create employee table guys remember this is important because in the upcoming part two part three i will show you some complex queries you have to write i'll give you a problem statement you have to write this is what you get as a data analyst okay you get as a powerbear tablet developers in interviews so i'm going to create a table say create table employee okay create table employee and this in this i will just use capital e and i've already told you the feature name that i'm going to basically use it okay so let's go ahead and discuss about the feature name now the feature name that i'm actually first of all going to have is something called as employee id which will be in and we will keep this as not null okay and apart from that i will also want to make this as a primary key okay and i also want to use a feature which is called as auto increment so that i don't have to increment that particular value from which value it is started if i'm not giving anything then it will automatically get incremented okay so this is my employee id then i will start with my first name so my first name over here will be specifically where care with 25 characters so this is done and then i'm going to basically do last name which will be a varchar with 25 characters and then what i can actually do apart from first name last name i also need to write salary so salary will be an integer value let's say that i'm going to specify 15 15 figures is also very huge figure okay then i'm going to say my joining date and here i'm actually going to use date type okay i'll show you what will be the format of the date time okay don't worry about it and then finally i'm going to have my department name which will be a varcat okay so this is what i have actually done in this the most important information that i have done is that employee id will be your primary key it is in auto increment mode and then you are basically creating first name last name salary joining date perfect till here it has got executed uh integer display width is deprecated okay fine but anyhow i think it is got created refresh so this is my table here you have all the values okay now what i'm actually going to do this is done with respect to create employee table now i'm going to basically check check the schema we're getting some warnings that is properly fine so if i write dsc employee table if i execute it here you can see this is my entire information with respect to different different values and all the data types that has been created so check the schema we basically use dsc right so this table is done so we will go to the next table again understand which is the next table the bonus table it has employee id bonus date and bonus amount okay so here i'm actually going to create bonus table bonus table okay so i have actually created this bonus table now again for creating the bonus table i want you all to practice guys see to be a good sql developer you have to practice so this is my bonus here i basically have two things one is i'll say employee ref id underscore id okay now inside this ref underscore id remember one thing is that what it should be it should be integer but remember we are going to use it as a reference because this will be a foreign key okay and i have already shown you how to create a foreign key if you don't know don't worry i'll show you here also okay so then i will basically be having my bonus amount okay and in this this will be my integer again okay uh apart from bonus what we can actually actually do i've written over here that what should be my bonus date bonus date also from which on which day i've actually got the bonus okay so i will write bonus date and this will be date time right so everything is clear now till here i think yes bonus date time but now i need to make sure that i need to provide information about this foreign key which i want to refer it to this employee id in my employee table so for that what we are going to do we are basically going to write foreign key give my key name over here here i'm going to say employer reference underscore id and this will definitely refer so i'll write references employee which field it is nothing but m emp id okay so i hope we have done this okay and we will write one more statement which is called as on delete cascade that basically means suppose if i am deleting one specific row with a specific employee id over here automatically all the values will get deleted over here okay so this is the importance of on delete cascade now you may be thinking see i have taught you a new topic but here you will definitely be able to relate it so that is the reason i'm starting this interview series as we go ahead only this kind of videos will come in my sequel because i feel instead of teaching you topic by topic it is better that i teach you with some specific interview question an example so this is my uh bonus table so let me just execute it so this is got created so if you go and probably refresh these tables here you will be able to see bonus table also okay now let's go ahead and add some records to employee and all that also i will try to add it at the last okay now let's go and discuss about the third table employee designation employee id designation and employee designation date so i'm actually going to create create employee designation employee designation table and here i'm going to create a table create table and then i'm basically going to write designation again you can use capital letter it's not a problem at all whatever you want definitely go ahead and do it okay it is up to you so here the first thing again i'm going to use emp underscore ref underscore id again this will be an int okay and then let me go over here what about the next field that i have talked spoken about designation okay designation and designation date so my designation designation will be a var care and here i'm going to use 25 characters apart from the designation what i'm actually going to do i'm going to basically write designation date from which date okay this person is basically a manager or anything as such i'm writing manager over here so where can 25 oh no date is date time so i'm going to basically write date time okay done now the next thing i will write foreign key again this is basically a foreign key and i'm going to specify with respect to emp underscore ref underscore id okay and this will basically references employee and here i'm going to basically use employee id i hope i it's perfectly fine because employee id is my feature name okay over here so this basically means that i'm actually going to do this and again here one thing don't miss always please go ahead and write on delete cascade okay on delete cascade always understand that it will actually help you to delete whenever a main main value is basically deleted from here so i'm getting some kind of error over here let's say foreign key okay i don't have to write the comma last perfect so this is my another table and i'm going to execute it so this table has also get created so i have actually designed this particular table in this way if you are able to do this amazing okay now what i'm actually going to do i'm going to basically insert the records so guys now once we have created this specific table we really need to insert records three tables we have created one is employ uh the other one is bonus and the other one is designation so what i'm actually going to do is that now insert the records already you know how to insert and i don't want to waste much time so i've already written the script over here so this is what is my script to insert the employee values in the employee values you basically write this syntax and certain to apply values with all the details so here is your employee id here is crash first name last name your salary your all these values present over here okay now salaries i can also make this as an integer so let's make this as an integer because i have already told that salary will be an integer so i'm just going to make some changes here just remove the quotes so it will already or it will automatically become integers now with respect to the date because in date time you need to really understand how this is getting stored the first field is always the year so if i say christian sudanshu has joined in 1412 2014 12 11 that basically means they have joined in 2014 december 11th okay this you really need to understand so let's say sunshine krista joined in the same day in the development team i'm going to change for sanket let's say he has joined in 2015 12 12 on this particular time this person has also joined on 15 december 13th here let's say he has joined on 17th and here also he has joined on 17 okay so all these values are there i'm just going to execute it and probably this has got executed let's execute this select start from employee and here you can see all the values as i said 2014 chris is joined on 12th uh sorry 11th and december 12th is basically the month and 11th is basically the date okay so all these values are there now similarly i'm going to insert records for the bonus table in the bonus table also i have created the script but always remember you need to understand employee reference id is a reference key over here so you cannot create 0 0 0 7 over here right it will give you an error okay if you try to insert a record with zero zero seven because triple zero seven because that value is not present over there so here i'm actually saying okay this particular person got the hike in 2016 third and fourteenth this person got five thousand dollars hike over here then again chris got hike in probably let's say 2017 same date okay and probably uh again okay this zero zero three is not krish actually zero zero three is sankirt he got a hike over here okay let's say uh this again krish got a hike in 18 okay let's say he got a hike in eighteen or five thousand dollars again zero zero four he got 3500 hike on 2016 ninth third and 19th that basically means march 19th and zero zero five got seven thousand and this person zero zero one got again hike in 2020 let's say so these are all the records that i have uh and probably i can also add one more record you can add the record based on your side it's not a problem okay so this person also got the hike on the same day that is of 8 000 or i can also increase it to 8500 so that we have some varieties of data so once i execute this you can see that okay one more record is basically there i'll just remove this because i don't want it okay so let's execute it and here you will basically see you have an sql syntax error what is the error comma is missing fine let's do it okay so this in records are also there i'm just going to select select star from bonus so this is my bonus amount with respect to different different reference id okay now finally we'll also insert records into designation for this also my script is ready okay that basically means this indicates that when i'm inserting this value 0 0 1 0 0 2 0 0 probably 3 okay i'll just say this many are updated okay so here i'm basically going to say that 001 became manager on this specific date okay that is 2016. for this also if you want i will be able to write like this i will be able to write like this i will be able to zero zero to become an executive at this specific day and this person became an executive at this specific day okay oh 50 or i will try to use in this date format see before we use the date format like this here we are using it like this okay and let's see what all things can come cannot come with respect to this okay but again if you want to use this date format also it will work fine okay so let's try to execute this specific thing um incorrect date time of 50 is there sorry so this will be 5 to 2016 fine now it should work now this has got executed now if i go and see select start from designation okay so here you can see all the records great now if you are able to do this that basically means you have thought of designing a database with foreign key primary key now in the upcoming session we will try to execute many queries as such i will give you around 5 to 10 problem statement same way like how we discussed over here first of all you need to pause the video see the question try it with yourself if you are not able to get it then then try to see the solution so in the next part i'm going to solve lot of queries and questions in order to make you understand like what all questions can definitely come up in interview so thank you one dal i'll see you all in the next video bye bye