Introduction to Databases

In this module you will learn what data and databases really are, how DBMS and RDBMS work, where MySQL fits into the modern data-analytics world, and how to install and connect to MySQL on Windows, Linux, and Mac.

Learning Objectives

• Explain the difference between data, information, and a database
• Compare DBMS vs RDBMS and understand why RDBMS dominates business analytics
• Identify SQL vs NoSQL use-cases with real business examples
• Install MySQL Server + Workbench on any operating system
• Successfully connect to a MySQL database from the command line and Workbench

1.1 What is Data?

Data is any raw fact, figure, or observation that has not yet been processed into a meaningful form. A phone number, a product price, a customer's name, a timestamp of a website click — all of these are data. On its own, data does not tell a story.

Information is what you get when you organize and process data so it becomes useful for decision-making. For example, “50,000” is data. “Our Samantus YouTube channel gained 50,000 views this month” is information.

1.2 What is a Database?

A database is an organized, electronically stored collection of data that can be easily accessed, managed, and updated. Instead of storing customer records in scattered Excel files, a database keeps everything structured, searchable, and consistent — this is exactly why every serious company (Amazon, Swiggy, Flipkart) runs on databases instead of spreadsheets.

1.3 DBMS — Database Management System

A DBMS is the software layer that lets users and applications create, read, update, and delete data inside a database, while also handling security, backup, and multi-user access. MySQL, Oracle, PostgreSQL, and SQL Server are all examples of DBMS software.

1.4 RDBMS — Relational Database Management System

An RDBMS is a DBMS that stores data in tables (rows and columns) and lets you define relationships between tables using keys. MySQL is an RDBMS. This relational model is what makes complex business reporting — like joining a Customers table with an Orders table — possible and fast.

1.5 SQL vs NoSQL

1.6 History of MySQL

MySQL was first released in 1995 by MySQL AB (Sweden), acquired by Sun Microsystems in 2008, and is now owned and actively developed by Oracle Corporation. It remains one of the most widely used open-source RDBMS platforms in the world, powering everything from WordPress websites to large-scale analytics pipelines.

1.7 Key Features of MySQL

• Free and open-source (Community Edition)
• Cross-platform: runs on Windows, Linux, macOS
• High performance with strong indexing support
• ACID-compliant transactions (with InnoDB engine)
• Huge community + extensive documentation
• Seamless integration with BI tools (Power BI, Tableau, Metabase)

1.8 Real-World Applications of MySQL

1.9 Installing MySQL

Windows

• Download MySQL Installer from the official MySQL website
• Choose ‘Developer Default’ setup type
• Install MySQL Server + MySQL Workbench + Sample Databases
• Set a strong root password and remember it
• Finish setup and let the installer start the MySQL service

macOS

brew install mysql
brew services start mysql
mysql_secure_installation

Linux (Ubuntu/Debian)

sudo apt update
sudo apt install mysql-server -y
sudo systemctl start mysql
sudo systemctl enable mysql
sudo mysql_secure_installation

1.10 Connecting to MySQL

Command Line

mysql -u root -p
-- Enter your password when prompted
SHOW DATABASES;

MySQL Workbench

Open Workbench → click the ‘+’ next to MySQL Connections → enter Connection Name, Hostname (127.0.0.1), Port (3306), Username (root) → Test Connection → OK.

✅ Key Takeaways

• Data becomes valuable only when turned into information — this is the analyst's core job.
• A DBMS manages data; an RDBMS additionally organizes it into related tables.
• SQL databases (like MySQL) are the default choice for structured, relationship-heavy, transaction-critical business data.
• MySQL is free, fast, widely adopted, and integrates with almost every modern BI/reporting tool.
• Before writing any SQL, you must have MySQL Server running and a working client connection (CLI or Workbench).

🎯 Interview Questions

Q1. What is the difference between DBMS and RDBMS?

DBMS stores data as files/records with no mandatory relationships between them; RDBMS stores data in tables and enforces relationships via keys (primary/foreign), enabling joins and referential integrity.

Q2. Why is MySQL preferred for analytics projects over spreadsheets?

MySQL handles much larger volumes, enforces data integrity, supports concurrent multi-user access, and allows complex querying (joins, aggregations, window functions) that spreadsheets cannot do efficiently at scale.

Q3. Name two situations where NoSQL would beat MySQL.

Storing unstructured/rapidly changing data such as chat messages or IoT sensor streams, and systems needing massive horizontal scaling across many servers.

📝 Practice Exercise

1. Install MySQL Server + Workbench on your own machine and take a screenshot of a successful connection.
2. Write 3 real-life examples (from your own city/business) where you would need a database instead of Excel.
3. List 2 companies you use daily and guess whether their core data most likely lives in SQL or NoSQL, with reasoning.

📚 Mini Assignment

Prepare a one-page comparison sheet: ‘SQL vs NoSQL for a Food Delivery App’ covering data structure, scaling needs, and one real risk of choosing the wrong database type. Submit as PDF.

❓ Chapter Quiz (5 Questions)

1. RDBMS organizes data using: (a) Files (b) Tables with relationships (c) Key-value pairs (d) Graphs

2. Which of these is NOT an RDBMS? (a) MySQL (b) PostgreSQL (c) MongoDB (d) Oracle

3. The default MySQL port number is: (a) 8080 (b) 3306 (c) 5432 (d) 27017

4. Which company currently owns MySQL? (a) Sun Microsystems (b) Oracle (c) Microsoft (d) IBM

5. True/False: NoSQL databases always use a fixed schema.

Answer Key: 1-b, 2-c, 3-b, 4-b, 5-False

End of Module 1 sample. Modules 2-25, 300+ exercises, real company case studies, 300 interview questions, cheat sheets, and the capstone project will follow in the same visual standard across Phases 2-5.

2.1 What is SQL?

SQL (Structured Query Language) is the standard language used to create, read, update, and delete data in a relational database. Every RDBMS (MySQL, PostgreSQL, SQL Server) understands SQL, with small dialect differences. Once you master MySQL's SQL, switching to another RDBMS is easy.

2.2 SQL Statement Categories

2.3 Basic SQL Syntax

Every SQL statement ends with a semicolon (;). Keywords are not case-sensitive, but writing them in UPPERCASE (SELECT, FROM, WHERE) is an industry convention that makes queries easier to read.

SELECT first_name, last_name
FROM students
WHERE course = 'Digital Marketing';

2.4 Comments in SQL

-- This is a single-line comment

/* This is a
   multi-line comment */

2.5 Naming Rules

• Names can contain letters, numbers, and underscores (student_name, not student-name)
• Cannot start with a number (avoid 1st_attempt)
• Avoid MySQL reserved words as names (e.g. don't name a column 'order' or 'group')
• Use clear, descriptive names: use 'enrollment_date' instead of 'ed'

2.6 MySQL Data Types (Most Used)

2.7 Constraints (Introduction)

Constraints are rules enforced on columns to keep data accurate and reliable. The most common are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT — these are covered in full detail in Module 13, but you will start using PRIMARY KEY and NOT NULL from the very next module.

✅ Key Takeaways

• SQL statements fall into 5 categories: DDL, DML, DQL, DCL, TCL.
• Every statement ends with a semicolon; keywords are conventionally written in UPPERCASE.
• Choosing the correct data type (INT vs VARCHAR vs DECIMAL) is critical for accurate analytics later.
• Constraints protect data quality and are the foundation of reliable reporting.

🎯 Interview Questions

Q1. What are the 5 categories of SQL statements?

DDL (structure), DML (data changes), DQL (retrieval), DCL (permissions), and TCL (transactions) — each serves a distinct purpose in managing a database.

Q2. Why use DECIMAL instead of FLOAT for storing currency?

DECIMAL stores exact values with no rounding error, which is essential for financial figures; FLOAT can introduce tiny precision errors that compound across large datasets.

📝 Practice Exercise

1. Classify these commands into DDL/DML/DQL: CREATE TABLE, INSERT, SELECT, DELETE, ALTER TABLE.
2. Design column names and data types for a 'YouTube_Videos' table (title, views, upload_date, is_monetized).
3. Write one single-line and one multi-line comment explaining a sample query.

📚 Mini Assignment

Design a full column list (with data types) for a 'Course_Enrollments' table for Samantus Web Training Institute, covering student name, course, fee, enrollment date, and payment status. Justify each data type choice in one line.

❓ Chapter Quiz

1. Which category does ALTER TABLE belong to? (a) DML (b) DDL (c) DQL (d) DCL

2. Best data type for storing a course fee of ₹15,999.50: (a) INT (b) VARCHAR (c) DECIMAL(10,2) (d) BOOLEAN

3. SQL comments for multiple lines use: (a) // (b) # # (c) /* */ (d)

Answer Key: 1-b, 2-c, 3-c

3.1 CREATE DATABASE

CREATE DATABASE samantus_institute;
SHOW DATABASES;

This creates a new, empty database. Always check for naming clashes first with SHOW DATABASES, or use the safe form below.

CREATE DATABASE IF NOT EXISTS samantus_institute;

3.2 USE DATABASE

USE samantus_institute;

This tells MySQL which database all following commands should apply to. Forgetting to USE the correct database is one of the most common beginner mistakes.

3.3 ALTER DATABASE

ALTER DATABASE samantus_institute CHARACTER SET utf8mb4;

ALTER DATABASE changes database-level properties such as character set and collation. It does not rename the database (MySQL has no direct RENAME DATABASE command).

3.4 DROP DATABASE

DROP DATABASE IF EXISTS old_test_db;

✅ Key Takeaways

• CREATE DATABASE sets up a new container; USE selects which one you're working in.
• ALTER DATABASE changes properties like character set, not the name itself.
• DROP DATABASE is permanent and irreversible — always confirm and back up first.

🎯 Interview Questions

Q1. What happens to the tables inside a database when you run DROP DATABASE?

All tables, their data, and their structures inside that database are permanently deleted along with the database itself — there is no undo without a backup.

Q2. How do you avoid an error if a database might already exist?

Use 'CREATE DATABASE IF NOT EXISTS db_name;' so the statement runs safely even on repeat execution.

📝 Practice Exercise

1. Create a database named 'samantus_marketing' and confirm it exists using SHOW DATABASES.
2. Switch into it using USE and confirm with SELECT DATABASE();.
3. Safely drop a test database using IF EXISTS.

📚 Mini Assignment

Write a complete script that creates a 'samantus_agency' database, sets its character set to utf8mb4, and safely drops any leftover 'temp_test' database before doing so.

❓ Chapter Quiz

1. Which command selects the active database? (a) SELECT (b) USE (c) SHOW (d) SET

2. DROP DATABASE removes: (a) Only data (b) Only structure (c) Both structure and data (d) Nothing

Answer Key: 1-b, 2-c

4.1 CREATE TABLE

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    course VARCHAR(50),
    fee DECIMAL(10,2),
    enrollment_date DATE DEFAULT (CURRENT_DATE)
);

