SQL Commands Cheat Sheet

Okay, here’s a very detailed article (approximately 5000 words) covering an SQL Commands Cheat Sheet, broken down into logical sections and providing explanations, examples, and best practices.

SQL Commands Cheat Sheet: Your Comprehensive Guide

SQL (Structured Query Language) is the standard language for interacting with relational databases. Whether you’re a beginner database administrator, a data analyst, a software developer, or just someone who needs to query data, understanding SQL commands is crucial. This comprehensive cheat sheet provides a detailed breakdown of essential SQL commands, categorized for easy reference and understanding.

Table of Contents

1. Introduction to SQL and Relational Databases

   • What is SQL?
   • What are Relational Databases?
   • Key Concepts: Tables, Columns, Rows, Primary Keys, Foreign Keys
   • Database Management Systems (DBMS)

2. Data Definition Language (DDL) Commands

   • CREATE (Database, Table, Index, View, Stored Procedure, Function, Trigger)
     • CREATE DATABASE
     • CREATE TABLE (Data Types, Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT)
     • CREATE INDEX
     • CREATE VIEW
     • CREATE PROCEDURE
     • CREATE FUNCTION
     • CREATE TRIGGER
   • ALTER (Add, Modify, Drop Columns, Constraints)
     • ALTER TABLE
     • Adding a Column
     • Modifying a Column
     • Dropping a Column
     • Adding/Dropping Constraints
   • DROP (Database, Table, Index, View, Stored Procedure, Function, Trigger)
     • DROP DATABASE
     • DROP TABLE
     • DROP INDEX
     • DROP VIEW, etc.
   • TRUNCATE TABLE
   • RENAME TABLE (or ALTER TABLE ... RENAME TO ...)

3. Data Manipulation Language (DML) Commands

   • SELECT (Retrieving Data)
     • Basic SELECT Syntax
     • SELECT * (Selecting All Columns)
     • Selecting Specific Columns
     • WHERE Clause (Filtering Data)
       • Comparison Operators (=, !=, <, >, <=, >=)
       • Logical Operators (AND, OR, NOT)
       • BETWEEN
       • IN
       • LIKE (Pattern Matching)
       • IS NULL, IS NOT NULL
     • ORDER BY Clause (Sorting Data)
     • LIMIT / FETCH FIRST / TOP (Restricting Number of Rows)
     • DISTINCT (Selecting Unique Values)
     • Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
     • GROUP BY Clause (Grouping Data)
     • HAVING Clause (Filtering Grouped Data)
     • Aliases (Column and Table Aliases)
   • INSERT (Adding Data)
     • INSERT INTO
     • Inserting Single Rows
     • Inserting Multiple Rows
     • Inserting Data from Another Table
   • UPDATE (Modifying Data)
     • UPDATE ... SET ... WHERE
   • DELETE (Removing Data)
     • DELETE FROM ... WHERE

4. Data Control Language (DCL) Commands

   • GRANT (Granting Privileges)
   • REVOKE (Revoking Privileges)

5. Transaction Control Language (TCL) Commands

   • COMMIT (Saving Changes)
   • ROLLBACK (Undoing Changes)
   • SAVEPOINT (Creating Savepoints)
   • SET TRANSACTION

6. Joining Tables

   • INNER JOIN
   • LEFT JOIN (and LEFT OUTER JOIN)
   • RIGHT JOIN (and RIGHT OUTER JOIN)
   • FULL JOIN (and FULL OUTER JOIN)
   • CROSS JOIN
   • Self Joins

7. Subqueries

   • Subqueries in the SELECT Clause
   • Subqueries in the FROM Clause
   • Subqueries in the WHERE Clause
   • Correlated Subqueries
   • EXISTS and NOT EXISTS

8. Set Operations

   • UNION and UNION ALL
   • INTERSECT
   • EXCEPT (or MINUS)

