Chapter 9 — Sql

What are SQL Data Types?

SQL data types define the kind of values a column can hold (numbers, text, dates, binary, etc.). Choosing the right data type affects correctness, storage, indexing and performance.

Main categories and key types

• Numeric
  • INT / SMALLINT / BIGINT — integers for counters, ids. Typical sizes: SMALLINT (2 bytes), INT (4 bytes), BIGINT (8 bytes).
  • DECIMAL(p,s) / NUMERIC(p,s) — exact fixed-point (money). p = precision (total digits), s = scale (digits after decimal).
  • FLOAT / REAL / DOUBLE — approximate floating-point (scientific values, measurements). Not recommended for money.
• Character / String
  • CHAR(n) — fixed-length strings (padded). Good for codes of known length.
  • VARCHAR(n) — variable-length strings (efficient when length varies).
  • TEXT (or CLOB) — large text blocks (descriptions, articles).
• Date & Time
  • DATE — calendar date (YYYY-MM-DD).
  • TIME — time of day (HH:MM:SS).
  • DATETIME / TIMESTAMP — combined date and time (timestamp often used for UTC/timezone-aware values).
• Binary / Large Objects
  • BLOB — binary large objects (images, files).
• Boolean & Enumerations
  • BOOLEAN (TRUE/FALSE), ENUM or SET (vendor-specific) for limited choices.
• NULL — indicates absence of a value; columns may allow or disallow NULL.

Important distinctions & best practices

• Use DECIMAL(p,s) for monetary values to avoid rounding errors of floating point.
• Choose fixed-size CHAR for truly fixed-length fields (like country codes), and VARCHAR for variable-length text to save space.
• Store identifiers that are not used for arithmetic (phone numbers, ZIP codes) as strings (VARCHAR), not integers.
• Smaller types reduce storage and improve index performance. Don’t over-allocate (e.g., avoid VARCHAR(1000) if 50 chars suffice).
• Be aware of DBMS-specific behavior (storage overhead, max lengths, ENUM availability).

Example CREATE TABLE

<code>CREATE TABLE Employees (
  EmpID INT PRIMARY KEY AUTO_INCREMENT,
  Name VARCHAR(100) NOT NULL,
  Phone VARCHAR(15),          -- stored as text to preserve leading zeros
  BirthDate DATE,
  Salary DECIMAL(10,2),       -- precise currency
  Rating FLOAT,               -- approximate measurement
  Photo BLOB,
  IsActive BOOLEAN DEFAULT TRUE
);
</code>

Choosing a type — quick decision guide

• If exact arithmetic (money) → DECIMAL(p,s).
• If fixed-width code (2-letter country) → CHAR(2).
• If variable text (names) → VARCHAR(n).
• If large text → TEXT/CLOB.
• If binary data (image) → BLOB.

What is DDL?

Data Definition Language (DDL) is the subset of SQL commands used to define, modify and remove database structures such as tables, schemas and indexes. DDL commands affect the database schema (structure) but do not directly manipulate the data stored (that is handled by DML).

Common DDL commands

• CREATE — create a new database object (table, view, index, schema).
• ALTER — modify the structure of an existing object (add/drop/modify columns, constraints).
• DROP — remove an object permanently from the database.
• TRUNCATE — remove all rows from a table quickly while keeping the table definition.
• RENAME — change the name of a database object.
• COMMENT — add descriptive comments to database objects (supported in some DBMS).

Key characteristics

• DDL commands change the schema and are usually auto-committed (most DBMS perform an implicit COMMIT before and after DDL).
• DDL affects metadata stored in the data dictionary (catalog).
• DDL is generally used by DBAs and developers when designing or changing the database layout.

Example elements of a CREATE TABLE

• Column name and data type (e.g., name VARCHAR(50)).
• Column constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.
• Table-level constraints such as composite primary keys or foreign keys.

Difference between DDL and DML

DDL (CREATE, ALTER, DROP...) defines and alters structure. DML (INSERT, UPDATE, DELETE, SELECT) manipulates data inside those structures.

Practical notes

• Use DROP with caution — it permanently removes structure and usually the data within.
• TRUNCATE is faster than DELETE for removing all rows but cannot be rolled back in many DBMS.
• When altering production schemas, plan migrations, backups, and downtime to avoid data loss.

Small code examples

CREATE TABLE Student (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(10),
  dob DATE
);

ALTER TABLE Student ADD COLUMN mobile VARCHAR(15);

DROP TABLE Student;

TRUNCATE TABLE Student;

RENAME TABLE Student TO Student_Info; -- syntax varies by DBMS

Definition: Data Manipulation Language (DML) is the subset of SQL commands used to retrieve, insert, modify and delete data stored in a relational database. DML operations act on table rows and are used in everyday application tasks.

Primary DML commands:

• SELECT — retrieve data (reads).
• INSERT — add new rows (creates).
• UPDATE — change existing rows (modifies).
• DELETE — remove rows (deletes).
• MERGE — conditional insert/update (upsert) in some RDBMS.

