Top 25 SQL Interview Questions and Answers(The BEST SQL Interview Questions)

Apr 03, 2024

Hi guys, I'm tawfiq. In this video, I'm going to share with you my top 25 SQL

interview

questions

that I came up with from my own experience using SQL over the last 12 years of my career. I have taken several SQL

interview

s and 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 a SQL interview or someone who is learning SQL, I highly recommend you to go through these 25 SQL questions and also all the SQL concepts covered in these questions and try to check if you can answer all these questions yourself.

I firmly believe that if you are comfortable with all of the concepts covered in these 25 SQL questions, then you should be in pretty good shape to clear any SQL interview. Now most of the SQL questions that I will cover in this video will be theoretical or will cover some SQL concepts. I won't cover much of SQL. queries and the reason for this is that I have already made separate videos covering the SQL queries that you should be familiar with to clear SQL interviews so I will leave a link to those videos in the description below or you should find it somewhere on the screen now well, so what I did was I wrote a blog on my website where I mentioned all these 25 sql questions and also the

answers

to these 25 sql questions.

More Interesting Facts About,

answers

you can check out my blog so I'm not going to cover all the answers in this video because I want this video to be as short as possible so I'm just trying to give you all the questions and concepts. that you need to prepare for a SQL interview, okay then let's get started, this is basically what my blog would look like.

Top 25 SQL Interview Questions, you would see an introduction and then you would find the link to this particular video and then you would have the questions, so the number one question is what are ddl and dml languages? This is the most basic question you can ask, especially if you plan to attend a SQL interview for a newer position or an entry-level position. 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 is very important to understand what is the difference between these languages and what are the different statements that are available in each of these languages.

Okay, so this This will be my first question and you will find all the explanations of these five different languages in this blog so that you can check that you have ddl dml dcl tcl in addition to dql and now let's move on to the question number. two, question number two is what is the difference between deleting and truncating the statement. Now, this may be one of the most frequently asked questions in a SQL interview. Now, this is a pretty simple question, but I've seen it personally when taking interviews when I ask. Not everyone can answer these questions with much comfort or confidence, so it is 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? Are the things you can do in a delete that you can't do in a truncate and why do people use truncate? What are the advantages? The main advantage is that truncating is much faster, so let's say you want to delete a table that you have, say. a million records, so if you try to use the delete statement it will be very slow, instead of trying to use a truncate statement it will be much faster. I haven't explained the details why truncate is faster than delete but this is basically one of the reasons and truncate is a ddl while delete belongs to dml language so there are some differences which I mentioned in my blog.

Check it out, so this is the second question. Now let's move on to the third question. The third question again will be a very simple question: why do we use case statement in SQL? Give an example so that the case statement is similar to the if else statement that you will find in any other programming language, although it may be very simple. concept, you wouldn't believe how often the case statement is used when we write SQL queries, so although it is a very simple thing, it is used so widely that you can expect to be asked a question about the case statement, so It is always good to be thorough with the case. statement when you attend a SQL interview, okay, let's move on to question number four, so question number four is actually a very important question: what is the difference between left full right outer join and inner join, Many of you.

I was also asked in the comments if I can make videos on joins etc. so I plan to make a detailed video on all the types of joins in SQL very soon, but I think if you are trying to understand what the difference is between the different ones. types of joins in SQL then I highly recommend you read everything I have mentioned in this blog now, basically when you go to an interview the interviewer can basically show you a couple of tables and then tell you when you do a left join. be the result and when you do a full outer join what would be the result then the question could be something like this, that's why I have presented here two different tables, I have shown you the data of these tables and then for each of join them, so I'm starting with the inner join.

I explained to them what inner join is and then I wrote the query and then I showed them the result they would get by doing an inner join and then I did the same. left join and then right join and then full outer join, so just by looking at these queries and the data written from this type of joins and also the explanation I have given you should be able to understand what is the difference between these joins now not only this I have also mentioned the other types of joins so self join what is self join some examples of that and then what is a natural join and then I also explain what is a cross join so if you want to get for a full understanding of joins, check out this blog and definitely if you are going to do a SQL interview you need to be thorough with joins and I think just looking at this blog and looking at all these examples and then trying to replicate all of them. these queries on your own system, I think you'll understand joins pretty well, so definitely be prepared to answer some questions about joins.

Okay, let's move on to the next question, so question number five is what is the difference between distinct and grouped now, this can also be a pretty common question to ask in a SQL interview. Now I think if you are quite new to SQL then you may get confused about what is the difference between distinct and groups because by using both you can retrieve unique records correctly but there is one important difference between distinct and groups, they are not similar in any way so which I have explained everything in this block so check that and definitely get a complete understanding of when to use distinct statement and when to use group by statement and why we use both statements.

