Real Page, HYD - SqlServer/MSBI Interview Questions asked by Companies

I have started posting a series of sqlserver/MSBI interview questions asked by companies.

I attended Real Page Software Company’s interview recently.  I have given all the questions which I faced during interview. I will provide answers to the below questions shortly. Please keep on visit this page for more updates.

Happy Job Hunt!! ALL THE BEST!!

1.        Tell me about yourself?

2.        What is the view? Disadvantages of the view?

3.        Difference between Delete and truncate?

4.        What is cardinality? Why is it used? What is the need of setting the cardinality? Any specific reason?

5.        What is the use of trigger?

6.        Difference between trigger and stored procedure?

7.        What is the use of cursors and types of cursors ?

8.        How many types of statements we have and what are DCL commands?

9.        What is the use of normalization? Types of normalization? Why we need normalization?

10.     What is the use of CTE?

11.     What is the use of locks and how many types of it?

12.     What the ACID? What is the use of atomicity?

13.     What is the SDLC?

14.     What is the Agile model?

15.     What is materialized view?

16.     What is the difference between candidate key and foreign key?

17.     What is domain integrity and column integrity?

18.     What is Blocking and deadlock?

19.     What is the use of profiler and what are the trace flags you set before starting the profiler?

20.     What are the isolation levels?

21.     How will you set the jobs in sqlserver? Give me one scenario where you have used?

22.     What are the SQL injections and what are the best ways to handle it?

23.     Can you name at least 5 aggregate functions?

24.     How will you solve the performance problems of a query and what are the performance counters you use?

25.     What are the functions? Different types of functions?

26.     Best practices to design an efficient database?

IBM, Chennai - SqlServer/MSBI Interview Questions asked by Companies

I have started posting a series of sqlserver/MSBI interview questions asked by companies. 

       I attended IBM Company’s interview in Sep 2015.  I have given all the questions which I faced during interview. I will provide answers to the below questions shortly. Please keep on visit this page for more updates.

Happy Job Hunt !! ALL THE BEST!!

1.        What is the difference between primary key and foreign key?

2.        What is the general architecture of ETL project?

3.        Where do you apply the business logic and remove error out records either in stage or source or target?

4.        I have two databases client1.database and client2.database. The table names are like Client1.EmpTarget and Client2.EmpSource. But, both tables have similar structure.

          I used client1.EmpSource table when I am working in the Client1 database and use Client2.EmpTarget table when I am working on the Client2.EmpTarget table.

                 I have written a procedure in the client1 database on EmpTarget table and I want to move the same procedure in the client2 database as well without changing the table names. How can we do that ? ( Ans: Synonyms)

5.       How many indexes are available? Do you recommend to create a covering index on the table? If yes, which scenario, you use it?

6.       How many joins are there? when can we use cross join?

7.       I have a emp table with 100 records and empDetails table with 60 records. I want to know what are all the records that are available in the emp table and not in the empdetails table & available in emp details table and available in emp table. How to find out without  using sub-query?

8.       I have a table with 3 columns like empid, empname and dateofjoining. I want to validate whether the dateofjoining is a date or not in the select query? How will you write a query?

9.     How many UDF are available and how will you call a scalar function in the select query and tabular function in the select query?

10.     What is Parameter Sniffing? How will you correct it?

11.     I have a table with 1 lakh records. The records are validated with the business logic. Now, I want to insert the validation results like column Name and reason into ErroroutRecords table. How will you do that with single query?

12.     What are the steps to create a SSRS report?

13.     You have a stored procedure with if and else condition. The columns returned in the if clause is different from the columns returned in the else clause. Now, I want to create two different datasets in the ssrs report and use those datasets in two tables . How can you do that?   

Microsoft Technologies,Hyderabad - SqlServer/MSBI Interview Questions asked by Companies

I have started posting a series of sqlserver/MSBI interview questions asked by companies. 

       I gathered Microsoft, Hyderabad Company’s interview questions. I will provide answers to the below questions shortly. Please keep on visit this page for more updates.

Happy Job Hunt!! ALL THE BEST!!

