Chapter 8 — Structured Query Language

SQL (Structured Query Language) is a standard language used to communicate with relational database management systems (RDBMS). It allows users to create, read, update and delete (CRUD) data stored in tables. SQL is not case-sensitive for keywords but writing them in uppercase helps readability.

Key purposes of SQL:

• Create and modify database structures (tables, indexes).
• Insert, update and remove data.
• Retrieve data using flexible queries and filters.
• Control access and manage transactions.

Basic relational concepts:

• Table: collection of rows (records) and columns (fields).
• Row (tuple): a single record in a table.
• Column: attribute with a data type (INT, VARCHAR, DATE, etc.).
• Primary Key (PK): unique identifier for each row.
• Foreign Key (FK): column that references a primary key in another table to maintain relationships.

Major SQL categories:

• DDL (Data Definition Language): CREATE, ALTER, DROP (define structure).
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE (manipulate data).
• DCL (Data Control Language): GRANT, REVOKE (permissions).
• TCL (Transaction Control Language): COMMIT, ROLLBACK (transactions).

Common SQL patterns:

-- Retrieve columns from a table
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 DESC;

-- Insert a new row
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- Update rows
UPDATE table_name SET col1 = new_value WHERE condition;

-- Delete rows
DELETE FROM table_name WHERE condition;

Important query features:

• Filtering: WHERE, logical operators (AND, OR, NOT).
• Aggregation: COUNT(), SUM(), AVG(), MIN(), MAX() with GROUP BY and HAVING.
• Sorting & limiting: ORDER BY, LIMIT (or TOP in some systems).
• Join multiple tables: INNER JOIN, LEFT JOIN, RIGHT JOIN to combine related data.
• Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT to enforce data quality.
• Indexing: improves query performance by creating indexes on columns used in searches.

Transactions (atomic operations): start a transaction to perform multiple related changes; COMMIT to save or ROLLBACK to undo on error. This ensures data integrity.

Example small schema (concept): Students and Courses

Students(student_id PK, name, class, dob)
Courses(course_id PK, title, credits)
Enrollments(enroll_id PK, student_id FK, course_id FK, grade)

Why learn SQL in Class 11 IP?

• SQL is widely used in software, business analytics, web apps and scientific research.
• It teaches structured thinking about data storage and retrieval.
• Basic SQL skills are immediately useful for school projects and higher studies.

Relational Terminology — overview

In the relational model a relation is a mathematically defined table. Understanding basic terminology is essential for designing, querying and enforcing integrity of relational databases.

• Relation: A set of tuples organized in rows and columns; commonly called a table.
• Tuple: A single row of a relation; an ordered set of attribute values that describes one entity or relationship instance.
• Attribute: A column of a relation; each attribute has a name and a domain (set of permissible values).
• Domain: The data type and allowable values for an attribute (for example, integer, varchar, date).
• Degree: Number of attributes (columns) in a relation. If a table has n columns, degree = n.
• Cardinality: Number of tuples (rows) in a relation; written as |R|.
• Relation Schema: The definition of a relation: R(A1, A2, ..., An) where Ai are attributes.
• Relation Instance / State: The actual set of tuples in a relation at a particular time.
• Primary Key: An attribute or minimal set of attributes that uniquely identifies each tuple in a relation. Must be NOT NULL (entity integrity).
• Candidate Key: A minimal superkey; any attribute set that can be chosen as primary key.
• Superkey: Any superset of a key that uniquely identifies tuples.
• Composite (Concatenated) Key: A key made of two or more attributes.
• Foreign Key: An attribute (or set) in one relation whose values refer to primary key values in another relation; enforces referential integrity.
• Null: A special marker meaning unknown, not applicable or missing value.

Integrity constraints

• Entity integrity: Primary key attributes must not be null.
• Referential integrity: Every non-null foreign key value must match an existing primary key value in the referenced relation.
• Key constraint: Keys must be unique; no two distinct tuples share the same key value(s).

Functional dependency: A expresses that an attribute (or set) X functionally determines an attribute Y, written X -> Y. This underlies keys and normalization.

Why these terms matter: They let us design tables that avoid redundancy, support correct queries, and maintain data consistency through constraints.

Overview: SQL (Structured Query Language) commands are grouped by purpose. The main categories taught in Class 11 Informatics Practices are:

• DDL (Data Definition Language): Commands that define or modify database schema (tables, indexes, constraints). Examples: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• DML (Data Manipulation Language): Commands that change the data stored in tables. Examples: INSERT, UPDATE, DELETE, MERGE.
• DQL (Data Query Language): Commands used to retrieve data. Primarily the SELECT statement (with clauses such as WHERE, GROUP BY, HAVING, ORDER BY).
• TCL (Transaction Control Language): Commands that control transactions to ensure data integrity. Examples: COMMIT, ROLLBACK, SAVEPOINT.
• DCL (Data Control Language): Commands that manage permissions and access. Examples: GRANT, REVOKE.

Why categories matter: Grouping helps understand what will change (schema vs data vs access vs transactions) and prevents accidental changes. For example, DDL changes structure and is often run by DBAs; DML changes row data and usually needs transaction control (TCL) to make operations safe.

Common patterns: CRUD maps to SQL categories: Create → DDL/INSERT, Read → SELECT (DQL), Update → UPDATE (DML), Delete → DELETE (DML). Also, constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT) are defined by DDL.

Data types in SQL define the kind of data that can be stored in a column of a table. Choosing the correct data type is essential for correctness, storage efficiency, query performance and data validation. SQL data types are grouped by purpose: numeric, character (string), date/time, boolean, binary, and large objects (LOBs).

