Module IV: SQL & Indexing | DBMS Learning Book

1. Overview of SQL

📖 What is SQL?

Structured Query Language (SQL) is a standardized programming language used for managing and manipulating relational databases. Originally developed by IBM as SEQUEL (Structured English Query Language) as part of System R project, it was later renamed to SQL.

SQL Standards Evolution

Standard	Year	Key Features
SQL-86	1986	First ANSI standard
SQL-89	1989	Minor revision
SQL-92	1992	Major revision, widely implemented
SQL:1999	1999	Object-relational features, triggers
SQL:2003	2003	XML support, window functions
SQL:2008+	2008+	Enhanced features

Categories of SQL Commands

DDL

Data Definition Language

CREATE, ALTER, DROP, TRUNCATE

DML

Data Manipulation Language

SELECT, INSERT, UPDATE, DELETE

DCL

Data Control Language

GRANT, REVOKE

TCL

Transaction Control Language

COMMIT, ROLLBACK, SAVEPOINT

DQL

Data Query Language

SELECT

Constraints

Data Integrity Rules

PRIMARY KEY, FOREIGN KEY, CHECK

2. Data Definition Language (DDL)

DDL allows specification of information about relations, including schemas, domains, integrity constraints, indices, and physical storage structure.

Domain Types in SQL

Data Type	Description	Example
`CHAR(n)`	Fixed-length character string of length n	`CHAR(10)`
`VARCHAR(n)`	Variable-length character string, max length n	`VARCHAR(50)`
`INT`	Integer (machine-dependent)	`INT`
`SMALLINT`	Small integer	`SMALLINT`
`NUMERIC(p,d)`	Fixed-point number, p digits, d after decimal	`NUMERIC(8,2)`
`REAL`	Floating-point number	`REAL`
`DATE`	Date (year, month, day)	`DATE '2024-01-15'`
`TIME`	Time of day	`TIME '09:30:00'`
`TIMESTAMP`	Date plus time	`TIMESTAMP '2024-01-15 09:30:00'`

CREATE TABLE

Syntax

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

Example: Creating Tables with Constraints

CREATE TABLE department (
    dept_name   VARCHAR(20),
    building    VARCHAR(15),
    budget      NUMERIC(12,2),
    PRIMARY KEY (dept_name)
);

CREATE TABLE instructor (
    ID          CHAR(5),
    name        VARCHAR(20) NOT NULL,
    dept_name   VARCHAR(20),
    salary      NUMERIC(8,2),
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department
);

CREATE TABLE student (
    ID          VARCHAR(5),
    name        VARCHAR(20) NOT NULL,
    dept_name   VARCHAR(20),
    tot_cred    NUMERIC(3,0) DEFAULT 0,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department
);

ALTER TABLE

Adding and Dropping Columns

-- Add a new column
ALTER TABLE instructor ADD phone VARCHAR(15);

-- Drop a column
ALTER TABLE instructor DROP phone;

-- Modify column datatype
ALTER TABLE instructor MODIFY name VARCHAR(30);

DROP and DELETE

DROP TABLE

Deletes the table and its contents permanently.

DROP TABLE student;

DELETE FROM

Deletes all contents but retains table structure.

DELETE FROM student;

⚠️ Important Difference

DROP TABLE removes the entire table (structure + data), while DELETE FROM only removes data, keeping the table structure intact. TRUNCATE TABLE is faster than DELETE for removing all rows.

3. Data Manipulation Language (DML)

DML provides the ability to query information and insert, delete, and update tuples in the database.

Basic Query Structure

SELECT Statement Syntax

SELECT [DISTINCT] column1, column2, ...
FROM table1, table2, ...
[WHERE condition]
[GROUP BY column_list]
[HAVING group_condition]
[ORDER BY column_list [ASC|DESC]];

SELECT Clause

Basic SELECT Examples

-- Select all columns
SELECT * FROM instructor;

-- Select specific columns
SELECT name, salary FROM instructor;

-- Remove duplicates
SELECT DISTINCT dept_name FROM instructor;

-- Arithmetic expressions
SELECT ID, name, salary/12 AS monthly_salary
FROM instructor;