4.2 DESCRIBE / SHOW COLUMNS

DESCRIBE students;
-- or
SHOW COLUMNS FROM students;

4.3 ALTER TABLE

4.4 RENAME TABLE

RENAME TABLE students TO enrolled_students;

4.5 TRUNCATE vs DROP vs DELETE

✅ Key Takeaways

• CREATE TABLE defines structure once; ALTER TABLE lets you evolve it safely over time.
• DESCRIBE / SHOW COLUMNS quickly reveal a table's structure and constraints.
• DROP removes structure + data; TRUNCATE removes data only (all rows); DELETE removes data selectively and supports WHERE.

🎯 Interview Questions

Q1. Does TRUNCATE reset AUTO_INCREMENT?

Yes, in MySQL's default InnoDB behavior TRUNCATE resets the AUTO_INCREMENT counter back to its starting value, unlike DELETE which leaves it untouched.

Q2. Can you undo a DROP TABLE?

No, once committed there is no built-in undo — recovery is only possible from a prior backup or binary log, which is why DROP TABLE should always be used with extreme caution.

📝 Practice Exercise

1. Create a 'courses' table with course_id, course_name, duration_weeks, and price.
2. Add a new column 'is_active' (BOOLEAN) using ALTER TABLE.
3. Practice the difference: TRUNCATE the table, then try to recover the data (observe it's gone).

📚 Mini Assignment

Design and create a full 'agency_clients' table for Samantus Web Services with at least 6 relevant columns, then write 3 ALTER TABLE statements evolving it (add a column, modify a type, rename a column).

❓ Chapter Quiz

1. Which command resets AUTO_INCREMENT to its starting value? (a) DELETE (b) TRUNCATE (c) DROP (d) ALTER

2. Which command can use a WHERE clause? (a) DELETE (b) TRUNCATE (c) DROP (d) RENAME

Answer Key: 1-b, 2-a

5.1 INSERT

INSERT INTO students (full_name, course, fee, enrollment_date)
VALUES ('Ananya Sharma', 'Digital Marketing', 15000.00, '2026-07-01');

-- Multiple rows in a single statement
INSERT INTO students (full_name, course, fee, enrollment_date) VALUES
('Rohit Verma', 'SEO', 8000.00, '2026-07-02'),
('Priya Singh', 'Meta Ads', 9000.00, '2026-07-03');

5.2 UPDATE

UPDATE students
SET fee = 8500.00
WHERE full_name = 'Rohit Verma';

5.3 DELETE

DELETE FROM students
WHERE course = 'Discontinued Course';

Like UPDATE, DELETE without a WHERE clause removes every row in the table (though the table structure remains, unlike DROP or TRUNCATE).

5.4 REPLACE

REPLACE INTO students (student_id, full_name, course, fee)
VALUES (5, 'Rohit Verma', 'Advanced SEO', 12000.00);

REPLACE deletes the existing row (if a matching PRIMARY KEY/UNIQUE key is found) and inserts the new one — useful for 'insert or overwrite' scenarios like syncing CRM exports.

5.5 LOAD DATA (Bulk Import)

LOAD DATA INFILE '/path/to/leads.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

✅ Key Takeaways

• INSERT adds new rows; always list column names explicitly for safety and clarity.
• UPDATE and DELETE must almost always include a WHERE clause — test with SELECT first.
• REPLACE is 'insert or overwrite' based on a unique/primary key match.
• LOAD DATA is the fastest way to bulk-import CSV data into a table.

🎯 Interview Questions

Q1. What is the danger of running DELETE FROM table_name; with no WHERE clause?

It deletes every row in the table immediately, and unless wrapped in a transaction that hasn't been committed, that data is gone — the table structure survives but all records are lost.

Q2. How is REPLACE different from UPDATE?

UPDATE modifies specific columns of an existing row in place; REPLACE fully deletes the matching row (if found) and inserts a completely new row, which can reset unspecified columns to their default values.

📝 Practice Exercise

1. Insert 5 sample student records into your 'students' table using a single INSERT statement.
2. Update one student's course and fee, using WHERE on student_id (not name, to avoid duplicates).
3. Delete only the students enrolled before a specific date, verifying with SELECT first.

📚 Mini Assignment

Simulate a real agency workflow: create a 'leads' table, insert 10 sample leads, update 3 leads' status to 'Converted', and delete 2 leads marked 'Spam' — submit all your SQL statements with comments explaining each step.

❓ Chapter Quiz

1. Which statement is most dangerous to run without a WHERE clause? (a) SELECT (b) DELETE (c) DESCRIBE (d) SHOW

2. REPLACE requires the table to have: (a) A FOREIGN KEY (b) A PRIMARY KEY or UNIQUE key (c) A TRIGGER (d) An INDEX only

Answer Key: 1-b, 2-b

6.1 Basic SELECT

SELECT full_name, course, fee
FROM students;

-- Select all columns
SELECT * FROM students;

6.2 WHERE (Filtering Rows)

SELECT full_name, fee
FROM students
WHERE course = 'SEO' AND fee > 7000;

6.3 DISTINCT

SELECT DISTINCT course FROM students;

DISTINCT removes duplicate values from the result set — useful for quickly seeing how many unique courses, cities, or lead sources exist in your data.

6.4 ORDER BY, LIMIT, OFFSET

SELECT full_name, fee
FROM students
ORDER BY fee DESC
LIMIT 5;

-- Pagination: skip first 5, then show next 5
SELECT full_name, fee FROM students
ORDER BY fee DESC
LIMIT 5 OFFSET 5;

6.5 GROUP BY and HAVING

SELECT course, COUNT(*) AS total_students, SUM(fee) AS total_revenue
FROM students
GROUP BY course
HAVING COUNT(*) > 3;

6.6 Aliases (AS)

SELECT full_name AS student_name, fee AS course_fee
FROM students AS s;

6.7 WHERE vs HAVING

✅ Key Takeaways

• SELECT retrieves data; WHERE filters rows; DISTINCT removes duplicates.
• ORDER BY sorts results; LIMIT/OFFSET control pagination — essential for dashboards and reports.
• GROUP BY summarizes data into categories; HAVING filters those summarized groups.
• Aliases (AS) make output column names and joins far more readable in reports.

🎯 Interview Questions

Q1. Can you use an aggregate function like SUM() in a WHERE clause?

No — WHERE executes before grouping/aggregation happens, so aggregate functions aren't available yet at that stage; HAVING must be used instead, since it runs after GROUP BY.

Q2. What does LIMIT 5 OFFSET 10 return?

It skips the first 10 rows of the ordered result set and then returns the next 5 rows — a common pattern for paginated reports and dashboards.

📝 Practice Exercise

1. Write a query to list all students sorted by fee, highest first, showing only the top 3.
2. Find all distinct courses that have more than 2 enrolled students, using GROUP BY and HAVING.
3. Rewrite a query using aliases so 'full_name' displays as 'Student Name' in the output.

📚 Mini Assignment

Using your 'students' or 'leads' table, write 5 SELECT queries answering these business questions: (1) Top 5 highest-paying students, (2) Total revenue per course, (3) Courses with more than 5 students, (4) All unique enrollment dates, (5) Students enrolled in the last 30 days.

❓ Chapter Quiz

1. Which clause removes duplicate rows? (a) WHERE (b) DISTINCT (c) GROUP BY (d) HAVING

2. Which runs first in query execution order? (a) HAVING (b) WHERE (c) ORDER BY (d) LIMIT

3. LIMIT 10 OFFSET 20 returns which rows? (a) 1-10 (b) 11-20 (c) 21-30 (d) 20-30

Answer Key: 1-b, 2-b, 3-c

End of Phase 1 (Modules 2-6). Phase 2 will cover Filtering Data, SQL Functions, Joins, Subqueries, Views, Indexes, and Constraints in the same visual standard.

7.1 AND / OR / NOT

SELECT * FROM students
WHERE course = 'SEO' AND fee > 7000;

SELECT * FROM students
WHERE course = 'SEO' OR course = 'Meta Ads';

SELECT * FROM students
WHERE NOT course = 'Discontinued';

7.2 IN

SELECT * FROM students
WHERE course IN ('SEO', 'Meta Ads', 'Google Ads');

IN is a cleaner shortcut for multiple OR conditions on the same column.

7.3 BETWEEN

SELECT * FROM students
WHERE fee BETWEEN 5000 AND 10000;

SELECT * FROM students
WHERE enrollment_date BETWEEN '2026-01-01' AND '2026-06-30';

7.4 LIKE (Pattern Matching)

SELECT * FROM students
WHERE course LIKE '%Ads%';

7.5 EXISTS, ANY, ALL

-- EXISTS: true if subquery returns any row
SELECT full_name FROM students s
WHERE EXISTS (
    SELECT 1 FROM payments p WHERE p.student_id = s.student_id
);

-- ANY: compares to any value in a list/subquery
SELECT * FROM students
WHERE fee > ANY (SELECT fee FROM students WHERE course = 'SEO');

-- ALL: must be true compared to every value
SELECT * FROM students
WHERE fee > ALL (SELECT fee FROM students WHERE course = 'SEO');

✅ Key Takeaways

• AND/OR/NOT combine multiple conditions; IN simplifies multiple OR checks on one column.
• BETWEEN is inclusive of both boundary values.
• LIKE with % and _ enables flexible text pattern searches.
• EXISTS/ANY/ALL let you filter based on the results of another query.

🎯 Interview Questions

Q1. Is BETWEEN inclusive or exclusive of its boundary values?

Inclusive — 'fee BETWEEN 5000 AND 10000' includes rows where fee equals exactly 5000 or exactly 10000, not just values strictly between them.

Q2. What's the difference between WHERE fee > ANY(...) and WHERE fee > ALL(...)?

ANY only needs the condition to be true compared to at least one value in the subquery result, while ALL requires the condition to hold true against every value returned.

📝 Practice Exercise

1. Write a query finding students whose course is either 'SEO' or 'Google Ads' using IN.
2. Find all students with a fee between 6000 and 9000 using BETWEEN.
3. Find all students whose name starts with 'A' using LIKE.

📚 Mini Assignment

From a 'leads' table, write one query for each operator (AND, IN, BETWEEN, LIKE, EXISTS) that answers a realistic agency reporting question of your choice.

❓ Chapter Quiz

1. Which operator checks a range inclusively? (a) IN (b) BETWEEN (c) LIKE (d) EXISTS

2. LIKE 'S___' (S + 3 underscores) matches names with: (a) Any length starting with S (b) Exactly 4 characters starting with S (c) Ending in S (d) Only 'S'

Answer Key: 1-b, 2-b

8.1 Numeric Functions

8.2 String Functions

8.3 Date Functions

8.4 Aggregate Functions

SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_revenue,
    AVG(fee) AS average_fee,
    MAX(fee) AS highest_fee,
    MIN(fee) AS lowest_fee
FROM students;

8.5 Conversion & Conditional Functions

SELECT CAST(fee AS CHAR) AS fee_text FROM students;

SELECT full_name,
    CASE
        WHEN fee >= 10000 THEN 'Premium'
        WHEN fee >= 5000 THEN 'Standard'
        ELSE 'Basic'
    END AS student_tier
FROM students;

✅ Key Takeaways

• Numeric functions (ROUND, CEIL, FLOOR) handle calculations directly in SQL.
• String functions (CONCAT, UPPER, SUBSTRING) reshape text output for reports.
• Date functions (DATEDIFF, DATE_ADD) power time-based analytics like retention and aging.
• Aggregate functions (COUNT, SUM, AVG) are the backbone of every KPI dashboard.
• CASE WHEN adds conditional logic directly inside a SELECT statement.

🎯 Interview Questions

Q1. What's the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows regardless of NULLs, while COUNT(column_name) only counts rows where that specific column has a non-NULL value.

Q2. How would you categorize customers into tiers based on spend using SQL?

Using a CASE WHEN expression inside the SELECT statement, checking spend thresholds in order and assigning a label like 'Premium', 'Standard', or 'Basic' accordingly.

📝 Practice Exercise

1. Write a query showing each student's full name in uppercase alongside their fee rounded to 0 decimals.
2. Calculate how many days ago each student enrolled using DATEDIFF and CURDATE.
3. Use CASE WHEN to label students as 'High Value' if fee > 10000, else 'Regular'.

📚 Mini Assignment

Build one report query combining at least one function from each family (numeric, string, date, aggregate, conditional) on your 'students' or 'leads' table.

❓ Chapter Quiz

1. Which function joins two text values together? (a) TRIM (b) CONCAT (c) LENGTH (d) SUBSTRING

2. DATEDIFF(NOW(), enrollment_date) returns: (a) A date (b) Number of days between the dates (c) A boolean (d) A string

Answer Key: 1-b, 2-b

9.1 INNER JOIN

SELECT s.full_name, p.amount, p.payment_date
FROM students s
INNER JOIN payments p ON s.student_id = p.student_id;

Returns only rows where a match exists in both tables — students who have made at least one payment.

9.2 LEFT JOIN

SELECT s.full_name, p.amount
FROM students s
LEFT JOIN payments p ON s.student_id = p.student_id;

Returns all students, whether or not they've made a payment (unmatched payment columns show NULL) — perfect for finding students who haven't paid yet.

9.3 RIGHT JOIN

SELECT s.full_name, p.amount
FROM students s
RIGHT JOIN payments p ON s.student_id = p.student_id;

9.4 FULL JOIN (via UNION in MySQL)

MySQL has no native FULL JOIN keyword — simulate it by combining LEFT and RIGHT JOIN with UNION.

SELECT s.full_name, p.amount FROM students s LEFT JOIN payments p ON s.student_id = p.student_id
UNION
SELECT s.full_name, p.amount FROM students s RIGHT JOIN payments p ON s.student_id = p.student_id;

9.5 SELF JOIN

SELECT a.full_name AS student, b.full_name AS referred_by
FROM students a
JOIN students b ON a.referred_by_id = b.student_id;

A SELF JOIN joins a table to itself — useful for referral chains, org charts (employee-manager), or comparing rows within the same table.

9.6 CROSS JOIN

SELECT c.course_name, b.batch_time
FROM courses c
CROSS JOIN batch_timings b;

CROSS JOIN returns every possible combination of rows from both tables — useful for generating a full course x batch-timing grid to plan a training calendar.

✅ Key Takeaways

• INNER JOIN returns only matching rows from both tables.
• LEFT/RIGHT JOIN keep all rows from one side even without a match (NULLs fill the gap).
• MySQL simulates FULL JOIN using LEFT JOIN UNION RIGHT JOIN.
• SELF JOIN compares a table to itself; CROSS JOIN produces every possible row combination.

🎯 Interview Questions

Q1. How do you write a FULL JOIN in MySQL?

Since MySQL lacks a native FULL JOIN keyword, you combine a LEFT JOIN and a RIGHT JOIN between the same two tables using UNION, which also removes duplicate matching rows.

Q2. Give a real business use-case for a SELF JOIN.

Modeling a referral program where each student record stores who referred them, or an employee table where each row stores their manager's ID, referencing the same table.

📝 Practice Exercise

1. Write an INNER JOIN between students and payments to list only students who have paid.
2. Write a LEFT JOIN to find all students who have NOT made any payment (hint: WHERE payment IS NULL).
3. Write a CROSS JOIN between a 'courses' and 'batch_timings' table to generate a full schedule grid.

📚 Mini Assignment

Design two related tables ('clients' and 'invoices') for Samantus Web Services, insert sample data, then write INNER, LEFT, and SELF JOIN queries answering 3 different real agency questions.

❓ Chapter Quiz

1. Which join returns unmatched rows from the left table as NULL on the right? (a) INNER (b) LEFT (c) CROSS (d) SELF

2. Table A (4 rows) CROSS JOIN Table B (6 rows) returns how many rows? (a) 10 (b) 4 (c) 24 (d) Depends on matches

Answer Key: 1-b, 2-c

10.1 Nested Subquery

SELECT full_name, fee
FROM students
WHERE fee > (SELECT AVG(fee) FROM students);

The inner query runs first (calculating the average fee), then the outer query uses that result to filter students earning above average.

10.2 Correlated Subquery

SELECT s.full_name, s.course, s.fee
FROM students s
WHERE s.fee > (
    SELECT AVG(fee) FROM students s2 WHERE s2.course = s.course
);

Unlike a nested subquery, a correlated subquery re-runs once per row of the outer query, referencing the outer row (s.course) each time — here it finds students earning above their own course's average.

10.3 Scalar Subquery

SELECT full_name,
    (SELECT COUNT(*) FROM payments p WHERE p.student_id = s.student_id) AS total_payments
FROM students s;

A scalar subquery returns exactly one value per row and can be used directly inside a SELECT column list.

✅ Key Takeaways

• Nested subqueries run once, independent of the outer query.
• Correlated subqueries run once per outer row, referencing outer-query columns.
• Scalar subqueries return a single value and can sit inside a SELECT column list.
• Subqueries let you answer multi-step questions without multiple manual queries.

🎯 Interview Questions

Q1. What is the key difference between a nested and a correlated subquery?

A nested subquery executes once and its result is used by the outer query; a correlated subquery depends on the outer query's current row and re-executes for every row processed.

Q2. Can a subquery in a WHERE clause return multiple rows?

It depends on the operator used — with '=' it must return exactly one value, but with IN, ANY, or ALL it can safely return multiple rows.

📝 Practice Exercise

1. Write a query to find students paying more than the overall average fee.
2. Write a correlated subquery to find students paying more than their own course's average fee.
3. Add a scalar subquery column showing each student's total number of payments.

📚 Mini Assignment

Using your 'students' and 'payments' tables, write 3 queries: one nested, one correlated, and one scalar subquery, each solving a different realistic business question for your institute.

❓ Chapter Quiz

1. A subquery that re-runs once per outer row is called: (a) Nested (b) Correlated (c) Scalar (d) Aggregate

2. A scalar subquery must return: (a) Multiple rows (b) A single value (c) A table (d) Nothing

Answer Key: 1-b, 2-b

11.1 Creating a View

CREATE VIEW active_students AS
SELECT student_id, full_name, course, fee
FROM students
WHERE is_active = TRUE;

SELECT * FROM active_students;

11.2 Updating and Dropping a View

CREATE OR REPLACE VIEW active_students AS
SELECT student_id, full_name, course, fee, enrollment_date
FROM students WHERE is_active = TRUE;

DROP VIEW IF EXISTS active_students;

✅ Key Takeaways

• A view stores a query, not the data itself — it always reflects live table data.
• Views simplify repeated complex reporting logic into a simple SELECT.
• CREATE OR REPLACE VIEW safely updates a view's definition without dropping it first.

🎯 Interview Questions

Q1. Does a view store data physically?

No, a standard view stores only the SELECT query definition and always executes against the current live data in the underlying tables when queried.

Q2. Why would you use a view instead of just writing the full query each time?

Views simplify repeated, complex queries into a simple, reusable name, and can also restrict which columns or rows different users are allowed to see.

📝 Practice Exercise

1. Create a view showing only students who enrolled in the last 90 days.
2. Modify that view to also include their course fee, using CREATE OR REPLACE VIEW.
3. Drop the view safely using IF EXISTS.

📚 Mini Assignment

Create a 'top_paying_students' view for Samantus showing the top 10 highest-fee students, then write a short note on 2 real use-cases where you'd share this view with your team instead of the raw table.

❓ Chapter Quiz

1. A view is best described as: (a) A physical copy of data (b) A saved, reusable query (c) A backup file (d) An index

Answer Key: 1-b

12.1 What is an Index?

An index is a special lookup structure that lets MySQL find rows without scanning the entire table — similar to an index at the back of a book letting you jump straight to a topic instead of reading every page.

12.2 Clustered vs Non-Clustered Indexes

12.3 Creating and Dropping an Index

CREATE INDEX idx_course ON students(course);

SHOW INDEX FROM students;

DROP INDEX idx_course ON students;

✅ Key Takeaways

• Indexes dramatically speed up SELECT queries by avoiding full table scans.
• A table has exactly one clustered index (typically the PRIMARY KEY) but can have many non-clustered indexes.
• Indexes slightly slow down INSERT/UPDATE/DELETE, so they should be added deliberately, not everywhere.

🎯 Interview Questions

Q1. Why might adding too many indexes hurt performance?

Every INSERT, UPDATE, or DELETE must also update all the affected indexes, so excessive indexing increases write overhead and storage while only benefiting read speed.

Q2. Which column is typically the clustered index by default in MySQL/InnoDB?

The PRIMARY KEY column is used as the clustered index by default in InnoDB, physically ordering the table's rows around it.

📝 Practice Exercise

1. Add an index on the 'course' column of your students table.
2. Run SHOW INDEX FROM students; and interpret the output.
3. Discuss (in writing) which columns in a 'leads' table you would index and why.

📚 Mini Assignment

For a 'transactions' table handling 100,000+ rows for a client, list 3 columns you would index and explain the expected performance benefit for each, based on how that column is likely to be queried.

❓ Chapter Quiz

1. How many clustered indexes can one table have? (a) 0 (b) 1 (c) Unlimited (d) Exactly 2

2. Indexes primarily improve the speed of: (a) INSERT (b) DELETE (c) SELECT (d) UPDATE

Answer Key: 1-b, 2-c

13.1 Constraint Types

13.2 Full Example

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) CHECK (amount > 0),
    payment_date DATE DEFAULT (CURRENT_DATE),
    method VARCHAR(20) DEFAULT 'UPI',
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

