Most Frequently Asked SQL queries In Interviews.

Most Frequently Asked SQL queries In Interviews. TOP 10 SQL Queries and Solutions . Common SqL Queries Asked In Interviews. Top SQL Interview Questions And Answers.

1. SQL Query to find second highest salary of Employee

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

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

You can find the 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.

3. Write SQL Query to display the current date.


SQL has built-in function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also has equivalent functions.
SELECT GetDate();

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

SQL has IsDate() function which is used to check passed value is a date or not of specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an 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.

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

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


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 an employee whose Salary is equal or greater than 10000.

SELECT EmpName FROM  Employees WHERE  Salary>=10000;

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

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

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

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

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

Here is how you can find Year from a Date in SQL Server 2008
SELECT YEAR(GETDATE()) as "Year";

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

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

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