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 e
Disable autocommit
o l le
SET autocommit = 0;
n a C
Enable autocommit
SET autocommit = 1;
A p
Transactions
Start & Commit
START TRANSACTION;
le g e
C o l
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
n a
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;
A p
Transactions
Rollback
START TRANSACTION;
le g e
C o l
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
n a
UPDATE accounts SET balance = balance + 100 WHERE id = 3;
ROLLBACK;
A p
Transactions
Savepoint
le g e
l
START TRANSACTION;
C o
a
UPDATE accounts SET balance = balance + 1000 WHERE id = 1;
n
SAVEPOINT after_wallet_topup;
A p
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
ROLLBACK TO after_wallet_topup;
COMMIT;
JOINs
JOINs are used to combine rows from two or more tables based on a related column between them.
le g e
C o l
a
Inner Join Left Join Right Join Full Join
A p n
Outer Joins
JOINs
INNER JOIN
customers orders
Syntax
le g e
SELECT column(s)
FROM tableA
C o l
INNER JOIN tableB
p n a
A
O N tableA.col_name = tableB.col_name;
JOINs
LEFT JOIN
customers orders
Syntax
le g e
SELECT column(s)
C o l
a
FROM tableA
p n
LEFT JOIN tableB
A
O N tableA.col_name = tableB.col_name;
JOINs
RIGHT JOIN
customers orders
Syntax
le g e
SELECT column(s)
C o l
a
FROM tableA
p n
RIGHT JOIN tableB
A
O N tableA.col_name = tableB.col_name;
JOINs LEFT JOIN
UNION
OUTER JOIN RIGHT JOIN
Syntax in MySQL
le g e
C o l
p n a
A
JOINs
CROSS JOIN
e
Syntax
SELECT column(s)
l le g
C o
FROM tableA
a
CROSS JOIN tableB ;
A p n
JOINs A A
SELF JOIN
It is a regular join but the table is joined with itself.
le g e
Syntax
C o l
n a
SELECT column(s)
A p
FROM table as a
JOIN table as b
O N a.col_name = b.col_name;
Practice Qs
Write SQL command to display the exclusive joins :
customers orders
le g e
C o l
a
Left Exclusive Join Right Exclusive Join
A p n
Query
Sub-Queries Sub Query
A Subquery or Inner query or a Nested query is a query within
another SQL query. It involves 2 select statements.
le g e
o l
Syntax
SELECT column(s)
n a C
p
FROM table_name
A
WHERE col_name operator
( subquery );
Sub-Queries
With WHERE
le g e
C o l
p n a
A
Sub-Queries
With SELECT
le g e
C o l
p n a
A
Sub-Queries
With FROM
le g e
C o l
p n a
A
Views in SQL
A view is a virtual table based on the result-set of an SQL statement.
g e
Syntax
CREATE VIEW view1 AS
o l le
C
SELECT col1, col2 FROM table_name;
p n a
A
*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).
e
Can include columns from one or more tables.
l le g
o
Can be used in SELECT, JOIN, or even WHERE clauses like a normal table.
a C
Helps with security by exposing only certain columns to users.
A p n
Index in SQL
indexes are special database objects that make data retrieval faster.
g e
Syntax (single col & multi-col)
l le
CREATE INDEX idx_name ON table(col);
CREATE INDEX idx_name ON table(col1, col2);
C o
p n a
SHOW INDEX FROM table;
DROP INDEX idx_name ON table;
A
Stored Procedures
Predefined set of SQL statements that you can save in the database and execute whenever needed.
g e
Syntax (Create)
l le
CREATE PROCEDURE procedure_name (parameters)
o
BEGIN
-- SQL statements
n a C
p
END;
A
Stored Procedures
g e
Syntax (Call)
l le
CALL procedure_name (arguments);
C o
Syntax (Drop)
p n a
A
DROP PROCEDURE IF EXISTS procedure_name;