Chapter 4 — Database Query Using Sql

What is SQL?
SQL (Structured Query Language) is the standard language used to communicate with relational database management systems (RDBMS). It lets you create, read, update and delete (CRUD) data and define database structures. SQL is declarative: you specify what you want, and the database engine figures out how to get it.

Key areas of SQL

• DDL (Data Definition Language): Commands to define schema and structures — CREATE, ALTER, DROP.
• DML (Data Manipulation Language): Commands to manipulate data — SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language): Commands to control access — GRANT, REVOKE.
• TCL (Transaction Control Language): Commands to manage transactions — COMMIT, ROLLBACK.

Basic SELECT query structure

SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column ASC|DESC
LIMIT n;

Important concepts

• Filters: WHERE clause filters rows using operators (=, <>, <, >, BETWEEN, IN, LIKE).
• Aggregation: Functions such as COUNT(), SUM(), AVG(), MIN(), MAX() used with GROUP BY.
• Joins: Combine rows from two or more tables using relationships: INNER JOIN, LEFT (OUTER) JOIN, RIGHT JOIN, FULL OUTER JOIN.
• Subqueries: A query inside another query (can be in SELECT, FROM, WHERE).
• Constraints: Enforce rules on data: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.
• Transactions: Ensure atomic operations using BEGIN/COMMIT/ROLLBACK to keep data consistent.

Why SQL matters (real-life uses)
Retailers use SQL to find top-selling products, banks use it to fetch account transactions, schools use it to manage student records, and hospitals use it to retrieve patient histories. Any application that stores structured data typically uses SQL on the backend.

Example workflow (simple): A school web app stores student data in a Students table. When a teacher requests 'all students with marks >= 75', the app runs a SELECT query with a WHERE clause, optionally orders results, and shows them on screen.

Good practices

• Use meaningful names for tables/columns.
• Normalize data to avoid redundancy (use separate tables and foreign keys).
• Use indexes on columns frequently used in WHERE or JOIN to improve performance.
• Always test queries on sample data and use transactions when performing multiple related updates.

Overview

SQL commands are grouped by purpose into categories so that we can manage database structure, data, security and transactions in an organized way. The main categories used in Class 12 Informatics Practices are:

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DQL (Data Query Language)
• DCL (Data Control Language)
• TCL (Transaction Control Language)

1. DDL — Data Definition Language

DDL commands define or modify the schema (structure) of database objects such as tables, views, and indexes. They change metadata and are usually auto-committed.

Common commands:

• CREATE — create database objects (tables, views)
• ALTER — change structure (add/drop columns, change types)
• DROP — delete objects (table, view)
• TRUNCATE — remove all rows from a table (fast, cannot rollback in many DBMS)
• RENAME — rename an object

2. DML — Data Manipulation Language

DML commands change the data stored in tables. These operations can usually be rolled back or committed (depending on transaction control).

• INSERT — add new rows
• UPDATE — modify existing rows
• DELETE — remove rows (DELETE can be rolled back if within transaction)

3. DQL — Data Query Language

DQL is used to retrieve data from the database. The core command is SELECT (often taught as separate category because querying is a primary activity).

• SELECT — fetch data using projections, filtering (WHERE), grouping (GROUP BY), ordering (ORDER BY)

4. DCL — Data Control Language

DCL manages access and permissions to the database objects.

• GRANT — give privileges to users/roles
• REVOKE — remove granted privileges

5. TCL — Transaction Control Language

TCL commands handle transactions — a set of operations that must succeed or fail as a unit.

• COMMIT — save transaction changes permanently
• ROLLBACK — undo changes since last commit (useful for error recovery)
• SAVEPOINT — define intermediate points within a transaction to roll back to

Important points

• DDL changes affect structure and are often auto-committed.
• DML and DQL operate on table data; use TCL to control atomicity and persistence.
• DCL enforces security and multi-user control.

Short Example Flow (library system)