Okay, let's move on to the next question and that will be question number six. Question number six establishes what are the rules to follow when using the union operator. Now it's similar. A question can be asked for any other operator, such as union, all intersect or less. Now there are certain rules that you have to follow when you want to join a couple of queries using any of these operators and the rules that I have mentioned here, you can definitely check them. answers on my blog so let's move on to the next one question number seven which is what are aggregate functions and name and explain the different types of aggregate functions now if you see most of these questions I have here are more than just questions . are something like the complete concepts of SQL, because now under this concept there can be various types of questions that an interviewer can ask, for example an interviewer could ask you to write a query where you have a table of employees and you ask you to count the total number of employees in each department now, to write that type of query, you will have to use an aggregate function and that is why you need to understand what are aggregate functions and when you can do it, how you can use aggregate functions, for I have explained each of the different types of aggregate functions sum average min max and count and I have also mentioned that you can use aggregate functions without the group by clause which I have mentioned here and you can also use the aggregate functions with the group by clause So. you will find both examples here and also you will find some explanation of these aggregate functions so check it out and familiarize yourself with aggregate functions because I think if you attend a SQL interview a question about aggregate function would definitely come up okay so let's move on to the next question and that will be question number eight and that is what is the difference between the range dense range and the row number window function.

Now I can guarantee you one thing if you are walking into a SQL interview. You will definitely have a question about the window function because the window function is widely used for all kinds of things in SQL, so here I basically asked what is the difference between dense range and row number and I took a particular table, for example, and Then I explained these three functions and then I basically showed them a table where they would see the difference between what is the range value, what is the dense range value, and what would be the row number value.

Well, hopefully. This would understand the difference between these three types of window functions, but I would say that you should not only learn these three window functions but also all other window functions, so that's what I have mentioned here, you have other window functions like lead. delay queue list and tile last value first value and some others so I have already made separate videos covering all these window functions in detail so you should check out my videos to fully understand all these window functions. You'll find a link to that. on this blog, so definitely be prepared to answer some questions about window functions.

I can guarantee that you will have questions about window functions in a SQL interview. Okay, let's move on to the next question, which is question number nine, and the question is: can we? use aggregate function as window function, if yes how do we do it now? This can be a tricky question if you're not comfortable or familiar with the window function, so we know we have aggregate functions like count max min average sum, but these are all aggregated. functions can be used as a window function and the easiest way to do it or basically the only way to do it is just by using the over clause so here I have given you an example query which is the query to select the salary sum of the managers. and you would see a result and then I will show you how you can use it as a window function by simply using the over clause which is to select the sum of the salary above and inside.

I haven't mentioned partition by or order by because it isn't. required you can just use over and this will just convert or basically SQL will treat your aggregate sum 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 it out, OK? Let's move on to the next question and that is question number 10 which tells how you can convert a text to a date format. Consider the text given as 01-31-2021. Well, again, this can also be a very common question. about date format, how do you convert a particular text to a particular date format?

Okay, so you should be familiar with 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 date to. Ok, now different rdbms would have different functions to dothis and that is why what I have done is provide the solution in four of the most popular rdbms today, which are Oracle MySQL, Microsoft SQL Server and PostgreSQL, so that you find a solution for everyone. of these four rdbms on this blog, so definitely check them out and definitely be prepared to answer some questions about some date formats.

Okay, let's move on to the next question and the next question is question number 11 and I'm going to read this. question, then the question says imagine there is a full name column in a table that has a 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 what function would you use to fetch just the name of this full name column okay and give an example so basically what I'm asking is try to get a substring or a particular uh string from a given text it's okay, and this, believe me, is widely used when we do some data analysis or data extraction or write some SQL queries, so it is very widely used, so you should be familiar with it.

What are the functions that you can use to retrieve 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 will find a solution for this question in oracle in mysql in microsoft sql server and postgresql now there is one thing you need to remember to perform the same operation you can also use different functions, i just used one in particular function that i just to remember instantly but of course you can solve the same operation using other functions and the same goes for the date format, so if you have a different solution for the same question, definitely mention your solution in the comments below.

I will definitely be interested to hear about your solution, so let's move on to the next question, and that is question number 12, which states what subqueries are and where we can use them now if you are writing SQL queries. I can guarantee you that I would have written subqueries because I don't think you can write any complicated or complex query without using subqueries so you definitely need to use subqueries so you definitely need to understand what subqueries are so I try to explain in the most words. simple what are subqueries and I have also given you an example so definitely check it out and also know where you can use subqueries so you can use subqueries in a select clause in a form clause in a where clause and you can also use subqueries in different statements like insert, update, delete etc. so I have mentioned all that in this block so definitely understand subqueries before attending a SQL interview.