• Numeric types: used for integers and decimals.
  • INT, SMALLINT, BIGINT — integer values with different ranges.
  • DECIMAL(p, s) / NUMERIC(p, s) — fixed precision and scale for exact decimal storage (money, marks).
  • FLOAT, REAL — approximate floating-point numbers (scientific calculations).
• Character (string) types:
  • CHAR(n) — fixed-length string, padded with spaces if shorter.
  • VARCHAR(n) — variable-length string up to n characters (most commonly used for names, addresses).
  • Some DBs provide TEXT or VARCHAR(MAX) / CLOB for very large text.
• Date and time types:
  • DATE — calendar date (YYYY-MM-DD).
  • TIME — time of day (HH:MM:SS).
  • TIMESTAMP / DATETIME — date + time, often with timezone options.
• Boolean: BOOLEAN (or BIT/tinyint in some DBs) for true/false values.
• Binary and large objects:
  • BINARY, VARBINARY — store raw bytes (files, images).
  • BLOB / CLOB — large binary or character objects for multimedia or long text.
• Special types: ENUM, SET (MySQL), UUID, JSON types in modern DBs to store structured documents.

Key points and best practices:

• Use the smallest type that fits the data to save space and improve performance (e.g., SMALLINT instead of INT if values are small).
• For money or precise decimals, prefer DECIMAL(p,s) over FLOAT to avoid rounding errors.
• Use CHAR for fixed-length codes (like country codes), VARCHAR for variable-length text (names, emails).
• Declare NOT NULL when a value is required and use DEFAULT for sensible defaults.
• Consider indexing and how the type affects index size and search speed.

Example short syntax (illustrative):

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  dob DATE,
  percentage DECIMAL(5,2),
  is_pass BOOLEAN
);

This creates columns appropriate for each kind of data: integer id, variable-length name, a date of birth, a decimal for percentage with 2 digits after the decimal, and a boolean pass/fail flag.

Overview: Creating and modifying database objects is about defining the structure that stores data (databases, tables, indexes, views) and changing that structure safely as requirements evolve. SQL commands covered: CREATE, ALTER, DROP, TRUNCATE, RENAME and commands to create indexes and views.

Creating a database: Use CREATE DATABASE db_name; then switch with USE db_name;. A database is a container for tables and other objects.

Creating tables: CREATE TABLE defines columns, data types and constraints. Each column has a name and a data type (e.g., INT, VARCHAR(n), DATE, FLOAT). Constraints enforce rules: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

Common constraints and effects:

• PRIMARY KEY: Uniquely identifies rows; cannot be NULL. Enforced as UNIQUE + NOT NULL.
• FOREIGN KEY: Enforces referential integrity between parent and child tables. Options: ON DELETE CASCADE / SET NULL, ON UPDATE CASCADE, etc.
• UNIQUE: Ensures column values are unique.
• NOT NULL: Column must have a value.
• CHECK: Validates a condition (e.g., CHECK(age >= 0)).
• DEFAULT: Supplies a default value when none provided.

Modifying tables: ALTER TABLE is used to add, modify, rename or drop columns and constraints. ALTER can also add/drop PRIMARY KEY or FOREIGN KEY constraints. Examples of operations: ADD COLUMN, DROP COLUMN, MODIFY (or ALTER COLUMN depending on DBMS), RENAME TO.

Removing objects: DROP TABLE deletes the table and its data and metadata. TRUNCATE TABLE removes all rows but typically keeps the table structure and is faster (can be non-transactional in some DBMS). DROP DATABASE deletes the entire database.

Indexes and views:

• CREATE INDEX creates a data structure (often a B-tree) to speed up queries on specified columns. DROP INDEX removes it.
• CREATE VIEW creates a named query result (virtual table) that can simplify complex queries and provide a level of abstraction/security. Views do not store data (unless materialized).

Rules & best practices:

• Always define a PRIMARY KEY for tables to uniquely identify rows.
• Use appropriate data types (e.g., use INT for integers, not VARCHAR).
• Apply constraints to enforce data integrity rather than relying on application code.
• When altering live production schemas, plan migrations, back up data, and consider downtime or transactional migrations.
• Use indexes selectively: they speed reads but slow writes and use space.

Referential integrity and cascading: FOREIGN KEY constraint ensures that a child table row refers to an existing parent row. ON DELETE CASCADE removes child rows automatically when parent is deleted. ON DELETE SET NULL sets the foreign key in child to NULL. Use cascading carefully to avoid accidental data loss.

Example workflow (typical): CREATE DATABASE > USE db > CREATE TABLE(s) with constraints > LOAD or INSERT data > CREATE INDEX for common queries > ALTER TABLE when schema changes are required > DROP/TRUNCATE when removing objects.

Overview: In relational databases (SQL), keys and constraints ensure correct, unique and meaningful data. Keys identify tuples (rows) uniquely; constraints enforce rules (e.g., no nulls, valid references, value limits). Together they maintain data integrity.

Types of Keys

• Superkey: Any set of attributes that uniquely identifies a row. May contain extra attributes (redundant).
• Candidate key: A minimal superkey (no subset of it is a superkey). There can be multiple candidate keys.
• Primary key: One chosen candidate key to uniquely identify rows in a table. Cannot be NULL (entity integrity).
• Alternate key: Candidate keys that are not chosen as primary key.
• Composite (or Concatenated) key: A key made of two or more attributes together uniquely identifying a row.
• Foreign key: An attribute (or set) in one table whose values refer to primary key values in another table; enforces referential integrity.

Types of Constraints

