SQL Query Concepts with Examples

❓ What is SQL?

SQL (Structured Query Language) is the standard language used to store, retrieve, and manage data in relational databases.

💡 Why is SQL Important?

SQL is widely used across different industries for:

• 📊 Data Analysis – Extracting insights from large datasets.
• 🛒 E-commerce – Managing customers, orders, and inventory.
• 📈 Business Intelligence – Generating reports and dashboards.
• 💻 Software Development – Building database-driven applications.

🗄️ Which Databases Use SQL?

SQL is used in many popular database systems, including:

• ✅ MySQL – Popular for websites and web apps.
• ✅ PostgreSQL – Open-source and feature-rich.
• ✅ SQL Server – Used in enterprise applications.
• ✅ Oracle – Handles large-scale databases.
• ✅ SQLite – Lightweight and used in mobile apps.

🛠 What Can You Do with SQL?

SQL allows you to perform many database operations, including:

• 🔍 Retrieve Data – Find records using SELECT.
• ✏️ Modify Data – Add, update, or delete records ( INSERT, UPDATE, DELETE).
• 📌 Define Structures – Create tables and relationships ( CREATE, ALTER, DROP).
• 🔐 Control Access – Manage user permissions ( GRANT, REVOKE).

📌 Example: A Simple SQL Query

Suppose a company wants to find all customers from New York:

SELECT * FROM Customers WHERE city = 'New York';

🎯 What’s Next?

Now that you know what SQL is and why it’s useful, let’s move to the next step: Understanding SQL Syntax & Keywords

📝 Understanding SQL Syntax

SQL (Structured Query Language) uses simple, readable statements to interact with databases.

💡 SQL Commands: What Can You Do with SQL?

SQL consists of different commandsto manage data efficiently. Here are the most commonly used ones:

📌 Data Retrieval

• 🔍 SELECT – Retrieves data from tables.

📌 Data Manipulation

• ➕ INSERT INTO – Adds new records.
• ✏️ UPDATE – Modifies existing records.
• 🗑️ DELETE – Removes records.

📌 Database & Table Management

• 📁 CREATE TABLE – Defines a new table.
• 🔧 ALTER TABLE – Modifies an existing table.
• 🚮 DROP TABLE – Deletes a table.

📌 Example: Retrieving All Employees

The following query fetches all records from the Employeestable:

SELECT * FROM Employees;

🚀 What’s Next?

Now that you know the basic SQL commands, let’s move on to the next step: Understanding Databases & Tables

📌 What is a Database?

A database is an organized collection of data that allows easy storage, retrieval, and management.

📂 What is a Table?

In SQL, data is stored in tables, which are like spreadsheets. Each table consists of:

• 📌 Columns (Fields) – Define the type of data stored (e.g., Name, Age, City).
• 📌 Rows (Records) – Contain actual data entries.

🔧 Example: Creating a “Customers” Table

Here’s how you define a table in SQL:

CREATE TABLE Customers (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50)
);

💡 Real-World Use Cases

• 📌 Storing user profiles in a web application.
• 📌 Managing product catalogs in an e-commerce store.
• 📌 Tracking sales and orders for a business.

🚀 What’s Next?

Now that you understand databases and tables, let’s move on to the next step: Writing Basic Queries

The SELECT statement retrieves data from tables.

Understanding the SELECT Statement

The basic syntax of the SELECT statement:

SELECT column1, column2 FROM table_name;

Where:

• column1, column2: The specific columns to retrieve.
• table_name: The name of the table to fetch data from.

1️⃣ Retrieving Specific Columns

Example:

SELECT name, age FROM Customers;

2️⃣ Retrieving All Columns

To retrieve all columns:

SELECT * FROM Customers;

3️⃣ Renaming Columns in Output (AS Keyword)

Use the AS keyword to rename columns:

SELECT name AS Customer_Name, age AS Customer_Age FROM Customers;

4️⃣ Adding Computed Columns in the Query

Perform calculations within a query:

SELECT name, age, age + 5 AS Age_In_5_Years FROM Customers;

5️⃣ Removing Duplicate Results (DISTINCT Keyword)

To fetch unique cities from the Customers table:

SELECT DISTINCT city FROM Customers;

6️⃣ Sorting Results (ORDER BY Clause)

Sort results in ascending order (default):

SELECT * FROM Customers ORDER BY age;

Sort in descending order:

SELECT * FROM Customers ORDER BY age DESC;

