🗃️ SQL Server Mastery - T-SQL Script Collection

A Comprehensive Guide from Basics to Advanced Database Operations

📖 Overview

This repository contains a comprehensive collection of T-SQL scripts for Microsoft SQL Server, designed as a practical learning resource and reference guide. From basic database creation to advanced stored procedures and functions, these scripts cover essential SQL Server concepts through hands-on, executable examples.

Perfect for database administrators, developers, and students, this collection provides ready-to-use code snippets that demonstrate real-world SQL Server operations and best practices.

📚 Complete Script Index

📝 Foundation & Schema Management

File	Topic	Description
01-Create.sql	Database Creation	Creating new databases and understanding options
03-Create_table.sql	Table Creation	Creating tables with various data types and constraints
02-DELETE_Drop.sql	Deletion Operations	Properly dropping databases, tables, and data

🔒 Constraints & Data Integrity

File	Topic	Description
04-Defult_constrain.sql	Default Constraints	Setting default values for columns
05-Defult_constrain.sql	Advanced Defaults	Complex default constraint scenarios
06-Cascadingreferentialintegrityconstraint.sql	Referential Integrity	Implementing cascading updates/deletes
07-Check_constraint.sql	Check Constraints	Data validation using check constraints

🆔 Identity & Special Values

File	Topic	Description
08-Retrive_identity_column_Valuessql.sql	Identity Values	Retrieving identity column values after insertion
09-Retrivint_Identity.sql	Identity Management	Working with IDENTITY columns and SCOPE_IDENTITY()

🔍 Data Retrieval & Queries

File	Topic	Description
10-All_About_Select.sql	SELECT Statements	Comprehensive SELECT query examples
11-Group_by.sql	GROUP BY & Aggregation	Data aggregation and grouping operations
15-ReplacingNullValues.sql	NULL Handling	Techniques for managing NULL values
16-CoalesceFunction.sql	COALESCE Function	Using COALESCE for NULL value replacement

🤝 Joins & Set Operations

File	Topic	Description
12-Joins.sql	Basic Joins	INNER, LEFT, RIGHT, FULL OUTER joins
13-AdvancedORintelligentJoin.sql	Advanced Joins	Complex join scenarios and optimizations
14-Self_join.sql	Self Joins	Joining a table to itself
17-Union_UnionAll.sql	Set Operations	UNION, UNION ALL operations

⚙️ Stored Procedures & Functions

File	Topic	Description
18-StoreProcedures.sql	Basic Stored Procedures	Creating and executing stored procedures
19-Store_procedureWithOutputParameter.sql	Output Parameters	Procedures with output parameters
20-StoreProcedureOutputParameterOrReturnValues.sql	Return Values	Procedures returning values
21-AdvantagesOfStoreProcedure.sql	Procedure Benefits	Advantages and use cases for stored procedures
22-BuiltInFunction.sql	Built-in Functions	SQL Server's built-in function library
23-Function.sql	User-defined Functions	Creating custom functions

🔢 Advanced Functions & Operations

File	Topic	Description
24-Math-Numeic.sql	Mathematical Functions	Math and numeric operations
25-Date_Function.sql	Date Functions	Date and time manipulations
26-SQL_Advanced_FUnction.sql	Advanced Functions	Window functions, ranking, and analytics

🚀 Getting Started

Prerequisites

• Microsoft SQL Server (2016 or later recommended)
• SQL Server Management Studio (SSMS) or Azure Data Studio
• Basic understanding of database concepts
• (Optional) AdventureWorks sample database for practice

How to Use These Scripts

Method 1: SSMS/Azure Data Studio

-- Open any .sql file in SSMS
-- Execute the entire script or selected portions
-- Modify parameters as needed for your environment

Method 2: Command Line (sqlcmd)

# Execute a script using sqlcmd
sqlcmd -S your_server -d your_database -i 01-Create.sql

Method 3: Practice Environment Setup

-- 1. Create a practice database
CREATE DATABASE SQLPractice;

-- 2. Switch to the new database
USE SQLPractice;