Notes: DML changes the data within tables. Transaction control (COMMIT, ROLLBACK) belongs to TCL — you usually commit or rollback DML operations to make changes permanent or undo them.

Important concepts:

• WHERE clause: restricts which rows are affected; omitting WHERE on UPDATE/DELETE affects all rows — use carefully.
• SELECT modifiers: ORDER BY, GROUP BY, HAVING, DISTINCT, JOINs and aggregate functions (COUNT, SUM, AVG, MIN, MAX) are commonly used with SELECT.
• Transactions: group multiple DML statements into an atomic unit so either all succeed (COMMIT) or none (ROLLBACK).
• Permissions: users need appropriate rights (INSERT, UPDATE, DELETE, SELECT) to run DML commands.

Example snippets:

-- SELECT
SELECT student_id, name, marks FROM students WHERE class = 12 ORDER BY marks DESC;

-- INSERT
INSERT INTO students(student_id, name, class, marks)
VALUES (101, 'Asha', 12, 86);

-- UPDATE
UPDATE students SET marks = marks + 5 WHERE student_id = 101;

-- DELETE
DELETE FROM students WHERE student_id = 101;

-- MERGE (pseudo-syntax varies by RDBMS)
MERGE INTO inventory AS tgt
USING (SELECT 'P100' AS code, 50 AS qty) AS src
ON (tgt.code = src.code)
WHEN MATCHED THEN UPDATE SET tgt.qty = tgt.qty + src.qty
WHEN NOT MATCHED THEN INSERT (code, qty) VALUES (src.code, src.qty);

Best practices: always test UPDATE/DELETE with a SELECT using the same WHERE first, use transactions for multi-step changes (especially for financial updates), and add appropriate indexes to speed up SELECTs.

Overview

In SQL, controlling access to data and ensuring correctness of multi-step operations are handled by two groups of commands:

• Data Control Language (DCL) — commands that grant and revoke privileges on database objects.
• Transaction Control Language (TCL) — commands that manage transactions to ensure integrity (ACID properties).

Data Control Language (DCL)

DCL controls who can do what in the database. Typical commands:

• GRANT — give specific privileges (SELECT, INSERT, UPDATE, DELETE, ALTER, etc.) to a user or role.
• REVOKE — remove previously granted privileges.

Key points:

• Privileges can be granted on objects: tables, views, procedures, etc.
• WITH GRANT OPTION allows the grantee to pass the same privilege to others.
• Revoking a privilege may cascade, removing privileges that were granted by the revoked user if the system supports cascade behavior.

Transaction Control Language (TCL)

TCL commands manage transactions — a transaction is a sequence of SQL statements treated as a single logical unit. Transactions must satisfy ACID:

• Atomicity — all steps succeed or none do.
• Consistency — transaction moves DB from one valid state to another.
• Isolation — concurrent transactions do not interfere (controlled via isolation levels).
• Durability — once committed, changes persist even after failures.

Common TCL commands:

• START TRANSACTION or BEGIN — begin a transaction (some DBMS implicitly start transactions or use autocommit).
• COMMIT — make all changes in the current transaction permanent.
• ROLLBACK — undo all changes since the start of the transaction or since a savepoint.
• SAVEPOINT name — create a point within a transaction to which you can rollback partially.
• ROLLBACK TO SAVEPOINT name — revert changes only back to the savepoint.

How DCL and TCL interact

Privileges (DCL) determine what operations a user can run. TCL ensures that multi-step operations by allowed users are completed reliably. For example, a user with UPDATE privilege can run a transaction that updates several rows; TCL ensures either all rows are updated (COMMIT) or none (ROLLBACK) if an error occurs.

Practical considerations

• Many DBMSs run in autocommit mode by default (every statement committed immediately). Disable autocommit for multi-statement transactions.
• Use SAVEPOINTs for complex transactions where partial rollback is useful.
• Carefully manage GRANT/REVOKE to follow least-privilege principle (give users only necessary rights).

Small example (MySQL-style)

-- Grant read and update on employees table to user1
GRANT SELECT, UPDATE ON company.employees TO 'user1'@'localhost';

-- Start a transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 101; -- debit
UPDATE accounts SET balance = balance + 100 WHERE id = 202; -- credit
-- if both succeed
COMMIT;
-- if an error occurs
ROLLBACK;

Summary

DCL secures who can access or modify database objects. TCL secures how multi-statement changes are applied, making sure operations are atomic, consistent, isolated, and durable.

Overview
In SQL a constraint is a rule applied on table columns to enforce data integrity and correctness. Keys are special constraints/attributes used to uniquely identify rows and to establish relationships between tables.

Why they matter
Constraints prevent invalid data (e.g., nulls where not allowed, duplicate IDs, or invalid values). Keys let the database enforce uniqueness and referential integrity (e.g., linking orders to customers).

Common constraints

• NOT NULL — column must have a value.
• UNIQUE — values in a column (or column set) must be distinct.
• PRIMARY KEY — combination of NOT NULL + UNIQUE; uniquely identifies a row.
• FOREIGN KEY — value(s) in a column must match a primary (or unique) key in another table; enforces referential integrity.
• CHECK — enforces a condition (e.g., salary >= 0).
• DEFAULT — supplies a default value when none is provided.

