SQL (Structured Query Language) is essential for managing and interacting with databases. If you want to understand how SQL works in a clear, concise way, this article breaks down key components you need to know based on the SQL mindmap above. Let’s dive into the important aspects of SQL without unnecessary jargon.

1. DML (Data Manipulation Language)

DML commands help you interact with the data inside your tables.

• INSERT: Adds new data to your table.
• UPDATE: Modifies existing data in your table.
• DELETE: Removes data from your table.
• SELECT: Retrieves data from the table (specific columns or the entire table using *).

2. DDL (Data Definition Language)

DDL is used to define or change the structure of your database objects.

• CREATE: Used to make new databases, tables, or views.
• ALTER: Modifies the structure of existing tables (e.g., adding or removing columns).
• TRUNCATE: Removes all rows from a table without deleting the table itself.
• DROP: Deletes tables or databases completely.

3. DCL (Data Control Language)

DCL manages access to the database.

• GRANT: Gives a user permission to access the database.
• REVOKE: Removes previously granted permissions.

4. TCL (Transaction Control Language)

TCL commands help manage changes made by DML commands, especially in cases where transactions are involved.

• COMMIT: Saves all the changes made to the database.
• ROLLBACK: Reverts any changes made during a transaction.
• SAVEPOINT: Sets a point in a transaction to which you can later roll back.

5. Functions

SQL provides several built-in functions to perform calculations on your data.

• AVG(): Finds the average value of a column.
• SUM(): Adds up all the values in a column.
• COUNT(): Counts the number of rows.
• MAX(): Returns the maximum value in a column.
• MIN(): Returns the minimum value in a column.

6. Joins

Joins are used to combine rows from two or more tables based on related columns.

• LEFT JOIN: Returns all records from the left table, and matched records from the right table.
• RIGHT JOIN: Returns all records from the right table, and matched records from the left table.
• INNER JOIN: Returns only the records that have matching values in both tables.
• FULL JOIN: Returns all records when there is a match in either left or right table.

7. GROUP BY and HAVING

• GROUP BY: Groups rows that have the same values into summary rows (e.g., to calculate SUM or AVG).
• HAVING: Acts like a WHERE clause but works with aggregate functions to filter results.

8. WHERE Clauses

The WHERE clause helps filter rows based on certain conditions. Some operators you can use in WHERE:

• =, <>, >, <, >=, <=: Comparison operators.
• AND, OR, NOT: Combine multiple conditions.
• BETWEEN: Finds values in a specific range.
• LIKE: Searches for a specified pattern in a column.
• IN: Checks if a value exists in a list of values.
• ALL, ANY, EXISTS: Advanced operators for specific conditions.

9. ORDER BY

• ORDER BY is used to sort your results in ascending (ASC) or descending (DESC) order by specific columns.

10. Window Functions

Window functions allow you to perform calculations across a set of rows related to the current row.

• ROW_NUMBER(): Assigns a unique number to rows within a partition.
• RANK(): Gives ranks to rows, leaving gaps for ties.
• DENSE_RANK(): Ranks rows without gaps in ranking.
• LEAD() and LAG(): Accesses data from the next or previous row in the result set.
• NTILE(): Divides rows into a specified number of groups.

11. Aliases

• AS: Used to temporarily rename a column or table for clarity.

This mindmap offers a visual summary of essential SQL concepts. Mastering these SQL elements will help you efficiently interact with and manage databases, whether you’re retrieving data, updating records, or setting permissions.