Interview Questions (Click on Question to view the answer)

1. Like

Q 1 : Find all employee detail from EmployeeDetail table whose "Name" start with any single character between 'a-n'.

  Select * from EmployeeDetail where Name like '[a-n]%'

Q 2. Get all employee detail from EmployeeDetail table whose "Name" not start with any single character between 'a-n'.

 Select * from EmployeeDetail where Name like '[^a-n]%'

Q 3. Get all employee detail from EmployeeDetail table whose "Name" ends with 'y' and contains 3 letters.

Select * from EmployeeDetail where Name like '__y' 

Q 4. Get all employee detail from EmployeeDetail table whose "Name" containing '%' character.

Select * from EmployeeDetail where Name like '%[%]%' 

Q 5. Finds all the Names in EmployeDetail where Name contains 2 in the second position and end with a 3.

Select * from EmployeeDetail where Name like '_2%3' 

2. General

Q 6. What will be the output of below query

select 0/9 
a. Exception
b. 0
c. 1
d. Null

Ans : b (0)

Q 7. What will be the output of below query

Select 1 where NUll=NULL
a. Exception
b. NULL
c. 1
d. No Row will Return

Ans : d (No Row will Return)
Explanation : No row will return because condition is false.

Q 8. What will be the output of below query

Select NULL+5
a. Error
b. NULL
c. 0
d. 5

Ans : b (NULL)

Q 9. What will be the output of below query

Select count(*)
a. Error
b. NULL
c. 0
d. 1

Ans : d (1)

Q 10. What will be the output of below query

Select $
a. Error
b. NULL
c. 0.00
d. 1

Ans : c (0.00)

3. IDENTITY

Q 11. Explain Identity in SQL Server

Identity column is Auto-Increment Columns. Identity Column allows to generate a new value every time we insert a row in a table.
We create Identity column in a table as below
CREATE TABLE Students
 (
 ID int IDENTITY(1,1) PRIMARY KEY,
 Name varchar(255) NOT NULL,
 PhoneNumber varchar(20)
 ) 
In this example ID column will start with 1 and will increment by 1. If we write IDENTITY(10,5) then it starts with 10 and increment by 5. We can set Identity on a column through column Properties also. Select column and go to Properties and click on IsIdentity and set this yes as below

Q 12. How to get last insert identity in SQL Server.

1. @@IDENTITY
2. SCOPE_IDENTITY()
3. IDENT_CURRENT('TableName')

Q 13. Difference between @@IDENTITY,SCOPE_IDENTITY() and IDENT_CURRENT('TableName').

1. @@IDENTITY : It returns the last identity value generated for any table in the current session, across all scopes. @@IDENTITY is limited to the current session but it is not limited to the current scope. Suppose we have a Insert trigger on a table which insert a row in different table then we will get the identity that was created last means created by trigger.
2. SCOPE_IDENTITY() : It returns the last insert identity generated in any table but in current session and the current scope.A scope is a module StoredProcedure,Trigger,Function etc.
3. IDENT_CURRENT('TableName') : It returns the last identity value generated for a specific table in any session and any scope.
Lets understand this with an example
Suppose i have two tables FirstTable(ID and Name) and SecondTable(ID,Name) and both the tables has Identity on ID column.
CREATE TABLE FirstTable(id int IDENTITY(1,1),Name nvarchar(50))
CREATE TABLE SecondTable(id int IDENTITY(50,1),Name nvarchar(50))
In First Table identity will start from 1 and in Second Table identity will start from 50. Now suppose we have a trigger on FirstTable which will insert a record in second table as below
Create TRIGGER TG_FirstTable ON FirstTable FOR INSERT
AS
BEGIN
       INSERT SecondTable values('One')
END
Now suppose i insert a record in First Table as below
INSERT INTO FirstTable values('Vijay')
Now select
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY() 
@@IDENTITY will print 50 because It checks last insert identity in all the scopes in current session.
SCOPE_IDENTITY() will show 1 because It returns the last insert identity generated in any table but in current session and the current scope.

Q 14. How to reset the identity value after deleting some records.

The DBCC CHECKIDENT management command is used to reset identity counter. Example:
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
This will reset the identity value to 0 again.

Q 15. What IDENT_INCR() function does?

IDENT_INCR() function returns the increment value. Suppose we have a identity like Identity(10,5) on FirstTable where 10 is seed and 5 is increment. So if we will run below query then this will return 5.
select IDENT_INCR('FirstTable')

Q 16. What IDENT_SEED() function does?

IDENT_SEED() function returns the seed value. Suppose we have a identity like Identity(10,5) on FirstTable where 10 is seed and 5 is increment. So if we will run below query then this will return 10.
select IDENT_SEED('FirstTable')

Q 17. Explain the meaning of SET IDENTITY_INSERT?

Suppose i have a table named FirstTable where ID is a identity column and i have two rows in this table where id has 1 and 2. If i delete the record and insert a record as below then i will get a warning
Insert into FirstTable(id,name) values(1,'Vijay')
because ID is a identity column and we can not insert a value in this column explicitly.
If we want to do so than we need to set Identity_Insert on for this table as below
SET IDENTITY_INSERT FirstTable ON
Insert into FirstTable(id,name) values(1,'Vijay')

About Company

TechiesEra is a quality Training Center of Noida.TechiesEra provides summer training in Noida on Hadoop, MongoDB,CouchDB,NoSql,Dot Net, Android, Java,PHP etc with live projects. TechiesEra is group of highly qualified professionals in the field of IT education & recruitments.

Get in Touch

  • Phone:
    +91-9821384666/ 9560231801
  • Email:
    info@TechiesEra.com
  • Address:
    C-33,Sector - 2
    Near By : Nirula Hotel
    Noida -201301