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, QA, BA, supports professional, project manager or any other technical 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 usingWHERE 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 the solution, you can post it here for everyone's benefit. If you are looking for more challenging SQL query exercises and puzzles then you can also check Joe Cleko's SQL Puzzles And Answers, one of the best books to really check and improve your SQL skills.

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

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

These 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 keyDeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as 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, we have used RIGHT OUTER JOIN because we need the name of the department from Department table which is on the right side of JOIN clause, even if there is no reference of dept_id on Employee table. 

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

SELECT GetDate(); 

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

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

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

SELECT EmpName FROM  Employees WHERE  Salary>=10000;

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

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

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 the 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 subquery 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 a standard employee table with an additional column mgr_id, which contains employee id of the manager.
Answer: You need to know about self-join to solve this problem. In Self Join, you can join two instances of the 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 manager name in two column e.g.

name  manager_name
John   David

One follow-up is to modify this query to include employees which don't have a manager. To solve that, instead of using the 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 the 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 the index will not invoke, but if the given 2 columns contain the primary index(first column while creating index) then the index will invoke. In this case, Index will be used because EmpId and EmpFirstName are primary columns.