Transactions

shivakumardarur3@[Link]
ACID Properties

e
Atomicity - All statements succeed or none succeed.

lleg
Consistency - Data moves from one valid state to another.

Co
Isolation - Parallel transactions don't interfere.

pna
Durability - Committed data is permanently saved.

A
Transactions

shivakumardarur3@[Link]
e
Disable autocommit

SET autocommit = 0;

lleg
a Co
Enable autocommit

SET autocommit = 1;
Apn
Transactions

shivakumardarur3@[Link]
Start & Commit

ege
START TRANSACTION;

Coll
UPDATE accounts SET balance = balance - 50 WHERE id = 1;

na
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;

Ap
Transactions

shivakumardarur3@[Link]
Rollback

ege
START TRANSACTION;

Coll
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

na
UPDATE accounts SET balance = balance + 100 WHERE id = 3;

ROLLBACK;

Ap
Transactions

shivakumardarur3@[Link]
Savepoint

START TRANSACTION;

lege
Col
a
UPDATE accounts SET balance = balance + 1000 WHERE id = 1;

n
SAVEPOINT after_wallet_topup;

Ap
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

ROLLBACK TO after_wallet_topup;

COMMIT;
JOINs

shivakumardarur3@[Link]
JOINs are used to combine rows from two or more tables based on a related column between them.

lege
Col
a
Inner Join Left Join Right Join Full Join

Apn Outer Joins

shivakumardarur3@[Link]
INNER JOIN
customers orders

ge
Syntax
SELECT column(s)

olle
C
FROM tableA

na
INNER JOIN tableB

Ap
ON tableA.col_name = tableB.col_name;
JOINs

shivakumardarur3@[Link]
LEFT JOIN
customers orders

ge
Syntax

SELECT column(s)

olle
a C
FROM tableA

pn
LEFT JOIN tableB

A
ON tableA.col_name = tableB.col_name;
JOINs

shivakumardarur3@[Link]
RIGHT JOIN
customers orders

ge
Syntax

SELECT column(s)

olle
a C
FROM tableA

pn
RIGHT JOIN tableB

A
ON tableA.col_name = tableB.col_name;
JOINs LEFT JOIN

shivakumardarur3@[Link]
UNION
OUTER JOIN RIGHT JOIN

ge
Syntax in MySQL

olle
na C
Ap
JOINs

shivakumardarur3@[Link]
CROSS JOIN

Syntax

lege
SELECT column(s)

ol
FROM tableA

a C
CROSS JOIN tableB ;

Apn
JOINs

shivakumardarur3@[Link]
A A
SELF JOIN

It is a regular join but the table is joined with itself.

lege
Syntax

Col
na
SELECT column(s)

Ap
FROM table as a
JOIN table as b
ON a.col_name = b.col_name;
Practice Qs

shivakumardarur3@[Link]
Write SQL command to display the exclusive joins :
customers orders

lege
Col
a
Left Exclusive Join Right Exclusive Join

Apn
 Query

Sub-Queries

shivakumardarur3@[Link]
Sub Query

A Subquery or Inner query or a Nested query is a query within

lege
ol
Syntax

a C
SELECT column(s)

pn
FROM table_name

A
WHERE col_name operator
( subquery );
Sub-Queries

shivakumardarur3@[Link]
With WHERE

lege
Col
pna
A
Sub-Queries

shivakumardarur3@[Link]
With SELECT

lege
Col
pna
A
Sub-Queries

shivakumardarur3@[Link]
With FROM

lege
Col
pna
A
Views in SQL

shivakumardarur3@[Link]
A view is a virtual table based on the result-set of an SQL statement.

e
Syntax

lleg
o
CREATE VIEW view1 AS

C
SELECT col1, col2 FROM table_name;

pna
A
*A view always shows up-to-date data.
The database engine recreates the view,
every time a user queries it.
Views in SQL

shivakumardarur3@[Link]
No data is stored physically (unless it's a materialized view in some DBs).

e
Can include columns from one or more tables.

lleg
Can be used in SELECT, JOIN, or even WHERE clauses like a normal table.

a Co
Helps with security by exposing only certain columns to users.

Apn
Index in SQL

shivakumardarur3@[Link]
indexes are special database objects that make data retrieval faster.

e
Syntax (single col & multi-col)

lleg
CREATE INDEX idx_name ON table(col);

Co
CREATE INDEX idx_name ON table(col1, col2);

pna
SHOW INDEX FROM table;
DROP INDEX idx_name ON table;
A
Stored Procedures

shivakumardarur3@[Link]
Predefined set of SQL statements that you can save in the database and execute whenever needed.

e
Syntax (Create)

leg
CREATE PROCEDURE procedure_name (parameters)

ol
BEGIN

a C
-- SQL statements

pn
END;

A
Stored Procedures

shivakumardarur3@[Link]
e
Syntax (Call)

leg
CALL procedure_name (arguments);

Col
Syntax (Drop)

pna
A
DROP PROCEDURE IF EXISTS procedure_name;