Oracle SQL Practice Queries
Query 1: Second Highest Salary
SELECT Name, Salary
FROM Employees
WHERE Salary = (
SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees)
);
Query 2: Employees with the Same Second-Highest Salary
WITH SecondHighest AS (
SELECT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
)
SELECT Name, Salary
FROM Employees
WHERE Salary = (SELECT Salary FROM SecondHighest);
Query 3: Departments with More Than 2 Employees
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 2;
Query 4: Departments with Above-Average Salary
SELECT d.DepartmentName, AVG(e.Salary) AS AvgSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName
HAVING AVG(e.Salary) > (SELECT AVG(Salary) FROM Employees);
Query 5: Employees Above the 75th Percentile in Their Department
SELECT Name, Salary, DepartmentID
FROM (
SELECT Name, Salary, DepartmentID, PERCENT_RANK() OVER (PARTITION BY
DepartmentID ORDER BY Salary) AS Percentile
FROM Employees
)
WHERE Percentile >= 0.75;
Query 6: Total Salaries by Department
SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;
Query 7: Find Duplicate Employee Names
SELECT Name, COUNT(*) AS NameCount
FROM Employees
GROUP BY Name
HAVING COUNT(*) > 1;
Query 8: Employees Who Do Not Belong to a Department
SELECT Name
FROM Employees
WHERE DepartmentID IS NULL;
Query 9: Employees Hired in the Last Year
SELECT Name, HireDate
FROM Employees
WHERE HireDate >= ADD_MONTHS(SYSDATE, -12);
Query 10: Highest-Paid Employee in Each Department
SELECT Name, DepartmentID, Salary
FROM (
SELECT Name, DepartmentID, Salary, RANK() OVER (PARTITION BY DepartmentID
ORDER BY Salary DESC) AS Rank
FROM Employees
)
WHERE Rank = 1;
Query 11: Average Salary by Department
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;
Query 12: Employees Earning More Than Their Department's Average
SELECT e.Name, e.Salary, e.DepartmentID
FROM Employees e
JOIN (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
) d ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > d.AvgSalary;
Query 13: Top 3 Highest Salaries in Each Department
SELECT Name, DepartmentID, Salary
FROM (
SELECT Name, DepartmentID, Salary, RANK() OVER (PARTITION BY DepartmentID
ORDER BY Salary DESC) AS Rank
FROM Employees
)
WHERE Rank <= 3;
Query 14: Count of Employees Hired Each Month
SELECT TO_CHAR(HireDate, 'YYYY-MM') AS Month, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY TO_CHAR(HireDate, 'YYYY-MM');
Query 15: Departments with No Employees
SELECT DepartmentID, DepartmentName
FROM Departments
WHERE DepartmentID NOT IN (SELECT DISTINCT DepartmentID FROM Employees);
Query 16: Employees Who Joined Before Their Manager
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE e.HireDate < m.HireDate;
Query 17: Department with Maximum Employees
SELECT DepartmentID
FROM Employees
GROUP BY DepartmentID
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROWS ONLY;
Query 18: Employees with Salaries in the Top 10%
SELECT Name, Salary
FROM (
SELECT Name, Salary, NTILE(10) OVER (ORDER BY Salary DESC) AS Decile
FROM Employees
)
WHERE Decile = 1;
Query 19: Employee Details for the Most Recent Hires
SELECT *
FROM Employees
WHERE HireDate = (SELECT MAX(HireDate) FROM Employees);
Query 20: Total Compensation Cost by Department
SELECT DepartmentID, SUM(Salary + COALESCE(Bonus, 0)) AS TotalCompensation
FROM Employees
GROUP BY DepartmentID;
Query 21: Departments with Salaries Above a Threshold
SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary) > 1000000;
Query 22: Longest-Serving Employees
SELECT Name, SYSDATE - HireDate AS DaysWithCompany
FROM Employees
ORDER BY DaysWithCompany DESC;
Query 23: Employees Without Managers
SELECT Name
FROM Employees
WHERE ManagerID IS NULL;
Query 24: Average Salary for Each Job Role
SELECT JobTitle, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY JobTitle;
Query 25: Employees in Departments with Below-Average Salaries
SELECT e.Name, e.DepartmentID, e.Salary
FROM Employees e
JOIN (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
) d ON e.DepartmentID = d.DepartmentID
WHERE d.AvgSalary < (SELECT AVG(Salary) FROM Employees);
Query 26: Percentage of Employees in Each Department
SELECT DepartmentID, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Employees) AS
Percentage
FROM Employees
GROUP BY DepartmentID;
Query 27: Departments with the Highest Average Tenure
SELECT DepartmentID, AVG(SYSDATE - HireDate) AS AvgTenure
FROM Employees
GROUP BY DepartmentID
ORDER BY AvgTenure DESC;
Query 28: Employees with Duplicate Emails
SELECT Email, COUNT(*) AS EmailCount
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;
Query 29: Total Number of Managers
SELECT COUNT(DISTINCT ManagerID) AS TotalManagers
FROM Employees;
Query 30: Employees with No Assigned Job Titles
SELECT Name
FROM Employees
WHERE JobTitle IS NULL;
