SQl Query

-- Sample Tables --
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);

CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(100),
Salary DECIMAL(10,2),
DeptID INT,
ManagerID INT,
JoinDate DATE,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Insert Sample Data --
INSERT INTO Departments VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');
INSERT INTO Employees VALUES
(101, 'Alice', 60000, 1, NULL, '2022-01-15'),
(102, 'Bob', 75000, 2, 101, '2021-05-10'),
(103, 'Charlie', 50000, 3, 101, '2023-03-20'),
(104, 'David', 90000, 2, 102, '2020-11-25'),
(105, 'Eve', 72000, 1, 101, '2021-08-05');

-- 1. Second Highest Salary --
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

-- 2. Delete Duplicates (Keep One) --
DELETE E1
FROM Employees E1
JOIN Employees E2
ON E1.EmpID > E2.EmpID AND E1.EmpName = E2.EmpName;

-- 3. Department-wise Highest Salary --
SELECT DeptID, MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY DeptID;

-- 4. Pagination (Skip 10, Take 5) --
SELECT * FROM Employees
ORDER BY JoinDate
LIMIT 5 OFFSET 10;

-- 5. Join Orders and Customers --
-- Assume Orders and Customers tables exist --
SELECT C.CustomerName, O.OrderID, O.OrderDate
FROM Orders O
JOIN Customers C
ON O.CustomerID = C.CustomerID;

-- 6. Running Total of Salaries --
SELECT EmpID, EmpName, Salary,
SUM(Salary) OVER (ORDER BY EmpID) AS RunningTotal
FROM Employees;

-- 7. Employees without Managers (Self Join) --
SELECT E.EmpName
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID
WHERE M.EmpID IS NULL;

-- 8. Orders in 2022 and 2023 --
SELECT CustomerID
FROM Orders
WHERE YEAR(OrderDate) IN (2022, 2023)
GROUP BY CustomerID
HAVING COUNT(DISTINCT YEAR(OrderDate)) = 2;

-- 9. Inner vs Left Join --
SELECT E.EmpName, D.DeptName
FROM Employees E
INNER JOIN Departments D
ON E.DeptID = D.DeptID;

SELECT E.EmpName, D.DeptName
FROM Employees E
LEFT JOIN Departments D
ON E.DeptID = D.DeptID;

-- 10. Update Multiple Rows --
UPDATE Employees
SET Salary = CASE WHEN DeptID = 1 THEN Salary + 5000
WHEN DeptID = 2 THEN Salary + 7000
ELSE Salary + 3000 END;

-- 11. ACID Properties Explanation --
-- Atomicity, Consistency, Isolation, Durability
-- Ensures reliable transactions and data integrity.

-- 12. Average, Sum, Min, Max --
SELECT DeptID, AVG(Salary) AS AvgSalary, SUM(Salary) AS TotalSalary,
MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DeptID;

-- 13. Percent Contribution --
SELECT EmpName, Salary,
(Salary * 100.0 / SUM(Salary) OVER ()) AS PercentageContribution
FROM Employees;

-- 14. Median Salary --
SELECT EmpName, Salary
FROM (
SELECT EmpName, Salary,
ROW_NUMBER() OVER (ORDER BY Salary) AS RowAsc,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowDesc
FROM Employees
) AS Temp
WHERE ABS(RowAsc - RowDesc) <= 1;

-- 15. Days Difference --
SELECT EmpName, DATEDIFF(DAY, JoinDate, GETDATE()) AS DaysWorked
FROM Employees;

-- 16. Year-to-Date (YTD) Sales --
-- Example with Sales Table --
SELECT ProductID, SUM(SalesAmount) AS YTD_Sales
FROM Sales
WHERE YEAR(SalesDate) = YEAR(GETDATE())
GROUP BY ProductID;

-- 17. Deadlock Handling --
-- Use Transactions and Proper Locking Mechanisms
BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary + 1000 WHERE DeptID = 1;
COMMIT;

-- 18. Database Design for Library --
-- Tables: Books, Authors, Borrowers, Loans -- Foreign Keys and Indexing

-- 19. Partitioning Example --
-- Partition Tables by Year or Region for Better Performance

-- 20. Orphan Records --
SELECT *
FROM Orders