-- All rows (default)
SELECT ALL dept_name FROM instructor;

📝 Note: SQL is Case-Insensitive

SQL names are case insensitive: Name ≡ NAME ≡ name. Keywords can be written in any case.

INSERT Statement

Inserting Data

-- Insert with all columns
INSERT INTO instructor
VALUES ('10211', 'Smith', 'Biology', 66000);

-- Insert with specified columns
INSERT INTO instructor (ID, name, dept_name, salary)
VALUES ('10211', 'Smith', 'Biology', 66000);

-- Insert with NULL value
INSERT INTO student
VALUES ('3003', 'Green', 'Finance', NULL);

-- Insert from another query
INSERT INTO student
SELECT ID, name, dept_name, 0
FROM instructor;

UPDATE Statement

Updating Data

-- Simple update
UPDATE instructor
SET salary = salary * 1.05
WHERE salary < 100000;

-- Update with CASE statement
UPDATE instructor
SET salary = CASE
    WHEN salary <= 100000 THEN salary * 1.05
    ELSE salary * 1.03
END;

-- Update with subquery
UPDATE student S
SET tot_cred = (
    SELECT SUM(credits)
    FROM takes NATURAL JOIN course
    WHERE S.ID = takes.ID
);

DELETE Statement

Deleting Data

-- Delete all instructors
DELETE FROM instructor;

-- Delete with condition
DELETE FROM instructor
WHERE dept_name = 'Finance';

-- Delete with subquery
DELETE FROM instructor
WHERE dept_name IN (
    SELECT dept_name
    FROM department
    WHERE building = 'Watson'
);

-- Delete below average salary
DELETE FROM instructor
WHERE salary < (SELECT AVG(salary) FROM instructor);

4. SQL Operators & Clauses

WHERE Clause

The WHERE clause specifies conditions that the result must satisfy, corresponding to the selection predicate in relational algebra.

WHERE Clause Examples

-- Comparison operators
SELECT name FROM instructor
WHERE dept_name = 'Comp. Sci.' AND salary > 80000;

-- BETWEEN operator
SELECT name FROM instructor
WHERE salary BETWEEN 90000 AND 100000;

-- IN operator
SELECT name FROM instructor
WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Biology');

String Operations (LIKE)

Pattern Matching with LIKE

SQL uses two special characters for pattern matching:

% (percent) - Matches any substring (zero or more characters)
_ (underscore) - Matches any single character

LIKE Pattern Examples

-- Names starting with 'S'
SELECT name FROM instructor
WHERE name LIKE 'S%';

-- Names containing 'dar'
SELECT name FROM instructor
WHERE name LIKE '%dar%';

-- Names with exactly 5 characters
SELECT name FROM instructor
WHERE name LIKE '_____';

-- Names with at least 3 characters
SELECT name FROM instructor
WHERE name LIKE '___%';

-- Escape special characters
SELECT * FROM courses
WHERE title LIKE '100\%' ESCAPE '\';

ORDER BY Clause

Ordering Results

-- Ascending order (default)
SELECT DISTINCT name FROM instructor
ORDER BY name;

-- Descending order
SELECT name, salary FROM instructor
ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM instructor
ORDER BY dept_name ASC, salary DESC;

Rename Operation (AS)

Using Aliases

-- Column alias
SELECT ID, name, salary/12 AS monthly_salary
FROM instructor;

-- Table alias for self-join
SELECT DISTINCT T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > S.salary
AND S.dept_name = 'Comp. Sci.';

5. Aggregate Functions

Aggregate functions operate on a multiset of values and return a single value.

Σ

SUM

Sum of values

μ

AVG

Average value

#

COUNT

Number of values

↑

MAX

Maximum value

↓

MIN

Minimum value

Aggregate Function Examples

-- Average salary in Computer Science
SELECT AVG(salary) FROM instructor
WHERE dept_name = 'Comp. Sci.';

-- Count distinct instructors teaching in Spring 2010
SELECT COUNT(DISTINCT ID) FROM teaches
WHERE semester = 'Spring' AND year = 2010;

