Prepare statement

 -- Step 1: Prepare an INSERT statement to insert a user into the users table

PREPARE insert_stmt FROM 'INSERT INTO users (name, email) VALUES (?, ?)';

-- Step 2: Set values for placeholders

SET @name = 'John Doe', @email = 'john.doe@example.com';

-- Step 3: Execute the prepared INSERT statement with the provided values

EXECUTE insert_stmt USING @name, @email;

-- Step 4: Deallocate the prepared INSERT statement

DEALLOCATE PREPARE insert_stmt;

-- Step 5: Prepare a SELECT statement to retrieve user by ID

PREPARE select_stmt FROM 'SELECT id, name, email FROM users WHERE id = ?';

-- Step 6: Set the value for the ID of the user to retrieve

SET @id = 1;

-- Step 7: Execute the SELECT statement with the ID

EXECUTE select_stmt USING @id;

-- Step 8: Deallocate the prepared SELECT statement

DEALLOCATE PREPARE select_stmt;

MySQL Transactions

 In MySQL, transactions allow you to group multiple SQL statements into a single unit of work. This ensures that the changes made by those statements are executed together, and if something goes wrong, the transaction can be rolled back to maintain data integrity. Transactions are especially useful when you need to perform a series of operations that should either all succeed or all fail.

Basic Commands for MySQL Transactions

1. Start a Transaction
   To start a transaction, you use the START TRANSACTION or BEGIN command.

START TRANSACTION;

2 Commit a Transaction
To save all the changes made during the transaction, you use the COMMIT command. Once a transaction is committed, the changes become permanent.

COMMIT;

3 Rollback a Transaction
If something goes wrong during the transaction (e.g., a constraint violation), you can use the ROLLBACK command to undo all the changes made since the transaction started.

ROLLBACK;

4 Savepoints
A savepoint allows you to create a point within a transaction to which you can roll back without affecting the entire transaction.

SAVEPOINT savepoint_name;

If you want to roll back to a specific savepoint:

ROLLBACK TO SAVEPOINT savepoint_name;

5 Autocommit Mode
By default, MySQL runs in autocommit mode, meaning every SQL statement is treated as a transaction. If you want to disable autocommit for your session (so you can manually manage transactions), use the following command:

SET autocommit = 0;

To re-enable autocommit, use:

SET autocommit = 1;

Example of Using Transactions in MySQL

START TRANSACTION;

-- Insert data into a table

INSERT INTO accounts (account_id, balance) VALUES (1, 500);

INSERT INTO accounts (account_id, balance) VALUES (2, 300);

-- Simulate an error (e.g., a constraint violation)

-- For example, inserting an invalid value

-- INSERT INTO accounts (account_id, balance) VALUES (NULL, 'invalid_balance');

-- If no error occurs, commit the transaction

COMMIT;

In the above example:

1. We start a transaction with START TRANSACTION.
2. We perform some INSERT operations.
3. If everything is successful, we use COMMIT to save the changes to the database.
4. If an error occurs during the transaction (e.g., inserting invalid data), we could roll back the transaction with ROLLBACK to ensure the database stays in a consistent state.

Isolation Levels in MySQL

MySQL supports different transaction isolation levels, which control the visibility of changes made within a transaction to other transactions. The isolation level can be set as follows:

Here are the available isolation levels:

1. READ UNCOMMITTED: Transactions can see uncommitted changes made by other transactions.
2. READ COMMITTED: Transactions can only see committed changes from other transactions.
3. REPEATABLE READ: Ensures that if a transaction reads a value, it will see the same value if it reads it again later. This is the default isolation level in MySQL.
4. SERIALIZABLE: The highest isolation level. It ensures no other transactions can read, write, or even lock the same rows.

Example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Key Points to Remember

• Atomicity: A transaction ensures that all the changes are applied or none at all. If a transaction is rolled back, none of the operations will take effect.
• Consistency: Transactions bring the database from one valid state to another, ensuring integrity.
• Isolation: Transactions are isolated from each other. Depending on the isolation level, the changes in one transaction may or may not be visible to others.
• Durability: Once a transaction is committed, the changes are permanent, even if the system crashes.

Conclusion

Transactions in MySQL are essential for maintaining data integrity and consistency, especially in systems where multiple operations need to be executed together. By using START TRANSACTION, COMMIT, and ROLLBACK, you can ensure that your database operations are reliable and robust.

MYSQL CASE

 mysql> select * from orders;

+----------+-----------+------------+------------+----------+

| order_id | client_id | product_id | order_date | quantity |

+----------+-----------+------------+------------+----------+

|        1 |         1 |          1 | 2023-05-01 |        2 |

|        2 |         1 |          2 | 2023-05-03 |        1 |

|        4 |         3 |          1 | 2023-07-20 |        1 |

+----------+-----------+------------+------------+----------+

3 rows in set (0.00 sec)

mysql> 

mysql> 

mysql> 

