SQL Tutorial 2025 - DGMnagaland

Beginner Level – SQL Basics

1. What is SQL?

Definition, History, Why SQL is important
Use cases in real-world applications

2. Setting Up the Environment

Tools: MySQL, PostgreSQL, SQLite, SQL Server
GUI tools: DBeaver, HeidiSQL, phpMyAdmin, pgAdmin

3. SQL Syntax Overview

Keywords, Case sensitivity, Semicolons
SQL Statements (DDL, DML, DCL, TCL)

4. Databases, Tables, and Data Types

CREATE DATABASE, CREATE TABLE
Data types (INT, VARCHAR, DATE, etc.)

5. Inserting Data

```
INSERT INTO
```
, NULL values
Bulk insert and formatting

6. Basic SELECT Queries

```
SELECT
```
,
```
FROM
```
,
```
WHERE
```
Filtering data, Comparison operators

7. SQL Operators and Conditions

Logical Operators: AND, OR, NOT
BETWEEN, IN, LIKE, IS NULL

Sorting and Limiting Data

```
ORDER BY
```
,
```
LIMIT
```
/
```
TOP
```
ASC vs DESC

9. Updating and Deleting Data

```
UPDATE
```
,
```
DELETE
```
,
```
TRUNCATE
```

Intermediate Level – Data Manipulation & Relationships

10. Aggregate Functions

```
COUNT
```
,
```
SUM
```
,
```
AVG
```
,
```
MIN
```
,
```
MAX
```
```
GROUP BY
```
,
```
HAVING
```

11. Joins Explained

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

Joining multiple tables

12. Aliases & Subqueries

Column and table aliases
Nested queries (single-row & multi-row subqueries)

13. Constraints and Indexes

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
```
CREATE INDEX
```
, performance tuning

14. Views and Stored Data

```
CREATE VIEW
```
,
```
UPDATE VIEW
```
Use cases for virtual tables

15. SQL Functions

String Functions:
```
CONCAT
```
,
```
SUBSTRING
```
,
```
LENGTH
```
Date Functions:
```
NOW()
```
,
```
DATE_ADD()
```
,
```
DATEDIFF()
```
Conversion Functions

16. Transactions and ACID Properties

```
BEGIN
```
,
```
COMMIT
```
,
```
ROLLBACK
```
Ensuring data integrity

17. Normalization & Table Design

1NF, 2NF, 3NF explained with examples
De-normalization, pros and cons

Advanced Level – Real-World Usage

18. Stored Procedures & Functions

```
CREATE PROCEDURE
```
,
```
CREATE FUNCTION
```
Parameters, RETURNS, and logic

19. Triggers

```
CREATE TRIGGER
```
on
```
INSERT
```
,
```
UPDATE
```
,
```
DELETE
```

20. Window Functions

```
ROW_NUMBER()
```
,
```
RANK()
```
,
```
PARTITION BY
```
,
```
OVER()
```

21. Recursive Queries (CTEs)

```
WITH
```
clause, hierarchical data (parent-child relationships)

22. Performance Optimization

Indexing strategies
Query Execution Plans
Optimization techniques

23. Security in SQL

User roles, GRANT, REVOKE
SQL Injection prevention techniques

24. JSON & XML in SQL

Storing and querying JSON/XML
```
JSON_EXTRACT
```
,
```
OPENJSON
```
, etc.

25. Data Export & Import

```
LOAD DATA INFILE
```
,
```
COPY
```
,
```
mysqldump
```
Backups and migrations