-- Total number of tuples
SELECT COUNT(*) FROM course;

-- Maximum salary
SELECT MAX(salary) FROM instructor;

-- Find employee with maximum salary
SELECT name FROM instructor
WHERE salary = (SELECT MAX(salary) FROM instructor);

-- Find 2nd highest salary
SELECT MAX(salary) FROM instructor
WHERE salary < (SELECT MAX(salary) FROM instructor);

⚠️ Important Notes on Aggregates

All aggregate functions except COUNT(*) ignore NULL values
If collection has only NULL values: COUNT returns 0, others return NULL
Attributes in SELECT outside aggregates must appear in GROUP BY

6. Null Values

📖 What is NULL?

NULL signifies an unknown value or that a value does not exist. It is not the same as zero or an empty string.

Arithmetic with NULL

Any arithmetic expression involving NULL returns NULL.

5 + NULL → NULL

Three-Valued Logic

Comparisons with NULL return unknown, creating a three-valued logic system.

Expression	Result
`5 < NULL`	unknown
`NULL = NULL`	unknown
`NULL <> NULL`	unknown

Logic Operations with Unknown

OR Operations

(unknown OR true) = true
(unknown OR false) = unknown
(unknown OR unknown) = unknown

AND Operations

(true AND unknown) = unknown
(false AND unknown) = false
(unknown AND unknown) = unknown

NOT Operation

(NOT unknown) = unknown

Testing for NULL

-- Find instructors with NULL salary
SELECT name FROM instructor
WHERE salary IS NULL;

-- Find instructors with non-NULL salary
SELECT name FROM instructor
WHERE salary IS NOT NULL;

7. Set Operations

SQL provides set operations that operate on relations: UNION, INTERSECT, and EXCEPT.

UNION

Returns all tuples in either or both relations

Eliminates duplicates

INTERSECT

Returns tuples present in both relations

Eliminates duplicates

EXCEPT

Returns tuples in first but not in second relation

Eliminates duplicates

Set Operation Examples

-- Courses in Fall 2009 OR Spring 2010
(SELECT course_id FROM section
 WHERE semester = 'Fall' AND year = 2009)
UNION
(SELECT course_id FROM section
 WHERE semester = 'Spring' AND year = 2010);

-- Courses in Fall 2009 AND Spring 2010
(SELECT course_id FROM section
 WHERE semester = 'Fall' AND year = 2009)
INTERSECT
(SELECT course_id FROM section
 WHERE semester = 'Spring' AND year = 2010);

-- Courses in Fall 2009 but NOT in Spring 2010
(SELECT course_id FROM section
 WHERE semester = 'Fall' AND year = 2009)
EXCEPT
(SELECT course_id FROM section
 WHERE semester = 'Spring' AND year = 2010);

Retaining Duplicates

To retain duplicates, use UNION ALL, INTERSECT ALL, and EXCEPT ALL.

📝 Duplicate Count Rules

If tuple occurs m times in r and n times in s:

r UNION ALL s: m + n times
r INTERSECT ALL s: min(m, n) times
r EXCEPT ALL s: max(0, m - n) times

8. Joins

Join operations combine data from two or more tables based on related columns.

Types of Joins

Visual Representation of Joins

Table A

Table B

Join Type	Description	Returns
INNER JOIN	Matches rows in both tables	Only matching rows
LEFT OUTER JOIN	All rows from left + matching from right	All left, NULL for non-matching right
RIGHT OUTER JOIN	All rows from right + matching from left	All right, NULL for non-matching left
FULL OUTER JOIN	All rows from both tables	All rows, NULL where no match
NATURAL JOIN	Matches on common columns automatically	Matching rows, removes duplicate columns
CROSS JOIN	Cartesian product	Every combination

Join Examples

-- Cartesian Product (Cross Join)
SELECT * FROM instructor, teaches;

-- Inner Join with WHERE
SELECT name, course_id
FROM instructor, teaches
WHERE instructor.ID = teaches.ID;

-- Natural Join
SELECT * FROM instructor NATURAL JOIN teaches;

-- Left Outer Join
SELECT * FROM course
LEFT OUTER JOIN prereq ON course.course_id = prereq.course_id;