9. Common SQL Functions

   • String Functions (CONCAT, SUBSTRING, LENGTH, UPPER, LOWER, TRIM, REPLACE)
   • Numeric Functions (ABS, ROUND, CEILING, FLOOR, MOD)
   • Date and Time Functions (NOW, CURDATE, CURTIME, DATE_ADD, DATE_SUB, DATEDIFF, YEAR, MONTH, DAY)
   • Conditional Expressions (CASE WHEN)
   • Conversion Functions (CAST, CONVERT)

10. Best Practices and Tips

    • Use Consistent Naming Conventions
    • Comment Your Code
    • Format Your SQL for Readability
    • Optimize Queries for Performance
    • Understand and Use Indexes
    • Avoid SELECT * in Production Code
    • Use Parameterized Queries (to prevent SQL injection)
    • Use transactions for data integrity.

---

1. Introduction to SQL and Relational Databases

• What is SQL?

  SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

• What are Relational Databases?

  A relational database is a type of database that stores and provides access to data points that are related to one another. Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points.

• Key Concepts:

  • Tables: The fundamental building blocks of a relational database. Data is organized into tables, which consist of rows and columns.
  • Columns (Attributes): Represent specific characteristics or attributes of the data (e.g., customer_id, name, email, order_date).
  • Rows (Records/Tuples): Represent individual instances or entries in the table (e.g., a specific customer, a specific order).
  • Primary Key: A column (or a combination of columns) that uniquely identifies each row in a table. It cannot contain NULL values, and there can only be one primary key per table. Ensures data integrity and is crucial for relationships between tables.
  • Foreign Key: A column (or a combination of columns) in one table that refers to the primary key of another table. Establishes relationships between tables (e.g., an order table might have a customer_id foreign key referencing the customer table’s primary key).

• Database Management Systems (DBMS):

  Software applications that allow users to create, manage, and interact with databases. Popular DBMSs include:
  * MySQL
  * PostgreSQL
  * Oracle Database
  * Microsoft SQL Server
  * SQLite
  * MariaDB (a fork of MySQL)

---

2. Data Definition Language (DDL) Commands

DDL commands are used to define and modify the structure of database objects. They deal with the “schema” of the database.

• CREATE

  The CREATE command is used to create new database objects.

  • CREATE DATABASE
sql
CREATE DATABASE MyDatabase;
    This creates a new database named “MyDatabase.” The specific options and syntax may vary slightly depending on the DBMS.

  • CREATE TABLE
    “`sql
    CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date DATE DEFAULT CURRENT_DATE,
    country_code VARCHAR(2) CHECK (country_code IN (‘US’, ‘CA’, ‘MX’))
    );

    CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    );
    ``
This creates two tables:CustomersandOrders. Let's break down the components:
*customer_id INT PRIMARY KEY: Creates a column namedcustomer_idof integer type, and designates it as the primary key.
*first_name VARCHAR(50) NOT NULL: Creates a string column (variable-length character) with a maximum length of 50 characters.NOT NULLensures that this column cannot be left empty.
*email VARCHAR(100) UNIQUE: Creates a string column, andUNIQUEensures that no two customers can have the same email address.
*registration_date DATE DEFAULT CURRENT_DATE: A date column.DEFAULT CURRENT_DATEautomatically sets the value to the current date when a new row is inserted if no value is provided.
*country_code VARCHAR(2) CHECK (country_code IN (‘US’, ‘CA’, ‘MX’)): A string column with aCHECKconstraint, ensuring that only 'US', 'CA', or 'MX' can be entered.
*FOREIGN KEY (customer_id) REFERENCES Customers(customer_id): In theOrderstable, this establishes a foreign key relationship. Thecustomer_idin theOrderstable must match acustomer_idin theCustomerstable. This ensures referential integrity.
* **Data Types:** Common data types include:
*INT,INTEGER: Integer numbers.
*BIGINT: Larger integer numbers
*SMALLINT: Smaller integer numbers
*FLOAT,REAL,DOUBLE: Floating-point numbers (numbers with decimal points).
*DECIMAL(precision, scale),NUMERIC(precision, scale): Fixed-point numbers (precise decimal values).precisionis the total number of digits, andscaleis the number of digits after the decimal point.
*VARCHAR(n): Variable-length character strings (up toncharacters).
*CHAR(n): Fixed-length character strings (exactlyncharacters, padded with spaces if necessary).
*TEXT: Large text strings.
*DATE: Date values (year, month, day).
*TIME: Time values (hour, minute, second).
*DATETIME,TIMESTAMP: Combined date and time values.
*BOOLEAN: True/False values (often represented as 1/0).
*BLOB`: Binary Large Object. Used to store binary data like images.

  • CREATE INDEX

    sql