7️⃣ Limiting the Number of Results

Fetch only 2 records:

SELECT * FROM Customers LIMIT 2;

💡 Real-World Scenario

Imagine an Employee table:

| emp_id | emp_name  | department | salary |
|--------|-----------|------------|--------|
| 101    | John Doe  | HR         | 50000  |
| 102    | Jane Roe  | IT         | 70000  |
| 103    | Mark Smith| Finance    | 60000  |

Fetch the names and salaries of employees:

SELECT emp_name, salary FROM Employee;

Fetch employees with salaries greater than 60,000:

SELECT * FROM Employee WHERE salary > 60000;

🔹 Next Steps

Now that you know how to write basic SELECT queries, the next step is to filter results using WHERE conditions, which we’ll cover in the next lesson. 🚀

The WHERE clause is used to filter data and retrieve only the rows that meet specific conditions.

Understanding the WHERE Clause

The basic syntax of the WHERE clause:

SELECT column1, column2 FROM table_name WHERE condition;

Where:

• column1, column2: The specific columns to retrieve.
• table_name: The table from which data is fetched.
• condition: The filter that determines which rows are returned.

1️⃣ Filtering Data Based on a Single Condition

Retrieve customers older than 30:

SELECT * FROM Customers WHERE age > 30;

2️⃣ Using Multiple Conditions (AND & OR)

Retrieve customers older than 25 and living in New York:

SELECT * FROM Customers WHERE age > 25 AND city = 'New York';

Retrieve customers who are either older than 25 or live in New York:

SELECT * FROM Customers WHERE age > 25 OR city = 'New York';

3️⃣ Filtering Using Equality & Inequality Operators

Retrieve customers from a specific city:

SELECT * FROM Customers WHERE city = 'Los Angeles';

Retrieve customers who are not from Los Angeles:

SELECT * FROM Customers WHERE city <> 'Los Angeles';

4️⃣ Using the BETWEEN Operator

Retrieve customers aged between 25 and 40:

SELECT * FROM Customers WHERE age BETWEEN 25 AND 40;

5️⃣ Filtering Data Using the IN Operator

Retrieve customers from specific cities:

SELECT * FROM Customers WHERE city IN ('New York', 'Chicago', 'San Francisco');

6️⃣ Filtering Text with LIKE (Pattern Matching)

Retrieve customers whose names start with ‘J’:

SELECT * FROM Customers WHERE name LIKE 'J%';

Retrieve customers whose names contain ‘an’:

SELECT * FROM Customers WHERE name LIKE '%an%';

7️⃣ Handling NULL Values (IS NULL / IS NOT NULL)

Retrieve customers whose phone number is missing:

SELECT * FROM Customers WHERE phone IS NULL;

Retrieve customers who have a phone number:

SELECT * FROM Customers WHERE phone IS NOT NULL;

💡 Real-World Scenario

Consider an Orders table:

| order_id | customer_name | product     | amount | status   |
|----------|---------------|-------------|--------|----------|
| 1        | Alice         | Laptop      | 1200   | Shipped  |
| 2        | Bob           | Phone       | 800    | Pending  |
| 3        | Charlie       | Headphones  | 150    | Shipped  |
| 4        | David         | Tablet      | 600    | Canceled |

Retrieve all shipped orders:

SELECT * FROM Orders WHERE status = 'Shipped';

Retrieve orders with an amount greater than 500 but less than 1000:

SELECT * FROM Orders WHERE amount BETWEEN 500 AND 1000;

🔹 Next Steps

Now that you know how to filter data using the WHERE clause, the next step is learning how to aggregate data using GROUP BY and HAVING. 🚀

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, typically used with aggregate functions.

🔹 Understanding GROUP BY

The GROUP BY clause groups data based on one or more columns. It is commonly used with aggregate functions like:

• COUNT() – Counts the number of rows
• SUM() – Calculates the total sum
• AVG() – Finds the average value
• MAX() – Gets the highest value
• MIN() – Gets the lowest value

1️⃣ Basic GROUP BY Example

Let’s say we have a Sales table:

| id | customer   | product  | amount |
|----|------------|----------|--------|
| 1  | Alice      | Laptop   | 1200   |
| 2  | Bob        | Phone    | 800    |
| 3  | Alice      | Mouse    | 50     |
| 4  | Bob        | Laptop   | 1500   |
| 5  | Charlie    | Phone    | 900    |

To find the total sales amount per customer:

SELECT customer, SUM(amount) AS total_spent FROM Sales GROUP BY customer;

2️⃣ Counting the Number of Orders per Product

To see how many times each product was ordered:

SELECT product, COUNT(*) AS order_count FROM Sales GROUP BY product;

3️⃣ Using GROUP BY with Multiple Columns

To find the total amount spent per customer, per product:

SELECT customer, product, SUM(amount) AS total_spent FROM Sales GROUP BY customer, product;

🔹 Filtering Grouped Data with HAVING

The HAVING clause filters groups based on aggregate values (since WHERE cannot be used with aggregate functions).

4️⃣ Filtering Groups with HAVING

Find customers who have spent more than $1000:

SELECT customer, SUM(amount) AS total_spent FROM Sales GROUP BY customer HAVING total_spent > 1000;

Find products that have been ordered more than once:

SELECT product, COUNT(*) AS order_count FROM Sales GROUP BY product HAVING order_count > 1;

💡 Real-World Scenario

Consider an Orders table:

| order_id | customer_name | product  | amount | status   |
|----------|---------------|----------|--------|----------|
| 1        | Alice         | Laptop   | 1200   | Shipped  |
| 2        | Bob           | Phone    | 800    | Pending  |
| 3        | Alice         | Mouse    | 50     | Shipped  |
| 4        | Bob           | Laptop   | 1500   | Shipped  |
| 5        | Charlie       | Phone    | 900    | Canceled |

Find the total revenue from shipped orders:

SELECT status, SUM(amount) AS total_revenue FROM Orders GROUP BY status HAVING status = 'Shipped';

Find customers who placed more than one order:

SELECT customer_name, COUNT(*) AS order_count FROM Orders GROUP BY customer_name HAVING order_count > 1;

🔹 Next Steps

Now that you know how to aggregate data using GROUP BY and HAVING, the next step is learning about JOIN operations to combine data from multiple tables! 🚀

The JOIN clause is used to combine rows from two or more tables based on a related column.

🔹 Why Use JOINs?

In real-world databases, data is stored in multiple tables. To retrieve meaningful information, we need to combine data using JOIN operations.

🛠 Types of SQL JOINs

• INNER JOIN – Returns matching records from both tables.
• LEFT JOIN – Returns all records from the left table and matching ones from the right.
• RIGHT JOIN – Returns all records from the right table and matching ones from the left.
• FULL JOIN – Returns all records when there is a match in either table.

1️⃣ INNER JOIN – Get Matched Data from Both Tables

Consider two tables: Customers and Orders

Customers Table:
| id | name    | city     |
|----|---------|----------|
| 1  | Alice   | New York |
| 2  | Bob     | London   |
| 3  | Charlie | Berlin   |

Orders Table:
| order_id | customer_id | product  | amount |
|----------|-------------|----------|--------|
| 101      | 1           | Laptop   | 1200   |
| 102      | 2           | Phone    | 800    |
| 103      | 1           | Mouse    | 50     |

To get customer names along with their orders:

SELECT Customers.name, Orders.product, Orders.amount 
FROM Customers 
INNER JOIN Orders ON Customers.id = Orders.customer_id;

2️⃣ LEFT JOIN – Get All Customers, Even Those Without Orders

Returns all customers, including those who haven’t placed an order.

SELECT Customers.name, Orders.product, Orders.amount 
FROM Customers 
LEFT JOIN Orders ON Customers.id = Orders.customer_id;

3️⃣ RIGHT JOIN – Get All Orders, Even If Customers Are Missing

Returns all orders, even if there’s no matching customer.

SELECT Customers.name, Orders.product, Orders.amount 
FROM Customers 
RIGHT JOIN Orders ON Customers.id = Orders.customer_id;

4️⃣ FULL JOIN – Get All Data from Both Tables

Returns all records where there is a match in either table.

SELECT Customers.name, Orders.product, Orders.amount 
FROM Customers 
FULL JOIN Orders ON Customers.id = Orders.customer_id;

🔹 Real-World Scenario

In an e-commerce database, you might use JOINs to:

• List all customers along with their orders.
• Find customers who haven’t placed any orders.
• Analyze total sales per customer.

🔹 Next Steps

Now that you know how to join tables, the next step is learning about sorting and limiting results for more advanced data retrieval. 🚀

🔹 Why Sorting & Limiting?

When dealing with large datasets, sorting and limiting results helps in finding relevant information quickly and optimizing queries.

🛠 Sorting Data with ORDER BY

