hey guys i'm tawfiq in this video i'm going to share with you my top 25 sql interview questions which i have come up with with my own experience of using sql over the past 12 years of my career now during my career i have taken several sql interviews and i have given several sql interviews myself so all of these questions are based on my own experience now if you are someone who is planning to attend an sql interview or someone who is learning sql then i highly recommend that you go through these 25 sql questions and also all the sql concepts that are covered in these questions and try to check if you're able to answer all of these questions yourself i strongly believe if you're comfortable with all the concepts that are covered in these 25 sql questions then you should be in pretty good state to clear any sql interviews now most of the sql questions that i'll be covering in this video are going to be theoretical or covering some sql concepts i'll not be covering a lot of sql queries and the reason for that is i've already made separate videos covering the sql queries that you should be familiar with to clear sql interviews so i'll leave a link to those videos in the description below or you should find it somewhere in the screen now okay so what i have done is i have written a blog on my website where i have mentioned all of these 25 sql questions and also the answers to these 25 sql questions now i'll leave a link to my blog in the description below so you can check that out but before you go and check that blog out first try to watch this entire video because in this video i'm going to quickly go through these 25 sql questions and then may provide some additional information about some of these questions which may help you to create some sql interviews okay so before i can start i understand from youtube analytics that almost 90 percent of my viewers do not subscribe to my channel so if you are one of them and if you think that you have watched any one of my video where you have found some value then do consider subscribing also if you think that this video is going to help you in one way or the other then please make sure to like this video thank you and let's begin okay so i'll be going through these 25 sql questions from my blog itself now as i told you i'll quickly go through these 25 questions and try to give you some additional information which you may not find on my blog so try to check this video completely before you go into my blog and for all the answers you can refer to my blog so i'll not be covering all the answers in this video because i want to keep this video as short as possible so just trying to give you all the questions and the concepts that you need to prepare for an sql interview okay so let's begin okay so this is basically how my blog would look like top 25 sql interview questions you would see some introduction and then you would find the link to this particular video and then you have the questions so the question number one is what are ddl and dml languages now this is the most basic question that can be asked especially if you are planning to attend an sql interview for a fresher role or for a beginner role now this ddl dml dcl pcl and dql these are the five languages that are available in sql and these are the most fundamental concepts in sql so it's very important to understand what are the difference between these languages and what are the different statements that are available under each of these languages okay so this is going to be my very first question and you would find all the explanation to all of these five different languages in this blog so you can check that out you have ddl dml dcl tcl as well as dql and now let's move on to the question number two the question number two is what is the difference between delete and truncate statement now this can be one of the most commonly asked questions in an sql interview now this is a pretty simple question but i have seen personally when i have taken interviews when i ask these questions not everyone is able to answer this question very comfortably or very confidently so it's important that you understand the difference between these two statements we know that delete and truncate can be used to remove data from the table but what exactly is the difference what what are the things that you can do in a delete that you cannot do in a truncate and why do people use truncate like what are the advantages the main advantage is that truncate is much faster so let's say you want to delete a table which has let's say a million records then if you try to use the delete statement it's going to be very slow rather if you try to use a truncate statement it's going to be much faster now i have not explained the details on why truncate is faster than delete but this is basically one of the reasons and truncate is a ddl whereas delete is it belongs to the dml language so there are a few differences which i mentioned in my blog do check that out so this is the second question now let's move on to the third question the third question again is going to be a very simple question that is why do we use case statement in sql give example so case statement is similar to the if else statement that you would find in any other programming languages even though this may be a very simple concept you would not believe that how often case statement is used when we write sql queries so even though it's a very simple thing it's so widely used that you can expect to be asked a question on case statement so it's always good to be thorough with case statement when you are attending an sql interview okay now let's move on to the question number four so the question number four is actually a very important question that is what is the difference between left right full outer join and inner join now a lot of you guys have asked me on in comments as well like can i make videos on joints and etc so i plan to make a detailed video on all types of joints in sql pretty soon but i think if you are trying to understand what are the difference between the different types of joints in sql then i highly recommend that you read everything that i have mentioned in this blog now basically when you go to an interview the interviewer basically may show you couple of tables and then tell you when when you do a left joint what would be the result and when you do a full outer join what would be the result so the question might be something like this so that is why i have presented here two different tables i have shown you the data of these tables and then for each of the join so i'm starting with inner join i have explained you what inner join is and then i have written the query and then i have shown you the output that you would get by doing an inner join and then the same thing i have done for left join and then for right join and then for full outer join so just by looking at these queries and the data that is written from these type of joints and also the explanation that i have given you should be able to understand what is the difference between these joints now not only this i've also mentioned the other types of joints so self-joint what is self-joint what some examples to that and then what is natural join and then also explain what is cross join so if you want to get a complete understanding of joints do check this blog out and definitely if you're going for an sql interview you should be thorough with joins and i think just by looking at this blog and looking at all of these examples and then trying to replicate all of these queries in your own system i think you'll get a pretty good understanding of joints so definitely be prepared to answer some questions on joints okay so let's move on to the next question so the question number five is what is the difference between distinct and grouped by now this also can be a pretty common question that can be asked in an sql interview now i think if you are pretty new to sql then you can get confused on what is the difference between distinct and group because by using both of them we can fetch unique records right but there's a major difference between distinct and group they are not similar in any way so and have explained everything in this block so do check that out and definitely get complete understanding of when to use a distinct statement and when to use a group by statement and why do we use both of these statements okay so let's move on to the next question and that is going to be the question number six the question number six states that what are the rules to follow when using union operator now similar question can be asked for any other operators like union all intersect or minus now there are certain rules that you need to follow when you want to join couple of queries by using any of these operators and the rules i have mentioned here you can definitely check the answers in my blog so let's move on to the next one the question number seven so that is what are aggregate functions and name and explain different types of aggregate functions now if you would see that most of these questions that i have here are more than questions they are something like the complete sql concepts that is because now under this concept there can be several types of question that an interviewer can ask for example an interviewer might ask you to write a query where you have some employee table and ask you to count the total number of employees in each department now in order to write that kind of query you will have to use an aggregate function and that is why you need to understand what aggregate functions are and when you can how you can use aggregate functions so that is why i have explained each of the different types of aggregate functions sum average min max and count and also i have mentioned you can use aggregate functions without the group by clause that i have mentioned here and also you can use the aggregate functions with the group by clause so you would find both examples here and also you would find some some explanations to these aggregate functions so do check that out and do be familiar with aggregate functions because i think that if you're attending an sql interview a question on aggregate function would definitely arise okay so let's move on to the next question and that is going to be the question number eight and that is what is the difference between rank dense rank and row number window function now i can guarantee you one thing if you are entering an sql interview you will definitely have a question on window function because window function is so widely used for all sort of things on sql so here i have basically asked what is the difference between rank dense rank and row number and i have taken a particular table for example and then i have explained these three functions and then i have basically shown you a table where you would see the difference between what is what is the value of rank what is the value of dense rank and what would be the value of row number okay hopefully with this you would understand the difference between these three types of window function but i would say that you should not only learn these three window functions but also all the other window functions so so that is what i have mentioned here you have other window functions like lead lag queue list and tile last value first value and few others so i have already made separate videos covering all of these window functions in detail so you should be checking out my videos to completely understand all of these window functions you will find a link to that in this blog here so definitely prepare to answer some questions on window functions that i can guarantee that you would have questions on window function in an sql interview okay so let's move on to the next question that is question number nine and the question is can we use aggregate function as window function if yes then how do we do it now this can be a tricky question if you are not comfortable or familiar with window function so we know that we have aggregate functions like sum average min max count but all of these aggregate functions can be used as window function and the simplest way of doing that or basically the only way of doing that is just by using the over clause so here i have given you an example query that is this is the query select sum of salary from managers and you would see an output and then i have shown you how you can use that as a window function by just using the over clause that is select sum of salary over and inside over i have not mentioned partition by or order by because it is not mandatory you can simply use over and this will just convert or basically sql is going to treat your sum aggregate function as a window function so the result would be slightly different and that is what i have explained in this blog so definitely check that out okay so let's move on to the next question and that is question number 10 which says how can you convert a text into a date format consider the given text as 31-01-2021 okay now again this can also be a very common question that is a question on date format how do you convert a particular text to a particular date format okay so you should be familiar in using some functions that you can use to convert text to date format and what are the different types of formats that you can convert the date to okay now different rdbms would have different functions to do this and that is why what i have done is i have provided the solution in four of the most popular rdbms today that is oracle mysql microsoft sql server and postgresql so you will find a solution to all of these four rdbms in this blog so definitely check that out and definitely be prepared to answer some questions on some date formats okay so let's move on to the next question and the next question is the question number 11 and i'm going to read this question so the question says imagine there is a full name column in a table which has value like elon musk bill gates jeff bezos etc so each full name has a first name a space and a last name so the question is which function would you use to fetch only the first name from this full name column okay and give example so basically what i'm asking is try to fetch a substring or a particular uh string from a given text okay and this trust me this is so widely used when we do some data analysis or some data extraction or write some sql queries so this is very widely used so you should be familiar with what are the function that you can use to fetch a particular string from a given text and that is why i have included this question here now as again different rdbms would have different functions to perform this operation and that is why i have given my solution in four of the most popular rdbms you would find a solution for this question in oracle in mysql in microsoft sql server and postgresql now there is one thing to remember to perform the same operation you can use different functions as well i have just used one particular function which i just remembered instantly but of course you can solve the same operation by using some other functions and the same goes for date format as well so if you have a different solution to the same question then definitely mention your solution in the comments below i'll i'll definitely be interested in knowing your solution okay so let's move on to the next question and that is the question number 12 which states what are sub queries and where can we use them now if you're writing sql queries i can guarantee you that you would have written sub queries because i don't think so you can write any complicated or complex query without using sub query so you definitely have to use sub queries so you definitely need to understand what sub queries are so i try to explain in the most simplest of words what sub queries are and also have given you an example so definitely check that out and also know where can you use sub queries so you can use sub query in a select clause in a form clause in a where clause and also you can use sub queries in different statements like insert update delete etc so i have mentioned all of that in this block so definitely understand sub queries before you attend an sql interview okay so let's move on to the next question and that is the question number 13 which states is it good to have the same sub query multiple times in your query if no then how you can solve it so basically the question is let's say you have a very big query where you have a particular sub query which is used multiple times in your query so is it a good approach and the answer to that is no it's not a good approach to repeat the same sub query multiple times in your query and the simple reason for that is if you have the same sub query multiple times in your query then sql will have to execute the same query multiple times okay rather if you just try to replace all of these sub queries and put the subquery inside a with clause and then just replace the sub query with the but the temptable name of your with clause then it's going to really improve the performance and also it's going to help with the maintenance and that is what i have explained here so definitely check that out and definitely try to write some queries uh to understand this better okay okay so let's move on to the next question and that is the question number 14 which basically states what is the difference between wear and having claws now trust me this is a question that not only the freshers but a lot of experienced sql developers may also find it difficult to answer of course we will know like where to use having clause and where to use the where clause but trying to explain this to the interviewer might be a slightly tricky uh thing to do so i have tried to explain the difference between these two in the most simplest of words in this blog try to go through that and definitely try to write some queries to understand where you can use the having clause and where you can use where clause and what are the different purposes of using both of these clauses in sql okay so let's move on to the next question and that is the question number 15 which states what are indexes and why do we use it now i'm sure that if you're learning sql you might have heard of indexes and probably you know that in order to use improve the performance we use indexes but not everyone truly understand what indexes are okay so when an interviewer will ask you what indexes are if you try to explain index in the most simplest of terms then that gives you the best chance of impressing your interviewer and i can guarantee you that other than the freshers maybe even for freshers but mainly for the intermediate sql roles or for the experience or the senior sql roles a question on index is most definitely going to happen and it's how you're going to explain indexes it's actually going to be play a very important role whether you pass this interview or not so definitely spend some time in understanding what index is and i have tried to explain what index is in the most simplest of term so you may see that this answer is quite big but it's actually i've tried to explain it in the most simplest of terms definitely check this answer and let me know if you understand it if you want me to make a separate video on indexes i can do that in the future but for now just try to get the most basic understanding of indexes and uh definitely do not attend an interview without understanding what index is okay so let's move on to the next question and that is the question number 16 and the question is what are the steps that you would take to tune a sql query now this is not a question that an interviewer would ask a fresher but it's always better to understand how you can improve the performance of your query and i think if you are applying for a senior sql role then this can definitely be one of the must questions that you should be expecting to be asked by the interviewer now personally when i have to tune a sql query there are four steps that i follow and that is what i have mentioned here okay so the first step is i need to basically check the sql query so whatever the query that we have written there are so many ways that we can improve the query that we have written okay now if you are using very huge tables we can definitely make sure that we only consider the data that we need from this table before we join this table with some other tables now if a table has a lot of columns do we really need all of those columns we can reduce the number of columns now we can replace the multiple sub queries with a bit query and there are so many different ways that you can write and improve the sql query that you have that you have written so this definitely is the first step once you have made sure that the query is written in the best possible way that you can write then the next step is have you created indexes for all the tables that you are using okay or especially for all the huge tables that you will be using okay and are the correct indexes being created because there are different types of indexes which perform differently so you should be aware that you should be using the correct index for a particular table so that's definitely one thing so i'm going to check if indexes are created the third thing is i'll check if the statistics are generated for all the tables and if the statistics are up to date okay and the fourth thing is i'll check the explain plan now explain plan will basically provide you the step-by-step guide of how the execution of the query happens by the optimizer so basically you need to look at the explain plan and basically reading and explain plan is also a different task and that's something that you need to learn but you don't need to be an expert of reading explain plan you just need to understand the most basic things like is an index being used and what type of joints is happening so just by understanding most basic things of explain plan you'll understand what is going on and you can then follow these four steps once again to try to improve the performance of this query so that is basically what i have explained in this question okay okay so now let's move on to the next question that is question number 17 and that is what is the difference between primary key unique key and foreign key now this is the most basic question that can be asked so you should be familiar with all of these constraints now there are other constraints as well but i think these three are the most basic or most commonly used constraints and you should definitely be aware of constraints when you are going for an sql interview even if you are as fresher or if you are applying for an intermediate sql role okay so here i have explained you what is the difference between primary key and unique key and also explain you what foreign key is so definitely go through this in my blog now let's move on to the next question that is question number 18 and that is what is the difference between a weave and a synonym now my ultimate idea of asking this question is i want you guys to be aware of what are views and you should be aware of what are synonyms and what is the difference between them okay so i've tried to explain them here definitely we use views so many different times and we also use synonym a lot of times so you should know both of these concepts and you should kind of know what is the difference the difference is pretty simple actually but understanding what synonym is and why we use synonym is very important and understanding why why do we use view is also very important so definitely be comfortable with both of these concepts especially i would say views before you attend an sql interview okay so let's move on to the next question that is the question number 19 and that states when can a function not be called from select query now we know that in our select query we can call functions right like average sum min max or the window function these are all functions but these are all inbuilt functions that are provided by the database okay but then we can create our own functions which are user defined functions and we can call those functions in select statement as well but there is a rule when we create such functions so we cannot just call any function from select statement there is a rule that we need to follow and that is basically we cannot change the state of the database or we cannot change the data in the table in a function that is we cannot basically use the dml statements like insert update delete inside a function so let's say you have written a function which does some insert update delete some commits rollbacks etc then such kind of functions cannot be called from a select statement and that is what this question is about okay now let's move on to the next question and that is question number 20 what is a trigger now if you are attending an interview for a senior role definitely there is going to be a question on trigger and it's not just about telling what trigger is what are the different types of triggers you should also be familiar with is it a good practice to use triggers so try to get some understanding of triggers and be prepared to answer some questions on triggers especially for a senior role but for freshers and for i would say for a fresher role i don't think so a lot of questions about trigger would be asked but definitely you should you should know what trigger is and what is the difference between a trigger and a procedure okay there's one major difference that is a procedure is called by by us right we call a procedure we create a procedure and we execute the procedure ourselves but a trigger cannot be executed by us it happens automatically based on certain events that can occur in the database okay so you need to understand the basics of triggers and that is why this question so try to understand triggers and go through this question in my blog okay so let's move on to the next question there is a question number 21 and that states what is the difference between a weave and a materialized view now i already covered views in the previous question now there is another concept called as materialized wave and it's one of the most important concepts especially if you are attending a senior sql interview role but even for a junior sql interview role it's always good to know the concepts okay even though you might not have used it so what exactly is a materialized view and how is it different from view that is what you should be aware of and that is what this question is about so you would find an answer to that in this blog so definitely check that out moving on to the next question the next question is what is a merge statement now merge is part of the dml statement and it kind of does both update and insert based on certain conditions so definitely try to understand what merge is i have definitely been part of interviews where they asked me what merge was and also asked me to write the syntax of merge so definitely have some understanding of merge statements before you attend an sql interview okay so let's move on to the question number 23 and the question is which functions can be used to fetch yesterday's date okay so basically this is one of the question that was asked by one of my subscribers and i felt that this can actually be a pretty good question if i was taking an interview tomorrow this can actually be a pretty good question that i might ask not for a very senior role but maybe for a beginner or for an intermediate role so definitely check how you can do that now as i again for different rdbms you would have different functions to solve this question so basically what you need to do is you need to find what was yesterday's date so in order to find what is yesterday's date you need to first understand or find what is today's date so different rdbms have different functions to find today's date and once you find today's date you may have to use different functions to subtract one day from it to get yesterday's date and the solution for for that in oracle mysql microsoft sql server and postgresql is mentioned in my blog so definitely check that out moving on to the question number 24 that is what is the difference between a function and a procedure trust me i have been asked this question when i was a beginner in in my career so many different times and i have taken interviews where i have asked this question and trust me a lot of a lot of guys have not been able to clearly answer this question they will know one one difference but they may not know all the difference so definitely check what is the difference between a procedure and a function i've tried to provide three different uh differences here there may be more difference as well check that out as well but definitely one of the most frequently asked questions if you are a beginner or applying for an intermediate sql role okay now let's move on to the last question and the last question is what is pragma autonomous transaction now if you are beginner then i don't think so you would be asked this question but let's say if you are applying for an intermediate role or for a senior role then pragma autonomous transaction can actually be one of the most commonly asked questions now it's a very special concept in sql or in databases that will let a particular procedure to have kind of its own session okay so i have tried to explain this concept in the most simplest of terms you can see the answer is pretty long but i have basically given an example and tried to explain that definitely go through this and try to understand what pragma autonomous transaction is okay so finally i have used some tables and some example queries in this blog so everything you would find in this uh download script so you can just click on this download a script button to download the text file which will basically have all of those scripts so that's all guys these were the top 25 sql question that i felt uh is something that you should be aware of now it's not just about these 25 questions but also about all the concepts that were covered in this sql question so if you are familiar and comfortable with all the concepts that were covered in this 25 sql questions and if you are familiar with all the sql queries that have provided in my other videos then i can guarantee you that you will be able to clear almost any sql interview so if you found some value in this video then please make sure to subscribe to the channel and give me a thumbs up thank you and see you soon in the next one bye