Saturday, April 27, 2013

10 Frequently asked SQL Query Interview Questions



In this article I am giving example of some SQL query which is asked when you go for interview who is having one or two year experience on this field .whenever you go for java developer position or any other programmer position interviewee expect that if you are working from one or two years on any project definitely you come across to handle this database query, so they test your skill by asking this type of simple query.

Question 1: SQL Query to find second highest salary of Employee

Answer : There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery :

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
 

See How to find second highest salary in SQL for more ways to solve this problem.

Question 2: SQL Query to find Max Salary from each department.

Answer :

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.
 

Question 3:Write SQL Query to display current date.

 Ans:SQL has built in function called GetDate() which returns current timestamp.

SELECT GetDate();
 

Question 4:Write an SQL Query to check whether date passed to Query is date of given format or not.

Ans: SQL has IsDate() function which is used to check passed value is date or not of specified format ,it returns 1(true) or 0(false) accordingly.

SELECT  ISDATE('1/08/13') AS "MM/DD/YY";
 

It will return 0 because passed date is not in correct format.

Question 5: Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

Ans:
SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND31/12/1975’;

Question 6:Write an SQL Query find number of employees according to gender  whose DOB is between 01/01/1960 to 31/12/1975.


Answer : SELECT COUNT(*), sex from Employees  WHERE  DOB BETWEEN ‘01/01/1960 ' AND ‘31/12/1975’  GROUP BY sex;

Question 7:Write an SQL Query to find employee whose Salary is equal or greater than 10000.

Answer : SELECT EmpName FROM  Employees WHERE  Salary>=10000;

Question 8:Write an SQL Query to find name of employee whose name Start with ‘M’

Ans: SELECT * FROM Employees WHERE EmpName like 'M%';

Question 9: find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

Answer : SELECT  * from Employees  WHERE  upper(EmpName) like upper('joe%');

Question 10: Write a SQL Query to find  year from date.

Answer :  SELECT YEAR(GETDATE()) as "Year";

Hope this article will help you to take a quick practice whenever you are going to attend any interview and not have much time to go into the deep of each query.

Other Interview Questions posts from Java67 Blog

