SQL Interview Questions

Hi Friends,

In this post, I'm going to share SQL interview questions that have been asked in multiple interviews.


Table - EmployeeDetails

EmpId	FullName	ManagerId	DateOfJoining
121	John Smith	322	01/10/2010
322	James	988	02/09/2009
421	Bill Gates	122	03/08/2008

Table - EmployeeSalary

EmpId	Project	Salary
121	P1	10000
322	P2	11000
421	P3	12000

Question 1:

Write a SQL query to fetch the count of employees working in project P1.

Answer:

Select count(*) from EmployeeSalary where project = 'p1';

Question 2:

Write a SQL query to fetch employee names having salary greater than or equal to 5000 and less than or equal to 10000.

Answer:

Select FullName
from EmployeeDetails ed
where EmpId in
(Select EmpId from EmployeeSalary
where Salary between 5000 AND 10000);


Question 3:

Write a SQL query to fetch project-wise count of employees sorted by project's count in descending order.

Answer:

Select Project, count(EmpId) as count
from EmployeeSalary
Group By project
Order by count DESC;

Question 4:

Write a query to fetch only the first name from the FullName column of EmployeeDetails table.

Answer:

Select substring(FullName, 0, CharIndex(' ',FullName)) from EmployeeDetails;

Question 5:

Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.

Answer:

Select ED.FullName, ES.salary
from EmployeeDetails ED
left join EmployeeSalary ES
ON ED.EmpId = ES.EmpId;

Question 6:

Write a SQL query to fetch all the employees who are also managers from EmployeeDetails table.

Answer:

Select Distinct ED.FullName
from EmployeeDetails ED
INNER JOIN EmployeeDetails M
ON ED.EmpId = M.ManagerId;

Question 7:

Write a SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.

Answer:

Select * from EmployeeDetails ED
where EXISTS
(Select * from EmployeeSalary S where ED.EmpId = S.EmpId);

Question 8:

Write a SQL query to fetch duplicate records from a table.

Answer:

Select FullName, EmpId, Count(*)
from  EmployeeDetails
Group By EmpId, FullName
Having Count(*) > 1;

Question 9:

Write a SQL query to remove duplicates from a table without using temporary table.

Answer:

Delete from EmployeeDetails
where EmpId IN
(Select EmpId from EmployeeDetails
Group By FullName
Having count(*) > 1);

Question 10:

Write a SQL query to create a new table with data and structure copied from another table.

Answer:

Select * INTO newTable from oldTable;

Question 11:

Write a SQL query to create an empty table with same structure as some other table.

Answer:

Select *INTO newTable from oldTable where 1 = 0;

Question 12:

Write a SQL query to fetch common records between two tables.

Answer:

Select * from EmployeeDetails
INTERSECT
Select * from EmployeeDetails

Question 13:

Write a SQL query to fetch records that are present in one table but not in another table.

Answer:

Select * from EmployeeDetails
MINUS
Select * from EmoloyeeDetails

String interview Questions Part - 1

Hi Friends,

In this post, I'm going to share String interview questions that have been asked in multiple interviews.

Question 1:

How to print duplicate characters from String?

Answer 1:

Steps:

• Run for loop on each character and add that character in HashSet.
• If add() method returns false, just print that character[duplicate one].

Question 2:

Check if two strings are anagrams of each other?

Answer 2:

Two strings are anagrams of each other, if they contain same exact characters.

e.g.: army and mary are anagrams of each other. But Army and Mary are not.

Remember: String.contains(charsequence) checks for capitalization. So, if String is "Abc", then "Abc".contain("a") returns false.

Steps:

• Compare lengths of both strings. If they are different, strings are not anagrams of each other.
• Run for loop on 1st string and check for every character that whether it is present in another string.
• And then create a new string from 2nd string by removing matched character.

Question 3:

Write the code to reverse a string using recursion.

Answer 3:

String sz = "abc";

int index = 0;

StringBuffer strBuffer = new StringBuffer();

public void main(String[] args){

    String strReversed = reverse(sz);

    System.out.println("Reversed String is : "+strReversed);

}

public String reverse(String str){

    index++;

    strBuffer.append(str.charAt(str.length()-index));

    if(index != str.length())

        reverse(str);

    return strBuffer.toString();

}

Question 4:

How will you check if a String contains only digits?

Answer 4:

Use Pattern class.

Pattern pattern = Pattern.compile(".*\\d.*");

boolean matched = pattern.matcher(inputString).matches(); // If it returns true, that means, String contains digits only.

Note: If we take Pattern pattern = Pattern.compile(".*\\D.*");, then matches() method will return false, if String contains all digits.

Question 5:

Find all permutations of a string.

Answer 5:

public class StringPermutations{

    public static void main(String[] args){

        String sz = "ABC";

        int length = sz.length();

        StringPermutation  perm = new StringPermutation();

        perm.permute(sz, 0, length-1);

    }

    private void permute(String sz, int len, int right){

        if(len == right)

            System.out.println(sz);

        else{

            for(int i = 1; i<=right; i++){

                sz = swap(sz, len, i);

                permute(sz, len+1, right);

                sz = swap(sz, len, i);

            }

        }

    }