Key types (relational concepts)

• Superkey — any set of columns that uniquely identifies rows.
• Candidate key — minimal superkey (no subset is a superkey).
• Primary key — chosen candidate key for row identification.
• Alternate key — candidate keys not chosen as primary.
• Composite key — primary key made of multiple columns.
• Foreign key — references a primary/unique key in another table.

Typical SQL syntax examples

CREATE TABLE Student (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age BETWEEN 5 AND 25)
);

CREATE TABLE Enrollment (
  enroll_id INT PRIMARY KEY,
  student_roll INT,
  course_id INT,
  FOREIGN KEY (student_roll) REFERENCES Student(roll_no) ON DELETE CASCADE
);

-- Add constraint later
ALTER TABLE Student ADD CONSTRAINT chk_salary CHECK (salary >= 0);

Behavior & enforcement
When data-modifying statements (INSERT/UPDATE/DELETE) would violate a constraint, the DBMS rejects the operation and returns an error. Foreign keys may also specify actions (CASCADE, SET NULL, RESTRICT) for deletes/updates on referenced rows.

Overview: The SELECT statement is the fundamental SQL command used to retrieve data from one or more tables. It is combined with clauses (FROM, WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT, LIMIT) to filter, group, sort and shape the result set.

Basic syntax:

SELECT <columns> FROM <table> [WHERE <condition>] [GROUP BY <columns>] [HAVING <group_condition>] [ORDER BY <columns> [ASC|DESC]] [LIMIT <n>];

Key parts explained:

• SELECT <columns> — list columns or expressions to return. Use * to select all columns.
• FROM <table> — the source table(s) or subqueries.
• WHERE — row-level filter using comparison (=, <>, <, >, <=, >=), logical operators (AND, OR, NOT), BETWEEN, IN, LIKE.
• GROUP BY — aggregate rows into groups (used with aggregate functions like COUNT, SUM, AVG, MIN, MAX).
• HAVING — filter groups created by GROUP BY (acts like WHERE but for groups).
• ORDER BY — sort result rows by one or more columns (ASC default, DESC for descending).
• DISTINCT — remove duplicate rows from the result set.
• LIMIT / OFFSET — restrict number of returned rows and paginate.
• Aliases (AS) — give temporary names to columns or tables for clarity.

Aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX(). Aggregates are often used with GROUP BY.

Order of evaluation (conceptual): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Understanding this helps when writing queries that mix aggregates and row filters.

Best practices: specify explicit column names rather than SELECT * in production; use WHERE to reduce scanned rows; use aliases for readability; test queries with LIMIT during development.

Overview: Filtering in SQL means selecting only those rows from a table that satisfy a condition. This is done using the WHERE clause and various conditional (comparison and logical) operators. Filtering reduces result sets to relevant data.

Key building blocks:

• WHERE clause: Applies conditions to rows. Syntax: SELECT columns FROM table WHERE condition;
• Comparison operators: =, <>, <, >, <=, >= to compare values.
• Logical operators: AND, OR, NOT to combine or invert conditions.
• Range and list tests: BETWEEN ... AND ... (inclusive) and IN (val1, val2,...) (matches any listed value).
• Pattern matching: LIKE with wildcards % (zero or more chars) and _ (single char).
• NULL checks: Use IS NULL or IS NOT NULL because = NULL does not work.
• Existence tests: EXISTS (subquery) checks whether subquery returns any row.

Operator precedence: NOT evaluated first, then AND, then OR. Use parentheses (...) to force grouping.

Examples of use (short snippets):

-- students scoring more than 75
SELECT name, marks FROM Students WHERE marks > 75;

-- employees in Sales with salary >= 50000
SELECT emp_id, name FROM Employees WHERE dept = 'Sales' AND salary >= 50000;

-- products priced between 100 and 500
SELECT prod_id, price FROM Products WHERE price BETWEEN 100 AND 500;

-- customers whose name starts with 'A'
SELECT name FROM Customers WHERE name LIKE 'A%';

-- orders with NULL delivery_date
SELECT order_id FROM Orders WHERE delivery_date IS NULL;

-- rows where a related record exists
SELECT c.customer_id FROM Customers c WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id);

Good practices:

• Use parentheses for clarity when combining multiple logical operators.
• Prefer indexed columns for WHERE conditions to improve performance.
• Use EXISTS for correlated-subquery existence checks; use IN for small lists.

What are Aggregate Functions?
Aggregate functions perform calculations on a set of rows and return a single value. They are used to summarize data. Common aggregate functions in SQL are COUNT, SUM, AVG, MIN, and MAX. Aggregate functions ignore NULL values except where noted (e.g., COUNT(*) counts rows including NULLs).

Basic usage (no grouping)
When used without GROUP BY, an aggregate function computes a result across all selected rows. Example: SELECT AVG(marks) FROM Students; returns the average marks of all students.

