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