DAY 8 — DBMS Part 2: SQL, Joins and Transactions — Complete Study Material

Phase 1: Technical Theory | Date: 28 July 2026 | Subject: SQL Mastery | Expected Questions: Part of 15 DBMS Questions — Highest-Weight Subject

---

📌 Day Overview

Today you will study:

Hour	Topic
1	DDL: CREATE, ALTER, DROP; DML: SELECT, INSERT, UPDATE, DELETE
2	DCL: GRANT, REVOKE; TCL: COMMIT, ROLLBACK, SAVEPOINT; aggregate functions
3	WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT, LIKE, IN and BETWEEN
4	INNER, LEFT, RIGHT, FULL OUTER, CROSS and SELF JOIN; correlated and non-correlated subqueries
5	Transactions, ACID, transaction states, serializability, locks, 2PL and NoSQL

What You Will Master

After completing this material, you will be able to:

1. Identify DDL, DML, DCL and TCL commands.
2. Create, alter and delete database tables.
3. Insert, retrieve, update and delete rows.
4. Use aggregate functions correctly.
5. Filter, group and sort data.
6. Use WHERE and HAVING correctly.
7. Write patterns using LIKE.
8. Use IN, BETWEEN and DISTINCT.
9. Predict the output of all major joins.
10. Write correlated and non-correlated subqueries.
11. Explain transactions and ACID properties.
12. Identify transaction states.
13. Explain serial and serializable schedules.
14. Understand shared and exclusive locks.
15. Explain the two phases of 2PL.
16. Compare SQL and NoSQL databases.
17. Identify key-value, document, wide-column and graph databases.

---

1. SQL Commands — DDL and DML

1.1 What Is SQL?

SQL stands for Structured Query Language.

It is used to communicate with relational database systems.

SQL can be used to:

• Create database objects
• Create tables
• Insert data
• Retrieve data
• Update data
• Delete data
• Control access
• Manage transactions

Important Point

SQL is declarative.

A declarative language describes what result is required. The DBMS decides how to obtain that result.

Example:

SELECT Name
FROM Student
WHERE Marks > 80;

The query asks for names of students with marks above 80. It does not specify the exact internal search procedure.

---

1.2 SQL Command Categories

Category	Full Form	Main Purpose	Commands
DDL	Data Definition Language	Defines database structure	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	Data Manipulation Language	Manipulates table rows	INSERT, UPDATE, DELETE
DQL	Data Query Language	Retrieves data	SELECT
DCL	Data Control Language	Controls permissions	GRANT, REVOKE
TCL	Transaction Control Language	Controls transactions	COMMIT, ROLLBACK, SAVEPOINT

Classification Note

Many exam books include SELECT under DML. Other books place it separately under DQL. Follow the wording of the question.

Mnemonic

DDL = Design
DML = Modify rows
DQL = Query
DCL = Control access
TCL = Transaction control

---

1.3 Sample Tables Used Today

We will use the following tables.

DEPARTMENT

DeptID	DeptName	City
10	Computer	Jaipur
20	Science	Kota
30	Commerce	Ajmer
40	Arts	Udaipur

STUDENT

StudentID	StudentName	Marks	DeptID
1	Asha	85	10
2	Ravi	72	20
3	Mohan	85	10
4	Neha	60	30
5	Imran	45	NULL
6	Kavya	92	10

---

1.4 CREATE

CREATE creates a database object.

Objects include:

• Database
• Table
• View
• Index
• Schema

Create a Table

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL,
    City VARCHAR(30)
);

Explanation

Part	Meaning
CREATE TABLE	Creates a table
Department	Table name
DeptID INT	Integer column
PRIMARY KEY	Unique and not NULL
VARCHAR(50)	Variable-length text up to the allowed length
NOT NULL	Value is required

---

1.5 Create STUDENT Table

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Marks DECIMAL(5,2),
    DeptID INT,
    CONSTRAINT chk_marks
        CHECK (Marks BETWEEN 0 AND 100),
    CONSTRAINT fk_student_department
        FOREIGN KEY (DeptID)
        REFERENCES Department(DeptID)
);

Constraints

A constraint is a rule enforced by the database.

Constraint	Purpose
PRIMARY KEY	Uniquely identifies each row
FOREIGN KEY	References a key in another table
NOT NULL	Prevents NULL
UNIQUE	Prevents duplicate non-NULL values according to DBMS rules
CHECK	Tests a condition
DEFAULT	Supplies a default value

---

1.6 Common SQL Data Types

Exact names and limits differ among DBMS products.

Type	Purpose	Example
INT	Whole numbers	101
SMALLINT	Smaller whole numbers	25
DECIMAL(p,s)	Exact decimal	DECIMAL(8,2)
FLOAT	Approximate numeric value	3.14
CHAR(n)	Fixed-length text	CHAR(10)
VARCHAR(n)	Variable-length text	VARCHAR(50)
DATE	Date	2026-07-28
TIME	Time	10:30:00
TIMESTAMP	Date and time	Date-time value
BOOLEAN	True or false where supported	TRUE
BLOB	Binary large object	Image or file data
CLOB	Character large object	Large text

CHAR vs VARCHAR

CHAR(n)	VARCHAR(n)
Fixed-length character data	Variable-length character data
May pad shorter values	Stores varying lengths
Useful for fixed codes	Useful for names and addresses

---

1.7 ALTER

ALTER changes the structure of an existing table.

Add a Column

ALTER TABLE Student
ADD Email VARCHAR(100);

Drop a Column

ALTER TABLE Student
DROP COLUMN Email;

Add a Constraint

ALTER TABLE Student
ADD CONSTRAINT uq_student_email
UNIQUE (Email);

Change a Column

The exact syntax differs by DBMS.

Examples may use:

ALTER TABLE Student
ALTER COLUMN StudentName VARCHAR(100);

or:

ALTER TABLE Student
MODIFY StudentName VARCHAR(100);

Rename a Column

Syntax differs among DBMS products. A common form is:

ALTER TABLE Student
RENAME COLUMN StudentName TO Name;

Important Point

ALTER changes table structure, not ordinary row values.

---

1.8 DROP

DROP removes a database object and its definition.

DROP TABLE Student;

This removes:

• Table structure
• Table data
• Associated object definition

Recovery depends on the DBMS, transaction rules and backup facilities.

Important Warning

DROP should be used carefully.

---

1.9 TRUNCATE

TRUNCATE removes all rows from a table while normally preserving its structure.

TRUNCATE TABLE Student;

General exam characteristics:

• Removes all rows
• Does not use a WHERE clause
• Usually faster than deleting rows individually
• Commonly treated as DDL
• Transaction and identity-reset behaviour varies by DBMS

---

1.10 DELETE vs TRUNCATE vs DROP

Feature	DELETE	TRUNCATE	DROP
Category	DML	Usually DDL	DDL
Removes selected rows	Yes, with WHERE	No	No
Removes all rows	Yes, without WHERE	Yes	Yes, with object
Keeps table structure	Yes	Yes	No
WHERE allowed	Yes	No	No
Removes table definition	No	No	Yes
Transaction behaviour	Usually rollback possible before commit	DBMS-dependent	DBMS-dependent

Memory Rule

DELETE = Remove rows
TRUNCATE = Empty table
DROP = Remove table

---

1.11 INSERT

INSERT adds new rows.

Insert One Row

INSERT INTO Department (DeptID, DeptName, City)
VALUES (10, 'Computer', 'Jaipur');

Insert Into All Columns

If values follow the table’s column order:

INSERT INTO Department
VALUES (20, 'Science', 'Kota');

Writing column names is safer and clearer.

Insert Multiple Rows

Supported syntax in many DBMS products:

INSERT INTO Department (DeptID, DeptName, City)
VALUES
    (30, 'Commerce', 'Ajmer'),
    (40, 'Arts', 'Udaipur');

Insert From Another Query

INSERT INTO TopStudent (StudentID, StudentName, Marks)
SELECT StudentID, StudentName, Marks
FROM Student
WHERE Marks >= 80;

---

1.12 SELECT

SELECT retrieves data.

Select All Columns

SELECT *
FROM Student;

The asterisk means all columns.

Select Specific Columns

SELECT StudentName, Marks
FROM Student;

Column Alias

An alias gives a temporary display name.

SELECT StudentName AS Name,
       Marks AS Score
FROM Student;

AS is optional in many SQL dialects.

---

1.13 Expressions in SELECT

SELECT StudentName,
       Marks,
       Marks + 5 AS RevisedMarks
FROM Student;

This calculates a displayed value. It does not update the stored marks.

String Literals

SQL string values normally use single quotation marks:

'Asha'
'Jaipur'

Double quotation marks are generally used for quoted identifiers under standard SQL rules, though DBMS settings differ.

---

1.14 UPDATE

UPDATE changes existing rows.

Update One Student

UPDATE Student
SET Marks = 90
WHERE StudentID = 1;

Update Multiple Columns

UPDATE Department
SET DeptName = 'Computer Science',
    City = 'Jodhpur'
WHERE DeptID = 10;

Update All Rows

UPDATE Student
SET Marks = Marks + 5;

This updates every row.

Critical Warning

Without WHERE, all rows are updated.

---

1.15 DELETE

DELETE removes rows.

Delete One Row

DELETE FROM Student
WHERE StudentID = 5;

Delete Rows Matching a Condition

DELETE FROM Student
WHERE Marks < 40;

Delete All Rows

DELETE FROM Student;

The table structure remains.

Critical Warning

Without WHERE, all rows are deleted.

---

1.16 Safe DML Checklist

Before running UPDATE or DELETE:

1. Write the intended WHERE condition.
2. Test the condition using SELECT.
3. Verify the selected rows.
4. Start a transaction where appropriate.
5. Run the update or delete.
6. Verify the result.
7. Use COMMIT or ROLLBACK.

Example:

SELECT *
FROM Student
WHERE DeptID = 10;

After confirming:

UPDATE Student
SET Marks = Marks + 2
WHERE DeptID = 10;

---

2. DCL, TCL and Aggregate Functions

2A. Data Control Language

2.1 GRANT

GRANT gives privileges to a user or role.

A privilege is permission to perform an operation.

Example:

GRANT SELECT
ON Student
TO teacher_user;

This allows teacher_user to read STUDENT.

Grant Multiple Privileges

GRANT SELECT, INSERT, UPDATE
ON Student
TO clerk_user;

Grant All Available Privileges

Syntax and meaning vary by DBMS:

GRANT ALL PRIVILEGES
ON Student
TO admin_user;