-- DDL: create table Books
CREATE TABLE Books(book_id INT PRIMARY KEY, title VARCHAR(100), author VARCHAR(50), copies INT);

-- DML: add books
INSERT INTO Books VALUES(1,'Physics', 'A. Author', 3);

-- DQL: view available books
SELECT title, copies FROM Books WHERE copies > 0;

-- DML: update after lending one copy
UPDATE Books SET copies = copies - 1 WHERE book_id = 1;

-- TCL: commit the lending operation
COMMIT;

-- DCL: allow librarian to manage table
GRANT SELECT, INSERT, UPDATE ON Books TO librarian_role;

Data Types (SQL) — Class 12 Informatics Practices

In SQL, a data type defines the kind of values a column can hold and how those values are stored, processed and compared. Choosing the correct data type ensures data integrity, efficient storage and correct query results.

Main categories

• Numeric types: for integer and real numbers (e.g., INT, SMALLINT, DECIMAL, NUMERIC, FLOAT, REAL).
• Character types: for text (e.g., CHAR(n), VARCHAR(n), TEXT / CLOB).
• Date and time types: DATE, TIME, TIMESTAMP (store dates, times, or both).
• Boolean: TRUE / FALSE (some DBMS use BOOLEAN or TINYINT(1)).
• Binary / Large Object types: BLOB, BYTEA for images/files.
• Special types: ENUM/SET (MySQL), UUID, JSON (some DBMS).

Key properties

• Precision & scale (DECIMAL/NUMERIC): DECIMAL(p,s) stores p total digits where s digits are after decimal point. Use for exact money values.
• Fixed vs variable length: CHAR(n) is fixed-length (padded), VARCHAR(n) is variable-length up to n characters.
• NULL vs NOT NULL: Whether a column can have missing (NULL) values.
• Storage & performance: Smaller and appropriate types improve speed and reduce disk usage.

SQL examples (brief)

CREATE TABLE Students (
  AdmissionNo INT PRIMARY KEY,
  Name VARCHAR(60) NOT NULL,
  DOB DATE,
  Mobile CHAR(10),
  AverageMarks DECIMAL(5,2),   -- total 5 digits, 2 after decimal
  IsPass BOOLEAN,
  Photo BLOB
);

-- Insert example
INSERT INTO Students (AdmissionNo, Name, DOB, Mobile, AverageMarks, IsPass)
VALUES (101, 'Anita Sharma', '2004-08-12', '9876543210', 85.75, TRUE);

Best practices

• Use INT (or SMALLINT/TINYINT) for counts/IDs; do not use VARCHAR for numbers.
• Use DECIMAL for money; do not use FLOAT for exact financial calculations.
• Pick length limits (VARCHAR) that reflect real data to save space.
• Use DATE/TIMESTAMP for dates and times, not VARCHAR.
• Avoid NULL if a value is always required; use NOT NULL and defaults.

Understanding and selecting correct data types is fundamental to designing efficient and reliable databases.

Overview
In a relational database, constraints enforce rules on table data (validity, uniqueness, relationships) and keys identify rows uniquely. Constraints ensure data integrity; keys are special columns or sets of columns used to enforce uniqueness and relationships.

Common Constraints

• NOT NULL — column cannot contain NULL values.
• UNIQUE — values in a column (or combination of columns) must be unique across rows.
• PRIMARY KEY — UNIQUE + NOT NULL; identifies each row uniquely. Can be single-column or composite (multi-column).
• FOREIGN KEY — enforces referential integrity by linking a column (or columns) in one table to a PRIMARY/UNIQUE key in another table.
• CHECK — enforces a boolean expression on column values (e.g., salary >= 0).
• DEFAULT — supplies a default value when none is provided on INSERT.

Key Types and Properties

