SQL Interview Questions
=================
Beginners:
=================
Q. Write a query to display all the records from a given table where date must be equal to the last date in each month?
Q. Difference between DELETE and TRUNCATE?
A.
CHAR is static in nature, whereas VARCHAR is variable length.
select convert(varchar,'1234567890123456789012345678901234567890') vcdefaultlen
, convert(char,'1234567890123456789012345678901234567890') cdefaultlen -- default length = 30-characters for both char | varchar
Q. How many IDENTITY columns a table can have?
Q. Difference between Temporary Table and Table Variables?
Q. Difference between a Procedure and Trigger?
Q. Difference between SET and SELECT?
Q. What is a trigger? Explain advantages and disadvantages?
Q. Difference between Procedures and Functions?
Q. Difference between ISNULL and COALESCE?
Q. Difference between TOP n and TOP(n)?
Q. Table Name : EmployeeDesignation { EmpId int, Designation Varchar(50) }
a. Find all employees who has designation ONLY "Tester" and should not have any other designations?
b. Find all employees who have designation ONLY "Tester" & "Developer" and should not have any other designation?
c. Find all employees who have designations otherthan "Tester" & "Developer" only?
Q. Give any 5 best differences between PRIMARY KEY and UNIQUE KEY?
=================
Intermediate:
=================
Q. Limitations on Functions in SQL Server?
Q. Difference between subqueries and corelated-subqueries?
A.
Subqueries:
============
Subqueries are enclosed in parentheses. Subquery is also called an inner query and the query which encloses that inner query is called an outer query. Many times subqueries can be replaced with joins.
Correlated Subqueries:
=======================
If our subquery depends on the outer query for its value then it is called a Correlated Subquery. It means subquery depends on outer subquery. Correlated subqueries are executed for every single row executed by outer subqueries.
if object_id('tempdb..#Employee') is not null
drop table #Employee
go
create table #Employee(EmpId int, Sal money)
go
insert into #Employee values(1,5000),(2,4500),(3,2500),(4,7000),(5,6800),(6,8000),(7,2500),(8,8000),(9,3500),(10,4000)
go
select * from #Employee order by Sal desc
go
declare @nThSal tinyint = 2 -- 2nd highest sal
select * from #Employee e
where (@nThSal-1) = (select count(distinct sal) from #Employee where sal > e.sal)
go
-- using corelated-subqueries
select distinct Sal, (select count(distinct EmpId) from #Employee where Sal = a.Sal) EmpCnt
from #Employee a
order by EmpCnt
go
-- OR
-- using group by clause; without corelated-subqueries... :)
select Sal, count(distinct EmpId) EmpCnt
from #Employee
group by Sal
order by EmpCnt
go
Q. Write a query to fetch nth-highest SAL employee details?
A.
if object_id('tempdb..#Employee') is not null
drop table #Employee
go
create table #Employee(EmpId int, Sal money)
go
insert into #Employee values(1,5000),(2,4500),(3,2500),(4,7000),(5,6800),(6,8000),(7,2500),(8,8000),(9,3500),(10,4000)
go
select * from #Employee order by Sal desc
go
declare @nThSal tinyint = 2 -- 2nd highest sal
select * from #Employee e
where (@nThSal-1) = (select count(distinct sal) from #Employee where sal > e.sal)
go
=================
Experts:
=================
Q. What is Isolation Level? Types?
Q. The default isolation level in sql server?
Q. What is a Role and how many types of roles are there in SQL Server?
Q. What do you mean by lock Escalation?
Q. What is a dead lock?
Q. Difference between shared and exclusive locks?
Q. Table1 = {Policy_No, Major_Cd} (Note A Policy_No can have distinct Major_Cd's)
Table2 = {Policy_No, Tbl2_Type_Cd, Tbl2_Amt1, Tbl2_Amt2},
Table3 = {Policy_No, Tbl3_Type_Cd, Tbl3_Amt1};
where Major_Cd = {'M1','M2','M3','M4','M5','M6','M7','M8','M9','M10'},
Tbl2_Type_Cd = {'3SE','LOP','LDF','4HH','503SEPSE','INT','TER','PSE'}
Tbl3_Type_Cd = {'3SE','LOP','LDF','4HH','503SEPSE','INT','TER','PSE'}
Condition (Table2/Tabl3 logic):
a) if Major_Cd='M1','M3','M4' and :
IF Policy includes LOP type cd, map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else if policy includes PSE type cd map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else leave blank"
b) if Major_Cd='M2','M6','M7' and
"IF Policy includes 3SE type cd, map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else if policy includes 503SEPSE type cd, map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else if Policy includes LOP type cd, map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else leave blank"
c) if Major_Cd = 'M9' and
"IF Policy includes 4HH type cd, map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else If Policy includes LOP type cd, map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else leave blank"
d) if Major_Cd='M10','M5' and
"IF Policy includes LOP type cd, map Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Else leave blank"
Result:
Policy_No, Major_Cd, Tbl2_Type_Cd, Tbl3_Type_Cd, Tbl2_Amt1, Tbl2_Amt2, Tbl3_Amt
Comments
Post a Comment