• NOT NULL: Column cannot have NULL values.
• UNIQUE: Values in the column (or group) must be distinct across rows.
• PRIMARY KEY: Combination of NOT NULL + UNIQUE; declares the primary identifier for the table.
• FOREIGN KEY: Enforces that values match existing values in referenced table; optionally specifies actions on update/delete (CASCADE, SET NULL, NO ACTION).
• CHECK: Ensures column values satisfy a boolean condition (e.g., age >= 0).
• DEFAULT: Provides a default value when INSERT omits the column.

Integrity Rules

• Domain constraint: Each column must store values from a defined domain (type, format).
• Entity integrity: Primary key must not be NULL.
• Referential integrity: Foreign key values must match existing primary key values (or be NULL if allowed).

SQL examples (creation and alteration)

CREATE TABLE Student (
  roll_no INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  dob DATE,
  admission_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Course (
  course_id INT PRIMARY KEY,
  title VARCHAR(100) NOT NULL
);

CREATE TABLE Enrollment (
  student_roll INT,
  course_id INT,
  semester VARCHAR(10),
  PRIMARY KEY (student_roll, course_id), -- composite primary key
  FOREIGN KEY (student_roll) REFERENCES Student(roll_no) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES Course(course_id) ON DELETE NO ACTION
);

-- Adding a constraint later
ALTER TABLE Student ADD CONSTRAINT chk_email CHECK (email LIKE '%_@_%_.%');

How constraints affect operations

• INSERT/UPDATE violating NOT NULL, UNIQUE, PRIMARY KEY or CHECK will fail.
• Deleting a row referenced by a foreign key will fail unless ON DELETE CASCADE or ON DELETE SET NULL is used.
• Using constraints improves data quality and enables reliable joins between tables.

Overview

INSERT, UPDATE and DELETE are the three basic Data Manipulation Language (DML) SQL commands used to add, modify and remove rows in database tables. They operate on table data while keeping the table structure unchanged. Use WHERE clauses to limit UPDATE and DELETE to specific rows; without WHERE they affect all rows.

INSERT

INSERT adds new rows into a table. You can insert one row at a time or multiple rows. You may specify column names (recommended) or rely on the default column order. Constraints (PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY) apply when inserting.

<!-- Syntax -->
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);
<!-- Multiple rows -->
INSERT INTO table_name (col1, col2) VALUES (v1a, v2a), (v1b, v2b);
<!-- Insert from another table -->
INSERT INTO table_name (col1, col2) SELECT colA, colB FROM other_table WHERE ...;

UPDATE

UPDATE changes values of existing rows. Always use a WHERE clause to target specific rows; otherwise all rows in the table will be updated. You can update one or more columns at once. You can also use expressions, subqueries or joins (DBMS-specific features may vary).

<!-- Syntax -->
UPDATE table_name
SET col1 = expression1, col2 = expression2, ...
WHERE condition;

DELETE

DELETE removes rows from a table. With a WHERE clause it deletes only matching rows; without WHERE it deletes all rows but leaves the table structure intact. (To remove the table itself use DROP TABLE; to quickly empty a table some DBMS provide TRUNCATE TABLE.)

<!-- Syntax -->
DELETE FROM table_name WHERE condition;
<!-- Delete all rows -->
DELETE FROM table_name;  -- removes all rows

Important Points & Best Practices

• Always back up important data before running mass UPDATE/DELETE.
• Use transactions (BEGIN/COMMIT/ROLLBACK) when performing multiple related DML operations so changes can be undone if something goes wrong.
• Use WHERE to avoid accidental full-table updates/deletes.
• Prefer specifying column names in INSERT for clarity and safety when table structure changes.
• Watch constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL) — INSERT/UPDATE may fail if constraints are violated.
• Many DBMS offer RETURNING (or OUTPUT) clauses to return changed rows (e.g., INSERT ... RETURNING * in PostgreSQL).

Example walkthrough (Student table)

-- Table: Student(rollno INT PRIMARY KEY, name VARCHAR(50), marks INT)

-- 1) Insert a new student
INSERT INTO Student (rollno, name, marks) VALUES (101, 'Asha', 85);

-- 2) Update marks for rollno 101
UPDATE Student SET marks = 90 WHERE rollno = 101;

-- 3) Delete a student record
DELETE FROM Student WHERE rollno = 101;

Overview
The SELECT statement is the fundamental SQL command used to retrieve data from one or more tables. A SELECT query can pick specific columns, filter rows, sort results, remove duplicates, and compute aggregates.

Basic syntax

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column_list]
[HAVING aggregate_condition]
[ORDER BY column_list [ASC|DESC]]
[LIMIT n];

Clause explanations

• SELECT — lists columns or expressions you want returned. Use * to select all columns. You can use column aliases: SELECT name AS student_name.
• DISTINCT — removes duplicate rows from the result for the selected columns: SELECT DISTINCT city FROM Students.
• FROM — specifies the table(s) to read data from.
• WHERE — filters rows using conditions (comparison operators, logical operators, BETWEEN, IN, LIKE, IS NULL). WHERE applies before grouping and aggregation.
• GROUP BY — groups rows that have the same values in listed column(s). Used with aggregate functions (COUNT, SUM, AVG, MIN, MAX) to summarize data per group.
• HAVING — filters groups created by GROUP BY based on aggregate conditions (e.g., groups having SUM(sales) > 10000). HAVING is like WHERE but for groups.
• ORDER BY — sorts the result set by one or more columns or expressions; default is ASC (ascending). Use DESC for descending order.
• LIMIT / FETCH — (DBMS-specific) restricts the number of rows returned, useful for pagination or previewing results.

Execution (conceptual) order
Although written in the order above, SQL is conceptually processed in this order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. Understanding this helps write correct queries.

Common aggregate functions: COUNT(column_or_*), SUM(numeric_column), AVG(numeric_column), MIN(column), MAX(column). Aggregates often used with GROUP BY.