-- Right Outer Join
SELECT * FROM course
RIGHT OUTER JOIN prereq ON course.course_id = prereq.course_id;

-- Full Outer Join
SELECT * FROM course
FULL OUTER JOIN prereq USING (course_id);

-- Join with explicit ON clause
SELECT section.course_id, semester, year, title
FROM section INNER JOIN course
ON section.course_id = course.course_id
WHERE dept_name = 'Comp. Sci.';

⚠️ Natural Join Warning

Be careful with Natural Join - it matches on ALL common attribute names, which can lead to incorrect results if unrelated attributes happen to share the same name.

9. Nested Subqueries

A subquery is a SELECT statement nested within another query. Subqueries can appear in SELECT, FROM, or WHERE clauses.

Subqueries with IN

IN and NOT IN Examples

-- Courses offered in Fall 2009 and Spring 2010
SELECT DISTINCT course_id FROM section
WHERE semester = 'Fall' AND year = 2009
AND course_id IN (
    SELECT course_id FROM section
    WHERE semester = 'Spring' AND year = 2010
);

-- Courses in Fall 2009 but not Spring 2010
SELECT DISTINCT course_id FROM section
WHERE semester = 'Fall' AND year = 2009
AND course_id NOT IN (
    SELECT course_id FROM section
    WHERE semester = 'Spring' AND year = 2010
);

Comparison Operators: SOME and ALL

SOME (or ANY)

Returns TRUE if comparison is true for at least one value

= SOME ≡ IN
≠ SOME ≢ NOT IN

ALL

Returns TRUE if comparison is true for all values

≠ ALL ≡ NOT IN
= ALL ≢ IN

SOME and ALL Examples

-- Salary greater than SOME instructor in Biology
SELECT name FROM instructor
WHERE salary > SOME (
    SELECT salary FROM instructor
    WHERE dept_name = 'Biology'
);

-- Salary greater than ALL instructors in Biology
SELECT name FROM instructor
WHERE salary > ALL (
    SELECT salary FROM instructor
    WHERE dept_name = 'Biology'
);

EXISTS and NOT EXISTS

EXISTS Examples

-- Correlated subquery with EXISTS
SELECT course_id FROM section AS S
WHERE semester = 'Fall' AND year = 2009
AND EXISTS (
    SELECT * FROM section AS T
    WHERE semester = 'Spring' AND year = 2010
    AND S.course_id = T.course_id
);

-- Students who have taken all Biology courses
SELECT DISTINCT S.ID, S.name FROM student AS S
WHERE NOT EXISTS (
    (SELECT course_id FROM course
     WHERE dept_name = 'Biology')
    EXCEPT
    (SELECT T.course_id FROM takes AS T
     WHERE S.ID = T.ID)
);

Scalar Subqueries

Scalar Subquery Examples

-- Subquery returns single value
SELECT dept_name,
    (SELECT COUNT(*) FROM instructor
     WHERE department.dept_name = instructor.dept_name)
    AS num_instructors
FROM department;

WITH Clause (Common Table Expressions)

WITH Clause Example

-- Define temporary views
WITH dept_total (dept_name, value) AS
    (SELECT dept_name, SUM(salary)
     FROM instructor
     GROUP BY dept_name),
dept_total_avg(value) AS
    (SELECT AVG(value) FROM dept_total)
SELECT dept_name
FROM dept_total, dept_total_avg
WHERE dept_total.value >= dept_total_avg.value;

10. GROUP BY & HAVING

GROUP BY Clause

Groups rows that have the same values in specified columns. Aggregate functions can then be applied to each group.

GROUP BY Examples

-- Average salary by department
SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name;

-- Count of courses per department
SELECT dept_name, COUNT(*) AS num_courses
FROM course
GROUP BY dept_name;

⚠️ Important Rule

Attributes in SELECT clause outside of aggregate functions MUST appear in GROUP BY list.

                        -- ERRONEOUS: ID not in GROUP BY
SELECT dept_name, ID, AVG(salary)
FROM instructor
GROUP BY dept_name;  -- Error!
                    