---

2.2 REVOKE

REVOKE removes previously granted privileges.

REVOKE UPDATE
ON Student
FROM clerk_user;

Now clerk_user cannot update STUDENT through that permission.

GRANT vs REVOKE

GRANT	REVOKE
Gives permission	Removes permission
DCL	DCL
Uses TO	Uses FROM

---

2.3 Roles

A role is a named collection of privileges.

Instead of granting permissions separately to every user:

1. Create a role.
2. Grant privileges to the role.
3. Assign the role to users.

Conceptual example:

CREATE ROLE teacher_role;

GRANT SELECT
ON Student
TO teacher_role;

Role syntax varies among systems.

---

2B. Transaction Control Language

2.4 Transaction

A transaction is a logical unit of database work containing one or more operations.

Example bank transfer:

1. Subtract ₹500 from Account A.
2. Add ₹500 to Account B.

Both steps should succeed together.

---

2.5 COMMIT

COMMIT makes the current transaction’s changes permanent and ends that transaction under normal transaction processing.

UPDATE Account
SET Balance = Balance - 500
WHERE AccountID = 1;

UPDATE Account
SET Balance = Balance + 500
WHERE AccountID = 2;

COMMIT;

After commit, the changes are durable according to the database’s guarantees.

---

2.6 ROLLBACK

ROLLBACK cancels uncommitted changes in the current transaction.

UPDATE Student
SET Marks = 200
WHERE StudentID = 1;

ROLLBACK;

The invalid uncommitted change is undone.

Important Point

A normal rollback cannot usually undo a transaction that has already been committed.

Recovery after commit requires other methods, such as backup restoration or compensating transactions.

---

2.7 SAVEPOINT

A savepoint marks a position inside a transaction.

SAVEPOINT point1;

Later, the transaction can roll back to that point.

Example

UPDATE Student
SET Marks = 90
WHERE StudentID = 1;

SAVEPOINT after_first_update;

UPDATE Student
SET Marks = 95
WHERE StudentID = 2;

ROLLBACK TO after_first_update;

COMMIT;

Result:

• The first update remains part of the transaction.
• The second update is undone.
• COMMIT makes the remaining change permanent.

Transaction Diagram

Transaction starts
      │
      v
Operation A
      │
      v
SAVEPOINT S1
      │
      v
Operation B
      │
      v
ROLLBACK TO S1
      │
      └── Operation B undone
      │
      v
   COMMIT

---

2.8 TCL Comparison

Command	Purpose
COMMIT	Makes transaction changes permanent
ROLLBACK	Cancels uncommitted transaction changes
SAVEPOINT	Marks a point within a transaction
ROLLBACK TO	Returns to a savepoint

Important Note

Automatic commit behaviour and DDL transaction handling differ among DBMS products.

---

2C. Aggregate Functions

2.9 What Is an Aggregate Function?

An aggregate function calculates one result from multiple rows.

Main aggregate functions:

• COUNT
• SUM
• AVG
• MAX
• MIN

---

2.10 COUNT

Count All Rows

SELECT COUNT(*)
FROM Student;

If STUDENT has six rows:

6

COUNT(*) counts rows, including rows containing NULL values.

Count Non-NULL Values

SELECT COUNT(DeptID)
FROM Student;

If one student has DeptID = NULL, the result is:

5

Count Distinct Non-NULL Values

SELECT COUNT(DISTINCT DeptID)
FROM Student;

Non-NULL department values are:

10, 20, 30

Result:

3

---

2.11 SUM

SUM adds numeric non-NULL values.

SELECT SUM(Marks)
FROM Student;

Marks:

85 + 72 + 85 + 60 + 45 + 92 = 439

Result:

439

---

2.12 AVG

AVG returns the average of numeric non-NULL values.

SELECT AVG(Marks)
FROM Student;

Calculation:

439 / 6 = 73.166...

The displayed precision depends on the DBMS and data type.

NULL Behaviour

If one mark is NULL, AVG(Marks) normally divides by the number of non-NULL marks, not the total row count.

---

2.13 MAX and MIN

SELECT MAX(Marks), MIN(Marks)
FROM Student;

Result:

MAX = 92
MIN = 45

MAX and MIN can also work with dates and text under the DBMS’s ordering rules.

---

2.14 Aggregate Function Summary

Function	Result	Ignores NULL?
COUNT(*)	Number of rows	No; counts rows
COUNT(column)	Number of non-NULL values	Yes
COUNT(DISTINCT column)	Distinct non-NULL values	Yes
SUM(column)	Total	Yes
AVG(column)	Average	Yes
MAX(column)	Largest value	Yes
MIN(column)	Smallest value	Yes

---

3. SQL Clauses and Conditions

3.1 Basic SELECT Query Structure

A common query structure is:

SELECT column_list
FROM table_list
WHERE row_condition
GROUP BY grouping_columns
HAVING group_condition
ORDER BY sorting_columns;

Not every clause is required.

---

3.2 Logical Query Processing Order

Although SQL is written starting with SELECT, its logical processing is commonly understood in this order:

1. FROM
2. JOIN and ON
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY

Memory Mnemonic

Friendly Jaguars Wear Green Hats, Selecting Delicious Oranges

• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• ORDER BY

This explains why a select-list alias often cannot be used in WHERE: the WHERE step logically occurs before SELECT.

---

3.3 WHERE

WHERE filters individual rows before grouping.

Example

SELECT StudentName, Marks
FROM Student
WHERE Marks >= 80;

Result:

StudentName	Marks
Asha	85
Mohan	85
Kavya	92

---

3.4 WHERE Operators

Operator	Meaning
=	Equal
<> or !=	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
AND	Both conditions must be true
OR	At least one condition true
NOT	Reverses a condition

Example

SELECT *
FROM Student
WHERE Marks >= 70
  AND DeptID = 10;

Result:

• Asha
• Mohan
• Kavya

---

3.5 Operator Precedence

A common logical precedence is:

1. NOT
2. AND
3. OR

Use parentheses to make the intention clear.

Example

WHERE DeptID = 10
   OR DeptID = 20
  AND Marks >= 80

AND is evaluated before OR.

Clearer version:

WHERE (DeptID = 10 OR DeptID = 20)
  AND Marks >= 80

---

3.6 NULL Comparison

Do not compare NULL using:

DeptID = NULL

Use:

DeptID IS NULL

or:

DeptID IS NOT NULL

Example

SELECT StudentName
FROM Student
WHERE DeptID IS NULL;

Result:

Imran

Why?

NULL means unknown or missing. Ordinary equality with unknown does not produce TRUE.

---

3.7 DISTINCT

DISTINCT removes duplicate rows from the query result.

SELECT DISTINCT Marks
FROM Student;

Marks in the table:

85, 72, 85, 60, 45, 92

Distinct result:

85, 72, 60, 45, 92

Multiple Columns

SELECT DISTINCT DeptID, Marks
FROM Student;

DISTINCT applies to the complete selected combination, not separately to each column.

---

3.8 ORDER BY

ORDER BY sorts the result.

Ascending Order

SELECT StudentName, Marks
FROM Student
ORDER BY Marks ASC;

ASC is commonly the default.

Descending Order

SELECT StudentName, Marks
FROM Student
ORDER BY Marks DESC;

Multiple Sort Columns

SELECT StudentName, DeptID, Marks
FROM Student
ORDER BY DeptID ASC, Marks DESC;

The result is sorted by department first. Within each department, marks are sorted descending.

Important Point

Without ORDER BY, row order is not guaranteed.

---

3.9 LIKE

LIKE performs pattern matching on text.

Main wildcards:

Wildcard	Meaning
%	Zero or more characters
_	Exactly one character

Starts With A

WHERE StudentName LIKE 'A%'

Matches:

• Asha
• Amit
• Anjali

Ends With A

WHERE StudentName LIKE '%a'

Matches names ending in a.

Contains “av”

WHERE StudentName LIKE '%av%'

May match:

Ravi

Exactly Four Characters

WHERE StudentName LIKE '____'

Four underscores mean exactly four characters.

Second Character Is “a”

WHERE StudentName LIKE '_a%'

Examples:

• Ravi
• Kavya may not match because its second character is a, so it does match.
• Asha does not match because its second character is s.

Case Sensitivity

Case sensitivity depends on the DBMS, collation and operator used.

---

3.10 NOT LIKE

SELECT StudentName
FROM Student
WHERE StudentName NOT LIKE 'A%';

Returns names that do not begin with A, subject to NULL and collation rules.

---

3.11 IN

IN checks whether a value belongs to a list or subquery result.

SELECT *
FROM Student
WHERE DeptID IN (10, 20);

Equivalent basic condition:

WHERE DeptID = 10
   OR DeptID = 20

NOT IN

WHERE DeptID NOT IN (10, 20)

NULL Warning

NOT IN can produce unexpected results if the list or subquery contains NULL because SQL uses three-valued logic. NOT EXISTS is often safer for anti-matching queries.

---

3.12 BETWEEN

BETWEEN checks an inclusive range.

SELECT StudentName, Marks
FROM Student
WHERE Marks BETWEEN 60 AND 85;

Equivalent:

WHERE Marks >= 60
  AND Marks <= 85

Result includes both 60 and 85.

NOT BETWEEN

WHERE Marks NOT BETWEEN 60 AND 85

---

3.13 GROUP BY

GROUP BY combines rows with the same grouping values.

Count Students Per Department

SELECT DeptID, COUNT(*) AS StudentCount
FROM Student
GROUP BY DeptID;

Possible result:

DeptID	StudentCount
10	3
20	1
30	1
NULL	1

Rows with NULL grouping values normally form one group.

---

3.14 Aggregate Per Group

SELECT DeptID,
       AVG(Marks) AS AverageMarks,
       MAX(Marks) AS HighestMarks
FROM Student
GROUP BY DeptID;

For DeptID 10:

Marks = 85, 85, 92
Average = 262 / 3
Highest = 92

---

3.15 GROUP BY Rule

In a grouped query, a selected expression should generally be:

• A grouping column, or
• An aggregate expression

Correct:

SELECT DeptID, AVG(Marks)
FROM Student
GROUP BY DeptID;

Incorrect under standard grouping rules:

SELECT StudentName, DeptID, AVG(Marks)
FROM Student
GROUP BY DeptID;

StudentName is neither grouped nor aggregated.

Some DBMS modes may allow non-standard behaviour, but do not rely on it.

---

3.16 HAVING

HAVING filters groups after grouping.

Departments With More Than One Student

