ConsoleFlare
  • Python
    • Python Installation
    • Pandas and SQL
  • Projects
    • Data Analytics Project
      • Courier Analytics Challenge
      • Solution
    • Skytrax Airline Review Analysis Pipeline
      • Setting up Azure SQL Database
      • SkyTrax Web Scraping
  • Reporting
    • Power BI
      • Installation
      • Data Sources
      • Important Links
  • PySpark & Databricks
    • Spark vs Hadoop
    • Cluster Computing
    • PySpark
    • Databricks Introduction
    • PySpark in Databricks
    • Reading Data with PySpark
    • PySpark Transformation Methods
    • Handling Duplicate Data
    • PySpark Action Methods
    • PySpark Native Functions
    • Partitioning
    • Bucketing
    • Partitioning vs Bucketing
  • Live Data Streaming
    • Spark Streaming
      • Installation Issues
      • Jupyter Notebook Setup
  • Data Pipeline
    • Azure Data Factory
  • Blockchain
    • Smart Contract Guide
      • Setting up a Node project
      • Developing smart contracts
  • Interview Questions
    • SQL Interview Questions
    • Power BI Interview Questions
  • T-SQL Exercises
    • Exercise 0
    • Exercise 1
    • Exercise 2
    • Exercise 3
  • CHEAT SHEET
    • Ultimate SQL Server Cheat Sheet
Powered by GitBook
On this page
  • Getting Started
  • Sample Data
  • Basic Commands
  • CRUD Operations
  • Filtering & Sorting
  • Joins
  • Aggregations & Grouping
  • Subqueries
  • Common Table Expressions (CTEs)
  • Window Functions
  • Indexing
  • Stored Procedures & Functions
  • Transactions
  • Triggers
  • Error Handling
  • Security & User Management
  • Performance Tuning

Was this helpful?

  1. CHEAT SHEET

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

Was this helpful?

Page cover image