The ORDER BY clause is used to sort results in ascending ( ASC) or descending ( DESC) order.

1️⃣ Sorting in Ascending Order (Default)

Sort customers by their age:

SELECT name, age FROM Customers ORDER BY age;

2️⃣ Sorting in Descending Order

Sort customers by their age in descending order:

SELECT name, age FROM Customers ORDER BY age DESC;

3️⃣ Sorting by Multiple Columns

First, sort by city in ascending order, then by age in descending order within each city:

SELECT name, city, age FROM Customers ORDER BY city ASC, age DESC;

🛠 Limiting Results with LIMIT

The LIMIT clause restricts the number of rows returned.

1️⃣ Get Only the First 5 Records

SELECT * FROM Customers LIMIT 5;

2️⃣ Get Records from a Specific Position ( OFFSET)

Skip the first 5 records and fetch the next 5:

SELECT * FROM Customers LIMIT 5 OFFSET 5;

🔹 Real-World Scenario

• Get the top 10 highest-paying customers in an e-commerce database.
• Find the latest 5 orders placed on a website.
• Sort employees by salary to identify top earners.

🔹 Next Steps

Now that you can sort and limit results, the next step is mastering subqueries and nested queries for more advanced filtering. 🚀

🔹 Why Use Subqueries?

Subqueries (also called nested queries) allow you to use the result of one query inside another. They help break complex problems into smaller, manageable parts.

🛠 Basic Subquery Example

Find customers who have placed at least one order:

SELECT * FROM Customers 
WHERE customer_id IN (SELECT customer_id FROM Orders);

🛠 Using Subqueries in SELECT

Find each customer’s total number of orders:

SELECT name, 
       (SELECT COUNT(*) FROM Orders WHERE Orders.customer_id = Customers.customer_id) AS order_count
FROM Customers;

🛠 Using Subqueries in WHERE (Filtering Data)

Find customers who have spent more than the average order amount:

SELECT * FROM Customers 
WHERE customer_id IN (
    SELECT customer_id FROM Orders 
    WHERE amount > (SELECT AVG(amount) FROM Orders)
);

🛠 Using Subqueries in FROM (Derived Tables)

Get the highest-paying customers from a temporary result set:

SELECT * FROM (
    SELECT customer_id, SUM(amount) AS total_spent 
    FROM Orders 
    GROUP BY customer_id
) AS CustomerTotals
WHERE total_spent > 1000;

🔹 Real-World Scenario

• Find products that have never been ordered.
• Retrieve employees earning more than their department’s average salary.
• Get customers who placed the highest order on the platform.

🔹 Next Steps

Now that you’ve mastered subqueries, the next step is learning about JOINs vs. Subqueries and when to use each for performance optimization. 🚀

🔹 Understanding JOINs & Subqueries

Both JOINs and Subqueries allow you to combine data from multiple tables, but they have different use cases and performance considerations.

🛠 When to Use JOINs

JOINs are typically faster than subqueries because they work directly with tables rather than relying on intermediate results.

Example: Retrieve customer names and their order amounts using a JOIN:

SELECT Customers.name, Orders.amount 
FROM Customers 
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

🛠 When to Use Subqueries

Subqueries are useful when filtering, computing aggregations, or when a JOIN is too complex.

Example: Retrieve customers who have placed at least one order using a subquery:

SELECT name FROM Customers 
WHERE customer_id IN (SELECT customer_id FROM Orders);

🔹 Performance Considerations

• JOINs are generally more efficient and optimized by SQL engines.
• Subqueries can be easier to read but may have performance issues with large datasets.
• For large datasets, prefer JOINs over subqueries whenever possible.

🔹 Real-World Scenario

• Use a JOIN to list all employees and their department names.
• Use a Subquery to find employees earning more than their department’s average salary.
• Use a JOIN to get products with their latest order details.

🔹 Next Steps

Now that you’ve learned JOINs vs. Subqueries, the next step is “Advanced SQL Joins (INNER, LEFT, RIGHT, FULL, CROSS)” for even more complex queries. 🚀

🔹 Understanding SQL Joins

SQL JOIN operations allow you to retrieve data from multiple tables based on related columns. Let’s dive into different types of joins with examples.

🛠 INNER JOIN (Matches Only)

Returns only the records that have matching values in both tables.

SELECT Employees.name, Departments.department_name 
FROM Employees 
INNER JOIN Departments ON Employees.department_id = Departments.department_id;

