Interview Question - How to INSERT data into two tables with one INSERT statement

Well the answer is simple use OUTPUT clause with your INSERT statement and you should be able to achieve it.

Given below is the example 


-- First Table in which we will INSERT data

DECLARE @TAB TABLE
(
NUM INT IDENTITY(50,1),
ID INT

)

-- Second table to hold the indentity value from the first table
-- This table will be populated using OUTPUT clause


DECLARE @TAB1 TABLE
(
ID1 INT

)

Interview Question - Write a query to produce required output

Recently, a friend of mine faced an interesting question in an interview which from the look of it looked worth sharing

Firstly, interviewer asked him about his comfort level in SQL scripting to which he replied as he is Alright but there is a lot of scope for an improvement. Then interviewer presented him with the following table 

and asked him to write a query to produce the following output within 3 minutes

Interview Question - What is the difference between LEN() and DATALENGTH() function ?

This question was faced by friend in a project allocation interview.

From the look of it both the function appears to be same but they are not. One gives the total number  (count) of characters in the string while second gives the number of bytes occupied by the characters in the string. 

As a SQL developer while dealing with string, quite often we end up using LEN() function. But DATALENGTH() isn't used that often.

The LEN() function is a String function while DATLENGTH() comes under data type function.

Interview Question - How to combine result and display where the data is coming from

This one is the interview question faced by my friend during project allocation interview. 

We have two tables (tab1 and tab2) with following data in column ID1 and ID2 respectively.

 


The query needs to be written to produce the output given below

MS-CRM - Query to list the roles that user belongs to in a business unit

One day my client asked me to give him the list of CRM users and their roles as defined in the CRM organization. He also laid down couple of conditions 

• If a user belongs to more than one role then roles must be displayed as a comma separated string in a single row.
• We must know whether that user is in disabled state or not
• What is the business unit of the User

Luckily for me all this information was store in 3 objetcs (Table / view)

• SystemUser
• SystemUserRole
• Role

So given below is what I came up with