MITS Academy — SQL & Database Management

MITS ACADEMY
SQL & Database Management

MySQL — From Basics to Advanced

[Link]

Page 1 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

Chapter 1: Introduction to Databases

1.1 What is Data, Database, and DBMS?
Before we dive into SQL, we must understand why databases were invented in the first place. In
the early days of computing, programs stored data in plain files — text files, binary files, or flat
CSV files. Every program managed its own data independently. This worked fine for small,
single-user applications, but as organizations grew and multiple programs began sharing the
same data, serious problems emerged. Understanding these problems is the key to
appreciating why a Database Management System (DBMS) is not just a convenience but a
necessity.
Data is any raw fact — a student's name, a bank balance, a date of birth. When data is
organized, stored, and made retrievable, it becomes information. A database is a structured
collection of related data. It is not just a pile of files — it is organized data with relationships
between pieces, enforced rules, and controlled access. A Database Management System
(DBMS) is the software layer that sits between your application and the physical data storage,
managing all interactions with the database.
A Relational Database Management System (RDBMS) is a specific type of DBMS that
organizes data into tables (relations) with rows (tuples) and columns (attributes). The "relational"
model, proposed by E.F. Codd in 1970, is based on mathematical set theory and relational
algebra. Every table represents one entity type, and relationships between entities are
expressed through shared key values. MySQL, PostgreSQL, Oracle, and SQLite are all RDBMS
products.
The file system approach to data management causes five classic problems. Data redundancy
means the same data is stored in multiple files — a student's address might appear in the
admissions file, the fee file, and the hostel file. Data inconsistency occurs when one copy is
updated but others are not — the fee file shows an old address. There is no concurrent access
control, so two programs writing simultaneously corrupt each other's data. There is no security
model — anyone who can access the directory can read or delete files. Finally, there is no way
to query data flexibly — you must write custom code to answer every question.

1.2 Types of DBMS

Database systems have evolved through several models. The Hierarchical model (IBM IMS,
1960s) organizes data in a tree structure — each parent can have many children but each child
has only one parent. It is fast for known access paths but rigid. The Network model extends this
to allow multiple parent-child relationships, forming a graph rather than a tree. Both models
require programmers to know the physical data structure to write queries — a serious limitation.
The Relational model (1970s onward) solved this with data independence — you describe
WHAT you want, not HOW to find it. SQL is a declarative language: "SELECT students where
marks > 80" — the DBMS figures out the most efficient execution plan. NoSQL databases
(MongoDB, Cassandra, Redis) emerged in the 2000s for specific use cases: document storage,
key-value caches, and wide-column stores for massive scale. They trade ACID guarantees for
horizontal scalability. For most business applications, RDBMS remains the right choice.

Page 2 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

1.3 MySQL vs PostgreSQL vs Oracle vs SQLite

Choosing the right RDBMS depends on your use case. Here is a practical comparison of the
four most common systems:
• MySQL: Open source, owned by Oracle. Extremely popular for web applications (LAMP
stack). Fast reads, excellent community support. Default storage engine InnoDB
supports full ACID transactions. Best for web apps, e-commerce, SaaS products.
• PostgreSQL: Open source, community-driven. More feature-rich than MySQL — supports
advanced data types (arrays, JSON, hstore), full-text search, window functions, and
better standards compliance. Best for complex queries, data warehousing, applications
needing advanced features.
• Oracle Database: Commercial, enterprise-grade. Extremely powerful with features like
Real Application Clusters (RAC) for high availability. Very expensive. Dominant in
banking, telecom, and large enterprises where cost is secondary to reliability.
• SQLite: Serverless, file-based database. The entire database is a single file. No
installation required. Used in mobile apps (Android, iOS), browsers, and embedded
systems. Not suitable for concurrent multi-user applications but perfect for local storage
and prototyping.

1.4 MySQL Installation and Key Concepts

To install MySQL, download MySQL Community Server from [Link]. The installer
includes MySQL Server (the database engine), MySQL Workbench (a GUI client for designing
and querying), and MySQL Shell (a command-line tool). After installation, connect using the root
user with the password you set during setup. MySQL Workbench allows you to visually design
ER diagrams, run SQL queries, and manage users.
Key terminology you must know: A table is a two-dimensional structure with rows and columns
representing one type of entity (e.g., students). A row (also called a record or tuple) represents
one instance of the entity — one student. A column (also called a field or attribute) represents
one property of the entity — student_name. A schema is a named container for tables, views,
and other database objects — think of it as a database. A primary key is a column (or
combination of columns) that uniquely identifies each row — no duplicates, no NULLs. A foreign
key is a column that references the primary key of another table, establishing a relationship
between tables.

1.5 Code Example: Setting Up the University Database

-- Connect to MySQL and verify version
SELECT VERSION(); -- Check MySQL version installed

-- Create a new database (schema) for our university project

CREATE DATABASE IF NOT EXISTS mitsacademy
CHARACTER SET utf8mb4 -- Support full Unicode including
emojis
COLLATE utf8mb4_unicode_ci; -- Case-insensitive Unicode collation

-- Switch to our new database

USE mitsacademy;

-- Verify we are in the right database

SELECT DATABASE(); -- Should return: mitsacademy

-- Preview the structure we will build (these will be created in Chapter 2)

-- departments → dept_id, dept_name, hod_name

Page 3 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- students → student_id, name, dept_id (FK), email, marks

-- faculty → faculty_id, name, dept_id (FK), designation
-- courses → course_id, course_name, dept_id (FK), credits
-- enrollments → enrollment_id, student_id (FK), course_id (FK), grade

-- Show all databases on this server

SHOW DATABASES;

-- Show all tables in current database (empty for now)

SHOW TABLES;

1.6 Common Mistakes in Chapter 1

• Confusing schema and database: In MySQL, CREATE SCHEMA and CREATE
DATABASE are synonyms. In other RDBMS like PostgreSQL, a schema is a
namespace within a database — they are not the same thing.
• Forgetting USE database_name: After connecting to MySQL, you must run USE
mitsacademy; before any table operations, or prefix every table with the database name:
[Link].
• Using wrong character set: Using latin1 instead of utf8mb4 means you cannot store Hindi,
Chinese, or emoji characters. Always specify utf8mb4 when creating databases.

1.7 Practice Exercises

• Install MySQL Community Server and Workbench. Connect as root and verify the version.
• Create a database called college_db with utf8mb4 character set.
• Research: What is the difference between DDL, DML, DQL, DCL, and TCL in SQL?
• List five real-world applications that use MySQL. What kind of data do they store?
• What problems would arise if a school stored all student data in a single Excel sheet
shared on a network drive?

Chapter 2: DDL — Data Definition Language

2.1 Understanding DDL
Data Definition Language (DDL) is the subset of SQL used to define, modify, and delete the
structure of database objects — tables, indexes, views, and schemas. DDL statements do not
manipulate data; they define the containers that hold data. The critical characteristic of DDL is
that it is auto-committed in MySQL — there is no ROLLBACK for DDL. If you DROP a table, it is
gone immediately without needing a COMMIT. This is why DDL changes require careful
planning.
Choosing the right data type for each column is one of the most important database design
decisions. The wrong data type wastes storage, prevents valid values, or allows invalid ones.
Using VARCHAR(255) for a column that stores only Y or N wastes 254 bytes per row. Using
INT for a primary key that will eventually exceed 2 billion rows causes a catastrophic failure.
Choosing data types requires you to think about the real-world values each column will hold:
What is the maximum value? Can it be NULL? Is it fixed or variable length?

Page 4 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

2.2 MySQL Data Types

MySQL provides a rich set of data types. Integer types: TINYINT (1 byte, -128 to 127 or 0 to 255
unsigned), SMALLINT (2 bytes), MEDIUMINT (3 bytes), INT (4 bytes, up to ~2.1 billion), BIGINT
(8 bytes, up to ~9.2 quintillion). For monetary values, never use FLOAT or DOUBLE — they are
approximate (binary floating point). Use DECIMAL(precision, scale) for exact decimal arithmetic.
DECIMAL(10,2) stores values up to 99999999.99 with exact precision.
String types: CHAR(n) is fixed-length — CHAR(10) always uses 10 bytes. Good for codes like
ISO country codes (always 2 chars) or phone numbers. VARCHAR(n) is variable-length —
VARCHAR(255) uses only as much space as the actual string plus 1-2 bytes for length. Use
VARCHAR for names, emails, descriptions. TEXT types (TINYTEXT, TEXT, MEDIUMTEXT,
LONGTEXT) store large strings but cannot have DEFAULT values or be fully indexed.
ENUM('val1','val2') stores one value from a predefined list — compact and self-documenting.
Date and time types: DATE stores YYYY-MM-DD. TIME stores HH:MM:SS. DATETIME stores
YYYY-MM-DD HH:MM:SS with no timezone info. TIMESTAMP stores datetime but converts to
UTC for storage and back to session timezone on retrieval — critical for applications used
across timezones. TIMESTAMP has a CURRENT_TIMESTAMP default, making it perfect for
"created_at" columns. The range difference: DATETIME supports 1000-9999, TIMESTAMP
supports 1970-2038 (the Unix timestamp limit).

2.3 Column Constraints

Constraints are rules enforced by the database engine on column values. NOT NULL prevents
a column from accepting NULL — it forces the application to always provide a value. UNIQUE
ensures no two rows have the same value in that column (or combination of columns for
composite unique constraints). DEFAULT provides a value when none is supplied.
AUTO_INCREMENT automatically generates sequential integers for primary keys — you never
insert this value manually. CHECK validates that a value meets a condition, e.g., CHECK (age
>= 18).

2.4 Foreign Keys and Referential Integrity

A foreign key constraint ensures that a value in one table corresponds to an existing value in
another table. You cannot insert a student with dept_id = 99 if no department with id 99 exists.
This is called referential integrity. The ON DELETE clause specifies what happens when the
referenced row is deleted: CASCADE automatically deletes all child rows (delete a department,
delete all its students — dangerous!), SET NULL sets the foreign key column to NULL (student
stays but loses dept assignment), RESTRICT (default) prevents the delete if any child rows
exist.

2.5 ALTER TABLE and Schema Changes

In production databases, tables must be changed without dropping them — they contain real
data. ALTER TABLE lets you add new columns, modify existing column definitions, rename
columns, drop columns, and add or remove constraints and indexes. Be aware that ALTER
TABLE on large tables (millions of rows) can take minutes or hours and may lock the table
during that time. Tools like pt-online-schema-change (Percona Toolkit) perform zero-downtime
schema changes by creating a copy, migrating data, and swapping the table.

2.6 Code Example: Complete University Database DDL

USE mitsacademy;

Page 5 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- ─── DEPARTMENTS TABLE

───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS departments (
dept_id INT NOT NULL AUTO_INCREMENT,
dept_name VARCHAR(100) NOT NULL,
dept_code CHAR(4) NOT NULL, -- e.g., 'COMP', 'MECH'
hod_name VARCHAR(100) NOT NULL,
established_year SMALLINT NOT NULL,
building VARCHAR(50) DEFAULT 'Main Block',
PRIMARY KEY (dept_id),
UNIQUE KEY uq_dept_code (dept_code), -- No two depts with same
code
UNIQUE KEY uq_dept_name (dept_name)
) ENGINE=InnoDB COMMENT='University departments';

-- ─── FACULTY TABLE

