About SQL

1. Introduction to SQL

• SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to create, read, update, and delete data, often referred to by the acronym CRUD.

2. Core Components of SQL

SQL commands are categorized into different groups based on their function. The most important SQL commands include:

• DDL (Data Definition Language): Commands to define and modify the structure of database objects.
  • CREATE: To create new databases, tables, or other objects.
  • ALTER: To modify the structure of an existing database object.
  • DROP: To delete databases or tables.
  • TRUNCATE: To remove all rows from a table but retain the structure.
• DML (Data Manipulation Language): Commands to manipulate data within existing tables.
  • SELECT: To retrieve data from one or more tables.
  • INSERT: To insert new records into a table.
  • UPDATE: To modify existing data.
  • DELETE: To remove records from a table.
• DCL (Data Control Language): Commands related to access control.
  • GRANT: To give permissions to users.
  • REVOKE: To remove granted permissions.
• TCL (Transaction Control Language): Commands that deal with database transactions.
  • COMMIT: To save changes made during a transaction.
  • ROLLBACK: To undo changes made during a transaction.
  • SAVEPOINT: To create points within a transaction to which a rollback can be done.

3. Database and Table Operations

• Creating Databases and Tables:
  CREATE DATABASE database_name;
CREATE TABLE table_name (
column1 datatype KEY,
column2 datatype,
);

• Altering and Dropping Tables:
  ALTER TABLE table_name ADD column_name datatype;
DROP TABLE table_name;


4. CRUD Operations

• INSERT: Add new records to a table.
  INSERT INTO table_name (column1, column2) VALUES (value1, value2);

• SELECT: Retrieve records from one or more tables.
  SELECT column1, column2 FROM table_name WHERE condition;

• UPDATE: Modify existing records in a table.
  UPDATE table_name SET column1 value1 WHERE condition;

• DELETE: Remove records from a table.
  DELETE FROM table_name WHERE condition;


5. Filtering and Sorting Data

• WHERE Clause: To filter records based on specific conditions.
  SELECT * FROM table_name WHERE column = value;

• Logical Operators: AND, OR, NOT.
• Comparison Operators: =, >, <, >=, <=, <>.
• ORDER BY: Sort results in ascending (ASC) or descending (DESC) order.
  SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

• LIMIT: Restrict the number of rows returned.
  SELECT * FROM table_name LIMIT 10;


6. Joining Tables

• INNER JOIN: Retrieve records that have matching values in both tables.
  SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

• LEFT JOIN (LEFT OUTER JOIN): Retrieve all records from the left table and the matched records from the right.
  SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

• RIGHT JOIN and FULL JOIN are also available, providing variations in matching rows.

7. Aggregation Functions

• SQL supports several aggregation functions, such as:
  • COUNT(): Count rows.
  • SUM(): Sum values in a column.
  • AVG(): Calculate the average value.
  • MIN() / MAX(): Find the smallest or largest value.
  SELECT COUNT (*), AVG(column1), SUM(column2) FROM table_name;

• GROUP BY: Used with aggregation to group rows that have the same values in specified columns.
  SELECT COUNT(*) FROM table_name GROUP BY column1;

• HAVING: Filter groups after aggregation, often used with GROUP BY.
  SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*)>1;


8. Subqueries and Nested Queries

• Subqueries are queries nested inside another query.
  SELECT column1 FROM table_name WHERE column2 = (SELECT MAX (column2) FROM table_name);

• Can be used in the WHERE, FROM, or SELECT clause.

9. Constraints

• PRIMARY KEY: Uniquely identifies each record.
• FOREIGN KEY: Establishes a relationship between two tables.
• NOT NULL: Ensures that a column cannot have a null value.
• UNIQUE: Ensures all values in a column are different.
• CHECK: Ensures all values in a column satisfy a specific condition.

10. Views

• A view is a virtual table created from a query. It provides a way to encapsulate complex queries for easier reuse.
  CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

• Updating Views: Some views can be updatable, but it depends on the SQL query that defines them.

11. Indexes

• Indexes improve the speed of data retrieval but may slow down data insertion and updating.
  CREATE INDEX index_name ON table_name (column1);

• Types of indexes: Unique Index, Full-Text Index (used for searching large text fields), etc.

12. Transactions

• Transactions ensure that a series of SQL commands are executed as a unit.
• Commands: BEGIN, COMMIT, ROLLBACK.
  BEGIN;

  
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- If successful
-- or ROLLBACK; -- If an error occurs

• Properties: ACID (Atomicity, Consistency, Isolation, Durability).

13. Stored Procedures and Functions

• Stored Procedure: A set of SQL statements that can be saved and reused.
  CREATE PROCEDURE procedure_name (IN param1 INT)
BEGIN
UPDATE table_name SET column1 = column1 + param1;
END;

• Function: Similar to procedures but must return a value.
  CREATE FUNCTION function_name (param1 INT) RETURNS INT
BEGIN
RETURN param1 * 10;
END;


14. Triggers

• Triggers are procedures that are automatically executed in response to certain events on a table, such as INSERT, UPDATE, or DELETE.
  CREATE TRIGGER trigger_name AFTER INSERT ON table_name
FOR EACH ROW BEGIN
INSERT INTO audit_table (action) VALUES ('Insert');
END;


15. User and Security Management

• GRANT: Assign permissions to users.
  GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

• REVOKE: Remove permissions.
  REVOKE INSERT ON database_name.table_name FROM 'username'@'host';

• Roles and Privileges: Manage database security and control access.

16. Advanced SQL Concepts

• Window Functions: These perform calculations across a set of table rows related to the current row.
  SELECT column1, SUM(column2) OVER (PARTITION BY column3) FROM table_name;

• CTE (Common Table Expressions): Temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
  WITH cte_name AS (
SELECT column1 FROM table_name WHERE condition
)
SELECT * FROM cte_name;

• Recursive Queries: Used for hierarchical data, like organizational structures.
  WITH RECURSIVE cte_name AS (
SELECT column1, column2 FROM table_name WHERE condition
UNION
SELECT column1, column2 FROM table_name, cte_name WHERE join_condition
)
SELECT * FROM cte_name;


17. SQL Best Practices

• Normalization: Organize data to reduce redundancy (1NF, 2NF, 3NF, etc.).
• Use Indexes Wisely: Use them to speed up searches but balance the cost of slower writes.
• Avoid Using SELECT *: Specify columns for better performance and clarity.
• Backups and Recovery: Always maintain a backup plan for database recovery.

18. Popular SQL Dialects

• MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle each offer variations of SQL, with some proprietary extensions.

This comprehensive overview gives you the foundation and tools to interact with relational databases using SQL. You can use this knowledge to create, manage, and manipulate data, while also optimizing queries and ensuring.