Top 17 SQL and UNIX Interview Questions for Software Support Engineers

The SQL and UNIX are two of the most essential skill for any support or development job. It doesn't matter whether you are applying for Java developer or C++ developer, Python developer or Ruby programmer, SQL and UNIX always have some role to play. Hence, it's imperative for any programmer to prepare both SQL and UNIX well before going for any job interview. This becomes even more important if you are going for a support engineer role. Since the main job of support professional is to look after production environment, which often runs on Linux, it's must for them to have a good knowledge of both Linux operating system and command line. They should know what they are doing because an incorrect grep or rm command can bring down the whole production system. That's why they are extensively questioned and tested in their UNIX skill.

Same is true for SQL because most of the real world system uses a relational database for backend e.g. Oracle, MySQL or Microsoft SQL Server and support professional often need to run queries, study stored procedure or write procedures to generate reports.

Just like UNIX command, a careless SQL query also has potential to bring your production system to halt and you should always do diligent before running any query against live environment e.g. running them in UAT or QA environment before running them in Production, you can find more such SQL tips here.

Because of these reasons, SQL and UNIX become essential skills for any software support professional. I have interviewed a lot of support guys, particular for Java applications and I have always focused on their core Java, and support skills along with SQL and UNIX.




SQL and UNIX Questions from Support Engineers

This list contains some of the questions which I think very important from a Java support perspective, and many of them are actually asked in real interviews. If you are going for any Java support role, you can expect some SQL and Linux questions along with Java JEE question. I encourage you to go through this list to get an idea of what to expect in real Java support interview.

Btw, this is the second part of my list of interview questions for Java IT support professional. In the first part, I have shared 30 essential Java and JEE questions and in this part,  I'll share 17 SQL and UNIX questions from various Java Support Interviews.



1) What will happen when a TCP/IP client stops consuming messages from Server but it's connected? Assume Server is continuously sending messages. (answer)
The message will be stored on sending buffer at the sender end because it will stop receiving the ack from the receiver. If server keep continues to push messages, the sending buffer will overflow, throw an error to stop the server.


2) What guarantees are provided by TCP/IP?   (answer)
The TCP/IP provides the guaranteed delivery but apart that it also provides ordering guarantee i.e. message will be delivered to the client in the same order they are sent from the sender. This is one of the important reason to consider TCP/IP for ordered messages e.g. orders and trades in the financial world. See here to find some good book to learn more about TCP/IP protocol from application developer's perspective.


3) How do you find the PID of a process listening on port 8080? (answer)
If the process is running on Linux operating system then it's pretty easy, just use the netstat command to find the PID of the process, netstat -p shows the PID. Once you got the process id, you can use the ps command to find the process itself. See the answer for a live example.



4) What is multicasting?  (answer)
The multicasting is a networking concept which allows you to send the same messages to multiple clients without sending it to all i.e. without broadcasting it. In the case of multicasting, there exists a multicast group, which is represented by a special multicasting IP address. Any client who wants to receive the message sent to this IP address can join this group and then the router will ensure that new messages will be delivered to all subscribers.


5) What is the difference between multicasting and broadcasting?  (answer)
Even though both are networking concepts to deliver messages to multiple clients, there is subtle difference exists between them. In the case of multicasting, messages are delivered to only machines which are part of the multicast group while in the case of broadcasting message is delivered to all machines in the network.


6) What is a multicast group?  (answer)
A multicast group is a single machine or group of machines with special multicast IP address i.e. the IP address in the range of 224.0.0.0 to 239. 255.255.255. The multicast group can only be used as the destination of a datagram, not as source.


7) You received an alert that file system is about to fill up, how do you find big files to remove?  (answer)
This is one of the common situation Java programmer face. In Linux, you can use the find command to search all big files. The find -size options list the file with their sizes. See my article 10 example of find command in UNIX for a complete example.



8) How do you find the heap dump files in Linux?  (answer)
The heap dump files are generally in the form of java_pid.hprof, so you can easily search them using find command in Linux as shown below:

$ find . -name *.hprof -print


9) How do you find the files currently used by your Java process?  (answer)
You can use the lsof command to find all the files, including sockets by your Java process in Linux.  The lsof command stands for list open files and since everything in UNIX is file it can list both normal files as well as open socket connection by any process.

$ lsof -c java

will list all files opened by java command, and,

$ lsof -p 22342

will list all files opened by process id 22342.

For a more in-depth discussion on using lsof command and its various open, please read 10 examples of lsof command in Linux.



10) How do you find the ports currently used by a Java process?  (answer)
You can use the netstat or lsof command to find all the ports currently used by a Java process, but for that, you need the process id of your Java process. You can find the PID using ps and grep command as shown below:

$ ps -ef | grep java | grep myprocess

Once you find the PID of your Java process, you can use the netstat command to find the ports currently used this Java process as shown in the following example:

$ netstat -nap | grep PID

If netstat is not available on your Linux machine you can also use the lsof command to find all the ports used by a Java process as shown below:

$ lsof -i

If you want to learn more about Linux commands I suggest you to first complete Linux Command Line Interface (CLI) Fundamentals course from Pluralsight.


11) Can you name 10 Linux commands you use daily?  (answer)
This is easy, right? you can name any Linux commands you use but make sure you give a variety of commands e.g. commands to search, networking commands, process management commands, CPU and memory profiling commands etc. Anyway, here is my list of 10 Linux commands
ls
top
ps
find
grep
netstat
df
lsof
kill
sort
chmod

If you want to learn more about UNIX commands then please see  The Linux Command Line: A Complete Introduction, a complete guide to Linux commands.





12) How do you find the second highest salary in employee table?  (answer)
There are many ways to find the second highest salary in the employee table e.g. you can either use the co-related subquery, or you can use a ranking function like row_number() and rank(). The question can be even more tricky by adding duplicates e.g. two employees with the same salary. In that case, you can choose between ranking function to satisfy the requirement. I have already written a detailed blog post about this topic, please see here for a couple of example of finding second highest salary in the employee table.


13) How do you find the duplicate rows in a table?  (answer)
Again, like the previous question, there are several ways to find the duplicate rows in table e.g. you can use a ranking function like row_number which assign row number depending upon the value you ask and for same values like in the case of duplicate rows, the row number will be same. You can also the group by clause to detect duplicates e.g.

select emp_name, count(*) from employee group by emp_name having count(*) > 1

This one of the classic way to find the duplicate rows in a table and it will work in almost all database e.g. Oracle, SQL Server, or MySQL.  See here for a detailed answer to this problem.



14) How do you remove the duplicate rows from the table?  (answer)
This is the follow-up previous question and much tougher than the previous one, especially if you are not familiar with the ranking functions like row_number. If you only know about GROUP BY clause then you will struggle to remove duplicate rows because if you remove by emp_id both rows will be removed. Here you must use the row_number() function, the duplicate rows will have row number > 1 e.g.

select emp_name, row_number() over (order by emp_name desc) row_number from Employee

This will print something like below

John        1
Heroku    1
David      1
David      2

Here second David is duplicate and you can easily remove it by giving condition where row_number> 1.  See here for complete SQL query and to learn more about solving this problem in the real world.


15) What is the difference between clustered and non-clustered index?  (answer)
This is one of the frequently asked database questions, the main difference between two is that there can be only one clustered index on a table while you can have more than one non-clustered index. The clustered index also defines how the data is physically stored on the disk. See here to learn more difference between these the clustered and non-clustered index in the database.


16) How do you find all records modified between two dates in SQL?  (answer)
This is one of the tricky SQL questions. It looks simple but when you go and write the SQL query, you will find that it's not working as expected. For example, many programmers will use the "between" SQL clause to find all the rows modified between two dates e.g.

select * from Orders where order_date between '20160901' and '20160930'

This will work well in most cases but if there is an order where order_date is midnight of 1st October then it will also be picked. You can be more precise by using the logical operator as shown below:

select * from Orders where order_date >='20160901' and order_date < '20160930'

See Querying Microsoft SQL Server to learn more about the date and time data types and comparing them in SQL Server.



17) What is the difference between count(field) and count(*) in SQL?  (answer)
The only difference between count(field) and count(*) is that former doesn't count null values while later does. For example, if you have an emp_name column on your table and it contains 10 valid values and 3 null values then count(emp_name) will return 10 while count(*) will return 13 rows.


That's all about SQL and UNIX questions for software support engineers. As I said, these two technologies are very very important for any job and a good command on these two goes a long way to secure the job. They are also classic technologies i.e. they don't obsolete in every couple of year and they will remain popular for several years to come.

Further Learning
Cracking The Coding Interview 
Programming Interviews Exposed: Secrets to Landing Your Next Job
Introduction to SQL by Jon Flanders
Linux Command Line Interface (CLI) Fundamentals

Other Interview questions for software support engineers
Along with these questions, you should also check following interview questions shared before to do well on your support job interviews.
  • Frequently asked SQL queries from Interviews - part 2  (see)
  • Frequently asked Java Interview Questions  (see)
  • Top 50 Multithreading Interview Questions  (see)
  • Top 30 Array based Interview Questions  (see)
  • Top 20 LinkedList based Interview Questions   (see)
  • Top 50 Java Programs from Coding Interviews (see)
  • 10 Books to Prepare for Coding Interviews   (see)
  • Difference between Primary and Unique key in SQL (answer)
  • Top 50 Programming questions for telephonic interview (answer)

Thanks for reading this article so far. If you like this tutorial then please share with your friends and colleagues. If you have any question, doubt or seeking an answer for something, please drop a comment and I'll try to find an answer for you.

No comments:

Post a Comment