Grouping rows: GROUP BY
GROUP BY divides rows into groups based on the values of one or more columns. Aggregate functions then compute a value per group. Example: SELECT class, AVG(marks) FROM Students GROUP BY class; computes the average marks for each class.

Filtering groups: HAVING vs WHERE
Use WHERE to filter rows before aggregation. Use HAVING to filter groups after aggregation. Example: SELECT class, COUNT(*) FROM Students GROUP BY class HAVING COUNT(*) > 30; lists classes with more than 30 students.

Multiple aggregates and grouping columns
You can compute several aggregates at once and group by multiple columns: SELECT department, gender, AVG(salary), MAX(salary) FROM Employees GROUP BY department, gender;

NULL behavior and DISTINCT
NULL values are ignored by SUM, AVG, MIN, MAX. Use COUNT(*) to count all rows; use COUNT(column) to count non-NULL values. Use DISTINCT inside aggregates to remove duplicates, e.g. COUNT(DISTINCT city).

Examples of SQL patterns

-- Aggregate across whole table
SELECT COUNT(*) AS total_students, AVG(marks) AS avg_marks FROM Students;

-- Grouping
SELECT class, AVG(marks) AS avg_marks
FROM Students
GROUP BY class
ORDER BY avg_marks DESC;

-- Grouping with HAVING
SELECT product_category, SUM(units_sold) AS total_units
FROM Sales
GROUP BY product_category
HAVING SUM(units_sold) > 1000;

-- Multiple grouping columns
SELECT region, product, SUM(revenue) FROM Sales
GROUP BY region, product;

When to use
Aggregate functions and grouping are used whenever you need summary statistics: totals, averages, counts, minima/maxima per category, distributions, comparisons between groups, etc. They are essential in reports, dashboards and data analysis.

Overview
Scalar (built-in) functions in SQL are predefined routines that take one or more input values (usually from a single row) and return a single value. They are applied to individual column values and are different from aggregate functions (like SUM, AVG) that work on groups of rows.

Key characteristics

• Operate on a single value (per row) and return one result per row.
• Used in SELECT lists, WHERE, ORDER BY, HAVING, and expressions.
• Built-in categories: string, numeric, date/time, conversion, NULL-handling and system functions.
• Many SQL dialects (MySQL, Oracle, SQL Server) provide similar functions but names/syntax can differ slightly.

Common categories & examples

• String functions: change or examine text. Examples: UPPER, LOWER, LTRIM, RTRIM, TRIM, CONCAT, SUBSTR, LENGTH.
• Numeric functions: math and rounding. Examples: ABS, ROUND, CEIL / CEILING, FLOOR, MOD, POWER, SQRT.
• Date/time functions: extract or manipulate dates. Examples: CURRENT_DATE, SYSDATE, NOW(), EXTRACT, DATE_ADD (MySQL) / ADD_MONTHS (Oracle), MONTHS_BETWEEN.
• Conversion functions: change data types or formats. Examples: CAST, TO_CHAR, TO_DATE, CONVERT.
• NULL-handling: handle missing values. Examples: COALESCE, NVL, NULLIF.
• Conditional: return conditional results. Example: CASE ... WHEN ... THEN ... END.

How they are used (patterns)

• Normalizing data: SELECT UPPER(name) FROM students; ensures consistent casing before grouping or comparing.
• Formatting output: SELECT TO_CHAR(join_date, 'DD-MON-YYYY') FROM employees;
• Calculations per row: SELECT salary, ROUND(salary * 0.12, 2) AS tax FROM employees;
• Nesting functions: SELECT UPPER(SUBSTR(name,1,1)) FROM students;
• NULL handling: SELECT COALESCE(mobile, 'Not Given') FROM contacts;

Important notes

• Order and nesting matter: functions are evaluated from inner to outer.
• Functions may return NULL if input is NULL (unless specifically handling NULLs with COALESCE/NVL).
• Performance: using functions on columns in WHERE clauses can prevent index usage (e.g., avoid WHERE UPPER(col) = 'X' on large indexed tables; better store normalized data or use functional indexes).

Short example queries (conceptual)

-- Show students with formatted names and rounded percentages
SELECT id,
       UPPER(name) AS name_upper,
       ROUND(marks * 100.0 / max_marks, 2) AS percent
FROM students;

-- Fill missing phone numbers
SELECT id, name, COALESCE(phone, 'No Phone') FROM students;

-- Create a marks-range label
SELECT CASE
         WHEN marks >= 90 THEN '90+'
         WHEN marks >= 80 THEN '80-89'
         WHEN marks >= 70 THEN '70-79'
         ELSE 'Below 70'
       END AS range_label,
       COUNT(*)
FROM students
GROUP BY range_label;

This covers the essentials of scalar and built-in functions for Class 12 Computer Science: what they do, major types, usage patterns, and best practices.

What is a JOIN? A JOIN combines rows from two (or more) tables based on a related column between them. Joins let you query related data stored separately (for normalization) and present it as a single result set.

