The 25 SQL Questions You MUST Know for Data Analyst Interviews

welcome to casa data vision youtube channel in this video we are going to discuss about the top 25 questions that keeps repeating in every interview now if you are applying for a data analyst role or a power bi developer sql is something which you will be tested initially every interview starts with sql so it is best practice to know how to answer and how to present the answers in an interview remember one thing sql will be the fuel where you can start impressing the panelists so the way you answer sql questions will keep up the

expectations and which will be taken forward so please concentrate on these questions and not only the questions how you have to present it that is very important no one is expecting a book definition or an internet definition everyone expects in terms of the work which you have done and the way you have presented to the panelist so what we have done in this video is we have categorized into four parts every interview starts with the basics questions and then they'll if you're answering everything they'll go to the next step if you're answering even that will

go to the advanced level and nowadays even during this uh offline uh like in the video meeting they're asking to share screen and they're asking to write the code so we will discuss about the few coding questions in sql as well so in this approach let's go with first basics the first and foremost questions you can expect in interviewers what is ddl and dml now these two are very key important factors to know it is defined as the data definition language and the data manipulation language now when you say the data definition language it becomes

the commands where you try to change the structure of a table you are trying to work with a structure of a table example create table drop table alter table so what exactly is happening in these three commands you're changing the structure that structural change is called ddl commands now coming to dml we try to insert the record we try to delete the record we try to update a record so we are playing with the data and not the structure in this case it is called data manipulation language and the one major differences you need to

tell is dml commands can be rolled back whereas ddl commands cannot be rolled back this is an important point you need to add it now in order to explain in the interview tell them that ddl is a structural change dml is a data changes and give example what are the commands you get in ddl what are the commands you get in dml like as i said alter create drop comes under ddl insert update delete comes under dml and then tell the most important difference that is rollback right now coming to the next follow-up question if

you have answered well or if you have missed some or the other point the next follow-up question will be what is the difference between either they can ask you truncate versus delete or delete versus drop or drop assist truncate so i will give you all three based on the questions you get please tell the answer now coming to the truncate truncate is the command which is a ddl command which cleans the complete table which cleans the complete table you're not going to delete either one by one the complete table will be cleaned it's a ddl

command and in fact if you go with a truncate the columns which you have already created that will never change only the complete table will get cleaned so you will not be able to clean uh delete one by one but you will be able to delete the complete records this becomes truncate but since it's a ddl you cannot roll it back this is our first point come to delete now delete is a dml command now what happens in delete is by using the where condition you can select which are the rows to delete and which

are the rows to exclude it so it's in your control either you delete the complete table by just excluding the where condition or by giving the where condition you can limit the delete statement now in this case the delete is a dml and can be rollback can be rollback if you delete also you can still retrieve it now coming to the drop the entire table along with the structure is dropped you can never get it back again because it is also a ddl once you drop it it's gone and in fact if you drop it

the table structure is also gone that was not in the case of truncate now i can say most of the sql code depends on group by conditions so always the data will be like one entry one transaction per one entry in a table that's how we store the data in the table now there could be a situation where you are asked you have been asked can you give me the total sales by country total sales by product total sales by customer in that case the group by becomes an added advantage code for us so what

you have to tell us what is a group by when is a group i used grupa is used to get the records aggregated at a feature level given example let's say we want to find out total sales made by each and every country australia this much india this much uk this much so if you want to get it in aggregated value we usually go with group by condition go by query now the next question you will get is what is the difference between a where clause and having clause now this is a very important question

that will be asked in almost every interview the way you need to answer is where condition can be applied to filter the data on the existing column of the table let's say in your table you have country you have time you have date you have location you can just write where location equal to india way state equal to andhra where state equal to karnataka you can start writing the filters but it should be on the existing field existing column now coming to the having clause having clause also filters the data but it will not be

on the existing column it will be on the aggregated column that you have implemented for example select country comma count of transaction this count of transaction is aggregated column this is a new column you are adding to the query by taking count this is an aggregated value for this if you want to filter it having is the only possibility you can use it so remember having is used to filter the aggregated column whereas where clause is used to filter the actual column if you tell the sentence you will be selected now coming to the what

are the aggregate aggregate functions and scalar functions if you see the functions like minimum maximum average sum count all this becomes aggregation either you're counting it or you're summing up when come for scalar the functions like extract i can call it as length you find the length of it will it will get it will calculate the length for all the all the values when you go with extract for every row you will be finding the extract so this type of functions are called scalar so give an example when you tell what it is now the

