Interview Questions Answers in SQL Server, SQL PLSQL

Questions / { veeresh kethari }

Questions Answers Category Views Company eMail

When you first load SQL SERVER you will startup with what all databases?

CompuSol,

3 SQL Server 5464

Answers / { veeresh kethari }

Question { Choice Solutions, 6587 }

Regarding joins what are the differences you observed in
oracle 9i and sql server?

Answer

I'm not happy with Uma answer,anybody can explain?please

Is This Answer Correct ?

0 Yes

1 No

Question { 19029 }

what is explain plan?

Answer

The explain plan command is a tool to tune SQL statements.To
use if you must have an explain_table generated in the user
you are running the explain plan for.This is created by
using the utlxplan.sql script.once the explain plan table
exists u run the explain plan command giving as it's
argument the SQL statement to be explained.The explain_plan
table is then queried to see the execution plan of the
statement.Explain plan can also be run using tkprof.

Is This Answer Correct ?

11 Yes

1 No

Question { 3926 }

when inserting to a table how many rows will be effected
using triggers

Answer

Before commit in the database,data will get store in the
INSERTED table in trigger. From the table we can come to
know how many records are affected in the table.

Is This Answer Correct ?

1 Yes

0 No

Question { 7908 }

what is the difference between group and having
give an example with query and sample output

Answer

Where: 'WHERE' will filter the data before grouping

Having:'HAVING' will filter,after the grouping the data.

Is This Answer Correct ?

3 Yes

0 No

Question { Ramco, 36866 }

How to select nth record from a table?

Answer

select min(sal) from EMP where sal in(select top n sal from
EMP
order by SAL desc)

Is This Answer Correct ?

3 Yes

0 No

Question { Aptech, 6352 }

i want to create procedure for create table in sql server
2005

for example

create procedure create_table
@table varchar(20)
as
create @table(
id int,
name char(20)
)
but it will get error
what is solution?

Answer

Here is the solution...

create proc CREATE_TABLE
@TableName varchar(50)
as
begin
declare @String nvarchar(max)
set @String='create table '+@TableName +'(ID int,Name
varchar(50))'
execute sp_executesql @String
end

Is This Answer Correct ?

1 Yes

0 No

Question { 247Customer, 3945 }

How to handle errors in Stored Procedures. I want to display
a message to the user in the .aspx page that is calling a
stored procedure in it's code behind. please help me.

Answer

With simple example:

begin try
declare @int int
set @int='select 1/0'
end try
begin catch
print 'Error detected with error number -'+convert(char
(10),@@error)
end catch

Is This Answer Correct ?

1 Yes

0 No

Question { 3029 }

Define Check points and End Points?

Answer

CheckPoints:Whenever we perform any DML operations in the
database,it will go to the checkpoint and then commit in
the database.If we don't have checkpoints,the data will get
store in transaction log file,once it got fill full,it will
make the database performance issues.
Endpoints:Endpoints are objects that represent a
communication point between the server and a client.

Is This Answer Correct ?

4 Yes

0 No

Question { 7307 }

What is Schema? and why we use schemas?

Answer

Schema is a collection of database objects.Using database
objects will manage the data in the database.

Is This Answer Correct ?

8 Yes

0 No

Question { 7718 }

what is syntex second or third highest salary.

thanks & Regards
Dhirendra sinha

Answer

with Topsal (EmpID,Ename,Sal,Dno,Topsal) as
(select *,row_number() over(order by sal) Topsal from emp)
select top 2 EmpID,Ename,Sal,Dno from Topsal order by sal desc

Is This Answer Correct ?

1 Yes

0 No

Question { 5924 }

i have a table #temp1(id, Name groupname ) and record
like this 1 R1 S
2 R3 S
3 R2 S
4 R4 D
5 R5 D
6 R6 K
7 R7 K
8 R8 L
9 R9 L
10 R10 L
11 R11 K

and i want to display record based on user defind sorting
order e.g.
1 R4 D
2 R5 D
3 R6 K
4 R7 K
5 R11 K
6 R1 S
7 R3 S
8 R2 S
9 R8 L
10 R9 L
11 R10 L

Answer

SELECT ROW_NUMBER() OVER(ORDER BY GROUPNAME ASC) ID
,NAME,GROUPNAME FROM REC ORDER BY GROUPNAME

Is This Answer Correct ?

2 Yes

0 No

Question { 10845 }

Please get the 4 th maximum salary from a table without
using any sql keyword (TOP,MAX are mot allowed)

Answer

with SalCTE (EMPID,Ename,Sal,Num)as

(select *,row_number() over(order by sal desc) num from emp)

select * from SalCTE where Num=4

Is This Answer Correct ?

1 Yes

1 No

Question { Deloitte, 26126 }

how to get the maximum among two tables,for example table 1
(dep1) have (emp_id,emp_name,salary) columns and table 2
(dept2) have (emp_id,emp_name,salary) columns,i want which
employee have the maximum salary among two tables?

Answer

User CTE:

with Temp as(select max(sal) Sal from Table1
union
select max(sal) sal from Table2)
select top 1 * from Temp order by sal desc

Is This Answer Correct ?

3 Yes

0 No

Question { Apollo, 5121 }

What is the joins and how many types of Joins in sql server
a diffrentiate ever one give a suaitable query

Answer

1.Inner join:All the matched records from the both tables.
2.Left outer join: All the matched records from the both
tables and unmatched records from the left table.
3.Right outer join:All the matched records from the both
tables and unmatched records from the right table.
4.Self join:Joining the table itself.
5.Cross join:Cross join will give u the Cartesian product
from the 2 tables,it won't allow 'where' clause.

Is This Answer Correct ?

3 Yes

1 No

Question { HP, 7127 }

How to avoid cursors?

Answer

1.By using case.. set,we can avoid the cursors

2.By using temporary tables/table variables/CTE
with while loop we can avoid cursor in the transaction.

Is This Answer Correct ?

3 Yes

1 No