Fundamental SQL Concepts

A storage engine is the underlying software that handles how data is stored, retrieved, and managed in a database.

SHOW TABLE STATUS WHERE Name = 'Orders';

ALTER TABLE Orders ENGINE = InnoDB;

InnoDB is best because it supports ACID compliance, transactions, and foreign keys.

It does not support transactions or foreign keys, leading to data integrity risks.

A primary key is a column (or set of columns) that uniquely identifies each record in a table.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(255),
    City VARCHAR(100)
);

A foreign key creates a relationship between two tables.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

If referential integrity is enforced:

• CASCADE - Deletes child records
• SET NULL - Sets child values to NULL
• RESTRICT - Prevents deletion

ON DELETE CASCADE;
ON DELETE SET NULL;
ON DELETE RESTRICT;

SET FOREIGN_KEY_CHECKS = 0;  -- Disable
SET FOREIGN_KEY_CHECKS = 1;  -- Enable

GRANT ALL PRIVILEGES ON mydatabase.* TO 'user1'@'localhost';
FLUSH PRIVILEGES;

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'user1'@'localhost';

GRANT SELECT ON mydatabase.* TO 'user1'@'localhost';

SHOW GRANTS FOR 'user1'@'localhost';

A character set (charset) defines how text is stored (e.g., utf8, latin1).

A collation defines sorting and comparison rules (e.g., utf8_general_ci, utf8_unicode_ci).

• utf8_general_ci - Faster, but less accurate sorting
• utf8_unicode_ci - More accurate, supports multiple languages

SHOW CREATE DATABASE mydatabase;

ALTER TABLE Customers CHARACTER SET utf8 COLLATE utf8_unicode_ci;

utf8mb4_unicode_ci ? Supports Hindi, Tamil, Bengali, etc.

• ACID (Atomicity, Consistency, Isolation, Durability) ? Used in SQL databases.
• BASE (Basically Available, Soft state, Eventually consistent) ? Used in NoSQL databases.

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
COMMIT;

An index improves query performance by creating a sorted structure for lookups.

CREATE INDEX idx_customer ON Orders(CustomerID);

A unique key prevents duplicate values but allows one NULL.

CREATE TABLE Employees (
    EmployeeID INT UNIQUE,
    Name VARCHAR(255)
);

CREATE UNIQUE INDEX idx_unique ON Employees(Name, Department);

A deadlock occurs when two queries wait for each other to release locks.
 Prevention: Lock tables in the same order

• Row-level locking (InnoDB) - Better performance
• Table-level locking (MyISAM) - Slower for concurrent writes

A transaction is a group of SQL queries executed as a single unit, ensuring data consistency.

Example: Transferring Rs.500 between two bank accounts

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 101;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 102;
COMMIT; -- Saves the changes

If any query fails, use ROLLBACK to cancel the transaction.

Example:

START TRANSACTION;
DELETE FROM Employees WHERE EmployeeID = 5;
ROLLBACK;  -- Restores the deleted row

A Savepoint allows partial rollbacks inside a transaction.

START TRANSACTION;
DELETE FROM Employees WHERE Department = 'HR';
SAVEPOINT SP1;
DELETE FROM Employees WHERE Department = 'IT';
ROLLBACK TO SP1; -- Undo IT deletions but keep HR deletions
COMMIT;

Example: Explicit table locking

LOCK TABLE Orders WRITE;
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1001;
UNLOCK TABLES;

Always access tables in the same order
Use row-level locks instead of table-level locks
Keep transactions short

Example of Deadlock Prevention

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
COMMIT;

An index is a performance optimization structure that speeds up searches.

CREATE INDEX idx_customer ON Orders(CustomerID);

Faster queries but slower INSERTs/UPDATEs

DROP INDEX idx_customer ON Orders;

Constraints enforce rules on data to maintain integrity.

ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (Salary > 20000);

MyISAM - Faster reads, good for reporting.

ALTER TABLE Sales ENGINE = MyISAM;

InnoDB - Supports transactions, best for banking, finance.

Memory Engine - Stores data in RAM for faster access.

CREATE TEMPORARY TABLE TempUsers ENGINE=MEMORY AS SELECT * FROM Users;

SHOW TABLE STATUS WHERE Name = 'Orders';

Use indexes
Avoid **SELECT ***
Use JOINs instead of subqueries
Use EXPLAIN to analyze queries

EXPLAIN SELECT * FROM Orders WHERE CustomerID = 5;

Stores query results in memory for faster retrieval.

SET GLOBAL query_cache_size = 1000000;

SELECT City, AVG(Salary) FROM Employees GROUP BY City HAVING AVG(Salary) > 30000;

DELETE FROM Employees
WHERE EmployeeID NOT IN (
    SELECT MIN(EmployeeID) FROM Employees GROUP BY Name, Department
);

OPTIMIZE TABLE Orders;