• Superkey — any set of columns that uniquely identifies rows (may have extra attributes).
• Candidate key — a minimal superkey (no proper subset is a superkey). There can be multiple candidate keys.
• Primary key — one chosen candidate key to identify the row; must be unique and NOT NULL.
• Alternate key — candidate keys not chosen as primary.
• Composite (compound) key — a key made of two or more columns.
• Foreign key — column(s) that reference a primary/unique key in another table; maintains referential integrity.
• Surrogate key — system-generated key (e.g., auto-increment ID) used as primary key when no natural key is suitable.

Referential Integrity & Cascading
A foreign key maintains relationships: INSERT/UPDATE/DELETE operations that would break the relationship are rejected or handled via actions such as ON DELETE CASCADE, ON UPDATE CASCADE, ON DELETE SET NULL, etc.

When to use which constraint
Use PRIMARY KEY for row identity, UNIQUE for alternate identifiers (email, phone), NOT NULL for required fields, CHECK for business rules (age >= 0), and FOREIGN KEY to link related tables (orders → customers).

How constraints are enforced
Most DBMS enforce constraints at INSERT/UPDATE time. Violations cause an error and the statement is rolled back (unless deferred constraints are used).

Short example summary
A typical small schema: students, courses, enrollments. student_id is PRIMARY KEY in students; course_id is PRIMARY KEY in courses; enrollment uses (student_id, course_id) as a composite PRIMARY KEY and both are FOREIGN KEYs referencing students and courses respectively.

The SELECT statement is the primary SQL command to retrieve data from one or more database tables. It specifies which columns or expressions to return and from which table(s). SELECT can also filter rows, remove duplicates, rename columns, sort results, and compute simple expressions.

Basic syntax:

SELECT <column_list or expressions>
FROM <table_name>
[WHERE <condition>]
[GROUP BY <columns>]
[HAVING <group_condition>]
[ORDER BY <columns> [ASC|DESC]]
[LIMIT <number>];

Key parts explained:

• SELECT — columns, expressions, or * (all columns). You may use arithmetic expressions (salary * 12) or functions (COUNT(), AVG()).
• FROM — table(s) to read. Can include joins for multiple tables.
• WHERE — row-level filter using comparison operators (=, <>, <, >, <=, >=), logical operators (AND, OR, NOT), IN, BETWEEN, LIKE, IS NULL.
• DISTINCT — removes duplicate rows for the selected columns.
• AS — gives an alias to a column or expression (SELECT name AS student_name).
• ORDER BY — sorts results by one or more columns (ascending is default).
• GROUP BY and HAVING — group rows to apply aggregate functions (SUM, COUNT, AVG, MIN, MAX); HAVING filters groups.
• Execution order (conceptual): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Tips: Use WHERE to filter rows before aggregation. Use DISTINCT when you only want unique values. Use aliases to make output readable. Use LIMIT to show only top N rows (or TOP in some SQL dialects).

Overview

This topic covers how SQL uses operators to perform comparisons, arithmetic and logical tests; how to search text using pattern matching; and how NULL values (unknown/missing data) behave and must be handled safely in queries.

1. Operators

• Arithmetic: +, -, *, /, % (modulo). Used in SELECT and UPDATE to compute values. Example: salary * 1.10.
• Comparison: =, <>, !=, <, <=, >, >=. Compare column values in WHERE or HAVING. Example: marks >= 40.
• Logical (Boolean): AND, OR, NOT. Combine conditions. SQL uses three-valued logic (TRUE, FALSE, UNKNOWN) when NULLs are involved.
• Membership & range: IN (…), BETWEEN a AND b.
• Null tests: IS NULL, IS NOT NULL (do not use = NULL).

2. Pattern Matching

Pattern matching is used to test strings. The most common tool is the LIKE operator with wildcards:

• '%' matches zero or more characters. Example: name LIKE 'A%' finds names starting with A.
• '_' matches exactly one character. Example: code LIKE 'AB_1' matches 'ABX1'.
• Some DBMS support bracket expressions (SQL Server) or regular expressions (REGEXP in MySQL, ~ in PostgreSQL) for advanced patterns.
• Use ESCAPE to search for literal '%' or '_' characters.

