Complete SQL Server Tutorial for Beginners

---

1. Introduction to SQL Server {#introduction}

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It’s designed to store, retrieve, and manage data efficiently.

Key Features:

• Scalability: Handles small to enterprise-level applications
• Security: Built-in security features and authentication
• Integration: Works seamlessly with Microsoft ecosystem
• Performance: Optimized for high-performance operations

What is a Database?

A database is an organized collection of structured information stored electronically in a computer system.

Example: Think of a library catalog system that keeps track of books, authors, and borrowers.

---

2. Installation and Setup {#installation}

System Requirements:

• Windows Operating System
• Minimum 4GB RAM (8GB+ recommended)
• At least 6GB free disk space

Installation Steps:

1. Download SQL Server from Microsoft’s official website
2. Choose your edition (Express is free for learning)
3. Run the installer and select “Basic” installation
4. Download and install SQL Server Management Studio (SSMS)

---

3. Basic Concepts and Terminology {#basic-concepts}

Essential Terms:

Database: Container that holds related tables and objects

Example: CompanyDB (contains employee, department, project tables)

Table: Collection of related data organized in rows and columns

Example: Employees table with columns: ID, Name, Department, Salary

Row (Record): Single entry in a table

Example: 1, "John Smith", "IT", 50000

Column (Field): Attribute or property of the data

Example: Name, Department, Salary

Primary Key: Unique identifier for each row

Example: EmployeeID (each employee has a unique ID)

Foreign Key: Links two tables together

Example: DepartmentID in Employees table references Departments table

---

4. SQL Server Management Studio (SSMS) {#ssms}

SSMS is the primary tool for managing SQL Server databases.

Key Components:

• Object Explorer: Navigate databases and objects
• Query Editor: Write and execute SQL commands
• Results Panel: View query results
• Messages Panel: Display system messages and errors

Basic Navigation:

Object Explorer Structure:
├── Databases
│   ├── System Databases
│   └── User Databases
├── Security
├── Server Objects
└── Management

---

5. Creating and Managing Databases {#databases}

Creating a Database:

Method 1: Using GUI

1. Right-click “Databases” in Object Explorer
2. Select “New Database”
3. Enter database name
4. Click “OK”

Method 2: Using SQL Command

CREATE DATABASE CompanyDB;

Using a Database:

USE CompanyDB;

Dropping a Database:

DROP DATABASE CompanyDB;

⚠️ Warning: This permanently deletes the database and all its data!

---

6. Data Types {#data-types}

Understanding data types is crucial for efficient database design.

Common Data Types:

Numeric Types:

INT          -- Integer numbers (-2,147,483,648 to 2,147,483,647)
BIGINT       -- Large integers
DECIMAL(p,s) -- Fixed precision numbers
FLOAT        -- Floating point numbers

String Types:

VARCHAR(n)   -- Variable-length string (up to n characters)
NVARCHAR(n)  -- Unicode variable-length string
CHAR(n)      -- Fixed-length string
TEXT         -- Large text data

Date/Time Types:

DATE         -- Date only (YYYY-MM-DD)
DATETIME     -- Date and time
TIME         -- Time only
TIMESTAMP    -- Automatically updated timestamp

Other Types:

BIT          -- Boolean (0 or 1)
UNIQUEIDENTIFIER -- GUID
BINARY       -- Binary data

Examples:

DECLARE @Age INT = 25;
DECLARE @Name VARCHAR(50) = 'John Doe';
DECLARE @BirthDate DATE = '1998-05-15';
DECLARE @IsActive BIT = 1;

---

7. Creating Tables {#tables}

Tables are the foundation of your database structure.

Basic Syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example – Creating an Employees Table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    HireDate DATE NOT NULL,
    Salary DECIMAL(10,2),
    DepartmentID INT,
    IsActive BIT DEFAULT 1
);

Explanation:

• IDENTITY(1,1): Auto-incrementing number starting at 1
• NOT NULL: Field must have a value
• UNIQUE: No duplicate values allowed
• DEFAULT 1: Sets default value if none provided

Creating a Departments Table:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName VARCHAR(50) NOT NULL,
    Location VARCHAR(100),
    ManagerID INT
);

---

8. Basic SQL Operations (CRUD) {#crud-operations}

CRUD stands for Create, Read, Update, Delete – the four basic operations.

CREATE (INSERT) – Adding Data

Single Row Insert:

INSERT INTO Departments (DepartmentName, Location)
VALUES ('Information Technology', 'Building A');

Multiple Rows Insert:

INSERT INTO Departments (DepartmentName, Location)
VALUES 
    ('Human Resources', 'Building B'),
    ('Finance', 'Building C'),
    ('Marketing', 'Building A');

Insert with All Columns:

INSERT INTO Employees 
VALUES (1, 'John', 'Smith', 'john.smith@company.com', '2023-01-15', 55000, 1, 1);

READ (SELECT) – Retrieving Data

Select All Columns:

SELECT * FROM Employees;

Select Specific Columns:

SELECT FirstName, LastName, Salary 
FROM Employees;

Select with Conditions:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Select with Multiple Conditions:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 40000 AND DepartmentID = 1;

Select with Sorting:

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

Select Top Records:

SELECT TOP 5 FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

UPDATE – Modifying Data

Update Single Record:

UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;

Update Multiple Records:

UPDATE Employees
SET Salary = Salary * 1.1
WHERE DepartmentID = 1;

Update with Multiple Columns:

UPDATE Employees
SET Salary = 65000, DepartmentID = 2
WHERE EmployeeID = 1;

DELETE – Removing Data

Delete Specific Records:

DELETE FROM Employees
WHERE EmployeeID = 1;

Delete with Conditions:

DELETE FROM Employees
WHERE IsActive = 0;

⚠️ Delete All Records (Be Careful!):

DELETE FROM Employees;

---

9. Data Manipulation Language (DML) {#dml}

Advanced SELECT Queries

LIKE Operator (Pattern Matching):

-- Names starting with 'J'
SELECT * FROM Employees WHERE FirstName LIKE 'J%';

-- Names ending with 'son'
SELECT * FROM Employees WHERE LastName LIKE '%son';

-- Names containing 'an'
SELECT * FROM Employees WHERE FirstName LIKE '%an%';

IN Operator:

SELECT * FROM Employees 
WHERE DepartmentID IN (1, 2, 3);

BETWEEN Operator:

SELECT * FROM Employees 
WHERE Salary BETWEEN 40000 AND 60000;

IS NULL / IS NOT NULL:

SELECT * FROM Employees WHERE Email IS NOT NULL;
SELECT * FROM Employees WHERE ManagerID IS NULL;

Aggregate Functions

COUNT:

SELECT COUNT(*) AS TotalEmployees FROM Employees;
SELECT COUNT(Email) AS EmployeesWithEmail FROM Employees;

SUM:

SELECT SUM(Salary) AS TotalSalaryBudget FROM Employees;

AVG:

SELECT AVG(Salary) AS AverageSalary FROM Employees;

MIN/MAX:

SELECT MIN(Salary) AS LowestSalary, MAX(Salary) AS HighestSalary 
FROM Employees;

GROUP BY and HAVING

GROUP BY:

SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

HAVING (Filter Groups):

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

---

10. Data Definition Language (DDL) {#ddl}

ALTER TABLE – Modifying Table Structure

Add Column:

ALTER TABLE Employees
ADD Phone VARCHAR(15);

Drop Column:

ALTER TABLE Employees
DROP COLUMN Phone;

Modify Column:

ALTER TABLE Employees
ALTER COLUMN FirstName VARCHAR(100);

TRUNCATE vs DELETE

TRUNCATE (Faster, removes all data):

TRUNCATE TABLE Employees;

DELETE (Slower, can use WHERE clause):

DELETE FROM Employees WHERE Salary < 30000;

---

11. Constraints {#constraints}

Constraints ensure data integrity and enforce business rules.

Types of Constraints:

PRIMARY KEY:

ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

FOREIGN KEY:

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Department 
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

UNIQUE:

ALTER TABLE Employees
ADD CONSTRAINT UK_Employees_Email UNIQUE (Email);

CHECK:

ALTER TABLE Employees
ADD CONSTRAINT CK_Employees_Salary CHECK (Salary > 0);

NOT NULL:

ALTER TABLE Employees
ALTER COLUMN FirstName VARCHAR(50) NOT

NOT NULL:

ALTER TABLE Employees
ALTER COLUMN FirstName VARCHAR(50) NOT NULL;

DEFAULT:

ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_IsActive DEFAULT 1 FOR IsActive;

Example – Creating Table with Multiple Constraints:

CREATE TABLE Projects (
    ProjectID INT IDENTITY(1,1) PRIMARY KEY,
    ProjectName VARCHAR(100) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE,
    Budget DECIMAL(12,2) CHECK (Budget > 0),
    Status VARCHAR(20) DEFAULT 'Active',
    ManagerID INT,
    CONSTRAINT FK_Projects_Manager 
        FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID),
    CONSTRAINT CK_Projects_Dates 
        CHECK (EndDate IS NULL OR EndDate > StartDate)
);

---

12. Indexes {#indexes}

Indexes improve query performance by creating faster data access paths.

Types of Indexes:

Clustered Index (One per table):

CREATE CLUSTERED INDEX IX_Employees_EmployeeID 
ON Employees (EmployeeID);

Non-Clustered Index:

CREATE NONCLUSTERED INDEX IX_Employees_LastName 
ON Employees (LastName);

Composite Index (Multiple columns):

CREATE INDEX IX_Employees_Name 
ON Employees (LastName, FirstName);

Unique Index:

CREATE UNIQUE INDEX IX_Employees_Email 
ON Employees (Email);

When to Use Indexes:

• ✅ Columns frequently used in WHERE clauses
• ✅ Columns used in JOIN conditions
• ✅ Columns used in ORDER BY
• ❌ Tables with frequent INSERT/UPDATE operations
• ❌ Small tables (few hundred rows)

Example Usage:

-- This query will benefit from an index on LastName
SELECT * FROM Employees WHERE LastName = 'Smith';

-- Create the index
CREATE INDEX IX_Employees_LastName ON Employees (LastName);

---

13. Views {#views}

Views are virtual tables based on SQL queries. They don’t store data but provide a way to simplify complex queries.

Creating Views:

Simple View:

CREATE VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Email, Salary
FROM Employees
WHERE IsActive = 1;

Complex View with JOINs:

CREATE VIEW vw_EmployeeDepartments AS
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS FullName,
    e.Email,
    e.Salary,
    d.DepartmentName,
    d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.IsActive = 1;

Using Views:

-- Query the view like a regular table
SELECT * FROM vw_ActiveEmployees;

SELECT * FROM vw_EmployeeDepartments 
WHERE DepartmentName = 'IT';

Benefits of Views:

• Security: Hide sensitive columns
• Simplicity: Simplify complex queries
• Consistency: Standardize data access
• Abstraction: Hide table structure changes

Modifying Views:

ALTER VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Email, Salary, HireDate
FROM Employees
WHERE IsActive = 1;

---

14. Stored Procedures {#stored-procedures}

Stored procedures are pre-compiled SQL code blocks that can be executed repeatedly.

Creating Stored Procedures:

Simple Stored Procedure:

CREATE PROCEDURE sp_GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees WHERE IsActive = 1;
END;

Stored Procedure with Parameters:

CREATE PROCEDURE sp_GetEmployeesByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
END;

Stored Procedure with Multiple Parameters:

CREATE PROCEDURE sp_GetEmployeesBySalaryRange
    @MinSalary DECIMAL(10,2),
    @MaxSalary DECIMAL(10,2)
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary BETWEEN @MinSalary AND @MaxSalary
    AND IsActive = 1
    ORDER BY Salary DESC;
END;

Stored Procedure with Output Parameters:

CREATE PROCEDURE sp_GetEmployeeCount
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*)
    FROM Employees
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
END;

Executing Stored Procedures:

Execute Simple Procedure:

EXEC sp_GetAllEmployees;

Execute with Parameters:

EXEC sp_GetEmployeesByDepartment @DepartmentID = 1;

Execute with Output Parameter:

DECLARE @Count INT;
EXEC sp_GetEmployeeCount @DepartmentID = 1, @EmployeeCount = @Count OUTPUT;
SELECT @Count AS EmployeeCount;

Advanced Stored Procedure Example:

CREATE PROCEDURE sp_InsertEmployee
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @Email VARCHAR(100),
    @HireDate DATE,
    @Salary DECIMAL(10,2),
    @DepartmentID INT,
    @NewEmployeeID INT OUTPUT
AS
BEGIN
    BEGIN TRY
        -- Check if email already exists
        IF EXISTS (SELECT 1 FROM Employees WHERE Email = @Email)
        BEGIN
            RAISERROR ('Email address already exists', 16, 1);
            RETURN;
        END
        
        -- Insert the new employee
        INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
        VALUES (@FirstName, @LastName, @Email, @HireDate, @Salary, @DepartmentID);
        
        -- Get the new employee ID
        SET @NewEmployeeID = SCOPE_IDENTITY();
        
        PRINT 'Employee added successfully with ID: ' + CAST(@NewEmployeeID AS VARCHAR(10));
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE();
    END CATCH
END;

---

15. Functions {#functions}

SQL Server has built-in functions and allows you to create custom functions.

Built-in String Functions:

-- String manipulation examples
SELECT 
    FirstName,
    LEN(FirstName) AS NameLength,
    UPPER(FirstName) AS UpperCase,
    LOWER(FirstName) AS LowerCase,
    LEFT(FirstName, 3) AS FirstThreeChars,
    RIGHT(LastName, 3) AS LastThreeChars,
    SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS Username
FROM Employees;

Built-in Date Functions:

-- Date manipulation examples
SELECT 
    HireDate,
    GETDATE() AS CurrentDate,
    DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsEmployed,
    DATEADD(YEAR, 1, HireDate) AS OneYearAfterHire,
    YEAR(HireDate) AS HireYear,
    MONTH(HireDate) AS HireMonth,
    DAY(HireDate) AS HireDay
FROM Employees;

Built-in Mathematical Functions:

-- Math functions
SELECT 
    Salary,
    ROUND(Salary, 0) AS RoundedSalary,
    CEILING(Salary / 1000.0) AS CeilingSalaryInThousands,
    FLOOR(Salary / 1000.0) AS FloorSalaryInThousands,
    ABS(Salary - 50000) AS DifferenceFrom50K
FROM Employees;

Creating User-Defined Functions:

Scalar Function (Returns single value):

CREATE FUNCTION fn_CalculateBonus(@Salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10,2);
    
    IF @Salary < 40000
        SET @Bonus = @Salary * 0.05;
    ELSE IF @Salary < 60000
        SET @Bonus = @Salary * 0.10;
    ELSE
        SET @Bonus = @Salary * 0.15;
    
    RETURN @Bonus;
END;

Using the Function:

SELECT 
    FirstName,
    LastName,
    Salary,
    dbo.fn_CalculateBonus(Salary) AS Bonus
FROM Employees;

Table-Valued Function:

CREATE FUNCTION fn_GetEmployeesByDepartment(@DepartmentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID AND IsActive = 1
);

Using Table-Valued Function:

SELECT * FROM dbo.fn_GetEmployeesByDepartment(1);

---

16. Advanced Concepts

JOINs – Combining Data from Multiple Tables

INNER JOIN (Returns matching records from both tables):

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

LEFT JOIN (Returns all records from left table):

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

RIGHT JOIN (Returns all records from right table):

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

FULL OUTER JOIN (Returns all records from both tables):

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Subqueries

Subquery in WHERE clause:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary) 
    FROM Employees
);

Subquery with EXISTS:

SELECT DepartmentName
FROM Departments d
WHERE EXISTS (
    SELECT 1 
    FROM Employees e 
    WHERE e.DepartmentID = d.DepartmentID
);

Common Table Expressions (CTEs)

Simple CTE:

WITH HighEarners AS (
    SELECT FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 60000
)
SELECT * FROM HighEarners ORDER BY Salary DESC;

Recursive CTE (Employee Hierarchy):

WITH EmployeeHierarchy AS (
    -- Anchor: Top-level managers
    SELECT EmployeeID, FirstName, LastName, ManagerID, 0 as Level
    FROM Employees
    WHERE 

WITH EmployeeHierarchy AS (
    -- Anchor: Top-level managers
    SELECT EmployeeID, FirstName, LastName, ManagerID, 0 as Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive: Employees with managers
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy ORDER BY Level, LastName;

Window Functions

ROW_NUMBER():

SELECT 
    FirstName,
    LastName,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) as SalaryRank
FROM Employees;

RANK() and DENSE_RANK():

SELECT 
    FirstName,
    LastName,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) as Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) as DenseRank
FROM Employees;

Partitioned Window Functions:

SELECT 
    FirstName,
    LastName,
    DepartmentID,
    Salary,
    AVG(Salary) OVER (PARTITION BY DepartmentID) as DeptAvgSalary,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) as DeptRank
FROM Employees;

---

17. Error Handling and Transactions

TRY-CATCH Blocks:

BEGIN TRY
    BEGIN TRANSACTION;
    
    INSERT INTO Departments (DepartmentName, Location)
    VALUES ('Research', 'Building D');
    
    INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
    VALUES ('Jane', 'Doe', 'jane.doe@company.com', GETDATE(), 55000, 
            (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Research'));
    
    COMMIT TRANSACTION;
    PRINT 'Transaction completed successfully.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
END CATCH;

Transaction Control:

-- Start a transaction
BEGIN TRANSACTION;

-- Perform operations
UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 1;

-- Check if everything is correct
IF @@ROWCOUNT > 0
    COMMIT TRANSACTION;  -- Make changes permanent
ELSE
    ROLLBACK TRANSACTION;  -- Undo changes

---

18. Performance Optimization Tips

Query Optimization:

Use Indexes Effectively:

-- Good: Uses index on LastName
SELECT * FROM Employees WHERE LastName = 'Smith';

-- Bad: Function on column prevents index usage
SELECT * FROM Employees WHERE UPPER(LastName) = 'SMITH';

-- Better: Store data in consistent case or use computed column
SELECT * FROM Employees WHERE LastName = 'Smith';

Use EXISTS instead of IN with subqueries:

-- Slower
SELECT * FROM Departments 
WHERE DepartmentID IN (SELECT DepartmentID FROM Employees);

-- Faster
SELECT * FROM Departments d
WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID);

Limit Result Sets:

-- Use TOP to limit results
SELECT TOP 100 * FROM Employees ORDER BY Salary DESC;

-- Use WHERE clauses to filter early
SELECT * FROM Employees 
WHERE IsActive = 1 AND HireDate >= '2020-01-01';

---

19. Best Practices {#best-practices}

Database Design Best Practices:

1. Naming Conventions:-- Tables: Plural nouns Employees, Departments, Projects -- Columns: Descriptive names FirstName (not FName), CreatedDate (not CDate) -- Primary Keys: TableName + ID EmployeeID, DepartmentID -- Foreign Keys: ReferencedTable + ID DepartmentID (in Employees table)
2. Data Types:-- Use appropriate data types VARCHAR(50) for names (not VARCHAR(MAX)) INT for IDs (not VARCHAR) DECIMAL(10,2) for currency (not FLOAT) DATE for dates (not VARCHAR)
3. Constraints:-- Always define primary keys EmployeeID INT PRIMARY KEY IDENTITY(1,1) -- Use foreign keys for referential integrity CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) -- Add check constraints for business rules CONSTRAINT CK_Employee_Salary CHECK (Salary > 0)

SQL Coding Best Practices:

1. Formatting:-- Good formatting SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE e.IsActive = 1 ORDER BY e.LastName, e.FirstName;
2. Use Aliases:-- Clear table aliases SELECT emp.FirstName, dept.DepartmentName FROM Employees emp INNER JOIN Departments dept ON emp.DepartmentID = dept.DepartmentID;
3. *Avoid SELECT :-- Bad SELECT * FROM Employees; -- Good SELECT EmployeeID, FirstName, LastName, Email FROM Employees;

Security Best Practices:

1. Use Parameterized Queries:-- Stored procedure with parameters (prevents SQL injection) CREATE PROCEDURE sp_GetEmployee @EmployeeID INT AS BEGIN SELECT FirstName, LastName, Email FROM Employees WHERE EmployeeID = @EmployeeID; END;
2. Principle of Least Privilege:
   • Give users only the minimum permissions needed
   • Use database roles
   • Regularly review and audit permissions
3. Regular Backups:-- Full backup BACKUP DATABASE CompanyDB TO DISK = 'C:\Backups\CompanyDB_Full.bak'; -- Differential backup BACKUP DATABASE CompanyDB TO DISK = 'C:\Backups\CompanyDB_Diff.bak' WITH DIFFERENTIAL;

---

20. Common Scenarios and Examples

Scenario 1: Employee Management System

Complete Example:

-- Create the database structure
CREATE TABLE Departments (
    DepartmentID INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName VARCHAR(50) NOT NULL UNIQUE,
    Location VARCHAR(100),
    Budget DECIMAL(12,2)
);

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(15),
    HireDate DATE NOT NULL,
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    DepartmentID INT,
    ManagerID INT,
    IsActive BIT DEFAULT 1,
    CONSTRAINT FK_Employee_Department 
        FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
    CONSTRAINT FK_Employee_Manager 
        FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

-- Insert sample data
INSERT INTO Departments (DepartmentName, Location, Budget) VALUES
('Human Resources', 'Building A', 500000),
('Information Technology', 'Building B', 1200000),
('Finance', 'Building C', 800000),
('Marketing', 'Building A', 600000);

INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID) VALUES
('John', 'Smith', 'john.smith@company.com', '2020-01-15', 75000, 2),
('Sarah', 'Johnson', 'sarah.johnson@company.com', '2019-03-20', 65000, 1),
('Mike', 'Brown', 'mike.brown@company.com', '2021-06-10', 80000, 2),
('Lisa', 'Davis', 'lisa.davis@company.com', '2020-09-05', 70000, 3),
('David', 'Wilson', 'david.wilson@company.com', '2022-01-12', 60000, 4);

-- Update manager relationships
UPDATE Employees SET ManagerID = 1 WHERE EmployeeID IN (3, 5);
UPDATE Employees SET ManagerID = 2 WHERE EmployeeID = 4;

Common Queries:

1. Get all employees with their department information:

SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS FullName,
    e.Email,
    e.Salary,
    d.DepartmentName,
    d.Location
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.IsActive = 1;

2. Find employees earning above average salary:

SELECT 
    FirstName,
    LastName,
    Salary,
    (SELECT AVG(Salary) FROM Employees WHERE IsActive = 1) AS AvgSalary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE IsActive = 1)
AND IsActive = 1
ORDER BY Salary DESC;

3. Get department statistics:

SELECT 
    d.DepartmentName,
    COUNT(e.EmployeeID) AS EmployeeCount,
    AVG(e.Salary) AS AverageSalary,
    MIN(e.Salary) AS MinSalary,
    MAX(e.Salary) AS MaxSalary,
    SUM(e.Salary) AS TotalSalaryBudget
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID AND e.IsActive = 1
GROUP BY d.DepartmentID, d.DepartmentName
ORDER BY EmployeeCount DESC;

4. Find employees and their managers:

SELECT 
    emp.FirstName + ' ' + emp.LastName AS Employee,
    emp.Salary AS EmployeeSalary,
    mgr.FirstName + ' ' + mgr.LastName AS Manager,
    dept.DepartmentName
FROM Employees emp
LEFT JOIN Employees mgr ON emp.ManagerID = mgr.EmployeeID
LEFT JOIN Departments dept ON emp.DepartmentID = dept.DepartmentID
WHERE emp.IsActive = 1
ORDER BY dept.DepartmentName, mgr.LastName, emp.LastName;

---

21. Troubleshooting Common Issues

Common Errors and Solutions:

1. “Invalid column name”

-- Error: Column doesn't exist
SELECT FirstNam FROM Employees;  -- Typo in column name

-- Solution: Check spelling and verify column exists
SELECT FirstName FROM Employees;

2. “Cannot insert NULL value”

-- Error: Trying to insert NULL into NOT NULL column
INSERT INTO Employees (LastName, Email, HireDate, Salary)
VALUES ('Smith', 'john.smith@company.com', '2023-01-15', 50000);
-- Missing required FirstName

-- Solution: Provide all required values
INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary)
VALUES ('John', 'Smith', 'john.smith@company.com', '2023-01-15', 50000);

3. “Primary Key violation”

-- Error: Trying to insert duplicate primary key
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, HireDate, Salary)
VALUES (1, 'Jane', 'Doe', 'jane.doe@company.com', '2023-01-15', 45000);
-- EmployeeID 1 already exists

-- Solution: Let IDENTITY handle the ID or use a unique value
INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary)
VALUES ('Jane', 'Doe', 'jane.doe@company.com', '2023-01-15', 45000);

4. “Foreign Key constraint violation”

-- Error: Referenced value doesn't exist
INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
VALUES ('Bob', 'Johnson', 'bob.johnson@company.com', '2023-01-15', 55000, 999);
-- Department 999 doesn't exist

-- Solution: Use valid DepartmentID or create the department first
INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
VALUES ('Bob', 'Johnson', 'bob.johnson@company.com', '2023-01-15', 55000, 1);

5. “Arithmetic overflow error”

-- Error: Value too large for data type
DECLARE @SmallNumber TINYINT = 300;  -- TINYINT max is 255

-- Solution: Use appropriate data type
DECLARE @Number SMALLINT = 300;  -- SMALLINT can handle this valueF

24. Database Maintenance

Backup and Restore:

Full Backup:

-- Create full backup
BACKUP DATABASE CompanyDB
TO DISK = N'C:\Backup\CompanyDB_Full.bak'
WITH FORMAT, INIT, 
NAME = N'CompanyDB Full Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

Differential Backup:

-- Create differential backup
BACKUP DATABASE CompanyDB
TO DISK = N'C:\Backup\CompanyDB_Diff.bak'
WITH DIFFERENTIAL, FORMAT, INIT,
NAME = N'CompanyDB Differential Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

Transaction Log Backup:

-- Backup transaction log
BACKUP LOG CompanyDB
TO DISK = N'C:\Backup\CompanyDB_Log.trn'
WITH FORMAT, INIT,
NAME = N'CompanyDB Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

Restore Database:

-- Restore from full backup
RESTORE DATABASE CompanyDB_Restored
FROM DISK = N'C:\Backup\CompanyDB_Full.bak'
WITH FILE = 1, 
MOVE N'CompanyDB' TO N'C:\Data\CompanyDB_Restored.mdf',
MOVE N'CompanyDB_Log' TO N'C:\Data\CompanyDB_Restored.ldf',
NOUNLOAD, REPLACE, STATS = 5;

Database Maintenance Tasks:

Update Statistics:

-- Update statistics for all tables
EXEC sp_updatestats;

-- Update statistics for specific table
UPDATE STATISTICS Employees;

Rebuild Indexes:

-- Rebuild all indexes on a table
ALTER INDEX ALL ON Employees REBUILD;

-- Rebuild specific index
ALTER INDEX IX_Employees_LastName ON Employees REBUILD;

Check Database Integrity:

-- Check database consistency
DBCC CHECKDB('CompanyDB');

-- Check specific table
DBCC CHECKTABLE('Employees');

---

25. Security and User Management

Creating Logins and Users:

-- Create SQL Server login
CREATE LOGIN CompanyUser WITH PASSWORD = 'SecurePassword123!';

-- Create database user
USE CompanyDB;
CREATE USER CompanyUser FOR LOGIN CompanyUser;

-- Grant permissions
GRANT SELECT, INSERT, UPDATE ON Employees TO CompanyUser;
GRANT SELECT ON Departments TO CompanyUser;

Database Roles:

-- Create custom role
CREATE ROLE EmployeeManager;

-- Grant permissions to role
GRANT SELECT, INSERT, UPDATE ON Employees TO EmployeeManager;
GRANT SELECT ON Departments TO EmployeeManager;

-- Add user to role
ALTER ROLE EmployeeManager ADD MEMBER CompanyUser;

Row-Level Security Example:

-- Add security column
ALTER TABLE Employees ADD CreatedBy VARCHAR(50) DEFAULT SYSTEM_USER;

-- Create security policy function
CREATE FUNCTION dbo.fn_SecurityPredicate(@CreatedBy VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_SecurityPredicate_Result
WHERE @CreatedBy = SYSTEM_USER OR IS_MEMBER('db_owner') = 1;

-- Create security policy
CREATE SECURITY POLICY EmployeeSecurityPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(CreatedBy) ON dbo.Employees
WITH (STATE = ON);