✅ Key Takeaways

• PRIMARY KEY uniquely identifies rows; FOREIGN KEY enforces valid relationships between tables.
• UNIQUE prevents duplicates; CHECK restricts values to valid ranges/conditions.
• DEFAULT auto-fills sensible values; NOT NULL guarantees required data is never missing.
• AUTO_INCREMENT removes the need to manually manage unique ID numbers.

🎯 Interview Questions

Q1. What problem does a FOREIGN KEY constraint solve?

It prevents 'orphan' rows by ensuring a value in a child table (like student_id in payments) must already exist in the referenced parent table (students), preserving relational integrity.

Q2. Can a table have more than one UNIQUE constraint?

Yes — a table can have multiple UNIQUE constraints on different columns, unlike PRIMARY KEY which can only be defined once per table (though it can span multiple columns as a composite key).

📝 Practice Exercise

1. Add a UNIQUE constraint to the email column of your students table.
2. Add a CHECK constraint ensuring fee is always greater than 0.
3. Create a 'payments' table with a FOREIGN KEY referencing 'students'.

📚 Mini Assignment

Design a complete 3-table schema (students, courses, payments) for Samantus with appropriate PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, and NOT NULL constraints on each table, and briefly justify each constraint choice.

❓ Chapter Quiz

1. Which constraint prevents duplicate emails? (a) CHECK (b) UNIQUE (c) DEFAULT (d) FOREIGN KEY

