Top 10 Sql Interview Questions with Answers (Part 1)

This post attampts to test yourSql skills in the form of a question/answer based sql tutorial .










Q1->How will you find out the second highest sal from a emp table? 
There are many ways to do this. However the easiest way to do this is as below:-

Assume there is a Emp table with the following fields;-

EMP_ID  EMP_NAME  DEPT    SAL
001          X1                   IT          1000
002          X2                   HR        2000
003          X3                   IT          3000

The one approach would we lke this

select max(sal) from schema.emp  where
                                                sal not in ( select max(sal) from emp );

Explanation:-Here in the inner query first we are selecting the max salary from the available dataset and then after excluding that max sal,we are again selecting the max sal from the remaining dataset. Nice and simple though...rite??

Q2->However what if i want to select the Nth highest sal then?

 Above approach is not gonna work In this case we have to use the analytic functions .

select emp_id,sal from(
              select emp_id,sal,row_number() over(order by sal desc) as rowno
              from emp)
    where rowno=n

in this way we can select the nth highest sal from the emp table.

Explanation:-Here in the inner query we are arranging all the emp in the emp table in order of their salary from highest to lowest and assigning a sequence number using row_number().once that is done rest is pretty straightforward. u just need to select which one do u want. 1 2 3  or Nth Highest.


Q3->The next thing would we to find out Nth highest sal from each department?


select * from (
          select emp_id,dept,sal,row_number() over(partition by dept order by sal desc) as rownum 
         from emp) 
where rownum=n

in this way we can select the nth highest salary each deparment from the emp table.

Explanation:-Here in the inner query the clause "partition by dept" devides the emp data in partitions based on dept and then arrange emp in that dept in order of their salary from highest to lowest.


Q4->How would u delete the duplicate rows from the emp tables?


       delete from  emp where rowid in
                          (select rowid from 
(select rowid,row_number() over (partition by empid order by empid) dup
                                   from  emp)
                            where dup > 1);

Explanation:-Whenever you insert a row in the database a unique rowid is assigned to it.It represent the physical location of that record on the disk.we can use this feature for removing the duplicates from a table.

Suppose your emp table have duplicate entries of employees records and you have to removes the duplicates from it.Since each employee have a unique emp_id in the system we can use that as a key for arranging records in groups.

Top 15 Unix interview questions with answers (Part I)

This post attampts to test your Unix skills in the form of a question/answer based Unix tutorial on Unix command lines. The commands discussed here are particulary useful for the developers working in the middle-tier (e.g. ETL) systems, where they may need to interact with several *nx source systems for data retrieval.




Q1-> Printing the  first line of a file?
There are many ways to do this. However the easiest way to display the first line of a file is using the [head] command.

$> head -1 abc.txt

The number 1 tell the head command to display only the one line from top.If you set it to 5 it will display top 5 lines.By default the head command display top 10 lines.


Another way can be by using [sed] command. .

$> sed '2,$ d' abc.txt

How does the above command work? The 'd' parameter basically tells [sed] to delete all the records from display from line 2 to last line of the file (last line is represented by $ symbol).

Q2-> Printing the last line of a file?
The easiest way is to use the [tail] command.

$> tail -1 abc.txt
If you want to do it using [sed] command, here is what you should write:

$> sed -n '$ p' test
From our previous answer, we already know that '$' stands for the last line of the file. So '$ p' basically prints (p for print) the last line in standard output screen. '-n' switch takes [sed] to silent mode so that [sed] does not print anything else in the output.


Q3->How will you list only directories in a perticular location?
There are two ways to do this. first one is using the [find] command.Suppose you want to list all the directories in current dir i.e [.]

$>find . -type d

It will list all the directories as well as sub directories in the current directory.However if youwish to list only the directories not the sub-directories inside those dir  then

$> ls -lrt * | grep ^d

Here first we are long listing all the files,directories in the current location and then passing the result to [grep] which then filtering all the directories using "^d".
Note** "^" means start of the line.