SQL Language Full Theory Course

SQL (Structured Query Language) – Full Theory

1. Introduction to SQL

SQL stands for Structured Query Language.
It is used to communicate with relational databases.
SQL is a standard language approved by ANSI and ISO.
Used to create, retrieve, update, and delete (CRUD) data in databases.

2. Features of SQL

Simple & Powerful – Easy to learn and use.
Declarative Language – You specify what you want, not how.
Portable – Works with all major databases (MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.).
Secure – Provides access control via permissions and roles.
Supports Transactions – Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).

3. Types of SQL Commands

SQL commands are divided into categories:
🔹 3.1 DDL – Data Definition Language
Used to define database schema (structure).
CREATE – Create database objects (table, view, etc.).
ALTER – Modify existing objects.
DROP – Delete objects.
TRUNCATE – Remove all records but keep structure.
RENAME – Rename objects.
🔹 3.2 DML – Data Manipulation Language
Used to manage data inside tables.
SELECT – Retrieve data.
INSERT – Add new data.
UPDATE – Modify data.
DELETE – Remove data.
🔹 3.3 DCL – Data Control Language Used for access control.
GRANT – Give permissions.
REVOKE – Remove permissions.
🔹 3.4 TCL – Transaction Control Language
Used to manage transactions.
COMMIT – Save changes permanently.
ROLLBACK – Undo changes.
SAVEPOINT – Set checkpoints.

4. SQL Data Types

Different databases have variations, but common types are:
Numeric: INT, DECIMAL, FLOAT
String: CHAR, VARCHAR, TEXT
Date/Time: DATE, TIME, DATETIME, TIMESTAMP
Boolean: TRUE / FALSE

5. SQL Operators

Arithmetic: +, -, *, /, %
Comparison: =, !=, <, >, <=, >=
Logical: AND, OR, NOT
Other: BETWEEN, IN, LIKE, IS NULL

6. Constraints in SQL

Constraints ensure data integrity.
PRIMARY KEY – Uniquely identifies each row.
FOREIGN KEY – Links two tables.
UNIQUE – No duplicate values.
NOT NULL – Value cannot be empty.
CHECK – Condition must be true.
DEFAULT – Provides default value.

7. Joins in SQL

Used to combine rows from multiple tables.
INNER JOIN – Returns matching rows.
LEFT JOIN – All rows from left + matches.
RIGHT JOIN – All rows from right + matches.
FULL JOIN – All rows when there is a match in one.
CROSS JOIN – Cartesian product.

8. Views

A view is a virtual table based on SQL query.
Used for simplification and security.
Example: CREATE VIEW employee_info AS SELECT name, salary FROM employees WHERE salary > 50000;

9. Indexes

Indexes speed up searches in tables.
Types:
Clustered Index – Alters physical order of table.
Non-clustered Index – Creates separate structure.

10. Stored Procedures & Functions

Stored Procedure – Precompiled SQL code executed with parameters.
Function – Returns a single value.

11. Normalization

Process of organizing data to avoid redundancy.
1NF – Atomic values.
2NF – No partial dependency.
3NF – No transitive dependency.
BCNF – Stronger version of 3NF.

12. Transactions & ACID Properties

Transaction – A sequence of operations treated as a single logical unit.
ACID:
Atomicity – All or nothing.
Consistency – Data remains valid.
Isolation – Transactions are independent.
Durability – Changes are permanent.

13. SQL Functions

Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
String Functions: UPPER(), LOWER(), CONCAT(), SUBSTRING()
Date Functions: NOW(), DATEADD(), DATEDIFF(), CURDATE()

14. Advanced SQL Concepts

Triggers – Execute automatically on events.
Subqueries – Query inside another query.
CTE (Common Table Expressions) – Temporary result set.
Window Functions – Perform calculations across sets (ROW_NUMBER(), RANK()).

15. Popular SQL Databases

MySQL – Open-source, widely used.
PostgreSQL – Advanced, supports complex queries.
Oracle DB – Enterprise-level.
MS SQL Server – Microsoft’s RDBMS.
SQLite – Lightweight, file-based.
✅ This is the complete SQL theory overview.