Sql:
Features of sql:
Basic Sql Statements:
Select statement select empName from Emp
Distinct Statement select Distinct empName from Emp
Where Statement select empName from Emp where job = clurk
Order Statement select empName from Emp ordered by job
Operators:
Arthmetic Operators(+, -, *, /)
select sal * 2 from Emp;
select (sal – 200) * 2 from Emp;
Relational Operators(<, >, <=, >=)
Select sal from Emp where sal < 500;
Logical Operators(And, Or, Not)
Select sal from Emp where sal < 500 Or job=clerk
Between…And
Select sal from Emp where sal between 50 and 10
In
Select sal from Emp where sal in (1000, 2000)
Like Operator
Select empName from Emp where name = “S%”
Null Operator
Select sal from Emp where sal is not null
Any Operator, All Operator
Joins:
Inner Join:
SELECT [Link], [Link] FROM
Employees E INNER JOIN Departments D
ON [Link] = [Link];
Left Join:
SELECT [Link], [Link] FROM
Employees E Left JOIN Departments D
ON [Link] = [Link];
Right Join:
SELECT [Link], [Link] FROM
Employees E Right JOIN Departments D
ON [Link] = [Link];
Full Join:
SELECT [Link], [Link] FROM
Employees E Full JOIN Departments D;
Cross Join:
SELECT [Link], [Link] FROM
Employees ECross JOIN Departments D
ON [Link] = [Link];
Self Join:
Views:
Types: Simple view, complex view
Index:
CREATE INDEX idx_employee_name
ON Employees (Name);
Constraints:
Types: Not Null, unique,
SubQuery:
Single-Row subQuery
Multiple-Row subQuery
Correlated subquery:
wo subquery hoti hai jo outer query ke column par depend karti hai aur har row ke liye execute hoti hai.
Transaction:
Commit and rollback
ACID Properties of Transaction(Atomicity, Consistency, Isolation, Durability)
Schedule
Schedule batata hai ki multiple transactions ke operations kis order mein execute hote hain.
Types of Schedules
1. Serial Schedule
👉 Ek time par sirf ek transaction chalta hai
👉 No overlap
2. Parallel Schedule
👉 Ek time par multiple transactions chalte hain
👉 Operations overlap karte hain
Serializability
👉 Parallel schedule jo serial schedule jaisa result de, use serializable kehte hain
👉 Speed bhi milti hai aur correctness bhi ✔️
Equivalence of Schedules
1. Result Equivalence
👉 Final output same ho
2. View Equivalence
👉 Data ko read/write karne ka tareeqa same ho
3. Conflict Equivalence
👉 Conflicting operations ka order same ho
Concurrency Control (Introduction)
Concurrency control ka matlab hai:
👉 Multiple transactions ek sath chal sakein bina database ko galat banaye
Real life examples:
Bank systems
Online shopping
Ticket booking
Ye problems se bachata hai:
❌ Data inconsistency
❌ Wrong balance
❌ Overwriting data
2. Concurrency Control kyun zaroori hai?
Agar do users ek hi data ko same time update karein to problem hoti hai.
Bank Example
• User A ₹500 withdraw karta hai
• User B ₹200 deposit karta hai
👉 Galat balance ho sakta hai agar control na ho
Problems jo solve hoti hain
1. Lost Update – Ek update dusre ko overwrite kar deta hai
2. Dirty Read – Uncommitted data read ho jata hai
3. Uncommitted Dependency – Rollback hone wala data use ho jata hai
4. Inconsistent Retrieval – Adha updated data read ho jata hai
3. Types of Concurrency Control
A. Pessimistic Concurrency Control
👉 Maan leta hai ke conflict hoga, isliye pehle hi rok lagata hai
Kaise kaam karta hai?
• Data ko lock karta hai
Types of Locks
1. Shared Lock (S-Lock)
✔ Multiple transactions read kar sakti hain
❌ Write allowed nahi
2. Exclusive Lock (X-Lock)
✔ Read + Write allowed
❌ Koi aur access nahi kar sakta
Real-Life Example
🎟 Ticket booking – Seat select hote hi lock ho jati hai
Advantages
✔ High consistency
✔ Data safe
Disadvantages
❌ Deadlock ho sakta hai
B. Optimistic Concurrency Control
👉 Maan leta hai ke conflict kam hoga
Kaise kaam karta hai?
• Lock use nahi karta
• Commit time par check karta hai
Phases
1. Read Phase – Data read
2. Validation Phase – Conflict check
3. Write Phase – Update if safe
Real-Life Example
📖 Wikipedia editing – Save karte waqt conflict check
Advantages
✔ Deadlock nahi hota
✔ Fast for read-heavy systems
Disadvantages
❌ High load mein rollback zyada hota hai
C. Timestamp-Based Protocol
👉 Har transaction ko timestamp milta hai
Rule
• Older transaction ko priority milti hai
• Younger transaction wait ya abort hoti hai
Real-Life Example
📈 Stock trading – Pehle aane wala pehle process
Advantage
✔ Fair execution order
Disadvantage
❌ Complex system
D. Multiversion Concurrency Control (MVCC)
👉 Ek hi data ki multiple copies rakhta hai
Kaise kaam karta hai?
• Readers old version read karte hain
• Writers new version banate hain
Real-Life Example
🏦 Bank statement – Purana balance bhi dekh sakte ho
Advantages
✔ Reader aur writer ek dusre ko block nahi karte
✔ High performance
Disadvantage
❌ Extra storage use hoti hai
Database Failure:
Soft Failure
Hard Failure
Network Failure
4. Commit Protocol
👉 Ensure karta hai ke:
✔ Transaction proper commit ho
✔ Failure ke baad database consistent rahe
Do Actions
• Undo (Rollback) – Changes cancel
• Redo (Roll forward) – Changes dobara apply
5. Commit Point
👉 Wo time jab decide hota hai:
✔ Transaction commit hoga ya abort
Commit Point ke features
• Database consistent hota hai
• Changes sabko visible hote hain
• Log mein sab operations record hote hain
• Transaction undo possible hota hai
• Sare locks release ho jate hain
6. Transaction Undo (Rollback)
👉 Transaction ke saare changes cancel kar deta hai
📌 Mostly soft failure mein use hota hai
Example:
Transaction fail → old data restore
7. Transaction Redo (Roll Forward)
👉 Transaction ke changes dobara apply karta hai
📌 Mostly hard failure ke baad use hota hai
Example:
Disk crash → log se data wapas likhna
8. Transaction Log
👉 Ek sequential file hoti hai jo transaction ka record rakhti hai
Uses
• Commit / rollback support
• Failure ke baad recovery
📌 Disk par store hota hai
📌 Backup tape par bhi copy hoti hai
9. Transaction Log Lists
Recovery ke liye transaction ko categories mein rakha jata hai:
1. Commit List – Start + Commit record
2. Failed List – Start + Fail (no abort)
3. Abort List – Start + Abort
4. Before-Commit List – Start + Before-commit
5. Active List – Sirf start record
10. Immediate Update
👉 Changes direct disk par likhe jate hain
Rules
• Old + New value log mein store hoti hai
• Commit → changes permanent
• Rollback → old value restore
📌 Fast but risky
11. Deferred Update
👉 Pehle sirf log mein changes jate hain
Rules
• Commit → log se disk par write
• Rollback → log discard
📌 Safe but thoda slow
Database Recovery
Recovery by reprocessing
Recovery by rollback ya roll forward
Types of Backup
Full backup
Incremental backup
Offline backup
Online backup
Database Security:
Database Security Threats:
Database security threats wo khatre (risks) hote hain jo database ke data ko chura, badal ya destroy kar
sakte hain.
Authorization
Authentication
Views
Backup and Recovery
Encryption