SQL PRACTICE QUESTIONS
SELECT QUERY
Given a table Employee(EID, EName, DeptNo, Salary, City) and Department(DeptNo, DName) write
the queries for the following situations:-
1. Display all the details of the Employees.
2. Display the Department Names from the Department Table
3. Display the Details of the Employees who are working in Kolkata or Chennai
4. Display the Name and Salary of the Employees whose name has 3rd last Letter as o. (Eg. Ashutosh,
Santosh, Anoop, Swaroop etc.)
5. Display the EID, EName and DName from Employee and Department tables.
6. Display the EName and DName from Employee and Department for Employees who earn more than
50000 and are posted in 'Mumbai'
7. Display the Total Number of Employees in the Employee Table who are working in Delhi
8. Display the Average Salary of Each Department along with the DeptNo.
9. Arrange the Employees working in DeptNo 101 in Descending Order of their Salaries.
10. Display the Maximum Salary of Each Department for Departments which have more than 5
employees working in them.
11. Count the number of Employees working in each department whose names start with "A"
12. Find the Total Salary of Employees in Each Department which has less than 10 employees from
either "Mumbai" or "Chennai"
13. Find the No of Employees working in each city whose name contains "SH" and minimum salary in the
City is more than 25000. Arrange them in ascending order of the City.
14. Find the City wise Total Salary of employees for Employees working in "Physics" or "Chemistry"
Department, and earns more than 5000 and no of employees in the City is not less than 6 and arrange
them in Descending order of the No of Employees in each city.
ANSWERS
1. SELECT * FROM Employee;
2. SELECT DName FROM Department;
3. SELECT * FROM Employee
WHERE City IN ("Kolkata", "Chennai");
4. SELECT EName, Salary FROM Employee
WHERE EName LIKE "%o__";
5. SELECT EID, EName, DName
FROM Employee E,Department D
WHERE E.DeptNo = D.DeptNo;
6. SELECT EName, DName
FROM Employee E JOIN Department D
ON E.DeptNo = D.DeptNo
WHERE Salary> 50000 and City = 'Mumbai';
7. SELECT COUNT(*) FROM Employee
WHERE City = "Delhi";
8. SELECT DeptNo, AVG(Salary) FROM Employee
GROUP BY DeptNo;
9. SELECT * FROM Employee
WHERE DeptNo = 101
ORDER BY Salary DESC;
10. SELECT MAX(Salary) FROM Employee
GROUP BY DeptNo
HAVING COUNT(*) > 5;
11. SELECT DeptNo, COUNT(*) FROM Employee
WHERE EName LIKE "A%"
GROUP BY DeptNo;
12. SELECT SUM(Salary) FROM Employee
WHERE City IN ("Mumbai", "Chennai")
GROUP BY DeptNo
HAVING COUNT(*) < 10;
13. SELECT City, COUNT(*) FROM Employee
WHERE EName LIKE "%SH%"
GROUP BY City
HAVING MIN(Salary)> 25000
ORDER BY City;
14. SELECT City, SUM(Salary)
FROM Employee E JOIN Department D
ON E.DeptNo = D.DeptNo
WHERE DName IN ("Physics", "Chemistry") AND Salary > 5000
GROUP BY City
HAVING COUNT(*) >= 6
ORDER BY COUNT(*) DESC;
