This article may contain affiliate links. If you buy some products using those links, I may receive monetary benefits. See affiliate disclosure here

As someone who has already spent some time with PHP-MySQL websites, I know a little bit of SQL - Structured Query Language. If you don't know, it is a language that allows you to interact with relational databases like MariaDB, MySQL, MS SQL, etc.

So, I wanted to dig a little deeper and deepen my knowledge. But where to start, and what all things should I learn. I wanted an idea. So I asked AI to come up with a list of contents sorted from beginner to advanced level.

Then from my part, I made some modifications to that list to come up with the following table of contents.

Table of Contents

Table of Contents for Learning SQL (Beginner to Advanced)

SQL comes in different flavours, like MySQL, PostgreSQL, MS SQL, and so on. The syntax between them varies slightly, but the basic concepts remain the same.

I mostly use MariaDB, which is an alternative to MySQL. So most of the things I discuss here and in the upcoming articles will be from a perspective of MariaDB. I hope that won't be much of an issue in learning SQL.

Basics of SQL

  1. What is a database & types of databases
  2. Introduction to Relational Databases
  3. Data Types in SQL
  4. Types of SQL commands: DDL, DQL, DML, DCL, TCL, Environment

Commands to Get You Started With SQL

  1. mysql in the command line
  2. Listing databases: SHOW DATABASES
  3. Creating a new database: CREATE DATABASE
  4. Setup users: CREATE USER, DROP USER, ALTER USER
  5. Granting and revoking permissions: GRANT, REVOKE
  6. Creating Tables: CREATE TABLE
  7. Modifying Tables: ALTER TABLE
  8. Dropping Tables and Databases: DROP TABLE, DROP DATABASE
  9. Constraints: Primary Keys, Foreign Keys, Unique, Not Null

Basic SQL Commands

  1. SQL Basic commands: SELECT, FROM, WHERE
  2. Sorting Results: ORDER BY
  3. Multiple conditions: AND, OR operators
  4. Limiting Results: LIMIT or FETCH FIRST
  5. Inserting Data: INSERT INTO
  6. Updating Data: UPDATE
  7. Deleting Data: DELETE

Intermediate SQL Commands

  1. Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, Self-Joins
  2. Aggregations: SUM, AVG, COUNT, MIN, MAX
  3. Grouping Results: GROUP BY
  4. Filtering Groups: HAVING
  5. Beginning a transaction: BEGIN
  6. Making changes effective: COMMIT
  7. Reverting changes: ROLLBACK

Advanced SQL

  1. Subqueries: Scalar, Correlated, EXISTS, and IN
  2. Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
  3. Set Operations: UNION, UNION ALL, INTERSECT, EXCEPT
  4. Recursive Queries: WITH RECURSIVE
  5. Stored Procedures
  6. Triggers
  7. Views
  8. User-Defined Functions (UDFs)
  9. Analytic Functions and Advanced Aggregates
  10. Using CASE for Conditional Logic
  11. Pivoting and Un-pivoting Data
  12. Query Optimization: Understanding Indexes and Query Plans
  13. Isolation Levels and Locking Mechanisms
  14. Constructing Dynamic SQL Queries
  15. Scripting with SQL
  16. Writing Readable and Maintainable SQL Code
  17. Debugging and Analyzing Performance

Next Steps

I already know most of the basic and intermediate SQL commands. But advanced features like transactions, stored procedures, etc are a bit alien to me. So I am looking forward to mastering them. Hope I can also record the progress in a YouTube series, which I will update here.