====== Summary of Main SQL Concepts and Commands ======

SQL (Structured Query Language) is used to manage and manipulate relational databases. Below is a summary of the most essential SQL commands and concepts.

===== Data Querying =====
  * **[[sql:commands:select|SELECT]]** - Retrieves data from one or more tables. Example:
    ```
    SELECT name, age FROM users;
    ```
  * **[[sql:commands:where|WHERE]]** - Filters rows based on conditions. Example:
    ```
    SELECT * FROM users WHERE age > 18;
    ```
  * **[[sql:commands:distinct|DISTINCT]]** - Removes duplicate rows. Example:
    ```
    SELECT DISTINCT country FROM users;
    ```
  * **[[sql:commands:order_by|ORDER BY]]** - Sorts the result set. Example:
    ```
    SELECT * FROM users ORDER BY age DESC;
    ```

===== Data Filtering and Aggregation =====
  * **[[sql:commands:group_by|GROUP BY]]** - Groups rows with the same values. Example:
    ```
    SELECT country, COUNT(*) FROM users GROUP BY country;
    ```
  * **[[sql:commands:having|HAVING]]** - Filters grouped rows. Example:
    ```
    SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 10;
    ```
  * **[[sql:commands:limit|LIMIT]]** - Limits the number of rows returned. Example:
    ```
    SELECT * FROM users LIMIT 10;
    ```

===== Joins =====
Joins are used to combine rows from multiple tables based on related columns:
  * **[[sql:commands:inner_join|INNER JOIN]]** - Returns rows with matching values in both tables. Example:
    ```
    SELECT users.name, orders.amount
    FROM users
    INNER JOIN orders ON users.id = orders.user_id;
    ```
  * **[[sql:commands:left_join|LEFT JOIN]]** - Returns all rows from the left table and matched rows from the right. Example:
    ```
    SELECT users.name, orders.amount
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id;
    ```
  * **[[sql:commands:right_join|RIGHT JOIN]]** - Returns all rows from the right table and matched rows from the left.
  * **[[sql:commands:full_join|FULL JOIN]]** - Returns all rows when there is a match in either table.

===== Data Manipulation =====
  * **[[sql:commands:insert|INSERT]]** - Inserts new data into a table. Example:
    ```
    INSERT INTO users (name, age) VALUES ('John', 30);
    ```
  * **[[sql:commands:update|UPDATE]]** - Updates existing data. Example:
    ```
    UPDATE users SET age = 31 WHERE name = 'John';
    ```
  * **[[sql:commands:delete|DELETE]]** - Deletes data from a table. Example:
    ```
    DELETE FROM users WHERE age < 18;
    ```

===== Data Definition =====
  * **[[sql:commands:create_table|CREATE TABLE]]** - Creates a new table. Example:
    ```
    CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      age INT
    );
    ```
  * **[[sql:commands:alter_table|ALTER TABLE]]** - Modifies an existing table. Example:
    ```
    ALTER TABLE users ADD email VARCHAR(100);
    ```
  * **[[sql:commands:drop_table|DROP TABLE]]** - Deletes a table. Example:
    ```
    DROP TABLE users;
    ```

===== Constraints =====
  * **[[sql:constraints:primary_key|PRIMARY KEY]]** - Ensures unique and non-null values in a column.
  * **[[sql:constraints:foreign_key|FOREIGN KEY]]** - Establishes a relationship between two tables.
  * **[[sql:constraints:not_null|NOT NULL]]** - Ensures a column cannot have a null value.
  * **[[sql:constraints:unique|UNIQUE]]** - Ensures all values in a column are distinct.
  * **[[sql:constraints:check|CHECK]]** - Ensures values meet a specific condition. Example:
    ```
    CREATE TABLE users (
      age INT CHECK (age >= 18)
    );
    ```

===== Indexes =====
  * **[[sql:commands:create_index|CREATE INDEX]]** - Creates an index for faster queries. Example:
    ```
    CREATE INDEX idx_name ON users (name);
    ```
  * **[[sql:commands:drop_index|DROP INDEX]]** - Deletes an index.

===== Transactions =====
Transactions ensure data consistency:
  * **[[sql:commands:begin_transaction|BEGIN TRANSACTION]]** - Starts a transaction.
  * **[[sql:commands:commit|COMMIT]]** - Saves changes permanently.
  * **[[sql:commands:rollback|ROLLBACK]]** - Reverts changes made during a transaction.

===== Functions =====
SQL includes aggregate and scalar functions:
  * **[[sql:functions:count|COUNT()]]** - Counts the number of rows. Example:
    ```
    SELECT COUNT(*) FROM users;
    ```
  * **[[sql:functions:sum|SUM()]]** - Calculates the sum of a column. Example:
    ```
    SELECT SUM(amount) FROM orders;
    ```
  * **[[sql:functions:avg|AVG()]]** - Calculates the average value. Example:
    ```
    SELECT AVG(age) FROM users;
    ```
  * **[[sql:functions:max|min|MAX() / MIN()]]** - Returns the maximum or minimum value. Example:
    ```
    SELECT MAX(age) FROM users;
    ```

===== Subqueries =====
  * **[[sql:subqueries:definition|Subqueries]]** - Nested queries used within a larger query. Example:
    ```
    SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);
    ```

===== Views =====
  * **[[sql:commands:create_view|CREATE VIEW]]** - Creates a virtual table. Example:
    ```
    CREATE VIEW user_orders AS
    SELECT users.name, orders.amount
    FROM users
    JOIN orders ON users.id = orders.user_id;
    ```
  * **[[sql:commands:drop_view|DROP VIEW]]** - Deletes a view.

This summary covers the essential SQL commands for managing and querying databases. Expand each section for more detailed explanations and examples.