to crack the interviews of TCS digital ninja Prime SQL Developer or any of the software engineer field you need to crack the SQL questions because this is a very hot topic that always asks to any of the candidate who is preparing for any of the technical roles so in this particular video I will tell you about the multiple types of pattern that they can ask you because 70 questions are curated with the previous interview experiences of all service based companies as well as a product based companies so that this video will be the one stop for you so please tie your belt and start your journey with me stay till the end and crack any of the technical interviews because all P you will learn in this video so hello everyone my name is a Mishra and welcome back to the prime coding Channel I put the content which will help you to prepare for your placements or any of the tech techical interview so please subscribe the channel so that you won't miss any of the update so without wasting time let's hop over the questions so yes let's see the top 70 questions which covers all patterns previous year questions and all topics are covered like where else many topics would you will see in this video so let's move forward with the very first video uh page yes now first question States how do you switch to the IMDb database so in this case I'm using an IMDb database that I have loaded in my SQL database in my dbms but I will highly encourage you to create your own schema and database then try to do that like instead in my case my my SQL table name as IMDb in your case it might be other so use that I will provide all this PDF so that it will be helpful for you to read and revise all this concept so yes how do you switch to the IMDb database so very first keyword you will learn is use here use is basically denotes which particular database that you want to point because in database there would be multiple multiple tables or multiple database would be exist in dbms system there would be multiple database would be existed so you need to choose which database you need to uh access it and perform your manipulation so in this case it is stating that please use IMDb okay so use the keyword to access any of the database okay now moving forward with the second question how do you list all the tables in the current database great so you need to list all the current tables that are present in the particular database that you have been using now in this case I'm using IMDb so how many tables are there so you need to write show tables guys I can run it also all these codes but like I will write it as use table uh use IMDb now and the other that we have is show tables as SQL is not a case sensitive so it will not create any issues but you need to be very careful about each and every topic now you see use IMDb has been implemented and you show tables you can see there are many tables and guys this IMDb uh data set has huge number of data points like 38,000 or 3 lakh I guess so it would be hard for me to provide this it because it's a gbo file but if I found because some I guess I have deleted but it is in my database so I've been using it but I will provide this link into my drive and all you can just have a look over it great great so let's move forward I won't run each and every query because it meant same it meaning is same you will create by yourself and how much practice you will do from your side that much problem solving skill you will attain because in interview they will tell you to create a database or write a code to how to create a table and just perform all the information perform all the manipulation that the interview will give you so please prepare for that take an example from here create your database create your tables create your schema and try to perform all these question just you need to replace the column names that has been existed in your database in my case all the tables value are been same so I'm using that particular case but you can also use that I will provide everything in the description so very first show is a keyword to use the to show the tables that are present in the database okay so it will presented great now how do you display the structure of the movies data movies table so structure means what all type of uh name is there columns are there and what is their preferences like if I tell you if I write here if I write here describe describe uh any movies let's say describe movies and see now you can see here movie table had fields of ID if it is not visible let me okay so it is not been increasing so ID would be the first field which is in type name is Vare year is int and rank score is float guys if you are wondering what is Vare and all I have created an particular Theory video of all dbms questions that has been asked in interviews previous year interviews please have a look over it extensively have covered all type of theory question that can be asked from you great so all this will be presented to you if you write describe it will describe whole movie tables that uh movie is a table though and all these are the fields that resides in movie great now let's move forward with an another question is how do you select all columns from the movie tables so how you can select it's simple you can you need you need to use select asri will take all the present values from the table from movies okay so from is another keyword which dedicated to each table that you are using in a database great so now how do you select the name and ear columns from the movie table how do you do that so as the question State select so you need to write select what name ear and from the movie table so just from movies and that would be your query try to understand the query which is given by the interviewer great the in this sequel the beauty of the question is everything is there in the question you just need to apply the keywords and your query is done and dusted so let's move to the very sixth question so sixth question State how do you select the rank score and name columns from the movie table okay I need to select rank score rank score is the same rank score is the same uh attribute and name okay columns from the movie table so from movie table and it would be your SQL query select rank score name from movies see how it is easy it is I won't run all these queries because these all will give the answer it's right and please I will highly encourage you please write it by yourself according to your database or tables great so let's move forward with the seventh question before going to the seventh question you need to know what about keywords so keywords are limit and offsets limit restrict the number of rows returned in the result set which means like as I have said my IMDb data base has three lakhs of columns three or 38 lakhs or thousands of rows so I don't need that because it will take much time to execute one query so I will put a limit and I will tell you how we can put the limit and how the value will look like following with that offset will help us to start from specific row if I need that please search from specific 40th or 10th row till somewhere else so you can specify the source or the starting point from where your query should execute great and the professional definition would be specify the number of rows to skip before starting to return rows great great now let's see the question how do you get the first 20 rows of name and rank score from the movies as I have told you need to take rank name then rank score and from movies both these both words are the syntax of our query so now first 20 rows Okay so it would be the condition so let's apply let select name score okay from movies this much you know now just limit 20 will limit your query till 20 row that's it the 20 row would be printed and it's fine let's move to the next uh question if you want to execute this you can execute this as select name and rank score from movies uh where the limit uh tends to be let's say uh 10 fine now it will run it and see the output of it yes the output is you can see the name is # 28 and there are many null values also in that and they are all being according to the rank score great great great so yes let's move forward with an another question which states that what what to use as limit and offset why to use limit and offset as I have just now told you so we won't waste much time here but we'll do it later also please write the query by yourself write the definition by yourself take a pen paper and write the definition for yourself because this after reading this video you should revise this I will provide this PP just revise it every day before your interview or every Sunday you will become a good Problem Solver after revising each and every slides properly great now let's move to the ninth slide let's move to the ninth question how do you get 20 rows of name and rank score starting from the 41st row as I have told from if I want to start my query from a specific row what you can use as the definition States offset so let's have a look over the query now select name rank score as the question State you need rank you need rank score you need name starting for 41st column you need to use offset minus one minus one because it states that if you write offset 40 which means your query start executing from the very next 41th great now limit would be 20 because I don't need to process 38,000 or 3 lakhs of data points let's say 38,000 for now and from movies fine fair enough moving forward with the another key that you should know which is order by order by name itself says that it is something that we are ordering now let's hop over the definition the order by clause in SQL is used to sort the result set by one or more columns by default it sought in ascending as ASC is a kid key to ascend to process or put in order in ascending manner or descending is a keyword which will arrange your data according to the descending order great now let's see the query over it how do you list top 10 most recent movies by year top 10 so there is a limitation so you need to to use here is limit guys see how I am creating the SQL query like this manner you need to create it in exam also recent movie recent movie which means you need to do as descending order because if the movie has released in 1964 and now currently is 2024 so this is greater than which means it is starting from the end and starting from the end will give you the recent movie update same here with will use descending keyword here now movies by ear there is a parameter year you need to take name you need to take rank score you need to take and year which year for doing that same select name rank score and year from movies and you need to apply the order by which is descending order according to the year so order by descend year descending if you don't use descending here then it will sort your query according to the ascending because ascending is a default case great now here now limit 10 it will give you the latest information present in the database I will highly encourage you to see this Code Practice it by yourself and run it in your compiler great because running each and every code can increase my video in so much length I don't need that it should be crisp short and as fast as possible I can cover all the patterns for you now 11th question how do you list the top 10 oldest movies by year oldest movies which means you need the year which is very less according to the present year so for that you need to use ascending or you don't need to use ascending because it's a default top 10 which means you need to apply limit here great by year okay year is in criteria where you need to apply your order by showing name rank score and year are your select statement that's it Rite it now select name rank score Year from movies order by year limit 10 whatever questions set try to frame your query from the question itself great try to be attentive when interviewer is telling about the question then you can easily frame the SQL questions as of now the queries are very easy but as we are moving forward the query difficulty would be increased so stay tuned now the another keyword that we should know is distinct because as I have saw we have recently that there are many null values also and it might be some actors has been repeated multiple times or it would be some duplicacy would be there in our database so how you can come over from it so now the distinct keyword will help us to do that operation now skiw word is a SQL is used to return only unique distinct values it eliminates duplicate rows from the result set great so question itself State let's see let's see how the question ask us to find so how do you list all unique gener Jers are basically the type of movies that a Sci-Fi thatat a horor and all from the movie generous so we have a particular table inside it which which stores the movie names and the following parameters with movie genders like what gender it is are they are now from the table so from table name we know that it is now unique gender we know then and instead of unique we need to use distinct and yes then you are good good to go so select distinct gener from movies gener okay so it would be table now great great now let's hop over the next type of SQL question so how do you list all unique combinations of first name first name is the first attribute and the last name is the second attribute from the director table director table unique combination okay so you need to get all unique combination instead of unique you need to use distinct here and combination of first name so the in the select statement you can use first and last from the director table and your query is over select distinct first name last name from directors as you can see the framing SQL question is so easy so this question has been asked like I have applied over my table and database but these are asked in the interview where the interview experience written candidate who have wrote the interview experience they just wrote the syntax but I have given you the Practical example of it so please like the video if you are understanding all this concept so let's move forward with another keyword so where now we are using we Clause where Clause is basically help us to apply the condition over each row why I'm telling or why I'm considering over the row you will get to know just be patient now we Clause is SQL used to filter rows based on these specific condition let's see what type of question can be come can be asked to you using where Clause let's see how do you list all movies all stands for select great now with and rank score okay rank score greater than 9 greater than N is a condition here as you can also specify it because you have cleared the nqt exam now showing name okay name year and rank score would be the attribute for your select statement let's see select name year rank score as the question State I have directly wrote into my query now you need to apply the wear Clause where you need greater than nine Mo rank score which has greater than nine and I have wrote the same thing in English also what we can tell okay fetch the name year rank score from these table where their rank score is greater than nine simple as the English States you need to write in very few syntax used to be followed instead of fetch you need to write select great let's hop to the next question 15th question how do you list top 20 movies so top 20 movies would be the like 20 movies so it would be limit great now with a rank score rank score is your attribute here greater than n greater than n rank score is basically stands as IMDb here now sorted in the ending order where you need to use your order by descending d s c is the keyword guys try to write as the similar way I have written the query try to write the keyword in a capital Manner and the column or attribute name in a small thing so that it would create some impression over your interviewer and he would be or she would be very happy after reading your query great now order by rank score so let's hope as the question States we need to take it from the movie table rank score and the sorted ascending order now let's write it so name year rank score you can do or you can write it as star now from movies table as a question states where I need the rank score which are greater than so this is a condition greater than n so I have written greater than n order by as the question States in descending order so order by descending order and it states top 20 so till now this query this query will f all the values present in my SQL but I need only 20 so I have given or I have written the limit 20 so that only 20 rows would be given as an answer for me great now let's move to the 16th question how do you list all records from movie gers okay movie gers where we have the gender according to the movies where the gender is comedy so you need to take out all the movie names which all the movie information not only all the information of a movie which is the which the gender is equivalent to Comedy let's see okay select star which means it will select all columns from movie generators where the movie generators is stable where the gender is equivalent to Comedy and yes all the information which movies are gendering having comedy would be given to you great if you put limit here what it will do I know I hope you have spoken till now it will give only 20 top 20 values in from your query great now how do you list all records all records which means Aster from movie genders movie jous is a table now where the gender is not horror great so we can write the query as Star as the question States from movie genders where gender is not as you can see this symbol here which stands for not like horror you can write it as not like also but this you should know multiple keywords if you write this it will create an extraordinary impression over you because you know this keyword stands for not like great which means you it query will print all the genders rest leaving the horror gener great without wasting time let's hop to the 18th question what is the result of querying movies where rank score equals null the r score equals null okay so query movies where rank score equals null so we need to write a query which will give us a null value whose rank score is null we need to print that so select name your rank score from movies where rank score is equivalent to null instead of equivalent you can use like null great it will work now now let's hop over the 19th question I think I think you are like getting the confidence how to write the query but guys but you should write it by yourself after explaining by myself please write it by your hand so that it will fix into your mind now how do you list the first 20 which means as of now you have spoken now limit now movies where the rank score is null okay select name rank score movies rank score is null see eal we are using some different keyword here eal where the rank score is an attribute where which is e which is null and only top 20 value I need I don't need multiple values only top 20 values great so moving forward with the 21st question which states that how do you list the 20 movies where the rank score is not same query you just need to write is not null guys as you are thinking that the questions are very easy but it is because I'm explaining you and the question statement are also pretty clear to you but but these type of question please try to touch all the significant keyword that I'm using like is not null eal that particular arrow symbol that particular arrow symbol that we have discussed please take a note of it so that you won't miss out from your mind and please revise that's why I created a beautiful PDF for you so that you won't uh you won't be tired of revising so please revise every day or every weekend so that it will fit into your mind great so now it will give you the out put of top 20 values which has null great now how do you list 20 again I I think I don't need to tell it again and again it's limit where the rank score is not null same guys not null it's good to go it's good to go it's not a big issue now now keyword logical operators what is meant by logical operators which means and or not between in like so these are the keywords that you should use into your queries and and when let's see let's see so 23 how do you list movies with rank score so movies is the table also rank score is an attribute greater than nine is a condition and released after the year 2000 it's also a condition so we have two conditions which means in where statement we write we need to write two conditions so let's see select name year rank score as for the question States I have written it from movie table as the question States we need to fetch over the movies and two conditions it should rank score should be greater than nine and you can see in the question itself it is written that and you should use or if question States at or you need to use or now and year 20 2000 which means the year which means it will fetch that particular movies which has rank score greater than nine and greater than 2,000 okay if you need that if you need that this should be uh run into my compiler so let's have a quick run in my compiler and see the output like few of few think I will let you know how we can run it yes now let's run it okay you can see now okay you can see the output here like we are getting the output of all the values like we have 10 values I would say okay not 10 I'm not written any values so it's G all the values that are present in my table so you can hop over it and see all these values are been given which has the rank score greater than 9 and year is also greater than 2,000 great great so without wasting time let's hop over the very next Pro very very next query great so next query is how do you list the first 20 movies released after the year 2000 which means after the year 2000 is our ition which is come into the wear Clause first 20 movies so limit you should use limit here because it is written that you need only 20 movies now let's write it name ear rank score or you can write star because question doesn't specify to use this much from movie table where not it where the year is not because question states that you need to list a movie after the year so after so which is not equivalent to 2000 you need to print each and every rows so that's it and limit Clause will do I know what it do and you also know what it do now 255th question how do you list movies with rank score greater than n or released after the Year 2007 greater than n you know what it is condition so first condition you write it into your wear condition and or is The Logical operator here where you need to use or here released after released after 2000 which means the date should be greater than 2000 2007 same name year rank score from movies rank score should be greater than N9 or the year should be greater than 2007 I hope you got it it is very easy Guys these types of query has been asked multiple times in many interviews so if you are able to solve this query which means you are taking a step forward to your interview now let's hop to the 26th question movie released between 1999 till 2000 between is a logical operator here that you need to use now inclusive inclusive which means you should take 2,000 into it great although although between whatever range it is it is leftmost range and it is rightmost range all these range are inclusive great so name your rank score movies year between 1999 to 2000 as it states and just take it from the question and put it into your wear cloth that's that's because this because if I erase all these lines and you can see this itself create our we Clause condition and this itself tells which table and what all values you need to take WR to select and you're dusted now let's move to the very next question which states how do you list records where the general is either comedy or horror I hope I hope take a pen paper and write this query right and all please don't see the solution okay so select director ID and general as a State we need to do that I guess in this question doesn't state that to please add it you need to get the director ID and general into your question great so it's not an issue though you can use star also if question doesn't specify but I have wrote it now director General uh would be the table in which all director corresponding to the gener they create the video and where the gener in comedy and horror because question states that so I am using in here which means all the gener which is comes into comes into my comedy or horror it will give you whole AR or if you if you want these uh solution to be run I will happy to let you know because this is also a very unique solution so let's write it here as limit only 20 because I don't need each and every value so let's select it put a run condition and bingo all these value generate with Gen director has been come if I do start so that I need each and every parameter that is existed in the director table so director table has director ID G and probability okay great great so yes that's you have saw this particular example now let's hop over the next question so how do you list movies with names starting with t name starting with t so please you need to return the movie which is starting for T so you will learn this New Concept here so as the question state name ear rank score you have selected or you can use a star from movies like name like this so This mod what will it will do is it will fetch all the names all the names with starting three letters should be same as this and rest of the it can be anything great and manipulating this modulus symbol will let you will let you fetch all the particular information that you need we'll see each and every condition in this video so let's hop over to 29th question the first name ends with yes and if yes now in previous case the T last we have using moderator mod operator which will giving us the value which which the first name is starting from T or the first three letter is starting from T now es you need to end so end then you need to apply mod here that's it now same case we have written here and you're getting your output I hope you got it I hope you got the pattern please understand the pattern here now the next question contains first name contains es contains C yes so first name should contain C yes so first name and last name I'm fetching it because we need to print it now actor first name like if the modulus operator if you put in the beginning which means the first name which is ending with es word would be given to you but if I put it here again which means es should be in between either it can be anywhere in a word you should give as a result great so it will give you this output great great great so let's move forward with the 31st question please read the question and write it by yourself pause the video now how do you list actor whose first name match with a NG g a g ncore s you need to take underscore exactly one character so it should be the same as the question States please don't apply your mind here now first name last name actor first name like this it's a pretty Cake walk to you for now because till now you have solved 31 question in it just like a Chui so it might be you have solved this question very easily because I also when I saw a tutorial I used to read the question then directly write it by myself so that it will fit into my mind so please follow this rule okay so let's move to the 32th question so now how do you list actor whose first name start with L first name okay start with L okay so it will look like this but do not start with li I okay so it would be some someone like uh Lander okay great so it would be Lander but it can't be Linder fine so you need to take this name not this so you need to remove Li from it it's not should start from Li so simple as the question States write the same thing so yes who's first name okay I need to take the first name I will take it first name start with li okay start with li okay and not start with okay is iite is not like Li not start with so start with li that's that's that would be the code guys Bingo WR the same okay so like Li and first name should not like this okay guys so as the question States you should know the particular format like where to use modulus how to use models so that you can use it according to the question and if you are able to use it you can solve any any problem any problem related to this modulus starting from ending with where there everywhere great so yes now keyword states that aggregate functions aggregate functions in SQL compute a single value from okay so aggregate function are basically count min max sum average similar as provided in Python you can use any module to calculate sum count and everything so but but but guys there is a homework for you please look over replace keyword concat keyword because these are the particular aggregate function which mostly don't ask but this is a homework for you please have a quick look on W3 School of replace concatenate concat I would say or these type of few basic more uh functions it's not been asked thoroughly but as it strikes into my mind I will let you I have told you now now now let's move to the 33th question how do you find the earliest year in the movies table earliest year which is Min which is very first movie in which year it has been loaded into a database that would be the very earliest year so select the Min year and movies would be printed whatever movies has been published in that minimum year would be given to you as your output okay now how do you find the most recent year in movies table in movies table recent year which you mean Max which you mean Max simple select Max year fine you have written Max and yes whatever movies has a max year let's say 2024 all the movies will be listed as an output here now now if I execute each and every code let you see guys how much time it will add 1 minute for each question and it will create a big slot of this and I hope like many of people used to just double tab just need to know what actually like what all patterns are been there so please have a have a note over each and every keyword or each and new pattern that I'm solving it now it's count start total number of rows in the movies table so total number of rows you can write count start it will count each and every row present in a table so now movies great so it will give you okay great now how do you count the number of movies released after the year 2000 after the year 2,000 which means this would stands for your condition number of count the number of will give you the aggregate function what you need to use here you need to use count and from movies okay so it's not specify particularly which count they need so you should you star here movies where the year should be greater than 2000 whatever question said guys you just need to write the same thing now 37th question how do you count the number of non null year not Nar ear count the number of not Nar ear guys in this you have a parameter for your aggregate function so count itself states that you need to use count as your aggregate function of nonnull non learn ear so non learn ear will come here and it will create your select statement now select count here from movies and it and it will remove the null value because null value won't be counted in this so null value would not be counted and you will get your response output great so let's hop over the new keyword that you should know group buy guys this question came multiple times because group Buy has having have some issue with them so most of the people likes to ask in my college also people were ask this types of question Group by having and difference between having group and group by and where so please prepare for it I will also let you know in this video too so yes so Group by clause in SQL is used to arrange identical data into groups this Clause is often used with aggregate function count min max okay so it's basically same as the previous aggregate functions but Group by what what it basically do is if you apply it over any value over any row so instead of printing whole row it will take a it will take a single let's say how many Aditya has Cod marks so it will take total number of Aditya as an input so Aditya and the marks here simple we'll see the output like this okay so as the question States how do you find the number of movies released each year so this would be the scene because you don't need to print the number of movies you don't need to print the movie name with the year which has been published in the database you need the number of movies so from printing the number of movies which means you need to use count function here count aggregate function here mov is released each year which means count has an attribute with it year now now Group by year what it will do I will let you know I will execute this code so that you will get pretty rough idea how this group buy works so let's move to my workbench and just write p this code and yes yes run it and bingo we got it like in 2002 we have 12,056 data 12,056 data 2,000 May 11,000 data similar to that everything so 1888 we have the Min year as an 1888 see guys so how much data it has I will try to upload this my SQL IMDb in the description please have a look after 2 3 days because all the videos are lined up I want you to crack the interviews and let me know I would be very happy because I have helped you to crack the interviews so please be with me and I will help you till your last stage if you want guidance please let me know over my Instagram LinkedIn or anywhere find the link over my channel description I would be very happy to contact with you connect with you so yes as you can see how the group group by works the same case it will applies here and yes now how do you find the number of movies year and we discuss you need to use Group by because Group by will do each and year will be grouped together and it will give you the output great now how do you find the number of movies number of movies which means count okay released each year which means it's an attribute so write attribute now from ordered by year ordered by which means you need to order by as ascending order please guys do this specific precedence like select should be very first from group by order by because you can't use up and down it will create some issues great so now please have a look order by year it will sorted according to the year which means the minimum year would be at the top and the maximum year would be at the end similar to the previous just need to sort it that's it now moving forward with the next question how do you find the number of movies released each year number of movies so you need to use count function okay what's the par number each year so it should be year here okay year okay ordered by here same question but the thing is you are learning as keyword here as basically used for alling purpose you are alizing it somewhere like count ear would be denoted as year count so the table name table name if you don't write ads then the column name would be this count here but I don't need that I need a good name so I am putting as year count and it will look like the same okay so let me run this so that it would be very intuitive to you so let's copy paste go to our workbench and paste it here and uh bit of alignment I need oops yes now just select it run it and yes as you can see the ear count has been written if if I have removed this so see the difference see the difference and guys in the question it has been written many times like how to give your answer because in hacker rank test they need specific column name that they have specified in the question so please be prepared for that that's why I'm telling you so it would be great I have told you how the output will look like so moving forward with the very next very next uh table great very next keyword so it's a having so now the group by rivalry came having so having clause in the sequel is used to SP specify the condition on Group by created by Group by so if you using Group by to specify it okay group the years but the year should be greater than 2,000 we'll see we'll see the query in the next slides so let's hop over the query so query States how how do you let me write H very nicely I have I have cuted it so how do you find ears with more than 1,000 movies how do you find es es okay find es which means count we need to find count so count and year would be the attribute more than thousand so it is the condition so it is a condition and how find ear with more than th movies so you need to use Group by year because you need to group each and every year as for the question States and and in the last it should be more than 1,000 movies so yes similar ear count ear and I have showed you if you don't use as it will work it as would be applied defaultly and ear count from movies ear and ear count would be greater than 1,000 would be your condition so having you have used and yes so it might be coming into your mind ad can we apply where here guys where and group by have some difference like we only applied over row but Group by applied over whole group so taking or man ulating the group or applying condition you should use having to apply over a group as you can see in the definition also having conditions on group on group and in where you will apply where clause on row on row great so put it into your mind because this is a very famous question why don't we use uh can we use or apply the condition in groups or having queries so you should be answerable for that let's hop over the 42th question how do you list movies released after the year 2000 how do you list movies okay movies is a table in this case released after the year 2000 after the year 2000 is my condition without using group buy see how you can do that without using group buy so the output will be same it will be arranged in a column wise like how many year how many movies are there which is greater than 2000 I would like to run this code I would like to run this code and guys this question was was very very famous in the recent interviews that's why I'm running it those question whichever I'm running it it's a very important great so you see same thing happens name came up movies 18 okay let me run it again I guess it is the same output I will yes name is year name year okay only above greater than after 2000 all the inputs are coming up all the inputs are coming up which are greater than 2,000 according to the same condition as the question States great great now now let's hop over the very next question so please guys this question were asked in TCS guys Microsoft Amazon on phone screening round on phone screening round I have saw over the lead code and I have took this question great so creating the content is a very toughest because I need to create a Content which will help you it's not a you should know you should put your time where there it would be valuable for you so after this if you revise it then you will definitely remember me and you will write me guys ad due to your video I was cracked many people I have saw the particular message and would be very happy to respond them back so yes please if you are watching it please have a note and cracked interview and let me know what happens great I know that in this PT in this from this PP the same question will pop into your interview same question if the question is about imdp but the column name but the table name would be changed but from this all patterns would be covered so that from this only the concept will pop up to you great now how do you find with more than 20 movies that have rank score greater than I more than 20 movies so you should give more than 20 movies find the years with more than 20 movies that have a rank score greater than n which have a rank score so you have two conditions more than 20 movies and great than n rank score great so year count as the question stes find years more than 20 so you need to count them then only less than 20 year count should be less than 20 is it so find year so count year will give you the number of year that the particular query had or had been generated more than 20 using having because it's a group here Group by using so we need to apply let me write clearly having like this case less than or greater than 20 great and that have rank score greater than n which will be the another so you can use where condition to apply each and every row the row would be applied and whatever query like this much this much would be executed very first so all the values or all the rank score which is greater than nine would be collected now all these year would have like first step like first step what will happen is all these rank score rank score greater than n greater than 9 would be segregated out created a segment then it would be grouped by grouped by what what what it is written that year it would be grouped by year so that all year would be in the leftmost column third the having condition would be applied try to remember the flow of your SQL condition because I have saw the interview experience in that the candidate said that after writing the code it told me how your code is working let me know the step-by-step execution so this is the flow of this code please have a look over it great so now additional point so Group by Group by rows that have the same values in the specified columns great so same Valu specified columns aggregate functions count some average we have taken it h having filters the group based on the specified condition having would always depend over the group by not on WE case on the we Clause you can't apply having but but it you can apply over the group by Clause great so what is the difference between having and we because we are applying over the group by so as I have told where is applied to the individual rows individual rows before grouping so this would be the keyword that should come out from your mouth when you are telling your interview so individual rowes before grouping having is applied to group after the aggregation aggregation what it is Count min max group y all has having their or the same aggregation it I hope you got this difference this is guys very important very very important very very very important please have a look over it put it into your mind revise every day you won't forget and you also won't forget me now join joins so join most of the people worry about what is join but in dbms t video I have explained very good nicely about joints what are joints and let's see the coding question about so joint SQL is used to combine rows definitely the name State itself so what are the types of join so are inner join then left join then right join then full outer joint it would be full outer joint both of the table would be joined together great so let's let's write the very first query how do you list the names and the genders names and the genders of movies okay so movies have genders into it and names into it is that true no no because genders are in movie [Music] gener is table movie generous table we have generous and movies we have in names great so limiting the result only till 20 rows so you need to First join two tables what are the two tables first the movie and another would be the movie Genry where we have all these generes stored we need to attach that okay so now it and the result should be given to so okay I have wrote name gender just ignore for now what I'm doing is movies m m we are an analyzing it you can use as here but it is fine as will be defaultly executed if you write space separated M so M will denote movies here now join join will join both the tables of movie generat which is denoting by G and the same case has been using here if you're not alizing your table name then you need to write instead of M you need to write movies and instead of this you need to write whole these table names so instead of that please write to clear code please write this try alizing into your P now what is this ad because M ID and G mov ID are primary key and foreign key because it knows that the from both of the table the ID would be same for each and every movies great the I will let you know I will I will show you what that means is what that means is so uh let me copy this and let me go here just printed okay so now now you see guys you see firstly I will let you know select let me put my pen back select star from movies because we have two tables name as movies uh and star okay great star from movies that's it and I will let it as limit as 10 because I need to show you a few things great so yes let me run this now you can see ID is 012 # 28 as a movie name and year would be associated with it and instead of movies I will write movie gender movie genders great oops oops oops oop it will be executed everything everything everything it would be great great great now no now I need only this so I will read it again so yes now movie generes guys see the movie ID 1 2 3 this is would be the same column present in the movies okay so that the same thing we are taking the advantage of it we apply we apply is the join condition when we know that the both the column would have same number as an primary key in this ID is a primary key in this ID is a primary key and all these ID key would be there inside it so I need to join according to that so it would be the same condition it will join it and give you the answer it we are using inner joining here to now limit is 20 that I have told you so all the movies which have same movies ID and movies gener would be combined together and you give you the output so if I will uh let you know the answer of it because I have copy copy pasted so let me run this okay so yes you can see these are the same movies that are present in the movies as well the movie Jers and the output would be this the gener would be attached to the name according to their IDs we have used IDs guys try to use the same column used in both the tabl so that it will help you to join it great great great so now now now let's hop over this code and in this code what actually happens is this these are the two tables these are the two box two tables I would say this is movies table this is movies gener table great and both the values has been like from all the values has been printing it which has the same value which has the same value I hope you got my flavor what I am talking about now let's hop over the next question what does the query return When joining two tables T1 and T2 using their common values using their common values which means we have two tables and using their common values we need to print so we can use inner join here in this case because inner join stands for a intersection B and we need to print that only so select start T1 T1 joins T2 it will print all the column values which has same values similar to the just previous question this question same thing we did and this a particular syntactical code like what actually we are doing it great I hope you you have covered all this and you getting everything into your mind please have a note over it because join is the most favorite hot everything Co topic it would be definitely asked to you if you have written SQL into your resume and you have given the rating also like you are four in SQL so please if you know this it would be very easy for you please have a look for it great so 46th question how do you join two tables T1 T2 on column C1 see T1 T2 you can use join operation as we know on column C1 it's a new case so you need to use using C1 here it will take the column even and attach the values that's it any column name you just need to put it here put it here and according to that all the value would be joined and joined value will be given to you every column value will be given to you which applies after applying the joint great I hope the syntax is clear to you because interview in you they will tell you please let me know the syntax of this so now let's move on the 47th question how do you list the names generes generes of movies including movies with no generry limiting the result to 20 rows it's bit confusing but pretty similar let's see you need to list the name so first would be the name from where the movies have names okay in generally we don't we also have movies but in this case you need to use movies movies because we are applying join over it great now movies name generry of movies gener would be in movies or Genies of movies great of movies you took name and generry from other other tables you have performed the joint on which condition on both the ID condition I have joined my table first condition is over now including movies with no garies including movies so you need to apply what you need to apply what you have movies here please try to create these diagrams and another is your movies General great and you need to print with all the values with have no Gary also so you need to apply left join here you need to apply left join you don't need the right side value so as for the diagram I have written it please do that because I will use movies at the left side and movies Gary at the rights let's see the equation whatever I have said whatever I explained after reading the I I have wrote the same thing select M name g generry great from movies M movies on the left side movies on the left side that's why applying left join please guys please mandate it's mandatory to follow the particular order if your movie Gary are at the very first which means you need to apply right join then if your look like this where your movies generry are in the right side and in the left side and movies is there on the right side then you will apply right join here because whatever movies value we have whether they have generary or not we need to print that so same thing you need to follow the order that you are writing it please put into your mind now movies M left joint applying left joint on Mid according to the IDS we have Club both the tables and the limit is 20 just simple I hope I hope you got this it's a very easy easy explanation of this particular query so let's hop over the next question it's a pretty good question I would say how do you list the first name and last name from the first question I we have saw that the first name and last name is under the actor's table great of actors as states that actor is a table that we need to use who acted in the movie so acted is the keyword that you need to think what is acted it is roles like whatever people have worked in officer 444 movies and what are their roles so roles we have a table in which all these roles of each and everyone who have acted in the particular movie we have stored all this into the roles and each and every table have all these uh id id same ID column as same great so roles and in the movie so movie we have a movie table significantly uh separately so guys in this you need to you need to apply join Over actors roles and movies and the movie name is Officer 444 simple you have three movies great great great now you need to join it and you and you know that joining it will take a single column that is same in all the three tables and we know that that is ID so I will fetch the ID or I will try to add or Club all the tables using their ID and what all value we need we need a last name and first name from actor so I have written a actor first name a actor last name from actor table join roles where I got to know who walked in which movie so r on a ID and R actor ID great now movies M on M ID and R movie ID which mean roles and movies also been clubbed actor and roles also being clubbed and your solution is done now just need to write and M name which is a movie name is equivalent to officer 444 I hope you got the intuition you have figured out which column is same in both the tables what all you need to print from which table and what is the condition that you need to apply while joining it when we are told that we need a table which is joined all three we use ID and ID also with a condition the officer 444 should be the movie name great so this solution is pretty intuitive you if you have understood what the join and how it works it would be Cakewalk for you and I hope I hope you have know that how this join is working till great now moving with the subqueries guys in theory video there is a particular topic what is subqueries what is coated theories and many more please have a look over my dvms interview questions because it's an also important way because interview will start asking you the theory question first then they he will move forward with the pr practical or the coding question which can be done in SQL my SQL post SQL whatever you have written so subquery is a query inside the query let's say it's a query one and here you have written query 2 so query inside the query which is subquery you can go over this definition the main thing is not in in like in not in exist not exist any all it would be the type of aggregated functions that you should use great so operator exists and all the name itself States what it is we'll see as per examples now how do you list all actors so you need to add actors table in the movie so you need to add movies into it now which should be the Slender list so guys the subqueries can be implemented using the uh joins also but the question States you need to use subqueries so please use that I'm a fan of a subqueries because it's very looks very intuitive for me okay so you need to select first name last name as for the question great from actors okay I have written the same case now the second case is which the movies is scander list okay scander list I need to do that so what I will do is for scander list movie name I need to find the actor ID from roles who had work in that particular scander list because when we got the previous uh previous question that we see now that acted in which roles had a specifically actor ID with their actor name and the uh movie that they have worked in the movie that they have worked in from there I will get the ID and I from the ID I will take the first name and last name and I will print it okay so we are using three tables here first would be actors table first would be actors's table in which we need first name and last name first name and last name then I'm using roles table to fetch out actor ID who works in given movie and movie stable to fetch these movie whoever what all actor ID who's associated with this movie side great great so let's write the same code first name last name from actor good first statement is over where ID in where ID in select actor ID from roles from roles where movie ID is in where the movie ID okay select ID from movies where name of Slender list great so basically the trick is when you are when someone asks you to write the subquery please write it from the back okay you need a movie cender first okay I will fetch the movies which name as Slender list great now I need to take the movie ID from it because I need to check who's who all actors have acted in movie SC so I will take out I will start running my RO table in which I have stored all my actor IDs then and movie IDs too so all these movie IDs been done because my roles table have movies ID also into it where we can able to connect movies ID and roles together great so movies ID has been clubbed ID in name actor ID I hope you got it because from starting list then first actor ID and what all name associated with the actor ID you have printed it start from the back and end on the first it will solve any subquery very easy now see how do you list all actors All actors you need to actors table you need actor tables in the movie so you need a movie table also who screen the list who how do you list all the actors simple rank score which is greater than equals to all all means all condition when it it when it returns true which means it is having all the condition as true if no values is there it's false it won't work but if all the values are true all will work great so select Max r rank score if rank score exist with max value it will return otherwise it will don't return so from movies it will do that great I hope you got it if we we are using an aggregate function all here to fetch all the rank score which is Max value and movies name great great great all the movies information would be printed here and you can see it's a query inside the query for off a same table not another table you can use see that movies is the same thing movies is the same thing great now additional point if check if the value matches any value because I have show you many aggregate functions and not in check if the value does not match any value basically they are Boolean they will tell you false or true any returns if any of the value in subquery meets the condition returns to if all the subquery meets the condition if any subquery doesn't meet the condition which means they don't have the corresponding value that you are requesting then they will not print it great so return true if the subquery returns no records great so uh exist and not exist it name itself say whether the particular row or the particular condition exist in the particular table or not great so let's move to the another keyword which is data manipulation language which is DML okay so DML we use for select we have saw extensively till now and re insert update delete we'll see now so how do you insert a new movie record with ID this uh name Thor year this same thing movies you don't need to write this but if you write this in the interview it will create the impression on him now values values which states as per the question because they will not they can change the specific condition so you ask from your side so what is the format of your table it might be the ID should be in the last or somewhere so you should fill according to your describe according to the description of the table great so Thor movies would be appended into it so this is the particular syntax I hope this is a very basic you should know about it now how do you insert multiple movie record in a single Quil multiple movie record simple you need to specify the uh constraints like how particular constraint is how in which way the table has the all values now please append the values you can see three of the values I have been appending multiple values I been appending in one single insert equation right so how do you copy rows from one table to another table if you have watched anub stand this work has been given to him and he was not able to do that he was doing single single word and not able to perform the whole task in one day so you can do it if you learn this now insert into Target table column one column two you should take it now select column one column two from Source table that you are fetching it now where the condition that is according to the question so this is a syntax basically to how to do the specific operation please have a note for it because this question has been asked in 2018 batch not recently but if you have unlucky you unlucky what it's easy though you should know okay so yes I have explained this is very also so please have a look have a note over it revise it every day and a the interview is the interview how do you update the rank score of the movies with ID to9 simple update movie names uh table name set condition and set which row you want to update rank score equivalent to nine like whatever value you need to update put the condition you so that you can find the row so ID would be this so please find the ID which is equivalent to 412 321 and please set the rank score to n from the movie table that's it that's the upend statement will do great so how do you update the rank score of all movies released before 2010 before which means simple guys update movies set what you need to set in that particular column and year should be less than 2010 so all these 1888 to 2009 all the value would be set it as rank score eight great so how do you delete the movie record with ID 412 321 simple delete from table name and put the condition simple where ID equivalent to 412 321 that's it and you can delete the particular specific please try to focus over the specific condition and it will work great so how do you remove all all rows from the movies table so all rows so for removing all rows you need to use trunk it one of the people I saw in my college instead of using trunk K he used dropped and also the interviewer had dropped him great so trunk kit is used to remove the values of all rows it will not change the schema but drop will delete the table great so try to understand this definition now ddl ddl is basically create alter drop which is the data definition language D used to define man manage database schemas objects and table see manipulation and definition both are different manipulation means you are manipulating inside the table you are not disturbing the schemas but if you want to disturb the schema please go to use some uh data definition languages great so create alter drop are and truncate are the data definition languages so please see create table how to create a table you can create it like this please follow the particular condition as per the St because nowadays they take machine coding round in that you will get two page of question you should create a database according to that so all this data type primary key foreign key everything would be given you need to create it for creating foreign key you should use foreign references foreign key okay foreign uh let me write it properly so yes so primary key you can do it like this but if you need to write foreign so please foreign key the column name references to means switch references to and primary key in in this case it's an ID so put it that and the foreign key would be constructed so okay so let's hop over the 15th or the keyword the constraints okay the constraints are the rules applied to the table column to enforce the data Integrity guys if you have watched my dbms theory video see how much this data Integrity manages because I guess if the interviewer started asking a question about SQL dbms they will definitely ask you how you can manage the data Integrity if you know that if you want to know that please watch my DBS interview video in that each and every question that has came previously I have did in my video now now not null ensures that a column cannot have null value okay unique and short that all values are in the column should be different okay so difference between primary and unique is primary you cannot add null value into a table when you are inserting it but if you are using uni instead of primary then you can put null value into your table so please guys there is a very hot topic is difference between unique and primary key which can be asked from you in your interview okay so now check default index are basically the same used to create an inter data from the database very quickly so if in my database video I've talked extensively about what index is how it optimize our queries so please have a watch so default and check name step Sal I think I don't need to explain it now 58th question how do you ensure that the name column cannot have null values how you can ensure it just put not null thing okay you can put not null or you can put it as primary it also ensures that that particular column will not filled with a null value but if there is already a column which has a primary key as a constraint then you can apply not null and you can apply not n each and every columns it's not necessary you can use once twice you can use each and every columns great now how do you ensure that all values in the email columns are different in the email columns are different as I stay unique which means you can append the null value also or you can append your email also which should be unique not the same would be put in again guys try to focus on these minute things because it can create blunder because these are easy but they are not coming to your mind when you are appearing for the interview okay so let's move to the very 16th question how do you quick uh uniquely identified each row in the users table how do you uniquely identified each identify each row in the unique table as simple create users Prime put primary key then all the values that present in the row would be unique 100% it would be unique and it would not be null great so let's move to to the 61th question how do you ensure that the user ID in the order table uniquely identifies a record so you need to put order ID uh record in the user table okay so there is another another table that you are creating which is order from the user table so you the might have got the particular idea what it is we us we need to use foreign key so as I've told you how to create the foreign key in just few second back so create table orders order ID int and user ID int that you need to use as a foreign key so see now foreign key as user ID this value that you are denoting it like this let's say let's say this is an user this is an user table in which this is the this is the user ID and another table that you have that you have just now created which is orders and the orders has this users ID use references to user ID let's say let me write it clearly so so it is the uh order ID I okay okay so it would be the user ID and references to users ID so user is the table name here and ID is the ID of this so users is a table name and in my user ID is the column name great so what you are doing is you are pointing it to your primary key to the foreign key this mean by this line user ID will point to to the primary key of the user table on ID column great so let's hop to the 62th question how do you ensure that the price column in the product table is greater than zero it's just set as a check as I've told you check unique not null null multiple primary key fall on key everything just the back videos just in the back slides okay so create table products ID print price decimal which means it should be 10 comma 20 decimals now check price always when you are appending it the price should be greater than zero it should not uh less than zero it should be minus it not been possible so please put this condition so that you ensure that the value should not be less than zero great so how do you set the default value simple yeah default current or it about created the column current time stamp you need to store the time stamp also when your column has been created so please this is a particular syntax time stamp is a particular data type for storing a time stamp and default current time stamp which states that please store the current whatever current time it is please store into your database when particular row has been appended great how do you create an index name column in the user table as we know that if you watch my dbms interview questions we use indexing to optimize our query exponentially so create index index username which means what is the username or uh you need to provide let's say Adas please create an index IND over the Aditya on the users as my name user table user table with a name column please create an index over my Aditya if someone finds Aditya just hop directly to the Aditya scheme which means Aditya or I would say this index user name has a pointer over my database where the Aditya values has been stored so that if anyone directly calls my name so it without taking much time it will directly hop to my particular address and find whatever condition matches with the query and it will return the particular output great let's hop over the 65th question let's hop over the keyword alter because it's the same though that's why uh alter the alter command is used to add modify drop column in existing table great so alter basically used is a DD language which help us to manipulate the schema let's see how do you new how do you add new column so alter table table name language add country work at 50 in which country the language has been spoken please add that particular column into my table so you put the table name put the new column name that you need to add and the specific data type great so new row will be appended now change the country name country column change the country column in the language to the table to type whereare on whereare 60 so previously it was 50 you need to shift it as 60 so you just have a quick change write it modify and change your particular column great so how do you remove the country column from the language table how do you remove the country column from the language table so alter table table name drop I don't need that country so please drop it it will drop it that's why we are manipulating manipulating our table that's why it is uh that's why it is ddl which is data definition language in which we are manipulating the schema not the table schema because we have reduced that particular column from it okay now drop is also a ddl language where we are dropping something from the schema remove the table both the tables and all the data permanently okay so how do you remove the permanently please drop table table name you can remove it now on the very last question States how do you safely remove a table named table name only if it exist drop table table name and if exist it will drop it otherwise it will say the table doesn't exist in the table pce great great great so guys I have created one short video of array string and many more all shift Solutions of TS and QT if you are preparing for it this would be the short shot watch and these from these all the questions are coming from this type of one shot because it's been after researching a over the net I have created it so that it will help you so that your time would be valuable if you're spend spending on my videos so please subscribe the Channel please like it share with your friends and support me put the comment whatever you like what you have doubts and if you want to reach out to me asking any doubt guidance you can contact me over my Instagram or LinkedIn I would be very happy to respond you back so we'll meet you with a new video soon