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.

• 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;
  ```

• 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 are used to combine rows from multiple tables based on related columns:

• 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.

• 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;
  ```

• 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;
  ```

• PRIMARY KEY - Ensures unique and non-null values in a column.
• FOREIGN KEY - Establishes a relationship between two tables.
• NOT NULL - Ensures a column cannot have a null value.
• UNIQUE - Ensures all values in a column are distinct.
• CHECK - Ensures values meet a specific condition. Example:

```

  CREATE TABLE users (
    age INT CHECK (age >= 18)
  );
  ```

• 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 ensure data consistency:

• BEGIN TRANSACTION - Starts a transaction.
• COMMIT - Saves changes permanently.
• ROLLBACK - Reverts changes made during a transaction.

SQL includes aggregate and scalar functions:

• 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 - Nested queries used within a larger query. Example:

```

  SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);
  ```

• 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.