Okay, let's move on to the next question and that's the question. number 13 saying: is it good to have the same subquery multiple times in your query? If not, then how can you resolve it? Basically the question is, let's say you have a very large query in which you have a particular subquery that is used multiple times. times in your query, so is it a good approach and the answer is no? It is not a good approach to repeat the same subquery multiple times in your query and the simple reason is that if you have the same subquery multiple times in your query then SQL will have to execute the same query multiple times okay, instead of trying to replace all these subqueries and place the subquery inside a with clause and then simply replace the subquery with the tentable name of your with. clause then it will really improve the performance and also help with maintenance and that is what I have explained here so definitely check it out and definitely try to write some queries to understand this better okay so let's move on to the next question and that is the question number 14 which basically states what the difference is between wear and tear and having claws.

Believe me, this is a question that not only newbies but many experienced SQL developers can also find difficult to answer, of course. We will know where to use the have clause and where to use the Where clause, but trying to explain this to the interviewer can be a bit complicated, so I have tried to explain the difference between these two in the simplest of words. In this blog, try to analyze that and definitely try to write some queries to understand where you can use have clause and where you can use Where clause and what are the different purposes of using both clauses in SQL.

Okay, let's move on. to the next question and that is question number 15 which states what indexes are and why we use them now. I'm sure if you're learning SQL, you may have heard of indexes and probably know how to use them. For performance we use indices, but not everyone really understands what indices are okay, so when an interviewer asks you what indices are, if you try to explain them in the simplest terms, you'll have the

best

chance of impressing your interviewer and me. I guarantee you that apart from newbies, maybe even for newbies, but mainly for intermediate SQL roles or for experience or senior SQL roles, a question about index will definitely come up and this is how I will explain indexes. play a very important role whether you pass this interview or not so definitely spend some time understanding what index is and I have tried to explain what index is in the simplest term so you can see that this answer is quite big, but Actually, I've tried to explain it in the simplest terms.

Definitely check out this answer and let me know if you understand it. If you want me to do a separate video on indexes, I can do that in the future, but for now just give it a try. to get the most basic understanding of ratios and definitely don't go into an interview without understanding what ratio okay so let's move on to the next question and that's question number 16 and the question is what are the steps you would take to fit a SQL query now, this is not a question that an interviewer would ask most recently, but it is always

best

to understand how you can improve your query performance and I think if you are applying for a senior SQL position, this can definitely be one.

Of the mandatory questions that you should expect 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. Well, the first step is basically check the SQL query, so whatever query we have written, there are many ways that we can improve the query that we have written. Well, if you're using very large tables, we can definitely make sure that we only consider the data we need. of this table before joining this table with other tables now if a table has many columns do we really need all those columns?

We can reduce the number of columns. Now we can replace the multiple subqueries with one bit query and there. There are so many different ways you can write and improve the SQL query you've written, so this is definitely the first step once you've made sure the query is written in the best possible way and then the next. The step is: have you created indexes for all the tables you are using? Is this okay or especially for all the big boards you will use? Is this okay and are the correct indexes being created because there are different types of indexes that work differently so you need to be aware? that you should use the correct index for a particular table so that's definitely one thing so I'm going to check if indexes are created and the third thing is to check if statistics are generated for all tables and if statistics are updated , okay and the fourth thing is that I will review the explanation plan.

Now the explanation plan will basically provide you the step by step guide of how the optimizer executes the query, so basically you need to look at the explanation. planning and basically reading and explaining the plan is also a different task and that is something you need to learn, but you don't need to be an expert in reading and explaining the plan, you just need to understand the most basic things, like whether an index is used and what kind of joins are happening, so just understanding the very basics of the explain plan, you'll understand what's happening and then you can follow these four steps one more time to try to improve the performance of this query, so that's basically it what I have. explained in this question, okay, now let's move on to the next question, which is question number 17, and that is what is the difference between primary key, unique key and foreign key.

This is the most basic question that can be asked, so you should be familiar with all 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 the constraints. when you go for a SQL interview, even if you are so new or If you are applying for a SQL intermediate role, it's okay, here I have explained to you what is the difference between primary key and unique key and also explained to you what is foreign key, so definitely check this out on my blog and now let's move on to the next question.

That's question number 18 and that's what's the difference between a weave and a synonym. Now my main idea of asking this question is that I want you to know what views are and you should know what they are synonyms and what they are. The difference between them is fine, so I have tried to explain them here. We definitely use different views many times and we also use synonyms many times, so you should know both concepts and know what the difference is. The difference is actually quite simple but understanding what synonym is and why we use synonyms is very important and understanding why we use view is also very important so definitely get comfortable with both concepts especially I would say views before attending a SQL interview.