HAVING Clause

HAVING filters groups after GROUP BY, while WHERE filters rows before grouping.

HAVING Examples

-- Departments with average salary > 42000
SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 42000;

-- Departments with more than 5 instructors
SELECT dept_name, COUNT(*) AS cnt
FROM instructor
GROUP BY dept_name
HAVING COUNT(*) > 5;

Query Execution Order

FROM

→

WHERE

→

GROUP BY

→

HAVING

→

SELECT

→

ORDER BY

11. Integrity Constraints

📖 Integrity Constraints

Integrity constraints guard against accidental damage to the database by ensuring that authorized changes do not result in a loss of data consistency.

Types of Constraints

Single Relation Constraints

NOT NULL PRIMARY KEY UNIQUE CHECK

Multi-Relation Constraints

FOREIGN KEY REFERENTIAL

Constraint Definitions

Constraint	Description	Example
NOT NULL	Value cannot be NULL	`name VARCHAR(20) NOT NULL`
PRIMARY KEY	Unique identifier, cannot be NULL	`PRIMARY KEY (ID)`
UNIQUE	All values must be distinct (allows NULL)	`UNIQUE (email)`
FOREIGN KEY	References primary key of another table	`FOREIGN KEY (dept_name) REFERENCES department`
CHECK	Validates against a condition	`CHECK (salary > 0)`
DEFAULT	Default value if none specified	`tot_cred NUMERIC(3,0) DEFAULT 0`

Constraint Examples

CREATE TABLE section (
    course_id   VARCHAR(8),
    sec_id      VARCHAR(8),
    semester    VARCHAR(6),
    year        NUMERIC(4,0),
    building    VARCHAR(15),
    room_number VARCHAR(7),
    time_slot_id VARCHAR(4),
    PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (course_id) REFERENCES course,
    CHECK (semester IN ('Fall', 'Winter', 'Spring', 'Summer'))
);

Referential Integrity & Cascading Actions

Cascading Actions

