# Ultimate SQL Server Cheat Sheet

### Getting Started

#### **Connecting to SQL Server**

* Connect using SQL Server Management Studio (SSMS):
  * Open SSMS
  * Enter server name
  * Choose authentication mode (Windows/SQL Server)
  * Click 'Connect'

### Sample Data

* **Table: Employees**

```sql
-- create table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Department VARCHAR(50),
    Salary DECIMAL(10,2),
    Email VARCHAR(100) NULL
);
-- insert sample data
INSERT INTO Employees (Name, Age, Department, Salary, Email)
VALUES 
('John Doe', 30, 'IT', 60000.00, 'john.doe@example.com'),
('Jane Smith', 28, 'HR', 55000.00, 'jane.smith@example.com'),
('Alice Johnson', 35, 'Finance', 75000.00, 'alice.johnson@example.com'),
('Bob Williams', 40, 'IT', 80000.00, 'bob.williams@example.com'),
('Charlie Brown', 27, 'Marketing', 50000.00, 'charlie.brown@example.com');

```

* **Table: Departments**

```sql
-- create table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName VARCHAR(50) NOT NULL
);
-- insert sample data
INSERT INTO Departments (DepartmentName)
VALUES 
('IT'),
('HR'),
('Finance'),
('Marketing'),
('Operations');

```

* **Table: Employee\_Audit**

```sql
-- create table
CREATE TABLE Employee_Audit (
    AuditID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT,
    ChangeDate DATETIME DEFAULT GETDATE(),
    ChangeDescription VARCHAR(255)
);
-- insert sample data
INSERT INTO Employee_Audit (EmployeeID, ChangeDescription)
VALUES 
(1, 'Salary updated to 65000'),
(2, 'Department changed to Finance'),
(3, 'New email added: alice.johnson@example.com'),
(4, 'Employee promoted to Senior IT Engineer'),
(5, 'Salary increased to 55000');

```

### **Basic Commands**

```sql
-- Create Database
CREATE DATABASE MyDatabase;

-- Use Database
USE MyDatabase;

-- Drop Database
DROP DATABASE MyDatabase;

-- Create Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
);

-- Drop Table
DROP TABLE Employees;

-- Alter Table
ALTER TABLE Employees ADD Email VARCHAR(100);
ALTER TABLE Employees DROP COLUMN Email;
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);

```

### CRUD Operations

```sql
-- Insert Data
INSERT INTO Employees (Name, Age, Department, Salary)
VALUES ('John Doe', 30, 'IT', 60000.00);

-- Update Data
UPDATE Employees SET Salary = 65000.00 WHERE EmployeeID = 1;

-- Delete Data
DELETE FROM Employees WHERE EmployeeID = 1;

-- Select Data
SELECT * FROM Employees;
SELECT Name, Salary FROM Employees WHERE Department = 'IT';

```

### Filtering & Sorting

```sql
-- WHERE Clause
SELECT * FROM Employees WHERE Age > 30;

-- ORDER BY Clause
SELECT * FROM Employees ORDER BY Salary DESC;

-- DISTINCT Clause
SELECT DISTINCT Department FROM Employees;

-- TOP Clause (Return top N rows)
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;

```

### Joins

```sql
-- INNER JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
INNER JOIN Departments d ON e.Department = d.DepartmentID;

-- LEFT JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
LEFT JOIN Departments d ON e.Department = d.DepartmentID;

-- RIGHT JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
RIGHT JOIN Departments d ON e.Department = d.DepartmentID;

-- FULL JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
FULL JOIN Departments d ON e.Department = d.DepartmentID;

```

### Aggregations & Grouping

```sql
-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) AS TotalEmployees FROM Employees;
SELECT AVG(Salary) AS AvgSalary FROM Employees;
SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary FROM Employees;

-- GROUP BY
SELECT Department, COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY Department;

-- HAVING Clause
SELECT Department, AVG(Salary) AS AvgSalary 
FROM Employees 
GROUP BY Department 
HAVING AVG(Salary) > 50000;

```

### Subqueries

```sql
-- Subquery in WHERE
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Subquery in FROM
SELECT * FROM (SELECT Name, Salary FROM Employees) AS TempTable;

```

### Common Table Expressions (CTEs)

```sql
WITH EmployeeCTE AS (
    SELECT Name, Salary FROM Employees WHERE Salary > 50000
)
SELECT * FROM EmployeeCTE;

```

### Window Functions

```sql
-- ROW_NUMBER, RANK, DENSE_RANK, NTILE
SELECT Name, Salary, 
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
       RANK() OVER (ORDER BY Salary DESC) AS RankNum,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;

-- Running Total
SELECT Name, Salary, 
       SUM(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees;

```

### Indexing

```sql
-- Create Index
CREATE INDEX idx_EmployeeName ON Employees(Name);

-- Unique Index
CREATE UNIQUE INDEX idx_Unique_EmployeeEmail ON Employees(Email);

-- Drop Index
DROP INDEX idx_EmployeeName ON Employees;

```

### Stored Procedures & Functions

```sql
-- Stored Procedure
CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;

EXEC GetEmployees;

-- Function
CREATE FUNCTION GetAverageSalary()
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @AvgSalary DECIMAL(10,2);
    SELECT @AvgSalary = AVG(Salary) FROM Employees;
    RETURN @AvgSalary;
END;

SELECT dbo.GetAverageSalary();

```

### Transactions

```sql
-- Begin Transaction
BEGIN TRANSACTION;

-- Execute Queries
UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 1;

-- Commit Transaction
COMMIT;

-- Rollback Transaction
ROLLBACK;

```

### Triggers

```sql
-- Create Trigger
CREATE TRIGGER trg_AfterInsert ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'New Employee Inserted';
END;

-- Drop Trigger
DROP TRIGGER trg_AfterInsert;

```

### Error Handling

```sql
BEGIN TRY
    UPDATE Employees SET Salary = -5000 WHERE EmployeeID = 1;
END TRY
BEGIN CATCH
    PRINT 'An error occurred';
END CATCH;

```

### Security & User Management

```sql
-- Create User
CREATE LOGIN MyUser WITH PASSWORD = 'StrongPassword!';
CREATE USER MyUser FOR LOGIN MyUser;

-- Grant Permissions
GRANT SELECT, INSERT, UPDATE ON Employees TO MyUser;

-- Revoke Permissions
REVOKE DELETE ON Employees FROM MyUser;

-- Drop User
DROP USER MyUser;

```

### Performance Tuning

```sql
-- Execution Plan
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees;
SET SHOWPLAN_ALL OFF;

-- Analyze Query Performance
DBCC FREEPROCCACHE; -- Clears query cache
DBCC DROPCLEANBUFFERS; -- Clears data cache

```