SQL Query Interview Questions and Answers

Hello friends! in this post, we will see some of the most common SQL queries asked in interviews. These SQL query interview questions and answers are designed for both freshers and experienced. We will start with database queries covering basic SQL commands and then move to more complex problems.

If you want to skip the basic questions and start with some tricky SQL queries then you can directly move to our SQL queries interview questions for the experienced section. 

Consider the below two tables for reference while trying to create queries for the questions asked here.

Table – EmployeeDetails

EmpIdFullNameManagerIdDateOfJoiningCity
121John Snow32101/31/2014Toronto
321Walter White98601/30/2015California
421Kuldeep Rana87627/11/2016New Delhi


Table – EmployeeSalary

EmpIdProjectSalaryVariable
121P18000500
321P2100001000
421P1120000

SQL Query Interview Questions

Ques.1. Write an SQL query to fetch the EmpId and FullName of all the employees working under Manager with id – ‘986’.
Ans. We can use the EmployeeDetails table to fetch the employee details with a where clause for the manager-

SELECT  EmpId, FullName
FROM EmployeeDetails
WHERE ManagerId = 986;



Ques.2. Write an SQL query to fetch the different projects available from the EmployeeSalary table.
Ans. While referring to the EmployeeSalary table, we can see that this table contains project values corresponding to each employee, or we can say that we will have duplicate project values while selecting Project values from this table. So, we will use the distinct clause to get the unique values of the Project.

SELECT DISTINCT(Project)
FROM EmployeeSalary;



Ques.3. Write an SQL query to fetch the count of employees working in project ‘P1’.
Ans. Here, we would be using aggregate function count() with the SQL where clause-

SELECT COUNT(*) 
FROM EmployeeSalary 
WHERE Project = 'P1';



Ques.4. Write an SQL query to find the maximum, minimum, and average of the employees.
Ans. We can use the aggregate function here to fetch the max, min and average values-

SELECT Max(Salary), 
Min(Salary), 
AVG(Salary) 
FROM EmployeeSalary;



Ques.5. Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000.
Ans. Here, we can use the ‘Between’ operator with a where clause.

SELECT EmpId, Salary
FROM EmployeeSalary
WHERE Salary BETWEEN 9000 AND 15000;



Ques.6. Write an SQL query to fetch those employees who live in Toronto and work under manager with ManagerId – 321.
Ans. Since we have to satisfy both the conditions – employees living in ‘Toronto’ and working in Project ‘P2’. So, we will use AND operator here-

SELECT EmpId, City, ManagerId
FROM EmployeeDetails
WHERE City='Toronto' AND ManagerId='321';



Ques.7. Fetch all the employees who either live in California or work under a manager with ManagerId – 321.
Ans. Since we have to satisfy either of the conditions – employees living in ‘California’ and working under Manager with ManagerId ‘321’. So, we will use the OR operator here-

SELECT EmpId, City, ManagerId
FROM EmployeeDetails
WHERE City='California' OR ManagerId='321';



Ques.8. Write an SQL query to fetch all those employees who work on Project other than P1.
Ans. Here, we can use the NOT operator to fetch the rows which are not satisfying the given condition.

SELECT EmpId
FROM EmployeeSalary
WHERE NOT Project='P1';


Or using the not equal to operator-

SELECT EmpId
FROM EmployeeSalary
WHERE Project <> 'P1';

For difference between NOT and <> operator check this link – Difference between the NOT and != operators.



Ques.9. Display the total salary of each employee adding the Salary with Variable value.
Ans. Here, we can simply use the ‘+’ operator.

SELECT EmpId,
Salary+Variable as TotalSalary 
FROM EmployeeSalary;



Ques.10. Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text “hn” and ending with any sequence of characters.
Ans. For this, we will use like operator with ‘_’ and ‘%’ wild card characters where ‘_’ matches a single character and ‘%’ matches ‘0 or multiple characters’.

SELECT FullName
FROM EmployeeDetails
WHERE FullName LIKE ‘__hn%’;



Ques.11. Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’.
Ans. In order to get unique employee ids from both the tables, we can use Union clause which can combine the results of the two SQL queries and return unique rows.

SELECT EmpId FROM EmployeeDetails
UNION 
SELECT EmpId FROM EmployeeSalary;



Ques.12. Write an SQL query to fetch common records between two tables.
Ans. SQL Server – Using INTERSECT operator-

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary;


MySQL – Since MySQL doesn’t have INTERSECT operator so we can use the sub query-

SELECT *
FROM EmployeeSalary
WHERE EmpId IN 
(SELECT EmpId from ManagerSalary);