3. NULL Handling

• Meaning: NULL represents unknown or missing data. NULL is not the same as an empty string ('') or zero.
• Comparisons: Any comparison with NULL returns UNKNOWN (not TRUE). Use IS NULL or IS NOT NULL to test for NULL.
• Three-valued logic: Conditions evaluate to TRUE, FALSE or UNKNOWN. In a WHERE clause, only TRUE rows are returned; UNKNOWN acts like FALSE.
• Aggregation: Aggregate functions (SUM, AVG, MAX, MIN) ignore NULLs. COUNT(column) counts non-NULLs; COUNT(*) counts rows including NULL values in columns.
• Null-substitution functions: Use COALESCE(col, value) (standard), IFNULL(col, value) (MySQL), or NVL(col, value) (Oracle) to replace NULLs with default values. NULLIF(expr1, expr2) returns NULL if expressions are equal.

Rules & Common Pitfalls

• Avoid writing WHERE col = NULL or col <> NULL — they are invalid for NULL tests.
• Be careful with logical expressions: WHERE salary > 1000 OR salary IS NULL explicitly includes missing salaries.
• When computing with possible NULLs, substitute defaults: COALESCE(bonus,0) so arithmetic works as expected.
• When filtering with IN or BETWEEN, NULLs are excluded unless tested explicitly.

Example behaviour

-- Wrong: returns no rows for NULLs
SELECT * FROM students WHERE middle_name = NULL;

-- Correct: test NULL explicitly
SELECT * FROM students WHERE middle_name IS NULL;

-- Replace NULL with 0 when summing
SELECT SUM(COALESCE(extra_marks,0)) FROM exams;

-- Pattern matching
SELECT * FROM products WHERE description LIKE '%wireless%';

Summary: Use the correct operators for arithmetic, comparison and logic; use LIKE/REGEXP for string patterns; and always handle NULLs explicitly (IS NULL / COALESCE) to avoid unexpected results.

Overview

Aggregate functions compute a single value from a set of rows. They are used with SQL SELECT to produce summaries such as totals, averages, counts, minima and maxima. When you want summaries per category (for example, average marks per class), you use GROUP BY. To filter groups based on aggregate values (for example, departments with average salary > 50,000) you use HAVING.

Common aggregate functions

• COUNT(expr) — counts rows; COUNT(*) counts all rows; COUNT(DISTINCT col) counts distinct non-NULL values.
• SUM(expr) — sum of numeric values (NULLs ignored).
• AVG(expr) — average of numeric values (NULLs ignored).
• MIN(expr) — minimum value.
• MAX(expr) — maximum value.

GROUP BY

GROUP BY groups rows that have the same values in specified columns so aggregate functions can be applied per group. Every column in SELECT that is not an aggregate must appear in GROUP BY.

HAVING vs WHERE

• WHERE filters rows before aggregation (cannot use aggregates).
• HAVING filters groups after aggregation (can use aggregates).

NULL handling

Aggregate functions (except COUNT(*)) ignore NULL values. So AVG(salary) computes average of non-NULL salaries; COUNT(emp_id) ignores NULL emp_id values.

Example patterns

-- total sales per product
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

-- average marks per class and section, only classes with avg >= 60
SELECT class, section, AVG(marks) AS avg_marks
FROM student_marks
GROUP BY class, section
HAVING AVG(marks) >= 60;

-- count of employees per department (including departments with zero employees not returned)
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id;

-- distinct customers count
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Good practices

• Include non-aggregated selected columns in GROUP BY.
• Use meaningful aliases (AS) for aggregated columns.
• Use HAVING for group-level conditions and WHERE for row-level filters.

Combining data from two or more tables is a fundamental part of SQL. There are two broad ways to combine tables:

• Joins: combine rows from tables based on a related column (relationship-based). Typical joins: inner, left outer, right outer, full outer, cross, self, natural and non-equi (theta) joins.
• Set operators: combine results of two SELECT queries as sets (UNION, UNION ALL, INTERSECT, MINUS/EXCEPT).

Key points:

• An INNER JOIN returns only rows that match in both tables (intersection of rows based on condition).
• A LEFT OUTER JOIN returns all rows from the left table and matched rows from the right table; unmatched right columns are NULL.
• A RIGHT OUTER JOIN is symmetric to LEFT: all rows from right and matching left rows.
• A FULL OUTER JOIN returns rows that match plus unmatched rows from both tables (may not be supported in all DBMS).
• A CROSS JOIN returns Cartesian product: every row of A paired with every row of B (useful for combinations).
• A SELF JOIN joins a table to itself (useful for hierarchical data like employee-manager).
• A NATURAL JOIN automatically joins on all columns with the same names (use cautiously).
• UNION combines rows of two queries and removes duplicates; UNION ALL keeps duplicates. INTERSECT returns common rows; MINUS/EXCEPT returns rows in first query but not in second.

Use join condition(s) in the ON clause (or USING). Without a condition, JOIN becomes CROSS JOIN (Cartesian product). For performance, always join on indexed columns when possible and avoid unnecessarily large Cartesian products.

Example conceptual flow for a join:

SELECT columns
FROM tableA
JOIN_TYPE tableB
ON tableA.key = tableB.key
WHERE conditions;

What is a subquery (nested query)?

A subquery (or nested query) is a SQL query placed inside another SQL query. The outer query uses results returned by the inner query. Subqueries let you break complex problems into smaller queries and express filtering, comparison and aggregation conditions compactly.

Where can you use subqueries? Subqueries can appear in the SELECT list, FROM clause (as a derived table), WHERE clause, and HAVING clause.

Types of subqueries

• Scalar subquery: returns a single value (one row, one column). Can be used wherever a single value is allowed (e.g., SELECT list, WHERE comparison).
• Row subquery: returns a single row with multiple columns; used with row-wise comparisons.
• Column (single-column) subquery: returns a list/column of values; commonly used with IN.
• Table subquery / Derived table: returns a full table result and appears in FROM (needs an alias).

Correlated vs Non-correlated subqueries

• Non-correlated: inner query runs once and provides a result set for the outer query. Example: WHERE dept_id IN (SELECT id FROM dept WHERE location = 'X')
• Correlated: inner query references columns from the outer query; it is re-evaluated for each outer row. Example: WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept)

Common operators with subqueries: IN, NOT IN, EXISTS, NOT EXISTS, =, >>, ANY / SOME, ALL. Use EXISTS for existence checks (fast with indexes); use IN when matching against a known list.

Execution order: For non-correlated subqueries the inner query executes first and its result feeds the outer query. For correlated subqueries the inner query depends on the current outer row and runs repeatedly (logically inner runs per outer row).

Best practices & notes

• Prefer joins or derived tables for large datasets when appropriate — they often perform better than correlated subqueries.
• Use EXISTS when testing for presence/absence; it stops on first match.
• Ensure scalar subqueries return exactly one value; otherwise SQL errors occur (or unexpected behavior with IN/EXISTS differences).
• Alias derived tables: FROM (subquery) AS t.

Short example (non-correlated):

SELECT name FROM Student
WHERE marks > (SELECT AVG(marks) FROM Student WHERE class = '12A');

Short example (correlated):

SELECT e.emp_name
FROM Employee e
WHERE e.salary > (SELECT AVG(salary) FROM Employee e2 WHERE e2.dept_id = e.dept_id);

Subqueries are a powerful tool in SQL for conditional logic, filtering, aggregation-based comparisons and creating temporary result sets.

What are set operations? In SQL, set operations combine the result sets of two or more SELECT queries into a single result. They are based on set theory and let you perform union, intersection and difference of rows returned by queries.

