SQL Interview Questions for experienced professionals

Previous Page                                                     Next Page

Question 16: How to update only matching records between tables?
Answer: Let's create two tables as 'TableA' and 'TableB' respectively.

 TableA - Create table TableA(Id int,Name varchar(20))

 TableB - Create table TableB(Id int,Name varchar(20))

 Insert few records into TableA as well as into TableB.

Insert into tableA (Id,Name) values(1,'Name1'),(2,'Name2'),(3,'Name3'),(4,'Name4'),(5,'Name5')

Insert into tableB (Id,Name) values(1,'VIPUL'),(2,'NAKUL'),(3,'Bill'),(4,'BOB'),(5,'Crisine')

Now we update all the matching records between 'TableA' and 'TableB' -

update tableA set [Name]=b.Name

from tableA A inner join TableB B on A.Id=B.Id

Now run the below query - 

select *from tableA

You will find that all records has been updated.

Question 17: In the below table, How to get second highest salary for each department?

Answer: In order to implement above scenario, we will use the following query which includes dense_rank(), Partition -
With CTE

As

(

Select Department,dense_rank() over(partition by department order by salary desc) as [Id],Salary from Employee

)

select distinct Department,Salary from CTE

where Id=2

Question 18: How to delete all duplicate records in the below table?

Answer: In order to implement above scenario, we will use the following query which includes dense_rank(), Partition -

With CTE_Table

As

(

Select ID,Name, ROW_NUMBER() Over(Partition by Id,Name Order by Id Asc) as [Row Number]

from sampletable

)

Delete from CTE_Table where [Row Number]>1

Question 19: What do you mean by 'SET RECURSIVE_TRIGGERS OFF/On'?

Answer: In SQL server, One trigger execution  can trigger on same or another table. Trigger in the same table is called Nested trigger or Recursive trigger. SQL server supports nesting of triggers upto 32 levels. We can also decide whether trigger should be Recursive or not by using following SQL command -

ALTER DATABASE <[database] Name>

SET RECURSIVE_TRIGGERS OFF/On

or

SP_Configure 'nested_triggers', 0/1 --Off/On

Go

Reconfigure

Go 

Question 20: How to alter a constraint in SQL server table?
Answer: In SQL server we cannot revise or alter a constraint, in order to implement this we first drop the constraint and then recreate it.
Drop a constraint: Alter table <[table name]>  drop constraint <[constraint name]>
Recreate constraint: As regular query to solve this -

Alter table <[table name]> add constraint <[Constraint name]> foreign key(<[Column name]>) references <[Master table name]>(<[Column name]>)

On Delete cascade --Optional 

On Update cascade --Optional

Question 21: How to alter a column datatype in SQL server table?
Answer: To alter a column datatype follow the below query -

Alter table  <[table name]> alter column <[column name]> <[datatype]>

Question 22: What are ranking functions in SQL server?
Answer: Ranking functions were first introduced SQL server 2005, Ranking functions return a ranking value for each row in a partition. There are four types of ranking functions available in SQL server -
1. Rank() function displays the rank of the rows with gaps with in partitioned group.
2. Dense_Rank() function displays the rank of the rows without gaps with in partitioned group.
3. Row_Number() function displays the row number of each rows with in partitioned group.
4. NTile(<Group Number as Int/BigInt>) displays the rows in an ordered partition into a specified number of groups. Number of groups can be decided by passing arguments values in NTILE function. If total number of rows are not divisible by passing number of groups in argument area then first group always contains more rows than later groups.
To know more about NTile, check here

Question 23: What will, If 'Select @@Error' is written just after the exception in SQL server.
Answer - It will produce an error code.

Question 24: How to display execution plan in text format while running query in SQL server?
Answer - Follow the below steps to achieve the same Open SQL server Query analyzer then type the following queries-

• Set showplan_text on Go
• <Your Query>Go 

It will show the query execution plan in text format.

Question 25: What are the components of relational database server?
Answer - There are two components of relational database server -
1. Relational engine
Relational engine perform to define a precise execution plan for the current SQL statements and provide the result set to user window.
2. Storage engine
Storage engine collect and send each individual data row to rows set which is collected and combined by relational engine.  
Check here to know more.

Question 26: What are the scenarios when execution plan can be removed from cache memory?
Answer - There are following conditions when an execution plan can be removed from cache memory -
1. To have the execution plan more memory is required by the system.
2. Execution plan age is over.
3. If execution plan is not currently being referenced by connection string.

Question 27: What are the scenarios when execution plan can be removed from cache memory?
Answer - There are following conditions when an execution plan can be removed from cache memory -
1. To have the execution plan more memory is required by the system.
2. Execution plan age is over.
3. If execution plan is not currently being referenced by connection string.

Question 28: How to prevent insert/update/delete into SQL query?
Answer - To implement the prevention for Insert/Update/Delete, we can use triggers in SQL server.
As we know that data in magic tables always affect if any DML command runs.

To prevent Insert/Update/Delete

Create TRIGGER dbo.<Insert>/<Update>/<Delete>

ON dbo.TableA

FOR Insert

AS

BEGIN

  SET NOCOUNT ON;

    RAISERROR('You cannot perform <Insert>/<Update>/<Delete> operation into this table as it is prohibited', 11, 1);

    ROLLBACK TRANSACTION;

END

GO

To prevent Delete all

Create trigger DeleteOnTableA on TableA Instead of Delete

As

Begin

Declare @RowCount int

Declare @ShouldDelete bit

--Get the row count

set @RowCount=@@ROWCOUNT

if(@RowCount=0)

return;

--If@RowCount value is 1 then doesnot matter whether where condition is available or not

if(@RowCount=1 or (Select COUNT(*) from (select distinct *from deleted) A)=1)

Begin

set @ShouldDelete=1

End

--If @RowCount value is greater than one and respective table contains few records after deletion

--It means it contains a where clause.

else if(@RowCount > 1 and exists(SELECT 1 FROM dbo.TableA WHERE id NOT IN (SELECT id FROM deleted)))

Begin

set @ShouldDelete=1

End

--Display the error based on @ShouldDelete variable

if(@ShouldDelete=1)

Begin

Delete from TableA where Id in (select Id from deleted)

End

Else

Begin

RaisError('Where clause is requried or may be it is last record. So delete the last record individually',11,1)

End

End

To prevent Update all

There is not much difference in both queries, Just replace the yellow part by following query, It will work -

if(@ShouldDelete=1)

Begin

Select *from inserted

Update TableA set [Name] = i.Name from inserted i

where TableA.Id=i.Id

End

Else

Begin

RaisError('Where clause is requried or may be it is last record. So delete the last record individually',11,1)

End

Question 29: How to select distinct rows in count function in SQL server table?
Answer -  Use the following query to implement same -
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM TableA) Emp

Question 30: Can we create a function and have dynamic SQL in it?
Answer -  NO, You can't call stored procedures from within a function, including the stored procedures EXEC or EXECUTE or SP_EXECUTESQL. This means that you can't have dynamic sql embedded within a function.