Ques.13. Write an SQL query to fetch records that are present in one table but not in another table.
Ans. SQL Server – Using MINUS- operator-

SELECT * FROM EmployeeSalary
MINUS
SELECT * FROM ManagerSalary;


MySQL – Since MySQL doesn’t have MINUS operator so we can use LEFT join-

SELECT EmployeeSalary.*
FROM EmployeeSalary
LEFT JOIN
ManagerSalary USING (EmpId)
WHERE ManagerSalary.EmpId IS NULL;



Ques.14. Write an SQL query to fetch the EmpIds that are present in both the tables –  ‘EmployeeDetails’ and ‘EmployeeSalary.
Ans. Using sub query-

SELECT EmpId FROM 
EmployeeDetails 
where EmpId IN 
(SELECT EmpId FROM EmployeeSalary);



Ques.15. Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary.
Ans. Using sub query-

SELECT EmpId FROM 
EmployeeDetails 
where EmpId Not IN 
(SELECT EmpId FROM EmployeeSalary);



Ques.16. Write an SQL query to fetch the employee full names and replace the space with ‘-’.
Ans. Using ‘Replace’ function-

SELECT REPLACE(FullName, ' ', '-') 
FROM EmployeeDetails;



Ques.17. Write an SQL query to fetch the position of a given character(s) in a field.
Ans. Using ‘Instr’ function-

SELECT INSTR(FullName, 'Snow')
FROM EmployeeDetails;



Ques.18. Write an SQL query to display both the EmpId and ManagerId together.
Ans. Here we can use the CONCAT command.

SELECT CONCAT(EmpId, ManagerId) as NewId
FROM EmployeeDetails;



Ques.19. Write a query to fetch only the first name(string before space) from the FullName column of the EmployeeDetails table.
Ans. In this question, we are required to first fetch the location of the space character in the FullName field and then extract the first name out of the FullName field. For finding the location we will use LOCATE method in MySQL and CHARINDEX in SQL SERVER and for fetching the string before space, we will use SUBSTRING OR MID method.

MySQL – using MID

SELECT MID(FullName, 1, LOCATE(' ',FullName)) 
FROM EmployeeDetails;


SQL Server – using SUBSTRING

SELECT SUBSTRING(FullName, 1, CHARINDEX(' ',FullName)) 
FROM EmployeeDetails;



Ques.20. Write an SQL query to upper case the name of the employee and lower case the city values.
Ans. We can use Upper and lower functions to achieve the intended results.

SELECT UPPER(FullName), LOWER(City) 
FROM EmployeeDetails;



Ques.21. Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field.
Ans. Here, we can use the ‘Length’ function. We can subtract the total length of the FullName field with a length of the FullName after replacing the character – ‘n’.

SELECT FullName, 
LENGTH(FullName) - LENGTH(REPLACE(FullName, 'n', ''))
FROM EmployeeDetails;



Ques.22. Write an SQL query to update the employee names by removing leading and trailing spaces.
Ans. Using ‘Update’ command with ‘LTRIM’ and ‘RTRIM’ function.

UPDATE EmployeeDetails 
SET FullName = LTRIM(RTRIM(FullName));



Ques.23. Fetch all the employees who are not working on any project.
Ans. Using Is NULL-

SELECT EmpId 
FROM EmployeeSalary 
WHERE Project IS NULL;



Ques.24. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000.
Ans. Here, we will use BETWEEN in the ‘where’ clause to return the EmpId of the employees with salary satisfying the required criteria and then use it as subquery to find the fullName of the employee from EmployeeDetails table.

SELECT FullName 
FROM EmployeeDetails 
WHERE EmpId IN 
(SELECT EmpId FROM EmployeeSalary 
WHERE Salary BETWEEN 5000 AND 10000);



Ques.25. Write an SQL query to find the current date-time.
Ans. MySQL-

SELECT NOW();


SQL Server-

SELECT getdate();


Oracle-

SELECT SYSDATE FROM DUAL;



Ques.26. Write an SQL query to fetch all the Employees details from EmployeeDetails table who joined in the Year 2020.
Ans. Using BETWEEN for the date range ’01-01-2020′ AND ’31-12-2020′-

SELECT * FROM EmployeeDetails
WHERE DateOfJoining BETWEEN '2020/01/01'
AND date '2020/12/31';


Also, we can extract year part from the joining date (using YEAR in mySQL)-

SELECT * FROM EmployeeDetails 
WHERE YEAR(DateOfJoining) = '2020';



Ques.27. Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.
Ans. Using ‘Exists’-

