Data Analyst Interview Questions I SQL Interview Questions | SQL Interview Queries

hello all of you welcome back to my YouTube channel so in today's video we are going to discuss about data analyst SQL interview questions so if you are attending any of the data analyst interview so in that interview some questions might be asking or there might be a conditions or they they will ask you to write queries so let's see what are the queries which might ask in the data analy interview for freshes so you can see this is the first question so uh the question is list all the matches between team if matches are played once so uh see the input so the input they have provided is team so the DAT Ty has been provided that is workare or character so the these are the teams India Pakistan Australia and England so we want a desired output which has been shown in this table so what is the desired output they have asked us to list all the matches between teams if matches are played once so Australia now you can see the Australia is playing with England Australia play playing with India Australia playing with Pakistan likewise now again England versus India England versus Pakistan and India versus Pakistan so this is our desired output so let's see how it works so let me take you through the Oracle developer application so this is the application so as per the conditions they so they will provide you the table so this is the table from this table you have to prepare your table in this application so in Oracle application you have to prepare the table you have to create the table so let's create a table so so I had written a query like create table name of the table is match and column name team and its data type is Vare and size is 50 so if I fire this query the table will be created then so the I will show you the original table so this is the table and column name is team so that's why I have added column name as team now after firing this the table will be created let us fire the query so if I fire the the query yes okay the table is already exist so let me drop the table first so drop drop table match already I had created the table so that's why it is not allowing to allowing me to make the table so yes the table is dropped now let's create a table so create table table name is match and team this is the column so if I run this Square you will see the table will be created yes now table is created now let us insert the values so what are the values so let me take you the original table so this is original table so input is India Pakistan Australia and England so let us insert the values so insert into table name that is match values so first value is India likewise second value is PK then third value is Australia and fourth value is England so let us run these insert statements one by one so I first insert statement run then then the row is inserted second again the row will be inserted yes third you will see the message on the console yes now the row is inserted and last for England also yes the row is inserted now let's view the data from this particular table so if I select star from match all the data which we have inserted is successfully inserted into the table India Pakistan Australia and England so this is our desired input now let's work on the output so what is our output so we have to list all the matches between the teams so Australia will be playing with England so all the let's uh work see the input table so in the input table let's consider India so India will be playing with Pakistan Australia England so this is the output of now second Pakistan will be playing with all the rest of the three teams that is India Australia and England so likewise we have to generate the output so you can see in this output sample output has been given here so total number of rows are 1 2 3 4 5 six so in six rows all the matches can be possible so let's work on the this particular query so what we are doing in the first case so first we will create a city so City common table or we can say it is a temporary table so CT I'm creating so with CTE as so CT name is city as select so in this case we will add row number for this part particular table because you can see the output was there was only the team column so there was no any row number so we will generate a row number so how to generate row number we have in build function that is rore number this is function over into bracket we have to write order by team so we have only team column so we will order the data by team as team number so this will generate the row number then comma and we have to write team means we are fetching the team so let let's run only this inner query so if I only City okay so from match so this table name is remaining so from match I am considering this also okay or if I run only the city then you will see that row number is generated unique row number will be generated for each row so this is this was our original table and if I use the this particular query the query which is written in inside the city it will generate the unique row numbers so we are generating the row numbers now let's move further now what we are interested in we will use the self join now what is this self join let's see so next statement will be select star I want to F all this data into this next statement select star from CTE that is this particular CT a this is aliah's name comma CTE B Alias name where a. team number so this a. team number is not equal to B do team team uh B do team number so this I am joining this particular table with this same table so self joining I'm using self jooin concept in this particular case so to F the data I'm using the self jooin so where condition a. team number is not equal to B.T number means Australia will not play with Australia England will not play with England so this is the case I have specified so let's run only this part and see what will be the output so if I run this part you will see the output yes now team number see this is our original uh row number now after joining self join you will see that Australia let me maximize the window now Australia is playing with England Australia is playing with India Australia is playing with Pakistan now again England is playing with Australia but already it has been played with Australia see see the first case already it is playing with England and Australia now again this case is repeating so this is the repetion or we can say duplication is generated so again let's move further England India England Pakistan likewise India again it is playing with Australia already it has been played with Australia you can see here India versus Australia see here India versus Australia has been played again India versus Australia again it is showing here so this should be removed so these are the duplicate records so again we can remove this now observe carefully that consider this as a table number one and this as a table number two in self join so this team number these These are the row numbers for this table number two these are the row numbers now observe that the values which are repeated you can see here Australia versus India so this team number is one and India's uh row number is three likewise if I move further India versus Australia you can see in this case now team row number is three and row uh row number for this table is one so if we write if you write row number of this table is Le uh it it should be greater than if I write condition or we can say it is less always less than if I write any one of the condition if I write suppose if I'm writing this row number of this table is less than this row number then this particular record will not come see I had written I had written here and a do team number so this a do team number should be less than b do team T number right means see here now Australia versus India is playing here whose row numbers are 1 and three now this is allowed because I have specified your team number a do team number is less than b do team number now if same case is happening means India versus Australia again the match is taking place this should be avoided so this can be avoided by using this condition that is team a number is less than Team B do team number so this is the smaller value so this will not come in the output so that's why I have specified this particular condition so let's run this query so if I run this query so this is our query so if you run this query we will see the desired output that unique records will be fed so Australia is playing with England Australia playing with India Australia playing with Pakistan finish then England is playing only with India England is playing with Pakistan already it has been played with the Australia now that record will not come now next last India is playing with Pakistan and rest of the teams India has been played with the rest of the team so that's why only one record has been ped so these are the unique records this is how the output will come by using this query now let's see the question so this was the question but they have asked us to write only team number a and team number B so let us remove the row numbers that is team numbers these two team numbers we want to remove so we can write simply I have already written here in the comment section so I'm cutting this I'm writing instead of star I'm only using a a do team b. team so we can write alas so as teamore a and b. team as teamore B so team a and team team B so if I run this all the query together you will see the desired output so team a and Team B so Australia all India Australia Pakistan India all the matches are taking place in this way so I hope this question number one is pretty much clear so let's move on to the next question that is question number two now in question number two they have asked us to write a query to get the output so this is the input so you can see in the input the integer ID is given and name of the employe is given so desired names like EMP 1 2 3 4 till eight has been given and employee ID is also given now what is our desired output so the desired output should be that 1 emp1 comma 2 emp2 so what this is doing is this ID and name is concatenated you can see here 1 emp1 so if you see in the input data one ID 1 and emp1 that is name is called concatenated that is combined so one emp1 and it is separated by the comma and second row is taking that is 2 emp2 likewise 3 emp3 comma separated 4 emp4 so likewise the grouping is done and groups number of groups the group numbers has been given here so let's see how we can get the desired output so let me take you through the Oracle application okay so this is our application so here here we have finished the previous query so let me uh copy the question okay so this is a uh second question so write a query to get to get the output so first what we need to do we have to create this particular table I hope you know the table creation syntax so already we discussed in the previous query so I'm directly creating this uh table so this is the syntax of creating the table so int ID name that is Vare so likewise I have inserted all the data so like EMP 1 emp2 emp3 likewise till EMP 8 I have inserted all the records so let's view the table that is EMP 99 so if I view the data you will see this is the input right now next let's see what how to uh get the desired output again in this case we will be using the CTE concept right so with CTE that is name of the city as into bracket select so what I want to select ID comma name comma now let's concatenate this ID and name together so again to concatenate we have syntax like you can see ID I want to concate concatenate this ID with name but in between I should have the space so you can see the concatination symbol this is vertical concatenation symbol you can see the two symbols has been added here then in the single inverted commas I am applying the space so to get space between two strings I am using the single inverted commas then again the same sign of concatenation and name of the next column so if I use this this one and emp1 will be combined together or concatenation of these two columns will be taken will be taking place as combined so this is Alias name you can write as here as combine this is Alias name so the combine column will be generated then enti function now what this enti function will do so this enti function Will Group the vertical columns into a group so what I want so you can see here there are eight records eight records are present so I want to group so let's let me take you through the question so what is the question we are interested we are grouping all the eight records into the four records that is we are grouping in each row we are grouping two employees right so that's why I have used the enti function so ntile function Will Group the data together so I am specifying the number as four as our total number of data is eight if I use the four it will group all the data and it will fetch only four records it it will group in single record it will group two employees so likewise it will group so see the synex of enti enti 4 over order by ID so I'm ordering this by ID as groups so alas name as groups from EMP 99 so let us run only this part so if I run only this part you will the desire output so see the original ID name and after combining or after concatenation concatenation you will see that one space emp1 is generated likewise second two emp2 is generated so this is how the concatenation of the string is done or the columns is done now see the enti what the enti has done antile has grouped all the records in the you can see these are the two two records has been grouped together so employee one and employee two has been grouped together in the one group then second Group Employee three and four has been grouped in the second group likewise 67 is grouped into the sorry 56 is grouped into the group number three so likewise the function works now let's move further now again we will be using the list tag method so what does this list tag will do now in this case what is our requirement let me show you the question so this is our requirement that vertically I want to combine the two RADS so one emp1 comma 2 emp2 so to combining vertically combination of the to achieve the combination vertically we are using this list tag function so list tag into bracket combine now this combine column which which we have created using CTE we will consider in this particular list tag method so this combined column I have considered and in single inverted commas I want I want comma sign so let me take you through the output so this is our desired output so how the output should be 1 emp1 and it should be separated by the comma and second employee so that's why I have specified here comma so it will combine the two vertically all the employees together so list will combine all the vertical employees together with comma separated so next within Group aler by combine so Al by again the combine this combine column as all groups comma groups so this column I again I want to print from CTE so let's run this only this part let's see what would be the output okay uh it will not show okay fine so again Group by Group by groups so I want to group all the data by the groups so let me show you the previous city data so this was our city data yeah so we are grouping all the data by group so what will happen here group one is having two emplo employees so it will group together in the single group that is one it will fetch only one record and it will combine this emp1 and emp2 by using the function that is list tag similarly in second group emp3 and emp4 will be grouped together by using this list tag and it will be separated by the commas and order by again by groups so 1 2 3 4 will come so if I run this Square together you will see the desired output like this so all groups so One EMP 1 comma 2 emp2 and group one so let's see what is the desired output required yes so this is our desired output mentioned in the question that 1 emp1 comma 2 emp1 and this is groups these are the groups 1 2 3 4 so I hope this part is pretty much clear so this is our desired output required for this particular query so these are the questions which might come in your data analyst interview so most of these questions are asked for product based companies so that's all for this so we'll meet in the next video if you have not subscribed to my Channel please do subscribe and all the material regarding this video I will provide in the I will I will provide the link in the description box so you will get all this code and all this material or this questions tables in the same link so thank you all and have a wonderful day ahead

Channel: Learn with Pam

Views: 4,466

Keywords: Learn with Pam, sql interview questions, sql interview questions and answers, data analyst interview, data analyst interview questions, data analyst interview questions and answers, interview questions sql, data analyst interview questions for freshers, sql interview questions for freshers, sql interview questions for experienced, sql interview questions and answers for freshers, sql interview, data analytics interview questions, sql interview questions for beginners

Id: XDD4yhZeHro

Length: 19min 22sec (1162 seconds)

Published: Tue Dec 26 2023