41 comments:

  1. Really gud bro iam seraching for two months onwards thnxs...

    ReplyDelete
  2. Nice stuffs...Thanks..pls also try to update somethng new if you have

    ReplyDelete
  3. Excellent Yaar..I was searching such question..Thanks keep posting

    ReplyDelete
  4. very nice, really helpful .... need more such type of questions.

    ReplyDelete
  5. thnx bro it's really helpful

    ReplyDelete
  6. Thanks For uploading............
    And please upload for subqueries Also............

    ReplyDelete
    Replies
    1. Thanks pal...I needed these things...

      Delete
  7. Question 9: find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

    SQL>SELECT * FROM Employees
    WHERE UPPER(EmpName) LIKE '%JOE%';

    ReplyDelete
    Replies
    1. Select * from employee where EmpName in ('Joe','JOE','joe')

      Delete
    2. This wont find jOE, jOe kind of names. Query given by previous Anonymous is correct.

      Delete
  8. Even a children can create all these query's ......

    ReplyDelete
  9. good effort publish more queries

    ReplyDelete
  10. I was asked this SQL Question in recent interview :

    1) You have a composite index of three columns, and you only provide value of two columns in WHERE clause of a select query? Will Index be used for this operation?

    Can any one please help here?

    ReplyDelete
    Replies
    1. If the given two columns are secondary index column then index will not invoke

      but if the given 2 columns contain primary index(first col while creating index) then index will invoke.

      Delete
    2. in your case the composite index would not work because of the column not included in the where clause.

      Still you want to use an index you give an index hint select /*+ INDEX(TABLE_NAME IDX_NAME) */ * from table_name;

      After imposing the force index you can read the explain plan and verify the cost , if index scan is more costlier than the FTS then its not a good idea to go with index

      Delete
  11. Good Questions.
    Need more sql questions of this kind. :)
    Thanks.

    ReplyDelete
  12. Initial few question are upto standard . Other are very very basic SQL queries

    ReplyDelete
  13. nice upload some query for experience developer

    ReplyDelete
  14. Some queries are very good but some one is very basic and please update this page after some period.

    ReplyDelete
  15. My list of some good SQL Query based interview Questions :

    1) Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
    2) TODO
    3) TODO
    4) TODO

    ha ha ha

    ReplyDelete
    Replies
    1. for selecting the distinct records:

      select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno)


      to Delete:

      delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

      Delete
    2. I know to ways one way use max and another way use distinct
      one way :DELETE
      FROM TestTable
      WHERE ID NOT IN
      (
      SELECT MAX(ID)
      FROM TestTable
      GROUP BY NameCol)
      GO
      and
      another way
      Select Distinct id, name into #temp from emp;
      Truncate table emp;
      Insert into emp(id,name) select id,name from #temp;

      Delete
  16. Really good ones, Can you add more do this list please?

    ReplyDelete
    Replies
    1. Hi Anonymous, I am planning to add more such SQL queries, but if you have been asked something then you can also share with us. Thanks

      Delete
  17. I was asked to write following SQL queries in interview. There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students. Here is what I wrote:

    SELECT Student, Marks FROM Products WHERE Marks> AVG(Marks);

    And he says WRONG? Hmmmmm.

    ReplyDelete
    Replies
    1. Hi Shubhangi,

      You can not use the where clause with SQL functions. Instead you should use HAVING. So, correct query will be- SELECT Student,Marks FROM Products HAVING Marks>AVG(Marks);

      Delete
    2. select student, marks from table where marks > (select avg(marks) from table)....


      will this work?

      Delete
    3. rahul's not right answer......above subquery will be good......

      Delete
    4. Shubhangi, Aggreate functions can't be used in WHERE clause.

      Rahul, HAVING can not be used without GROUP BY.

      Subquery will serve the purpose.

      Delete
    5. SELECT Student, Marks FROM products
      WHERE marks>(select avg(marks) from products);
      Here we can't use having clause as it is not a group by expression. We can't use group functions like avg,sum,max in where clause even it is a group by expression.

      Delete
    6. I think this is right

      SELECT Student, Marks FROM Products WHERE Marks > (SELECT AVG(Marks) FROM Products);

      Delete
    7. You can try this -
      WITH cte_mks(Student,AvgMarks)
      AS
      (SELECT Student,AVG(marks)mks
      FROM GROUP BY Student)
      SELECT s.Student,Marks
      FROM s
      INNER JOIN cte_mks m on s.Student=m.Student
      and s.marks>m.AvgMarks

      Delete
  18. “DBMS/RDBMS” Interview Questions With Answers".


    Here, are some sample questions based on “RDBMS”. Read it carefully as these questions will improve your basic concept on RDBMS, and will help you in cracking any interview.

    Click on any question to find out it's answers:




    1) What Is A Database?

    2) What Is DBMS & Database?

    3) What Are The Advantages Of DBMS?

    4) What Are The Disadvantages In File Processing System?

    5) Discuss How To Know When There Is A Need Of DBMS & When There Is Not?



    6) What Is Parallel & Distributed Database? Differentiate Them.

    7) Differentiate Between File-Oriented System & Database System?

    8) What Are The Applications Of Databases?

    9) What Is The Importance Of Databases?

    10) What Is DBMS & RDBMS?

    ReplyDelete
  19. its very helpful for all give some more example

    ReplyDelete
  20. Anohter interesting query I come across on an interview was "how do you find all employees which are also manager?". give an standard employee table with an additional column mgr_id, which contains employee id of manager.
    Answer : You need to know about self join to solve this problem. In Self Join, you join two instances of same table as shown below

    Select e.name, m.name from Employee e, Employee m where e.mgr_id = m.emp_id;

    this will show employee name and manger name in two column e.g.

    surabhi Balaji
    snhea Balaji

    One follow-up is to modify this query to include employees which doesn't have manager. To solve that, instead of using inner join, just use left outer join, this will include employees without managers.

    ReplyDelete
  21. querys r very helpful to me need more querys to practice

    ReplyDelete
    Replies
    1. sql is not case sensitive

      Delete
  22. SELECT *
    FROM Customer_Entry
    WHERE (Customer_Name LIKE '%JOE%')

    ReplyDelete
  23. The answers given certainly aren't portable or platform independent SQL

    ReplyDelete

Java67 Headline Animator