Comprehensive and beginner-friendly SQL Server notes including installation steps, SQL vs T-SQL differences, CRUD operations, joins, stored procedures, and more

SQL Server Complete Beginner Guide.md

SQL Server Complete Notes for Beginners and Trainers

---

1. Introduction to SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It allows users to create, manage, and interact with databases using SQL (Structured Query Language). SQL Server uses a dialect of SQL called T-SQL (Transact-SQL), which includes additional features beyond standard SQL.

Tool used to manage and interact with SQL Server:

• SQL Server Management Studio (SSMS)

---

2. Installing SQL Server and SSMS

Step-by-step Installation

1. Go to the official download page: https://www.microsoft.com/en-us/sql-server/sql-server-downloads
2. Download SQL Server 2022 Express (free version for development and learning).
3. Run the installer and choose Basic Installation.
4. After installation, install SQL Server Management Studio (SSMS) from: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
5. Open SSMS and connect to your local SQL Server instance (usually named localhost or (localdb)\MSSQLLocalDB).

---

3. Working with Databases

Create a Database (Standard SQL)

CREATE DATABASE SchoolDB;

Creates a new database named SchoolDB.

Use a Database (SQL Server specific)

USE SchoolDB;

Switches the context to SchoolDB so that all subsequent commands target this database.

Drop a Database (Standard SQL)

DROP DATABASE SchoolDB;

Deletes the entire database including its contents.

---

4. Working with Tables

Create a Table (Standard SQL with SQL Server-specific syntax)

CREATE TABLE Students (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    Age INT,
    Email NVARCHAR(100)
);

Creates a table Students with four columns. Id auto-increments (SQL Server-specific: IDENTITY).

View All Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES; -- Standard SQL
SELECT * FROM sys.tables; -- SQL Server specific

Lists all tables in the current database.

Drop a Table (Standard SQL)

DROP TABLE Students;

Deletes the Students table and all its data.

Truncate a Table (Standard SQL)

TRUNCATE TABLE Students;

Removes all rows from the table quickly without logging each row deletion.

---

5. Basic CRUD Operations

Insert Data (Standard SQL)

INSERT INTO Students (Name, Age, Email)
VALUES ('Alice', 20, 'alice@example.com');

Adds a new student to the Students table.

Read Data (Standard SQL)

SELECT * FROM Students;

Displays all records from the Students table.

Filter Records (Standard SQL)

SELECT * FROM Students WHERE Age > 18;

Displays only students older than 18.

Update Data (Standard SQL)

UPDATE Students
SET Age = 21
WHERE Name = 'Alice';

Changes Alice's age to 21.

Delete Data (Standard SQL)

DELETE FROM Students
WHERE Name = 'Alice';

Removes the student named Alice from the table.

---

6. Data Types in SQL Server

Data Type	Example	Description
INT	1, 100	Whole numbers
DECIMAL(10,2)	99.99	Decimal numbers
NVARCHAR(50)	'John'	Unicode character string
DATETIME	'2025-01-01 10:00:00'	Date and time
BIT	0 or 1	Boolean (true/false)

---

7. Constraints

Constraints enforce rules on data.

CREATE TABLE Courses (
    Id INT PRIMARY KEY IDENTITY,
    Title NVARCHAR(100) NOT NULL,
    Fee DECIMAL(10,2) CHECK(Fee >= 0),
    CreatedAt DATETIME DEFAULT GETDATE()
);

• PRIMARY KEY ensures each row is unique.
• NOT NULL prevents null values.
• CHECK validates a condition.
• DEFAULT sets a default value.

---

8. Filtering and Sorting

WHERE Clause (Standard SQL)

SELECT * FROM Students WHERE Age >= 18;

Filters results based on a condition.

ORDER BY Clause (Standard SQL)

SELECT * FROM Students ORDER BY Age DESC;

Sorts the results by age in descending order.

TOP Clause (SQL Server specific)

SELECT TOP 5 * FROM Students;

Returns only the first 5 rows.

---

9. Grouping and Aggregation

GROUP BY and COUNT (Standard SQL)

SELECT Age, COUNT(*) AS Count FROM Students GROUP BY Age;

Groups students by age and counts how many per group.

Other Aggregate Functions (Standard SQL)

• SUM(column) - total sum
• AVG(column) - average value
• MIN(column) - smallest value
• MAX(column) - largest value

---

10. Joins

INNER JOIN (Standard SQL)

SELECT s.Name, c.Title
FROM Students s
INNER JOIN Courses c ON s.CourseId = c.Id;

Shows students along with their course title if a match exists.

LEFT JOIN (Standard SQL)

SELECT s.Name, c.Title
FROM Students s
LEFT JOIN Courses c ON s.CourseId = c.Id;

Shows all students and their courses (null if no course).

---

11. Views

Creating and Using Views (Standard SQL)

CREATE VIEW AdultStudents AS
SELECT * FROM Students WHERE Age >= 18;

SELECT * FROM AdultStudents;

A view is a virtual table based on a SELECT query.

---

12. Stored Procedures (SQL Server specific)

CREATE PROCEDURE GetStudentsByAge @MinAge INT
AS
BEGIN
    SELECT * FROM Students WHERE Age >= @MinAge;
END

EXEC GetStudentsByAge @MinAge = 18;

Stored procedures are reusable blocks of SQL code.

---

13. Functions (SQL Server specific)

Scalar Function

CREATE FUNCTION GetStudentCount()
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Students);
END

SELECT dbo.GetStudentCount();

Functions return a single value and can be used inside queries.

---

14. Transactions (Standard SQL)

BEGIN TRANSACTION;
UPDATE Students SET Age = Age + 1 WHERE Id = 1;
COMMIT;
-- or use ROLLBACK to undo

Used to make changes atomic and reversible.

---

15. Useful Built-in Functions

Function	Description
GETDATE()	Current date and time (SQL Server)
DATEADD()	Adds interval to a date
DATEDIFF()	Calculates date difference
LEN(column)	Gets length of string
ISNULL(col, val)	Replaces NULL with another value

---

16. Admin/System Queries

Show all databases (SQL Server specific)

SELECT name FROM sys.databases;

Show current database (SQL Server specific)

SELECT DB_NAME();

Show all columns of a table (SQL Server specific)

EXEC sp_columns Students;

Show table structure (SQL Server specific)

SP_HELP Students;