───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS faculty (
faculty_id INT NOT NULL AUTO_INCREMENT,
dept_id INT NOT NULL,
faculty_name VARCHAR(100) NOT NULL,
designation ENUM('Professor','Associate Professor','Assistant
Professor','Lecturer') NOT NULL,
email VARCHAR(150) NOT NULL,
phone CHAR(10) DEFAULT NULL,
joining_date DATE NOT NULL,
salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
PRIMARY KEY (faculty_id),
UNIQUE KEY uq_faculty_email (email),
CONSTRAINT fk_faculty_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE RESTRICT -- Cannot delete dept with
faculty
ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ─── STUDENTS TABLE

───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS students (
student_id INT NOT NULL AUTO_INCREMENT,
dept_id INT NOT NULL,
student_name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
phone CHAR(10) DEFAULT NULL,
dob DATE NOT NULL,
gender ENUM('Male','Female','Other') NOT NULL,
admission_year YEAR NOT NULL,
cgpa DECIMAL(4,2) DEFAULT 0.00 CHECK (cgpa >= 0.00 AND cgpa <=
10.00),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id),
UNIQUE KEY uq_student_email (email),
CONSTRAINT fk_student_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
) ENGINE=InnoDB;

Page 6 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- ─── COURSES TABLE

────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS courses (
course_id INT NOT NULL AUTO_INCREMENT,
dept_id INT NOT NULL,
faculty_id INT DEFAULT NULL, -- NULL if not yet assigned
course_code VARCHAR(10) NOT NULL,
course_name VARCHAR(200) NOT NULL,
credits TINYINT NOT NULL CHECK (credits BETWEEN 1 AND 6),
semester TINYINT NOT NULL CHECK (semester BETWEEN 1 AND 8),
course_type ENUM('Core','Elective','Lab','Project') NOT NULL DEFAULT
'Core',
PRIMARY KEY (course_id),
UNIQUE KEY uq_course_code (course_code),
CONSTRAINT fk_course_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_course_faculty
FOREIGN KEY (faculty_id) REFERENCES faculty(faculty_id)
ON DELETE SET NULL -- Course stays if faculty
leaves
ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ─── ENROLLMENTS TABLE (junction/bridge table for many-to-many)

───────────────
CREATE TABLE IF NOT EXISTS enrollments (
enrollment_id INT NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
semester TINYINT NOT NULL,
marks_obtained DECIMAL(5,2) DEFAULT NULL, -- NULL until exam is
conducted
grade CHAR(2) DEFAULT NULL, -- A+, A, B, C, D, F
enrollment_date DATE NOT NULL DEFAULT (CURDATE()),
PRIMARY KEY (enrollment_id),
UNIQUE KEY uq_student_course (student_id, course_id), -- No duplicate
enrollments
CONSTRAINT fk_enroll_student
FOREIGN KEY (student_id) REFERENCES students(student_id)
ON DELETE CASCADE -- Delete enrollments if
student deleted
ON UPDATE CASCADE,
CONSTRAINT fk_enroll_course
FOREIGN KEY (course_id) REFERENCES courses(course_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ─── ALTER TABLE EXAMPLES

─────────────────────────────────────────────────────
-- Add a new column
ALTER TABLE students ADD COLUMN address VARCHAR(500) DEFAULT NULL AFTER phone;

-- Modify a column definition

ALTER TABLE students MODIFY COLUMN cgpa DECIMAL(4,2) NOT NULL DEFAULT 0.00;

-- Rename a column (MySQL 8.0+)

ALTER TABLE students RENAME COLUMN is_active TO is_enrolled;

-- Add an index for performance (covered in Chapter 9)

ALTER TABLE students ADD INDEX idx_admission_year (admission_year);

Page 7 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- Rename a table
ALTER TABLE enrollments RENAME TO student_courses;
ALTER TABLE student_courses RENAME TO enrollments; -- rename back

-- Verify structure
DESCRIBE students;
SHOW CREATE TABLE enrollments; -- Shows full DDL with
constraints

2.7 DROP vs TRUNCATE vs DELETE

• DROP TABLE: Removes the table structure AND all data permanently. Cannot be rolled
back. Use only when you want to completely eliminate a table.
• TRUNCATE TABLE: Removes all rows but keeps the table structure. Faster than
DELETE because it does not log individual row deletions. Resets AUTO_INCREMENT
counter. Cannot be rolled back in MySQL.
• DELETE FROM table: Removes rows one by one, logs each deletion (can be rolled back
within a transaction), does not reset AUTO_INCREMENT. Can use WHERE clause to
delete specific rows.

2.8 Practice Exercises

• Add a column "blood_group" ENUM('A+','A-','B+','B-','O+','O-','AB+','AB-') to the students
table.
• Create a table "library_books" with book_id, isbn (CHAR 13, unique), title, author, dept_id
(FK), quantity (TINYINT unsigned, default 1).
• What is the difference between CHAR(10) and VARCHAR(10) in terms of storage?
• Why would you use DECIMAL(10,2) for storing a student's fee amount instead of FLOAT?
• Add a composite unique constraint to enrollments that prevents the same student from
enrolling in the same course in the same semester.

Chapter 3: DML — INSERT, UPDATE, DELETE

3.1 Understanding DML
Data Manipulation Language (DML) covers the statements that actually work with the data
inside tables: INSERT to add new rows, UPDATE to modify existing rows, and DELETE to
remove rows. Unlike DDL, DML statements in MySQL are transactional — they can be rolled
back if something goes wrong (when used within an explicit transaction). This is one of the most
important safety features of a relational database.
The golden rule of DML, especially UPDATE and DELETE, is this: always write and test your
WHERE clause as a SELECT first. If you run UPDATE students SET marks = 0 without a
WHERE clause, every single student in the table gets marks set to 0. There is no undo unless
you have a transaction open or a recent backup. Professional database administrators follow a
discipline of always testing destructive operations as SELECT queries before executing
UPDATE or DELETE.

Page 8 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

3.2 INSERT Statement Variants

The basic INSERT inserts one row by specifying column names and corresponding values. It is
good practice to always list the column names explicitly — this makes your SQL readable and
protects you from table structure changes (if a column is added, the positional INSERT might
fail or insert into the wrong column). INSERT with multiple value lists inserts several rows in a
single round-trip to the database, which is dramatically faster than individual INSERT
statements for bulk data loading.
INSERT INTO...SELECT copies data from one table to another. This is useful for archiving old
records to a history table, populating a new table from existing data, or creating summary
tables. REPLACE INTO first tries to INSERT; if a duplicate key violation occurs, it DELETES the
existing row and INSERTS the new one. ON DUPLICATE KEY UPDATE is usually preferred
because it updates only specified columns without deleting the row, preserving columns not
mentioned in the UPDATE clause.

3.3 UPDATE and DELETE Safety

UPDATE modifies columns in existing rows. The WHERE clause is not optional from a safety
standpoint — it is always required in production code. Before running any UPDATE, run the
equivalent SELECT to verify which rows will be affected. UPDATE with JOIN allows you to
modify rows in one table based on values in another table — this is more efficient than a
subquery in many cases.
DELETE removes rows permanently (within the current transaction). DELETE with a subquery
lets you delete rows based on criteria in another table. Always verify the count of rows to be
deleted with SELECT COUNT(*) using the same WHERE condition before running DELETE. In
MySQL Workbench, there is a Safe Updates mode (enabled by default) that prevents UPDATE
and DELETE without a WHERE clause on an indexed column — this is a valuable safety net
during development.

3.4 Code Example: Populating the University Database

USE mitsacademy;

-- ─── INSERT DEPARTMENTS

───────────────────────────────────────────────────────
INSERT INTO departments (dept_name, dept_code, hod_name, established_year,
building)
VALUES
('Computer Science & Engineering', 'COMP', 'Dr. Rajesh Kumar', 1995,
'Block A'),
('Mechanical Engineering', 'MECH', 'Dr. Suresh Sharma', 1990,
'Block B'),
('Electronics & Communication', 'ELEX', 'Dr. Priya Nair', 1998,
'Block A'),
('Civil Engineering', 'CIVL', 'Dr. Amit Verma', 1988,
'Block C'),
('Information Technology', 'INFT', 'Dr. Kavita Joshi', 2002,
'Block A');

-- Verify insertion
SELECT * FROM departments;

-- ─── INSERT FACULTY

───────────────────────────────────────────────────────────
INSERT INTO faculty (dept_id, faculty_name, designation, email, phone,
joining_date, salary)

Page 9 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

VALUES
(1, 'Dr. Anand Mishra', 'Professor', '[Link]@[Link]',
'9876543210', '2010-07-01', 95000.00),
(1, 'Ms. Deepa Krishnan', 'Assistant Professor', 'deepa.k@[Link]',
'9876543211', '2018-08-01', 55000.00),
(2, 'Dr. Ramesh Gupta', 'Associate Professor', 'ramesh.g@[Link]',
'9876543212', '2012-06-15', 75000.00),
(3, 'Dr. Sunita Rao', 'Professor', '[Link]@[Link]',
'9876543213', '2008-07-01', 98000.00),
(5, 'Mr. Vikram Singh', 'Lecturer', 'vikram.s@[Link]',
'9876543214', '2021-01-10', 42000.00);

-- ─── INSERT STUDENTS (20 realistic Indian students)

──────────────────────────
INSERT INTO students (dept_id, student_name, email, phone, dob, gender,
admission_year, cgpa)
VALUES
(1, 'Aarav Sharma', '[Link]@[Link]', '9900001001',
'2003-04-12', 'Male', 2021, 8.75),
(1, 'Priya Patel', '[Link]@[Link]', '9900001002',
'2003-07-22', 'Female', 2021, 9.10),
(1, 'Rohan Mehta', '[Link]@[Link]', '9900001003',
'2002-11-05', 'Male', 2021, 7.40),
(1, 'Sneha Nair', '[Link]@[Link]', '9900001004',
'2003-01-30', 'Female', 2021, 8.20),
(1, 'Arjun Reddy', '[Link]@[Link]', '9900001005',
'2003-09-14', 'Male', 2021, 6.90),
(2, 'Kiran Desai', '[Link]@[Link]', '9900001006',
'2002-06-18', 'Male', 2021, 7.85),
(2, 'Divya Joshi', '[Link]@[Link]', '9900001007',
'2003-03-25', 'Female', 2021, 8.50),
(2, 'Sanjay Kumar', '[Link]@[Link]', '9900001008',
'2003-08-09', 'Male', 2021, 5.60),
(3, 'Anjali Mishra', '[Link]@[Link]', '9900001009',
'2002-12-20', 'Female', 2021, 9.30),
(3, 'Rahul Verma', '[Link]@[Link]', '9900001010',
'2003-05-16', 'Male', 2021, 7.00),
(3, 'Meera Krishnan', 'meera.k@[Link]', '9900001011',
'2002-10-03', 'Female', 2021, 8.80),
(4, 'Tarun Agarwal', 'tarun.a@[Link]', '9900001012',
'2003-02-14', 'Male', 2021, 6.50),
(4, 'Pooja Yadav', 'pooja.y@[Link]', '9900001013',
'2003-06-28', 'Female', 2021, 7.20),
(5, 'Nikhil Bhatt', 'nikhil.b@[Link]', '9900001014',
'2002-09-07', 'Male', 2021, 8.00),
(5, 'Ritu Saxena', 'ritu.s@[Link]', '9900001015',
'2003-04-19', 'Female', 2021, 9.05),
(1, 'Aditya Gupta', 'aditya.g@[Link]', '9900001016',
'2004-01-11', 'Male', 2022, 8.30),
(1, 'Kavya Rao', '[Link]@[Link]', '9900001017',
'2004-03-23', 'Female', 2022, 7.60),
(2, 'Harsh Tiwari', 'harsh.t@[Link]', '9900001018',
'2004-07-15', 'Male', 2022, 6.10),
(3, 'Ishaan Chopra', 'ishaan.c@[Link]', '9900001019',
'2004-02-08', 'Male', 2022, 8.90),
(5, 'Simran Kaur', 'simran.k@[Link]', '9900001020',
'2004-05-30', 'Female', 2022, 9.20);

-- ─── INSERT COURSES

───────────────────────────────────────────────────────────

Page 10 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

INSERT INTO courses (dept_id, faculty_id, course_code, course_name, credits,

semester, course_type)
VALUES
(1, 1, 'CS301', 'Database Management Systems', 4, 3, 'Core'),
(1, 2, 'CS302', 'Data Structures & Algorithms', 4, 3, 'Core'),
(1, 1, 'CS303', 'Operating Systems', 3, 3, 'Core'),
(1, 2, 'CS401', 'Machine Learning', 3, 4, 'Elective'),
(5, 5, 'IT301', 'Web Technologies', 3, 3, 'Core'),
(3, 4, 'EC301', 'Digital Signal Processing', 4, 3, 'Core');

-- ─── INSERT ENROLLMENTS

───────────────────────────────────────────────────────
INSERT INTO enrollments (student_id, course_id, semester, marks_obtained,
grade, enrollment_date)
VALUES
(1, 1, 3, 88.50, 'A', '2023-07-15'),
(2, 1, 3, 95.00, 'A+', '2023-07-15'),
(3, 1, 3, 72.00, 'B', '2023-07-15'),
(4, 1, 3, 81.00, 'A', '2023-07-15'),
(5, 1, 3, 55.00, 'C', '2023-07-15'),
(1, 2, 3, 91.00, 'A+', '2023-07-15'),
(2, 2, 3, 87.50, 'A', '2023-07-15'),
(3, 2, 3, 68.00, 'B', '2023-07-15');

-- ─── UPDATE EXAMPLE

──────────────────────────────────────────────────────────
-- ALWAYS run the SELECT first to verify which rows will be affected!
SELECT student_id, student_name, cgpa
FROM students
WHERE dept_id = 1 AND admission_year = 2021; -- Check before update

-- Now update safely

UPDATE students
SET cgpa = cgpa + 0.10 -- 0.1 bonus for all CS 2021
students
WHERE dept_id = 1
AND admission_year = 2021;

-- Update with JOIN: update grade based on marks in enrollments

UPDATE enrollments e
JOIN students s ON e.student_id = s.student_id
SET [Link] = CASE
WHEN e.marks_obtained >= 90 THEN 'A+'
WHEN e.marks_obtained >= 80 THEN 'A'
WHEN e.marks_obtained >= 70 THEN 'B'
WHEN e.marks_obtained >= 60 THEN 'C'
WHEN e.marks_obtained >= 50 THEN 'D'
ELSE 'F'
END
WHERE e.marks_obtained IS NOT NULL;

-- ─── DELETE EXAMPLE

──────────────────────────────────────────────────────────
-- First check what will be deleted
SELECT * FROM students WHERE is_enrolled = FALSE AND admission_year < 2020;

-- UPSERT: insert or update on duplicate key

INSERT INTO enrollments (student_id, course_id, semester, marks_obtained,
grade, enrollment_date)
VALUES (1, 3, 3, 78.00, 'B', '2023-07-15')
ON DUPLICATE KEY UPDATE

Page 11 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

marks_obtained = VALUES(marks_obtained),
grade = VALUES(grade); -- Update marks if already
enrolled

3.5 Practice Exercises

• Insert 5 new students of your choice into the students table with realistic data.
• Write an UPDATE to set cgpa = 0.00 for all students with cgpa < 4.0 in the MECH
department. Test with SELECT first.
• Write a DELETE statement to remove all enrollments for courses in semester 4. Test with
SELECT COUNT(*) first.
• Use INSERT INTO...SELECT to copy all Computer Science students into a backup table
called cs_students_backup.
• What is the difference between REPLACE INTO and ON DUPLICATE KEY UPDATE?
When would you prefer each?

Chapter 4: SELECT Queries & Filtering

4.1 The Power of SELECT
The SELECT statement is the heart of SQL — it is the query language that retrieves data from
one or more tables. The reason SQL became the universal database language is precisely
because of SELECT's expressive power. You can filter rows, sort them, compute new values,
combine data from multiple tables, and aggregate millions of rows into summary statistics — all
in a single, declarative statement. The database engine figures out the most efficient execution
plan; you just describe what you want.
A SELECT statement is executed in a specific logical order that differs from its written order.
This is crucial for understanding why certain clauses cannot reference aliases defined in the
same query level. The logical order is: FROM (identify source tables), WHERE (filter rows),
GROUP BY (group rows), HAVING (filter groups), SELECT (compute output columns), ORDER
BY (sort results), LIMIT (restrict count). Even though you write SELECT before WHERE,
WHERE executes first. This is why you cannot use a SELECT alias in a WHERE clause.

4.2 WHERE Clause Operators

The WHERE clause uses operators to filter rows. Comparison operators (=, !=, <, >, <=, >=)
work on numbers, strings, and dates. BETWEEN x AND y is inclusive on both ends —
equivalent to column >= x AND column <= y. IN (val1, val2, val3) matches any value in the list
and is much cleaner than multiple OR conditions. LIKE performs pattern matching: % matches
any sequence of characters, _ matches exactly one character. IS NULL and IS NOT NULL test
for NULL values — you cannot use = NULL because NULL = NULL is NULL, not TRUE.
The REGEXP operator allows full regular expression pattern matching in WHERE clauses.
REGEXP '^A' matches strings starting with A. The logical operators AND, OR, and NOT
combine conditions. Operator precedence: NOT is evaluated first, then AND, then OR. Always
use parentheses when mixing AND and OR to make your intent explicit and avoid subtle bugs.
"WHERE dept = 1 AND marks > 80 OR dept = 2" is evaluated as "(dept = 1 AND marks > 80)
OR dept = 2" — which probably is not what you wanted.

Page 12 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

4.3 Sorting, Pagination, and Expressions

ORDER BY sorts the result set. Multiple columns in ORDER BY create a multi-level sort:
ORDER BY dept_id ASC, marks DESC sorts by department first, then by marks descending
within each department. ORDER BY with NULL values: by default, NULLs sort first in ASC order
(treated as lowest value). Use ORDER BY column IS NULL ASC, column ASC to push NULLs
to the end.
LIMIT and OFFSET implement pagination. LIMIT 10 OFFSET 20 skips the first 20 rows and
returns the next 10 — this is page 3 of a 10-items-per-page list. DISTINCT removes duplicate
rows from the result. Expressions in SELECT let you compute new values: marks * 0.4 +
internal * 0.6 AS final_marks calculates a weighted total. CASE WHEN is SQL's if-else
construct, usable directly in SELECT, WHERE, ORDER BY, and GROUP BY.

4.4 Handling NULL Values

NULL is a special marker meaning "unknown" or "not applicable." It is not zero, not an empty
string, and not FALSE — it is the absence of a value. Any arithmetic with NULL produces NULL
(NULL + 5 = NULL). Any comparison with NULL produces NULL, not TRUE or FALSE.
COALESCE(expr1, expr2, expr3) returns the first non-NULL expression — extremely useful for
providing default display values. IFNULL(expr, default) is MySQL-specific and equivalent to
COALESCE with two arguments. NULLIF(expr, value) returns NULL if expr equals value,
otherwise returns expr — useful for avoiding division-by-zero errors: SUM(marks) /
NULLIF(COUNT(*), 0).

4.5 Code Example: Comprehensive Student Queries

USE mitsacademy;

-- ─── BASIC SELECT

────────────────────────────────────────────────────────────
SELECT student_id, student_name, email -- Specific columns, not *
FROM students
ORDER BY student_name ASC;

-- Column aliases make output readable

SELECT student_id AS 'Roll No',
student_name AS 'Student Name',
UPPER(email) AS 'Email (Upper)',
TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS 'Age'
FROM students;

-- ─── WHERE CLAUSE EXAMPLES

───────────────────────────────────────────────────
-- Students in CSE department
SELECT student_name, cgpa
FROM students
WHERE dept_id = 1;

-- Students with CGPA between 8 and 9 (inclusive)

SELECT student_name, cgpa
FROM students
WHERE cgpa BETWEEN 8.00 AND 9.00
ORDER BY cgpa DESC;

-- Students in CSE or IT department

SELECT student_name, dept_id
FROM students

Page 13 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

WHERE dept_id IN (1, 5);

-- Students whose name starts with 'A' (LIKE pattern)

SELECT student_name
FROM students
WHERE student_name LIKE 'A%';

-- Students with no phone number on record

SELECT student_name, phone
FROM students
WHERE phone IS NULL;

-- Students using REGEXP: name contains 'a' or 'A'

SELECT student_name
FROM students
WHERE student_name REGEXP '[aA]';

-- ─── CASE WHEN for grade calculation

─────────────────────────────────────────
SELECT s.student_name,
e.marks_obtained,
CASE
WHEN e.marks_obtained >= 90 THEN 'Outstanding'
WHEN e.marks_obtained >= 75 THEN 'Distinction'
WHEN e.marks_obtained >= 60 THEN 'First Class'
WHEN e.marks_obtained >= 50 THEN 'Second Class'
WHEN e.marks_obtained >= 40 THEN 'Pass'
WHEN e.marks_obtained IS NULL THEN 'Not Appeared'
ELSE 'Fail'
END AS 'Performance',
COALESCE([Link], 'Pending') AS 'Grade' -- Show 'Pending' if
grade is NULL
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1 -- DBMS course
ORDER BY e.marks_obtained DESC NULLS LAST;

-- ─── PAGINATION EXAMPLE

──────────────────────────────────────────────────────
-- Page 1: records 1-5
SELECT student_name, cgpa
FROM students
ORDER BY cgpa DESC
LIMIT 5 OFFSET 0; -- OFFSET 0 = first page

-- Page 2: records 6-10

SELECT student_name, cgpa
FROM students
ORDER BY cgpa DESC
LIMIT 5 OFFSET 5; -- Skip first 5

-- ─── DISTINCT
────────────────────────────────────────────────────────────────
SELECT DISTINCT admission_year
FROM students
ORDER BY admission_year; -- List unique admission years

-- ─── COMPUTED COLUMNS

─────────────────────────────────────────────────────────
SELECT student_name,
cgpa,

Page 14 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

cgpa * 10 AS 'Percentage Equivalent',

ROUND(cgpa * 9.5, 1) AS 'Approx Percentage', -- Common
Indian formula
NULLIF(cgpa, 0.00) AS 'CGPA (NULL if zero)' -- NULL if
cgpa is 0
FROM students
ORDER BY cgpa DESC;

4.6 Practice Exercises

• Write a SELECT to find all female students admitted in 2022 with CGPA above 8.0,
ordered by CGPA descending.
• Use LIKE to find all students whose email contains "nair" or "rao" (case-insensitive).
• Write a paginated query to display students 11 through 15 sorted alphabetically by name.
• Use CASE WHEN to add a "Scholarship Eligible" column: YES if CGPA >= 8.5, NO
otherwise.
• Find all students where the phone number is NOT NULL and the email contains
"@[Link]".

Chapter 5: Aggregate Functions & GROUP BY

5.1 Aggregate Functions — Computing Summaries
Aggregate functions compute a single result from a set of rows. They are the foundation of
analytical queries — answering questions like "How many students failed?" or "What is the
average CGPA per department?" The most important distinction to understand is COUNT(*)
versus COUNT(column_name). COUNT(*) counts all rows including those with NULLs.
COUNT(column_name) counts only rows where that column is NOT NULL. This difference can
lead to seriously wrong results if you are not careful.
SUM, AVG, MAX, and MIN all ignore NULL values. AVG(marks) does not count NULL marks as
zero — it divides the sum of non-null marks by the count of non-null rows. If you want to treat
NULL as zero in an average, use AVG(COALESCE(marks, 0)). MAX and MIN work on strings
(alphabetically) and dates as well as numbers. SUM on an empty set or all NULLs returns
NULL, not zero.

5.2 GROUP BY — Grouping Rows

GROUP BY collapses multiple rows with the same value(s) in the specified column(s) into a
single summary row. The critical rule is this: every column in the SELECT clause must either
appear in GROUP BY or be wrapped in an aggregate function. This rule exists because after
grouping, the database has one output row per group — if a column has different values within
a group, which one should it show? MySQL has a ONLY_FULL_GROUP_BY mode (enabled by
default in MySQL 5.7+) that enforces this rule strictly. In older MySQL versions, violating this
rule gave non-deterministic results.
Grouping by multiple columns creates one group per unique combination. GROUP BY dept_id,
admission_year creates one row per department-year pair. This allows very detailed analytical
breakdowns. The order of columns in GROUP BY affects grouping behavior but not the sort
order of output — use ORDER BY to control output ordering.

Page 15 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

5.3 HAVING vs WHERE

This is one of the most commonly confused concepts in SQL. WHERE filters individual rows
before any grouping happens. HAVING filters groups after the GROUP BY and aggregate
computation. You use WHERE to filter the source data, and HAVING to filter the results of
aggregation. For example, to find departments where the average CGPA is above 8: you cannot
put AVG(cgpa) > 8 in WHERE because AVG has not been computed yet at that point in query
execution. It must go in HAVING.

5.4 Date and String Functions

MySQL provides rich built-in functions for working with dates and strings. For dates: NOW()
returns current datetime, CURDATE() returns today's date, DATE_ADD(date, INTERVAL n unit)
adds time to a date, DATEDIFF(date1, date2) returns the number of days between two dates,
DATE_FORMAT(date, format) formats a date for display, TIMESTAMPDIFF(unit, start, end)
computes the difference in specified units. For strings: CONCAT(s1, s2) joins strings,
LENGTH(s) returns byte count, CHAR_LENGTH(s) returns character count (important for multi-
byte UTF8), UPPER/LOWER change case, TRIM removes whitespace, SUBSTRING(s, start,
length) extracts a portion, REPLACE(s, from, to) substitutes text.

5.5 Code Example: Department-wise Analytics

USE mitsacademy;

-- ─── BASIC AGGREGATES

─────────────────────────────────────────────────────────
SELECT COUNT(*) AS 'Total Students',
COUNT(phone) AS 'Students with Phone', -- COUNT ignores NULLs
COUNT(*) - COUNT(phone) AS 'Students without Phone',
AVG(cgpa) AS 'Average CGPA',
MAX(cgpa) AS 'Highest CGPA',
MIN(cgpa) AS 'Lowest CGPA',
ROUND(SUM(cgpa)/COUNT(*), 2) AS 'Manual Average' -- Same as AVG
FROM students;

-- ─── GROUP BY DEPARTMENT

──────────────────────────────────────────────────────
SELECT d.dept_name AS 'Department',
COUNT(s.student_id) AS 'Total Students',
ROUND(AVG([Link]), 2) AS 'Average CGPA',
MAX([Link]) AS 'Highest CGPA',
MIN([Link]) AS 'Lowest CGPA',
SUM(CASE WHEN [Link] >= 7.0
THEN 1 ELSE 0 END) AS 'Students above 7.0',
ROUND(
SUM(CASE WHEN [Link] >= 5.0 THEN 1 ELSE 0 END)
/ COUNT(s.student_id) * 100, 1) AS 'Pass Rate %'
FROM departments d
LEFT JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY AVG([Link]) DESC;

-- ─── HAVING — filter groups

───────────────────────────────────────────────────
-- Departments with MORE than 3 students AND average CGPA above 7.5
SELECT d.dept_name,
COUNT(s.student_id) AS student_count,
ROUND(AVG([Link]), 2) AS avg_cgpa

Page 16 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

FROM departments d
JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(s.student_id) > 3 -- Filter group by count
AND AVG([Link]) > 7.5; -- Filter group by average

-- ─── WITH ROLLUP — adds subtotal rows

────────────────────────────────────────
SELECT COALESCE(d.dept_name, 'GRAND TOTAL') AS 'Department',
COUNT(s.student_id) AS 'Students',
ROUND(AVG([Link]), 2) AS 'Average CGPA'
FROM departments d
JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_name WITH ROLLUP; -- Extra row at end: grand total

-- ─── GROUP_CONCAT — aggregate strings

─────────────────────────────────────────
SELECT d.dept_name,
GROUP_CONCAT(s.student_name ORDER BY s.student_name SEPARATOR ', ')
AS 'Students'
FROM departments d
JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name;

-- ─── DATE FUNCTIONS

────────────────────────────────────────────────────────────
SELECT student_name,
dob,
TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS 'Age',
DATE_FORMAT(dob, '%D %M %Y') AS 'Birth Date',
YEAR(dob) AS 'Birth Year',
MONTHNAME(dob) AS 'Birth Month',
DATE_ADD(dob, INTERVAL 18 YEAR) AS '18th Birthday',
DATEDIFF(CURDATE(), dob) AS 'Days Lived'
FROM students
LIMIT 5;

-- ─── STRING FUNCTIONS

─────────────────────────────────────────────────────────
SELECT student_name,
UPPER(student_name) AS 'Name Upper',
LENGTH(student_name) AS 'Bytes',
CHAR_LENGTH(student_name) AS 'Characters',
SUBSTRING(email, 1, INSTR(email,'@')-1) AS 'Username', -- Extract
before @
REPLACE(student_name, ' ', '_') AS 'Username Style',
CONCAT('MITS/', LPAD(student_id, 5,'0')) AS 'Roll Number' -- MITS/00001
FROM students
LIMIT 5;

5.6 Practice Exercises

• Find the total number of male and female students in each department using GROUP BY.
• Find all departments where the minimum CGPA is below 6.0 using HAVING.
• Write a query to display the number of students admitted each year, sorted by year.
• Use GROUP_CONCAT to list all course codes taught by each faculty member.
• Find the average marks obtained in each course. Only show courses where at least 3
students are enrolled and the average is above 70.

Page 17 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

Chapter 6: JOINs — Combining Tables

6.1 Why JOINs are Necessary
The relational model stores data in multiple normalized tables to avoid redundancy. The
student's department name is not stored in the students table — only the dept_id. This is correct
design. But when you need to display a report showing student names with their department
names, you need to combine data from both tables. JOINs are the mechanism for combining
rows from two or more tables based on a related column. JOINs are the most powerful and
most misunderstood feature of SQL.
The key concept to internalize is that a JOIN creates a Cartesian product first — every row from
the left table is matched with every row from the right table — and then filters based on the ON
condition. If the students table has 20 rows and departments has 5 rows, the raw Cartesian
product has 100 rows. The ON condition then filters these to only the rows where the foreign
key matches the primary key. Understanding this mental model helps you debug incorrect
JOINs that return too many or too few rows.

6.2 Types of JOINs

INNER JOIN returns only rows that have matching values in BOTH tables. If a student has no
dept_id, they will not appear in an INNER JOIN with departments. If a department has no
students, it will not appear either. INNER JOIN is the most common join type for fetching related
data where both sides must exist.
LEFT JOIN (also called LEFT OUTER JOIN) returns ALL rows from the left table, plus matching
rows from the right table. Where there is no match, the right table columns contain NULL. This
is essential for finding orphaned records: students with no enrollments, departments with no
faculty. RIGHT JOIN is the mirror image — all right table rows are returned. FULL OUTER JOIN
returns all rows from both tables (unmatched rows get NULL on the other side); MySQL does
not support FULL OUTER JOIN directly, so you combine LEFT JOIN UNION ALL RIGHT JOIN
and filter.
CROSS JOIN returns every combination of rows from both tables with no ON condition — a
Cartesian product. Rarely useful in practice, but has legitimate uses: generating a multiplication
table, or pairing every student with every course to find which combinations are missing. SELF
JOIN joins a table to itself using aliases — classic use case is an employee table where each
employee has a manager_id pointing to another row in the same table.

6.3 Joining Three or More Tables

SQL allows joining as many tables as needed in a single query. The query optimizer plans the
most efficient join order. When joining multiple tables, be precise with aliases and fully qualify
ambiguous column names. A common mistake is joining in the wrong order when using LEFT
JOIN — if you LEFT JOIN A to B then INNER JOIN B to C, the INNER JOIN silently converts
the LEFT JOIN to an INNER JOIN for rows where B has no match.

6.4 Code Example: Comprehensive University Reports

USE mitsacademy;

Page 18 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- ─── INNER JOIN: students with department info

─────────────────────────────────
SELECT s.student_id,
s.student_name,
d.dept_name,
d.dept_code,
[Link]
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id
ORDER BY d.dept_name, s.student_name;

-- ─── 4-TABLE JOIN: complete enrollment report

──────────────────────────────────
SELECT s.student_name AS 'Student',
d.dept_name AS 'Department',
c.course_name AS 'Course',
c.course_code AS 'Code',
f.faculty_name AS 'Faculty',
e.marks_obtained AS 'Marks',
COALESCE([Link], 'Not Graded') AS 'Grade'
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
LEFT JOIN faculty f ON c.faculty_id = f.faculty_id -- LEFT: show course
even if no faculty
ORDER BY s.student_name, c.course_code;

-- ─── LEFT JOIN: find students with NO enrollments

─────────────────────────────
SELECT s.student_name,
[Link],
e.enrollment_id -- Will be NULL if not enrolled
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE e.enrollment_id IS NULL; -- Only unenrolled students

-- ─── LEFT JOIN: find departments with NO students

─────────────────────────────
SELECT d.dept_name,
COUNT(s.student_id) AS student_count -- 0 for empty departments
FROM departments d
LEFT JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY student_count ASC;

-- ─── SELF JOIN: find students in the same department

──────────────────────────
-- Pairs of students who share the same department and admission year
SELECT s1.student_name AS 'Student 1',
s2.student_name AS 'Student 2',
d.dept_name AS 'Department',
s1.admission_year
FROM students s1
JOIN students s2 ON s1.dept_id = s2.dept_id
AND s1.admission_year = s2.admission_year
AND s1.student_id < s2.student_id -- Avoid
duplicates and self-pairs
JOIN departments d ON s1.dept_id = d.dept_id
ORDER BY d.dept_name, s1.student_name;

Page 19 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- ─── FULL OUTER JOIN workaround in MySQL

─────────────────────────────────────
-- All students (even unenrolled) AND all courses (even with no enrollments)
SELECT s.student_name, c.course_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id

UNION ALL

SELECT s.student_name, c.course_name

FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
LEFT JOIN students s ON e.student_id = s.student_id
WHERE e.enrollment_id IS NULL; -- Courses with no enrollments
only

6.5 Practice Exercises

• Write a query to list all faculty members and their department names using INNER JOIN.
• Use LEFT JOIN to find all courses that have no students enrolled in them.
• Write a 3-table JOIN to produce a report: faculty_name, department, list of courses they
teach.
• Use a SELF JOIN to find pairs of students with the same CGPA.
• Explain why INNER JOIN with departments would hide students whose dept_id is NULL,
while LEFT JOIN would show them.

Chapter 7: Subqueries & CTEs

7.1 What is a Subquery?
A subquery is a SELECT statement nested inside another SQL statement. It can appear in the
WHERE clause, FROM clause, SELECT column list, or HAVING clause. Subqueries are a
powerful alternative to JOINs for certain types of queries, particularly when you need to
compare a row against an aggregated value or when you need to reference the outer query
from within the inner query (correlated subquery). Understanding when to use a subquery
versus a JOIN is both a performance and a readability decision.
IN vs EXISTS is one of the most important performance distinctions in SQL. IN executes the
subquery first, collects all results into a list, then checks each outer row against that list. EXISTS
executes the subquery for each outer row and stops as soon as it finds one matching row. For
small result sets IN is fine, but when the subquery returns thousands of rows, EXISTS is
dramatically faster because it short-circuits. Also, IN with NULL values behaves unexpectedly: if
the subquery returns any NULL, NOT IN returns no rows at all, which is a notorious bug.

7.2 Correlated Subqueries

A correlated subquery references a column from the outer query. It re-executes for each row
processed by the outer query, making it essentially a loop. This can be very slow on large
tables. However, correlated subqueries can express queries that are difficult or impossible with
joins — particularly "for each X, find the maximum Y in the same group." EXISTS is almost
always written as a correlated subquery.

Page 20 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

7.3 CTEs — Common Table Expressions

A CTE, introduced with the WITH keyword, is a named temporary result set that you can
reference in the main query. CTEs were introduced in MySQL 8.0 and are one of the most
powerful features for writing readable, maintainable SQL. A CTE defined in the WITH clause
behaves like a view that exists only for the duration of that single query. Unlike subqueries in the
FROM clause, CTEs can be referenced multiple times in the main query without re-executing,
and multiple CTEs can be chained (each can reference previous ones).
Recursive CTEs are a special form that allows a query to refer to its own output. The recursive
CTE has two parts: an anchor (the base case) and a recursive member (references the CTE
itself). Recursive CTEs are ideal for hierarchical data: organizational charts, product category
trees, bill of materials, and course prerequisite chains. Without recursive CTEs, such
hierarchical queries require application-level code or multiple round-trips to the database.

7.4 Code Example: Advanced Subquery and CTE Techniques

USE mitsacademy;

-- ─── SUBQUERY IN WHERE

────────────────────────────────────────────────────────
-- Students with CGPA above the overall average
SELECT student_name, cgpa
FROM students
WHERE cgpa > (SELECT AVG(cgpa) FROM students) -- Scalar subquery returns
one value
ORDER BY cgpa DESC;

-- ─── IN vs EXISTS
─────────────────────────────────────────────────────────────
-- Students who are enrolled in at least one course (using IN)
SELECT student_name FROM students
WHERE student_id IN (SELECT DISTINCT student_id FROM enrollments);

-- Same query using EXISTS (preferred for large datasets — stops at first
match)
SELECT student_name FROM students s
WHERE EXISTS (
SELECT 1 FROM enrollments e
WHERE e.student_id = s.student_id -- References outer query
column
);

-- ─── CORRELATED SUBQUERY

─────────────────────────────────────────────────────
-- Students with CGPA above the average FOR THEIR OWN DEPARTMENT
SELECT s.student_name,
[Link],
s.dept_id,
(SELECT ROUND(AVG([Link]), 2)
FROM students s2
WHERE s2.dept_id = s.dept_id) -- Correlated: references
outer s.dept_id
AS 'Dept Average'
FROM students s
WHERE [Link] > (
SELECT AVG([Link])
FROM students s3
WHERE s3.dept_id = s.dept_id -- Re-executes for each outer
row

Page 21 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

)
ORDER BY s.dept_id, [Link] DESC;

-- ─── SUBQUERY IN FROM (Derived Table)

────────────────────────────────────────
SELECT dept_stats.dept_id,
d.dept_name,
dept_stats.avg_cgpa,
dept_stats.student_count
FROM (
SELECT dept_id,
ROUND(AVG(cgpa), 2) AS avg_cgpa,
COUNT(*) AS student_count
FROM students
GROUP BY dept_id
) AS dept_stats -- Derived table must have an
alias
JOIN departments d ON dept_stats.dept_id = d.dept_id
WHERE dept_stats.avg_cgpa > 7.5;

-- ─── CTE — readable alternative to derived tables

────────────────────────────
WITH dept_stats AS (
SELECT dept_id,
ROUND(AVG(cgpa), 2) AS avg_cgpa,
COUNT(*) AS student_count
FROM students
GROUP BY dept_id
),
top_student_per_dept AS (
SELECT student_id,
dept_id,
student_name,
cgpa,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS
rank_in_dept
FROM students
)
SELECT d.dept_name,
ds.avg_cgpa AS 'Dept Average CGPA',
ds.student_count AS 'Student Count',
ts.student_name AS 'Top Student',
[Link] AS 'Top CGPA'
FROM departments d
JOIN dept_stats ds ON d.dept_id = ds.dept_id
JOIN top_student_per_dept ts ON d.dept_id = ts.dept_id
WHERE ts.rank_in_dept = 1
ORDER BY ds.avg_cgpa DESC;

-- ─── RECURSIVE CTE — course prerequisite chain

────────────────────────────────
-- First, add a self-referential prerequisite column to courses for demo
-- ALTER TABLE courses ADD COLUMN prerequisite_id INT DEFAULT NULL;
-- UPDATE courses SET prerequisite_id = 1 WHERE course_id = 2; -- CS302
requires CS301
-- UPDATE courses SET prerequisite_id = 2 WHERE course_id = 4; -- CS401
requires CS302

WITH RECURSIVE prerequisite_chain AS (

-- Anchor: start with the target course
SELECT course_id,

Page 22 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

course_name,
prerequisite_id,
0 AS depth,
course_name AS chain
FROM courses
WHERE course_id = 4 -- Start from Machine Learning

UNION ALL

-- Recursive: find the prerequisite of the current course

SELECT c.course_id,
c.course_name,
c.prerequisite_id,
[Link] + 1,
CONCAT(c.course_name, ' -> ', [Link])
FROM courses c
JOIN prerequisite_chain pc ON c.course_id = pc.prerequisite_id
WHERE [Link] < 10 -- Prevent infinite loops (cycle detection)
)
SELECT chain AS 'Prerequisite Chain',
depth AS 'Levels Deep'
FROM prerequisite_chain
ORDER BY depth DESC
LIMIT 1; -- Show the full chain

7.5 Practice Exercises

• Write a subquery to find students who scored above the average marks in the DBMS
course (course_id = 1).
• Use EXISTS to find all faculty members who are teaching at least one course.
• Rewrite the correlated subquery example using a CTE for better readability.
• Write a CTE that first calculates total enrolled courses per student, then selects students
enrolled in more than 2 courses.
• What would happen if you used NOT IN with a subquery that returns NULLs? Write an
example demonstrating the problem and how EXISTS avoids it.

Chapter 8: Database Design & Normalization

8.1 Entity-Relationship Modelling
Before writing a single CREATE TABLE statement, a good database designer spends time
understanding the real-world domain. Entity-Relationship (ER) modelling is the process of
identifying the entities (things we want to store data about), their attributes (properties), and the
relationships between them. An entity becomes a table, an attribute becomes a column, and
relationships are implemented through foreign keys (for one-to-many) or junction tables (for
many-to-many).
Relationships have cardinality. One-to-One: one person has one passport. Implemented by
putting a foreign key in either table, or storing both in one table. One-to-Many: one department
has many students, but each student belongs to one department. Implemented by putting the
foreign key on the "many" side (dept_id in students table). Many-to-Many: one student can
enroll in many courses, and one course can have many students. Cannot be directly

Page 23 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

represented with two tables — requires a junction (bridge) table containing foreign keys to both
tables.

8.2 Normal Forms — The Theory of Good Design

Normalization is the process of organizing a database to reduce redundancy and improve data
integrity. Each normal form (NF) eliminates a specific type of anomaly. An anomaly is a problem
that occurs when you insert, update, or delete data — data gets corrupted or becomes
inconsistent.
First Normal Form (1NF) requires that every column contains atomic (indivisible) values — no
comma-separated lists, no repeating groups. If a column stores "Math, Physics, Chemistry," you
cannot query it meaningfully. Each value must be in its own row. 1NF also requires that each
row is uniquely identifiable (needs a primary key).
Second Normal Form (2NF) applies only to tables with composite primary keys. It requires that
every non-key column depends on the FULL primary key, not just part of it. If a table has
primary key (student_id, course_id) and also has a column dept_name that depends only on
student_id (not on course_id), that is a partial dependency — move dept_name to the students
table.
Third Normal Form (3NF) eliminates transitive dependencies. A transitive dependency is when
column A depends on B, and B depends on the primary key C — so A transitively depends on C
through B. Example: if students table has (student_id, dept_id, dept_name), then dept_name
depends on dept_id which depends on student_id. dept_name should be in the departments
table, not students table. 3NF is the practical target for most OLTP databases.

8.3 When to Denormalize

Normalization is not always the final answer. OLTP (Online Transaction Processing) databases
— those that handle frequent INSERT/UPDATE/DELETE operations — benefit greatly from
normalization because each piece of data is stored once. OLAP (Online Analytical Processing)
databases — used for reporting and analytics — sometimes benefit from denormalization
(combining tables, storing redundant data) because fewer joins mean faster reads. Data
warehouses often use Star Schema or Snowflake Schema — deliberately denormalized
structures optimized for analytical queries.

8.4 Code Example: Normalizing MITS Academy from Scratch

USE mitsacademy;

-- ─── UNNORMALIZED (BAD) DESIGN — showing what NOT to do ─────────────────────

-- This violates 1NF (comma-separated courses), 2NF, and 3NF
/*
CREATE TABLE bad_student_data (
student_id INT,
student_name VARCHAR(100),
dept_id INT,
dept_name VARCHAR(100), -- Transitive dependency (3NF violation)
hod_name VARCHAR(100), -- Depends on dept, not student (3NF
violation)
courses_enrolled VARCHAR(500) -- 'CS301,CS302,CS303' violates 1NF!
);
*/

-- ─── STEP 1: Identify 1NF violation and fix

───────────────────────────────────

Page 24 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- courses_enrolled as a list → separate enrollments table (already done in

Ch2)

-- ─── STEP 2: Identify 2NF violations and fix

─────────────────────────────────
-- If enrollment had: (student_id, course_id, student_name, course_name)
-- student_name depends only on student_id (partial dependency)
-- course_name depends only on course_id (partial dependency)
-- Fix: student_name stays in students, course_name stays in courses

-- ─── STEP 3: Identify 3NF violations and fix

─────────────────────────────────
-- students table having dept_name, hod_name:
-- student_id → dept_id → dept_name (transitive)
-- Fix: dept_name, hod_name belong in departments table

-- ─── VERIFY the normalized structure we have built

────────────────────────────
-- departments: dept_id(PK), dept_name, dept_code, hod_name, established_year
-- students: student_id(PK), dept_id(FK→departments), student_name, email...
-- faculty: faculty_id(PK), dept_id(FK→departments), faculty_name...
-- courses: course_id(PK), dept_id(FK), faculty_id(FK), course_name...
-- enrollments: enrollment_id(PK), student_id(FK), course_id(FK), marks...

-- This is 3NF — every non-key column depends on the key, the whole key,
-- and nothing but the key.

-- ─── Check for proper key usage

───────────────────────────────────────────────
-- Verify primary keys
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'mitsacademy'
AND CONSTRAINT_NAME = 'PRIMARY';

-- Verify foreign keys

SELECT TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'mitsacademy'
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

-- ─── JUNCTION TABLE design for many-to-many

───────────────────────────────────
-- The enrollments table IS our junction table for students ↔ courses
-- It has additional columns (marks_obtained, grade) which is perfectly normal
-- Pure junction: CREATE TABLE student_course (student_id INT, course_id INT,
PRIMARY KEY(student_id, course_id))
-- With attributes: enrollment_id, student_id, course_id, marks, grade, date

-- ─── Sample normalization benefit: update in ONE place

───────────────────────
-- Change HOD of CSE department — ONE row update, reflected everywhere
UPDATE departments
SET hod_name = 'Dr. Sanjay Ramanujan'
WHERE dept_code = 'COMP';

Page 25 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- Without normalization, we'd need to update this in EVERY student row!

8.5 Practice Exercises

• Given a table: order(order_id, customer_name, customer_address, product_id,
product_name, quantity, price) — identify all normalization violations and normalize to
3NF.
• What is the difference between a primary key and a candidate key? Give an example from
the students table.
• A library table stores: book_id, isbn, title, author_name, author_bio, publisher, category.
Normalize it to 3NF.
• Draw (or describe in text) the ER diagram for the MITS Academy database with all entities
and relationships.
• When might you intentionally violate 3NF? Describe a real-world scenario where
denormalization improves performance.

Chapter 9: Indexes — Speeding Up Queries

9.1 The Problem Without Indexes
Without an index, MySQL must scan every single row in a table to find matching rows — this is
called a full table scan. For a table with 100 rows, this is instantaneous. For a table with 10
million rows, a full table scan can take minutes. An index is a separate data structure that allows
MySQL to jump directly to the relevant rows without reading the whole table — like using a
book's index to find a topic instead of reading every page.
MySQL uses B-tree (Balanced Tree) indexes by default. A B-tree index stores column values in
a sorted, hierarchical structure where every leaf node is at the same depth. To find rows where
marks = 85, MySQL traverses the tree from the root — at each node, it decides left or right
based on the comparison — and reaches the answer in O(log n) operations instead of O(n). For
a table with 1 million rows, that is roughly 20 comparisons instead of 1,000,000.

9.2 Types of Indexes

The PRIMARY KEY creates a clustered index — the table's physical row storage is ordered by
the primary key. This makes primary key lookups extremely fast. In InnoDB, every table is
organized as a clustered index (also called an index-organized table). A UNIQUE index
enforces uniqueness and allows fast lookups by that column. A regular INDEX (non-unique)
allows fast lookups without enforcing uniqueness. A FULLTEXT index is used for natural
language text search with MATCH()...AGAINST() syntax.
A composite index covers multiple columns. The column order in a composite index matters
critically — MySQL can use the index only if the query filters on a prefix of the index columns
(the leftmost prefix rule). An index on (dept_id, admission_year, cgpa) can be used for queries
filtering on dept_id alone, on dept_id + admission_year, or on all three. It CANNOT be used for
queries filtering only on admission_year or only on cgpa (because dept_id is not included in the
filter).

Page 26 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

9.3 When to Index and When Not To

Index columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses for
frequently-run queries. Index columns with high cardinality (many distinct values) — an index on
email is useful because every email is unique. An index on gender (only 2-3 distinct values) is
rarely useful — MySQL may choose to do a full table scan anyway because 50% of the table
needs to be read.
Do not index every column. Each index slows down INSERT, UPDATE, and DELETE because
the index must be updated along with the table data. On small tables (< 1000 rows), indexes
rarely help — the overhead of index maintenance may outweigh the query speedup. On
columns that are updated very frequently, index maintenance cost may be significant.

9.4 EXPLAIN — Understanding Query Execution

EXPLAIN is your primary tool for understanding how MySQL executes a query. Place EXPLAIN
before any SELECT to see the execution plan without actually running the query. The most
important column is "type" — it shows the access method: ALL (full table scan — usually bad for
large tables), index (full index scan), range (index range scan), ref (index lookup by non-unique
key), eq_ref (index lookup returning at most one row), const/system (single row lookup —
optimal). EXPLAIN ANALYZE (MySQL 8.0.18+) actually executes the query and shows real
timing, not just estimates.

9.5 Code Example: Index Performance Analysis

USE mitsacademy;

-- ─── BASELINE: query without index

───────────────────────────────────────────
-- First remove any existing index on cgpa
-- DROP INDEX idx_cgpa ON students; -- run if index exists

EXPLAIN SELECT student_name, cgpa

FROM students
WHERE cgpa > 8.0;
-- type: ALL (full table scan) — reads every row
-- rows: 20 (estimates all rows must be checked)

-- ─── CREATE INDEXES

───────────────────────────────────────────────────────────
-- Single column index on cgpa
CREATE INDEX idx_cgpa ON students(cgpa);

-- Composite index for common filter pattern: dept + year + cgpa

CREATE INDEX idx_dept_year_cgpa ON students(dept_id, admission_year, cgpa);

-- Unique index (already created as UNIQUE KEY — but shown here for clarity)
-- CREATE UNIQUE INDEX uq_email ON students(email);

-- ─── EXPLAIN AFTER INDEX CREATION

────────────────────────────────────────────
EXPLAIN SELECT student_name, cgpa
FROM students
WHERE cgpa > 8.0;
-- type: range (index range scan — much better!)
-- key: idx_cgpa (MySQL chose our index)
-- rows: estimated 8 (not 20)

Page 27 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

-- ─── COMPOSITE INDEX — leftmost prefix rule

───────────────────────────────────
-- This query CAN use idx_dept_year_cgpa (matches leftmost column dept_id)
EXPLAIN SELECT student_name, cgpa, admission_year
FROM students
WHERE dept_id = 1
AND admission_year = 2021;
-- key: idx_dept_year_cgpa ✓

-- This query CANNOT use idx_dept_year_cgpa (skips dept_id, starts with

admission_year)
EXPLAIN SELECT student_name, cgpa
FROM students
WHERE admission_year = 2021;
-- key: NULL — full table scan! Create separate index if this query runs
frequently

-- ─── COVERING INDEX: query answered from index alone

─────────────────────────
-- If SELECT only needs columns that are ALL in the index, MySQL never touches
the table
EXPLAIN SELECT dept_id, admission_year, cgpa
FROM students
WHERE dept_id = 1;
-- Extra: 'Using index' — all data came from the index, no table lookup!

-- ─── FULLTEXT INDEX for text search

──────────────────────────────────────────
ALTER TABLE courses ADD FULLTEXT INDEX ft_course_name (course_name);

-- Natural language full-text search

SELECT course_name, MATCH(course_name) AGAINST ('database systems' IN NATURAL
LANGUAGE MODE) AS score
FROM courses
WHERE MATCH(course_name) AGAINST ('database systems' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- ─── SHOW INDEXES on a table

─────────────────────────────────────────────────
SHOW INDEXES FROM students; -- Lists all indexes: name, columns,
unique/non-unique

-- ─── EXPLAIN ANALYZE (MySQL 8.0.18+) — real timing

───────────────────────────
EXPLAIN ANALYZE
SELECT s.student_name, d.dept_name, [Link]
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
WHERE [Link] > 8.0
ORDER BY [Link] DESC;
-- Shows: actual time=X..Y rows=Z loops=N
-- Reveals whether estimates match reality

9.6 Practice Exercises

• Run EXPLAIN on a JOIN query before and after creating an index on the JOIN column.
Compare the "type" and "rows" values.
• Create a composite index for the enrollments table that optimizes the query: WHERE
student_id = ? AND semester = ?

Page 28 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

• What is a covering index? Write a query and index that demonstrates covering index
behavior (look for "Using index" in EXPLAIN Extra).
• When should you NOT add an index? List three scenarios and explain the reasoning.
• Use SHOW INDEXES to list all indexes on the students table. Drop any redundant
indexes.

Chapter 10: Transactions & ACID Properties

10.1 What is a Transaction?
A transaction is a sequence of one or more SQL operations treated as a single logical unit of
work. Either ALL operations in the transaction complete successfully (COMMIT), or NONE of
them take effect (ROLLBACK). The classic example is a bank transfer: debit ₹5000 from
account A, credit ₹5000 to account B. If the debit succeeds but the power fails before the credit,
₹5000 has vanished. A transaction ensures this cannot happen — either both operations
complete, or neither does.
In MySQL, transactions are supported by InnoDB (the default engine since MySQL 5.5). The
older MyISAM engine does not support transactions — one of the main reasons it is now
deprecated for most uses. Every DML statement in InnoDB is implicitly in a transaction. By
default, MySQL is in autocommit mode: each statement is its own transaction, auto-committed
immediately. To use multi-statement transactions, either disable autocommit or explicitly use
BEGIN...COMMIT.

10.2 ACID Properties

ACID is the set of properties that guarantee reliable transaction processing. Atomicity: the
transaction is atomic — all or nothing. There is no partial success. If any statement fails, the
entire transaction is rolled back. Consistency: the database moves from one valid state to
another valid state. Constraints (NOT NULL, CHECK, FOREIGN KEY) are enforced at commit
time. A transfer that would make a balance negative is rejected if a CHECK constraint exists.
Isolation: concurrent transactions do not see each other's uncommitted changes. Two bank
transfers happening simultaneously do not interfere. Durability: once committed, changes
survive system crashes. InnoDB uses write-ahead logging (redo log) to ensure durability —
committed changes are written to disk even before the data pages are updated.

10.3 Isolation Levels and Concurrency Problems

Transaction isolation has four levels, each providing stronger guarantees at the cost of reduced
concurrency. READ UNCOMMITTED allows dirty reads — you can read another transaction's
uncommitted changes. READ COMMITTED prevents dirty reads but allows non-repeatable
reads (a row you read twice in the same transaction can return different values if another
transaction commits between your reads). REPEATABLE READ (MySQL InnoDB default)
prevents non-repeatable reads but allows phantom reads (a range query can return different
rows if another transaction inserts in that range). SERIALIZABLE prevents all anomalies by
effectively serializing transactions — maximum safety, minimum concurrency.
Deadlock occurs when two transactions each hold a lock that the other needs. Transaction A
locks row 1 and waits for row 2. Transaction B locks row 2 and waits for row 1. Neither can
proceed. MySQL's InnoDB engine detects deadlocks automatically and kills the transaction that

Page 29 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

has done less work, rolling it back. The surviving transaction can then complete. Application
code must handle the deadlock error (error 1213) by retrying the transaction.

10.4 Code Example: Bank Transfer Simulation

USE mitsacademy;

-- ─── SETUP: fee accounts table for transaction demo

───────────────────────────
CREATE TABLE IF NOT EXISTS fee_accounts (
account_id INT NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL,
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
CHECK (balance >= 0.00), -- Cannot go negative
(Consistency)
PRIMARY KEY (account_id),
FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE
) ENGINE=InnoDB; -- MUST be InnoDB for
transactions

INSERT INTO fee_accounts (student_id, balance) VALUES (1, 50000.00), (2,

30000.00);

-- ─── FEE TRANSFER TRANSACTION

─────────────────────────────────────────────────
START TRANSACTION; -- BEGIN also works

-- Check balance before debit (SELECT ... FOR UPDATE locks the row)
SELECT balance FROM fee_accounts WHERE student_id = 1 FOR UPDATE;

-- Debit from student 1

UPDATE fee_accounts SET balance = balance - 5000.00 WHERE student_id = 1;

-- Credit to student 2
UPDATE fee_accounts SET balance = balance + 5000.00 WHERE student_id = 2;

-- Verify both changes look correct before committing

SELECT student_id, balance FROM fee_accounts WHERE student_id IN (1, 2);

COMMIT; -- Make changes permanent

(Durability)

-- Verify committed state

SELECT student_id, balance FROM fee_accounts;

-- ─── ROLLBACK EXAMPLE: something went wrong

───────────────────────────────────
START TRANSACTION;

UPDATE fee_accounts SET balance = balance - 10000.00 WHERE student_id = 2;

-- Simulate an error: student 2 balance would go negative

-- In a stored procedure, you'd check and ROLLBACK
-- For now, manually check:
SELECT balance FROM fee_accounts WHERE student_id = 2;
-- If balance < 0 after debit → ROLLBACK

ROLLBACK; -- Undo ALL changes since

START TRANSACTION

-- Balance is restored

Page 30 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

SELECT student_id, balance FROM fee_accounts;

-- ─── SAVEPOINT — partial rollback

─────────────────────────────────────────────
START TRANSACTION;

UPDATE fee_accounts SET balance = balance - 1000 WHERE student_id = 1;

SAVEPOINT after_debit; -- Mark a restore point

UPDATE fee_accounts SET balance = balance + 999 WHERE student_id = 2; --

Wrong amount!

-- Undo only to the savepoint (keep the debit, undo the wrong credit)
ROLLBACK TO SAVEPOINT after_debit;

-- Now do the correct credit

UPDATE fee_accounts SET balance = balance + 1000 WHERE student_id = 2;

COMMIT;

-- ─── ISOLATION LEVEL DEMONSTRATION

───────────────────────────────────────────
-- In Session 1 (Connection 1):
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- Read student 1 balance (will see uncommitted changes from session 2!)
SELECT balance FROM fee_accounts WHERE student_id = 1;

-- In Session 2 (Connection 2) — simultaneously:

-- START TRANSACTION;
-- UPDATE fee_accounts SET balance = balance - 9999999 WHERE student_id = 1;
-- (Do NOT commit) — session 1 with READ UNCOMMITTED will see this dirty read!
-- ROLLBACK; — session 2 rolls back

-- Restore proper isolation level

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- InnoDB default

10.5 Practice Exercises

• Write a transaction that transfers scholarship money to the top 3 students by CGPA.
Rollback if any student account does not exist.
• What is the difference between COMMIT and ROLLBACK? Can you ROLLBACK after a
COMMIT?
• Explain why MyISAM is not suitable for banking applications.
• Write a scenario that would cause a deadlock between two transactions on the
fee_accounts table. How would MySQL resolve it?
• What isolation level would you use for a reporting query that reads large amounts of data
and must not be affected by concurrent updates? Explain your reasoning.

Page 31 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

Chapter 11: Views, Stored Procedures & Triggers

11.1 Views — Virtual Tables
A view is a named SELECT query stored in the database. When you query a view, MySQL
executes the underlying SELECT and returns the result as if it were a real table. Views serve
three important purposes. First, they simplify complex queries — a 5-table JOIN with multiple
conditions can be packaged as a view, allowing application code to simply SELECT FROM
view_name. Second, they provide security — you can give a user access to a view that shows
only certain columns (hiding salary, hiding personal data) while preventing direct table access.
Third, they provide a stable interface — if the underlying table structure changes, you can
update the view definition without changing application code.
A view is updatable if MySQL can determine exactly which base table row to modify. Simple
views on a single table with no aggregates, DISTINCT, or GROUP BY are usually updatable.
WITH CHECK OPTION ensures that any INSERT or UPDATE through the view satisfies the
view's WHERE condition — you cannot insert a row through a "high-GPA students" view that
would have a low GPA.

11.2 Stored Procedures

A stored procedure is a named block of SQL code stored in the database and executed with a
CALL statement. Procedures can contain variables (DECLARE x INT DEFAULT 0), conditional
logic (IF/ELSEIF/ELSE/END IF), loops (WHILE/REPEAT/LOOP), and cursors (to iterate row by
row). Procedures accept IN parameters (input), OUT parameters (output), and INOUT
parameters (both). Business logic in stored procedures runs inside the database server —
network round-trips are reduced and complex operations are atomic.
A stored function is similar to a procedure but must return a single value and can be used
directly in SQL expressions like a built-in function. Use functions for computations
(letter_grade(marks) → "A+"), and procedures for operations (enroll_student(student_id,
course_id)). The DELIMITER command is necessary when writing procedures in the MySQL
client because semicolons within the procedure body would terminate the CREATE
PROCEDURE statement prematurely — you change the delimiter to // or $$ for the duration of
the procedure definition.

11.3 Triggers
A trigger is a stored program that automatically executes when a specific event (INSERT,
UPDATE, or DELETE) occurs on a table. Triggers can fire BEFORE the event (to validate or
modify data before it is written) or AFTER the event (to perform side effects like logging or
updating summary tables). Inside a trigger, NEW refers to the new row values (available in
INSERT and UPDATE triggers) and OLD refers to the old row values (available in UPDATE and
DELETE triggers).
Triggers are powerful but require discipline. Poorly designed triggers can cause cascading
effects, slow down DML operations, and make debugging difficult because behavior is implicit
(not visible in application code). Common legitimate uses: audit logging (record who changed
what when), enforcing business rules that cannot be expressed as constraints (e.g., enrollment
only allowed if student has paid fees), and maintaining derived or summary data (auto-update a
GPA table when marks are inserted).

Page 32 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

11.4 Code Example: View, Procedure, and Trigger

USE mitsacademy;

-- ─── VIEWS
────────────────────────────────────────────────────────────────────

-- View: student report card (hides internal implementation details)

CREATE OR REPLACE VIEW vw_student_report_card AS
SELECT s.student_id,
s.student_name,
d.dept_name,
d.dept_code,
s.admission_year,
[Link],
COUNT(e.enrollment_id) AS courses_enrolled,
ROUND(AVG(e.marks_obtained), 2) AS avg_marks,
CASE
WHEN [Link] >= 9.0 THEN 'Outstanding'
WHEN [Link] >= 8.0 THEN 'Excellent'
WHEN [Link] >= 7.0 THEN 'Good'
WHEN [Link] >= 6.0 THEN 'Average'
ELSE 'Needs Improvement'
END AS performance_band
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.student_name, d.dept_name, d.dept_code,
s.admission_year, [Link];

-- Query the view like a table

SELECT * FROM vw_student_report_card WHERE performance_band = 'Excellent';

-- View: hide sensitive salary info from faculty public profile

CREATE OR REPLACE VIEW vw_faculty_public AS
SELECT faculty_id,
faculty_name,
designation,
dept_id
FROM faculty; -- salary column NOT included
-- GRANT SELECT ON mitsacademy.vw_faculty_public TO 'student_user'@'%';

-- ─── STORED PROCEDURE: enroll student with validation

─────────────────────────
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS sp_enroll_student(

IN p_student_id INT,
IN p_course_id INT,
IN p_semester TINYINT,
OUT p_result VARCHAR(200) -- OUT parameter: returns result
message
)
BEGIN
DECLARE v_student_count INT DEFAULT 0;
DECLARE v_course_count INT DEFAULT 0;
DECLARE v_already_enrolled INT DEFAULT 0;
DECLARE v_dept_id INT;
DECLARE v_course_dept INT;

-- Check student exists

Page 33 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

SELECT COUNT(*) INTO v_student_count FROM students WHERE student_id =

p_student_id AND is_enrolled = TRUE;
IF v_student_count = 0 THEN
SET p_result = 'ERROR: Student not found or not active';
LEAVE sp_enroll_student; -- Exit the procedure
END IF;

-- Check course exists

SELECT COUNT(*), dept_id INTO v_course_count, v_course_dept
FROM courses WHERE course_id = p_course_id;
IF v_course_count = 0 THEN
SET p_result = 'ERROR: Course not found';
LEAVE sp_enroll_student;
END IF;

-- Check not already enrolled

SELECT COUNT(*) INTO v_already_enrolled
FROM enrollments
WHERE student_id = p_student_id AND course_id = p_course_id;

IF v_already_enrolled > 0 THEN

SET p_result = 'ERROR: Student already enrolled in this course';
LEAVE sp_enroll_student;
END IF;

-- All validations passed — enroll the student

START TRANSACTION;
INSERT INTO enrollments (student_id, course_id, semester, enrollment_date)
VALUES (p_student_id, p_course_id, p_semester, CURDATE());
COMMIT;

SET p_result = CONCAT('SUCCESS: Student ', p_student_id, ' enrolled in

course ', p_course_id);

END //

DELIMITER ;

-- Call the procedure

CALL sp_enroll_student(5, 3, 3, @result);
SELECT @result; -- Check the OUT parameter

-- ─── STORED FUNCTION: calculate letter grade

──────────────────────────────────
DELIMITER //

CREATE FUNCTION IF NOT EXISTS fn_get_grade(p_marks DECIMAL(5,2))

RETURNS CHAR(2)
DETERMINISTIC -- Same input always gives same
output
BEGIN
RETURN CASE
WHEN p_marks >= 90 THEN 'A+'
WHEN p_marks >= 80 THEN 'A'
WHEN p_marks >= 70 THEN 'B'
WHEN p_marks >= 60 THEN 'C'
WHEN p_marks >= 50 THEN 'D'
WHEN p_marks IS NULL THEN NULL
ELSE 'F'
END;
END //

Page 34 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

DELIMITER ;

-- Use the function in a SELECT like a built-in function

SELECT student_id, marks_obtained, fn_get_grade(marks_obtained) AS letter_grade
FROM enrollments WHERE marks_obtained IS NOT NULL;

-- ─── TRIGGERS
────────────────────────────────────────────────────────────────

-- Audit log table

CREATE TABLE IF NOT EXISTS student_audit_log (
log_id INT NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL,
action_type ENUM('INSERT','UPDATE','DELETE') NOT NULL,
old_cgpa DECIMAL(4,2) DEFAULT NULL,
new_cgpa DECIMAL(4,2) DEFAULT NULL,
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
changed_by VARCHAR(100) DEFAULT USER(), -- Who made the change
PRIMARY KEY (log_id)
) ENGINE=InnoDB;

DELIMITER //

-- AFTER UPDATE trigger: log CGPA changes

CREATE TRIGGER IF NOT EXISTS trg_student_cgpa_audit
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
IF [Link] != [Link] THEN -- Only log if CGPA actually changed
INSERT INTO student_audit_log (student_id, action_type, old_cgpa,
new_cgpa)
VALUES (NEW.student_id, 'UPDATE', [Link], [Link]);
END IF;
END //

-- AFTER INSERT trigger on enrollments: auto-recalculate student CGPA

CREATE TRIGGER IF NOT EXISTS trg_recalculate_cgpa
AFTER UPDATE ON enrollments
FOR EACH ROW
BEGIN
IF NEW.marks_obtained IS NOT NULL AND (OLD.marks_obtained IS NULL OR
OLD.marks_obtained != NEW.marks_obtained) THEN
UPDATE students
SET cgpa = (
SELECT ROUND(AVG(e2.marks_obtained / 10.0), 2) -- Convert marks to
10-point scale
FROM enrollments e2
WHERE e2.student_id = NEW.student_id
AND e2.marks_obtained IS NOT NULL
)
WHERE student_id = NEW.student_id;
END IF;
END //

DELIMITER ;

-- Test the trigger

UPDATE enrollments SET marks_obtained = 92.0, grade = 'A+' WHERE student_id = 1
AND course_id = 1;
SELECT * FROM student_audit_log; -- Should show the CGPA change

Page 35 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

SELECT student_id, cgpa FROM students WHERE student_id = 1; -- CGPA auto-

updated

11.5 Practice Exercises

• Create a view vw_top_students that shows students with CGPA above 8.5, including their
department name.
• Write a stored procedure sp_calculate_result that takes student_id and semester, and
marks the student as PASS or FAIL based on whether all marks are above 40.
• Create a BEFORE INSERT trigger on the enrollments table that prevents enrollment if the
student's is_enrolled flag is FALSE.
• What is the difference between a stored procedure and a stored function? When would
you use each?
• Create a DELETE trigger on students that logs deleted student information to a
students_deleted_log table before deletion.

Chapter 12: Advanced SQL & Capstone Project

12.1 Window Functions — Analytics Without GROUP BY
Window functions perform calculations across a set of rows related to the current row, without
collapsing those rows into a single group (unlike aggregate functions with GROUP BY). The
OVER() clause defines the "window" — the set of rows to consider. PARTITION BY divides
rows into groups (like GROUP BY but keeps all rows). ORDER BY within OVER() defines the
order within each partition. Window functions are evaluated after WHERE, GROUP BY, and
HAVING — they operate on the result set, not the raw data.
ROW_NUMBER() assigns a unique sequential integer to each row within the partition. RANK()
assigns the same rank to tied rows but skips ranks after ties (1, 2, 2, 4). DENSE_RANK() does
not skip ranks (1, 2, 2, 3). NTILE(n) divides rows into n roughly equal groups (quartiles, deciles).
LAG(column, offset) accesses the value from a previous row without a self-join. LEAD(column,
offset) accesses the value from a following row. FIRST_VALUE/LAST_VALUE retrieve the
first/last value in the window. These functions enable sophisticated analytics that previously
required multiple subqueries or application-level code.

12.2 JSON Data Type

MySQL 5.7+ supports a native JSON data type that stores and validates JSON documents. The
JSON type is not just a TEXT column with JSON inside — MySQL parses and stores JSON in
an optimized binary format and provides functions to query and manipulate it. JSON is
appropriate when different records legitimately have different attributes (product catalog where a
laptop has CPU/RAM specs but a shirt has size/color), or when storing semi-structured data that
does not fit neatly into relational columns.
The -> operator extracts a JSON value (equivalent to JSON_EXTRACT). JSON_SET modifies a
value. JSON_ARRAYAGG aggregates rows into a JSON array. JSON_OBJECTAGG creates a
JSON object from key-value pairs. Generated columns can be created from JSON fields and
indexed, allowing WHERE conditions on JSON properties to use indexes — avoiding the
performance trap of JSON columns that must be fully scanned.

Page 36 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

12.3 Query Optimization and the Slow Query Log

The MySQL slow query log records queries that take longer than a configurable threshold
(long_query_time, default 10 seconds). By setting long_query_time = 1 or even 0.1, you capture
slow queries for analysis. The mysqldumpslow and pt-query-digest tools analyze slow query
logs and group similar queries to show which query patterns are consuming the most time.
EXPLAIN ANALYZE (MySQL 8.0.18+) combines EXPLAIN's plan with actual execution
statistics — actual rows processed, actual time taken, and number of loops — letting you see
where your query estimation and reality diverge.

12.4 MySQL with [Link]

The mysql2 package for [Link] is the standard way to connect [Link] applications to MySQL.
It supports Promises and async/await natively. Connection pools are essential for production
applications — creating a new MySQL connection for every request is slow (involves TCP
handshake, authentication, session setup). A pool maintains a set of pre-established
connections and lends them to requests. Always use parameterized queries (prepared
statements) with placeholders (?) — never concatenate user input into SQL strings. String
concatenation is the root cause of SQL injection vulnerabilities, where malicious input can
modify the query structure.

12.5 Capstone: Complete MITS Academy System

USE mitsacademy;

-- ─── WINDOW FUNCTIONS: Rankings and Analytics

─────────────────────────────────

-- Rank students within each department by CGPA

SELECT s.student_name,
d.dept_name,
[Link],
RANK() OVER (PARTITION BY s.dept_id ORDER BY [Link] DESC) AS
dept_rank,
DENSE_RANK() OVER (PARTITION BY s.dept_id ORDER BY [Link] DESC) AS
dense_rank,
ROW_NUMBER() OVER (PARTITION BY s.dept_id ORDER BY [Link] DESC) AS
row_num,
NTILE(4) OVER (PARTITION BY s.dept_id ORDER BY [Link] DESC) AS
quartile,
ROUND(AVG([Link]) OVER (PARTITION BY s.dept_id), 2) AS
dept_avg,
ROUND([Link] - AVG([Link]) OVER (PARTITION BY s.dept_id), 2) AS
diff_from_avg
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
ORDER BY d.dept_name, dept_rank;

-- LAG / LEAD: compare each student's CGPA with the next/previous student in
dept
SELECT student_name,
dept_id,
cgpa,
LAG(cgpa, 1) OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS
higher_cgpa,
LEAD(cgpa, 1) OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS
lower_cgpa,

Page 37 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

cgpa - LAG(cgpa, 1) OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS

gap_from_above
FROM students
ORDER BY dept_id, cgpa DESC;

-- Running total of enrollments per day (cumulative count)

SELECT enrollment_date,
COUNT(*) AS daily_enrollments,
SUM(COUNT(*)) OVER (ORDER BY enrollment_date) AS running_total
FROM enrollments
GROUP BY enrollment_date
ORDER BY enrollment_date;

-- ─── TOP-N PER GROUP using CTE + window function

─────────────────────────────
WITH ranked_students AS (
SELECT student_name,
dept_id,
cgpa,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS rn
FROM students
)
SELECT d.dept_name,
rs.student_name AS 'Top Student',
[Link]
FROM ranked_students rs
JOIN departments d ON rs.dept_id = d.dept_id
WHERE [Link] = 1; -- Only rank 1 from each department

-- ─── JSON DATA TYPE

───────────────────────────────────────────────────────────
-- Add a JSON column to store flexible course metadata
ALTER TABLE courses ADD COLUMN course_metadata JSON DEFAULT NULL;

-- Insert JSON data

UPDATE courses
SET course_metadata = JSON_OBJECT(
'lab_required', TRUE,
'textbooks', JSON_ARRAY('Database System Concepts', 'MySQL Reference
Manual'),
'software', JSON_ARRAY('MySQL', 'MySQL Workbench'),
'difficulty', 'Intermediate'
)
WHERE course_code = 'CS301';

-- Query JSON data using -> operator

SELECT course_name,
course_metadata -> '$.difficulty' AS difficulty,
course_metadata -> '$.lab_required' AS lab_required,
JSON_EXTRACT(course_metadata, '$.textbooks[0]') AS primary_textbook
FROM courses
WHERE course_metadata IS NOT NULL;

-- Aggregate enrollments as JSON array

SELECT s.student_name,
JSON_ARRAYAGG(
JSON_OBJECT('course', c.course_name, 'marks', e.marks_obtained,
'grade', [Link])
) AS enrolled_courses
FROM students s
JOIN enrollments e ON s.student_id = e.student_id

Page 38 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

JOIN courses c ON e.course_id = c.course_id

GROUP BY s.student_id, s.student_name;

-- ─── COMPLETE CAPSTONE: MITS Academy result processing procedure

───────────────
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS sp_generate_semester_results(

IN p_semester TINYINT
)
BEGIN
-- Temp table for results
CREATE TEMPORARY TABLE IF NOT EXISTS temp_results AS
SELECT s.student_id,
s.student_name,
d.dept_name,
COUNT(e.enrollment_id) AS
courses_taken,
SUM(CASE WHEN e.marks_obtained >= 40 THEN 1 ELSE 0 END) AS
courses_passed,
ROUND(AVG(e.marks_obtained), 2) AS avg_marks,
MIN(e.marks_obtained) AS min_marks,
MAX(e.marks_obtained) AS max_marks,
CASE WHEN MIN(e.marks_obtained) >= 40 THEN 'PASS' ELSE 'FAIL' END
AS result,
RANK() OVER (PARTITION BY s.dept_id ORDER BY AVG(e.marks_obtained)
DESC) AS dept_rank
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
JOIN enrollments e ON s.student_id = e.student_id
WHERE [Link] = p_semester
AND e.marks_obtained IS NOT NULL
GROUP BY s.student_id, s.student_name, d.dept_name;

-- Output the results

SELECT student_id,
student_name,
dept_name AS department,
courses_taken,
courses_passed,
avg_marks AS 'Average Marks',
min_marks AS 'Lowest Mark',
max_marks AS 'Highest Mark',
result,
dept_rank AS 'Rank in Dept'
FROM temp_results
ORDER BY dept_name, dept_rank;

DROP TEMPORARY TABLE IF EXISTS temp_results;

END //

DELIMITER ;

-- Generate results for semester 3

CALL sp_generate_semester_results(3);

-- ─── [Link] INTEGRATION

─────────────────────────────────────────────────────
-- The following is shown as a comment demonstrating [Link] usage pattern
/*
// npm install mysql2

Page 39 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

const mysql = require('mysql2/promise');

// Create a connection pool (reuse connections — don't create one per request)
const pool = [Link]({
host: 'localhost',
user: 'mitsapp',
password: 'SecurePassword123!',
database: 'mitsacademy',
connectionLimit: 10, // Max 10 simultaneous connections
waitForConnections: true
});

// SAFE: parameterized query — user input cannot modify SQL structure

async function getStudentsByDept(deptId) {
const [rows] = await [Link](
'SELECT student_id, student_name, cgpa FROM students WHERE dept_id = ?
AND is_enrolled = TRUE',
[deptId] // ? placeholder replaced safely by
mysql2
);
return rows;
}

// DANGEROUS (never do this):

// const q = `SELECT * FROM students WHERE dept_id = ${deptId}`; // SQL
injection risk!

// Use a transaction in [Link]

async function transferFees(fromId, toId, amount) {
const conn = await [Link]();
try {
await [Link]();
await [Link]('UPDATE fee_accounts SET balance = balance - ? WHERE
student_id = ?', [amount, fromId]);
await [Link]('UPDATE fee_accounts SET balance = balance + ? WHERE
student_id = ?', [amount, toId]);
await [Link]();
} catch (err) {
await [Link]();
throw err;
} finally {
[Link](); // Return connection to pool
}
}
*/

-- ─── PERFORMANCE: EXPLAIN ANALYZE

────────────────────────────────────────────
EXPLAIN ANALYZE
WITH dept_avg AS (
SELECT dept_id, ROUND(AVG(cgpa), 2) AS avg_cgpa
FROM students
GROUP BY dept_id
)
SELECT s.student_name, d.dept_name, [Link], da.avg_cgpa,
RANK() OVER (PARTITION BY s.dept_id ORDER BY [Link] DESC) AS
rank_in_dept
FROM students s
JOIN departments d ON s.dept_id = d.dept_id
JOIN dept_avg da ON s.dept_id = da.dept_id

Page 40 | MITS Academy | [Link]

MITS Academy — SQL & Database Management

ORDER BY d.dept_name, rank_in_dept;

-- ─── PARTITIONING OVERVIEW (concept — not executed here)

─────────────────────
-- For a large enrollments archive table, RANGE partition by year:
/*
CREATE TABLE enrollments_archive (
enrollment_id INT NOT NULL,
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE NOT NULL,
marks_obtained DECIMAL(5,2)
)
PARTITION BY RANGE (YEAR(enrollment_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- Query for 2022 data only reads the p2022 partition, not the entire table
SELECT * FROM enrollments_archive WHERE YEAR(enrollment_date) = 2022;
*/

12.6 Practice Exercises — Capstone

• Use DENSE_RANK() to find students ranked in the top 3 by CGPA across the entire
university (not per department).
• Write a window function query to compute a 2-row moving average of enrollment counts
by date.
• Create a JSON column in the students table to store "hobbies" as a JSON array. Insert
data for 5 students and query students who have "coding" as a hobby.
• Write a [Link] function (as a comment in SQL) that enrolls a student using the
sp_enroll_student procedure and handles the error case.
• Identify one slow query from the chapter examples and optimize it by adding an
appropriate index. Use EXPLAIN ANALYZE before and after to measure the
improvement.
• Design and create a complete "attendance" system as an extension to MITS Academy:
table structure, a stored procedure to mark attendance, a view for attendance
percentage, and a trigger that flags students below 75% attendance.

Page 41 | MITS Academy | [Link]