🛠 LEFT JOIN (All from Left + Matches from Right)

Returns all records from the left table and matching records from the right table. If there’s no match, NULL is returned.

SELECT Employees.name, Departments.department_name 
FROM Employees 
LEFT JOIN Departments ON Employees.department_id = Departments.department_id;

🛠 RIGHT JOIN (All from Right + Matches from Left)

Returns all records from the right table and matching records from the left table.

SELECT Employees.name, Departments.department_name 
FROM Employees 
RIGHT JOIN Departments ON Employees.department_id = Departments.department_id;

🛠 FULL JOIN (All from Both Tables)

Returns all records from both tables, filling NULLs where there’s no match.

SELECT Employees.name, Departments.department_name 
FROM Employees 
FULL JOIN Departments ON Employees.department_id = Departments.department_id;

🛠 CROSS JOIN (All Possible Combinations)

Returns a Cartesian product of both tables (every row from the first table is paired with every row from the second table).

SELECT Employees.name, Departments.department_name 
FROM Employees 
CROSS JOIN Departments;

🔹 Choosing the Right Join

• Use INNER JOIN when you only need matched data.
• Use LEFT JOIN when you need all data from the left table, even if there’s no match.
• Use RIGHT JOIN when you need all data from the right table.
• Use FULL JOIN when you need everything from both tables.
• Use CROSS JOIN when you need to combine every row from two tables.

🔹 Next Steps

Now that you’ve mastered Advanced Joins, the next step is “Common Table Expressions (CTEs) & Window Functions” for even more powerful queries. 🚀

🔹 Understanding CTEs & Window Functions

Common Table Expressions (CTEs) and Window Functions allow for more readable, efficient, and advanced queries.

🛠 What is a CTE?

A CTE (Common Table Expression) is a temporary result set that can be referenced within a SQL statement. It improves query readability and makes complex queries easier to manage.

✅ Example: Using a CTE

WITH HighSalaryEmployees AS (
    SELECT name, salary 
    FROM Employees 
    WHERE salary > 80000
)
SELECT * FROM HighSalaryEmployees;

Here, we create a temporary table HighSalaryEmployees and then query it.

🛠 What are Window Functions?

Window functions perform calculations across a set of table rows that are related to the current row, without collapsing the result set like GROUP BY.

✅ Example: Ranking Employees by Salary

SELECT name, salary, 
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM Employees;

This ranks employees based on salary without grouping them.

🔹 More Window Functions

✅ ROW_NUMBER()

Assigns a unique number to each row based on a specified order.

SELECT name, department, 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM Employees;

✅ DENSE_RANK()

Similar to RANK() but without skipping rankings.

SELECT name, salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM Employees;

✅ LEAD() & LAG()

Compare a row’s value with the next or previous row’s value.

SELECT name, salary, 
    LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary,
    LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary
FROM Employees;

🔹 When to Use CTEs & Window Functions?

• Use CTEs when breaking down complex queries into manageable steps.
• Use Window Functions when performing row-based calculations while keeping the full result set.

🔹 Next Steps

Now that you’ve learned CTEs & Window Functions, the next step is “Performance Optimization: Indexing & Query Tuning” to make your SQL queries faster and more efficient. 🚀

🚀 Why Optimize SQL Queries?

Optimizing queries ensures your database runs efficiently, reducing execution time and improving performance.

🔹 Using Indexes for Faster Queries

Indexes speed up searches by allowing the database to find rows faster instead of scanning the entire table.

✅ Creating an Index

CREATE INDEX idx_customer_name 
ON Customers (name);

This creates an index on the name column to speed up searches.

✅ Checking Existing Indexes

SELECT * FROM pg_indexes WHERE tablename = 'Customers'; -- PostgreSQL
SHOW INDEX FROM Customers; -- MySQL

🔹 Avoiding Full Table Scans

Avoid queries that force the database to scan an entire table.

❌ Bad Query (Slow)

SELECT * FROM Customers WHERE LOWER(name) = 'john';

🔴 Problem:The LOWER() function prevents index usage, forcing a full scan.

✅ Optimized Query (Fast)

SELECT * FROM Customers WHERE name = 'John';

✅ Fix:Store data in a consistent format and avoid unnecessary functions in WHERE clauses.

🔹 Using EXPLAIN to Analyze Queries

Use EXPLAIN to check how your query executes.

EXPLAIN ANALYZE 
SELECT * FROM Orders WHERE order_date > '2024-01-01';