Examples explained
1) Filter: SELECT name, marks FROM Students WHERE marks >= 75; — returns names and marks of students who scored 75 or more.
2) Distinct: SELECT DISTINCT city FROM Students; — lists each city represented, once.
3) Group & aggregate: SELECT class, AVG(marks) AS avg_mark FROM Students GROUP BY class; — average marks per class.
4) Having: SELECT class, COUNT(*) AS num_students FROM Students GROUP BY class HAVING COUNT(*) > 30; — classes with more than 30 students.
5) Order & limit: SELECT name, marks FROM Students ORDER BY marks DESC LIMIT 5; — top 5 students by marks.

Tips:

• Columns in SELECT that are not aggregated must appear in GROUP BY (SQL standard rule).
• Use parentheses with complex logical conditions in WHERE for clarity: WHERE (city = 'Delhi' OR city = 'Mumbai') AND marks >= 60.
• Use indexes on columns used in WHERE/ORDER BY to improve performance.

What is filtering? In SQL, filtering means selecting only those rows from a table that satisfy one or more conditions. Filtering is done with the WHERE clause in a SELECT, UPDATE, or DELETE statement.

Basic structure:

SELECT column_list
FROM table_name
WHERE condition;

Comparison operators are used to form simple conditions:

• = equal to
• <> or != not equal to
• < less than
• <= less than or equal to
• > greater than
• >= greater than or equal to

Logical (conditional) operators combine conditions:

• AND — both conditions must be true
• OR — at least one condition must be true
• NOT — negates a condition

Other useful filtering operators:

• BETWEEN low AND high — range inclusive (e.g., age BETWEEN 10 AND 15)
• IN (value1, value2, ...) — matches any value in a list
• LIKE 'pattern' — pattern matching with wildcards: % (any sequence), _ (single character)
• IS NULL / IS NOT NULL — test for NULL values

Precedence: NOT is evaluated before AND, which is evaluated before OR. Use parentheses to make evaluation order explicit.

Important note about NULL: Comparisons with NULL (e.g., column = NULL) do not return true. Use IS NULL or IS NOT NULL.

Example queries:

-- Simple filter
SELECT name, marks FROM Students
WHERE marks >= 75;

-- Combined conditions
SELECT * FROM Employees
WHERE department = 'Sales' AND salary > 30000;

-- Range and list
SELECT product_name FROM Products
WHERE price BETWEEN 100 AND 500
  AND category IN ('Electronics', 'Home');

-- Pattern matching
SELECT name FROM Customers
WHERE name LIKE 'A%';

How filtering works in real life: Imagine a school register (table) — you can filter to find students who scored above 80, who are in Grade 11, or whose names start with 'S'. Filtering returns only the rows that match your criteria.

Tips for writing filters:

• Prefer parentheses for clarity if combining AND/OR.
• Be careful with NULLs — always use IS NULL rather than equality checks.
• Use LIKE for simple pattern matches; full-text search or regex may be needed for complex patterns.

Aggregate functions compute a single value from a set of rows. Common aggregate functions in SQL are COUNT(), SUM(), AVG(), MIN() and MAX(). They are used to get totals, averages, extremes and counts.

GROUP BY groups rows that have the same values in specified columns into summary rows so that aggregate functions can be applied per group. HAVING filters groups after aggregation (unlike WHERE, which filters rows before grouping).

Key behavior notes:

• Use non-aggregated columns in the SELECT clause only if they appear in GROUP BY.
• COUNT(column) ignores NULLs; COUNT(*) counts rows including NULL values in other columns.
• You can use DISTINCT inside aggregates (e.g., COUNT(DISTINCT col)) to count unique values.
• HAVING is used to restrict groups (e.g., groups with SUM > 1000).

Typical pattern:

SELECT grouping_columns, AGG(column)
FROM table
WHERE row_conditions
GROUP BY grouping_columns
HAVING group_conditions
ORDER BY something;

Examples of use in real life: monthly sales totals, average marks per class, number of customers per city, maximum salary per department.

What are Built-in Functions?
Built-in functions in SQL are predefined routines provided by the database engine to perform common operations on data — such as calculations, text manipulation, date/time extraction, type conversion and null handling. They simplify queries by doing data transformations directly in SELECT, WHERE, HAVING or ORDER BY clauses.

Main categories

• Aggregate functions — operate on a set of rows and return a single value (e.g., SUM, COUNT, AVG, MIN, MAX). Often used with GROUP BY.
• String (text) functions — manipulate text values (e.g., CONCAT, UPPER, LOWER, SUBSTRING, TRIM, LENGTH).
• Numeric functions — numeric processing (e.g., ROUND, CEILING, FLOOR, ABS, MOD, POWER).
• Date/time functions — extract or format date parts (e.g., NOW(), CURDATE(), YEAR(), MONTH(), DATE_FORMAT).
• Conversion functions — change data types (e.g., CAST(... AS type), CONVERT(..., type)).
• Null-handling / conditional — handle NULLs or conditional values (e.g., COALESCE, IFNULL, NULLIF, CASE).

Important notes

• Aggregate functions ignore NULLs (except COUNT(*)). Use COALESCE to replace NULLs if needed.
• Use GROUP BY when applying aggregates per category. Use HAVING to filter groups after aggregation.
• Functions can be nested: e.g., UPPER(TRIM(name)) or ROUND(AVG(price), 2).
• Some functions and exact names may vary slightly between SQL dialects (MySQL, PostgreSQL, SQL Server). CBSE examples assume standard/common functions.

Typical usage examples (SQL snippets)

-- Aggregates: total sales per product
SELECT product_id, SUM(amount) AS total_sales
FROM Sales
GROUP BY product_id
ORDER BY total_sales DESC;