• Common set operations:
  • UNION — returns distinct rows that appear in either or both queries (duplicates removed).
  • UNION ALL — returns all rows from both queries including duplicates.
  • INTERSECT — returns rows common to both queries.
  • MINUS (Oracle) / EXCEPT (standard SQL) — returns rows in the first query that are not in the second.
• Rules and requirements
  • Each SELECT must return the same number of columns.
  • Corresponding columns must have compatible data types (or be implicitly convertible).
  • An ORDER BY applies to the final combined result and should appear once after the last SELECT (or use parentheses to order partial results before combining).
  • Column names in the result are taken from the first SELECT in many SQL dialects. Use aliases in the first SELECT if you want specific column names.
• Duplicates and NULLs
  • UNION removes duplicate rows; UNION ALL preserves them.
  • NULL values are considered equal for the purpose of deduplication (two rows with NULL in the same position are treated as equal when deciding duplicates).
• Precedence & grouping
  • Use parentheses to control the order in which set operations are applied. Example: (A UNION B) INTERSECT C.
• Performance notes
  • UNION ALL is usually faster than UNION because it does not require duplicate elimination (no sort/unique step).
  • Indexes and careful filtering in each SELECT help performance; avoid returning unneeded columns or rows before combining.

When to use each operation (short guidance)

• UNION — merge two lists but remove repeats (e.g., compile a master list of emails from two sources).
• UNION ALL — concatenate results and keep duplicates (e.g., keep repeated transaction rows from two sources to preserve counts).
• INTERSECT — find common items (e.g., students enrolled in both courses).
• MINUS / EXCEPT — find items in A not in B (e.g., products in catalog A not yet published in catalog B).

Overview: Built-in SQL functions are pre-defined operations that perform computations on data values and return a single value. They fall into two broad categories: aggregate (group) functions that operate on sets of rows and return a summary value, and scalar (single-row) functions that operate on individual column values.

Categories and common functions:

• Aggregate functions — work on groups or whole tables: COUNT(), SUM(), AVG(), MIN(), MAX(). Often used with GROUP BY and HAVING.
• Numeric/scalar functions — operate on numeric values: ROUND(), CEIL(), FLOOR(), ABS().
• String/text functions — manipulate text: CONCAT(), SUBSTR() or SUBSTRING(), LENGTH(), UPPER(), LOWER(), TRIM(), REPLACE().
• Date/time functions — handle dates and times: CURDATE()/CURRENT_DATE, NOW(), DATE_ADD(), DATE_SUB(), DATEDIFF(), YEAR(), MONTH(), DAY().
• Conversion and conditional — change types or handle NULLs: CAST(... AS ...), CONVERT(), COALESCE() (first non-NULL), NULLIF().

NULL behavior: Many functions ignore NULLs in aggregates (e.g., SUM() skips NULL rows). Use COALESCE(column, default) to substitute values for NULL.

Usage patterns:

• Aggregates with grouping: SELECT dept, AVG(salary) FROM Employee GROUP BY dept;
• Filter groups: HAVING applies after grouping (e.g., departments with average salary > 50000)
• Scalar functions in SELECT or WHERE to transform or filter values (e.g., WHERE UPPER(name) = 'RAHUL')

Best practices:

• Use indexes for columns used in WHERE to improve performance; applying functions to indexed columns in WHERE can prevent index usage (e.g., avoid WHERE UPPER(col) = 'X' if possible).
• Use HAVING only for filtering aggregated results; prefer WHERE for row-level filtering before grouping.
• Be explicit with CAST when combining different types (dates and strings).

Example brief summary: Built-in SQL functions let you compute totals, averages, manipulate strings, work with dates, handle NULLs, and convert data types — all essential for data reporting and transformation in real-life applications such as sales reporting, student marks analysis, payroll, and inventory management.