SELECT DeptID, COUNT(*) AS StudentCount
FROM Student
GROUP BY DeptID
HAVING COUNT(*) > 1;

Result:

DeptID	StudentCount
10	3

---

3.17 WHERE vs HAVING

WHERE	HAVING
Filters rows	Filters groups
Applied before grouping	Applied after grouping
Usually does not contain aggregate conditions	Commonly contains aggregate conditions
Can be used without GROUP BY	Often used with GROUP BY
Example: Marks >= 60	Example: AVG(Marks) >= 70

Example Using Both

SELECT DeptID, AVG(Marks) AS AverageMarks
FROM Student
WHERE Marks >= 50
GROUP BY DeptID
HAVING AVG(Marks) >= 75;

Processing:

1. WHERE removes rows with marks below 50.
2. Remaining rows are grouped by DeptID.
3. Average is calculated per group.
4. HAVING keeps groups with average at least 75.

Memory Rule

WHERE filters rows.
HAVING filters groups.

---

3.18 Complete Clause Example

SELECT DeptID,
       COUNT(*) AS StudentCount,
       AVG(Marks) AS AverageMarks
FROM Student
WHERE Marks >= 50
GROUP BY DeptID
HAVING COUNT(*) >= 2
ORDER BY AverageMarks DESC;

Meaning:

1. Consider students with marks at least 50.
2. Group them by department.
3. Calculate count and average.
4. Keep departments with at least two qualifying students.
5. Sort groups by average marks descending.

---

4. Joins and Subqueries

4.1 What Is a Join?

A join combines related rows from two or more tables.

We will use:

STUDENT

StudentID	StudentName	DeptID
1	Asha	10
2	Ravi	20
3	Mohan	10
4	Neha	30
5	Imran	NULL

DEPARTMENT

DeptID	DeptName
10	Computer
20	Science
40	Arts

Important observations:

• DeptID 10 matches two students.
• DeptID 20 matches one student.
• Student Neha has DeptID 30, but Department 30 is missing here.
• Imran has NULL DeptID.
• Arts department has no student.

---

4.2 INNER JOIN

An INNER JOIN returns only matching rows.

SELECT s.StudentName,
       d.DeptName
FROM Student AS s
INNER JOIN Department AS d
    ON s.DeptID = d.DeptID;

Result:

StudentName	DeptName
Asha	Computer
Ravi	Science
Mohan	Computer

Not included:

• Neha: no matching department 30
• Imran: NULL does not match
• Arts: no matching student

Diagram

STUDENT matching rows ∩ DEPARTMENT matching rows

---

4.3 Equi Join

An equi join uses equality as the join condition.

ON s.DeptID = d.DeptID

Most common inner joins are equi joins.

---

4.4 LEFT OUTER JOIN

A LEFT JOIN returns:

• All rows from the left table

• Matching rows from the right table

• NULL for missing right-side values

  SELECT s.StudentName, d.DeptName FROM Student AS s LEFT JOIN Department AS d ON s.DeptID = d.DeptID;

Result:

StudentName	DeptName
Asha	Computer
Ravi	Science
Mohan	Computer
Neha	NULL
Imran	NULL

Arts is not included because it exists only in the right table.

Memory Rule

LEFT JOIN = Keep every left row

---

4.5 RIGHT OUTER JOIN

A RIGHT JOIN returns:

• All rows from the right table

• Matching rows from the left table

• NULL for missing left-side values

  SELECT s.StudentName, d.DeptName FROM Student AS s RIGHT JOIN Department AS d ON s.DeptID = d.DeptID;

Result:

StudentName	DeptName
Asha	Computer
Mohan	Computer
Ravi	Science
NULL	Arts

Neha and Imran are not included because they exist only on the left without a match.

Memory Rule

RIGHT JOIN = Keep every right row

Portability Note

Some DBMS products, such as SQLite, historically had limited outer-join support, and query capabilities vary by version. A RIGHT JOIN can often be rewritten by swapping table positions and using LEFT JOIN.

---

4.6 FULL OUTER JOIN

A FULL OUTER JOIN returns:

• All matching rows

• All unmatched left rows

• All unmatched right rows

  SELECT s.StudentName, d.DeptName FROM Student AS s FULL OUTER JOIN Department AS d ON s.DeptID = d.DeptID;

Result:

StudentName	DeptName
Asha	Computer
Mohan	Computer
Ravi	Science
Neha	NULL
Imran	NULL
NULL	Arts

Memory Rule

FULL OUTER JOIN = Keep everything from both sides

Dialect Note

Some systems, including MySQL, do not directly support FULL OUTER JOIN. It can often be simulated with a combination of LEFT JOIN, RIGHT JOIN and UNION.

---

4.7 CROSS JOIN

A CROSS JOIN returns the Cartesian product.

Every row of the first table is combined with every row of the second table.

SELECT s.StudentName,
       d.DeptName
FROM Student AS s
CROSS JOIN Department AS d;

If:

Student has 5 rows
Department has 3 rows

Result rows:

5 × 3 = 15

Formula

CROSS JOIN rows = Left rows × Right rows

Example

Students:

Asha, Ravi

Departments:

Computer, Science

Result:

StudentName	DeptName
Asha	Computer
Asha	Science
Ravi	Computer
Ravi	Science

---

4.8 SELF JOIN

A self join joins a table to itself.

It requires table aliases to distinguish the two logical copies.

EMPLOYEE

EmpID	EmpName	ManagerID
1	Asha	NULL
2	Ravi	1
3	Mohan	1
4	Neha	2

Query

SELECT e.EmpName AS Employee,
       m.EmpName AS Manager
FROM Employee AS e
LEFT JOIN Employee AS m
    ON e.ManagerID = m.EmpID;

Result:

Employee	Manager
Asha	NULL
Ravi	Asha
Mohan	Asha
Neha	Ravi

Both e and m refer to EMPLOYEE, but they represent different roles.

---

4.9 Join Summary

Join	Result
INNER JOIN	Matching rows only
LEFT JOIN	All left rows plus matches
RIGHT JOIN	All right rows plus matches
FULL OUTER JOIN	All rows from both sides
CROSS JOIN	Every possible row combination
SELF JOIN	A table joined to itself

Mnemonic

INNER = Intersection
LEFT = Keep left
RIGHT = Keep right
FULL = Keep all
CROSS = Multiply rows
SELF = Same table twice

---

4.10 Finding Unmatched Rows

Students Without a Valid Department

SELECT s.StudentName
FROM Student AS s
LEFT JOIN Department AS d
    ON s.DeptID = d.DeptID
WHERE d.DeptID IS NULL;

Result:

• Neha
• Imran

Departments Without Students

SELECT d.DeptName
FROM Department AS d
LEFT JOIN Student AS s
    ON d.DeptID = s.DeptID
WHERE s.StudentID IS NULL;

Result:

Arts

---

4.11 ON vs WHERE in an Outer Join

This difference is important.

Condition in ON

SELECT s.StudentName, d.DeptName
FROM Student AS s
LEFT JOIN Department AS d
    ON s.DeptID = d.DeptID
   AND d.DeptName = 'Computer';

All students remain because it is a LEFT JOIN. Non-Computer or unmatched right-side values become NULL.

Condition in WHERE

SELECT s.StudentName, d.DeptName
FROM Student AS s
LEFT JOIN Department AS d
    ON s.DeptID = d.DeptID
WHERE d.DeptName = 'Computer';

Rows with NULL department names are removed by WHERE.

This makes the result behave like an inner match for that condition.

---

4B. Subqueries

4.12 What Is a Subquery?

A subquery is a query written inside another SQL statement.

It is also called:

• Inner query
• Nested query

The surrounding query is called the outer query.

Basic Example

SELECT StudentName, Marks
FROM Student
WHERE Marks > (
    SELECT AVG(Marks)
    FROM Student
);

The inner query calculates the average.

The outer query returns students scoring above that average.

---

4.13 Non-Correlated Subquery

A non-correlated subquery can execute independently of the outer query.

Example:

SELECT AVG(Marks)
FROM Student;

It does not use a value from the outer query.

Full Query

SELECT StudentName
FROM Student
WHERE Marks > (
    SELECT AVG(Marks)
    FROM Student
);

Conceptual process:

1. Execute inner query once.
2. Get average marks.
3. Use that result in the outer query.

---

4.14 Single-Row Subquery

A single-row subquery returns one row or one value.

Use operators such as:

• =
• >
• <
• >=
• <=
• <>

Example:

SELECT StudentName
FROM Student
WHERE Marks = (
    SELECT MAX(Marks)
    FROM Student
);

Returns the student or students whose marks equal the maximum.

Even though the subquery returns one value, the outer query may return multiple students if there is a tie.

---

4.15 Multi-Row Subquery

A multi-row subquery returns several values.

Use operators such as:

• IN
• ANY
• ALL
• EXISTS

Example Using IN

SELECT StudentName
FROM Student
WHERE DeptID IN (
    SELECT DeptID
    FROM Department
    WHERE City IN ('Jaipur', 'Kota')
);

---

4.16 ANY

ANY compares a value with at least one value returned by the subquery.

Example:

Marks > ANY (subquery)

Meaning:

Marks is greater than at least one returned value.

If the subquery returns:

60, 70, 80

Then:

Marks > ANY (...) 

is true for a mark greater than at least one of them. For ordinary numeric values, this is effectively greater than the minimum, subject to empty sets and NULL rules.

---

4.17 ALL

ALL compares a value with every value returned by the subquery.

Example:

Marks > ALL (subquery)

If the subquery returns:

60, 70, 80

the mark must be greater than all values, effectively greater than 80 under ordinary non-NULL conditions.

ANY vs ALL

Operator	Meaning
> ANY	Greater than at least one returned value
> ALL	Greater than every returned value
= ANY	Similar to IN
< ALL	Less than every returned value

---

4.18 EXISTS

EXISTS is true if a subquery returns at least one row.

Departments That Have Students

SELECT d.DeptName
FROM Department AS d
WHERE EXISTS (
    SELECT 1
    FROM Student AS s
    WHERE s.DeptID = d.DeptID
);

The subquery is correlated because it refers to d.DeptID.

---

4.19 NOT EXISTS

NOT EXISTS is true if the subquery returns no rows.

Departments Without Students

SELECT d.DeptName
FROM Department AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM Student AS s
    WHERE s.DeptID = d.DeptID
);

Result:

Arts

---

4.20 Correlated Subquery

A correlated subquery refers to a column from the outer query.

It cannot be evaluated independently in the same way as a non-correlated subquery.

