SQL Query Helper

SELECT col1, col2
FROM   Employees
WHERE  Department = 'Engineering'
  AND  Salary > 80000
ORDER BY col1 DESC;

SELECT DISTINCT Department
FROM   Employees;

-- SQL Server
SELECT TOP 10 * FROM Orders ORDER BY OrderDate DESC;
--
-- MySQL / PostgreSQL
SELECT * FROM Orders ORDER BY OrderDate DESC LIMIT 10;

SELECT   Department, COUNT(*) AS HeadCount, AVG(Salary) AS AvgSalary
FROM     Employees
GROUP BY Department
HAVING   COUNT(*) > 5
ORDER BY AvgSalary DESC;

SELECT COUNT(*),          -- row count
       COUNT(DISTINCT col), -- unique values
       SUM(Amount),         -- total
       AVG(Amount),         -- average
       MIN(Amount),         -- smallest
       MAX(Amount)          -- largest
FROM   Orders;

SELECT o.OrderId, c.CustomerName
FROM   Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id;

SELECT e.Name, d.DepartmentName
FROM   Employees e
LEFT JOIN Departments d ON e.DeptId = d.Id;

SELECT a.Name, b.Name
FROM   TableA a
FULL OUTER JOIN TableB b ON a.Id = b.AId;

SELECT s.Size, c.Color
FROM   Sizes s
CROSS JOIN Colors c;

SELECT e.Name AS Employee, m.Name AS Manager
FROM   Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id;

SELECT Name, Salary
FROM   Employees
WHERE  Salary > (SELECT AVG(Salary) FROM Employees);

SELECT c.CustomerName
FROM   Customers c
WHERE  EXISTS (
  SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
);

WITH TopSellers AS (
  SELECT SellerId, SUM(Amount) AS Total
  FROM   Sales
  GROUP BY SellerId
  HAVING SUM(Amount) > 100000
)
SELECT e.Name, ts.Total
FROM   TopSellers ts
INNER JOIN Employees e ON ts.SellerId = e.Id;

SELECT Name, Department, Salary,
       ROW_NUMBER() OVER (
         PARTITION BY Department ORDER BY Salary DESC
       ) AS RowNum
FROM   Employees;

SELECT Name, Score,
       RANK()       OVER (ORDER BY Score DESC) AS Rnk,      -- gaps after ties
       DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRnk  -- no gaps
FROM   Students;

SELECT OrderDate, Amount,
       LAG(Amount, 1)  OVER (ORDER BY OrderDate) AS PrevAmount,
       LEAD(Amount, 1) OVER (ORDER BY OrderDate) AS NextAmount
FROM   Orders;

-- Single row
INSERT INTO Employees (Name, Department, Salary)
VALUES ('Alice', 'Engineering', 95000);
--
-- Multiple rows
INSERT INTO Employees (Name, Department, Salary)
VALUES ('Bob', 'Marketing', 72000),
       ('Carol', 'Sales', 68000);

UPDATE e
SET    e.DeptName = d.Name
FROM   Employees e
INNER JOIN Departments d ON e.DeptId = d.Id
WHERE  d.IsActive = 1;

DELETE FROM Logs
WHERE  CreatedAt < DATEADD(MONTH, -6, GETDATE())
  AND  Id NOT IN (SELECT LogId FROM AuditTrail);

CREATE NONCLUSTERED INDEX IX_Employees_Dept
ON Employees (Department)
INCLUDE (Name, Salary);

SELECT i.name AS IndexName, i.type_desc,
       COL_NAME(ic.object_id, ic.column_id) AS ColumnName
FROM   sys.indexes i
INNER JOIN sys.index_columns ic
  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE  i.object_id = OBJECT_ID('Employees');

CREATE PROCEDURE usp_GetEmployeesByDept
  @DeptName NVARCHAR(100),
  @MinSalary DECIMAL(10,2) = 0
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    SELECT Name, Salary
    FROM   Employees
    WHERE  Department = @DeptName
      AND  Salary >= @MinSalary
    ORDER BY Salary DESC;
  END TRY
  BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMsg;
  END CATCH
END;