-- String functions: display full name in uppercase
SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS full_name
FROM Students;

-- Date functions: extract year and month from order_date
SELECT order_id, YEAR(order_date) AS yr, MONTH(order_date) AS mth
FROM Orders;

-- Null handling: show 0 if no bonus
SELECT emp_id, COALESCE(bonus, 0) AS bonus_amount
FROM Employees;

-- Numeric function: round average price to 2 decimals
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM Products
GROUP BY category;

These built-in functions allow you to process and summarize data directly in queries, enabling concise reports and data cleaning without external tools.

Overview
In relational databases, data is distributed across multiple tables. Joins and combining operations let you retrieve and analyse related information by linking rows from two or more tables based on related columns or by concatenating result sets. Common join types are INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS, NATURAL and SELF JOIN. Other combining operations include UNION, UNION ALL, INTERSECT and EXCEPT/MINUS.

Why joins are used

• To reconstruct related information stored in normalized tables (for example, customer orders and order items).
• To filter or aggregate data that depends on relationships (for example, sales by product category).

Key concepts

• Join condition – a Boolean expression (usually equality of keys) that specifies how rows from tables match (for example A.id = B.a_id).
• Cartesian product – result of combining every row of one table with every row of another (happens with CROSS JOIN or missing join condition).
• Matched rows – rows that satisfy the join condition.
• Unmatched rows – rows that don’t satisfy the join condition (kept by OUTER JOINs with NULLs in missing columns).

Join types (short description)

• INNER JOIN – returns only rows having matching values in both tables.
• LEFT OUTER JOIN (LEFT JOIN) – returns all rows from left table; matched rows from right table or NULL where no match.
• RIGHT OUTER JOIN (RIGHT JOIN) – returns all rows from right table; matched rows from left or NULL when no match.
• FULL OUTER JOIN (FULL JOIN) – returns rows when there is a match in one of the tables. (Not supported directly in some DBMS; can be simulated using UNION of LEFT and RIGHT.)
• CROSS JOIN – Cartesian product of two tables (no join condition) – every row of A paired with every row of B.
• NATURAL JOIN – automatically joins on columns with the same names in both tables (use with caution to avoid unintended matches).
• SELF JOIN – table joined with itself to compare rows in the same table (use aliases to distinguish copies).

Combining result sets

• UNION – combines results of two SELECTs and removes duplicates; column list and types must match.
• UNION ALL – combines results and keeps duplicates.
• INTERSECT – returns rows common to both result sets (supported in some DBMS).
• EXCEPT / MINUS – returns rows in the first result set that are not in the second (EXCEPT in standard SQL, MINUS in Oracle).

Performance notes

• Ensure join columns are indexed (foreign key columns) to speed up joins on large tables.
• Avoid unnecessary CROSS JOINs and joining more tables than required.
• Use SELECT of only needed columns rather than SELECT * to reduce IO.

Example workflow
Typical steps when writing a join query: choose the main table, decide which related tables you need, set join types according to whether you want unmatched rows, write join conditions using ON or USING, and filter/aggregate as needed using WHERE, GROUP BY and HAVING.

What is a subquery? A subquery (or nested query) is an SQL query written inside another SQL query. The inner query runs first and its result is used by the outer query. Subqueries let you break complex questions into smaller queries and express conditions that depend on results computed from the database.

Why use subqueries? They help express conditions using aggregated values, select values from another table, filter rows based on existence, or compare a column to a set of values returned by another query.

Types of subqueries:

• Single-row (scalar) subquery: Returns one value (one row, one column). Used with operators like =, <, >.
• Single-column / multiple-row subquery: Returns one column but many rows. Often used with IN, NOT IN, or EXISTS.
• Correlated subquery: Refers to columns from the outer query. It is evaluated once per outer row.
• Scalar subquery: A subquery that returns exactly one value (useful in SELECT or WHERE comparisons).

Common subquery forms and keywords:

• IN / NOT IN — checks membership in a returned set.
• EXISTS / NOT EXISTS — checks whether the subquery returns any rows.
• ANY / ALL — compares a value to any or all values from the subquery.
• Aggregate in subquery — e.g., (SELECT AVG(...)) to compare against group results.

Execution order: For non-correlated subqueries, the DBMS evaluates the inner query first and then the outer query uses its result. For correlated subqueries, the inner query is re-evaluated for each row of the outer query.

When not to use subqueries: For some tasks JOINs are more efficient and easier for the optimizer to handle. Use subqueries when they make the logic clearer (e.g., comparing to aggregate values, existence checks) or when you need nested filtering that depends on intermediate results.

Example patterns (shown below in examples):

-- Compare to aggregate
SELECT name FROM Students WHERE marks > (SELECT AVG(marks) FROM Students);

-- Correlated: find rows relative to their group
SELECT e1.name FROM Employee e1
WHERE salary > (SELECT AVG(e2.salary) FROM Employee e2 WHERE e2.dept = e1.dept);

-- EXISTS: check related rows
SELECT c.customer_id FROM Customers c WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id);

-- IN: use set membership
SELECT product_name FROM Products WHERE category_id IN (SELECT id FROM Categories WHERE name = 'Stationery');

Performance note: Correlated subqueries can be slow on large tables because they run per outer row. Rewrite as JOINs or use indexed columns when possible. Use EXPLAIN or execution plans to compare alternatives.

What are Set Operations?

In SQL, set operations combine the result sets of two or more SELECT queries as if each result were a mathematical set. The main set operations are UNION (and UNION ALL), INTERSECT, and EXCEPT (or MINUS in some SQL dialects). They let you merge, find common rows, or subtract rows between queries.

Requirements and behaviour

