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.

How do you identify a foreign key?

601

---

Do you have any idea about the tcl commands?

553

---

What are the types of resultset?

535

---

What is the difference between for xml raw and for xml auto?

567

---

How do I start and stop sql server?

550

---

---

Explain indexed views and partitioned view with their syntax.

686

---

Explain the concept of view and Types of views in SQL server?

619

---

How to get the definition of a stored procedure back?

528

---

What is bit data type?

624

---

Explain the usage of floor function in sql server.

508

---

What are the types of subscriptions in SQL Server replication?

550

---

Explain how you can deploy an SSRS report?

106

---

Explain about the command-line tool SQLCMD?

554

---

What are .mdf files?

521

---

Does dbcc checkdb requires db to be in single_user mode? : sql server database administration

520

---