Saturday, April 27, 2013

10 Frequently asked SQL Query Interview Questions

In this article I am giving some examples of SQL queries which is frequently asked when you go for a programming interview, having one or two year experience on this field .Whether you go for Java developer position or any other technology programmer position, may interviewer expect you to answer basic questions from Database and SQL. It's also obvious that if you are working from one or two years on any project there is good chance that you come across to handle database, writing SQL queries to insert, update, delete and select records. One simple but effective way to check candidate's SQL skill is by asking these types of simple query. They are are neither very complex nor very big, but yet they cover all key concept a programmer should know about SQL. These queries test your SQL skill on Joins, both INNER and OUTER join, filtering records by using WHERE and HAVING clause, grouping records using GROUP BY clause, calculating sum, average and counting records using aggregate function like AVG(), SUM() and COUNT, searching records using wildcards in LIKE operator, searching records in a bound using BETWEEN and IN clause, DATE and TIME queries etc. If you have faced any interesting SQL query or you have any problem and searching for solution, you can post it here for everyone's benefit. 


SQL Query Interview Questions


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 : You can find maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate maximum salary in each group or each department.

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

This questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT OUTER JOIN to include departments without any employee as well.  Here is the query

SELECT DeptName, MAX(Salary) FROM Employee e LEFT JOIN Department d ON e.DeptId = d.DeptID;


Question 3: Write SQL Query to display current date.
Answer : SQL has built in function called GetDate() which returns current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also has equivalent functions.
SELECT GetDate(); 


Question 4: Write an SQL Query to check whether date passed to Query is date of given format or not.
Answer : 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. Remember ISDATE() is a MSSQL function and it may not work on Oracle, MySQL or any other database but there would be something similar.

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.
Answer : This SQL query is tricky but you can use BETWEEN clause to get all records whose date fall between two dates.
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’
Answer : 
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 '%JOE%';


Question 10: Write a SQL Query to find  year from date.
Answer :  Here is how you can find Year from a Date in SQL Server 2008 
SELECT YEAR(GETDATE()) as "Year";


Question 11 : Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Answer : You can use following query to select 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);


Question 12 : 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.
Answer : This query can be written using sub query as shown below :
SELECT student, marks from table where marks > SELECT AVG(marks) from table)


Question 13 : How do you find all employees which are also manager? .
You have given 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 can join two instances of same table to find out additional details 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.

name  manager_name
John   David

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 also include employees without managers.

Question 14 : 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? For example if Index is on EmpId, EmpFirstName and EmpSecondName and you write query like

SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'

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. In this case Index will be used because EmpId and EmpFirstName are primary columns.


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

46 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. its very helpful for all give some more example

    ReplyDelete
  19. 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
  20. querys r very helpful to me need more querys to practice

    ReplyDelete
    Replies
    1. sql is not case sensitive

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

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

    ReplyDelete
    Replies
    1. Yes, I believe GETDATE() method works only SQL Server, not sure if it works on Oracle or MySQL. Though in MSSQL it returns :

      SELECT GETDATE()

      2014-12-11 15:40:02.910

      Delete
    2. Hi,

      I was asked following SQL query in a recent interview :

      You have an Employee table with id, name, salary, manager_id and dept_id and you have a Deparatment table with dept_id and dept_name. Write SQL queries to

      1) Print name of Employee and their Manager's name
      2) Print All deparatment and number of employees on it
      3) Print all employees who has higher salary than average salary of their department.

      I solved them like following, but he says not correct ......

      1) SELECT e1.name, e2.name as manager FROM Employee e1 JOIN Employee e2 WHERE e1.manager_id = e2.id

      2) SELECT d.dept_name, count(*) as NumOfEmploye FROM Employee e, Department d WHERE e.dept_id = d.dept_id

      third one I couldn't able to solve in limited time.

      Please suggest why he said my answers are wrong, I still didn't get it. thank you

      Delete
    3. Hello Mansi, I think your first query is correct. That's the right way to do the self join. but you second query has a little mistake. Since Question was about print all deparatments, you should have used left outer join instead of innner join. In your query it will not print departments where no employee is working. So the correct query would be :

      SELECT d.dept_name, count(*) as NumOfEmploye FROM Depatment d LEFT JOIN Employee e ON d.dept_id = e.dept_id

      Remeber I have also change the order, bringing Department table at left.

      3) For third query I think you can use a subquery to solve the problem. First find out average salary of the department and then print all employee whose salry is greater than average salary.

      Delete
    4. Hi Mansi, Amit here. You can use correlated subquery to find all employees whose salary is greater than average salary in their department, here is the SQL query :

      SELECT emp_name, salary FROM Employee e1 WHERE salary >= (SELECT AVG(salary) FROM Employee e2 where e1.department = e2.department)

      Let me know if you have any question, happy to help.

      Delete
  23. An interesting question for freshers, you have a table called Scorecard with a numeric column score , containing last 6 scores of a cricket player as shown below :

    create table Scorecard (int score)

    insert into Scorecard values (144);
    insert into Scorecard values (144);
    insert into Scorecard values (99);
    insert into Scorecard values (23);
    insert into Scorecard values (68);
    insert into Scorecard values (105);

    Can you write a SQL query to find the second largest score from this table? for example in this case it shoud return 105

    ReplyDelete
    Replies
    1. This question can be solved with and without correlated subquery :

      without correlated sub query :

      select MAX(score) from Scorecard where score NOT IN (select MAX(score) from Scorecard)

      using correlated sub query

      select MAX(score) from Scorecard s1 where 2 = (select count(distinct score) from Scorecard s2 where s2.score >= s1.score)

      Delete

Java67 Headline Animator