SQL Tutorial

SQL (Structured Query Language) Tutorials Roadmap

What is a Database?
- Understanding the concept of storing and organizing data.
- Types of databases (Relational, NoSQL - brief introduction).
What is a Relational Database Management System (RDBMS)?
- Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite.
- Understanding tables, rows, columns, and schemas.
What is SQL?
- A standard language for managing and manipulating relational databases.
- Used for querying, inserting, updating, and deleting data.
Why Learn SQL?
- Fundamental skill for data analysis, development, and database administration.
- Used in almost all applications that deal with data.
- High demand in the job market.
Setting up Your Environment:
- Installing a RDBMS (e.g., MySQL, PostgreSQL, or using online SQL platforms).
- Using a database client tool (e.g., DBeaver, SQL Developer, pgAdmin, MySQL Workbench).
Your First SQL Queries:
- Connecting to a database.
- Selecting data from a table (SELECT * FROM table_name;).
- Understanding semicolons.

Understanding DDL.
Creating Databases and Tables:
- CREATE DATABASE database_name;
- CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
- Specifying data types (INT, VARCHAR, DATE, DECIMAL, etc.).
Modifying Table Structures:
- ALTER TABLE table_name ADD column_name datatype;
- ALTER TABLE table_name DROP COLUMN column_name;
- ALTER TABLE table_name MODIFY COLUMN column_name datatype;
- ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
Dropping Databases and Tables:
- DROP DATABASE database_name;
- DROP TABLE table_name;
Understanding Constraints:
- NOT NULL.
- UNIQUE.
- PRIMARY KEY (Composite Primary Keys).
- FOREIGN KEY (establishing relationships between tables).
- DEFAULT.
- CHECK.
Indexes:
- Understanding indexes for performance.
- CREATE INDEX index_name ON table_name (column_name);
- DROP INDEX index_name ON table_name;

Understanding DML.
Inserting Data:
- INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- Inserting multiple rows.
- Inserting data from another table.
Updating Data:
- UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
- Understanding the importance of the WHERE clause.
Deleting Data:
- DELETE FROM table_name WHERE condition;
- Deleting all rows from a table (TRUNCATE TABLE table_name; vs. DELETE FROM table_name;).

The SELECT Statement:
- Selecting specific columns (SELECT column1, column2 FROM table_name;).
- Selecting all columns (SELECT * FROM table_name;).
Filtering Data with WHERE:
- Comparison operators (=, !=, >, <, >=, <=).
- Logical operators (AND, OR, NOT).
- Using IN, BETWEEN, LIKE (wildcard characters % and _).
- Checking for NULL values (IS NULL, IS NOT NULL).
Sorting Data with ORDER BY:
- Ascending (ASC) and descending (DESC) order.
- Sorting by multiple columns.
Limiting Results:
- LIMIT (MySQL, PostgreSQL).
- TOP (SQL Server).
- ROWNUM (Oracle).
Aliases:
- Column aliases (column_name AS alias_name).
- Table aliases (table_name AS alias_name).

Aggregate Functions:
- COUNT(), SUM(), AVG(), MIN(), MAX().
Grouping Data with GROUP BY:
- Grouping rows with the same values in specified columns.
- Using aggregate functions with GROUP BY.
Filtering Groups with HAVING:
- Understanding the difference between WHERE and HAVING.
- Filtering based on aggregate results.

Understanding Relationships Between Tables (Primary Key - Foreign Key).
Types of Joins:
- INNER JOIN: Returns rows when there is a match in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table.
- FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in one of the tables.
- CROSS JOIN: Returns the Cartesian product of the two tables.
- SELF JOIN: Joining a table to itself.
Joining on Multiple Columns.
Using USING and ON clauses.

Understanding Subqueries (Nested Queries):
- Subqueries in the WHERE clause.
- Subqueries in the FROM clause (Derived Tables).
- Subqueries in the SELECT clause (Scalar Subqueries).
Understanding Common Table Expressions (CTEs):
- WITH cte_name (column1, column2, ...) AS (SELECT ...).
- Creating temporary, named result sets.
- Improving readability and reusability of complex queries.
- Recursive CTEs (optional, more advanced).

String Functions:
- Concatenation, length, substring, upper/lower case.
Numeric Functions:
- Rounding, absolute value, mathematical operations.
Date and Time Functions:
- Extracting parts of dates, date arithmetic, formatting dates.
- Functions vary significantly between RDBMS.
Conditional Expressions:
- CASE statement.

Set Operations:
- UNION, UNION ALL, INTERSECT, EXCEPT (or MINUS).
Window Functions:
- ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), aggregate functions as window functions.
- Performing calculations across a set of table rows related to the current row.
Transactions:
- START TRANSACTION, COMMIT, ROLLBACK.
- Understanding ACID properties.
Views:
- CREATE VIEW view_name AS SELECT ...;
- Creating virtual tables based on the result of a query.
Stored Procedures and Functions (Database-specific).
Triggers (Database-specific).
Database Normalization (brief introduction).
Query Optimization Techniques.

Official Documentation for your chosen RDBMS.
Online Courses and Tutorials (Khan Academy, Codecademy, Udemy, Coursera, etc.).
Books on SQL and Database Design.
Participating in Community Forums (Stack Overflow, Reddit r/SQL).
Exploring different RDBMS systems.