Well, let's move on to the next question, which is question number 19 and which indicates when a function cannot be called from the select query. Now we know that in our select query we can call functions like the minimum, maximum average sum or the window function. These are all functions, but they are all built-in functions provided by the database. Okay, but then we can create our own functions which are user defined functions and we can also call those functions in the select statement, but there is a rule when we create such functions. functions, so we can't just call any function from the select statement.

There is a rule that we must follow and that is that we basically cannot change the state of the database or we cannot change the data in the table in a function that we basically cannot use. dml statements like insert update delete inside a function, so let's say you have written a function which inserts, updates, deletes some commits, rolls back etc. then those kind of functions cannot be called from a select and from statement. That's what this question is about, okay? Let's move on to the next question, and that's question number 20, what's the trigger now? If you are attending an interview for a high-level position, there will definitely be a question about the trigger and it is not just about saying what the trigger is, what the triggers are.

You should also be familiar with the different types of triggers. Is it good practice to use triggers? Try to understand triggers a little bit and be prepared to answer some questions about triggers, especially for a high-level position, but for entry-level and, I would say, a newer role, I don't think, there would be a lot of questions asked about the trigger. , but you should definitely know what trigger is and what the difference is between a trigger and a procedure. Well, there is an important difference: a procedure is called by On our part, we call a procedure, we create a procedure and execute it ourselves, but we cannot execute a trigger, it happens automatically based on certain events that may occur in the base of data.

Okay, so you need to understand the basics of triggers and whatnot. That's why this question is about understanding triggers and check out this question on my blog. Okay, let's move on to the next question. There is question number 21 which states what is the difference between a woven view and a materialized view. Now I've covered it. seen in the previous question now there is another concept called materialized wave and it is one of the most important concepts especially if you are attending a senior SQL interview position but even for a junior SQL interview position it is always good to know the concepts, although You may not have used it, so what exactly is a materialized view and how is it different from the view?

That's what you need to keep in mind and that's what this question is about, sowhich you will find an answer in this blog, so definitely check it out. That, moving on to the next question, the next question is what is a join statement. Now the join is part of the dml declaration and somehow it is updated and inserted based on certain conditions, so definitely try to understand what the join is. I've definitely been a part of interviews where I was asked what join was and also asked to write the join syntax, so I definitely understand a bit about join statements before attending a SQL interview.

Okay, let's move on to question number 23 and the question is what functions. can be used to retrieve yesterday's date so basically this is one of the questions one of my subscribers asked and I felt like it may actually be a pretty good question if I were taking an interview tomorrow it may actually be a pretty good question good. question you could ask not for a very senior role but maybe for a beginner or for an intermediate role so definitely check how you can do it now as again for different rdbms you would have different functions to solve this question so basically I'm doing it what you need what you need to do is to find what was yesterday's date so to find what is yesterday's date you first need to understand or find what is today's date so different rdbms have different functions to find date today and once you find today's date you may have to use different functions to subtract one day to get yesterday's date and the solution for that in Oracle MySQL Microsoft SQL Server and PostgreSQL is mentioned in my blog so definitely check that and move on to question number 24, which is what is it? difference between a function and a procedure, believe me, I was asked this question when I was a beginner in my career on many different occasions and I took interviews where I asked this question. and believe me, many guys have not been able to answer this question clearly, you will know a difference, but they may not know all the differences, so definitely check what the difference is between a procedure and a function that I have tried.

To provide three different differences here, there may be more differences and check it too, but it is 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 the autonomous transaction pragma now? If you are a beginner, then I don't think you will be asked this question, but let's say if you are applying for a mid-level position or a senior position, then autonomous transaction pragma can actually be one of the most frequently asked questions now.

It is a very special concept in SQL. or in databases that will allow a particular procedure to have its own session, so I have tried to explain this concept in the simplest terms, as you can see. The answer is quite long but basically I gave an example and tried to explain that definitely check this out and try to understand what pragmatic autonomous transaction is ok so finally I used some tables and some example queries in this blog so that all you can find in this download script so you can click on this download a script button to download the text file which will basically have all those scripts so that's it guys these were the top 25 SQL questions that I felt is something they should take into account.

From now on it is 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 these 25 SQL questions and if you are Familiar with all the SQL queries that I have provided in my other videos, I can guarantee you that you will be able to solve almost any SQL interview, so if you found any value in this video, be sure to subscribe to the channel and give me a thumbs up, thank you. and see you soon next time goodbye

Watch Video & Subscribe

If you have any copyright issue, please Contact