most important part is whenever you're doing any joints as such what happens when you're joining two tables we are joining a customer table with the order table suppose if the cust if the customer has not purchased anything if he has not purchased anything then what happens in the uh query all the values will be replaced by null because we don't have order now nulls are always disturbing for us so what we can do is we can use something called colas so what is the scolars function so cola's function will replace all the null value with

a default value called zero i know he has not made any sale that's why it is null so why you want to replace with null replace it with some number zero if it is zero i can clearly say it as this person has not purchased anything now why this function is required is let's say if the not purchase the sales made will be 200 300 null 200 300 null the null is nothing but this customers have not purchased if you take an average do you think you can calculate average for a null value not possible

so if you replace with zero definitely you can go and take a average so this function is very useful if you want to take an average for a column which is having null you replace it with the whatever default value you want and then you can apply the function so this is a coolest function now whatever questions has been discussed here this is a basic level once you answer this the next will be your the next level of questions this could be one step tough i mean say tough in a sense it could be the

next level to start with they'll ask you what are the constraints the consensus is something which you put restriction to the tables and columns now what are the constraints available you have primary key foreign key you have default you have check you have unique all these are the constraints now when they ask you about the consents start giving an example let's say there is a table and you have all the employee id it becomes a primary key and this employees could be part of many departments and that can be a foreign key now coming for

unique unique is just similar to a primary key but the only thing is it accepts null value the best example is you can give phone number phone number if you give it should be unique but is but what you can do is you can avoid giving it if you're not interested you can avoid giving it in that case what happens it can accept null values now whenever you give an example also please tell the important point primary key cannot accept duplicates primary key cannot accept null values foreign key will be a reference of a primary

key which can accept duplicates which can also accept null values coming to unique unikey is basically a key that will be again like a primary key but will accept null values the best example i can give is phone number now whenever you are giving an example that will create a good impression for the panelists they assume that you are worked on all these functions now the next question is a unique question that will be asked like how many primary keys and foreign key can be in a table you can have as many as unique in

a table but there can be only one primary key in a table and that primary key can be a combination of a single column or it can be a combination of two columns that is up to us it can also be called as a composite key but remember one thing there can be only one primary key in a table but you can have n number of foreign keys you can have n number of unique is so remember this point this is very important now coming to joins so joins are the major concept of sql definitely

this will be tested in every interview suppose you want to get the data from multiple tables so joints is the only way you can achieve this now what are the types of joints available we have inner join we have left join right join full join cross joint self join these are the five joints which you will be having now whenever you explain joints uh my request will be try to explain with an example suppose if i was a candidate i'm giving an interview i'll take an example of customer and i'll take a order as a

second table now if i want to see the records who has actually purchased it i will go with the inner join so what inner join will do in a join will combine two tables based on a common column now by using inner join i may miss some records i may be not be able to get the records which are there in a table or the records which are there in b table sometimes you may not at all get it why because you are concentrating on only on the customers who has purchased which is the matching

records will come that's a inner join now the second case let's say i want to display all the customers those have purchased it let them get the order id those have not purchased it forget it i can get null values in this case the preferred join will be left join so what is the left john definition the left join will display all the records from the left table and the matching records from the right table and the rest of the values which is not present in the right table will be replaced with null value now

coming to the left right join this is exactly the opposite when you go with the right join all the records from the right table will come the matching records of table left will come and the rest will be 0 when i say 0 it will be null you can replace it with 0 as well now coming to the full join so full join is a very unique way where you can get all the information from table a all the information from table b and all the information regarding the matching records you can achieve everything in

full join but usually this is something which we avoid in the application because the table size keeps on increasing and coming to the fourth join which is a self join self join is responsible for when you are joining a table with the same table you have only one table you are applying a join on the same table it becomes a self join and one more join is there which is cross join cross join will be or a cartesian join will be the combination of left and right for every record of table a with one record

will be matching with all the records in table b again this also leads to a multiplication factor and your table size will increase so these are the joins now whatever i was speaking i was speaking in terms of tables different tables but now what i can do is when you're applying joins it can be a different structure one table can have 12 columns one table can have five columns one table can have two columns never mind how many columns are there but there should be a one common column between all these tables to apply joins

