SQL is still an important scripting language. The language is actively used by number of organisations around the world. When you go for programming interview, most of the tech questions are asked about SQL. Interviewer may expect you to answer basic SQL related questions. If you have experience of working for one or two years on web projects then, there is good chance that you know how to handle database, write SQL queries, insert, update, delete or select record.


1. SQL Query to find second highest salary of Employee

You can answer to this question in many different ways. You can use SQL Join or Subquery to solve this problem. This SQL query can also be solved using following subquery-

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

2. SQL Query to find Max Salary from each department

You can find maximum salary for each department by grouping all records using DeptID and then use MAX function to calculate maximum salary in each group or department.

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

3. Write SQL Query to display current date

SQL has built-in function that you can use to find the date. If you use the function, it returns timestamp. The function works in Microsoft SQL Server as well as other software vendors such as Oracle, MySQL.

SELECT GetDate(); 

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

SQL has built-in function to check the specified format of given date. You can use the function IsDate() to check the same. It is MSSQL function so, it may not support in Oracle, MySQL or any other database. 

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

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

This is a tricky query that you can use between clause to get all records whose date falls between two dates. You can use following syntax to get the answer.

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

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

Answering to this question is fairly simple. Use simple syntax to get the answer –

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

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

SELECT EmpName FROM Employees WHERE Salary>=10000;

8. Write a SQL Query to find year from date

You can use the following query to find year from a date in SQL server 2008.

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

9. Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

You can use simple query like this to select specific date in distinct record –

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);

10. 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

This query can be written using sub query as shown below.

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

TAGS :