Example: Students Above Their Department Average

SELECT s.StudentName,
       s.Marks,
       s.DeptID
FROM Student AS s
WHERE s.Marks > (
    SELECT AVG(s2.Marks)
    FROM Student AS s2
    WHERE s2.DeptID = s.DeptID
);

The inner query uses:

s.DeptID

from the outer row.

Conceptual Process

For each outer student:

1. Read the student’s department.
2. Calculate that department’s average.
3. Compare the student’s marks with that average.
4. Keep the student if marks are higher.

Modern query optimizers may transform the execution, but this is the correct logical understanding.

---

4.21 Correlated vs Non-Correlated Subquery

Non-Correlated	Correlated
Independent of outer query	Refers to outer-query columns
Can run by itself	Depends on the outer row
Conceptually runs once	Conceptually evaluated for outer rows
Usually simpler	Often more expensive
Example: compare with overall average	Example: compare with department average

---

4.22 JOIN vs Subquery

JOIN	Subquery
Combines tables in one table expression	Places one query inside another
Often used to retrieve columns from related tables	Often used for filtering or calculated comparisons
May be clearer for direct relationships	May be clearer for existence or aggregate tests
Optimizer may produce similar plans	Optimizer may transform into a join

There is no universal rule that one is always faster. Performance depends on:

• DBMS optimizer
• Indexes
• Data size
• Query form
• Statistics

---

5. Transactions, ACID, Serializability, Locks, 2PL and NoSQL

5A. Transactions

5.1 What Is a Transaction?

A transaction is a sequence of database operations treated as one logical unit.

Example bank transfer:

Read
Account A balance
    │
    v
Subtract ₹500 from Account A
    │
    v
Read Account B balance
    │
    v
Add ₹500 to Account B
    │
    v
Commit both changes

The two balance changes form one transaction.

If the first update succeeds but the second fails, the complete transfer should be rolled back. Otherwise, money would disappear from one account without reaching the other.

---

5.2 Transaction Boundaries

A transaction has:

• A beginning
• One or more database operations
• A successful COMMIT, or
• An unsuccessful ROLLBACK

Conceptual example:

BEGIN TRANSACTION;

UPDATE Account
SET Balance = Balance - 500
WHERE AccountID = 1;

UPDATE Account
SET Balance = Balance + 500
WHERE AccountID = 2;

COMMIT;

The exact command used to begin a transaction differs among DBMS products.

Common forms include:

BEGIN;
BEGIN TRANSACTION;
START TRANSACTION;

---

5.3 Read and Write Operations

Transaction theory often uses two basic operations.

Read Operation

read(X)

This reads data item X from the database into the transaction’s local memory.

Write Operation

write(X)

This writes the transaction’s updated value of X back to the database.

Example

A transaction adds 100 to balance X:

read(X)
X = X + 100
write(X)

---

5B. ACID Properties

5.4 What Is ACID?

ACID describes four important transaction properties:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

Mnemonic

ACID

• All or nothing
• Correct state
• Independent execution
• Data survives

---

5.5 Atomicity

Atomicity means a transaction is treated as one indivisible unit.

Either:

• All operations succeed, or
• None of them takes effect

Bank Transfer Example

Transaction:

1. Deduct ₹500 from A.
2. Add ₹500 to B.

If step 2 fails, step 1 must also be undone.

Diagram

Transaction operations
    │
    ├── All successful ───> COMMIT
    │
    └── Any failure ──────> ROLLBACK

Memory Rule

Atomicity = All or nothing

---

5.6 Consistency

Consistency means a transaction moves the database from one valid state to another valid state while preserving defined rules.

Examples of rules:

• Balance cannot violate a defined constraint.
• Primary key values must remain unique.
• Foreign keys must reference valid rows.
• Total money should remain correct during a transfer.
• Marks must remain between 0 and 100.

Example

Before transfer:

Account A = ₹2,000
Account B = ₹1,000
Total = ₹3,000

After transferring ₹500:

Account A = ₹1,500
Account B = ₹1,500
Total = ₹3,000

The total remains consistent.

Important Point

The DBMS enforces declared constraints, while correct transaction logic is also the application programmer’s responsibility.

---

5.7 Isolation

Isolation means concurrent transactions should not interfere in a way that produces an incorrect result.

Each transaction should behave as if it were executing alone, according to the selected isolation level.

Example

Two users update the same bank balance at the same time.

Without isolation, one user’s change may overwrite the other’s change.

Isolation is supported through techniques such as:

• Locks
• Timestamps
• Multiversion Concurrency Control
• Serializable execution

MVCC stands for Multiversion Concurrency Control. It keeps multiple versions of data to help transactions read consistent versions.

Memory Rule

Isolation = Concurrent transactions appear independent

---

5.8 Durability

Durability means that once a transaction commits, its changes survive later failures.

After commit, data should remain available even if:

• Power fails
• The DBMS restarts
• The operating system crashes

Durability may use:

• Transaction logs
• Write-ahead logging
• Stable storage
• Checkpoints
• Backups and recovery mechanisms

Memory Rule

Durability = Committed data survives

---

5.9 ACID Comparison

Property	Meaning	Failure Prevented
Atomicity	All operations or none	Partial transaction
Consistency	Valid state to valid state	Rule violations
Isolation	Concurrent transactions do not improperly interfere	Incorrect concurrent results
Durability	Committed changes survive	Loss after commit

Bank Transfer Mapping

Transfer Requirement	ACID Property
Both debit and credit happen together	Atomicity
Total money remains valid	Consistency
Other transfers do not interfere incorrectly	Isolation
Completed transfer survives a crash	Durability

---

5C. Transaction States

5.10 Main Transaction States

A transaction may move through these states:

1. Active
2. Partially committed
3. Committed
4. Failed
5. Aborted
6. Terminated

---

5.11 Active State

The transaction is executing its operations.

Examples:

• Reading rows

• Performing calculations

• Updating rows

  Start → Active

---

5.12 Partially Committed State

The final statement has executed, but the system has not yet guaranteed that the transaction’s effects are permanently recorded.

Active → Final operation completed → Partially committed

A failure can still occur before complete commit processing finishes.

---

5.13 Committed State

The transaction has completed successfully, and its changes are durable.

Partially committed → Committed

---

5.14 Failed State

The transaction cannot continue.

Possible causes:

• Constraint violation

• Deadlock victim selection

• Hardware failure

• Software error

• Invalid input

• Explicit cancellation

  Active or partially committed → Failed

---

5.15 Aborted State

After failure, the transaction’s changes are rolled back.

The database is restored to the state before that transaction began, as required by atomicity.

From aborted state, the transaction may:

• Restart, or
• End permanently

---

5.16 Terminated State

After commit or abort processing is complete, the transaction leaves the system.

---

5.17 Transaction State Diagram

┌─────────┐
│ Active  │
└────┬────┘
     │ Final statement executed
     v
┌─────────────────────┐
│ Partially Committed │
└──────┬──────────────┘
       │ Commit succeeds
       v
  ┌───────────┐
  │ Committed │
  └─────┬─────┘
        │
        v
  ┌────────────┐
  │ Terminated │
  └────────────┘

Failure path:

Active or Partially Committed
            │
            v
       ┌────────┐
       │ Failed │
       └───┬────┘
           │ Rollback
           v
      ┌─────────┐
      │ Aborted │
      └────┬────┘
           │
      ┌────┴─────┐
      │          │
      v          v
   Restart   Terminated

---

5D. Schedules and Serializability

5.18 What Is a Schedule?

A schedule is the order in which operations from one or more transactions are executed.

Suppose:

Transaction T1:

read(X)
X = X + 10
write(X)

Transaction T2:

read(X)
X = X * 2
write(X)

A schedule decides how T1 and T2 operations are ordered.

---

5.19 Serial Schedule

A serial schedule completes one transaction before starting another.

Example

T1: read(X)
T1: write(X)
T1: commit

T2: read(X)
T2: write(X)
T2: commit

No interleaving occurs.

Advantages

• Simple
• Easy to understand
• Naturally avoids many concurrency problems

Limitation

• Low concurrency
• Resources may be underused
• Lower performance for many users

---

5.20 Non-Serial Schedule

A non-serial schedule interleaves operations from multiple transactions.

Example:

T1: read(X)
T2: read(Y)
T1: write(X)
T2: write(Y)
T1: commit
T2: commit

Interleaving can improve performance, but it must preserve correctness.

---

5.21 Serializable Schedule

A serializable schedule is a non-serial schedule whose effect is equivalent to some serial schedule.

It provides concurrency while preserving the correctness of serial execution.

Important Relationship

Every serial schedule is serializable.

But:

A serializable schedule does not have to be serial.

---

5.22 Types of Serializability

The main theoretical types are:

1. Conflict serializability
2. View serializability

Conflict serializability is more commonly tested.

---

5.23 Conflicting Operations

Two operations conflict if:

1. They belong to different transactions.
2. They access the same data item.
3. At least one operation is a write.

Conflict Table

Operation Pair on Same Item	Conflict?
Read–Read	No
Read–Write	Yes
Write–Read	Yes
Write–Write	Yes

Memory Rule

Different transactions
+ Same item
+ At least one write
= Conflict

---

5.24 Conflict Equivalent Schedules

Two schedules are conflict equivalent if:

• They contain the same operations, and
• The relative order of every conflicting pair is the same

A schedule is conflict serializable if it is conflict equivalent to a serial schedule.

---

5.25 Precedence Graph

A precedence graph, also called a serialization graph, is used to test conflict serializability.

Steps

1. Create one node for each transaction.

2. Find conflicting operations.

3. If an operation of Ti occurs before a conflicting operation of Tj, draw:

   Ti → Tj
   

4. Check for a cycle.

Rule

• No cycle → Conflict serializable
• Cycle → Not conflict serializable

---

5.26 Precedence Graph Example 1

Schedule:

r1(X)
w1(X)
r2(X)
w2(X)

Conflicts place T1 before T2.

Graph:

T1 ───> T2

There is no cycle.

Therefore, the schedule is conflict serializable.

Equivalent serial order:

T1, then T2

---

5.27 Precedence Graph Example 2

Schedule:

r1(X)
w1(X)
r2(X)
w2(Y)
r1(Y)

Conflicts:

• On X, T1’s write occurs before T2’s read:

  T1 → T2
  

• On Y, T2’s write occurs before T1’s read:

  T2 → T1
  

Graph:

T1 ───> T2
 ^       │
 └───────┘

A cycle exists.

