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

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

Contribution Guidelines

Fork the repository
Create a feature branch (git checkout -b feature/enhancement)
Commit changes (git commit -m 'Add: description of improvement')
Push to branch (git push origin feature/enhancement)
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

Learning Platforms

Books

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

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

FilesExpand file tree

README.md

Latest commit

History