Top 10 SQL and UNIX Interview Questions for Programmers

The SQL and UNIX are two of the most essential skills for any programmer. It doesn't matter whether you are applying for the job as Java developer or C++ developer, Python developer or Ruby programmer, SQL and UNIX always have some role to play in your job. It's even important for people who have less to do with programming like application support guys, business analysts, project managers, and subject matter experts.  Hence, it's imperative for any programmer or IT professional 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, rather for a development role because than the importance of SQL, Database, and Linux skill increases as compared to programming language you know.

Since the main job of support professional is to look after production environment, which often runs on Linux, it's very important for them to have a good knowledge of both Linux operating system and command line.

They should know what they are doing because incorrect use of 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 like Oracle, MySQL or Microsoft SQL Server and support professionals 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 down your production system to halt and you should always do due diligence before running any query against live environment like testing them in UAT or QA environment before running them in Production.

This single thing has saved my life a couple of times and I have jolted a couple of points you should take care while running queries on production or live database,  you can find those tips here.

Because of these reasons, SQL and UNIX become essential skills for any software support professional. If you think you lack SQL and Linux skills then I suggest you to first go through a comprehensive course like The Complete SQL Bootcamp and Linux Command Line Basics before appearing any interviews. These courses are great to fill any gaps you have in your understanding.





Linux Interview Questions for Programmers

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 Core Java questions.

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.


Anyway, here are some of the frequently asked SQL and UNIX questions from Programming Job 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 the 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.

Btw, if you want to learn more about TCP protocol you can see this 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 a 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.

Btw, If you are not familiar with netstat, ps or fundamental Linux commands then I suggest you to first go through Linux Command Line Interface (CLI) Fundamentals course on Pluarlasight. It has helped me a lot to improve my Linux skills.

UNIX Interview Questions for Programmers

Btw, you would need a Pluralsight membership to access this course, which cost around $29 per month or $299 annually (14% discount).

If you don't have Pluralsight membership, I encourage you to get one because it allows you to access their 5000+ online courses on all latest topics like front-end and back-end development, machine learning etc. It also includes interactive quizzes, exercises, and latest certification material.

It's more like Netflix for Software Developers and Since learning is an important part of our job, Plurlasight membership is a great way to stay ahead of your competition.

They also provide a 10-day free trial without any commitment, which is a great way to not just access this course for free but also to check the quality of courses before joining Pluralsight.


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 a 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.


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, I suggest you go through the Linux Command Line Basics course on Udemy. It covers all basic Linux commands with real-world examples.

SQL and Linux Interview Questions with answers





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 lsof command, a "Swiss Army Knife for programmers", you can check out my earlier post, 10 ways to use the lsof command in Linux.




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 some basic Linux commands

  1. ls
  2. top
  3. ps
  4. find
  5. grep
  6. netstat
  7. df
  8. lsof
  9. kill
  10. sort
  11. chmod
  12. du
  13. pwd


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


Top 10 SQL and UNIX Interview Questions for Programmers



If you prefer online courses then check out the two courses I have to suggest you before, I generally start with a course and then read a book for more in-depth understanding.


SQL Interview Questions for Programmers

Now that you have seen some of the frequently asked Linux/UNIX command line questions from programming job interviews, let's discuss some SQL and Database interview questions. This list includes questions on SQL query and database fundamentals.


1) How do you find the second highest salary in the 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 the second highest salary in the employee table.

How do you find the second highest salary in the employee table?



2) 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 like Oracle, SQL Server, or MySQL.



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 like

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 like delete from table where row_number> 1.

Though, if you are not familiar with window functions like rank and row_number() I suggest you go through an online course like Microsoft SQL for Beginners. This will not only help you to understand basic SQL commands but also provide some insight on how to use them effectively in your day to day work.

SQL Interview Questions for Programmers



3) 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.


4) 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'


5) 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 Programmers and Software Engineers. As I said, these two technologies are very very important for any job and 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.

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, and fortunately the people who have come out of those interviews have done very well on their job.


Other Interview questions for Programmers
Along with these questions, you should also check following interview questions shared before to do well on your support job interviews.

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