now let's say about union and union all suppose you have a data quarter one data and you're appending with quarter two you're appending with quarter three or i can put it as a january records combining with february records combining with march records so i have a fixed tables that i want to put in everything into one table row by row remember this row by row in that case i will be going with union and union all now when they ask this question you can say it as union and union all performs exactly the same operation

by combining the table records row by row union will actually combine two or more tables by removing the duplicates whereas union all will include all the rows including duplicates then this is the first difference and coming to the second difference you should also say that union all performs faster than union the reason behind this for union there's one extra functionality that has been added by removing the duplicates so it will take some time so the union all will give the faster in performance because it is just clubbing the table without even checking for duplicates and

you can also sell tell us what is a condition required to apply this union on union both the table should have equal number of columns and the data types of the columns also should match you can see this now coming to the order of execution this is a very important question you will be having select condition order by limit group by all this you would have used now how the order executes this first line the first code will execute is from like from table will be there right that will be executed first because you need

to first extract where is a table so from will be executed then where condition so where conditional sense you apply any filters that will be the second condition that will execute then comes by group i and having if you have used any group i are having in your class that also will get executed and finally select will come whichever column you want to select that select will come and if you want to sort it order by and then limit so i repeat the order of execution will be from where group by having select order by

limit limit is basically trying to limit the trying to limit the records now this is a very important questions nowadays uh the panelists are asking in a tricky way how many joints is required to join eight tables okay some people say yes four no it's not four if you want to add the complete tables involved for two tables you can write one join for three tables you can write it two joints for four tables you have to write it three joints so what exactly i'm doing i am just doing n minus one so i'm just

doing n minus one so if someone is asking eight tables you can this tells us seven joints if someone is telling i want to join 987 tables so you will be telling 986 joints so always it is going to be n minus 1 so remember this this is very important now this is about a mid level now once you answer this as well you can also been asked some of the advanced some of the advanced functions as well now they will ask you have you worked on any window function or analytical functions so what is

window functions in a complete table you are working on a certain rows and certain columns that becomes like a window you're applying some functions for this window it is called window function or it can also be called as analytical functions functions like row number rank dense rank lead lag all this becomes a window functions now post this if you if you say that i worked on it they'll start asking the question what is the difference between rank and dense rank this is a very important hundred percent question that will be asked now let us consider

if there is a marks that has been given to the student let's say 99 98 98 97 so this is scored by student 1 student 2 student 3 student 4 the rank function gives us 1 2 2 4 this is a rank that is given to this score now if you see in the rank function the rank 3 is skipped why because 2 people have scored the same marks so the rank is skipped this is what rank function will do now coming to the dense rank one two two three so even though two people are

getting the same ranks the next rank will be the consecutive number this is a major difference please remember this okay now they may ask you to explain with an example take a simple example of four students four marks where two people having the same marks and explain this now what you can expect is the next question what is view how it is different from a table now slowly they'll be going into a bit of next level so view is a virtual table which will not store any data but it will store the query so sometimes

you can all you cannot always go and load the physical table because of the size and because of the limitations of the data storage so sometimes we may go with view so view will be a query storage it will not store the data but you can use it as a table this is a one major difference now this is something you can tell it in one line with an example just tell it as a table where you have order table customer table and i'm creating a view by selecting the rows and columns and i'm creating

a query that query will be stored as a view whenever you run a view select start from table or select start from view that record the query will get executed and will display the results for you now what is index i'll give a simple scenario let us say there is a book in a book if i ask you something what you will do you have to go and search on every page if i tell you one topic you have to search in every page so what happens here you're utilizing more time but what if you

have an index of a book you can clearly note down to the topic you can exactly go to the page number 20 or page number 40 or page number 60 depending on which page you want to go index is very important in terms of searching the same applies to a sql as well if you want to search something it will go and search in every row it is having one million rows it is having one crore it is having 10 million it can be anything it will go and search every single record it is going

to full scan which is going to take time but what if you create index so if you create index it will get to know in which part of the index the data you're searching for is present only in that part it will go and search this will actually avoid the full scan which will help us to give the record in the faster way index is a database object that can be created on a column in a table which will be helpful for helpful for fetching the data in a faster way now what is cte so