✅ Tip:If you see Seq Scan, it means a full table scan is happening—consider adding an index!

🔹 Limiting Rows for Efficiency

Returning fewer rows speeds up queries.

✅ Example: Fetching the top 10 customers

SELECT * FROM Customers ORDER BY created_at DESC LIMIT 10;

🔹 Avoiding SELECT *

Only fetch the columns you need to reduce data transfer.

❌ Bad Query (Slow)

SELECT * FROM Orders;

✅ Optimized Query (Fast)

SELECT order_id, customer_id, order_date FROM Orders;

🔹 Next Steps

Now that you’ve learned query tuning and indexing, the next step is “Transactions & Concurrency Control” to handle multi-user environments effectively. ⚡

🔄 What Are Transactions?

A transaction is a sequence of database operations that are executed as a single unit. Transactions ensure data integrity by following the ACID principles:

• A tomicity: All operations succeed or none at all.
• C onsistency: The database moves from one valid state to another.
• I solation: Transactions execute independently.
• D urability: Changes are saved permanently after commit.

✅ Starting a Transaction

Use BEGIN to start a transaction:

BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

✅ COMMIT makes the changes permanent.

❌ Rolling Back a Transaction

If something goes wrong, use ROLLBACK to undo changes:

BEGIN;
UPDATE Orders SET status = 'Shipped' WHERE order_id = 101;
ROLLBACK;

🔴 ROLLBACK cancels all changes since the transaction started.

🔀 Concurrency Control & Isolation Levels

When multiple users access the database, transactions must be isolated to prevent conflicts. SQL provides different isolation levels:

🔹 READ UNCOMMITTED (Low Isolation, Fastest)

Allows dirty reads (seeing uncommitted data).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

🔹 READ COMMITTED (Default in Most Databases)

Only reads committed data, avoiding dirty reads.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

🔹 REPEATABLE READ (Prevents Non-Repeatable Reads)

Ensures the same query always returns the same results.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

🔹 SERIALIZABLE (Highest Isolation, Slowest)

Ensures complete transaction isolation but may reduce performance.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

🔹 Deadlocks & How to Avoid Them

Deadlocks occur when two transactions wait for each other to release a lock.

🔴 Example of a Deadlock

-- Transaction 1
BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction 2 (Runs Simultaneously)
BEGIN;
UPDATE Accounts SET balance = balance - 50 WHERE id = 2;
UPDATE Accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

✅ Avoid Deadlocks By:

• Accessing tables in a consistent order.
• Keeping transactions short and fast.
• Using LOCK TIMEOUTS to avoid waiting indefinitely.

🚀 Next Steps

Now that you’ve mastered transactions, the next step is “Stored Procedures & Triggers” for automating database logic. ⚡

🛠 What Are Stored Procedures?

A Stored Procedure is a reusable SQL code block that can be executed with a single command. It helps in automating tasks, improving performance, and maintaining security.

✅ Creating a Stored Procedure

Example: A stored procedure to get customer details by ID.

CREATE PROCEDURE GetCustomerDetails(IN customer_id INT)
BEGIN
    SELECT * FROM Customers WHERE id = customer_id;
END;

▶️ Executing a Stored Procedure

Call the stored procedure using:

CALL GetCustomerDetails(5);

🔄 Updating Data with Stored Procedures

Example: A procedure to update customer age.

CREATE PROCEDURE UpdateCustomerAge(IN customer_id INT, IN new_age INT)
BEGIN
    UPDATE Customers SET age = new_age WHERE id = customer_id;
END;

Execute:

CALL UpdateCustomerAge(3, 30);

🚀 What Are Triggers?

A Trigger is an automatic action executed when a specific event (INSERT, UPDATE, DELETE) occurs in a table.

🔔 Creating a Trigger

Example: Automatically log changes when a customer’s age is updated.

CREATE TRIGGER LogAgeChange 
AFTER UPDATE ON Customers
FOR EACH ROW
BEGIN
    INSERT INTO CustomerLog (customer_id, old_age, new_age, changed_at)
    VALUES (OLD.id, OLD.age, NEW.age, NOW());
END;

💡 When to Use Stored Procedures vs. Triggers?

• Use Stored Procedures for executing logic when explicitly called.
• Use Triggers for automatic actions when a table is modified.

🚀 Next Steps

Now that you understand stored procedures and triggers, the next step is “Database Security & Access Control” to manage user roles and permissions. 🔐