Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Interview Questions Part-7 | What is a deadlock in SQL Server | What are “GRANT” and “REVOKE’ statements in SQL | How do you find the Second highest Salary in SQL Server

Oct 5, 2011
What is a deadlock in SQL Server?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.

SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.

SQL Server detects the situation after four denials and refuses further shared locks.
A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.


What are “GRANT” and “REVOKE’ statements in SQL?

GRANT statement grants rights to the objects (table). While revoke does the vice-versa of it, it removes rights from the object.
 
How do you find the Second highest Salary?
 
Answer:

We can write a sub-query to achieve the result

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY. 

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

5 comments :

Anonymous said...

Excellent blog. Variation of second highest salary.

select top 1 salary from
(select top 2 salary from employee
order by salary desc) a
order by salary

Shankar Kulkarni

kotte said...
This comment has been removed by the author.
Unknown said...

hi suresh will you pls help me the above query how to get the 2nd highst salary from employee.the query i was try but i got the higest salary of an employee.pls help me
how to get second highest sal from employee table.
Thanks&rgards
Haresh.A

Anonymous said...

select MAX(sal) from emp where sal <(select MAX(sal) from emp)

Unknown said...
This comment has been removed by the author.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.