-- 3. Execute learning scripts in sequence
-- Start with 01-Create.sql, then proceed numerically

🎯 Learning Path

Week 1: Fundamentals

• Days 1-2: Database and table creation (01-03)
• Days 3-4: Constraints and data integrity (04-07)
• Days 5-7: Basic queries and SELECT statements (10-11)

Week 2: Intermediate Skills

• Days 8-10: Joins and relationships (12-14)
• Days 11-12: Advanced query techniques (15-17)
• Days 13-14: Identity and special values (08-09)

Week 3: Advanced Topics

• Days 15-17: Stored procedures (18-21)
• Days 18-20: Functions (22-23, 26)
• Days 21-22: Specialized functions (24-25)

💡 Practical Examples

Creating a Table with Constraints

-- From 03-Create_table.sql and 07-Check_constraint.sql
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18),
    HireDate DATE DEFAULT GETDATE(),
    DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID)
);

Advanced Join Example

-- From 13-AdvancedORintelligentJoin.sql
SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    m.FirstName AS ManagerFirstName,
    m.LastName AS ManagerLastName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE e.HireDate > '2020-01-01';

Stored Procedure with Output

-- From 19-Store_procedureWithOutputParameter.sql
CREATE PROCEDURE GetEmployeeCount
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*)
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

📊 Repository Statistics

Metric	Value	Details
Total Scripts	26	Comprehensive coverage
Language	100% T-SQL	SQL Server Transact-SQL
Total Commits	27	Active development
Development Period	March 2022	Intensive learning phase
Lines of Code	1,000+ (estimated)	Substantial code base
License	MIT	Open source

🔧 Best Practices Demonstrated

1. Code Organization

• Clear, descriptive file names
• Logical progression from simple to complex
• Consistent formatting and commenting
• Separation of concerns (DDL, DML, procedures)

2. Performance Considerations

• Efficient query writing
• Proper indexing strategies
• Set-based operations over cursors
• Parameterized queries for security

3. Security Practices

• SQL injection prevention techniques
• Principle of least privilege in permissions
• Secure coding patterns
• Error handling implementation

🤝 How to Contribute

Ways to Contribute

1. Add More Examples: Additional use cases for each topic
2. Improve Documentation: Better comments and explanations
3. Add Performance Tips: Optimization techniques
4. Include New Features: SQL Server 2019/2022 features
5. Fix Issues: Bug reports and corrections

Contribution Guidelines

1. Fork the repository
2. Create a feature branch (git checkout -b feature/enhancement)
3. Commit changes (git commit -m 'Add: description of improvement')
4. Push to branch (git push origin feature/enhancement)
5. Open a Pull Request

📄 License

This project is licensed under the MIT License. See the LICENSE file for full details.

Permissions:

• ✅ Commercial use
• ✅ Modification
• ✅ Distribution
• ✅ Private use

Conditions:

• Include original copyright and license notice

Limitations:

• No liability
• No warranty

🔗 Related Resources

Official Documentation

• Microsoft SQL Server Docs
• T-SQL Reference
• SQL Server Samples

Learning Platforms

• SQL Server Central
• MSSQLTips
• SQL Shack
• W3Schools SQL

Books

• "T-SQL Fundamentals" by Itzik Ben-Gan
• "SQL Server Internals" by Kalen Delaney
• "Pro SQL Server Internals" by Dmitri Korotkevitch

Tools

• SQL Server Management Studio
• Azure Data Studio
• SQL Server Data Tools

🙏 Acknowledgments

This collection builds upon the extensive knowledge shared by the SQL Server community:

• Microsoft SQL Server Team for creating a robust database platform
• SQL Server MVPs and Experts for sharing knowledge and best practices
• Database Community for continuous learning and improvement
• Educators and Authors who make complex concepts accessible

---

🗄️ "Data is a precious thing and will last longer than the systems themselves." - Tim Berners-Lee

Master SQL Server with these practical scripts! 🚀

Maintained by Feroz455 | Last Updated: December 2024