Finding Nth highest salary using SQL interview question

This is one of very common question to find the Nth highest salary using sql.

Create table Command:-

 

  1. Generic Solution:- This solution use correlated subquery. Below given query can be use to find 2nd, 3rd……nth highest salary in an employee table. This solution will work on any database but it can little slower due to subquery.

     
  2. Mysql Specific Solution:- In Mysql we can use limit to solve this problem and it is fast solution but it is vendor specific.

     

    Please add comment if there is any better solution. Thanks in advance.

Mysql like AES_ENCRYPTION and AES_DECRYPTION in groovy/Java

In my recent project there was a requirement of encrypting the data in Database but same was needed in the application level as well so at the mysql end i used AES_encryption() and AES_decryption().

e.g.

and in the Groovy/Grails/Java (application end) same can be achieved using the following code :

The main thing to note in the code given above is that for encryption first you need to encrypt data and then apply hex and in case of decryption unhex the data and then decrypt the same.

The main thing to note in the code given above is that for encryption first you need to encrypt data and then apply hex and in case of decryption unhex the data and then decrypt the same.

This helps me hope that helps you guys!!!

Thanks,

Anuj Aneja

Mysql top just like we have top command in Linux

Once in my grails project i was facing the problem of freeze of server.When this happens tomcat becomes unresponsive.Later on by analyzing the thread dump we found that this problem might be due to the database and tomcat communication. So as to analyse how many connection are active at mysql end we found a query.

Which gives us the result as:

Output:

+—–+——+———–+———————+———+——+——-+——————+

| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+———————+———+——+——-+——————+
| 242 | root | localhost | test_db | Query | 0 | NULL | show processlist |
+—–+——+———–+———————+———+——+——-+——————+

The output shows how many connections are active and their properties. But for this you need to run the query again and again to analyse the connections information. For a better experience we can use mytop which is very easy to install and just like the top in linux, simple steps need to be followed to make it work.

Step 1:Install mytop using the following command.

Step 2: After installation we need make a configuration file .mysqlconfig any where e.g.

For more option you can refer to documentation of mytop.

Step 3: Now just need run this command to see the mysqltop working.

Thats it! Internally it runs the query “show processlist” and is very elegant for analysing mysql connections.

This helps me! Hope this helps you guys!

Anuj Aneja

Removing/replacing special character from database.

Recently in one of my project i faced a problem that database was having special character, which is shown as space on the User Interface. So as to solve this issue i found a very simple solution which consists of following steps:

1. First, you just need to identify which type special character to be removed/replaced like in my case it was shown as space but stored as <?> in the db.

For that you can use the query e.g.

and you can also refer to this link for ASCII code of character. In my case it was 160.

2. Now, next step is simple just run this query.

Thats it !!!

It helps me a lot!!! Hope that helps you guys!!!

Anuj Aneja