Core idea: When two tables are joined, rows are matched by a condition (usually equality on keys). If no condition is specified, the result is a Cartesian product (every row of table A paired with every row of table B).

Common types of joins:

• INNER JOIN (Equijoin) — returns rows that have matching values in both tables. Syntax: SELECT ... FROM A INNER JOIN B ON A.key = B.key. This is the most used join.
• CROSS JOIN — Cartesian product. If A has m rows and B has n rows, result has m × n rows. Syntax: FROM A CROSS JOIN B or FROM A, B (comma form).
• LEFT (OUTER) JOIN — returns all rows from the left table; matched rows from the right table; unmatched right columns are NULL. Syntax: FROM A LEFT JOIN B ON ....
• RIGHT (OUTER) JOIN — returns all rows from the right table; matched rows from the left table; unmatched left columns are NULL. Syntax: FROM A RIGHT JOIN B ON ....
• FULL (OUTER) JOIN — returns rows when there is a match in either table; unmatched columns from either side are NULL. Syntax: FROM A FULL JOIN B ON ... (supported in many DBMS).
• SELF JOIN — a table joined with itself. Useful for hierarchical or comparative queries. Use table aliases to distinguish the two instances.
• NATURAL JOIN — automatically joins using all columns with the same names in both tables. Use with care (implicit columns can cause unexpected matches).
• THETA / NON-EQUI JOIN — join with a condition other than equality, e.g., A.value > B.limit.

Related concepts:

• Primary key / Foreign key — foreign keys define referential relationships used for joins (e.g., student_id in Enrollment references Student(id)).
• Referential integrity — ensures that foreign key values match primary key values (or are NULL), keeping joins meaningful.
• NULL handling — outer joins produce NULLs for missing matches; use COALESCE() or IS NULL checks to handle them.
• Using ON vs USING — ON allows arbitrary join conditions; USING (col) simplifies syntax when both tables share the same column name and you want to join on it.
• Performance — indexes on join columns speed up joins. Join order and query plan matter for large tables.

Notes for Class 12 level:

• INNER JOIN is the same as writing explicit equality conditions (equijoin).
• Cartesian product (CROSS JOIN) is rarely useful directly, but important to understand as the default when no condition is provided.
• Outer joins are used when you want to retain non-matching rows from one or both tables.

What is a subquery?
A subquery (also called an inner query or nested query) is a SELECT statement placed inside another SQL statement (the outer query). The subquery returns a value or a set of values that the outer query uses to filter, compare or compute results.

Why use subqueries?
They let you break complex questions into simpler parts, compute values (like averages, maxima) on the fly, and write queries that depend on results of other queries without creating temporary tables.

Types of subqueries

• Non-correlated (independent) subquery: runs once and its result is used by the outer query.
• Correlated subquery: references columns from the outer query and is re-evaluated for each row of the outer query.
• Scalar subquery: returns exactly one value (one row, one column) and can be used wherever an expression is allowed.
• Row subquery: returns one row with multiple columns and can be used in comparisons like (col1, col2) = (subquery).
• Multiple-row subquery: returns many rows; used with IN, ANY, ALL, EXISTS, etc.

Common operators with subqueries

• IN (list of values): col IN (subquery)
• = : col = (scalar subquery)
• ANY / SOME : col > ANY (subquery) — true if col is greater than at least one returned value
• ALL : col > ALL (subquery) — true if col is greater than every returned value
• EXISTS : EXISTS (subquery) — true if subquery returns at least one row (often used with correlated subqueries)

Syntax examples

-- Non-correlated scalar subquery
SELECT student_name
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

-- Correlated subquery: employees with salary > department average
SELECT e.emp_name, e.salary, e.dept_id
FROM employee e
WHERE e.salary > (SELECT AVG(salary) FROM employee WHERE dept_id = e.dept_id);

-- EXISTS example: customers who placed at least one order
SELECT customer_id, name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- IN example: products not sold
SELECT product_name
FROM product
WHERE product_id NOT IN (SELECT product_id FROM sales);

Notes & best practices

• Use scalar subqueries only when you are sure they return one value (or use an aggregate that returns a single value).
• EXISTS with correlated subqueries can be faster than IN for large tables, because EXISTS stops at the first match.
• Avoid SELECT * in subqueries; select only needed columns (often SELECT 1 or a single column).
• Be careful with NULLs—IN with NULLs behaves differently; EXISTS ignores returned column values and tests row existence.

Overview
Set operations in SQL combine the result sets of two or more SELECT queries as if they were mathematical sets. The main operations are UNION, UNION ALL, INTERSECT, and EXCEPT (called MINUS in Oracle). They let you merge, intersect, or subtract rows returned by separate queries.

Requirements

• Each SELECT must return the same number of columns.
• Corresponding columns must have compatible data types (SQL will implicit-cast if possible).
• Column names in the final result are taken from the first SELECT (some DBs allow aliasing the final result).

Behavior of each operation