ct stands for common table expression let us say you are applying some logics and the logic is bit complex so what you can do is applying a logic you'll put it into one temporary table and that temporary table again you'll use it like create table table as write some logic again for the next statement create table table as write some logic and finally you will join now what the what the cte will do is it will try to avoid all these tables for an intermediate storage and we can write one virtual table starting with width

with t as write some code with t1 as write some code so this will be within that session and you can use it for your bigger implementation so ct is like common table expression which will help us to solve the code by writing it simpler rather than making it complex this way you can explain it now so far i've told what is sql it's a structural query language which we have many types of questions now if you compare it with java.net or python have you ever seen we are writing a for loop while loop if

conditions in sql if you have not seen then you know only sql there is a concept like pl sql where you can use it as a programming language as well you can start writing your for loops while loops if conditions every logic running implemented that is nothing but pl sql they will ask you what is the difference between sql and pl sql sql is like basic commands where we're trying to write the code line by line whereas in a pl sql the code will be executed in a block of code all the logics can be

written in a block this is something which you can say is as when you're talking about procedures functions all this comes out as a part of pl sql now this is all the advanced and they may ask you one um like use case of cross join cross one is like a cartesian product the best example i can give is let's say t-shirt you have three different sizes like large medium or small and you have three colors red blue green so what are the combination of t-shirts you can have you can have large with blue color

large with green color raj with red color again you can have medium medium with the blue color medium with the green color medium with the red color so for every color you can have three sets so it becomes nine combination you can give this example crosstown is a cartesian joint of a two tables the best example i can give is if you want to have a pair you can go with this crosstine now this is about the advance finally sometimes nowadays they are asking to do a screen share and they're asking to write the code

these are the most repetitive ask questions how do you find the third maximum salary this is possible in many ways but i would prefer to use rank or dense rank by using the rank condenser rank based on the salary you will be getting a rank one two three four on top of this write a sub query and filter this rank equal to three you will get this n number of ways to do it but i would always prefer to use window functions because that has advanced functions which will help us to do now how do

you find the duplicates and how do you remove it you have a very good way to find out first find out in a table what are the duplicates how do you find out group by any id comma count of star from table having count of star greater than 2 it will give you all the duplicates once you get the duplicates it's very easy to delete by just using a in condition you can achieve this this is also important now to come to a next question top 5 states this is you can do it in two

ways one is first take select state comma total sales sum of total sales group by table order by sum of sales you'll get all the total sales made by each and every state but you will get everything put limit five you'll get top three since you're ordering in descending order the maximum will come first you can achieve this this is one way to do it but also here also i would try to prefer by using dense rank or rank function which will rank based on the sales and then on auto query you filter it rank

less than or equal to 5 that is possible now coming to display alternative rows so nowadays they are asking this tricky question it's very simple you take any id column put a modulus when you apply modulus if it is equal to zero it's an even number if it's not equal to zero it's odd number alternative records you can achieve it this is very simple you can achieve it now this could be a date column example like they may ask you who joined last year who joined this year who joined this month simply you can go

and check which is a date column on the date column we have a very good functions like extract functions i'm just using my sequel in mysql you have extract functions you can extract it and you can filter out which month you want or this month uh the current system date minus 30 will give the last 30 day if you they ask you last six months just mine minus by 180 all these functions you can use it just one example if you see this will be a clear question for all the coding questions we will start

creating a video one by one in the next few videos so please subscribe so that you get the notification of all the code i'll explain you every code in a simple way how to achieve this these are all the most repetitive questions so we will create a playlist for this now coming to the last coding uh question which is very frequently asked they may ask you in a table you have a manager information you have employee information that everyone will be having id and everyone will be having an employee name and they will be associated

with manager id manager name is not present manager id is present but that manager is also an employee in this case the best way to do it is you can go with the self join by using the self join you can achieve this it's very easy you have to just join manager id of one table with the employee of the same table you will get that now these are all the questions that will be asked in the real time okay in the interview so take a look at it try to start with the basics go

in a good direction explain them with an example intermediate advance and coding structuralize this in your preparation that will be your clear cut to clear the sql interview in a 30 minutes interview they may give you 10 minutes for sql utilize that this is where your interview starts with crack it with a good confidence and it will be a very good impression if you are answering well in your sequel if you're answering well in sql then almost 30 to 40 percent of your interview is cleared then and there presentation is very important if you explain

in the same way which i did definitely will get selected we will be uploading more videos please subscribe if you have not subscribed and we will meet in the next video thank you all you