Transactions
ACID Properties
Atomicity - All statements succeed or none succeed.
Consistency - Data moves from one valid state to another.
Isolation - Parallel transactions don't interfere.
Durability - Committed data is permanently saved.
Transactions
G
KV
Disable autocommit
p_
SET autocommit = 0;
ee
ld
Enable autocommit
SET autocommit = 1;
Ku
@
Transactions
Start & Commit
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;
Transactions
G
Rollback
KV
START TRANSACTION;
p_
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ee
UPDATE accounts SET balance = balance + 100 WHERE id = 3;
ld
ROLLBACK;
Ku
@
Transactions
Savepoint
START TRANSACTION;
UPDATE accounts SET balance = balance + 1000 WHERE id = 1;
SAVEPOINT after_wallet_topup;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
ROLLBACK TO after_wallet_topup;
COMMIT;
JOINs
G
JOINs are used to combine rows from two or more tables based on a related column between them.
KV
p_
ee
Inner Join Left Join Right Join Full Join
ld
Outer Joins
Ku
@
JOINs
INNER JOIN
customers orders
Syntax
SELECT column(s)
FROM tableA
INNER JOIN tableB
ON tableA.col_name = tableB.col_name;
JOINs
G
LEFT JOIN
KV
customers orders
Syntax
p_
SELECT column(s)
ee
FROM tableA
LEFT JOIN tableB
ld
ON tableA.col_name = tableB.col_name;
Ku
@
JOINs
RIGHT JOIN
customers orders
Syntax
SELECT column(s)
FROM tableA
RIGHT JOIN tableB
ON tableA.col_name = tableB.col_name;
JOINs LEFT JOIN
UNION
G
OUTER JOIN RIGHT JOIN
KV
Syntax in MySQL
p_
ee
ld
Ku
@
JOINs
CROSS JOIN
Syntax
SELECT column(s)
FROM tableA
CROSS JOIN tableB ;
JOINs A A
G
SELF JOIN
KV
It is a regular join but the table is joined with itself.
p_
Syntax
ee
SELECT column(s)
ld
FROM table as a
JOIN table as b
ON a.col_name = b.col_name; Ku
@
Practice Qs
Write SQL command to display the exclusive joins :
customers orders
Left Exclusive Join Right Exclusive Join
Query
Sub-Queries Sub Query
G
A Subquery or Inner query or a Nested query is a query within
KV
another SQL query. It involves 2 select statements.
p_
Syntax
ee
SELECT column(s)
FROM table_name
ld
WHERE col_name operator
( subquery );
Ku
@
kuldeepkumarmukhiya5@[Link]
Sub-Queries
With WHERE
kuldeepkumarmukhiya5@[Link]
G
KV
p_
ee
ld
Ku
@
Sub-Queries
With SELECT
kuldeepkumarmukhiya5@[Link]
Sub-Queries
With FROM
Views in SQL
G
A view is a virtual table based on the result-set of an SQL statement.
KV
Syntax
p_
CREATE VIEW view1 AS
SELECT col1, col2 FROM table_name;
ee
ld
Ku
@
*A view always shows up-to-date data.
The database engine recreates the view,
every time a user queries it.
Views in SQL
No data is stored physically (unless it's a materialized view in some DBs).
Can include columns from one or more tables.
Can be used in SELECT, JOIN, or even WHERE clauses like a normal table.
Helps with security by exposing only certain columns to users.
Index in SQL
G
indexes are special database objects that make data retrieval faster.
KV
Syntax (single col & multi-col)
p_
CREATE INDEX idx_name ON table(col);
CREATE INDEX idx_name ON table(col1, col2);
ee
ld
SHOW INDEX FROM table; Ku
@
DROP INDEX idx_name ON table;
Stored Procedures
Predefined set of SQL statements that you can save in the database and execute whenever needed.
Syntax (Create)
CREATE PROCEDURE procedure_name (parameters)
BEGIN
-- SQL statements
END;
Stored Procedures
G
KV
Syntax (Call)
p_
CALL procedure_name (arguments);
ee
Syntax (Drop)
ld
DROP PROCEDURE IF EXISTS procedure_name;
Ku
@