Ultimate SQL Server Cheat Sheet

This ultimate SQL Server cheat sheet is designed to be a quick reference guide for database administrators, developers, and data analysts. It covers fundamental commands, concepts, and best-practices.

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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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)

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

Window Functions

-- 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

-- 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

-- 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

-- Begin Transaction
BEGIN TRANSACTION;

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

-- Commit Transaction
COMMIT;

-- Rollback Transaction
ROLLBACK;

Triggers

-- 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

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

Security & User Management

-- 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

-- 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

PreviousExercise 3

Last updated 1 year ago