mysql> SELECT 

    ->     order_id,

    ->     client_id,

    ->     product_id,

    ->     order_date,

    ->     quantity,

    ->     CASE 

    ->         WHEN quantity = 1 THEN 'Single'

    ->         WHEN quantity BETWEEN 2 AND 5 THEN 'Small Order'

    ->         WHEN quantity BETWEEN 6 AND 10 THEN 'Medium Order'

    ->         ELSE 'Large Order'

    ->     END AS order_category

    -> FROM 

    ->     orders;

+----------+-----------+------------+------------+----------+----------------+

| order_id | client_id | product_id | order_date | quantity | order_category |

+----------+-----------+------------+------------+----------+----------------+

|        1 |         1 |          1 | 2023-05-01 |        2 | Small Order    |

|        2 |         1 |          2 | 2023-05-03 |        1 | Single         |

|        4 |         3 |          1 | 2023-07-20 |        1 | Single         |

+----------+-----------+------------+------------+----------+----------------+

3 rows in set (0.00 sec)

mysql> 

Foreign Key

 

1. What is a Foreign Key?

A FOREIGN KEY in SQL is a constraint that creates a relationship between two tables. It links a column or group of columns in one table (child table) to a primary key or unique key in another table (parent table). This ensures referential integrity, meaning that any value in the foreign key column must exist in the primary key column of the parent table.

2. Creating Foreign Keys

Foreign keys can be defined:

• During table creation with the CREATE TABLE statement.
• After table creation with the ALTER TABLE statement.

2.1. Defining a Foreign Key in CREATE TABLE

Here’s the syntax for adding a foreign key while creating a table:

CREATE TABLE child_table (

    child_column datatype,

    ...

    FOREIGN KEY (child_column) REFERENCES parent_table(parent_column)

);

CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    name VARCHAR(100)

);

Example

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    order_date DATE,

    customer_id INT,

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

In this example, customer_id in the orders table is a foreign key that references customer_id in the customers table.

2.2. Adding a Foreign Key with ALTER TABLE

To add a foreign key after creating a table, use the ALTER TABLE statement:

sql
ALTER TABLE child_table
ADD FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);

Example

sql
ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

3. Naming Foreign Key Constraints

You can give a foreign key constraint a specific name using the CONSTRAINT keyword. This makes it easier to reference and manage the constraint later.

sql
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

In this example, the foreign key is named fk_customer.

4. Cascade Options for Foreign Keys

Foreign key constraints can define actions to take when a referenced row in the parent table is updated or deleted. Common cascade options include:

• ON DELETE CASCADE: Deletes all rows in the child table that reference the deleted row in the parent table.
• ALTER TABLE orders ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE CASCADE;
• 
  
• ON UPDATE CASCADE: Updates the foreign key in the child table if the referenced key in the parent table is updated.

ALTER TABLE orders 

ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) 

ON UPDATE CASCADE;

• ON DELETE SET NULL: Sets the foreign key to NULL in the child table when the referenced row in the parent table is deleted.

ALTER TABLE orders ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE SET NULL;

• ON DELETE RESTRICT: Prevents the deletion of a row in the parent table if it is referenced in the child table.
• ALTER TABLE orders ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE RESTRICT;

Example with Cascade Options

sql
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

In this example:

• If a customer_id in customers is deleted, all related orders will also be deleted (ON DELETE CASCADE).
• If customer_id in customers is updated, the customer_id in orders is also updated (ON UPDATE CASCADE).

5. Managing Foreign Keys

Managing foreign keys includes dropping, disabling, and checking for any constraint violations.

5.1 Dropping a Foreign Key

To remove a foreign key constraint, use the DROP command with ALTER TABLE:

sql
ALTER TABLE child_table
DROP FOREIGN KEY constraint_name;

Example

sql
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

5.2 Disabling and Enabling Foreign Key Checks

In some databases (like MySQL), you can temporarily disable foreign key checks, which may be helpful for bulk inserts or data migrations.

• Disable foreign key checks:

  sql
  SET foreign_key_checks = 0;
  

• Enable foreign key checks:

  sql
  SET foreign_key_checks = 1;
  

6. Foreign Key Best Practices

• Use Descriptive Names: Name constraints for easier management.
• Avoid Cascading Deletes in critical tables to prevent accidental data loss.
• Ensure Data Consistency: Insert data in the parent table before inserting in the child table to avoid foreign key violations.
• Indexes on Foreign Keys: In many databases, it’s recommended to index foreign key columns for better query performance.

7. Common Errors and Troubleshooting

• Error: "Cannot add or update a child row: a foreign key constraint fails."

  • Cause: This occurs if you attempt to add a foreign key value in the child table that doesn’t exist in the parent table.
  • Solution: Ensure that the referenced key exists in the parent table.

• Error: "Cannot delete or update a parent row: a foreign key constraint fails."

  • Cause: This occurs if you attempt to delete or update a row in the parent table that has dependent rows in the child table.
  • Solution: Use ON DELETE CASCADE or manually delete the dependent rows first.

8. Advanced Example: Multiple Foreign Keys

You can create tables with multiple foreign keys, referencing different tables.

sql
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE SET NULL
);

In this example:

