SQL QUERIES

📊 SQL Interview Queries (Real-World Scenarios)

Here are some commonly asked SQL queries, structured clearly with multiple approaches where applicable.

---

🧮 Find the Employee with the Highest Salary in Each Department

✅ Using Aggregate Function

Select Department, Max(Salary) As MaxSalary 
from EmployeesTest 
Group by Department;

---

✅ Using CTE (Common Table Expression)

WITH MaxSalaryCte As 
(
 Select *, ROW_NUMBER() OVER(Partition By Department Order By Salary desc) as Rank 
 from EmployeesTest
)
Select * 
from MaxSalaryCte 
Where Rank = 1;

---

💰 Find the Total Sales for Each Product

Select ProductName, Sum(Amount) As SumOfProduct 
from SalesTransactions 
group by ProductName;

---

🥈 Get the Second Highest Salary

✅ Using Subquery

SELECT Max(Salary) As 'Second Max Salary'
FROM Employees2
WHERE Salary < (SELECT MAX(Salary) FROM Employees2);

---

✅ Using OFFSET-FETCH

Select Salary 
from Employees2 
Order by Salary Desc 
offset 1 rows fetch next 1 rows only;

---

📈 Employees Earning More Than Average Salary

SELECT *
FROM Employees3
WHERE Salary > (SELECT AVG(Salary) FROM Employees3);

---

⏳ Employees Who Worked More Than 5 Years

✅ Using DATEADD

Select Name, HireDate 
from Employees4 
where HireDate < DATEADD(YEAR, -5, GetDate());

---

✅ Using DATEDIFF

Select Name, HireDate 
from Employees4 
where DATEDIFF(YEAR, HireDate, GetDate()) > 5;

---

🏆 Department with Highest Average Salary

Select TOP 1 Department, avg(Salary) 
from Employees5 
group by Department 
order by avg(Salary) desc;

---

🎁 Employees Who Received Bonus

select Name 
from EmployeesTest et 
inner join EmployeeBonuses eb 
on et.EmployeeId = eb.EmployeeId;

---

📊 Products with Sales Greater Than Average

Select ProductName 
from ProductSales 
where Amount > (Select AVG(Amount) from ProductSales);

---

🚫 Employees Not in IT Department

select * 
from EmployeesTest 
Where Department NOT IN ('IT');

---

💎 Top 3 Most Expensive Products

Select TOP 3 ProductName 
from Products 
Order By Price Desc;

---

🧠 Key Takeaways

• Use GROUP BY + Aggregate functions for summaries.

• Use CTE + Window functions for advanced ranking problems.

• Subqueries are powerful for comparisons like AVG, MAX.

• Date functions like DATEADD and DATEDIFF are essential for time-based queries.

• TOP, OFFSET-FETCH help in pagination and ranking scenarios.

---

These queries are highly useful for interviews, real-world scenarios, and database optimization understanding.