SQL Queries

Sample Database:
DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
  empno decimal(4,0) NOT NULL,
  ename varchar(10) default NULL,
  job varchar(9) default NULL,
  mgr decimal(4,0) default NULL,
  hiredate date default NULL,
  sal decimal(7,2) default NULL,
  comm decimal(7,2) default NULL,
  deptno decimal(2,0) default NULL
);

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (
  deptno decimal(2,0) default NULL,
  dname varchar(14) default NULL,
  loc varchar(13) default NULL
);

INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');

INSERT INTO dept VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('20','RESEARCH','DALLAS');
INSERT INTO dept VALUES ('30','SALES','CHICAGO');
INSERT INTO dept VALUES ('40','OPERATIONS','BOSTON');

1. Display departmentno,dept name and total no of employees working in each deptment

     Select e.deptno, d.dname ,count(*)  from emp e,dept d
     where e.deptno = d.deptno  group by deptno;

    2. Query to fetch count of employees under a manager having manager’s salary greater than 5000


      select e.empno, e.sal,count(*) noofemployee from emp e  group by mgr  having e.sal >=2000               order by noofemployee;

   3. Difference between group by and having

• condition in WHERE clause is used to filter rows before you aggregate them and then HAVING clause comes in picture for final filtering,having is used to filter group
• you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.
• One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.
  

  4.  display the name of employees who are not managers.
     select ename,job from emp e where empno  
     not in (select mgr from emp where   e.empno= mgr)
 
  5. display the name of employees who are  managers.
    select ename,job from emp e where empno 
    in (select mgr from emp where e.empno= mgr)
  
  6. display name of employees who are managers 
    select * from emp where empno in (select mgr from emp)
  
  7. display name of employees who are not managers (ORACLE)
    select * from emp minus minus
   (select * from emp where empno in (select mgr from    emp))

 8. Show dept no and total no employees in ech dept
  select deptno, count(ename) from emp group by deptno

9. Show job title and various employee under each job title
select job,count(*) from emp group by job

10.Display dept no with more than 3 employees in each dept
 select deptno,count(*) from emp group by deptno having count(*) > 3 

11.Display the name of emp who earns highest sal
  select ename from emp where sal =(select max(sal) from emp)

12.Display the emp no and name,sal of the employee working as clerk and earning highest sal among clerks

select  empno,ename,sal from emp where job ='CLERK' and sal=(select max(sal) from emp where job='CLERK')

Java Interview questions for Senior Developers

#1 Difference between SOAP and REST



http://javarevisited.blogspot.in/2015/08/difference-between-soap-and-restfull-webservice-java.html

Why REST is better than SOAP?

Now that you know some differences between REST and SOAP web services, let's summarize our reasons of why REST is better choice for modern day web service requirement :

1. REST can be consumed by any client  e.g. Java, C++, Python client and even a web browser with Ajax and JavaScript.

2. REST is lightweight as compared to SOAP, it doesn't require CPU consuming XML parsing and it also consumes less bandwidth because unlike SOAP, REST doesn't require a SOAP header for every message.

3. SOAP is an old technology, all modern technical giant are using REST e.g. Google, Twitter, and Flickr.

4. REST is easy to learn, its just nouns and verbs. If you already know HTTP methods then its even easier.

5. Java has excellent support for RESTFul web services, well it also has good support for SOAP web services but you have lots of choices here e.g. Jersey, RESTLet etc.


#2. Variable Arguments (var arg) methods in Java1.5

   How variable arguments were handled prior to Java1,5
1. Either overload the method.
2. Can take an array or Collection and pass the no of argument wrapped in array or Collection like List, Set or Map.