• UNION: returns unique rows present in either result; duplicates are removed (equivalent to set union).
• UNION ALL: returns all rows from both results, including duplicates (multiset union).
• INTERSECT: returns rows common to both results; duplicates removed (set intersection).
• EXCEPT / MINUS: returns rows in the first result that are not in the second; duplicates removed (set difference).

Notes & edge cases

• ORDER BY applies to the final combined result; in many DBs it must appear once at the end (after the last SELECT and operation).
• NULLs: for set-operation duplicate elimination, NULLs are treated as equal (i.e., two rows that differ only by NULL in the same column are considered duplicates).
• Some DBMS (Postgres) support INTERSECT ALL and EXCEPT ALL for multiset behavior; others do not.
• Performance: UNION/INTERSECT/EXCEPT typically require sorting or hashing to remove duplicates — UNION ALL is faster because it skips deduplication.

Common syntax

SELECT col1, col2, ... FROM tableA
UNION [ALL]
SELECT col1, col2, ... FROM tableB
[ORDER BY col1];

-- INTERSECT
SELECT ... FROM tableA
INTERSECT
SELECT ... FROM tableB;

-- EXCEPT (or MINUS in Oracle)
SELECT ... FROM tableA
EXCEPT
SELECT ... FROM tableB;

When to use

