DEV Community

Cover image for Top 10 SQL Queries asked in Interviews
rahul
rahul

Posted on • Updated on

Top 10 SQL Queries asked in Interviews

SQL stands for structured query language. Query is a request for data from database. SQL Queries are asked directly in interview as it tests both your practical as well as theoretical knowledge. In this article we are going to discuss the Top 10 SQL Query questions asked during interviews.
Reference table for SQL questions

Image description

QUE-1 Write an SQL query to fetch the different projects available from the EmployeeSalary table.

Ans In reference to the EmployeeSalary table, we can see that the 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;

QUE-2 Write an SQL query to display the total salary of each employee adding the Salary with Variable value.

Ans We can use the ‘+’ operator in SQL.
SELECT EmpId,
Salary+Variable as TotalSalary
FROM EmployeeSalary;

QUE-3 Write an SQL query to create an empty table with the same structure as some other table.

Ans The query that used to create an empty table with same structure as some other table is as

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

QUE-4 Write an SQL query to fetch the EmpIds that are present in both the tables – 'EmployeeDetails' and 'EmployeeSalary'.

Ans With the help of sub query
SELECT EmpId FROM
EmployeeDetails
where EmpId IN
(SELECT EmpId FROM EmployeeSalary);

QUE-5 Write an SQL query to fetch the position of a given character(s) in a field.

Ans ‘Instr’ function can be used
SELECT INSTR(FullName, 'Snow')
FROM EmployeeDetails;

QUE-6 Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.

Ans With the help of Exists command
SELECT * FROM EmployeeDetails E
WHERE EXISTS
(SELECT * FROM EmployeeSalary S
WHERE E.EmpId = S.EmpId);

QUE-7 Write an SQL query to remove duplicates from a table without using a temporary table.

Ans We can use delete with alias and inner join. Then 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;

QUE-8 Write an SQL query to fetch duplicate records from EmployeeDetails (without considering 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 the 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;

QUE-9 Write an SQL query to display both the EmpId and ManagerId together.

Ans With the help of CoNCAT command
SELECT CONCAT(EmpId, ManagerId) as NewId
FROM EmployeeDetails;

QUE-10 Write an SQL query to fetch common records between two tables.

Ans SQL Server – With the help of INTERSECT operator-
SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary;

MySQL – As MySQL doesn’t have INTERSECT operator so we can use the sub query-
SELECT *
FROM EmployeeSalary
WHERE EmpId IN
(SELECT EmpId from ManagerSalary);

This the end of article check references for more questions asked in interviews. Thanks for reading.:)
References :
SQL Queries asked in Interview

Learn SQL Queries

Discussion (2)

Collapse
moopet profile image
Ben Sinclair

Heads-up: I don't know what happened, but your external link to the table seems to be a picture of a link rather than the link itself - and it's missing the final letter so it's broken.

Collapse
rahul_14 profile image
rahul Author

Thanks for the info ill update it