CREATE TABLE course (
    course_id   CHAR(5) PRIMARY KEY,
    title       VARCHAR(20),
    dept_name   VARCHAR(20),
    FOREIGN KEY (dept_name) REFERENCES department
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Alternative actions:
-- ON DELETE SET NULL
-- ON DELETE SET DEFAULT
-- ON DELETE RESTRICT (default)

12. Views

📖 What is a View?

A view is a virtual relation based on the result of a SQL query. Views provide a mechanism to hide certain data from users and simplify complex queries.

View Syntax

CREATE VIEW view_name AS
    <query expression>;

View Examples

-- View without salary information
CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor;

-- Using the view
SELECT name FROM faculty
WHERE dept_name = 'Biology';

-- View with aggregation
CREATE VIEW departments_total_salary(dept_name, total_salary) AS
SELECT dept_name, SUM(salary)
FROM instructor
GROUP BY dept_name;

-- View based on another view
CREATE VIEW physics_fall_2009 AS
SELECT course.course_id, sec_id, building, room_number
FROM course, section
WHERE course.course_id = section.course_id
AND course.dept_name = 'Physics'
AND section.semester = 'Fall'
AND section.year = 2009;

Updatable Views

📝 View Update Conditions

Most SQL implementations allow updates only on simple views that:

Have only one database relation in FROM clause
SELECT contains only attribute names (no expressions, aggregates, DISTINCT)
Any attribute not in SELECT can be set to NULL

Materialized Views

A materialized view creates a physical table containing all tuples in the query result. It needs to be maintained when underlying relations are updated.

13. Security & Data Control Language (DCL)

Forms of Authorization

Data Access Authorization

Read - Read but not modify
Insert - Insert new data
Update - Modify existing data
Delete - Delete data

Schema Authorization

Index - Create/delete indices
Resources - Create new relations
Alteration - Add/delete attributes
Drop - Delete relations

GRANT Statement

Syntax

GRANT <privilege list>
ON <relation or view>
TO <user list>;

GRANT Examples

-- Grant SELECT to specific users
GRANT SELECT ON instructor TO U1, U2, U3;

-- Grant all privileges
GRANT ALL PRIVILEGES ON department TO admin;

-- Grant to all users
GRANT SELECT ON course TO PUBLIC;

-- Grant with ability to pass on
GRANT SELECT ON department TO Amit
WITH GRANT OPTION;

REVOKE Statement

REVOKE Examples

-- Revoke SELECT privilege
REVOKE SELECT ON branch FROM U1, U2, U3;

-- Revoke all privileges
REVOKE ALL ON instructor FROM public;

-- Cascade revoke (removes dependent privileges)
REVOKE SELECT ON department FROM Amit, Satoshi
CASCADE;

-- Restrict (fails if there are dependencies)
REVOKE SELECT ON department FROM Amit
RESTRICT;

Roles

Role Management

-- Create a role
CREATE ROLE instructor;

-- Grant privileges to role
GRANT SELECT ON takes TO instructor;

-- Grant role to user
GRANT instructor TO Amit;

-- Role hierarchy
CREATE ROLE teaching_assistant;
GRANT teaching_assistant TO instructor;

-- Chain of roles
CREATE ROLE dean;
GRANT instructor TO dean;
GRANT dean TO Satoshi;

14. Triggers

📖 What is a Trigger?

A trigger is a stored procedure that automatically executes in response to certain events on a particular table (INSERT, UPDATE, DELETE).

Trigger Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
BEGIN
    -- trigger body
END;

Trigger Examples

-- Trigger to log changes
CREATE TRIGGER salary_audit
AFTER UPDATE ON instructor
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (emp_id, old_salary, new_salary, change_date)
    VALUES (OLD.ID, OLD.salary, NEW.salary, CURRENT_DATE);
END;

-- Trigger to maintain total credits
CREATE TRIGGER update_credits
AFTER INSERT ON takes
FOR EACH ROW
BEGIN
    UPDATE student
    SET tot_cred = tot_cred + (
        SELECT credits FROM course
        WHERE course_id = NEW.course_id
    )
    WHERE ID = NEW.ID;
END;

Row-Level Triggers

Execute once for each row affected by the operation. Uses FOR EACH ROW.

Statement-Level Triggers

Execute once per SQL statement, regardless of rows affected. Uses FOR EACH STATEMENT.

15. Functions & Procedures

Functions

Functions return a single value and can be used in SQL expressions.

Function Example

CREATE FUNCTION dept_count(dept_name VARCHAR(20))
RETURNS INTEGER
BEGIN
    DECLARE d_count INTEGER;
    SELECT COUNT(*) INTO d_count
    FROM instructor
    WHERE instructor.dept_name = dept_name;
    RETURN d_count;
END;

-- Using the function
SELECT dept_name, dept_count(dept_name)
FROM department;

Stored Procedures

Procedures are named blocks of SQL statements that can be called with parameters.

Procedure Example

CREATE PROCEDURE give_raise(
    IN emp_id CHAR(5),
    IN percent NUMERIC(5,2)
)
BEGIN
    UPDATE instructor
    SET salary = salary * (1 + percent/100)
    WHERE ID = emp_id;
END;

-- Calling the procedure
CALL give_raise('10101', 10);

Feature	Function	Procedure
Return Value	Must return a value	May or may not return
Usage	Can be used in SELECT	Called with CALL statement
Parameters	Only IN parameters	IN, OUT, INOUT parameters
DML Operations	Limited	Full support

16. Cursors

📖 What is a Cursor?

A cursor is a database object that allows row-by-row processing of query results. Cursors are used when you need to process each row individually rather than as a set.

Cursor Operations

1

DECLARE

Define the cursor and its associated SELECT query

2

OPEN

Execute the query and populate the result set

3

FETCH

Retrieve data row by row from the result set

4

CLOSE

Release the cursor and free resources

Cursor Example

DECLARE
    v_name VARCHAR(20);
    v_salary NUMERIC(8,2);
    done INT DEFAULT FALSE;

    -- Declare cursor
    CURSOR instructor_cursor IS
        SELECT name, salary FROM instructor
        WHERE dept_name = 'Comp. Sci.';

    -- Handler for end of data
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = TRUE;

BEGIN
    -- Open cursor
    OPEN instructor_cursor;

    -- Loop through results
    read_loop: LOOP
        FETCH instructor_cursor INTO v_name, v_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Process each row
        -- ... your logic here ...
    END LOOP;

    -- Close cursor
    CLOSE instructor_cursor;
END;

17. Indexing

📖 What is an Index?

An index is a data structure that improves the speed of data retrieval operations on a database table. It works like a book's index, allowing quick lookup of data without scanning the entire table.

Why Use Indexes?

✓ Advantages

Faster data retrieval
Improved query performance
Efficient sorting and ordering
Enforces uniqueness

✗ Disadvantages

Additional storage space
Slower INSERT/UPDATE/DELETE
Maintenance overhead
May not be used by optimizer

Types of Indexes

Index Type	Description	Use Case
Primary Index	Index on primary key, ordered file	Default for primary key access
Clustered Index	Determines physical order of data	Range queries, sorted data
Non-Clustered	Separate from data, contains pointers	Secondary lookups
Dense Index	Entry for every search key value	Faster lookup, more space
Sparse Index	Entry for some search key values	Less space, slower lookup
B+ Tree Index	Balanced tree structure	Most common, range queries
Hash Index	Hash function based	Equality comparisons

Index Definition in SQL

Creating Indexes

-- Create a simple index
CREATE INDEX studentID_index ON student(ID);

-- Create unique index
CREATE UNIQUE INDEX email_idx ON instructor(email);

-- Create composite index
CREATE INDEX name_dept_idx ON instructor(dept_name, name);

-- Drop an index
DROP INDEX studentID_index;

-- Query using index (optimizer decides)
SELECT * FROM student WHERE ID = '12345';

B+ Tree Index Structure

50

↙ ↘

20 35

65 80

10, 15

20, 25, 30

35, 40, 45

50, 55, 60

65, 70, 75

80, 85, 90

Leaf nodes contain actual data/pointers and are linked for range queries

📝 When to Create Indexes

Columns frequently used in WHERE clauses
Columns used in JOIN conditions
Columns used in ORDER BY
Avoid on small tables or frequently updated columns

18. Practice Questions

Write SQL queries for the following on Employee(emp_id, ename, city, salary, dept_id) and Department(dept_id, dname, location):

▼

a) Find employees whose name starts with 'S'