CREATE INDEX idx_lastname ON Customers (last_name);
CREATE UNIQUE INDEX idx_email ON Customers (email);

    Indexes improve the speed of data retrieval. The first example creates a standard index on the last_name column. The second creates a unique index on the email column, which also enforces uniqueness (similar to the UNIQUE constraint). Indexes are crucial for performance, especially on large tables.

  • CREATE VIEW

    sql
CREATE VIEW US_Customers AS
SELECT customer_id, first_name, last_name, email
FROM Customers
WHERE country_code = 'US';

    A view is a virtual table based on the result of a SELECT query. It doesn’t store data itself; it’s a stored query. This example creates a view that shows only customers from the US. Views can simplify complex queries and provide a layer of abstraction.

  • CREATE PROCEDURE
    “`sql
    — Example in MySQL/MariaDB
    DELIMITER //
    CREATE PROCEDURE GetCustomerOrders (IN customerId INT)
    BEGIN
    SELECT *
    FROM Orders
    WHERE customer_id = customerId;
    END //
    DELIMITER ;

    — Call the stored procedure:
    CALL GetCustomerOrders(123);
    ``
Stored procedures are precompiled SQL code blocks that can be reused. They can accept input parameters (likecustomerIdabove) and return results. They improve code reusability, performance (because they are precompiled), and security. TheDELIMITERcommand is specific to MySQL/MariaDB and changes the statement delimiter to//so that the semicolon within the procedure body doesn't terminate theCREATE PROCEDURE` statement prematurely.

  • CREATE FUNCTION

    “`sql
    — Example in MySQL/MariaDB
    DELIMITER //
    CREATE FUNCTION CalculateTotalOrderAmount (customerId INT)
    RETURNS DECIMAL(10, 2)
    DETERMINISTIC
    BEGIN
    DECLARE totalAmount DECIMAL(10, 2);
    SELECT SUM(total_amount) INTO totalAmount
    FROM Orders
    WHERE customer_id = customerId;
    RETURN totalAmount;
    END //
    DELIMITER ;

    — Use the function:
    SELECT CalculateTotalOrderAmount(123);
    ``
Functions are similar to stored procedures, but they *must* return a single value. TheDETERMINISTIC` keyword indicates that the function will always return the same result for the same input, allowing the database to optimize its execution.

  • CREATE TRIGGER
sql
-- Example in MySQL
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
UPDATE Customers
SET last_order_date = NEW.order_date -- NEW refers to the newly inserted row
WHERE customer_id = NEW.customer_id;
END //
DELIMITER ;

    Triggers are special stored procedures that automatically execute in response to certain events on a table (like INSERT, UPDATE, DELETE). This example updates a last_order_date column in the Customers table whenever a new order is inserted. NEW refers to the new row being inserted, and OLD (in UPDATE and DELETE triggers) refers to the old row.

• ALTER

  The ALTER command is used to modify existing database objects.

  • ALTER TABLE

    • Adding a Column:

      sql