What is a transaction?
A transaction is a logical unit of work performed on a database. It is a sequence of one or more SQL operations (SELECT/INSERT/UPDATE/DELETE) that must be executed as a single unit so that the database moves from one consistent state to another.

Why transactions?
To ensure correctness when multiple operations are needed to accomplish a task (for example, transferring money between bank accounts), and to handle failures and concurrent access safely.

ACID properties

• Atomicity — All operations in a transaction succeed or none do. If any operation fails, the transaction is rolled back.
• Consistency — A transaction transforms the database from one valid state into another, respecting all integrity constraints.
• Isolation — Concurrent transactions should not interfere with each other; intermediate states of a transaction are invisible to others.
• Durability — Once a transaction commits, its changes are permanent even if the system crashes.

Transaction states

• Active: Transaction has started and is executing.
• Partially committed: Final statement executed, but commit not yet complete.
• Committed: All changes are permanently recorded.
• Failed: An error occurred; transaction can no longer proceed.
• Aborted/Rolled back: Changes undone; transaction terminated without commit.

Basic SQL control commands
START TRANSACTION; / BEGIN TRANSACTION;
COMMIT; — make changes permanent
ROLLBACK; — undo changes
SAVEPOINT name; — create a save point inside a transaction
ROLLBACK TO SAVEPOINT name;

Concurrency and why it is needed
Databases serve many users/processes at the same time. Concurrency allows multiple transactions to run in overlapping time periods to improve throughput and resource usage. Without control, concurrent execution can produce incorrect results.

Common concurrency problems (anomalies)

• Lost update: Two transactions read the same item and update it; one update overwrites the other.
• Dirty read: A transaction reads data written by another transaction that has not yet committed (and may later be rolled back).
• Non-repeatable read: A transaction reads the same item twice and gets different values because another committed transaction modified it in-between.
• Phantom read: A transaction re-executes a query returning a set of rows and finds additional rows (phantoms) inserted by another committed transaction.

Isolation levels (practical trade-offs)
Different DBMSs provide levels of isolation that balance correctness and performance. Typical levels (from weakest to strongest):

• READ UNCOMMITTED — allows dirty reads
• READ COMMITTED — prevents dirty reads, but allows non-repeatable reads and phantoms
• REPEATABLE READ — prevents dirty and non-repeatable reads, may still allow phantoms
• SERIALIZABLE — highest isolation; transactions appear to run one after another (no anomalies)

Concurrency control techniques (overview)

• Locking: Prevent conflicting accesses using locks. Two main lock types: shared (S) for read and exclusive (X) for write.
• Two-Phase Locking (2PL): Each transaction has a growing phase (acquires locks) and a shrinking phase (releases locks). Strict 2PL holds all write locks until commit to ensure recoverability.
• Timestamp ordering: Each transaction gets a timestamp; ordering of read/write operations follows timestamps to ensure serializability.
• Optimistic concurrency control: Execute without locks, validate at commit; if conflict detected, roll back one transaction.

Serializability (correctness criterion)
A concurrent schedule (interleaving of operations) is correct if it is serializable — i.e., its effect is equivalent to some serial execution of the same transactions. A common test: conflict serializability. Build a precedence (conflict) graph where nodes are transactions and directed edges show a conflict (Ti's operation before Tj on the same data with at least one write). If the graph is acyclic, the schedule is conflict-serializable.

Recoverability and cascading aborts

• A schedule is recoverable if whenever a transaction Tj reads a value written by Ti, Ti commits before Tj commits. This avoids committing based on uncommitted data that may later be rolled back.
• Cascading aborts occur when an aborted transaction forces other transactions that read its uncommitted data to also abort. Casadeless schedules avoid this by making transactions read only committed data.

Practical tips for students

• Always use transactions for multi-step changes (e.g., banking transfers): START, do updates, then COMMIT.
• Use appropriate isolation level: higher isolation prevents anomalies but may reduce concurrency.
• When debugging concurrency anomalies, draw timelines of operations to spot interleavings causing problems.