10 Frequently asked SQL Query Interview Questions

In this article, I am giving or so examples of SQL queries which is oft asked when you lot boot the bucket for a programming interview, having 1 or 2 yr sense on this field. Whether you lot boot the bucket for Java developer position, QA, BA, supports professional, projection managing director or whatever other technical position, may interviewer await you lot to respond basic questions from Database as well as SQL. It's every bit good obvious that if you lot are working from 1 or 2 years on whatever projection at that topographic point is skillful run a jeopardy that you lot come upwards across to conduct maintain database, writing SQL queries to insert, update, delete as well as choose records. One elementary but effective agency to banking concern check candidate's SQL science is past times bespeak these types of elementary query. They are are neither really complex nor really big, but nonetheless they comprehend all key concept a programmer should know near SQL.

These queries assay your SQL science on Joins, both INNER as well as OUTER join, filtering records past times using WHERE as well as HAVING clause, grouping records using GROUP BY clause, calculating sum, average as well as counting records using aggregate function similar AVG(), SUM() as well as COUNT(), searching records using wildcards inwards LIKE operator, searching records inwards a outpouring using BETWEEN as well as IN clause, DATE as well as TIME queries etc. If you lot conduct maintain faced whatever interesting SQL query or you lot conduct maintain whatever work as well as searching for the solution, you lot tin ship it hither for everyone's benefit. If you lot are looking for to a greater extent than challenging SQL query exercises as well as puzzles as well as so you lot tin every bit good check Joe Cleko's SQL Puzzles And Answers, 1 of the best books to actually banking concern check as well as ameliorate your SQL skills.

SQL Query Interview Questions as well as Answers

Question 1: SQL Query to notice minute highest salary of Employee

Answer: There are many ways to notice minute highest salary of Employee inwards SQL, you lot tin either utilisation 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 notice minute highest salary inwards SQL for to a greater extent than ways to solve this problem.

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

Answer: You tin notice the maximum salary for each subdivision past times grouping all records past times DeptId as well as and so using MAX() business office to calculate maximum salary inwards each grouping or each department.

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

These questions acquire to a greater extent than interesting if Interviewer volition inquire you lot to impress subdivision advert instead of subdivision id, inwards that case, you lot demand to bring together Employee tabular array amongst Department using unusual key DeptID, brand certain you lot create LEFT or RIGHT OUTER JOIN to include departments without whatever employee every bit well. Here is the query

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

In this query, nosotros conduct maintain used RIGHT OUTER JOIN because nosotros demand the advert of the subdivision from Department tabular array which is on the right side of JOIN clause, fifty-fifty if at that topographic point is no reference of dept_id on Employee table.

Question 3: Write SQL Query to display the electrical current date.

Answer: SQL has built-in business office called GetDate() which returns the electrical current timestamp. This volition piece of work inwards Microsoft SQL Server, other vendors similar Oracle as well as MySQL every bit good has equivalent functions.

SELECT GetDate();

Question 4: Write an SQL Query to banking concern check whether appointment passed to Query is the appointment of given format or not.

Answer: SQL has IsDate() business office which is used to banking concern check passed value is a appointment or non of specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an MSSQL business office as well as it may non piece of work on Oracle, MySQL or whatever other database but at that topographic point would hold out something similar.

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

It volition render 0 because passed appointment is non inwards right format.

Question 5: Write an SQL Query to impress the advert of the distinct employee whose DOB is betwixt 01/01/1960 to 31/12/1975.

Answer: This SQL query is tricky, but you lot tin utilisation BETWEEN clause to acquire all records whose appointment autumn betwixt 2 dates.

SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Question 6: Write an SQL Query notice discover of employees according to gender whose DOB is betwixt 01/01/1960 to 31/12/1975.

Answer :

SELECT COUNT(*), gender activity from Employees  WHERE  DOB BETWEEN '01/01/1960' AND '31/12/1975'  GROUP BY sex;

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

Answer :

SELECT EmpName FROM  Employees WHERE  Salary>=10000;

Question 8: Write an SQL Query to notice advert of employee whose advert Start amongst ‘M’

Answer :

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

Question 9: notice all Employee records containing the give-and-take "Joe", regardless of whether it was stored every bit JOE, Joe, or joe.

Answer :

SELECT * from Employees  WHERE  UPPER(EmpName) like '%JOE%';

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

Answer: Here is how you lot tin notice Year from a Date inwards SQL Server 2008

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

Question 11: Write SQL Query to notice duplicate rows inwards a database? as well as and so write SQL query to delete them?
Answer: You tin utilisation the next query to choose 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 tabular array which contains 2 column Student as well as Marks, you lot demand to notice all the students, whose marks are greater than average marks i.e. listing of inwards a higher house average students.
Answer: This query tin hold out written using subquery every bit shown below:

SELECT student, marks from tabular array where marks > SELECT AVG(marks) from table)

I am giving or so examples of SQL queries which is oft asked when you lot boot the bucket for a prog 10 Frequently asked SQL Query Interview Questions

Question 13: How create you lot notice all employees which are every bit good manager? .
You conduct maintain given a measure employee tabular array amongst an additional column mgr_id, which contains employee id of the manager.

Answer: You demand to know near self-join to solve this problem. In Self Join, you lot tin bring together 2 instances of the same tabular array to notice out additional details every bit shown below

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

this volition demonstrate employee advert as well as managing director advert inwards 2 column e.g.

advert manager_name
John David

One follow-up is to alter this query to include employees which don't conduct maintain a manager. To solve that, instead of using the inner join, merely utilisation left outer join, this volition every bit good include employees without managers.

Question 14: You conduct maintain a composite index of 3 columns, as well as you lot exclusively render the value of 2 columns inwards WHERE clause of a choose query? Will Index hold out used for this operation? For illustration if Index is on EmpId, EmpFirstName, as well as EmpSecondName and you lot write query like

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

If the given 2 columns are secondary index column as well as so the index volition non invoke, but if the given 2 columns comprise the top dog index(first column piece creating index) as well as so the index volition invoke. In this case, Index volition hold out used because EmpId and EmpFirstName are top dog columns.

Hope this article volition help you lot to conduct maintain a quick practise whenever you lot are going to attend whatever interview as well as non conduct maintain much fourth dimension to boot the bucket into the deep of each query, but if you lot conduct maintain skillful fourth dimension to prepare as well as so I advise you lot to read as well as solve SQL queries from Joe Celko's SQL Puzzles as well as Answers, Second edition, 1 of the best mass for SQL query lovers as well as enthusiastic.

Other Interview Questions posts from Blog

P.S. - If you lot are looking for online training/course to larn SQL from scratch, I advise you lot joining Introduction to SQL past times Jon Flanders. It's 1 of the best sourse to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, gropuing data, advanced filtering as well as SQL query optimization.