ALTER TABLE Customers
ADD COLUMN phone_number VARCHAR(20);

    • Modifying a Column:

      “`sql
      ALTER TABLE Customers
      MODIFY COLUMN email VARCHAR(255); — Change the maximum length

      — MySQL/MariaDB specific syntax:
      ALTER TABLE Customers
      CHANGE COLUMN old_column_name new_column_name VARCHAR(100); –Renames and modifies

      — Standard SQL
      ALTER TABLE Customers
      ALTER COLUMN email TYPE VARCHAR(255); –PostgreSQL, and some others
      “`
      The exact syntax for modifying a column’s data type can vary between DBMSs.

    • Dropping a Column:
      sql
ALTER TABLE Customers
DROP COLUMN phone_number;

    • Adding/Dropping Constraints:
      “`sql
      — Add a UNIQUE constraint:
      ALTER TABLE Customers
      ADD CONSTRAINT unique_email UNIQUE (email);

      — Drop a constraint (using the constraint name):
      ALTER TABLE Customers
      DROP CONSTRAINT unique_email;

      — Add a foreign key constraint:
      ALTER TABLE Orders
      ADD CONSTRAINT fk_customer
      FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
      ON DELETE CASCADE; — Optional: cascading delete

      — Drop a foreign key
      ALTER TABLE Orders
      DROP CONSTRAINT fk_customer;

      –Adding a NOT NULL constraint
      ALTER TABLE Customers
      ALTER COLUMN first_name SET NOT NULL; –PostgreSQL Syntax
      ``ON DELETE CASCADEis an important option for foreign keys. It means that if a row in the parent table (e.g.,Customers) is deleted, all corresponding rows in the child table (e.g.,Orders) will also be deleted. Other options includeON DELETE SET NULL(sets the foreign key to NULL) andON DELETE RESTRICT` (prevents deletion if there are related rows). The exact syntax for adding NOT NULL constraint might vary depending on your DBMS.

• DROP

  The DROP command is used to delete database objects. This is a permanent operation, so be careful!

  • DROP DATABASE
sql
DROP DATABASE MyDatabase;

  • DROP TABLE
sql
DROP TABLE Customers;

  • DROP INDEX
sql
DROP INDEX idx_lastname ON Customers;
-- Or, in some DBMS:
ALTER TABLE Customers DROP INDEX idx_lastname;
    The syntax can depend on the DBMS.

  • DROP VIEW, DROP PROCEDURE, DROP FUNCTION, DROP TRIGGER

    sql
DROP VIEW US_Customers;
DROP PROCEDURE GetCustomerOrders;
DROP FUNCTION CalculateTotalOrderAmount;
DROP TRIGGER after_order_insert;

• TRUNCATE TABLE

  sql
TRUNCATE TABLE Orders;

  TRUNCATE TABLE removes all rows from a table, but it keeps the table structure (columns, constraints, etc.). It’s generally faster than DELETE FROM Orders (without a WHERE clause) because it doesn’t log individual row deletions. It also resets auto-incrementing IDs. You cannot roll back a TRUNCATE operation.

• RENAME TABLE

  “`sql
  — Standard SQL:
  ALTER TABLE OldTableName RENAME TO NewTableName;

  — MySQL specific:
  RENAME TABLE OldTableName TO NewTableName;
  “`
  This renames an existing table.

---

3. Data Manipulation Language (DML) Commands

DML commands are used to manage data within the database objects (primarily tables).

• SELECT

  The SELECT statement is the workhorse of SQL. It’s used to retrieve data from one or more tables.

  • Basic SELECT Syntax:

    sql
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...
LIMIT count;

  • SELECT * (Selecting All Columns):

    sql
SELECT * FROM Customers;
    This retrieves all columns and all rows from the Customers table.

  • Selecting Specific Columns:

    sql
SELECT first_name, last_name, email FROM Customers;
    This retrieves only the specified columns.

  • WHERE Clause (Filtering Data):

    The WHERE clause specifies conditions that rows must meet to be included in the result set.

    • Comparison Operators:

      sql