SELECT * FROM Employee WHERE ename LIKE 'S%';

b) Find employees with salary between 50000 and 100000

SELECT * FROM Employee WHERE salary BETWEEN 50000 AND 100000;

c) Find the 2nd highest salary

                                SELECT MAX(salary) FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
                            

d) Give 10% raise to all employees

UPDATE Employee SET salary = salary * 1.10;

e) Find employees earning more than average salary of their department

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

Explain all types of integrity constraints with examples.

▼

1. Domain Integrity: Ensures values in a column are of correct type and within valid range.

salary NUMERIC(8,2) CHECK (salary > 0)

2. Entity Integrity: Every table must have a primary key that is unique and not null.

PRIMARY KEY (ID)

3. Referential Integrity: Foreign key must match a primary key in referenced table or be null.

FOREIGN KEY (dept_name) REFERENCES department

4. Key Constraints:

UNIQUE: All values must be distinct
NOT NULL: Must have a value

Define View. Write syntax for creating a view with example.

▼

Definition: A view is a virtual relation based on the result of a SQL query. It provides a mechanism to hide certain data from users and simplify complex queries.

Syntax:

                                CREATE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;
                            

Example 1: View to show instructors without salary

                                CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor;
                            

Example 2: View with aggregation

                                CREATE VIEW dept_salary_totals AS
SELECT dept_name, SUM(salary) as total_salary
FROM instructor
GROUP BY dept_name;
                            

Define Triggers. Write syntax and give an example.

▼

Definition: A trigger is a stored procedure that automatically executes in response to certain events on a particular table (INSERT, UPDATE, DELETE).

Syntax:

                                CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- trigger statements
END;
                            

Example: Audit trigger for salary changes

                                CREATE TRIGGER salary_audit