• order_id references orders, and if an order is deleted, the related order_items entries are also deleted.
• product_id references products, and if a product is deleted, product_id is set to NULL in order_items.

Get the list of tables in your database:

1)  SHOW TABLES;


2)  SHOW CREATE TABLE table_name;







Summary

• Foreign keys maintain relationships and referential integrity between tables.
• Cascade options (ON DELETE, ON UPDATE) allow flexibility in handling parent-child table relationships.
• Naming constraints and indexing foreign key columns can improve readability and performance.
• Disable foreign key checks temporarily when performing bulk operations if necessary.

With this guide, you should be well-equipped to define and manage foreign keys effectively in SQL databases!

INNER JOINS

 mysql> select * from sales;

+----+--------------+-------------+

| id | product_name | sale_amount |

+----+--------------+-------------+

|  1 | Product A    |      100.50 |

|  2 | Product B    |       75.25 |

|  3 | Product A    |      120.75 |

|  4 | Product C    |       50.00 |

|  5 | Product B    |       90.80 |

|  6 | Product R    |      300.00 |

+----+--------------+-------------+

6 rows in set (0.00 sec)

mysql> select * from clients;

+-----------+-------------+-------------------+

| client_id | client_name | registration_date |

+-----------+-------------+-------------------+

|         1 | Alice       | 2023-01-10        |

|         2 | Bob         | 2023-02-15        |

|         3 | Charlie     | 2023-03-20        |

|         4 | Ambiorix    | 2024-10-06        |

|         5 | Many        | 2024-10-11        |

+-----------+-------------+-------------------+

5 rows in set (0.00 sec)

mysql> select c.client_name,c.client_id,s.id,s.product_name from clients as c INNER JOIN sales as s on s.id=c.client_id;

+-------------+-----------+----+--------------+

| client_name | client_id | id | product_name |

+-------------+-----------+----+--------------+

| Alice       |         1 |  1 | Product A    |

| Bob         |         2 |  2 | Product B    |

| Charlie     |         3 |  3 | Product A    |

| Ambiorix    |         4 |  4 | Product C    |

| Many        |         5 |  5 | Product B    |

+-------------+-----------+----+--------------+

5 rows in set (0.00 sec)

mysql> select c.client_name,c.client_id,s.id,s.product_name from clients as c LEFT JOIN sales as s on s.id=c.client_id;

+-------------+-----------+------+--------------+

| client_name | client_id | id   | product_name |

+-------------+-----------+------+--------------+

| Alice       |         1 |    1 | Product A    |

| Bob         |         2 |    2 | Product B    |

| Charlie     |         3 |    3 | Product A    |

| Ambiorix    |         4 |    4 | Product C    |

| Many        |         5 |    5 | Product B    |

+-------------+-----------+------+--------------+

5 rows in set (0.00 sec)

mysql> select c.client_name,c.client_id,s.id,s.product_name from clients as c RIGHT JOIN sales as s on s.id=c.client_id;

+-------------+-----------+----+--------------+

| client_name | client_id | id | product_name |

+-------------+-----------+----+--------------+

| Alice       |         1 |  1 | Product A    |

| Bob         |         2 |  2 | Product B    |

| Charlie     |         3 |  3 | Product A    |

| Ambiorix    |         4 |  4 | Product C    |

| Many        |         5 |  5 | Product B    |

| NULL        |      NULL |  6 | Product R    |

+-------------+-----------+----+--------------+

Alter table

https://dev.mysql.com/doc/refman/8.4/en/alter-table.html

 https://dev.mysql.com/doc/refman/8.4/en/alter-table-examples.html

inner and left join 3 tables

 $query_ivr = "

    SELECT

        c.cliente_id,

        c.cliente_nombre,

        c.cliente_telefono,

        c.cliente_blacklist_status,

        SUM(co.compra_precio) AS total_compra,

        ivr.ivr_num_src,

        ivr.ivr_num_dst,

        ivr.ivr_caller_channel,

        ivr.ivr_call_status,

        ivr.ivr_calldate,

        b.balance_creditos

    FROM

        clientes AS c

    INNER JOIN

        compras AS co ON c.cliente_id = co.compra_cliente_id

    INNER JOIN

        ivr_calls AS ivr ON ivr.ivr_num_src = c.cliente_telefono AND ivr.ivr_call_status = 1

    LEFT JOIN

        balance AS b ON b.balance_cliente = c.cliente_id

    GROUP BY

        c.cliente_id,

        c.cliente_nombre,

        c.cliente_telefono,

        c.cliente_blacklist_status,

        ivr.ivr_num_src,

        ivr.ivr_num_dst,

        ivr.ivr_caller_channel,

        ivr.ivr_call_status,

        ivr.ivr_calldate,

        b.balance_creditos;";

1. Added a LEFT JOIN:

   • The balance table is joined with LEFT JOIN to ensure that even if there is no corresponding entry in the balance table, you still get results from the other tables.

2. Selected b.balance_creditos:

   • This column is included in the SELECT statement to retrieve the credit balance for each client.

Explanation:

• Using a LEFT JOIN allows you to fetch client information even if they do not have any associated balance records.