— 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
Source link
lol
