Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

SQL Server Fundamentals

SQL Server fundamentals introduce the basic concepts required to work with databases, tables, SQL queries and the SQL Server database engine.

What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store, manage and retrieve structured data.

SQL Server uses SQL (Structured Query Language) to communicate with databases and perform operations such as inserting, retrieving, updating and deleting data.

What is a Database?

A database is an organized collection of data that allows information to be stored and managed efficiently.

In SQL Server, a database can contain tables, views, stored procedures, functions and other database objects.

Example

A college database may contain student information, course information, faculty information and examination records.

What is a Table?

A table is used to store data in rows and columns. Each table represents a particular type of information.

For example, a Students table can contain information about students.

StudentId Name Email Course
1 Rahul rahul@example.com .NET
2 Priya priya@example.com Java
3 Amit amit@example.com Python

Rows and Columns

A table is made up of rows and columns.

  • Column: Defines a particular type of data.
  • Row: Represents one complete record.

What is SQL?

SQL stands for Structured Query Language. It is used to communicate with relational databases.

SQL allows developers and database administrators to perform different operations on database data.

Operation Purpose
SELECT Retrieve data from a database.
INSERT Add new data to a table.
UPDATE Modify existing data.
DELETE Remove data from a table.

Basic SQL Query

The following query retrieves all records from the Students table.

SQL
SELECT * FROM Students;

How SQL Server Works

A client application sends a SQL query to SQL Server. SQL Server processes the query and communicates with the database engine to retrieve or modify the required data.

SQL Server
                                                Client Application
                                                         ↓
                                                      SQL Query
                                                          ↓
                                                      SQL Server
                                                          ↓
                                                     Database Engine
                                                          ↓
                                                       Database
                                                          ↓
                                                        Result

The Database Engine processes the SQL statement and returns the requested result to the client application.

Common SQL Server Database Objects

SQL Server databases contain different objects that are used to store data and implement database functionality.

Object Purpose
Tables Store data in rows and columns.
Views Provide a virtual representation of data.
Stored Procedures Store reusable SQL statements and logic.
Functions Perform reusable operations and return values.
Triggers Automatically execute in response to database events.

CRUD Operations

CRUD represents the four basic operations performed on database data.

Operation SQL Command Purpose
Create INSERT Add new records.
Read SELECT Retrieve records.
Update UPDATE Modify records.
Delete DELETE Remove records.

Summary

In this section, we learned the fundamentals of SQL Server, databases, tables, rows and columns, SQL queries, database objects and CRUD operations. These concepts provide the foundation for working with SQL Server databases.