AFTER UPDATE ON instructor
FOR EACH ROW
BEGIN
    INSERT INTO salary_log (emp_id, old_sal, new_sal, change_date)
    VALUES (OLD.ID, OLD.salary, NEW.salary, CURRENT_DATE);
END;
                            

Key Concepts:

OLD - refers to the row before modification
NEW - refers to the row after modification
Row-level triggers execute for each affected row
Statement-level triggers execute once per statement

Explain types of joins with suitable examples.

▼

1. INNER JOIN: Returns only matching rows from both tables.

                                SELECT * FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id;
                            

2. LEFT OUTER JOIN: Returns all rows from left table and matching rows from right.

                                SELECT * FROM Employee e
LEFT OUTER JOIN Department d ON e.dept_id = d.dept_id;
                            

3. RIGHT OUTER JOIN: Returns all rows from right table and matching rows from left.

                                SELECT * FROM Employee e
RIGHT OUTER JOIN Department d ON e.dept_id = d.dept_id;
                            

4. FULL OUTER JOIN: Returns all rows from both tables.

                                SELECT * FROM Employee e
FULL OUTER JOIN Department d ON e.dept_id = d.dept_id;
                            

5. NATURAL JOIN: Automatically joins on common columns.

SELECT * FROM Employee NATURAL JOIN Department;

Explain Grant and Revoke commands with examples.

▼

GRANT: Used to give privileges to users or roles.

Syntax:

GRANT privilege_list ON object TO user_list;

Examples:

                                -- Grant SELECT to specific users
GRANT SELECT ON instructor TO U1, U2;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON student TO manager;

-- Grant with ability to pass on
GRANT SELECT ON course TO admin WITH GRANT OPTION;

-- Grant to all users
GRANT SELECT ON department TO PUBLIC;
                            

REVOKE: Used to remove privileges from users or roles.

Syntax:

REVOKE privilege_list ON object FROM user_list;

Examples:

                                -- Revoke SELECT
REVOKE SELECT ON instructor FROM U1;

-- Revoke all privileges
REVOKE ALL ON student FROM public;

-- Cascade revoke (removes dependent privileges)
REVOKE SELECT ON course FROM manager CASCADE;
                            

Structured Query Language (SQL) & Indexing

1. Overview of SQL

📖 What is SQL?

SQL Standards Evolution

Categories of SQL Commands

DDL

DML

DCL

TCL

DQL

Constraints

2. Data Definition Language (DDL)

Domain Types in SQL

CREATE TABLE

Syntax

ALTER TABLE

DROP and DELETE

DROP TABLE

DELETE FROM

⚠️ Important Difference

3. Data Manipulation Language (DML)

Basic Query Structure

SELECT Statement Syntax

SELECT Clause

📝 Note: SQL is Case-Insensitive

INSERT Statement

UPDATE Statement

DELETE Statement

4. SQL Operators & Clauses

WHERE Clause

String Operations (LIKE)

Pattern Matching with LIKE

ORDER BY Clause

Rename Operation (AS)

5. Aggregate Functions

SUM

AVG

COUNT

MAX

MIN

⚠️ Important Notes on Aggregates

6. Null Values

📖 What is NULL?

Arithmetic with NULL

Three-Valued Logic

Logic Operations with Unknown

OR Operations

AND Operations

NOT Operation

7. Set Operations

UNION

INTERSECT

EXCEPT

Retaining Duplicates

📝 Duplicate Count Rules

8. Joins

Types of Joins

Visual Representation of Joins

⚠️ Natural Join Warning

9. Nested Subqueries

Subqueries with IN

Comparison Operators: SOME and ALL

SOME (or ANY)

ALL

EXISTS and NOT EXISTS

Scalar Subqueries

WITH Clause (Common Table Expressions)

10. GROUP BY & HAVING

GROUP BY Clause

⚠️ Important Rule

HAVING Clause

Query Execution Order

11. Integrity Constraints

📖 Integrity Constraints

Types of Constraints

Single Relation Constraints

Multi-Relation Constraints

Constraint Definitions

Referential Integrity & Cascading Actions

12. Views