Therefore, the schedule is not conflict serializable.

---

5E. Concurrency Problems

5.28 Lost Update

A lost update occurs when one transaction’s update is overwritten by another transaction.

Initial balance:

X = 1000

Transactions:

T1 adds 100
T2 subtracts 50

Problem schedule:

T1 reads X = 1000
T2 reads X = 1000
T1 calculates 1100
T2 calculates 950
T1 writes 1100
T2 writes 950

Final value:

950

Correct serial result should be:

1000 + 100 - 50 = 1050

T1’s update is lost.

---

5.29 Dirty Read

A dirty read occurs when one transaction reads data written by another transaction that has not committed.

Example:

T1 writes Balance = 500
T2 reads Balance = 500
T1 rolls back

T2 used a value that never became permanent.

Dirty read is also called:

• Uncommitted dependency
• Temporary update problem

---

5.30 Non-Repeatable Read

A non-repeatable read occurs when a transaction reads the same row twice and gets different values because another transaction committed an update between the reads.

Example:

T1 reads Marks = 80
T2 updates Marks = 90 and commits
T1 reads Marks again = 90

The same row produced two different values inside T1.

---

5.31 Phantom Read

A phantom read occurs when repeating a query returns a different set of rows because another transaction inserted or deleted matching rows.

Example:

T1 executes:

SELECT COUNT(*)
FROM Student
WHERE Marks >= 80;

Result:

5

T2 inserts a student with Marks 90 and commits.

T1 runs the same query again:

Result = 6

The additional matching row is a phantom.

---

5.32 Incorrect Summary

An incorrect summary occurs when one transaction calculates an aggregate while another transaction updates some of the same data.

The summary may combine old and new values.

Example:

• T1 calculates total account balance.
• T2 transfers money between accounts during the calculation.
• T1 may count one side before transfer and the other side after transfer.

---

5.33 Concurrency Anomaly Summary

Problem	Meaning
Lost update	One update overwrites another
Dirty read	Reads uncommitted data
Non-repeatable read	Same row gives different committed values
Phantom read	Repeated query returns a different row set
Incorrect summary	Aggregate mixes inconsistent versions

---

5F. Locks

5.34 What Is a Lock?

A lock is a control mechanism that restricts concurrent access to a database item.

Before reading or writing a data item, a transaction may need to obtain a suitable lock.

Locks help maintain:

• Isolation
• Consistency
• Serializability

---

5.35 Binary Lock

A binary lock has two states:

• Locked
• Unlocked

It is simple but does not distinguish reading from writing.

---

5.36 Shared Lock

A shared lock, also called an S-lock or read lock, is used for reading.

Several transactions may usually hold shared locks on the same item at the same time.

Example:

T1 holds S-lock on X.
T2 may also receive S-lock on X.

Both transactions can read X.

---

5.37 Exclusive Lock

An exclusive lock, also called an X-lock or write lock, is used for writing.

When one transaction holds an X-lock:

• Other transactions cannot obtain an S-lock on that item.
• Other transactions cannot obtain an X-lock on that item.

Example:

T1 holds X-lock on X.
T2 must wait before reading or writing X.

---

5.38 Lock Compatibility Matrix

Existing Lock	Requested S-lock	Requested X-lock
S-lock	Compatible	Not compatible
X-lock	Not compatible	Not compatible

Memory Rule

Read + Read = Allowed
Read + Write = Wait
Write + Read = Wait
Write + Write = Wait

---

5.39 Lock Granularity

Granularity means the size of the data item being locked.

Possible levels:

• Database
• Table
• Page or block
• Row
• Field

Coarse-Grained Lock

Example:

Table lock

Advantages:

• Fewer locks
• Lower lock-management overhead

Disadvantages:

• Less concurrency

Fine-Grained Lock

Example:

Row lock

Advantages:

• More concurrency

Disadvantages:

• More locks
• Higher management overhead

---

5.40 Lock Conversion

Upgrade

A transaction may change:

S-lock → X-lock

when it needs to update an item it previously read.

Downgrade

A transaction may change:

X-lock → S-lock

when it no longer needs write access.

Lock conversion must follow the concurrency-control protocol.

---

5G. Two-Phase Locking

5.41 What Is 2PL?

2PL stands for Two-Phase Locking.

A transaction following 2PL has two phases:

1. Growing phase
2. Shrinking phase

---

5.42 Growing Phase

During the growing phase:

• The transaction may acquire locks.

• The transaction may upgrade locks.

• It cannot release locks.

  Acquire locks Acquire more locks Upgrade if permitted No unlock

---

5.43 Lock Point

The lock point is the moment when a transaction obtains its final lock.

After the lock point, it enters the shrinking phase.

---

5.44 Shrinking Phase

During the shrinking phase:

• The transaction may release locks.

• The transaction may downgrade locks.

• It cannot acquire new locks.

  Release locks Release more locks No new lock

---

5.45 2PL Diagram

Transaction begins
        │
        v
┌───────────────────┐
│ Growing Phase     │
│ Acquire locks     │
│ No release        │
└─────────┬─────────┘
          │ Lock point
          v
┌───────────────────┐
│ Shrinking Phase   │
│ Release locks     │
│ No new lock       │
└─────────┬─────────┘
          │
          v
    Transaction ends

Main Result

Basic 2PL guarantees conflict serializability when followed correctly.

Limitation

Basic 2PL can still cause:

• Deadlocks
• Cascading rollback in some forms

---

5.46 Example of Valid 2PL

lock-S(A)
read(A)
lock-X(B)
write(B)
unlock(A)
unlock(B)

Phases:

Growing:

lock-S(A)
lock-X(B)

Shrinking:

unlock(A)
unlock(B)

After the first unlock, no new lock is acquired.

Therefore, this follows 2PL.

---

5.47 Example That Violates 2PL

lock-S(A)
read(A)
unlock(A)
lock-X(B)
write(B)

After releasing A, the transaction acquires a new lock on B.

This violates 2PL because it returns to lock acquisition after shrinking began.

---

5.48 Types of 2PL

Basic 2PL

Follows growing and shrinking phases.

Guarantees conflict serializability.

May allow deadlocks and cascading rollbacks.

---

Conservative or Static 2PL

The transaction obtains all required locks before it begins.

If all locks are not available, it waits without acquiring them.

Advantages:

• Prevents deadlock caused by lock acquisition

Limitations:

• Transaction must know all required locks in advance.
• Concurrency may be lower.

---

Strict 2PL

All exclusive locks are held until the transaction commits or aborts.

Advantages:

• Prevents dirty reads of written data
• Avoids cascading rollback caused by reading uncommitted writes
• Simplifies recovery
• Produces strict schedules

---

Rigorous 2PL

All shared and exclusive locks are held until commit or abort.

It is stricter than strict 2PL.

---

5.49 2PL Type Comparison

Type	Main Rule
Basic 2PL	Acquire first, then release; no new locks after first release
Conservative 2PL	Obtain all locks before execution
Strict 2PL	Hold all X-locks until commit or abort
Rigorous 2PL	Hold all S-locks and X-locks until commit or abort

---

5H. Deadlock

5.50 What Is Deadlock?

A deadlock occurs when transactions wait for one another indefinitely.

Example

T1 locks A.
T2 locks B.
T1 requests B and waits for T2.
T2 requests A and waits for T1.

Wait cycle:

T1 waits for T2
    ^         │
    │         v
    └─────── T2

Neither can continue.

Important Point

2PL can guarantee conflict serializability, but basic and strict 2PL do not automatically eliminate deadlocks.

---

5.51 Deadlock Handling

Common methods:

• Prevention
• Avoidance
• Detection and recovery
• Timeout

Detection

A wait-for graph can be used.

• Node = Transaction
• Edge Ti → Tj = Ti waits for Tj
• Cycle = Deadlock

Recovery

The DBMS may:

• Select a victim transaction
• Roll it back
• Release its locks
• Restart it later

---

5I. Recovery Concepts

5.52 Cascading Rollback

A cascading rollback occurs when one transaction’s failure forces other transactions to roll back because they read its uncommitted data.

Example:

T1 writes X.
T2 reads uncommitted X.
T1 aborts.
T2 must also abort.

Strict 2PL helps prevent this problem by holding exclusive locks until commit or abort.

---

5.53 Recoverable Schedule

A schedule is recoverable if a transaction commits only after the transaction whose data it read has committed.

If T2 reads a value written by T1:

T1 must commit before T2 commits.

---

5.54 Cascadeless Schedule

A cascadeless schedule allows a transaction to read a value only after the transaction that wrote it has committed.

This prevents cascading rollback.

---

5.55 Strict Schedule

In a strict schedule, no transaction may read or write a data item written by an uncommitted transaction.

Strict schedules simplify recovery.

Relationship

A commonly taught relationship is:

Strict
   ↓
Cascadeless
   ↓
Recoverable

A strict schedule is cascadeless, and a cascadeless schedule is recoverable.

---

5J. NoSQL Basics

5.56 What Is NoSQL?

NoSQL commonly means Not Only SQL.

NoSQL databases are non-relational or not strictly table-centered database systems designed for needs such as:

• Flexible data structures
• Large distributed datasets
• High scalability
• High availability
• Rapidly changing schemas
• Specialized access patterns

Important Point

NoSQL does not mean that SQL is always completely absent. Some NoSQL products provide SQL-like query languages.

---

5.57 Why Use NoSQL?

NoSQL may be useful when an application needs:

• Horizontal scaling
• Flexible or evolving records
• High write throughput
• Distributed storage
• Very large datasets
• Graph traversal
• Simple key-based access

Horizontal Scaling

Add more machines to share the workload.

Server 1 + Server 2 + Server 3

Vertical Scaling

Increase the power of one machine.

More CPU
More RAM
Faster storage

NoSQL systems are often designed with horizontal scaling in mind, although relational systems can also scale horizontally.

---

5.58 Main NoSQL Types

The four commonly taught types are:

1. Key-value database
2. Document database
3. Wide-column database
4. Graph database

The roadmap specifically emphasizes:

• Key-value
• Document
• Graph

---

5.59 Key-Value Database

A key-value database stores each value using a unique key.

Structure

Key            Value
--------       -------------------
user:101   →   "Asha"
cart:501   →   Product list
session:X  →   Session information

Diagram

Key ───> Value

Advantages

• Simple
• Fast lookup by key
• Easy to distribute
• Useful for caching and sessions

Applications

• Cache
• User sessions
• Shopping carts
• Configuration data
• Real-time counters

Examples