(1)Tell me about yourself?

      (2) There is a parameter called country with country id and country name. It is enabled for multi selection. I have     selected 3 countries from the dropdown list. Then, how will you pass this information to the stored procedures     and return the result set with 3 countries?

     (3)  What is the logic you use inside the split function?

     (4)  There is a parameter called country with country id and country name. The user can see india, Australia etc..     But, when user selects India then I want to display the country id, not the country name?

     (5)  There are two parameters called country and states respectively. I want to load the states based on the             selection of country?

     (6)  I want to display each country information in the separate excel sheets. If I have 5 country information then I       have to show those details in 5 excel sheets and name of the country is the excel sheet name. How will you     do that?

     (7)   I have a report which displays the user details based on the login user. How will you pass the login user             name to the stored procedure from the SSRS report?

     (8)   I want to display page numbers and total pages information in the SSRS report?

     (9) I want to send a report to my manager and some couple of other people every week automatically? How will       you do that?

    (10)  If the list of managers are too large and they may change in the future then how will you send a report                automatically to all those?

    (11) The user wants to restrict the export options of the report to only excel and PDF? How will you do that in           SSRS?

    (12) Two users are connected to the same database and one user is updating 50 lakh records on the table. Now,       the second user wants to select the same data without affecting the update process of the first user? What is     the query he needs to be written for selecting the data?

    (13) What is the difference between temporary tables and table variables? When to use each of them?

    (14) I have a sales table with columns product category, year and Order Amount. The data in the tables are as         follows

(                 bikes,2011,50000

                  bikes,2012,30000

                  bikes,2013,60000

                  Cars,2011,70000

                  Cars,2012,10000

                  Cars,2013,80000

         Now, I want to show the columns like product category, 2011,2012,2013 and the amount as rows. 

                     Product category  2011    2012    2013

                               bikes        50000   30000   60000

                               Cars         70000   10000   80000

    (15) What are the best practices to write the stored procedures?

    (16) What is the toughest requirement you got till now and how you were managed to handle it technically?

    (17) How will you get the request on the performance tuning? What are the steps you take to to identify the                bottleneck and how will you resolve it?

    (18) I have a folder and sub folders those consist of excel files, flat files and sql scripts. Now, I want to load them     into sqlserver destination using SSIS?

   (19)  I have to take the data from the Oracle database and load them into excel and sqlserver destination? How         will you write the SSIS package? How many times will you fetch the data from the source database?

    (20)  How many package configurations are there in SSIS?

(   (21) I want to take the data once and perform DML operations without taking the source data again and again for      each operation? How to write a query to handle this situation?

Accenture,Hyderabad - SqlServer/MSBI Interview Questions asked by Companies

I have started posting a series of sqlserver/MSBI interview questions asked by companies. 

       I gathered Accenture, Hyderabad Company’s interview questions. I will provide answers to the below questions shortly. Please keep on visit this page for more updates.

Happy Job Hunt!! ALL THE BEST!!

  

    (1)   Tell me about yourself?

    (2)    How will you know that one procedure or query is slow? How will you investigate to find out the performance problem and how do you tune the queries?

    (3)   What is the use of DMV's? Please list out few important DMVs?

    (4)   What is the CTE? What is the syntax of that? When to use the CTEs?

    (5)   I have written a CTE as follows

         With CTEName

         AS

          (

                  select distinct PlanId,PlanName from ORM_BCPCoreDetails

           )

          select * from CTEName

(i)     Will the above query run? If yes, what is the output of the query?

        

         DECLARE @PlanDetails Table

         (

          PlanId  varchar(50),

          PlanName Varchar(255)

         )

        

         With CTEName

         AS

          (

                  select distinct PlanId,PlanName from ORM_BCPCoreDetails

           )

          insert into @PlanDetails

          select * from CTEName

       select * from @PlanDetails

(ii)            Will the above quey run? If yes, what is the output of the query?

                    

         DECLARE @PlanDetails Table

         (

          PlanId  varchar(50),

          PlanName Varchar(255)

         );

        

         With CTEName

         AS

          (

                  select distinct PlanId,PlanName from ORM_BCPCoreDetails

           )

          insert into @PlanDetails

          select * from CTEName

          select * from @PlanDetails

(iii)           Can I declare a temporary table and insert the data from cte to temporary table?

    (6)   Can I create a clustered index on the unique key?

    (7)   Will the foreign key allows the NULL values?

    (8)   What is the use of MERGE statement in Sqlserver?

    (9)   What is the difference between ISNULL and NULLIF statements?

    (10) What is the use of PIVOT and UNPIVOT statements?

   (11) What is the use of triggers? What are the types of triggers we have? When did you use triggers in your project?

    (12) What are the magic tables and what is the use of them?

    (13) What is the use of Isolation levels? What is the default isolation level in sqlserver?

    (14) How will you implement error handling in Sqlserver?

    (15)  How will you handle “Batch Processing” in Sqlserver?

    (16) What is the fragmentation?

    (17) What will happen when we rebuild the indexes? Do we need any downtime?

    (18) What is Mirroring? Does it require any downtime?