SELECT * FROM Customers WHERE country_code = 'US'; -- Equal to
SELECT * FROM Orders WHERE total_amount > 100; -- Greater than
SELECT * FROM Products WHERE price <= 50; -- Less than or equal to
SELECT * FROM Employees WHERE department != 'Sales'; -- Not equal to (!= or <>)

    • Logical Operators (AND, OR, NOT):

      sql
SELECT * FROM Customers WHERE country_code = 'US' AND registration_date >= '2023-01-01';
SELECT * FROM Products WHERE category = 'Electronics' OR price < 20;
SELECT * FROM Employees WHERE NOT (department = 'Sales' OR department = 'Marketing');

    • BETWEEN:

      sql
SELECT * FROM Orders WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';
      Selects values within a given range (inclusive).

    • IN:

      sql
SELECT * FROM Customers WHERE country_code IN ('US', 'CA', 'MX');
      Checks if a value matches any value in a list.

    • LIKE (Pattern Matching):

      sql
SELECT * FROM Customers WHERE last_name LIKE 'S%'; -- Starts with 'S'
SELECT * FROM Customers WHERE email LIKE '%@example.com'; -- Ends with '@example.com'
SELECT * FROM Customers WHERE first_name LIKE '%oh%'; -- Contains 'oh'
SELECT * FROM Customers WHERE city LIKE '_ew York'; -- Matches 'New York', 'Dew York', etc.
% is a wildcard representing zero or more characters. _ is a wildcard representing exactly one character.

    • IS NULL, IS NOT NULL:

      sql
SELECT * FROM Customers WHERE phone_number IS NULL; -- Find customers with no phone number
SELECT * FROM Orders WHERE ship_date IS NOT NULL; -- Find orders that have been shipped
      Checks for NULL values (missing or unknown values).

  • ORDER BY Clause (Sorting Data):

    sql
SELECT * FROM Customers ORDER BY last_name ASC, first_name DESC;
    Sorts the result set by one or more columns. ASC (ascending) is the default; DESC (descending) sorts in reverse order.

  • LIMIT / FETCH FIRST / TOP (Restricting Number of Rows):
    The syntax for limiting result set varies slightly depending on the DBMS used.
    “`sql
    — MySQL, MariaDB, SQLite:
    SELECT * FROM Orders ORDER BY order_date DESC LIMIT 10; — Get the 10 most recent orders

    — PostgreSQL:
    SELECT * FROM Orders ORDER BY order_date DESC LIMIT 10;
    SELECT * FROM Orders ORDER BY order_date DESC FETCH FIRST 10 ROWS ONLY;

    — SQL Server:
    SELECT TOP 10 * FROM Orders ORDER BY order_date DESC;

    –Oracle
    SELECT * FROM Orders ORDER BY order_date DESC FETCH FIRST 10 ROWS ONLY;
    –Older Oracle versions
    SELECT * FROM (SELECT * FROM Orders ORDER BY order_date DESC) WHERE ROWNUM <= 10;
    “`
    These commands limit the number of rows returned by the query.

  • DISTINCT (Selecting Unique Values):

    sql
SELECT DISTINCT country_code FROM Customers;
    Returns only the unique values in the specified column(s).

  • Aggregate Functions (COUNT, SUM, AVG, MIN, MAX):

    sql
SELECT COUNT(*) FROM Customers; -- Count the total number of customers
SELECT SUM(total_amount) FROM Orders; -- Calculate the sum of all order amounts
SELECT AVG(price) FROM Products; -- Calculate the average product price
SELECT MIN(order_date) FROM Orders; -- Find the earliest order date
SELECT MAX(salary) FROM Employees; -- Find the highest salary
SELECT COUNT(DISTINCT customer_id) FROM Orders; -- Count distinct customers who placed order.
    Aggregate functions perform calculations on a set of values and return a single value.

  • GROUP BY Clause (Grouping Data):

    sql
