 Hey guys, welcome to SSUniTeX, so see you on this side and this is continuation of SQL Server interview questions and answers. So today we are going to see one more interview question. So basically recently one of my friend has attended interview with the bold technologies. So the first round was a written interview. So here we are having the question paper that was shared by bold. So the time duration was 30 to 40 minutes. And here we are having few questions which is related to the SQL Server and Power BI. So basically as you can see the first question, so it is saying like using given data table answer the following. So here the first table that is staff table and it is having employee ID, name, manager ID, joining date, salary and department ID. And we are having few reports on this table. And we are having the second table that is bonus table. So bonus table is having three columns. First is the employee ID. Second is the bonus date and third is the bonus amount. And if you can see staff table and bonus table. So the employee ID is the primary key. We can join by using the employee ID between these two tables. So our first question is write a query to list employees and their salaries in descending order of their salary in each department. So what it mean? As you can see in this table, we are having this department. So this department is like marketing, insurance, marketing and services. So we want to see the number of employees in the descending order as per their salary. And the partition that should be on the department. So how we can write the query? Let's go to on the SQL server and we will try to write the query to achieve the output. So go to on the SQL server. So here as you can see I have already created these two tables if we can execute. So we are having the same data as we seen in the question report. So let me try to write the query. So for that we need to use the row number. So first of all while we are going to use the row number to create the sequence as per the descending order on the salary and we do the partition by on the department ID. So it will create a sequence and after that we can do the order by on that sequence. So how we can do that in the real time? Let me try to write the query. So select from our table that is a staff table and here we need to use the row number. So row underscore number and here we could use the over clause and inside the bracket we need to use the partition by partition by on the department. Next we need to use the order by clause so order by on the salary and it should be on the descending order and we can call this as a row number. So if we are going to execute this then we should see for the insurance we are having like you can see 3 lakh then again 3 lakh so it is having 1 then 2 then 3 then 4 then 5 like that so we have created this Rw column. So now we need to do the order by on this Rw column. So for that we could put this inside a CT and outside this CT we can directly select the data from this CT directly. So select ASTIC from this CT. So everything is good now let me try to execute this query. So here it is good now we need to use the order by so order by if we are going to use the order by on this Rw only so it will not going to return the output as we are expecting. So here as you can see it is doing the order by on this Rw but here on the department you could see insurance then marketing then services. So in the order by first of all we have to use the department and after that we need to use the Rw now let me try to execute it. So now here you can see the insurance so it is starting with the highest salary then going down you can see the lowest one and after that once your department is changed then again it is going to see the highest salary then lowest salary then lowest salary and once your department is changed so it is going to reset your Rw value and seeing the data on the descending order. So this is our first question go to on the question revenue and we will see the second question. So in the second question it is saying like get the employee IDs of those employees who did not receive the bonus. So as you can see inside the bonus table we are having the employee IDs who received the bonus. So as you can see ID 1 2 and 3 has received the bonus and in the staff table we can see we are having the data for 4 5 6 7 8 up to 14 so those did not receive any bonus. So we need to write the query to get these employees so this is very straight forward query. So here we can directly write the select statement like select ASTIC from your staff table where employee ID not in the bonus table and here we can directly write select EMP ID from bonus table. Here we should not have this underscore I guess now let me try to execute this. So here we can see we are having all these employees so in the select statement we should select only employee ID now we can execute it. So now we are having list of those employees who did not receive the bonus. Now move to next question so go to on the question people go to down. So here we are in the second question so using below data answer the following questions. So here we are having this transaction table. So in the transaction table we are having the transaction ID, date, product, agent, team, sales and unit price. Now go to on the down and we will see about the questions. So your first question is suggest some visualization to show business growth. So this question is related to the Power BI. So for that we could use the line chart or we could use the column chart or we could use the bar chart so we can do the comparison between the years or between the agents as you can see and we can also check between the teams. So for that we could use these three visualizations. Move to the next question. So in the second question it is saying like suggest some visualization to show what are the problem areas which can help to improve the revenue. So revenue first of all we need to check what sales we are having and as per the sales we can calculate the revenue. So for that again we can use the column chart we can use the bar chart so we can do the comparison between the years like how many sales we had in the last year and how many sales we have in the current year. So accordingly we can plan and increase the revenue of our products. Now move to the next question and that is on the SQL server. So here basically it is saying three traders are engaged in the stock market. Their monthly profits in lakhs are as follows. So here we are having first this fact trader profit table. So here we have this year and month and next column is trader one trader two and trader three and we are having the second table as well that is the mapping table which is going to indicate the year month with the quarter mapping. So here we have the quarter one two three and four so this is the mapping table. So now move to the question. So our question is saying with the provided month to quarter mapping so this is the second table write a query to display the year wise profit of those traders who have made the loss in any quarter. So first of all we need to calculate the quarter wise profit and after that we need to fetch only those traders those are having any loss in any quarter and after that we need to calculate the year wise total profit of those traders. So this is like three step process first we need to get quarter wise profit of the trader. Second we need to see only those traders who had made the loss in any quarter and third we need to do the total profit of those traders only who had made the loss. So now go to on the SQL server and we will try to write the query. So here basically I have created these two tables first is the mapping table. So as you can see we are having year month and quarter over there we have the second table and this table is fact your trader profit table. So now first of all we need to do the join between these two tables and we need to get the quarter wise profit for the traders. So let me write the query like select and after that from your fact profit table and let me call this as a and here we could use the inner join and in the inner join we could select this mapping table. So on the mapping table we could have this as B on A dot year month should be equals to B dot year month. So that is fine and here B dot quarter put comma and here we have the sum of A dot trader 1 put comma sum of A dot trader 2 put comma sum of A dot trader 3 and we can also have the alias name for these three traders like trader 3, trader 2 and here we should have the trader 1 as well. So we are going to use the aggregate function so the quarter that should go on the group by clause. So this is the first step like we have calculated the quarter wise profit for the traders. So here as we can see in this data we are having the minus on this trader 1 we have the minus on this trader 2. So we want to see the sum of trader 1 and sum of trader 3 only. We don't want to see the data for the trader 2. So how we can do that? So here first of all we need to write the query by which we can get the trader 1 and trader 3 only. So how we can do that? Let me put this in a CTE so we can call this as with CTE as start the bracket and here we can close the bracket and after that here we need to write the query. So in this query like we need to check select from the CTE where your trader 1 value is less than 0. So it is saying we are having the loss. So we could have the T1 over here. Now let me try to execute this. So it should have the T1 because we made the loss on the T1. Now we need to get the Erwise Profit. So how we can get that? So for that we can write your set. Select a statement like select then T1 as trader put comma then sum of trader underscore 1 as profit from your main table that is fact trader profitable. Here we need to check where your T1 in we need to put this inside the square bracket. Now let me try to execute and we will see. So here like we are having the trader 1 with the profit of 38 lakh. So what it means? So basically first of all we are going to check if we are having any loss in any particular quarter. If we do have then only we need to do the sum of that trader only. For the same we need to do the trader 2 and trader 3. So this should be for the trader 2. So we can have the trader 2 and it should be T2 and here it should be T2 here it should be T2. Now let me try to execute this. So as you can see we are not having any loss in the trader T2. So that is why here you can see the none the same thing we need to do for the T3 as well. So here T3, T3 here we should have T3 and T3 and at last it should be T3. Now let me try to execute this. So here everything is good as per our expectation but we do not want to see the T2. So for that we can put this in the CT2 and we can call this as after that we can start the bracket. At the last we can end with the bracket. Now select as stick from CT2 where the profit value that should not be null. Now let me try to execute this. So here we are having the trader that is T1 and the total profit of the year that is 38 and the T3 trader and the profit is 19 lakh and T2 is not having any loss in any quarter. So that is why the data of the T2 is not available on this query. So this is little bit complex. So you can do the practice on your own and if you will have any doubt then you can directly do the comment I will try to response. So these are the questions available on this question paper. If you need this script it will be available on the description of this video. So you can directly copy and try to do the practice if you want. Thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much see you in the next video.