• UNION to merge non-overlapping lists (e.g., two departments' employee lists) while removing duplicates.
• UNION ALL to concatenate results when duplicates are meaningful (e.g., aggregating event logs from two sources).
• INTERSECT to find common entries (e.g., students enrolled in both course A and B).
• EXCEPT to get exclusive items (e.g., customers who purchased in January but not in February).

What is a View (Virtual Table)?

A view is a saved SQL query treated like a table. It does not store rows itself (unless materialized) but provides a virtual representation of data derived from one or more base tables. When you query a view, the DBMS runs the underlying SELECT and returns the result as if it were a table.

Why use Views?

• Security: expose only selected columns/rows to users (hide sensitive data).
• Simplicity: present a complex join/aggregation as a simple table for users.
• Reusability & maintenance: centralize complex queries in one object.
• Logical data independence: change base table structure internally while keeping view interface stable.

Types of Views

• Simple (updatable) view: Derived from a single table, without GROUP BY, DISTINCT, aggregate functions or joins — often updatable.
• Complex (non-updatable) view: Uses joins, aggregates, GROUP BY, DISTINCT — usually not updatable.
• Materialized view (summary table): Stores results physically and must be refreshed; used for performance (note: not every DBMS uses materialized views under the generic name "view").

Updatable Views & Rules

• An INSERT/UPDATE/DELETE on a view affects base tables only when the view is updatable.
• Typical conditions for updatability: the view references a single base table, contains key columns, and does not contain aggregates, DISTINCT, GROUP BY, or set operations.
• WITH CHECK OPTION: ensures modifications through the view remain visible in the view (row must satisfy the view's WHERE clause).

How Views Work (conceptual flow)

1. User issues: SELECT * FROM view_name;
2. DBMS retrieves the stored view definition (the SELECT statement).
3. DBMS substitutes the view SELECT into the user query and executes against base tables.
4. Result set returned to user as if from a table.

Advantages

• Improved security and simpler user interface.
• Encapsulation of complex logic; easier reporting.
• Consistent data presentation across applications.

Limitations

• Performance: complex views executed repeatedly can be slow (materialized views mitigate this).
• Not all views are updatable.
• Dependency: dropping/changing base tables can invalidate views.

Common SQL Statements

-- Create a view
CREATE VIEW view_name AS
  SELECT column_list
  FROM table1 [JOIN table2 ...]
  WHERE conditions;

-- Select from a view
SELECT * FROM view_name;

-- Create an updatable view with CHECK OPTION
CREATE VIEW dept_emps AS
  SELECT emp_id, name, dept_id, salary
  FROM employees
  WHERE dept_id = 10
  WITH READ ONLY; -- or WITH CHECK OPTION

-- Drop a view
DROP VIEW view_name;

Summary

Views are virtual tables — saved queries that present data from base tables in a convenient, secure and reusable way. They improve logical data independence but have rules and performance considerations for updates.

What is an index?
An index is a database structure that improves the speed of data retrieval operations on a table by providing quick access paths to rows. It works like the index at the back of a book: instead of scanning every page (row), the index points you to the exact pages (row locations).

Why use indexes?
Indexes reduce the number of disk reads and CPU operations required to satisfy queries (especially SELECT with WHERE, JOINs and ORDER BY). They are essential for performance when tables grow large.

How indexes are organized
Most DBMS use B-tree (balanced tree) or hash structures for indexes:

• B-tree (or B+ tree): A balanced tree where leaf nodes contain pointers to rows. Good for range queries, ORDER BY and equality searches. Typical lookup cost: O(log n).
• Hash index: Uses a hash table for direct equality lookups. Very fast for exact-match queries (approaching O(1)), but cannot support range queries.

Types of indexes

• Primary key index: Automatically created by most DBMS for primary key columns; enforces uniqueness.
• Unique index: Ensures indexed columns contain unique values.
• Non-unique (regular) index: Speeds lookups but allows duplicates.
• Composite (multi-column) index: Index on two or more columns; useful when queries filter on multiple columns together.
• Clustered vs Non-clustered: Clustered index defines the physical order of rows (one per table). Non-clustered index stores pointers to rows and many can exist.

How a query uses an index
When a query has a WHERE clause on an indexed column, the optimizer can choose the index. The DBMS traverses the index (e.g., B-tree) to find the row location(s) and then fetches the full row from the table if needed. If the index contains all columns needed by the query (covering index), the DBMS can return results directly from the index without reading the table data.

Advantages

• Faster SELECT queries and quicker JOINs.
• Enforced uniqueness when using unique indexes.
• Can speed ORDER BY and GROUP BY when index order matches.

Disadvantages / trade-offs

• Indexes consume disk space and memory.
• Slower INSERT, UPDATE, DELETE (index maintenance overhead).
• Poor choice of indexes can degrade performance (too many or low-selectivity indexes).

When to create an index (guidelines)

• Columns frequently used in WHERE, JOIN, ORDER BY or GROUP BY.
• Columns with high selectivity (many distinct values).
• Avoid indexing small lookup tables where full table scans are cheaper.

SQL syntax (examples)

-- Create a simple index
CREATE INDEX idx_student_name ON Student(name);

-- Create a unique index
CREATE UNIQUE INDEX idx_student_email ON Student(email);

-- Create a composite index
CREATE INDEX idx_product_cat_price ON Product(category_id, price);

-- Drop an index (syntax varies by DBMS)
DROP INDEX idx_student_name; -- MySQL: DROP INDEX idx_student_name ON Student;

Practical tips

• Prefer indexes on columns used in selective WHERE conditions.
• Use composite indexes to match the leading column order of query predicates.
• Monitor index usage and remove unused indexes to save space and reduce write overhead.
• Use EXPLAIN (or EXPLAIN PLAN) to check whether queries use an index.

What is NULL?
NULL represents the absence of a value in a column — not the same as an empty string (''), a space, or zero. It means “unknown” or “not applicable.”

Key differences

• NULL <> '' (empty string) and NULL <> 0 (zero).
• Arithmetic or comparison with NULL yields UNKNOWN (three-valued logic): TRUE, FALSE, UNKNOWN.

Three-valued logic (brief)
When SQL evaluates expressions with NULL, results can be TRUE, FALSE or UNKNOWN. WHERE filters keep only TRUE rows; UNKNOWN behaves like FALSE in WHERE and HAVING clauses.

Testing NULL
Use IS NULL and IS NOT NULL. Do not use = NULL or <> NULL — they return UNKNOWN.

Common NULL-handling functions

• COALESCE(expr1, expr2, ...) — returns the first non-NULL argument.
• NULLIF(expr1, expr2) — returns NULL if expr1 = expr2, otherwise returns expr1.
• Vendor-specific: NVL(expr, replacement) (Oracle), IFNULL(expr, replacement) (MySQL), ISNULL(expr, replacement) (SQL Server).

Conditional logic in SQL
CASE is the standard conditional expression:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE result_default
END

It permits multiple conditions and returns the first matching THEN value. DECODE (Oracle) and IIF (some systems) provide shortcuts.

How NULL interacts with conditional logic

• A CASE WHEN column = value THEN ... will not match when column is NULL. To handle NULL explicitly, use WHEN column IS NULL THEN ....
• COALESCE and NVL let you supply defaults for NULL inside CASE or SELECT projections (useful for display).
• Aggregates: functions like SUM, AVG, COUNT(column) ignore NULLs. Use COUNT(*) to count rows regardless of NULLs.

Examples of usage (short)

-- Find rows with missing phone number
SELECT id, name FROM students WHERE phone IS NULL;

-- Replace NULL with default in output
SELECT name, COALESCE(phone, 'NoPhone') AS phone_display FROM students;

-- Conditional grade with NULL marks
SELECT name,
  CASE
    WHEN marks IS NULL THEN 'Absent'
    WHEN marks >= 90 THEN 'A+'
    WHEN marks >= 75 THEN 'A'
    ELSE 'B or below'
  END AS grade
FROM exam_results;

-- Avoid division by NULL (or NULL result)
SELECT id, total, COALESCE(total,0)/NULLIF(count,0) AS avg_per_item
FROM purchases;

Practical notes

• Use IS NULL in WHERE/HAVING, and COALESCE/NVL when you want default display values.
• Be careful with joins: outer joins produce NULLs for missing matching rows (e.g., customers with no orders).
• Remember aggregates ignore NULLs — that affects averages and counts.

Concise summary: NULL means unknown/absent. Use IS NULL/IS NOT NULL to test. Use COALESCE/NVL/IFNULL to provide defaults. Use CASE for rich conditional logic and explicitly handle NULL when needed.

Overview
In SQL, aliases, expressions and formatting are used to make query results readable, compute values on-the-fly, and present values in a desired visual form. These features are temporary and affect only the result set (they do not rename columns or tables permanently).

Aliases
A column alias gives a temporary name to a column or expression in the output. A table alias gives a short name for a table reference (useful in joins and when the table name is long).

• Syntax (column): SELECT column_expression AS alias or SELECT column_expression alias
• Syntax (table): FROM table_name AS t or FROM table_name t
• Usage notes: Aliases improve readability and are required when the SELECT item is an expression. Aliases generally cannot be used in the WHERE clause of the same SELECT (use a subquery or repeat the expression). Many DBMS allow aliases in ORDER BY.

Expressions
Expressions are computations inside SELECT (or WHERE, HAVING, ORDER BY). They include arithmetic, string operations, date arithmetic, aggregate expressions and conditional expressions.

• Arithmetic: marks1 + marks2, price * quantity, salary * 0.1 AS tax
• String: concatenation — CONCAT(first_name, ' ', last_name) (MySQL) or first_name || ' ' || last_name (Oracle)
• Date: add/subtract intervals — order_date + INTERVAL '7' DAY (some DBs) or DATEADD(day,7,order_date) (SQL Server)
• Conditional: CASE WHEN score >= 90 THEN 'A' WHEN ... END
• Aggregate expressions: sums and averages with aliases: SUM(amount) AS total_sales

Formatting
Formatting changes how values appear — rounding numbers, adding currency symbols, padding text, formatting dates. Different DBMS provide different functions; common ones:

• ROUND(number, decimals) — round numeric values
• CAST(expr AS type) / CONVERT(...) — change data type
• TO_CHAR(date,'DD-MON-YYYY') (Oracle) or DATE_FORMAT(date, '%d-%m-%Y') (MySQL) — format dates
• FORMAT(number, decimals) (MySQL) — locale-aware formatting
• LPAD, RPAD — pad strings; TRIM, UPPER, LOWER — text formatting

Practical rules

• Use aliases to clarify column headings in result sets (e.g., AS Total_Marks).
• If you need to use a computed column in WHERE/GROUP BY, either repeat the expression or wrap the SELECT as a subquery and filter/aggregate outside.
• Be aware of DBMS-specific functions (TO_CHAR vs FORMAT vs DATE_FORMAT).

Example explanation (quick)
To compute and present student percentage with a clean heading:

SELECT roll_no,
       (marks1+marks2+marks3) AS Total_Marks,
       ROUND((marks1+marks2+marks3)/300.0*100,2) AS Percentage
FROM Students;

Overview
Practical query writing and optimization means writing SQL that is correct, readable and performs well on real datasets. Good queries return correct results and use minimal resources (CPU, memory, I/O). Optimization is about reducing rows scanned, avoiding unnecessary work, and letting the database engine use indexes and efficient join methods.

• Write precise SELECTs: select only needed columns (avoid SELECT *) to reduce I/O and network transfer.
• Filter early: apply restrictive WHERE predicates so the engine reduces rows as soon as possible.
• Use appropriate joins: choose INNER/LEFT/RIGHT correctly and put join conditions in ON, not in WHERE for clarity and correctness.
• Prefer set-based operations: avoid row-by-row processing (cursors) in favor of single SQL statements that operate on sets.
• Index wisely: create indexes on columns used in WHERE, JOIN, ORDER BY and GROUP BY, but avoid creating too many indexes (overhead on writes).
• Use EXPLAIN/EXPLAIN ANALYZE: inspect the execution plan to see which indexes are used and how many rows are estimated/scanned.
• Avoid functions on indexed columns: wrapping a column in a function often prevents index usage (e.g., WHERE UPPER(name) = 'X').
• Prefer EXISTS over IN for subqueries: for correlated subqueries, EXISTS is often faster and avoids reading a large list into memory.
• Limit and paginate efficiently: use keyset pagination (seek method) rather than OFFSET for large offsets.
• Use UNION ALL when duplicates are not a concern: avoids the distinct step of UNION.
• Batch writes: insert/update/delete in batches rather than one row at a time.
• Denormalize selectively: for heavy read workloads, denormalization or materialized views can reduce expensive joins.

Practical workflow

1. Start with a correct, readable query.
2. Use EXPLAIN to see the plan and row estimates.
3. Identify the expensive steps (full table scans, large sorts, expensive joins).
4. Try adding an index or rewriting the join/subquery and compare plans.
5. Test on realistic data volumes, measure latency and resource usage.

Common mistakes

• Using SELECT * in production queries.
• Applying functions to indexed columns in WHERE clauses.
• Using OFFSET with large offsets (causes scanning/skipping many rows).
• Creating indexes on low-cardinality columns (e.g., gender with values M/F) which give little benefit.

Tools and commands

• EXPLAIN [ANALYZE] — inspect the query plan and real execution stats.
• ANALYZE / VACUUM / OPTIMIZE — keep statistics and storage healthy so the planner makes good decisions.
• SHOW INDEX / DESCRIBE — view indexes and table schemas.

Example of an optimization cycle (summary)

1) Write query -> 2) EXPLAIN -> 3) Find full scan or expensive join -> 4) Add/adjust index or rewrite query -> 5) Measure again