Nth maximum salary in MySQL using LIMIT clause

MySQL supports a LIMIT keyword, which provides pagination capability. We can find the nth highest salary in MySQL without using sub query.

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1;

4rth highest salary in MySQL with LIMIT clause: 

-- use database

use abusecore;

-- creating Employee table in mysql

CREATE TABLE Employee (name varchar(10), salary int);

-- inserting sample data into Employee table

INSERT INTO Employee VALUES ('Mill', 3000);

INSERT INTO Employee VALUES ('Sham', 4000);

INSERT INTO Employee VALUES ('Jack', 3000);

INSERT INTO Employee VALUES ('Pats', 5000);

INSERT INTO Employee VALUES ('Rock', 7000);

-- 4rth highest salary in MySQL

SELECT salary FROM Employee ORDER BY Salary DESC LIMIT 3,1

-- Output:

3000

Nth highest salary in MySQL with LIMIT clause:

SELECT salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1;

This approach is faster than correlated query approach but its vendor dependent.

What does persistence object means in Hibernate architecture?

Persistent object is nothing but an instance of POJO class that we create to represent rows in the table in the database.

According to hibernate-doc an instance of POJO class representing table in database goes through 3 states.

Transient

Persistent

Detached

When a POJO instance is in session scope, it is said to be persistent i.e hibernate detects any changes made to that object and synchronizes it with database when we close or flush the session.

What is database deadlock? How can we avoid them?

Database deadlock

When multiple external resources are trying to access the DB locks and runs into cyclic wait, it may make the DB unresponsive.

Avoid Database deadlock

1. Can make a queue wherein we can verify and order the request to DB (To run the operations in FIFO order).

2. Less use of cursors as they lock the tables for long time (slow down the DML executions).

3. Keeping the transaction smaller (process query relation operation before start the transactions).