SQL Query Interview Questions and Answers
Table – EmployeeDetails
EmpId | FullName | ManagerId | DateOfJoining | City |
121 | John Snow | 321 | 01/31/2014 | Toronto |
321 | Walter White | 986 | 01/30/2015 | California |
421 | Kuldeep Rana | 876 | 27/11/2016 | New Delhi |
Table – EmployeeSalary
EmpId | Project | Salary | Variable |
121 | P1 | 8000 | 500 |
321 | P2 | 10000 | 1000 |
421 | P1 | 12000 | 0 |
Write an SQL query to fetch the EmpId and FullName of all the employees working under Manager with id – ‘986’.
EmpId FullName ManagerId DateOfJoining City
2 Walter White 986 2015-01-30 California
--######################################################## SQL QUERY SET 1 START ########################################################################
--Ques.1. Write an SQL query to fetch the EmpId and FullName of all the employees working under Manager with id – ‘986’.
Select * from EmployeeDetails E1
where E1.ManagerId = 986
-- Arithmetic operation
Select ((Salary + 10)*2) as TotalSalary, Salary from EmployeeSalary
Select min(Salary) as Salary from EmployeeSalary;
Select max(Salary) as Salary from EmployeeSalary;
Select COUNT(*) as ProjectCount From EmployeeSalary where Project = 'P1';
-- Write a SQL query to fetch employee names having a salary greater than or equal to 5000 and less than equal 1000
Select * from EmployeeDetails E1
Inner Join EmployeeSalary E2 on E1.EmpId = E2.EmpId
where E2.Salary between 5000 and 10000;
Select * from EmployeeDetails Where EmpId IN (Select EmpId from EmployeeSalary where Salary between 5000 and 10000);
--Write a sql query to fetch project wise count of employees sorted by projects count in desc order
Select Project, COUNT(EmpId) as EmpProjectCount from EmployeeSalary Group by Project
order by EmpProjectCount desc
--Write a query to fetch only the first name(string before space) from the fullname column of employee details table
Select SUBSTRING(FullName,1,CHARINDEX(' ',FullName)-1) As Firstname from EmployeeDetails;
Select SUBSTRING(FullName,CHARINDEX(' ',FullName)+1,LEN(FullName) - CHARINDEX(' ', FullName)) As Lastname from EmployeeDetails;
--Write an SQL query to fetch the different projects available from the EmployeeSalary table.
Select Distinct(Project) from EmployeeSalary
-- Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.
Select E1.FullName, E2.Salary from EmployeeDetails E1
Left join EmployeeSalary E2 on E1.EmpId = E2.EmpId
--Write an SQL query to fetch the count of employees working in project ‘P1’.
Select COUNT(EmpId) AS Employees from EmployeeSalary where Project = 'P1';
--Write an SQL query to find the maximum, minimum, and average salary of the employees.
Select MIN(S.Salary)AS Minimum,MAX(S.Salary) AS Maximum from EmployeeSalary S
Left Join EmployeeDetails E on S.EmpId = E.EmpId
SELECT Max(Salary),
Min(Salary),
AVG(Salary)
FROM EmployeeSalary;
Select S.EmpId,E.FullName from EmployeeSalary S
Left Join EmployeeDetails E on S.EmpId = E.EmpId
Where S.Salary = (Select MIN(Salary) from EmployeeSalary)
Select S.EmpId,E.FullName from EmployeeSalary S
Left Join EmployeeDetails E on S.EmpId = E.EmpId
Where S.Salary = (Select MAX(Salary) from EmployeeSalary)
Select S.EmpId,E.FullName from EmployeeSalary S
Left Join EmployeeDetails E on S.EmpId = E.EmpId
Where S.Salary = (Select AVG(Salary) from EmployeeSalary)
--Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000.
SELECT EmpId, Salary FROM EmployeeSalary
WHERE Salary BETWEEN 9000 AND 15000;
--Write an SQL query to fetch those employees who live in Toronto and work under manager with ManagerId – 321.
SELECT EmpId, City, ManagerId FROM EmployeeDetails
WHERE City='Toronto' AND ManagerId='321';
-- Write an SQL query to fetch all the employees who either live in California or work under a manager with ManagerId – 321.
Select EmpId,City,ManagerId From EmployeeDetails
Where City = 'California' or ManagerId = '321';
--Write an SQL query to fetch all those employees who work on Project other than P1.
SELECT EmpId FROM EmployeeSalary
WHERE NOT Project='P1';
SELECT EmpId
FROM EmployeeSalary
WHERE Project <> 'P1';
--Write an SQL query to display the total salary of each employee adding the Salary with Variable value.
SELECT EmpId,
Salary+Variable as TotalSalary
FROM EmployeeSalary;
--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.
Select * from EmployeeDetails
Where FullName like '__hn%'
--Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’.
SELECT EmpId FROM EmployeeDetails
UNION
SELECT EmpId FROM EmployeeSalary;
--Write an SQL query to fetch common records between two tables.
SELECT EmpId FROM EmployeeDetails
INTERSECT
SELECT EmpId FROM EmployeeSalary;
--Write an SQL query to fetch records that are present in one table but not in another table.
SELECT * FROM EmployeeSalary
MINUS
SELECT * FROM EmployeeSalary;
--Write an SQL query to fetch the EmpIds that are present in both the tables – ‘EmployeeDetails’ and ‘EmployeeSalary.
SELECT EmpId FROM
EmployeeDetails
where EmpId IN
(SELECT EmpId FROM EmployeeSalary);
--Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary.
SELECT * from EmployeeDetails
where EmpId Not In (Select EmpId from EmployeeSalary)
--Write an SQL query to fetch the employee full names and replace the space with ‘-’.
SELECT REPLACE(FullName, ' ', '-')
FROM EmployeeDetails;
--Write an SQL query to fetch the position of a given character(s) in a field.
SELECT CHARINDEX('S', 'Snow') AS MatchPosition FROM EmployeeDetails;
--Write an SQL query to display both the EmpId and ManagerId together.
SELECT CONCAT(EmpId, ManagerId) as NewId
FROM EmployeeDetails;
--Write an SQL query to upper case the name of the employee and lower case the city values.
SELECT UPPER(FullName) as Name, LOWER(City) as City
FROM EmployeeDetails;
--Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field.
SELECT FullName, LEN(FullName) - LEN(REPLACE(FullName, 'n', '')) as Count
FROM EmployeeDetails;
--Write an SQL query to update the employee names by removing leading and trailing spaces.
UPDATE EmployeeDetails
SET FullName = LTRIM(RTRIM(FullName));
--Fetch all the employees who are not working on any project.
SELECT EmpId FROM EmployeeSalary
WHERE Project IS NULL;
--Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000.
Select * from EmployeeDetails
where EmpId IN (SELECT EmpId FROM EmployeeSalary
WHERE Salary BETWEEN 5000 AND 10000);
--Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee
Select * from EmployeeDetails E
left join EmployeeSalary S on E.EmpId = S.EmpId
--Write an SQL query to find the current date-time.
--MySQL-
--SELECT NOW();
--SQL Server-
SELECT getdate();
--Oracle
--SELECT SYSDATE FROM DUAL;
--Write an SQL query to fetch all the Employees details from EmployeeDetails table who joined in the Year 2016.
Select * from EmployeeDetails Where Year(DateOfJoining) = 2016 ;
SELECT * FROM EmployeeDetails WHERE DateOfJoining BETWEEN '2016/01/01' AND '2016/12/31';
--Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.
SELECT * FROM EmployeeDetails E WHERE EXISTS (SELECT * FROM EmployeeSalary S WHERE E.EmpId = S.EmpId);
--Write an SQL query to fetch project-wise count of employees sorted by project’s count in descending order.
SELECT Project, count(
EmpId) EmpProjectCount
FROM EmployeeSalary
GROUP BY Project
ORDER BY EmpProjectCount DESC;
--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.
SELECT E.FullName, S.Salary FROM EmployeeDetails E
LEFT JOIN EmployeeSalary S ON E.EmpId = S.EmpId;
--Write an SQL query to join 3 tables.
--Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table.
Select Distinct(E.FullName) from EmployeeDetails E
Inner join EmployeeSalary S on E.EmpId = S.EmpId
--Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId).
SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*) as COUNTS FROM EmployeeDetails
GROUP BY FullName, ManagerId, DateOfJoining, City
HAVING COUNT(*) > 1;
--Write an SQL query to remove duplicates from a table without using a temporary table.
--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;
--Write an SQL query to fetch only odd rows from the table.
SELECT * FROM EmployeeDetails WHERE EmpId % 2 <> 0;
--Write an SQL query to fetch only even rows from the table.
SELECT * FROM EmployeeDetails WHERE EmpId % 2 = 0;
--Write an SQL query to create a new table with data and structure copied from another table.
SELECT * into Temp FROM EmployeeSalary;
Select * from Temp;
--Write an SQL query to create an empty table with the same structure as some other table.
--CREATE TABLE NewTable Value (SELECT * FROM EmployeeSalary where 1=0);
Select * Into temp1 From EmployeeSalary Where 1 = 2
Select * from temp1;
--Write an SQL query to fetch top n records?
SELECT TOP 1 *
FROM EmployeeSalary
ORDER BY Salary DESC;
-- Write an SQL query to find the nth highest salary from table.
SELECT TOP 1 *
FROM EmployeeSalary Where Salary = (
SELECT DISTINCT TOP 1 Salary
FROM EmployeeSalary
ORDER BY Salary DESC
)
ORDER BY Salary ASC;
--Write SQL query to find the 3rd highest salary from a table without using the TOP/limit keyword.
SELECT Salary
FROM EmployeeSalary Emp1
WHERE 0 = (
SELECT COUNT( DISTINCT ( Emp2.Salary ) )
FROM EmployeeSalary Emp2
WHERE Emp2.Salary > Emp1.Salary
)
--For nth highest salary
SELECT Salary
FROM EmployeeSalary Emp1
WHERE 1-1 = (
SELECT COUNT( DISTINCT ( Emp2.Salary ) )
FROM EmployeeSalary Emp2
WHERE Emp2.Salary > Emp1.Salary
)
--################### SQL QUERY SET 1 END #######################################################################
No comments:
Post a Comment