2. Which constraint links a child table's column to a parent table's key? (a) UNIQUE (b) FOREIGN KEY (c) CHECK (d) DEFAULT

Answer Key: 1-b, 2-b

End of Phase 2 (Modules 7-13). Phase 3 will cover Stored Procedures, Functions, Triggers, Transactions, Cursors, Events, Window Functions, and CTEs in the same visual standard.

14.1 Creating a Stored Procedure

DELIMITER //

CREATE PROCEDURE GetStudentsByCourse(IN course_name VARCHAR(50))
BEGIN
    SELECT full_name, fee, enrollment_date
    FROM students
    WHERE course = course_name;
END //

DELIMITER ;

Call it with:

CALL GetStudentsByCourse('SEO');

14.2 Using OUT Parameters

DELIMITER //
CREATE PROCEDURE GetTotalRevenue(IN course_name VARCHAR(50), OUT total DECIMAL(10,2))
BEGIN
    SELECT SUM(fee) INTO total FROM students WHERE course = course_name;
END //
DELIMITER ;

CALL GetTotalRevenue('SEO', @revenue);
SELECT @revenue;

14.3 Business Use Cases

• Monthly revenue calculation procedure run automatically by finance staff
• Student enrollment procedure that validates and inserts a new record in one call
• Lead-status update procedure standardizing how your team's CRM changes records