SELECT * FROM EmployeeDetails E
WHERE EXISTS
(SELECT * FROM EmployeeSalary S 
WHERE  E.EmpId = S.EmpId);



Ques.28. Write an SQL query to fetch project-wise count of employees sorted by project’s count in descending order.
Ans. The query has two requirements – first to fetch the project-wise count and then to sort the result by that count. For project-wise count, we will be using the GROUP BY clause and for sorting, we will use the ORDER BY clause on the alias of the project-count.

SELECT Project, count(EmpId) EmpProjectCount
FROM EmployeeSalary
GROUP BY Project
ORDER BY EmpProjectCount DESC;



Ques.29. Write a query to fetch employee names and salary records. Display the employee details even if the salary record is not present for the employee.
Ans. Here, we can use left join with EmployeeDetail table on the left side.

SELECT E.FullName, S.Salary 
FROM EmployeeDetails E 
LEFT JOIN 
EmployeeSalary S
ON E.EmpId = S.EmpId;



Ques.30. Write an SQL query to join 3 tables.
Ans. Considering 3 tables TableA, TableB and TableC, we can use 2 joins clauses like below-

SELECT column1, column2
FROM TableA
JOIN TableB ON TableA.Column3 = TableB.Column3
JOIN TableC ON TableA.Column4 = TableC.Column4;

SQL Query Interview Questions for Experienced


Ques. 31. Write an SQL query to fetch all the Employees who are also managers from EmployeeDetails table.

Ans. Here, we have to use Self-Join as the requirement wants us to analyze the EmployeeDetails table as two different tables, each for Employee and manager records.

SELECT DISTINCT E.FullName
FROM EmpDetails E
INNER JOIN EmpDetails M
ON E.EmpID = M.ManagerID;



Ques.32. Write an SQL query to fetch duplicate records from a EmployeeDetails (leaving the primary key – EmpId).
Ans. In order to find duplicate records from the table, we can use GROUP BY on all the fields and then use HAVING clause to return only those fields whose count is greater than 1 i.e. the rows having duplicate records.

SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*)
FROM EmployeeDetails
GROUP BY FullName, ManagerId, DateOfJoining, City
HAVING COUNT(*) > 1;



Ques.33. Write an SQL query to remove duplicates from a table without using a temporary table.
Ans. Here, we can use delete with alias and inner join. We will check for the equality of all the matching records and them remove the row with higher EmpId.

DELETE E1 FROM EmployeeDetails E1
INNER JOIN EmployeeDetails E2 
WHERE E1.EmpId > E2.EmpId 
AND E1.FullName = E2.FullName 
AND E1.ManagerId = E2.ManagerId
AND E1.DateOfJoining = E2.DateOfJoining
AND E1.City = E2.City;



Ques.34. Write an SQL query to fetch only odd rows from the table.
Ans. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-

SELECT * FROM EmployeeDetails 
WHERE MOD (EmpId, 2) <> 0;


In case we don’t have such a field then we can use the below queries.

Using Row_number in SQL server and checking that the remainder when divided by 2 is 1-

SELECT E.EmpId, E.Project, E.Salary
FROM (
    SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber
    FROM EmployeeSalary
) E
WHERE E.RowNumber % 2 = 1;


Using a user defined variable in MySQL-

SELECT *
FROM (
      SELECT *, @rowNumber := @rowNumber+ 1 rn
      FROM EmployeeSalary
      JOIN (SELECT @rowNumber:= 0) r
     ) t 
WHERE rn % 2 = 1;



Ques.35. Write an SQL query to fetch only even rows from the table.
Ans. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-

SELECT * FROM EmployeeDetails 
WHERE MOD (EmpId, 2) = 0;


In case we don’t have such a field then we can use the below queries.

Using Row_number in SQL server and checking that the remainder when divided by 2 is 1-

SELECT E.EmpId, E.Project, E.Salary
FROM (
    SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber
    FROM EmployeeSalary
) E
WHERE E.RowNumber % 2 = 0;


Using a user defined variable in MySQL-

SELECT *
FROM (
      SELECT *, @rowNumber := @rowNumber+ 1 rn
      FROM EmployeeSalary
      JOIN (SELECT @rowNumber:= 0) r
     ) t 
WHERE rn % 2 = 0;



Ques.36. Write an SQL query to create a new table with data and structure copied from another table.
Ans.

CREATE TABLE NewTable 
SELECT * FROM EmployeeSalary;



Ques.37. Write an SQL query to create an empty table with the same structure as some other table.
Ans. Here we can use the same query as above with False ‘WHERE’ condition-