    public String swap(String s, int i, int j){

        char temp1;

        char[] chArray = s.toCharArray();

        temp1 = chArray[i];

        chArray[i] = chArray[j];

        chArray[j] = temp1;

        return String.valueOf(chArray);

    }

}

Question 6:

Find duplicate characters in String.

Answer 6:

• Convert String to char array using String.toCharArray().
• Put each character in HashSet. If add() method returns false, means, it is a duplicate character and print it.

Question 7:

Count number of vowels and consonants in String.

Answer 7:

• Convert String to char array using String.toCharArray().
• Run a for loop and compare each character with 'a', 'e', 'i' , 'o', 'u'.  If character matches with anyone of these, it is a vowel else it is a consonant.

Question 8:

Count occurrence of given character in String.

Answer 8:

• Convert String to char array using String.toCharArray().
• Run for loop and before storing character in HashMap, first call get(char) and check the return value.
• If it is -1, then store character with value 1. Else store it with value++.

Question 9:

How to reverse words in a sentence?

Answer 9:

Sentence: "Javafreeway.blogspot.com" is good tutorial.

Output: "tutorial good is "Javafreeway.blogspot.com"

Steps:

• Use split() method to get a String array of all words.
• Now just iterate this array in  reverse order and append words in StringBuffer.

Question 10:

Check if a String is Palindrome.

Answer 10:
A string is palindrome, if reading it from both ends results in same string.

• Convert a String into char array using String.toCharArray().
• Run a for loop in reverse order and store each character in StringBuilder or StringBuffer.
• Now compare original String with this new one. If both are equal , then String is palindrome.

REST API interview Questions

In this post, I'm going to share REST API interview questions actually asked in I.T. companies.

Question 1:

What exactly is RESTful programming?

Answer:
 
An architectural style called  REST [Representational State Transfer] states that Web applications should use HTTP methods for communication.
GET should be used for lookups.
PUT, POST and DELETE requests should be used for mutation, creation and deletion respectively.

Keep in mind that GET request should never be used for updating information. e.g. a GET request for adding an item to a cart :

http://javafreeway/addToCart?cartId=12345&item=4321    would not be appropriate.

GET requests should be idempotent. That means, issuing a request twice should be no different from issuing it once. That's what makes the request cacheable.
An addToCart request is not idempotent, because issuing it twice will add two copies of same item to the cart. A POST request is clearly appropriate in this context.

Programmatic Explanation:

Create a user with three properties:

POST /user
fname=Johnny&lname=Joe&age=30

The server responds:
200 OK
location: /user/321

Now, we can retrieve the user information:

GET /user/321

The server responds:

200 OK
<fname>johnny</fname><lname>Joe</lname><age>30</age>

To modify the record (age and lname will remain unchanged)

PATCH /user/321
fname=john


To update the record (lname and age will become NULL)

PUT /user/321
fname=john


Question 2:

What is the difference between REST Endpoints and REST resources?

Answer:

Resource is a RESTful subset of endpoint.

REST endpoints are used to define REST requests.
REST resources are used to define Data sets which are returned.

In simple words,  endpoints represents URL.
And resource represents the data to be returned.

e.g:

An endpoint is the location where a service can be accessed.

http://javafreeway.blogspot.com
services/service.asmx

A resource refers to one or more nouns being served.

/api/users/johnny              // look up johnny from a list of users.
/v2/books/4321                //  Get book with ID 4321 in API v2 schema

All of the above are service endpoints, but the bottom ones are for resources.


Question 3:

What is the most popular way to present a resource in REST?

Answer:

XML, JSON.

Question 4:

What are the core components of an HTTP request?

Answer:

Each HTTP request includes 5 key elements:

• The verbs : GET, POST, PUT, DELETE, PATCH
• URI: Uniform Resource Identifier. [Identifies resource on the server]
• HTTP version
• Request header: Carries metadata for the HTTP request message
• Request Body: represents message content

Question 5:

What are the core components of an HTTP response?

Answer:

An HTTP response includes 4 key elements:

• Status/Response code: indicates server status for the resource present in HTTP request. e.g. 404 means "resource not found"  and 200 means , "response is OK".
• HTTP Version
• Response Header: contains metadata for HTTP response
• Response Body: Indicates response message content

Question 6:

What is the difference between PUT and POST operations?

Answer:

                    PUT                                                                                        POST

PUT is used for update operation                                    POST is used for create operation

PUT method is idempotent                                              POST is not idempotent

Use PUT when you want to modify a singular                Use POST when you want to add a
resource which is already a part of resources                   child resource under resources
collection. PUT replaces the resource in its                      collection
entirety. Use PATCH if request updates part
of the resource.                            
                                                                                          

PUT is idempotent, so you can cache the                         Responses to this method are not
response                                                                             cacheable, unless the response
                                                                                          includes appropriate  Cache-Control
                                                                                          fields.

Generally, always use PUT for update                             Always use POST for create
operations.                                                                         operations.

