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.

Define tool Manage Statistics in SQL Server 2000 query ?

1026

---

What is the process of normalising?

949

---

How many types of schemas are there?

973

---

Write SQL queries on Self Join and Inner Join.

1072

---

How to execute stored procedure in select statement sql server?

988

---

What is the temp table?

868

---

Explain rdbms?

982

---

Explain what is cte (common table expression)?

1069

---

Explain optimistic and pessimistic concurrency?

930

---

Explain can you implement data mining in ssrs?

145

---

What is store procedure? How do they work? When do you use?

887

---

What are user defined functions in ms sql server?

925

---

Can you explain powershell included in sql server 2008?

893

---

what's new in sql server 2016?

909

---

What is normalization? Explain its different types?

1039

---