CREATE TABLE NewTable 
SELECT * FROM EmployeeSalary where 1=0;



Ques.38. Write an SQL query to fetch top n records?
Ans. In MySQL using LIMIT-

SELECT *
FROM EmployeeSalary
ORDER BY Salary DESC LIMIT N;


In SQL server using TOP command-

SELECT TOP N *
FROM EmployeeSalary
ORDER BY Salary DESC;



Ques.39. Write an SQL query to find the nth highest salary from table.
Ans, Using Top keyword (SQL Server)-

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      )
ORDER BY Salary ASC;


Using limit clause(MySQL)-

SELECT Salary
FROM Employee
ORDER BY Salary DESC LIMIT N-1,1;



Ques.40. Write SQL query to find the 3rd highest salary from table without using TOP/limit keyword.
Ans. The below SQL query makes use of correlated subquery wherein in order to find the 3rd highest salary the inner query will return the count of till we find that there are two rows that salary greater than other distinct salaries.

SELECT Salary
FROM EmployeeSalary Emp1
WHERE 2 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )


For nth highest salary-

SELECT Salary
FROM EmployeeSalary Emp1
WHERE N-1 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

This concludes our post on frequently asked SQL query interview questions and answers. I hope these questions help you with your database interviews.

Do check our post on – Database Interview Questions, focussing on the theoretical concepts of DBMS.

25 thoughts on “SQL Query Interview Questions and Answers”

  1. I think the answer for Question Number 6 is wrong.I hope this is the Correct Answer , Also I think there is no need to use the distinct because emp_id will be primary key normally

    SELECT DISTINCT E.FullName
    FROM EmpDetails E
    INNER JOIN EmpDetails M
    ON E.EmpID = M.ManagerID AND E.EmpID=M.EmpID

    Reply
    • @Firos You are right, but your solution also not giving us any solution , there is null value , I tried to correct it, please check it.

      SELECT DISTINCT E.FullName
      FROM EmpDetails E
      INNER JOIN EmpDetails M
      ON E.ManagerID = M.ManagerID

      Need to check employee who is a manager ,
      managerId and employee can’t be same, so how can we compare with each other. I think this works .

      Reply
      • Both of you are wrong, this is the very simple way and also correct:

        select e.fullname
        from EmployeeDetails e
        join EmployeeDetails s on e.EmpID =s.ManagerID

        where their ID is someones EmpID

        Reply
        • Much simpler way to do this will be:
          SELECT FullName FROM EmployeeDetails
          WHERE EmpID IN (SELECT ManagerID FROM EmployeeDetails)

          Reply
  2. For 2nd Level Manager Name:
    SELECT DISTINCT A.EmpID,ISNULL(C.Name,”) Manager_Name
    FROM Emp_Mgr A
    LEFT OUTER JOIN Emp_Mgr B ON A.MangrID=B.EmpID
    LEFT OUTER JOIN Emp_Mgr C ON B.MangrID=C.EmpID

    Reply
  3. DELETE FROM EmployeeSalary
    WHERE EmpId IN (
    SELECT EmpId
    FROM EmployeeSalary
    GROUP BY Project, Salary
    HAVING COUNT(*) > 1));

    this query is wrong

    Reply
    • Thanks Dorris, the intention was to just show the usage of ‘Minus’ command. Also, since EmployeeDetails and EmployeeSalary have different structures, so we can’t use minus with ‘*’ with those tables. Hence, I had to introduce a third table – ManagerSalary.

      Reply
  4. can I write the query for 6th question like

    Select FullName from EmployeeDeatils
    where ManagerId is not null;

    Because if anyone has ManagerId then he must be a manager right.so need to join??

    Reply
    • Hi Ramesh, the ManagerId field in the EmployeeDetails table refers to the Manager of that Employee. So, your query will return only the Employees with Manager assigned.

      Reply
  5. We don’t need to mention full tablename as EmployeeSalary for 19 answer?

    SELECT TOP 1 Salary
    FROM (
    SELECT DISTINCT TOP N Salary
    FROM EmployeeSalary
    ORDER BY Salary DESC
    )
    ORDER BY Salary ASC
    ——–
    SELECT Salary
    FROM EmployeeSalary
    ORDER BY Salary DESC LIMIT N-1,1;

    Reply
  6. Minor correction to find Duplicate Record:
    select FirstName, LastName, Dept, Contact, count(*)
    from employee
    group by FirstName, LastName, Dept, Contact
    having count(*)>1;

    Note: Here, we can not have EmpId, because EmpId is a Primary Key and this field never have a duplicate value. So, skip EmpI

    Reply

Leave a Comment