This is the second article about calculating 2nd highest salary in SQL. In the first part, you have learned how to find the second highest salary in MySQL, SQL SERVER and by using ANSI SQL, which should also work in all database which confirms ANSI standard e.g. PostgreSQL, SQLLite etc. In this part, I will show you how to find the 2nd maximum salary in Oracle and SQL SERVER using ROW_NUMBER(), RANK() and DENSE_RANK() method. These are window function in Oracle, which can be used to assign unique row id, or rank to each row based upon any column and then select the right row. For example, to calculate the 2nd highest salary, we can create row numbers using ROW_NUMBER() function over salary and then get the second row, which would be your 2nd maximum salary. Though these ranking function handles duplicates differently, so depending upon whether your table has the duplicate salary, you need to choose either ROW_NUMBER(), RANK() or DENSE_RANK(), which handle duplicate differently. This is also one of the most frequently asked SQL Interview questions for your reference.
Monday, November 30, 2015
Saturday, November 7, 2015
In Linux, many times, you want to find out the PID of a process which are listening on a port e.g. if multiple tomcat servers are running on a host then, how do you find the PID of the tomcat listening on port 8080? There are many UNIX commands to find the process using a specific port, but I'll share what I use. I always use the netstat command with -p option, which displays process id of the process listening on a port. Btw, netstat is not the only command to find all processes using a particular port, you can also use lsof command for the same purpose. If you remember, we have used lsof earlier to find all the processes accessing a file but it can also be used to find all processes accessing a specific port. You will see the example of both netstat and lsof commands in this article to find PID of process listening on a specific port in Linux.
Wednesday, November 4, 2015
In order to do well on Java certifications, you need good books and a reasonable number of practice questions before you go for exams. Practice questions and mock exams will help you to assess your topic-wise preparation level and help you to identify your strong and weak areas. Based upon the result of the mock exams, you can concentrate on areas where you lack expertise e.g. multi-threading is one of the tricky areas. It will also expose you to exam patterns and different types of questions you can expect in examination e.g. multiple choice questions, rearranging code, fill in the blanks etc. Though I highly recommend commercial exam simulators like Whizlabs and Enthuware, I also suggest you take advantage of many mock questions which are freely available. In general, free practice questions are not as good as those from Whizlabs and they lack explanation, but still they provide a chance to identify your weak and strong area. Since Java 8 certification is still quite new and there are not many resources, sample questions, and mock exams are available, I thought to collect and publish some of the free Java 8 mock exams which you can take online.
Monday, November 2, 2015
You can use LEN() function to find the length of a String value in SQL Server, for example, LEN(emp_name) will give you the length of values stored in the column emp_name. Remember this is different than the length of the actual column which you specify while creating table e.g. emp_name VARCHAR(60). To give you an example, you have a column called emp_name VARCHAR(60), which means the length of this column is 60 character, it can hold names with maximum 60 character long, but not all names are 60 character. In this SQL SERVER tutorial, I will teach you how to use the LEN() function to find out actual length or number of character in a VARCHAR or CHAR field. This function is also explained in detail on my favorite book Microsoft SQL SERVER 2012 T-SQL Fundamentals, one of the best books to learn SQL SERVER in general and SQL SERVER 2012 particular.