Answer Posted / palash

question is which db are we looking at..
sqlserver or oracle

if sqlserver then top function is readily available to get
the nth highest sal etc

what if it the db is oracle, oracle does not have a
implicit top function to use so how do we go about it

couple of ways

1) use analytical queries
2) use co-related queries (suitable in small sized
databases)

1) analytical queries

if looking for nth highest within the complete table

select * from (select sal , dense_rank() over(order by sal
desc) rnk from emp ) where rnk = n

we can use row_number/rank functions also in place of
dense_rank.


if looking for nth highest within each department.

select * from (select sal, dense_rank() over (partition by
dept order by sal) rnk from emp) where rnk = n


2) co-related queries:

select sal from emp e1 where (n-1) = (select count(1) from
emp e2 where e2.sal > e1.sal)

this query will be pretty slow if the size of the table is
huge.

so my advice is to use the analytical version which is much
much faster than the co-related version.

Explain what is the purpose of sql profiler in sql server?

1134

---

How to provide values to stored procedure parameters in ms sql server?

1101

---

What is the data type of time?

1019

---

What is report rendering ?

162

---

What is the use of keyword with encryption. Create a store procedure with encryption?

1000

---

Explain what is dbcc?

1068

---

How to connect sql server management studio express to sql server 2005 express?

1103

---

Would you store your query in a ssrs report or a database server? State the reason why?

157

---

How to convert numeric values to integers in ms sql server?

1163

---

What is the guest user account in sql server? What login is it mapped to it? : sql server security

1116

---

How you can move data or databases between servers and databases in sql server?

1101

---

What are the difference between primary key and unique key? : sql server database administration

992

---

explain what are the steps you will take, if you are tasked with securing an sql server? : Sql server database administration

1153

---

What are different types of subquery?

1311

---

How many primary keys are possible in a table?

966

---