• Redis
• Amazon DynamoDB
• Riak

DynamoDB can also support document-like structures.

---

5.60 Document Database

A document database stores records as documents.

Documents are commonly represented in JSON-like or BSON formats.

Example Document

{
    "studentId": 101,
    "name": "Asha",
    "marks": 85,
    "skills": ["Python", "SQL"],
    "address": {
        "city": "Jaipur",
        "state": "Rajasthan"
    }
}

Features

• Flexible document structure
• Nested objects and arrays
• Different documents may have different fields
• Natural for application objects

Applications

• Content management
• Product catalogues
• User profiles
• Mobile applications
• Web applications

Examples

• MongoDB
• CouchDB

---

5.61 Wide-Column Database

A wide-column database organizes data using row keys and column families.

Different rows may contain different columns.

Conceptual Example

Row Key: Student101
Personal:Name = Asha
Academic:Marks = 85
Contact:City = Jaipur

Applications

• Very large distributed datasets
• Time-series data
• Event data
• Large-scale analytics

Examples

• Apache Cassandra
• Apache HBase

---

5.62 Graph Database

A graph database stores:

• Nodes
• Edges
• Properties

Node

Represents an entity.

Examples:

• Person
• Product
• City

Edge

Represents a relationship.

Examples:

• FRIEND_OF
• PURCHASED
• LOCATED_IN

Property

Stores information about a node or edge.

Diagram

(Asha)
   │ FRIEND_OF
   v
(Ravi)
   │ PURCHASED
   v
(Laptop)

Applications

• Social networks
• Recommendation systems
• Fraud detection
• Route planning
• Knowledge graphs
• Network analysis

Examples

• Neo4j
• Amazon Neptune

---

5.63 NoSQL Type Comparison

Type	Structure	Best For	Example
Key-value	Key → Value	Cache, sessions	Redis
Document	JSON-like documents	Flexible application data	MongoDB
Wide-column	Column families	Large distributed data	Cassandra
Graph	Nodes and edges	Relationship-heavy data	Neo4j

Mnemonic

KDWG

• Key-value
• Document
• Wide-column
• Graph

Memory sentence:

Keys Describe Wide Graphs

---

5K. SQL vs NoSQL

5.64 Main Comparison

Basis	SQL Database	NoSQL Database
Main Model	Relational tables	Key-value, document, column or graph
Schema	Usually predefined and structured	Often flexible
Relationships	Foreign keys and joins	Embedded data or model-specific links
Query Language	SQL	Product-specific APIs or query languages
Normalization	Common	Denormalization is common
Transactions	Strong ACID support is traditional	Product-dependent; many now support transactions
Scaling	Traditionally vertical, also horizontal options	Often designed for horizontal scaling
Best Use	Structured data and complex relational queries	Flexible, distributed or specialized data
Examples	PostgreSQL, Oracle, MySQL	MongoDB, Redis, Cassandra, Neo4j

---

5.65 SQL Database Advantages

• Strong relational model
• Standardized query language
• Joins
• Integrity constraints
• Mature transaction support
• Good for structured data
• Good for reporting and complex queries

---

5.66 NoSQL Advantages

• Flexible schemas
• Easy storage of nested data
• Horizontal scalability
• High throughput in suitable workloads
• Specialized models for specific problems
• Useful for rapidly changing application data

---

5.67 SQL and NoSQL Limitations

SQL Limitations	NoSQL Limitations
Schema changes may require planning	Less standardization across products
Object-to-table mapping may be complex	Joins may be limited or model-specific
Distributed scaling can be complex	Data duplication may be common
Less natural for some graph operations	Consistency guarantees vary
Nested data may require several tables	Product-specific knowledge required

---

5.68 When to Choose Which?

Prefer SQL When

• Data has clear relationships.
• Strong integrity constraints are important.
• Complex joins are frequent.
• ACID transactions are central.
• Reporting queries are important.

Examples:

• Banking
• Accounting
• Inventory
• Student administration
• Payroll

Prefer NoSQL When

• Structure changes frequently.
• Data is naturally document-shaped.
• Very high horizontal scalability is required.
• Key-based lookup dominates.
• Graph relationships dominate.

Examples:

• Cache
• Session store
• Social graph
• Product catalogue
• Content platform

Important Exam Point

No database type is always better.

The correct choice depends on:

• Data structure
• Consistency needs
• Query pattern
• Scale
• Performance
• Development requirements

---

5.69 CAP Theorem — Introductory Note

The CAP theorem discusses distributed systems.

It focuses on:

• Consistency
• Availability
• Partition tolerance

Consistency

All clients see a consistent view according to the system’s consistency model.

Availability

Every request receives a non-error response, though it may not contain the newest data under some models.

Partition Tolerance

The system continues operating despite communication failures between network groups.

Basic Exam Understanding

During a network partition, a distributed system cannot simultaneously guarantee both perfect consistency and full availability in the strict CAP sense.

Do not confuse CAP consistency with ACID consistency. They use the same word in different contexts.

---

🔑 Quick Revision Sheet

SQL Command Categories

Category	Purpose	Commands
DDL	Defines structure	CREATE, ALTER, DROP, TRUNCATE
DML	Changes rows	INSERT, UPDATE, DELETE
DQL	Retrieves rows	SELECT
DCL	Controls permissions	GRANT, REVOKE
TCL	Controls transactions	COMMIT, ROLLBACK, SAVEPOINT

Mnemonic:

DDL = Design
DML = Modify
DQL = Query
DCL = Control
TCL = Transaction

---

DELETE vs TRUNCATE vs DROP

Command	Removes	Structure Remains?	WHERE?
DELETE	Selected or all rows	Yes	Yes
TRUNCATE	All rows	Yes	No
DROP	Complete object	No	No

---

Aggregate Functions

Function	Purpose
COUNT(*)	Counts rows
COUNT(column)	Counts non-NULL values
SUM	Total
AVG	Average of non-NULL values
MAX	Highest
MIN	Lowest

---

Query Clauses

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Logical order:

FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY

---

WHERE vs HAVING

WHERE = Filters rows before grouping
HAVING = Filters groups after grouping

---

Pattern and Range Operators

Operator	Meaning
% in LIKE	Zero or more characters
_ in LIKE	Exactly one character
IN	Matches a list or subquery result
BETWEEN	Inclusive range
IS NULL	Tests NULL
DISTINCT	Removes duplicate result rows

---

Joins

Join	Keeps
INNER	Matching rows
LEFT	Every left row
RIGHT	Every right row
FULL OUTER	Every row from both tables
CROSS	Every combination
SELF	Same table joined to itself

Formula:

CROSS JOIN rows = Left rows × Right rows

---

Subqueries

Type	Meaning
Non-correlated	Independent of outer query
Correlated	Uses outer-query value
Single-row	Returns one row/value
Multi-row	Returns multiple values
EXISTS	True if at least one row exists
NOT EXISTS	True if no row exists

---

ACID

Property	Memory Rule
Atomicity	All or nothing
Consistency	Correct state
Isolation	Independent concurrent execution
Durability	Committed data survives

---

Transaction States

Active
   │
   v
Partially committed
   │
   v
Committed
   │
   v
Terminated

Failure path:

Active → Failed → Aborted → Restart or Terminated

---

Serializability

Serial schedule:
One complete transaction at a time

Serializable schedule:
Same effect as some serial order

Conflict:

Different transactions
+ Same data item
+ At least one write

Precedence graph:

No cycle = Conflict serializable
Cycle = Not conflict serializable

---

Locks

Lock	Purpose	Compatibility
Shared or S-lock	Read	Compatible with other S-locks
Exclusive or X-lock	Write	Not compatible with S or X

---

Two-Phase Locking

Growing:
Acquire locks; release none

Lock point:
Final lock acquired

Shrinking:
Release locks; acquire none

Types:

Type	Rule
Basic	Growing then shrinking
Conservative	Obtain all locks before execution
Strict	Hold X-locks until commit/abort
Rigorous	Hold S and X locks until commit/abort

---

NoSQL

Type	Structure	Example
Key-value	Key → Value	Redis
Document	JSON-like documents	MongoDB
Wide-column	Column families	Cassandra
Graph	Nodes and edges	Neo4j

---

📝 Practice Questions

Questions 1–5: SQL Commands

Question 1

Which command changes the structure of an existing table?

A. UPDATE
B. ALTER
C. INSERT
D. COMMIT

---

Question 2

Which command removes a table’s definition and data?

A. DELETE
B. TRUNCATE
C. DROP
D. ROLLBACK

---

Question 3

What happens if this statement is executed without a WHERE clause?

UPDATE Student
SET Marks = 100;

A. Only the first row is updated
B. Every row is updated
C. The table is dropped
D. No row is updated

---

Question 4

Which command gives a user permission?

A. REVOKE
B. GRANT
C. ROLLBACK
D. SAVEPOINT

---

Question 5

Which command permanently accepts the current transaction’s changes?

A. COMMIT
B. DELETE
C. DROP
D. REVOKE

---

Questions 6–10: Functions and Clauses

Question 6

Which expression counts every row, regardless of NULL values in particular columns?

A. COUNT(column)
B. COUNT(*)
C. SUM(*)
D. AVG(*)

---

Question 7

Which clause filters rows before grouping?

A. HAVING
B. ORDER BY
C. WHERE
D. DISTINCT

---

Question 8

Which clause filters groups after aggregate calculation?

A. WHERE
B. HAVING
C. FROM
D. DISTINCT

---

Question 9

What does this condition include?

Marks BETWEEN 60 AND 80

A. Values above 60 and below 80 only
B. Values from 60 to 80, including both boundaries
C. Only 60 and 80
D. Values below 60 or above 80

---

Question 10

Which pattern matches names whose second character is a?

A. 'a%'
B. '%a'
C. '_a%'
D. '%a%'

---

Questions 11–15: Joins and Subqueries

Use these row counts for Question 11:

Table A has 4 rows.
Table B has 3 rows.

Question 11

How many rows does A CROSS JOIN B return?

A. 1
B. 7
C. 12
D. 43

---

Question 12

Which join returns all rows from the left table and matching rows from the right table?

A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. CROSS JOIN

---

Question 13

Which join is used when an EMPLOYEE table is joined to itself to find managers?

A. CROSS JOIN only
B. FULL JOIN only
C. SELF JOIN
D. Natural deletion

---

Question 14

A subquery that refers to a column from the outer query is:

A. A correlated subquery
B. A DDL command
C. A view only
D. A transaction log

---

Question 15