SELECT country_code, COUNT(*) AS customer_count
FROM Customers
GROUP BY country_code;
    Groups rows with the same values in the specified column(s) and allows you to use aggregate functions on each group. Any non-aggregated columns in the SELECT list must be included in the GROUP BY clause.

  • HAVING Clause (Filtering Grouped Data):

    sql
SELECT country_code, COUNT(*) AS customer_count
FROM Customers
GROUP BY country_code
HAVING COUNT(*) > 10;
    The HAVING clause filters the results after the GROUP BY clause has been applied. It’s used with aggregate functions. You cannot use WHERE to filter aggregated results.

  • Aliases (Column and Table Aliases):

    “`sql
    SELECT first_name AS given_name, last_name AS surname
    FROM Customers AS c;

    SELECT c.first_name, o.order_date
    FROM Customers c — Table alias
    INNER JOIN Orders o ON c.customer_id = o.customer_id;
    “`
    Aliases provide temporary names for columns or tables, making queries more readable and concise, especially in joins.

• INSERT

  The INSERT statement adds new rows to a table.

  • INSERT INTO

    • Inserting Single Rows:

      sql
INSERT INTO Customers (customer_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', '[email protected]');
      This inserts a single row with the specified values. The order of the values must match the order of the columns listed. If you are inserting value into every column of the table, you don’t have to name the columns.

      sql
INSERT INTO Customers
VALUES (1, 'John', 'Doe', '[email protected]', '2023-10-27', 'US');

    • Inserting Multiple Rows:

      sql
INSERT INTO Customers (customer_id, first_name, last_name, email)
VALUES
(2, 'Jane', 'Smith', '[email protected]'),
(3, 'Peter', 'Jones', '[email protected]'),
(4, 'Mary', 'Brown', '[email protected]');
      This inserts multiple rows in a single statement.

    • Inserting Data from Another Table:

      sql
INSERT INTO US_Customers (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email
FROM Customers
WHERE country_code = 'US';
      This inserts data into the US_Customers table by selecting data from the Customers table.

• UPDATE

  The UPDATE statement modifies existing data in a table.

  • UPDATE ... SET ... WHERE

    “`sql
    UPDATE Customers
    SET email = ‘[email protected]’, phone_number = ‘123-456-7890’
    WHERE customer_id = 1;

    UPDATE Products
    SET price = price * 1.10; — Increase all prices by 10%
    ``
TheSETclause specifies the columns to update and their new values. TheWHEREclause specifies which rows to update. **Crucially, if you omit theWHERE` clause, all rows in the table will be updated!**

• DELETE

  The DELETE statement removes rows from a table.

  • DELETE FROM ... WHERE

    “`sql
    DELETE FROM Customers
    WHERE customer_id = 1;

    DELETE FROM Orders
    WHERE order_date < ‘2023-01-01’; — Delete orders older than a certain date
    ``
TheWHEREclause specifies which rows to delete. **If you omit theWHEREclause, *all* rows in the table will be deleted!** This is different fromTRUNCATE TABLEasDELETEcan be rolled back within a transaction and it fires triggers, whereasTRUNCATE TABLE` generally cannot be rolled back and does not fire triggers.

---

4. Data Control Language (DCL) Commands

DCL commands control access and permissions to the database.

• GRANT

  sql
GRANT SELECT, INSERT, UPDATE, DELETE ON Customers TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON MyDatabase.* TO 'admin'@'localhost';
GRANT SELECT ON Orders TO 'readonly_user'@'%'; -- '%' is a wildcard for any host
  The GRANT command gives specific privileges to users or roles. Common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, and ALL PRIVILEGES. You can grant privileges on specific tables, views, databases, or other database objects.

• REVOKE

  sql
REVOKE UPDATE, DELETE ON Customers FROM 'user1'@'localhost';
REVOKE ALL PRIVILEGES ON MyDatabase.* FROM 'admin'@'localhost';
  The REVOKE command removes previously granted privileges.

---

5. Transaction Control Language (TCL) Commands

TCL commands manage transactions within a database. Transactions are sequences of operations that are treated as a single unit of work.

• COMMIT

  sql
START TRANSACTION; -- Or BEGIN;
UPDATE Customers SET email = '[email protected]' WHERE customer_id = 1;
INSERT INTO Orders (order_id, customer_id, order_date) VALUES (100, 1, '2023-10-27');
COMMIT; -- Save all changes
COMMIT saves all changes made within a transaction to the database permanently.

• ROLLBACK

  sql
START TRANSACTION;
UPDATE Customers SET email = '[email protected]' WHERE customer_id = 1;
-- Oops, made a mistake!
ROLLBACK; -- Undo all changes since the start of the transaction
ROLLBACK undoes all changes made within a transaction since the last COMMIT or START TRANSACTION.

• SAVEPOINT

  sql
START TRANSACTION;
UPDATE Customers SET email = '[email protected]' WHERE customer_id = 1;
SAVEPOINT update1;
UPDATE Customers SET email = '[email protected]' WHERE customer_id = 2;
SAVEPOINT update2;
ROLLBACK TO update1; -- Rollback to the first savepoint
COMMIT; -- Commits only the first update.
SAVEPOINT creates a named point within a transaction to which you can later roll back. This allows for partial rollbacks within a larger transaction.

• SET TRANSACTION

  “`sql
  SET TRANSACTION READ ONLY;
  — Perform SELECT queries
  — Any attempt to modify data will result in an error

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  ``
This command can change the transaction isolation level or access mode.
*READ ONLY: Prevents any modifications within the transaction.
*READ WRITE: Allow both read and write (default).
* **Isolation Levels**: Control the degree to which concurrent transactions are isolated from each other. Common levels include:
*READ UNCOMMITTED: The lowest isolation level. Allows dirty reads (reading uncommitted data).
*READ COMMITTED: Prevents dirty reads, but allows non-repeatable reads (reading the same row multiple times within a transaction and getting different values if another transaction modifies it).
*REPEATABLE READ: Prevents dirty reads and non-repeatable reads, but allows phantom reads (seeing new rows appear if another transaction inserts data).
*SERIALIZABLE`: The highest isolation level. Prevents all concurrency issues (dirty reads, non-repeatable reads, phantom reads) by effectively locking the data.

---

6. Joining Tables

Joins are used to combine data from multiple tables based on related columns.

• INNER JOIN

  sql
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;
  Returns only rows where there is a match in both tables based on the join condition (c.customer_id = o.customer_id). If a customer has no orders, they won’t appear in the result. If an order has no matching customer, it won’t appear.

• LEFT JOIN (and LEFT OUTER JOIN)

  sql
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
  Returns all rows from the left table (Customers) and the matching rows from the right table (Orders). If there’s no match in the right table, NULL values are returned for the right table’s columns. This will include all customers, even those who haven’t placed any orders.

• RIGHT JOIN (and RIGHT OUTER JOIN)

  sql
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;
  Returns all rows from the right table (Orders) and the matching rows from the left table (Customers). If there’s no match in the left table, NULL values are returned for the left table’s columns. This will include all orders, even those with invalid customer IDs.

• FULL JOIN (and FULL OUTER JOIN)

  sql
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM Customers c
FULL OUTER JOIN Orders o ON c.customer_id = o.customer_id;
  Returns all rows from both tables. If there’s no match in one of the tables, NULL values are returned for that table’s columns. This includes all customers and all orders, regardless of whether they have matches. Note: MySQL does not directly support FULL OUTER JOIN. You can achieve the same result using a combination of LEFT JOIN and UNION.

• CROSS JOIN

  sql
SELECT c.first_name, p.product_name
FROM Customers c
CROSS JOIN Products p;
  Returns the Cartesian product of the two tables – every possible combination of rows from both tables. If Customers has 100 rows and Products