DEV Community

Anjali Gurjar
Anjali Gurjar

Posted on

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more