Which operator is true when a subquery returns at least one row?

A. BETWEEN
B. EXISTS
C. DISTINCT
D. DROP

---

Questions 16–20: Transactions and Concurrency

Question 16

Which ACID property means all operations of a transaction occur or none occur?

A. Atomicity
B. Consistency
C. Isolation
D. Durability

---

Question 17

Which ACID property means committed changes survive a system restart?

A. Atomicity
B. Consistency
C. Isolation
D. Durability

---

Question 18

A transaction reads data written by another transaction that has not committed. This is:

A. Phantom read
B. Dirty read
C. Serial schedule
D. Durability

---

Question 19

Which pair of locks can normally be held by two transactions on the same item at the same time?

A. X and X
B. S and X
C. S and S
D. X and S

---

Question 20

Under basic 2PL, after a transaction releases its first lock, it:

A. May acquire any number of new locks
B. Cannot acquire a new lock
C. Must drop the table
D. Must become a DBA

---

Questions 21–25: Serializability and NoSQL

Question 21

A precedence graph has no cycle. The schedule is:

A. Not executable
B. Conflict serializable
C. Always deadlocked
D. A dirty read

---

Question 22

Which 2PL version holds all exclusive locks until commit or abort?

A. Basic 2PL only
B. Conservative 2PL
C. Strict 2PL
D. No-lock protocol

---

Question 23

Which NoSQL type stores data as a key and an associated value?

A. Graph
B. Key-value
C. Relational-only
D. Hierarchical file

---

Question 24

Which database type is best suited to highly connected data such as social-network relationships?

A. Graph database
B. Key-value cache only
C. Flat text file
D. Spreadsheet only

---

Question 25

Which statement correctly compares SQL and NoSQL?

A. SQL databases cannot support transactions.
B. NoSQL always means no query language.
C. SQL commonly uses relational tables, while NoSQL includes document, key-value and graph models.
D. NoSQL databases never support consistency.

---

✅ Answer Key + Explanations

Answer Key

Q.	Answer	Q.	Answer	Q.	Answer	Q.	Answer	Q.	Answer
1	B	6	B	11	C	16	A	21	B
2	C	7	C	12	B	17	D	22	C
3	B	8	B	13	C	18	B	23	B
4	B	9	B	14	A	19	C	24	A
5	A	10	C	15	B	20	B	25	C

---

Direct Explanations

1. Answer: B — ALTER

ALTER changes the structure of an existing table.

Example:

ALTER TABLE Student
ADD Email VARCHAR(100);

UPDATE changes row values, not the table definition.

---

2. Answer: C — DROP

DROP TABLE removes:

• Table data

• Table structure

• Table definition

  DROP TABLE Student;

TRUNCATE removes all rows but keeps the table structure.

---

3. Answer: B — Every row is updated

Without a WHERE clause:

UPDATE Student
SET Marks = 100;

changes Marks to 100 in every row.

Always test the intended condition using SELECT before a major update.

---

4. Answer: B — GRANT

GRANT gives privileges.

Example:

GRANT SELECT
ON Student
TO teacher_user;

REVOKE removes privileges.

---

5. Answer: A — COMMIT

COMMIT accepts and makes the current transaction’s changes permanent according to the DBMS’s transaction guarantees.

ROLLBACK cancels uncommitted changes.

---

6. Answer: B — COUNT(*)

COUNT(*) counts rows.

COUNT(column) counts only non-NULL values in that column.

Example:

Five rows, one NULL DeptID

COUNT(*)      = 5
COUNT(DeptID) = 4

---

7. Answer: C — WHERE

WHERE filters individual rows before grouping.

Example:

WHERE Marks >= 60

---

8. Answer: B — HAVING

HAVING filters groups after GROUP BY.

Example:

GROUP BY DeptID
HAVING AVG(Marks) >= 75

Memory rule:

WHERE → Rows
HAVING → Groups

---

9. Answer: B — Both boundaries are included

BETWEEN is inclusive.

Marks BETWEEN 60 AND 80

means:

Marks >= 60
AND Marks <= 80

---

10. Answer: C — '_a%'

In a LIKE pattern:

_  = Exactly one character
%  = Zero or more characters

Therefore:

'_a%'

means:

• Any first character
• a as the second character
• Any remaining characters

---

11. Answer: C — 12

CROSS JOIN formula:

Left rows × Right rows

Calculation:

4 × 3 = 12

---

12. Answer: B — LEFT JOIN

A LEFT JOIN keeps:

• Every row from the left table
• Matching rows from the right
• NULL on the right when no match exists

---

13. Answer: C — SELF JOIN

A self join treats one table as two logical copies.

Example:

Employee AS e
Employee AS m

One copy represents employees, and the other represents managers.

---

14. Answer: A — Correlated subquery

A correlated subquery uses a value from the current outer-query row.

Example:

WHERE s.Marks > (
    SELECT AVG(s2.Marks)
    FROM Student AS s2
    WHERE s2.DeptID = s.DeptID
);

The inner query refers to outer alias s.

---

15. Answer: B — EXISTS

EXISTS is true if its subquery returns at least one row.

NOT EXISTS is true if the subquery returns no rows.

---

16. Answer: A — Atomicity

Atomicity means:

All operations happen, or none happen.

It prevents a transaction from remaining partly completed.

---

17. Answer: D — Durability

Durability means committed changes survive later system failures.

It is supported through mechanisms such as transaction logs and recovery processing.

---

18. Answer: B — Dirty read

A dirty read uses data written by an uncommitted transaction.

If the writer rolls back, the reader used a value that never became permanent.

---

19. Answer: C — S and S

Shared locks are read locks.

Multiple transactions can normally hold S-locks on the same item.

An exclusive lock is incompatible with both shared and exclusive locks.

---

20. Answer: B — Cannot acquire a new lock

Under 2PL:

Growing phase:

Acquire locks
Release none

Shrinking phase:

Release locks
Acquire none

The first unlock begins the shrinking phase.

---

21. Answer: B — Conflict serializable

Precedence graph rule:

No cycle → Conflict serializable
Cycle → Not conflict serializable

A topological ordering of the graph gives an equivalent serial order.

---

22. Answer: C — Strict 2PL

Strict 2PL holds all exclusive locks until commit or abort.

This helps:

• Prevent dirty reads of uncommitted writes
• Avoid cascading rollback
• Simplify recovery

---

23. Answer: B — Key-value

A key-value database stores:

Key → Value

Example:

session:101 → Session data

Redis is a common example.

---

24. Answer: A — Graph database

Graph databases store:

• Nodes
• Edges
• Properties

They are suitable for:

• Social networks
• Recommendations
• Route planning
• Fraud networks

Neo4j is a common example.

---

25. Answer: C

SQL databases commonly use:

Tables, rows, columns, keys and joins

NoSQL database models include:

Key-value
Document
Wide-column
Graph

Modern SQL and NoSQL systems can both support important transaction and consistency features. Exact guarantees depend on the product and configuration.

---

🎓 Final Concept Tests

Test 1 — Classify SQL Commands

Classify each command:

Command	Category
CREATE	DDL
ALTER	DDL
DROP	DDL
TRUNCATE	Usually DDL
INSERT	DML
UPDATE	DML
DELETE	DML
SELECT	DQL or DML in some classifications
GRANT	DCL
REVOKE	DCL
COMMIT	TCL
ROLLBACK	TCL
SAVEPOINT	TCL

---

Test 2 — Write a Table

Requirement:

Create a COURSE table with:

• CourseID as primary key
• CourseName as required text
• Fee between 0 and 100000
• DeptID as a foreign key

Direct Solution

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Fee DECIMAL(10,2),
    DeptID INT,
    CONSTRAINT chk_course_fee
        CHECK (Fee BETWEEN 0 AND 100000),
    CONSTRAINT fk_course_department
        FOREIGN KEY (DeptID)
        REFERENCES Department(DeptID)
);

---

Test 3 — Insert, Update and Delete

Insert

INSERT INTO Course (
    CourseID,
    CourseName,
    Fee,
    DeptID
)
VALUES (
    101,
    'Database Systems',
    5000,
    10
);

Update

Increase the fee by 10%:

UPDATE Course
SET Fee = Fee * 1.10
WHERE CourseID = 101;

Delete

DELETE FROM Course
WHERE CourseID = 101;

---

Test 4 — Aggregate Output

Marks:

80, 90, NULL, 70, 90

Find:

COUNT(*)
COUNT(Marks)
SUM(Marks)
AVG(Marks)
MAX(Marks)
MIN(Marks)
COUNT(DISTINCT Marks)

Direct Answer

Total rows:

COUNT(*) = 5

Non-NULL marks:

80, 90, 70, 90

Therefore:

COUNT(Marks) = 4

Sum:

80 + 90 + 70 + 90 = 330

Average:

330 / 4 = 82.5

Maximum:

90

Minimum:

70

Distinct non-NULL values:

80, 90, 70

Therefore:

COUNT(DISTINCT Marks) = 3

---

Test 5 — WHERE and HAVING

Requirement:

• Consider only students scoring at least 50.
• Group by department.
• Keep departments with an average above 75.

Direct Solution

SELECT DeptID,
       AVG(Marks) AS AverageMarks
FROM Student
WHERE Marks >= 50
GROUP BY DeptID
HAVING AVG(Marks) > 75;

Explanation:

WHERE Marks >= 50
→ Filters rows

GROUP BY DeptID
→ Creates department groups

HAVING AVG(Marks) > 75
→ Filters groups

---

Test 6 — LIKE Patterns

Requirement	Pattern
Starts with A	'A%'
Ends with a	'%a'
Contains av	'%av%'
Exactly four characters	'____'
Second character is a	'_a%'
Starts with A and has exactly four characters	'A___'

---

Test 7 — JOIN Output

Tables:

A

ID	Name
1	Asha
2	Ravi
3	Mohan

B

ID	City
1	Jaipur
3	Kota
4	Ajmer

INNER JOIN

SELECT A.ID, A.Name, B.City
FROM A
INNER JOIN B
    ON A.ID = B.ID;

Result:

ID	Name	City
1	Asha	Jaipur
3	Mohan	Kota

---

LEFT JOIN

Result:

ID	Name	City
1	Asha	Jaipur
2	Ravi	NULL
3	Mohan	Kota

---

RIGHT JOIN

Result:

ID from A	Name	ID from B	City
1	Asha	1	Jaipur
3	Mohan	3	Kota
NULL	NULL	4	Ajmer

---

FULL OUTER JOIN

Result contains IDs:

1, 2, 3, 4

• ID 1 matches.
• ID 2 exists only in A.
• ID 3 matches.
• ID 4 exists only in B.

---

Test 8 — CROSS JOIN Count

Tables:

STUDENT has 6 rows.
COURSE has 5 rows.

Result:

6 × 5 = 30 rows

---

Test 9 — Non-Correlated Subquery

Write a query to find students with marks above the overall average.

Direct Solution

SELECT StudentName, Marks
FROM Student
WHERE Marks > (
    SELECT AVG(Marks)
    FROM Student
);

The inner query is independent of the outer row.

---

Test 10 — Correlated Subquery

Write a query to find students scoring above their own department’s average.

Direct Solution

SELECT s.StudentName,
       s.Marks,
       s.DeptID
FROM Student AS s
WHERE s.Marks > (
    SELECT AVG(s2.Marks)
    FROM Student AS s2
    WHERE s2.DeptID = s.DeptID
);

The inner query uses the outer student’s DeptID.

---

Test 11 — Transaction With Savepoint

BEGIN TRANSACTION;

UPDATE Account
SET Balance = Balance - 500
WHERE AccountID = 1;

SAVEPOINT after_debit;

UPDATE Account
SET Balance = Balance + 500
WHERE AccountID = 2;

ROLLBACK TO after_debit;

COMMIT;

Direct Result

• The debit before the savepoint remains.
• The credit after the savepoint is undone.
• Commit makes the remaining debit permanent.

Warning

This would be incorrect bank-transfer logic because only the debit remains.

A real transfer should normally roll back the complete transaction if the credit fails.

---

Test 12 — Identify ACID

Situation	Property
Debit and credit both occur or neither occurs	Atomicity
Database rules remain valid	Consistency
Concurrent transactions do not interfere incorrectly	Isolation
Committed transfer survives a crash	Durability

---

Test 13 — Identify Concurrency Anomaly

Situation A

T2 reads a value written by uncommitted T1.

Answer:

Dirty read

Situation B

T1 reads the same row twice and receives different committed values.

Answer:

Non-repeatable read

Situation C

T1 repeats a query and finds additional matching rows.

Answer:

Phantom read

Situation D

T2 overwrites T1’s update.

Answer:

Lost update

---

Test 14 — Lock Compatibility

Complete the table:

Existing	Requested S	Requested X
S	Yes	No
X	No	No

Memory rule:

Only S with S is compatible.

---

Test 15 — 2PL Validation

Schedule:

lock-S(A)
read(A)
lock-X(B)
write(B)
unlock(A)
lock-S(C)

Is it valid under 2PL?

Direct Answer

No.

After:

unlock(A)

the shrinking phase begins.

The transaction then tries to acquire:

lock-S(C)

A new lock cannot be acquired during the shrinking phase.

---

Test 16 — Precedence Graph

Schedule conflicts produce:

T1 → T2
T2 → T3
T1 → T3

Is the schedule conflict serializable?

Direct Answer

Yes.

Graph:

T1 ───> T2 ───> T3
 └────────────> T3

There is no cycle.

Equivalent serial order:

T1, T2, T3

---

Test 17 — Cyclic Precedence Graph

Edges:

T1 → T2
T2 → T3
T3 → T1

Direct Answer

The graph contains a cycle:

T1 → T2 → T3 → T1

Therefore:

The schedule is not conflict serializable.

---

Test 18 — NoSQL Identification

Requirement	Best Basic Type
Session data accessed through a unique key	Key-value
Flexible nested product records	Document
Social-network relationships	Graph
Large distributed column-family data	Wide-column

---

🧠 Active Recall Questions

1. What does SQL stand for?
2. Why is SQL called declarative?
3. What does DDL mean?
4. Name four DDL commands.
5. What does DML mean?
6. Name three DML commands.
7. What does DCL mean?
8. What do GRANT and REVOKE do?
9. What does TCL mean?
10. What do COMMIT, ROLLBACK and SAVEPOINT do?
11. What is the difference between DELETE, TRUNCATE and DROP?
12. What happens if UPDATE has no WHERE clause?
13. What does COUNT(*) count?
14. What does COUNT(column) count?
15. How does AVG treat NULL?
16. What is the difference between WHERE and HAVING?
17. Is BETWEEN inclusive?
18. What do % and _ mean in LIKE?
19. How do you test for NULL?
20. What does DISTINCT do?
21. What does GROUP BY do?
22. Why must selected non-aggregate columns normally appear in GROUP BY?
23. What is the logical processing order of a SELECT query?
24. What does an INNER JOIN return?
25. What does a LEFT JOIN return?
26. What does a RIGHT JOIN return?
27. What does a FULL OUTER JOIN return?
28. What does a CROSS JOIN return?
29. What is a SELF JOIN?
30. What is a subquery?
31. What is a correlated subquery?
32. What is a non-correlated subquery?
33. What does EXISTS test?
34. What is the difference between ANY and ALL?
35. What is a transaction?
36. What are the four ACID properties?
37. What is atomicity?
38. What is consistency?
39. What is isolation?
40. What is durability?
41. Name the main transaction states.
42. What is a schedule?
43. What is a serial schedule?
44. What is a serializable schedule?
45. When do two operations conflict?
46. What is a precedence graph?
47. What does a cycle in a precedence graph mean?
48. What is a lost update?
49. What is a dirty read?
50. What is a non-repeatable read?
51. What is a phantom read?
52. What is a shared lock?
53. What is an exclusive lock?
54. Which lock combination is compatible?
55. What is lock granularity?
56. What is the growing phase of 2PL?
57. What is the shrinking phase of 2PL?
58. What is the lock point?
59. Does basic 2PL guarantee conflict serializability?
60. Can 2PL cause deadlock?
61. What is strict 2PL?
62. What is rigorous 2PL?
63. What is conservative 2PL?
64. What is a cascading rollback?
65. What is a strict schedule?
66. What does NoSQL mean?
67. Name the four main NoSQL types.
68. What is a key-value database?
69. What is a document database?
70. What is a graph database?
71. What is a wide-column database?
72. What is horizontal scaling?
73. What is the main difference between SQL and NoSQL?
74. Which database type is suitable for highly connected data?
75. Which database type is suitable for flexible JSON-like records?

---

📊 Self-Assessment

Use your score from the 25 MCQs.

Correct Answers	Level	Required Action
23–25	Excellent	Revise the Quick Revision Sheet tomorrow
20–22	Good	Review incorrect answers once
16–19	Developing	Repeat joins, clauses and transactions
10–15	Weak	Restudy all five hourly topics
0–9	Beginning	Read the material again slowly

Topic-Based Diagnosis

Questions Missed	Topic to Revise
1–5	DDL, DML, DCL and TCL
6–10	Aggregate functions and query clauses
11–15	Joins and subqueries
16–20	ACID, anomalies, locks and 2PL
21–25	Serializability and NoSQL

---

📅 Suggested Five-Hour Study Method

Hour 1 — DDL and DML

Study:

• CREATE
• ALTER
• DROP
• TRUNCATE
• INSERT
• SELECT
• UPDATE
• DELETE
• Constraints

Write from memory:

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Marks DECIMAL(5,2)
);

Practice:

1. Create one table.
2. Add a column.
3. Insert three rows.
4. Update one row.
5. Delete one row.
6. Explain DELETE vs TRUNCATE vs DROP.

---

Hour 2 — DCL, TCL and Functions

Study:

• GRANT
• REVOKE
• COMMIT
• ROLLBACK
• SAVEPOINT
• COUNT
• SUM
• AVG
• MAX
• MIN

Practice:

1. Grant SELECT permission.
2. Revoke UPDATE permission.
3. Write a transaction with one savepoint.
4. Calculate aggregate output with NULL values.
5. Explain COUNT(*) vs COUNT(column).

---

Hour 3 — Query Clauses

Study:

• WHERE
• GROUP BY
• HAVING
• ORDER BY
• DISTINCT
• LIKE
• IN
• BETWEEN
• IS NULL

Memorize:

WHERE = Rows
HAVING = Groups

Practice:

1. Find students scoring from 60 to 80.
2. Find names beginning with A.
3. Count students per department.
4. Keep departments with more than two students.
5. Sort students by marks descending.

---

Hour 4 — Joins and Subqueries

Study:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN
• Correlated subquery
• Non-correlated subquery
• EXISTS and NOT EXISTS

Draw two small tables and manually produce every join result.

Memorize:

INNER = Matching
LEFT = Keep left
RIGHT = Keep right
FULL = Keep all
CROSS = Multiply
SELF = Same table

Practice:

1. Find students with department names.
2. Find students without departments.
3. Find departments without students.
4. Find students above the overall average.
5. Find students above their department average.

---

Hour 5 — Transactions and NoSQL

Study:

• ACID
• Transaction states
• Schedules
• Conflict serializability
• Precedence graphs
• Concurrency anomalies
• Shared and exclusive locks
• 2PL
• Deadlock
• NoSQL types

Memorize:

ACID:
All or nothing
Correct state
Independent execution
Data survives

2PL:
Growing → Lock point → Shrinking

Graph:
No cycle → Serializable
Cycle → Not serializable

Practice:

1. Identify four ACID examples.
2. Draw one transaction-state diagram.
3. Test one precedence graph.
4. Reproduce the lock-compatibility table.
5. Compare key-value, document and graph databases.

---

🗂️ One-Minute Exam Revision

DDL:
CREATE, ALTER, DROP, TRUNCATE

DML:
INSERT, UPDATE, DELETE

DQL:
SELECT

DCL:
GRANT, REVOKE

TCL:
COMMIT, ROLLBACK, SAVEPOINT

WHERE:
Filters rows

HAVING:
Filters groups

BETWEEN:
Inclusive

LIKE:
% = Any number of characters
_ = Exactly one character

INNER:
Matches only

LEFT:
All left rows

RIGHT:
All right rows

FULL:
All rows

CROSS:
Left count × Right count

SELF:
Table joined to itself

Correlated:
Uses outer-query value

ACID:
Atomicity, Consistency, Isolation, Durability

S-lock:
Read

X-lock:
Write

2PL:
Growing then shrinking

Strict 2PL:
Hold X-locks until commit or abort

NoSQL:
Key-value, Document, Wide-column, Graph

---

🎯 Day 8 Completion Checklist

• Can write any SQL query from description
• Can predict JOIN output from two tables
• Know DDL vs DML vs DCL vs TCL commands
• Can explain ACID properties
• SQL vs NoSQL comparison clear