✅ Key Takeaways

• Stored procedures package reusable SQL logic behind a callable name.
• IN parameters pass data in; OUT parameters return calculated results back to the caller.
• Procedures standardize business logic so every user/application follows the same rules.

🎯 Interview Questions

Q1. Why do you need to change the DELIMITER before creating a procedure?

Because the procedure body itself contains semicolons to end its internal statements, MySQL needs a different overall statement terminator (like //) so it doesn't stop reading the CREATE PROCEDURE statement too early.

Q2. What is the difference between an IN and an OUT parameter?

An IN parameter passes a value into the procedure for it to use, while an OUT parameter is used by the procedure to send a calculated result back out to the calling code.

📝 Practice Exercise

1. Create a procedure that returns all students for a given course name.
2. Create a procedure with an OUT parameter that returns the count of students in a course.
3. Call both procedures and verify the results.

📚 Mini Assignment

Design a 'RegisterNewLead' procedure for Samantus Web Services that takes lead name, source, and status as IN parameters, inserts the record, and returns the new lead's ID via an OUT parameter.

❓ Chapter Quiz

1. Which keyword starts a stored procedure call? (a) RUN (b) CALL (c) EXEC (d) START

2. An OUT parameter is used to: (a) Pass data in (b) Return a result (c) Delete data (d) Create an index

Answer Key: 1-b, 2-b

15.1 Creating a Function

DELIMITER //

CREATE FUNCTION CalculateGST(base_amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN base_amount * 1.18;
END //

DELIMITER ;

15.2 Using the Function

SELECT full_name, fee, CalculateGST(fee) AS fee_with_gst
FROM students;

15.3 Function vs Procedure

✅ Key Takeaways

• A function must return exactly one value and can be embedded directly in a SELECT statement.
• DETERMINISTIC tells MySQL the function always returns the same output for the same input, enabling internal optimizations.
• Use functions for calculations reused across many queries (tax, discounts, formatting).

🎯 Interview Questions

Q1. Can a user-defined function be used inside a SELECT statement, unlike a procedure?

Yes — this is the key practical advantage of functions: because they return a single value, they can be called directly as part of a column expression inside SELECT.

Q2. What does DETERMINISTIC mean in a function definition?

It tells MySQL the function will always return the same result given the same input parameters, which allows the optimizer to potentially cache or reuse results.

📝 Practice Exercise

1. Create a function that adds an 18% GST to any given amount.
2. Use it inside a SELECT query on your students or payments table.
3. Create a second function that converts a fee amount into a formatted currency string.

📚 Mini Assignment

Create a 'CalculateDiscount(fee, discount_percent)' function for Samantus that returns the discounted fee, then use it in a report showing original vs discounted fees for all students.

❓ Chapter Quiz

1. A user-defined function must return: (a) Nothing (b) Exactly one value (c) A full table (d) Multiple result sets

Answer Key: 1-b

16.1 INSERT Trigger

DELIMITER //
CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    IF NEW.fee < 0 THEN
        SET NEW.fee = 0;
    END IF;
END //
DELIMITER ;

16.2 UPDATE Trigger (Audit Log)

DELIMITER //
CREATE TRIGGER after_fee_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    IF OLD.fee <> NEW.fee THEN
        INSERT INTO fee_change_log (student_id, old_fee, new_fee, changed_at)
        VALUES (OLD.student_id, OLD.fee, NEW.fee, NOW());
    END IF;
END //
DELIMITER ;

16.3 DELETE Trigger

DELIMITER //
CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO deleted_students_log (student_id, full_name, deleted_at)
    VALUES (OLD.student_id, OLD.full_name, NOW());
END //
DELIMITER ;

✅ Key Takeaways

• Triggers run automatically on INSERT/UPDATE/DELETE events, without being called manually.
• BEFORE triggers can validate/modify incoming data; AFTER triggers are ideal for logging.
• NEW refers to the incoming/updated row; OLD refers to the previous row's values.

🎯 Interview Questions

Q1. What is the difference between OLD and NEW inside a trigger?

OLD refers to the row's values before the change (available in UPDATE/DELETE triggers), while NEW refers to the incoming or updated values (available in INSERT/UPDATE triggers).

Q2. Can an AFTER trigger modify the NEW values of the row being inserted?

No — by the time an AFTER trigger runs, the row has already been committed to the table, so only BEFORE triggers can modify NEW values before they are saved.

📝 Practice Exercise

1. Create a BEFORE INSERT trigger that prevents negative fee values.
2. Create an AFTER UPDATE trigger that logs every fee change to a log table.
3. Create an AFTER DELETE trigger that archives deleted student records.

📚 Mini Assignment

Design a full audit system for Samantus Web Services using 3 triggers (insert, update, delete) on a 'clients' table, and document what business problem each trigger solves.

❓ Chapter Quiz

1. Which trigger timing can modify incoming NEW values? (a) BEFORE (b) AFTER (c) Both (d) Neither

2. OLD is available in which trigger types? (a) INSERT only (b) UPDATE and DELETE (c) INSERT and UPDATE (d) None

Answer Key: 1-a, 2-b

17.1 ACID Properties

17.2 COMMIT and ROLLBACK

START TRANSACTION;

UPDATE students SET fee = fee - 1000 WHERE student_id = 5;
INSERT INTO payments (student_id, amount) VALUES (5, 1000);

COMMIT;  -- Save both changes permanently
-- or
ROLLBACK;  -- Undo both changes if something went wrong

17.3 SAVEPOINT

START TRANSACTION;

UPDATE students SET fee = fee - 500 WHERE student_id = 5;
SAVEPOINT after_discount;

INSERT INTO payments (student_id, amount) VALUES (5, 500);
-- Something went wrong with the payment step only:
ROLLBACK TO after_discount;

COMMIT;

✅ Key Takeaways

• ACID (Atomicity, Consistency, Isolation, Durability) defines what makes a transaction reliable.
• COMMIT permanently saves all changes in the current transaction; ROLLBACK undoes them.
• SAVEPOINT lets you roll back to a specific midpoint instead of undoing the entire transaction.

🎯 Interview Questions

Q1. Why are transactions critical for payment systems?

They guarantee that a sequence of related changes either all succeed together or all fail together, preventing inconsistent states like a balance being deducted with no matching payment record.

Q2. What does SAVEPOINT let you do that plain ROLLBACK cannot?

SAVEPOINT lets you roll back only part of a transaction to a named point, keeping earlier successful statements intact, rather than undoing the entire transaction from the start.

📝 Practice Exercise

1. Write a transaction that updates a student's fee and inserts a payment record, using COMMIT.
2. Deliberately write a transaction with an error and use ROLLBACK to undo it safely.
3. Practice using SAVEPOINT to partially roll back a multi-step transaction.

📚 Mini Assignment

Design a transaction-safe 'ProcessRefund' workflow for Samantus Web Services covering: reducing revenue, inserting a refund record, and updating client status — explain what could go wrong without transactions.

❓ Chapter Quiz

1. Which ACID property ensures a transaction is all-or-nothing? (a) Consistency (b) Atomicity (c) Isolation (d) Durability

2. Which command permanently saves transaction changes? (a) ROLLBACK (b) SAVEPOINT (c) COMMIT (d) START TRANSACTION

Answer Key: 1-b, 2-c

18.1 Cursor Syntax

DELIMITER //
CREATE PROCEDURE ApplyLateFee()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE sid INT;
    DECLARE cur CURSOR FOR SELECT student_id FROM students WHERE payment_status = 'Overdue';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO sid;
        IF done THEN LEAVE read_loop; END IF;
        UPDATE students SET fee = fee + 200 WHERE student_id = sid;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

✅ Key Takeaways

• A cursor processes query results one row at a time, unlike normal set-based SQL.
• The typical cycle is: DECLARE, OPEN, FETCH (in a loop), then CLOSE.
• Cursors are usually slower than set-based queries and should be a last resort.

🎯 Interview Questions

Q1. Why are cursors generally considered a last resort in SQL?

Because they process one row at a time instead of operating on the whole result set, cursors are typically much slower than an equivalent set-based query using JOINs or CASE logic.

Q2. What is the purpose of the CONTINUE HANDLER FOR NOT FOUND in a cursor loop?

It tells MySQL what to do once the cursor has fetched every row — typically setting a 'done' flag to true so the loop can exit cleanly instead of throwing an error.

📝 Practice Exercise

1. Rewrite the ApplyLateFee example logic as a single set-based UPDATE statement (no cursor).
2. Identify a scenario in your own data where a cursor might genuinely be necessary.
3. Practice declaring, opening, and closing a simple cursor over a small table.

📚 Mini Assignment

Write both a cursor-based and a set-based version of a task that applies a 5% fee increase to all students in a specific course, then explain which approach you would use in production and why.

❓ Chapter Quiz

1. Cursors process results: (a) All at once (b) One row at a time (c) In reverse order only (d) Randomly

Answer Key: 1-b

19.1 Enabling the Scheduler

SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE 'event_scheduler';

19.2 Creating a Recurring Event

DELIMITER //
CREATE EVENT daily_revenue_snapshot
ON SCHEDULE EVERY 1 DAY STARTS '2026-08-01 23:59:00'
DO
BEGIN
    INSERT INTO revenue_snapshots (snapshot_date, total_revenue)
    SELECT CURDATE(), SUM(fee) FROM students;
END //
DELIMITER ;

✅ Key Takeaways

• The MySQL Event Scheduler must be explicitly enabled before events will run.
• Events run SQL automatically on a defined schedule, similar to a cron job.
• Great for recurring snapshots, cleanup tasks, and scheduled reports.

🎯 Interview Questions

Q1. What must be enabled before a MySQL event will actually run?

The global event_scheduler variable must be set to ON; otherwise events remain defined in the database but never execute.

Q2. Give a practical use-case for MySQL events in a business setting.

Automatically generating a daily or monthly revenue snapshot, or periodically archiving old log data, without requiring a human or external scheduler to trigger it.

📝 Practice Exercise

1. Enable the event scheduler and confirm it's active.
2. Create a daily event that records total student count into a snapshot table.
3. Modify the event to run weekly instead of daily.

📚 Mini Assignment

Design an automation event for Samantus that archives students who completed their course more than 1 year ago into a 'students_archive' table, running monthly.

❓ Chapter Quiz

1. Which variable must be ON for events to run? (a) auto_events (b) event_scheduler (c) cron_mode (d) schedule_on

Answer Key: 1-b

20.1 ROW_NUMBER, RANK, DENSE_RANK

SELECT full_name, course, fee,
    ROW_NUMBER() OVER (PARTITION BY course ORDER BY fee DESC) AS row_num,
    RANK()       OVER (PARTITION BY course ORDER BY fee DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY course ORDER BY fee DESC) AS dense_rank_num
FROM students;

20.2 LEAD and LAG

SELECT full_name, enrollment_date,
    LAG(enrollment_date) OVER (ORDER BY enrollment_date) AS previous_enrollment,
    LEAD(enrollment_date) OVER (ORDER BY enrollment_date) AS next_enrollment
FROM students;

20.3 NTILE (Bucketing)

SELECT full_name, fee,
    NTILE(4) OVER (ORDER BY fee DESC) AS fee_quartile
FROM students;

NTILE(4) splits students into 4 equal-sized groups (quartiles) based on fee — useful for identifying your top 25% highest-paying students.

20.4 Running Total

SELECT enrollment_date, fee,
    SUM(fee) OVER (ORDER BY enrollment_date) AS running_total_revenue
FROM students;

✅ Key Takeaways

• Window functions calculate across related rows without collapsing them, unlike GROUP BY.
• ROW_NUMBER, RANK, and DENSE_RANK handle ties differently — know all three for interviews.
• LEAD/LAG access neighboring rows; NTILE buckets rows into equal groups.
• Running totals via SUM() OVER (ORDER BY ...) are a cornerstone of trend dashboards.

🎯 Interview Questions

Q1. What's the difference between RANK() and DENSE_RANK() when there's a tie?

RANK() leaves a gap in the numbering after a tie (e.g. 1,2,2,4), while DENSE_RANK() continues consecutively with no gaps (e.g. 1,2,2,3).

Q2. How would you find each course's top-paying student using a window function?

Use ROW_NUMBER() OVER (PARTITION BY course ORDER BY fee DESC) and then filter the outer query to keep only rows where that row number equals 1.

📝 Practice Exercise

1. Rank students within each course by fee using RANK() and DENSE_RANK(), and compare the outputs.
2. Use LAG to show each student's fee alongside the previous student's fee (by enrollment order).
3. Calculate a running total of revenue ordered by enrollment date.

📚 Mini Assignment

Build a full 'Top Student Per Course' report using ROW_NUMBER() with PARTITION BY, plus a running total revenue column for the whole institute, and present both in one query.

❓ Chapter Quiz

1. Which function never skips numbers after a tie? (a) RANK (b) DENSE_RANK (c) ROW_NUMBER (d) NTILE

2. NTILE(4) splits rows into: (a) 4 running totals (b) 4 equal-sized groups (c) 4 tables (d) 4 columns

Answer Key: 1-b, 2-b

21.1 Non-Recursive CTE

WITH course_revenue AS (
    SELECT course, SUM(fee) AS total_revenue
    FROM students
    GROUP BY course
)
SELECT * FROM course_revenue
WHERE total_revenue > 50000;

The CTE 'course_revenue' is calculated once and can then be queried like a normal table for the rest of the statement — far more readable than nesting the same logic as a subquery.

21.2 Recursive CTE

WITH RECURSIVE referral_chain AS (
    SELECT student_id, full_name, referred_by_id, 1 AS level
    FROM students WHERE referred_by_id IS NULL

    UNION ALL

    SELECT s.student_id, s.full_name, s.referred_by_id, rc.level + 1
    FROM students s
    JOIN referral_chain rc ON s.referred_by_id = rc.student_id
)
SELECT * FROM referral_chain ORDER BY level;

A recursive CTE has two parts: the anchor (base case, e.g. students with no referrer) and the recursive part (joins back to the CTE itself) — ideal for referral chains, org charts, or category trees.

✅ Key Takeaways

• A CTE (WITH clause) creates a named, temporary result set for use within one query.
• Non-recursive CTEs mainly improve readability over nested subqueries.
• Recursive CTEs (WITH RECURSIVE) are the only way to query hierarchical data like referral chains or org charts in MySQL.
• Every recursive CTE needs an anchor part and a recursive part joined with UNION ALL.

🎯 Interview Questions

Q1. What are the two required parts of a recursive CTE?

An anchor member that defines the base case (e.g. top-level rows with no parent), and a recursive member that joins back to the CTE itself, combined using UNION ALL.

Q2. Is a CTE only useful for recursion?

No — even non-recursive CTEs are valuable for breaking a complex query into named, readable steps, especially when the same intermediate result is referenced multiple times.

📝 Practice Exercise

1. Rewrite one of your earlier subquery examples (Module 10) as a CTE and compare readability.
2. Write a recursive CTE modeling a simple 3-level referral chain using sample data.
3. Use a CTE to calculate course revenue, then filter for courses above a certain threshold.

📚 Mini Assignment

Design a recursive CTE for Samantus modeling an employee-manager hierarchy (who reports to whom), showing each employee's level in the organization.

❓ Chapter Quiz

1. Which keyword is required for a recursive CTE? (a) LOOP (b) RECURSIVE (c) REPEAT (d) CHAIN

2. A recursive CTE's two parts are joined using: (a) INNER JOIN (b) UNION ALL (c) CROSS JOIN (d) INTERSECT

Answer Key: 1-b, 2-b

End of Phase 3 (Modules 14-21). Phase 4 will cover Performance Optimization, Import/Export, MySQL Security, and MySQL for Data Analytics (with real company case studies) in the same visual standard.

22.1 EXPLAIN

EXPLAIN SELECT * FROM students WHERE course = 'SEO';

EXPLAIN shows how MySQL plans to execute a query — whether it uses an index (type: ref/range) or scans the whole table (type: ALL, a red flag on large tables).

22.2 Normalization vs Denormalization

22.3 Practical Optimization Tips

• Index columns used frequently in WHERE, JOIN, and ORDER BY clauses
• Avoid SELECT * in production queries — select only the columns you need
• Use LIMIT when you only need a preview of results
• Avoid functions on indexed columns in WHERE (e.g. WHERE YEAR(date_col)=2026 disables the index; use a date range instead)
• Regularly run EXPLAIN on slow reporting queries before they go into production dashboards

✅ Key Takeaways

• EXPLAIN reveals whether MySQL is using an index or scanning the full table.
• Normalization reduces redundancy (good for transactional data); denormalization improves read speed (good for reporting/analytics).
• Avoiding SELECT *, indexing wisely, and not wrapping indexed columns in functions are practical daily habits for fast queries.

🎯 Interview Questions

Q1. What does it mean if EXPLAIN shows type: ALL for a query?

It means MySQL is performing a full table scan instead of using an index, which can be very slow on large tables and usually signals a missing or unusable index.

Q2. Why might a reporting/analytics database intentionally use some denormalization?

Because analytics queries often need to aggregate across many related entities, and storing some redundant data avoids excessive JOINs, trading storage space for read speed.

📝 Practice Exercise

1. Run EXPLAIN on 3 of your existing queries from earlier modules and note the 'type' column.
2. Identify one query using SELECT * and rewrite it to select only needed columns.
3. Rewrite a WHERE clause that wraps a date column in YEAR() as an equivalent date range instead.

📚 Mini Assignment

Take your most complex JOIN query from Module 9 or 10, run EXPLAIN on it, and write a short report identifying at least one potential optimization.

❓ Chapter Quiz

1. Which command shows a query's execution plan? (a) DESCRIBE (b) EXPLAIN (c) ANALYZE (d) SHOW

2. Denormalization is generally better suited for: (a) OLTP (b) OLAP/reporting (c) Backups (d) Security

Answer Key: 1-b, 2-b

23.1 Importing CSV (Command Line)

LOAD DATA INFILE '/path/to/leads.csv'
INTO TABLE leads
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

23.2 Exporting Query Results to CSV

SELECT * FROM students
INTO OUTFILE '/var/lib/mysql-files/students_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

For Excel/Workbench exports, use MySQL Workbench's built-in 'Export Resultset' button, which avoids MySQL server file-permission restrictions entirely.

23.3 Backup with mysqldump

-- Backup a single database
mysqldump -u root -p samantus_institute > samantus_backup.sql

-- Backup all databases
mysqldump -u root -p --all-databases > full_backup.sql

23.4 Restore from Backup

mysql -u root -p samantus_institute < samantus_backup.sql

✅ Key Takeaways

• LOAD DATA INFILE and INTO OUTFILE handle bulk CSV import/export from the command line.
• mysqldump creates a logical (SQL script) backup that can rebuild a database from scratch.
• Always back up before any risky bulk operation on real business data.

🎯 Interview Questions

Q1. What does mysqldump actually produce?

A text file containing the SQL statements (CREATE TABLE, INSERT, etc.) needed to fully recreate the database's structure and data from scratch on any MySQL server.

Q2. Why should you back up a database before running a bulk UPDATE or DELETE?

Because both operations can be very difficult or impossible to undo if a mistake is made, and a fresh backup guarantees a fast, safe recovery path.

📝 Practice Exercise

1. Export your students table to a CSV file.
2. Take a full mysqldump backup of one of your practice databases.
3. Practice restoring that backup into a newly created test database.

📚 Mini Assignment

Write a step-by-step backup-and-restore runbook (in plain language, for a non-technical team member) that Samantus staff could follow before any major client database change.

❓ Chapter Quiz

1. Which tool creates a full SQL backup file? (a) LOAD DATA (b) mysqldump (c) EXPLAIN (d) TRUNCATE

2. What should you always do before a risky bulk UPDATE? (a) Nothing (b) Take a backup (c) Drop the table (d) Disable indexes

Answer Key: 1-b, 2-b

24.1 Creating Users

CREATE USER 'samantus_analyst'@'localhost' IDENTIFIED BY 'StrongPassword123!';

24.2 GRANT and REVOKE

-- Give read-only access to one database
GRANT SELECT ON samantus_institute.* TO 'samantus_analyst'@'localhost';

-- Give full access
GRANT ALL PRIVILEGES ON samantus_institute.* TO 'samantus_admin'@'localhost';

-- Remove a specific permission
REVOKE DELETE ON samantus_institute.* FROM 'samantus_analyst'@'localhost';

FLUSH PRIVILEGES;

24.3 Roles (MySQL 8.x)

CREATE ROLE 'report_viewer';
GRANT SELECT ON samantus_institute.* TO 'report_viewer';
GRANT 'report_viewer' TO 'samantus_analyst'@'localhost';

24.4 Security Best Practices

• Never use the root account for everyday application connections
• Follow the Principle of Least Privilege — give each user only the access they truly need
• Use strong, unique passwords for every MySQL account
• Regularly review SHOW GRANTS FOR 'username'@'host'; to audit access
• Keep MySQL server software updated with the latest security patches

✅ Key Takeaways

• CREATE USER + GRANT define who can access what in MySQL; REVOKE removes specific permissions.
• Roles (MySQL 8.x) let you bundle permissions and assign them to multiple users at once.
• The Principle of Least Privilege — giving only the minimum access needed — is the foundation of good database security.

🎯 Interview Questions

Q1. What is the Principle of Least Privilege?

It means giving each user or application account only the minimum permissions necessary to do its job, reducing the potential damage from a mistake or a compromised account.

Q2. What's the difference between GRANT and REVOKE?

GRANT assigns specific permissions to a user or role, while REVOKE removes previously granted permissions, both taking effect immediately for future connections.

📝 Practice Exercise

1. Create a read-only user account for your practice database.
2. Grant SELECT-only access on one specific table instead of the whole database.
3. Run SHOW GRANTS FOR to audit what permissions that user actually has.

📚 Mini Assignment

Design a full user-access plan for Samantus Web Services covering 3 roles: Admin (full access), Analyst (read-only reporting), and Client-Viewer (read-only, single database) — write the exact CREATE USER/GRANT statements for each.

❓ Chapter Quiz

1. Which principle means giving users only the access they truly need? (a) Full Access (b) Least Privilege (c) Open Access (d) Root Access

2. Which command removes a previously granted permission? (a) GRANT (b) REVOKE (c) DROP (d) DELETE

Answer Key: 1-b, 2-b

25.1 Sales & Marketing Analytics

-- Monthly revenue trend
SELECT DATE_FORMAT(enrollment_date, '%Y-%m') AS month, SUM(fee) AS revenue
FROM students GROUP BY month ORDER BY month;

-- Lead-source conversion rate
SELECT lead_source,
    COUNT(*) AS total_leads,
    SUM(CASE WHEN status='Converted' THEN 1 ELSE 0 END) AS converted,
    ROUND(SUM(CASE WHEN status='Converted' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate
FROM leads GROUP BY lead_source;

25.2 Customer / HR / Finance Analytics

25.3 Dashboard & KPI Query Patterns

-- A typical dashboard 'top KPIs' query
SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_revenue,
    ROUND(AVG(fee), 2) AS avg_fee,
    (SELECT COUNT(*) FROM students WHERE enrollment_date >= CURDATE() - INTERVAL 30 DAY)
        AS new_students_last_30_days
FROM students;

25.4 MySQL and Business Intelligence

MySQL typically acts as the underlying data source for BI tools like Power BI, Tableau, Metabase, or Google Data Studio. Your job as an analyst is to write clean, well-indexed queries (or views) that these tools connect to — the modules you've completed in this manual are exactly the skill set BI tools depend on underneath their drag-and-drop interfaces.

✅ Key Takeaways

• Real analytics work applies the same core SQL skills (JOIN, GROUP BY, window functions, CTEs) repeatedly across different business domains.
• KPI queries are typically simple aggregates wrapped in clear, well-named output columns.
• MySQL commonly serves as the data backend powering BI dashboard tools.

🎯 Interview Questions

Q1. How does MySQL typically fit into a company's Business Intelligence stack?

It usually serves as the underlying data source that BI tools like Power BI or Tableau connect to, with the analyst responsible for writing clean, performant SQL or views that those tools query.

Q2. What's a good practice before presenting any KPI number to a client or stakeholder?

Provide context, such as comparison to a previous period or target, and confirm the underlying data has been checked for duplicates or data-quality issues that could skew results.

📝 Practice Exercise

1. Build a monthly revenue trend query for your institute's data.
2. Build a lead-source conversion rate query.
3. Combine 3 KPIs (total students, total revenue, new students in last 30 days) into one dashboard query.

📚 Mini Assignment

Design a complete one-page 'Samantus Business Dashboard' query set (5-6 KPIs) covering training institute revenue, agency client status, and YouTube-adjacent content metrics if you track them in MySQL.

❓ Chapter Quiz

1. MySQL typically serves BI tools as: (a) A charting library (b) The underlying data source (c) A design tool (d) A spreadsheet

2. Before presenting a KPI, you should always: (a) Round to zero decimals (b) Provide context/comparison (c) Hide the query (d) Remove all filters

Answer Key: 1-b, 2-b

Applying MySQL to Business Problems

These case studies use realistic, simplified schemas inspired by well-known consumer platforms to practice writing analytics queries for scenarios your students may encounter in interviews or real projects. All data described is illustrative and not sourced from any company's actual systems.

Case Study 1: Food Delivery Platform (Zomato/Swiggy-style)

Schema: restaurants(restaurant_id, name, city), orders(order_id, restaurant_id, customer_id, order_amount, order_date, status)

-- Top 5 restaurants by revenue this month
SELECT r.name, SUM(o.order_amount) AS monthly_revenue
FROM orders o JOIN restaurants r ON o.restaurant_id = r.restaurant_id
WHERE o.status = 'Delivered'
  AND o.order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY r.name
ORDER BY monthly_revenue DESC
LIMIT 5;

Case Study 2: E-commerce Platform (Amazon/Flipkart-style)

Schema: products(product_id, name, category, price), order_items(order_id, product_id, quantity), orders(order_id, customer_id, order_date)

-- Best-selling category by units sold
SELECT p.category, SUM(oi.quantity) AS units_sold
FROM order_items oi JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY units_sold DESC;

Case Study 3: Streaming Platform (Netflix-style)

Schema: users(user_id, signup_date, plan_type), watch_history(user_id, content_id, watch_date, minutes_watched)

-- Most-watched content by total minutes
SELECT content_id, SUM(minutes_watched) AS total_minutes
FROM watch_history
GROUP BY content_id
ORDER BY total_minutes DESC
LIMIT 10;

-- Monthly active users
SELECT DATE_FORMAT(watch_date, '%Y-%m') AS month, COUNT(DISTINCT user_id) AS active_users
FROM watch_history GROUP BY month ORDER BY month;

Case Study 4: Ride-Hailing Platform (Uber-style)

Schema: drivers(driver_id, city), rides(ride_id, driver_id, fare, ride_date, rating)

-- Average driver rating by city
SELECT d.city, ROUND(AVG(r.rating), 2) AS avg_rating, COUNT(*) AS total_rides
FROM rides r JOIN drivers d ON r.driver_id = d.driver_id
GROUP BY d.city
ORDER BY avg_rating DESC;

Case Study 5: Retail Chain (Reliance Retail/BigBasket-style)

Schema: stores(store_id, city), inventory(store_id, product_id, stock_qty, reorder_level)

-- Products below reorder level, needing urgent restock
SELECT s.city, i.product_id, i.stock_qty, i.reorder_level
FROM inventory i JOIN stores s ON i.store_id = s.store_id
WHERE i.stock_qty < i.reorder_level
ORDER BY (i.reorder_level - i.stock_qty) DESC;

📝 Case Study Practice Exercises

1. Zomato-style: Find the average order value per city.
2. Amazon-style: Find customers who have never placed an order (LEFT JOIN + IS NULL).
3. Netflix-style: Find users who haven't watched anything in the last 30 days (churn risk).
4. Uber-style: Find the top 3 drivers by total fare earned this month.
5. BigBasket-style: Find which store has the highest number of low-stock products.

End of Phase 4. Phase 5 will deliver the full Beginner/Intermediate/Advanced Projects, the 300 MySQL Interview Questions bank, all Cheat Sheets, and the Final Capstone Project.

1. Student Management System [Beginner]

Schema: students(student_id, name, course, fee, enrollment_date), attendance(student_id, class_date, status)

What You'll Practice: CREATE TABLE with constraints, JOIN, GROUP BY, date functions

List all students with attendance below 75%

SELECT s.name,
    ROUND(SUM(CASE WHEN a.status='Present' THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS attendance_pct
FROM students s JOIN attendance a ON s.student_id = a.student_id
GROUP BY s.name HAVING attendance_pct < 75;

2. Library Management System [Beginner]

Schema: books(book_id, title, author, copies_available), members(member_id, name), issued_books(book_id, member_id, issue_date, return_date)

What You'll Practice: FOREIGN KEY relationships, DATEDIFF for overdue tracking, LEFT JOIN

Find all currently overdue books (issued > 14 days, not yet returned)

SELECT b.title, m.name, DATEDIFF(CURDATE(), ib.issue_date) AS days_out
FROM issued_books ib
JOIN books b ON ib.book_id = b.book_id
JOIN members m ON ib.member_id = m.member_id
WHERE ib.return_date IS NULL AND DATEDIFF(CURDATE(), ib.issue_date) > 14;

3. Hospital Management System [Beginner]

Schema: patients(patient_id, name, age, admit_date), doctors(doctor_id, name, specialization), appointments(appointment_id, patient_id, doctor_id, appointment_date, fee)

What You'll Practice: Multi-table JOIN, Aggregate functions, GROUP BY with HAVING

Revenue generated per doctor specialization

SELECT d.specialization, SUM(a.fee) AS total_revenue
FROM appointments a JOIN doctors d ON a.doctor_id = d.doctor_id
GROUP BY d.specialization ORDER BY total_revenue DESC;

4. Employee Database [Beginner]

Schema: employees(employee_id, name, department, salary, join_date, manager_id)

What You'll Practice: SELF JOIN, Aggregate functions, Filtering

List each employee alongside their manager's name

SELECT e.name AS employee, m.name AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;

5. Retail Sales Analysis [Intermediate]

Schema: products(product_id, name, category, price), sales(sale_id, product_id, quantity, sale_date, store_id)

What You'll Practice: Window functions, GROUP BY multiple columns, Running totals

Running total of daily revenue

SELECT sale_date, SUM(quantity*price) AS daily_revenue,
    SUM(SUM(quantity*price)) OVER (ORDER BY sale_date) AS running_total
FROM sales s JOIN products p ON s.product_id = p.product_id
GROUP BY sale_date ORDER BY sale_date;

6. E-commerce Database [Intermediate]

Schema: customers(customer_id, name, city), orders(order_id, customer_id, order_date, total_amount)

What You'll Practice: Customer segmentation logic, CASE WHEN, Subqueries

Segment customers into High/Medium/Low value

SELECT customer_id, SUM(total_amount) AS total_spent,
    CASE WHEN SUM(total_amount) > 50000 THEN 'High'
         WHEN SUM(total_amount) > 15000 THEN 'Medium'
         ELSE 'Low' END AS segment
FROM orders GROUP BY customer_id;

7. HR Analytics Dashboard [Intermediate]

Schema: employees(employee_id, department, salary, join_date, exit_date)

What You'll Practice: Attrition rate calculation, Date functions, Conditional aggregation

Department-wise attrition rate

SELECT department,
    ROUND(SUM(CASE WHEN exit_date IS NOT NULL THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS attrition_rate
FROM employees GROUP BY department;

8. Customer Segmentation (RFM-style) [Intermediate]

Schema: orders(order_id, customer_id, order_date, amount)

What You'll Practice: Recency/Frequency/Monetary logic, Window functions, NTILE

Basic RFM scoring using NTILE

SELECT customer_id,
    NTILE(4) OVER (ORDER BY MAX(order_date) DESC) AS recency_score,
    NTILE(4) OVER (ORDER BY COUNT(*) DESC) AS frequency_score,
    NTILE(4) OVER (ORDER BY SUM(amount) DESC) AS monetary_score
FROM orders GROUP BY customer_id;

9. Inventory Dashboard [Intermediate]

Schema: products(product_id, name, stock_qty, reorder_level), stock_movements(product_id, change_qty, movement_date)

What You'll Practice: Views, Alerts via WHERE, Aggregation

Create a live low-stock alert view

CREATE VIEW low_stock_alert AS
SELECT name, stock_qty, reorder_level
FROM products WHERE stock_qty < reorder_level;

10. Restaurant Management System [Intermediate]

Schema: menu_items(item_id, name, category, price), orders(order_id, item_id, quantity, order_date, table_no)

What You'll Practice: JOIN + GROUP BY, Top-N queries, Time-based analysis

Top 5 best-selling menu items this month

SELECT m.name, SUM(o.quantity) AS units_sold
FROM orders o JOIN menu_items m ON o.item_id = m.item_id
WHERE o.order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY m.name ORDER BY units_sold DESC LIMIT 5;

D.1 Fundamentals & SQL Basics

D.2 Filtering, Functions & Joins

D.3 Subqueries, Views, Indexes, Constraints

D.4 Procedures, Triggers, Transactions

D.5 Window Functions & CTEs

D.6 Optimization, Security & Analytics

E.1 SQL Syntax Cheat Sheet

E.2 Functions Cheat Sheet

E.3 Join Cheat Sheet

E.4 Constraints Cheat Sheet

E.5 Window Functions Cheat Sheet

E.6 Query Optimization Cheat Sheet

• Index columns used in WHERE, JOIN, and ORDER BY
• Avoid SELECT * in production queries
• Never wrap an indexed column in a function inside WHERE
• Use EXPLAIN before optimizing blindly
• Normalize for transactional systems, denormalize for reporting/analytics

F.1 Suggested Capstone: ‘Samantus E-Learning Business Intelligence Project’

Students design a complete database for a training-institute-style business (or choose one of the 20 Phase 5 projects) and deliver the following components:

F.2 Evaluation Checklist

• Schema uses appropriate data types and constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, etc.)
• At least one JOIN, one subquery/CTE, and one window function used meaningfully
• Queries are readable, properly aliased, and commented
• Insights are backed directly by query results, not assumptions
• Final presentation is clear enough for a non-technical stakeholder to understand

G.1 Glossary of Key Terms

G.2 Certificate of Completion Template

This concludes the 5-Phase MySQL for Data Analytics Training Manual. Combine Phases 1-5 (in order) into one master document for your institute's complete 400+ page training book.