Syntax:variable length arguments is presented by 3 dots like display(String...[]) We can replace the one dimensional array with var args like display(String... args)
so all versions of below calling will work
display()
display("hi"),
display("hi","hello")
display(new String("hi","hello")

So it provides greater flexibility because an no of argument can be passed.

Now if we have display(String[] args)
Out of above4 calling only 4th only with array as calling parameters will work,So we can not replace var args with one dimensional array.

Rules: 
We can only use only one var args in the method params we can't have display(int... args,int... args1) method like this, compiler will complain
For more than one parameters ,var args should be last like display(int a,int... args)
Q:Why vararg is kept as last param
Ans:because its easy for compiler to map the list of a params to args
like 
display(int a,int...args)
calling display(5,10,15,20,30)
it will assign 5 to a and others 10,15,20,30 to args

Every call to varargs method require an anonymous array to be created and initialized which could affect performance in time critical application. There is an alternative of varargs method to achieve better performance. suppose you have a variable argument method sum(int... num) and its called with 2 parameters on 90% of time. In order to avoid array creation and initialization you can use method overloading in Java to provide two versions of sum() which accept int instead of varargs. here is an example of better performance alternative of varargs for 90% of time

public int sum(int a);

public int sum(int a, int b);

public int sum(int... num);

An example of variable argument method from JDK is Arrays.asList(T... args)

List listOfString = Arrays.asList("Red", "green", "Blue");

#3 How to iterate through a map to print key and value separately

    //How to iterate   a map to print keys and values separately
        Map<String,String> map = new HashMap<String,String>();
        map.put("key1", "value1");
        map.put("key2", "value2");
        map.put("key3", "value3");
        // way1: iterate through entry set
        for(Entry<String,String> entry: map.entrySet()){
            String key =entry.getKey();
            String value =entry.getValue();
            System.out.println(" key ::"+key+" value "+value);
        }
        //way2: iterate through map
        System.out.println("Iterating the keys in the map");
        for (String key :map.keySet()){
            String value = map.get(key);
            System.out.println(" key :"+key+"Value "+value);
        }

#4 Difference between Collections.synchronizedMap(Collection c) and ConcurrentHashMap              ConcurrentHashMap You should use ConcurrentHashMap when you need very high concurrency in your project. It is thread safe without synchronizing the whole map. Reads can happen very fast while write is done with a lock. There is no locking at the object level. The locking is at a much finer granularity at a hashmap bucket level. ConcurrentHashMap doesn’t throw a ConcurrentModificationException if one thread tries to modify it while another is iterating over it. ConcurrentHashMap uses multitude of locks. SynchronizedHashMap Synchronization at Object level. Every read/write operation needs to acquire lock. Locking the entire collection is a performance overhead. This essentially gives access to only one thread to the entire map & blocks all the other threads. It may cause contention. SynchronizedHashMap returns Iterator, which fails-fast on concurrent modification. #5 What are the points to take care while crafting synchronization policy to entry integrity of data in the face of concurrent access Crafting a synchronization policy requires a number of decisions: which variables to make volatile, which variables to guard with locks, which lock(s) guard which variables, which variables to make immutable or confine to a thread, which operations must be atomic, etc. Some of these are strictly implementation details and should be documented for the sake of future maintainers, but some affect the publicly observable locking behavior of your class and should be documented as part of its specification .    #6 How does yield() method works public static void yield() is method of Thread Class. What yield() is supposed to do is make the currently running thread head back to runnable to allow other threads of the same priority to get their turn. So the intention is to use yield() to promote graceful turn-taking among equal-priority threads. There's no guarantee the yieldingthread won't just be chosen again over all the others Calling yield cause currently running thread to go directly to runnable from running without going to waiting/blockingsleeping             #7 How does join() works public final void join(long millis) throws InterruptedException    Lets one thread join at the end of other if inside main thread Thread t = new Thread(); t.start(); t.join(1000);    wait until thread t is done, but if it takes longer than 5,000   milliseconds, then stop waiting and become runnable anyway.                                                                 Param   value 0 indicates that main thread to wait forever until t1 is done  #8 How many ways Thread can leave running state    A call to sleep() Guaranteed to cause the current thread to stop executingfor at least the specified sleep duration (although it might be interruptedbefore its specified time). A call to yield() Not guaranteed to do much of anything, although typically it will cause the currently running thread to move back to runnableso that a thread of the same priority can have a chance.  A call to join() Guaranteed to cause the current thread to stop executing until the thread it joins with (in other words, the thread it calls join() The thread's run() method completes. A call to wait() on an object A thread can't acquire the lock on the object whose method code it's attempting to run. The thread scheduler can decide to move the current thread from running to runnable in order to give another thread a chance to run

Basic Linux commands

Useful  Linux Commands:

1. How to find and kill a process in Unix:

find a process: ps -ef |grep tomcat

kill process: kill -9 (pid)

2.How will you figure out free available space in Uni

Ans: df command – Shows the amount of disk space used and available on Linux file systems.du command – Display the amount of disk space used by the specified files and for each subdirectory.
df -h

3. Help for any command

man  command name like man df

4.How will you find files recursively that contains specific words in their contents

Search for a given string in all files recursively

$ grep -r "ramesh" * 

  
5.Search for a given string in a file (case in-sensitive search)

$ grep -i "the" demo_file

6. How will you find files recursively that contains specific words in their filename

find . -maxdepth 1 -name "*string*" -print 

It will find all files in the current directory (delete maxdepth 1 if you want

it recursive) containing "string" and will print it on the screen.

7.How to sort a file using unix command
sort -filename
for numeric sort
sort -n filename

reverse sort
sort -r filename      

reverse sort without duplicate

sort -u -i filename

8. How to count number of lines in a file in unix terminal

wc sort.txt

output : 5  5 41 sort.txt 

The three numbers produced in output correspond to number of lines, 

number of words and number of bytes 

9.How will you list nth column of a flat file in Unix ?
awk '{ print $2 $4 }' filename.txt

10.How will you find a process using specific port in Unix and Windows ?

netstat -tulpn

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1138/mysqld     
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      850/portmap        

  
filter for specfic port:
netstat -tulpn | grep :80 

11.How will you copy a file from one Unix host to another Unix host
In the following example, transfer all files (/var/www/html) from remote server called server1 to another server called server2:
scp -r user@server1:/var/www/html/ user@server2:/var/www/html/

I recommend using rsync command which will only push or download updated files. It can copy locally, to/from another host over any remote shell, or to/from a remote rsync daemon. In this example, copy files from remote server called server1 into /backup directory:
rsync -avz -e ssh user@server1:/var/www/html /backup   

Basic Linux/Unix Commands Part1

Command	Explanation
	Commands are entered at terminal Unix is case sensitive ~ Home directory _ and hyphen allowed in file names, spaces are not allowed hidden files start with . There is no recycle bin in Unix. Files deleted cannot be restored. every user has a home directory denoted by ~
passwd	Change password
ls ls –l <directory>ls -l <directory>ls -t ls -a ls -la	List files and subdirectories All information of files or subdirectories /<fullpath> Sort by creation time all directories including hidden directory . – current .. – parent
cd <directory> cd .. cd / cd ~ pwd	Change directory parent directory root directory home directory of user present working directory
man <command>	documentation for all unix commands if there is : at bottom next page of documentation press <spacebar> to get out of documentation press q
cat <filename> more <filename>	displays contents of file, scrolls if file is large display page wise contents of a file
mkdir <directoryname> mv <filename> <destination_directory> cp <source_file> <new_file> cp -R <source_dir> <destination_dir>	create new directory move a file to another location wildcards can be used to move multiple files copy files copy all files and subdirectories recursively
rm <filename> rmdir <directory> rm -R <directory>	There is no recycle bin in unix and file/directory once deleted cannot be recovered remove a file wildcards can also be used remove the directory Empty directory cannot be removed remove everything in directory including the directory
ln <source_file> <destination_file> ln -s <sourc_dir> <destination_dir> ls <linkeddirectory>	Link file Deleting a linked file does not delete original file Link a directory. create a symbolic link
find <wheretofind> -name <whattofind> -print find <wheretofind> -type d -print find <wheretofind> -type f –print find <wheretofind> -size +10M -print	Find a file. Print results on screen Get all directories Get all files Get all files >10MB

Linux Commands Part2

Command	Explanation
history !<line no>	Get all previous commands Run a command from history list
chmod <permission> <filename>	Change file/directory permissions
wc <file_name> wc -w <file_name> wc -l <file_name> ls | wc -l	count of lines, count of words, count of characters count of words count of lines count of files in a directory
head <file_name> tail <file_name> tail -f <file_name>	first few lines last few lines watch last lines of a file as it grows
clear	Clear screen
grep <word> <filename> grep -c <word> <filename> grep -i <word> <filename> grep -v <word> <filename> grep ^<word> <filename> grep <word>$ <filename> grep a. <filename> grep a* <filename> grep a[bc] <filename>	Searh a word in file count of times a word occurs case insensitive search lines which do not have the word line starting with word line ending with word . means any character. word a followed with any character occurrence of a followed with any number of characters searches for words ab or ac in file
diff <filename1> <filename2>	Compare contents of two files
tar –cvf <tar_file_name> <contents> tar –tvf <tar_file_name> tar –xvf <tar_file_name>	Create a tar file Get list of tar file contents Extract contents of tar file
env | more	get list of all environment variables
echo $SHELL echo $PATH	get name of shell get PATH variable contents
which <command>	Get the location of file for command
ps	Get list of all processes
ps aux | grep <username>	Get list of all process running for all users and then sort the processes for a user
kill <processId> kill -9 <processId>	Kills a process Kills a process and all its child processes
vi editor commands
<ctrl>+o <ctrl>+x vi <filename> i <ESC> x :wq :q	Save Exit Open an existing or new file in vi editor Go to insert mode Get out of insert mode Delete a character Write and quit Quit

 

Java Qestions

1. How do you create an Object instance without using new
   1. Using newInstance method of Class class Eg: Class clas = Class.forName("NewClass"); NewClass obj = (NewClass) clas.newInstance(); - Class.forName() loads the class and to create an object, we need to use newInstance() method of Class class. or using class loader: getClass().getClassLoader().loadClass("NewClass").newInstance();
      
   2. Using Object Deserialization Eg : ObjectInputStream objStream = new ObjectInputStream(inputStream );
      NewClass obj = (NewClass ) inStream.readObject();
      
   3. Using clone() of java.lang.Object Eg: NewClass obj = new NewClass();
      NewClass obj2 = obj.clone(); - Creates copy of an existing object