• All SELECTs combined by a set operator must return the same number of columns, and each corresponding column must have compatible data types.
• Column names in the final result are taken from the first SELECT statement.
• By default set operations treat rows as sets: duplicates are removed. (UNION is equivalent to UNION DISTINCT.) Use UNION ALL to preserve duplicates.
• ORDER BY applies to the final combined result (and usually must appear at the end). Use parentheses to control the order of operations when mixing operators.
• NULLs: for the purpose of duplicate elimination and comparison in set operations, rows with NULLs in the same positions are considered the same.

Basic syntax

SELECT col1, col2, ... FROM table1
UNION [ALL]
SELECT col1, col2, ... FROM table2;

SELECT ... FROM ...
INTERSECT
SELECT ... FROM ...;

SELECT ... FROM ...
EXCEPT | MINUS
SELECT ... FROM ...;

Notes on precedence

SQL dialects may evaluate set operators left-to-right. To avoid ambiguity when combining operators (e.g., UNION with INTERSECT), put SELECT blocks in parentheses and add a final ORDER BY if needed.

What is a View?

A view is a virtual table defined by a SQL query. It does not store data itself (unless it's a materialized view); rather it presents data stored in one or more base tables using a SELECT statement. Users can query a view the same way they query a table.

Why use Views?

• Security: restrict access to specific columns/rows without giving access to the underlying table.
• Simplification: hide complex joins/filters behind a simple table-like interface.
• Reusability: centralize commonly used queries so multiple users/programs can reuse them.
• Abstraction: present data in a form that matches business needs without changing base tables.

Basic Syntax

CREATE VIEW view_name AS
SELECT column_list
FROM table_list
WHERE conditions;

-- Example: CREATE OR REPLACE VIEW view_name AS ...
-- DROP VIEW view_name;

Types of Views

• Simple view: based on a single table, may be updatable (INSERT/UPDATE/DELETE) if it meets certain rules.
• Complex view: uses JOINs, GROUP BY, aggregate functions, DISTINCT, subqueries — usually not updatable.
• Materialized view: stores the result set physically to improve performance. It must be refreshed when base data changes.

Updatable Views and WITH CHECK OPTION

Some views can be used to change (INSERT/UPDATE/DELETE) the underlying table. To enforce that changes through a view do not violate the view's conditions, use:

CREATE VIEW v AS SELECT ... FROM ... WHERE ...
WITH CHECK OPTION;

WITH CHECK OPTION prevents changes through the view that would cause rows to disappear from the view (i.e., violate the WHERE condition).

Advantages

• Security control: grant SELECT on a view but not on the base table.
• Consistency: present a stable interface even if table structures change.
• Reduced complexity for end-users and application code.

Limitations

• Performance: complex views (especially nested views) can be slower; materialized views trade freshness for speed.
• Not all views are updatable; DBMS-specific rules decide updatability.
• Views do not automatically reflect aggregated historical snapshots unless materialized.

Permissions

You can GRANT or REVOKE privileges on views just like tables. Granting SELECT on a view does not automatically grant privileges on the underlying tables.

Common operations (examples shown)

-- Create a view for students with average marks > 75
CREATE VIEW TopStudents AS
SELECT s.student_id, s.name, (m.math + m.science + m.english)/3.0 AS avg_marks
FROM Students s JOIN Marks m ON s.student_id = m.student_id
WHERE (m.math + m.science + m.english)/3.0 > 75;

-- Query the view
SELECT * FROM TopStudents;

-- Create an updatable view for employee phone numbers
CREATE VIEW EmpPhones AS
SELECT emp_id, name, phone
FROM Employees;

-- With check option to ensure phone updates keep emp in view conditions
CREATE VIEW ActiveEmployees AS
SELECT emp_id, name, dept
FROM Employees
WHERE status = 'ACTIVE'
WITH CHECK OPTION;

-- Drop a view
DROP VIEW TopStudents;

Best Practices

• Give meaningful names to views indicating their purpose (e.g., CurrentOrders, DeptSalesSummary).
• Keep views simple where possible; avoid deep nesting of views.
• Document whether a view is intended to be updatable or read-only.
• Use materialized views only when query performance is critical and data staleness is acceptable.

What is an index?
An index is a database structure that improves the speed of data retrieval operations on a table at the cost of additional writes and storage. It works like the index in a book — instead of scanning every page (full table scan), the DBMS uses the index to jump directly to the relevant rows.

Why use indexes?

• Faster SELECT queries that use WHERE, JOIN, ORDER BY or GROUP BY on indexed columns.
• Can enforce uniqueness (unique index).

Types of indexes (common types taught in Class 11 IP):

• Single-column index — index on one column: CREATE INDEX idx_name ON table(column);
• Composite (multi-column) index — index on multiple columns: CREATE INDEX idx_name ON table(col1, col2); useful when queries filter on col1 and col2 in that order.
• Unique index — enforces that indexed column(s) contain no duplicate values: CREATE UNIQUE INDEX ui_name ON table(column);
• Primary key index — automatically created for primary keys; often a clustered index.
• Clustered vs Non-clustered — clustered index defines physical order of rows (one per table); non-clustered stores pointers to rows (many possible).
• Index implementations — B-tree (general purpose), hash (good for equality), bitmap (low cardinality). In students' curriculum focus on B-tree conceptually.

How indexes help (conceptual):
A B-tree index reduces search cost from scanning n rows (O(n)) to a tree traversal cost approximately O(log n). The DBMS finds the location via the tree and retrieves only relevant rows.

SQL examples:

-- Create a single-column index
CREATE INDEX idx_students_roll ON Students(roll_no);

-- Create a unique index
CREATE UNIQUE INDEX ux_students_email ON Students(email);

-- Create a composite index
CREATE INDEX idx_students_class_name ON Students(class, last_name);

-- Drop index (syntax varies by DBMS; example for MySQL)
DROP INDEX idx_students_roll ON Students;

-- Alternative using ALTER TABLE (MySQL)
ALTER TABLE Students ADD INDEX idx_join_col (class);

Using EXPLAIN to check index usage:

EXPLAIN SELECT * FROM Students WHERE roll_no = 101;
-- The output will show if the index idx_students_roll is being used

Advantages:

• Greatly speeds up read operations (SELECT).
• Can enforce uniqueness.

Disadvantages / trade-offs:

• Indexes consume storage space.
• Every INSERT/UPDATE/DELETE that changes indexed columns must update the index — slower write performance.
• Poor choice of indexes (low selectivity) may not help performance.

When to create an index?

• Columns frequently used in WHERE, JOIN, ORDER BY, GROUP BY.
• Columns with high selectivity (many distinct values).
• Not on small lookup tables, frequently updated columns, or low-cardinality columns (like boolean fields) unless part of specialized indexes.

Important terms:

• Selectivity — how many distinct values a column has relative to rows; higher is better for indexing.
• Covering index — an index that contains all columns needed by a query so the DBMS can answer the query using only the index without touching the table.

Practical notes for students: Practice creating/dropping indexes and use EXPLAIN to compare query plans. Observe how indexed queries run faster for large tables but inserts become slower.

Transaction — Definition
A transaction is a logical unit of work that groups one or more SQL operations (INSERT/UPDATE/DELETE/SELECT) so they execute together. A transaction moves the database from one consistent state to another.

Why use transactions?
To ensure data integrity, recoverability and correct concurrent behavior when multiple users access/modify the database.

Transaction control commands (SQL)

BEGIN TRANSACTION;   -- start transaction (some systems use START TRANSACTION)
... SQL statements ...
COMMIT;              -- make changes permanent
ROLLBACK;            -- undo changes since transaction began
SAVEPOINT sp_name;   -- create a point to roll back to
ROLLBACK TO sp_name; -- roll back to that savepoint

ACID properties

• Atomicity — "all or nothing". Either all operations in a transaction succeed and are committed, or if any step fails, all changes are undone (rolled back). Example: in a bank transfer, debiting one account and crediting another must both occur, or neither.
• Consistency — Transactions must preserve database rules, constraints and integrity (primary keys, foreign keys, triggers, custom constraints). If the database was consistent before the transaction, it must be consistent after it (assuming the transaction commits).
• Isolation — Concurrent transactions should not interfere with each other. The intermediate states of a transaction are invisible to other transactions until it commits. Different isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control trade-offs between performance and isolation.
• Durability — Once a transaction commits, its changes are permanent even if the system crashes immediately after. Durability is usually implemented by writing redo logs or commit records to stable storage.

Common concurrency anomalies

• Dirty Read — Transaction A reads uncommitted changes made by Transaction B (possible under READ UNCOMMITTED).
• Non-repeatable Read — A row read twice in the same transaction returns different values because another transaction updated and committed between reads (possible under READ COMMITTED).
• Phantom Read — A query returns a different set of rows in the same transaction because another transaction inserted or deleted rows (possible under lower isolation levels).
• Lost Update — Two transactions update the same row; one update overwrites the other.

Transaction lifecycle (brief)

• Active — Transaction is executing statements.
• Partially Committed — Last statement executed but commit not complete.
• Committed — Changes permanently recorded.
• Failed/Aborted — Error occurred and transaction rolled back.
• Terminated — Transaction ended (committed or rolled back).

Practical tips for students

• Wrap multi-step operations (bank transfer, stock decrement + order create) in a transaction to avoid inconsistencies.
• Use SAVEPOINT to avoid rolling back entire work when only part must be undone.
• Choose an isolation level appropriate for the application: high consistency (SERIALIZABLE) for critical financial ops, lower levels for read-heavy apps to improve performance.

What are privileges and security? Privileges (or permissions) control what a database user can do (read, write, modify, execute). Security is the set of practices, configurations and controls that protect data confidentiality, integrity and availability. In SQL, privileges are used to authorize actions on database objects (tables, views, procedures) and system actions (create user, create database).

Types of privileges

• Object privileges: apply to specific schema objects. Common ones: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, INDEX, REFERENCES, EXECUTE.
• System/Administrative privileges: global actions such as CREATE USER, CREATE TABLE, CREATE DATABASE, GRANT ANY PRIVILEGE, etc.
• Role-based privileges: roles group privileges. Roles simplify privilege management by assigning a role to users instead of many individual privileges.

Key SQL commands

• GRANT — give privilege(s) to a user or role.
• REVOKE — remove previously granted privilege(s).
• CREATE ROLE / DROP ROLE — create or remove roles.

Important options and behaviors

• WITH GRANT OPTION: allows the grantee to grant that privilege to others.
• Privilege hierarchy / ownership: object owners and DBAs typically have full control. Public is a special pseudo-user to give privileges to all users.
• Definer vs Invoker rights: stored routines can run with the privileges of the definer (creator) or invoker (caller) — affects access control.

Security best practices

• Follow the principle of least privilege: grant only the privileges needed to perform tasks.
• Use roles to manage privileges centrally (easier auditing and changes).
• Use views to expose only necessary columns/rows (column- and row-level security).
• Enforce strong authentication (complex passwords, account lockout, multi-factor where supported).
• Protect against SQL injection: use parameterized queries / prepared statements and validate input.
• Use encryption for data at rest and in transit (SSL/TLS), and secure backups.
• Audit and log privilege changes and access patterns.

How it works (flow)

1. A user authenticates (username/password or other method).
2. The DBMS checks user roles and privileges.
3. If a requested action matches a granted privilege, the operation proceeds; otherwise it is denied.

Common classroom examples include granting SELECT to reporting users, creating a role for HR staff with SELECT/UPDATE on employee tables, and creating read-only views for interns.

Overview

Referential Actions and Data Integrity ensure that relationships between tables remain consistent. Referential integrity enforces that a foreign key (FK) in a child table must either match a primary key (PK) value in a parent table or be NULL (if allowed). Referential actions define what the database should do to child rows when the referenced parent row is updated or deleted.

Types of Data Integrity

• Entity integrity: Primary key values must be unique and NOT NULL.
• Referential integrity: Foreign keys must reference existing primary keys (or be NULL).
• Domain integrity: Column values must be from a valid domain (type, range, format).
• User-defined integrity: Business rules implemented via constraints, triggers, or application logic.

Referential Actions (ON DELETE / ON UPDATE)

When defining a foreign key, you can specify actions the DBMS performs if the referenced row is updated or deleted:

• CASCADE: Propagates the change to child rows. ON DELETE CASCADE removes child rows when the parent is deleted. ON UPDATE CASCADE updates child FK values when the parent PK changes.
• SET NULL: Sets the child FK to NULL when the parent is deleted/updated. Use only if the FK column allows NULL.
• SET DEFAULT: Sets the child FK to a default value when the parent is deleted/updated (requires a DEFAULT defined).
• RESTRICT: Prevents the delete/update on the parent if any child rows reference it. The delete/update is rejected immediately.
• NO ACTION: Standard SQL behavior similar to RESTRICT in many DBMSs; difference arises when constraint checking is deferred (NO ACTION defers checking until end of transaction, RESTRICT checks immediately).

Typical SQL syntax

CREATE TABLE Child (

  child_id INT PRIMARY KEY,

  parent_id INT,

  FOREIGN KEY (parent_id) REFERENCES Parent(parent_id)

    ON DELETE CASCADE

    ON UPDATE NO ACTION

);

Behavior examples (summary)

• Parent row deleted + ON DELETE CASCADE → child rows deleted automatically.
• Parent row deleted + ON DELETE SET NULL → child foreign key values become NULL.
• Parent row deleted + ON DELETE RESTRICT/NO ACTION → delete is refused if child rows exist.

When to use each action

• CASCADE: Use when child rows are meaningful only in context of parent (e.g., order items when an order is removed).
• SET NULL / SET DEFAULT: Use when child can exist without a parent but should lose the reference (e.g., employee's manager removed).
• RESTRICT/NO ACTION: Use when parent must not be removed if dependents exist (e.g., customer must not be deleted if they have orders).

Important notes

• Many DBMSs default to NO ACTION if no referential action is specified.
• Multi-level cascades will propagate through chains of FK relationships; be careful to avoid accidental large deletions.
• Changing a primary key value is rare; ON UPDATE CASCADE helps keep child FKs consistent if you do.

Overview: Practical Query Techniques and Best Practices teach how to write correct, readable, secure and efficient SQL queries. The goal is to retrieve and manipulate data reliably while minimizing resource use and response time.

Stepwise approach to build queries

• Identify the exact columns required — avoid SELECT * to reduce I/O and transfer time.
• Filter rows as early as possible with WHERE (row-level filtering) rather than later stages.
• Choose appropriate joins to combine related tables; prefer INNER JOIN when you only want matching rows.
• Aggregate only after filtering; use GROUP BY and HAVING correctly (WHERE filters rows before aggregation; HAVING filters grouped results).
• Limit result sets for paging (LIMIT/OFFSET) in UI-driven queries.

Readability & maintainability

• Use meaningful aliases for tables and columns (e.g., s for students, m for marks).
• Break complex queries into CTEs (WITH ... AS) or views for clarity.
• Comment non-obvious logic so others (and future you) understand intent.

Performance & optimization

• Use indexes for columns used in WHERE, JOIN ON and ORDER BY — they speed lookups. Avoid functions on indexed columns in WHERE (e.g., WHERE UPPER(name) = 'A') since they can prevent index use.
• Prefer INDEX-supported comparisons and equality predicates; aim for selective predicates (high selectivity returns few rows).
• Avoid unnecessary DISTINCT, ORDER BY or GROUP BY when not needed — they cause sorting/aggregation overhead.
• Use EXPLAIN/EXPLAIN ANALYZE to inspect query execution plans and identify full table scans or expensive operations.
• Consider proper join order and join types; databases optimize joins, but reducing input size with early filters helps.

Correct use of subqueries, EXISTS, IN and joins

• Use JOIN to combine columns from related tables when you need columns from both tables.
• Use EXISTS for semi-joins (check existence) and correlated subqueries when dependent on outer row values; EXISTS can be faster than IN for large subquery result sets.
• Use IN for fixed lists or small subquery results; for large lists use joins or temporary tables.

Safety, transactions & parameters

• Use parameterized queries or prepared statements to prevent SQL injection — never concatenate user input into SQL strings.
• Wrap multiple DML statements (INSERT/UPDATE/DELETE) in transactions to enforce atomicity (COMMIT/ROLLBACK).
• Perform backups and test destructive queries (DELETE/UPDATE) first with SELECT to verify affected rows.

Practical habits

• Test queries on a sample dataset before running on production.
• Profile slow queries and add targeted indexes rather than indexing everything.
• Use pagination for web apps and fetch only required columns for mobile apps to save bandwidth.

Summary: Combine clear, well-structured SQL with filtering-first logic, judicious use of indexes, safe parameterized execution, and analysis of execution plans. These practices produce correct results, better performance and easier maintenance.