Question 7:

What are the tools available for testing Web services?

Answer:

• SOAP UI tool
• Poster for Firefox Browser
• The Postman extension for Chrome.

Question 8:

Which Java API helps in developing a RESTful Web Service?

Answer:

There are many frameworks and libraries available that a developer can use to create RESTful web services in java.
e.g.: the JAX-RS library is a standard way to develop a REST web service.

Also Jersey is another most popular implementations of JAX-RS.
There are others like RESTEasy, RESTlet and Apache CFX.

 If you liked this post, then Hit 'LIKE' button.

Java QUIZ Part - 3

You must have gone through my tutorial on Java QUIZ part 2 here: Java QUIZ Part - 2.

In this post, I'm going to extend Java Quizzes by adding more.


Quiz 21:
    

    public class Quiz {

        public static void main(String[] args) {

            String s = "";

            StringBuffer sb1 = new StringBuffer("Hi");

            StringBuffer sb2 = new StringBuffer("Hi");
            StringBuffer sb3 = new StringBuffer(sb2);
            StringBuffer sb4 = sb3;

            if(sb1.equals(sb2)) s += "1";

            if(sb2.equals(sb3)) s += "2";

            if(sb3.equals(sb4)) s += "3";

            String s1 = "Hi";

            String s2 = "Hi";

            String s3 = s2;

            if(s1.equals(s2)) s += "4";

            if(s2.equals(s3)) s += "5";

            System.out.println(s);

        }

    }

O/P: 345

Explanation:

StringBuffer class doesn’t override equals() method.



Quiz 22:
 

    Is the following code valid override?
   
    class QuizClass{
        void add(int I, int j){

         }
    }

    class QuizSubclass extends QuizClass{
        public void add(int I, int j){

        }
    }

O/P : true

Explanation:

In subclass, we can assign higher access privilege.

Quiz 23:

    

    Is the following code valid override?

    class QuizClass{
        private void add(int i, int j){

        }
    }

    class QuizSubclass extends QuizClass{
        public void add(int i, int j){

        }
    }

    O/P : true

    Note: But, you cannot call add() method on QuizClass as it is private.

    e.g.

    QuizClass qc = new QuizSubclass();

    qc.add(1,2);// Cannot call, as add() is private in QuizClass.

Quiz 24:

    

    Is the following code valid override?

    class QuizClass{
        static void add(int i, int j){

        }
    }

    class QuizSubclass extends QuizClass{
        static void add(int i, int j){

        }
    }

    O/P : It is valid.

    

    Note: 

    But static methods cannot be overridden. It is called method hiding.

    So, when we use below code:

    QuizClass qc = new QuizSubclass();

    qc.add(1,2);

    It calls add() method of QuizClass.

Quiz 25:

    

    Is the following code valid override?

    class QuizClass{
        static void add(int i, int j) throws Exception{

        }

    }

    class QuizSubclass extends QuizClass{
        static void add(int i, int j) throws IOException{

        }
    }

    O/P : It is valid.

    

    Explanation:

    

    Overridden method can throw the subclass of Exception[or same exception] thrown by 
    method in parent class.

Quiz 26:

    

    Is the following code valid override?

    class QuizClass{
        static void add(int i, int j){


        }
    }

    class QuizSubclass extends QuizClass{
        static void add(int i, int j){

        }
    }

    O/P : It is valid.

Quiz 27:

    

    Is the following code valid override?

    
    class QuizClass{
        protected void add(int i, int j) throws Exception{

        }
    }

    class QuizSubclass extends QuizClass{
        public void add(int i, int j) throws IOException{

        }
    }

    O/P :  Not valid. Compiler error. Attempting to assign weaker access privilege.

Quiz 28:

    class QuizA{

    }

    

    class QuizB extends QuizA{

    }

    

    class QuizC extends QuizB{

    }

    public class MainClass{

        static void overloadedMethod(QuizA a){

            System.out.println("ONE");

        }

        

        static void overloadedMethod(QuizB b){

            System.out.println("TWO");

        }

        

        static void overloadedMethod(Object obj){

            System.out.println("THREE");

        }

        public static void main(String[] args){

            QuizC c = new QuizC();

            overloadedMethod(c);

        }

    }

    O/P: TWO

    Explanation: In method overloading, more specific method is chosen over generic.

Quiz 29:

    

    public class MainClass{

        static void overloadedMethod(Integer I, int i){

            System.out.println("ONE");

        }

        

        static void overloadedMethod(int i, Integer I){

            System.out.println("TWO");

        }

        

        static void overloadedMethod(Object obj){

            System.out.println("THREE");

        }

        public static void main(String[] args){

            overloadedMethod(1,2);

        }

    }

    O/P: Compile-time error

Quiz 30:

    Question: In a class, one method has two overloaded forms. One form is defined as static and   
    another form is defined as non-static. Is that method properly overloaded?

    Answer: Yes. Compiler checks only method signature to verify whether a particular method is 
    properly overloaded or not. It doesn't check static or non-static feature of method.

    If you really like this post, press Hit on 'Like'.