MySQL Roadmap

Phase 1: Basics of MySQL

1.Introduction to Databases

 What is a database?
 Types of databases (Relational vs NoSQL)
 What is MySQL? Use cases.

2.MySQL Installation & Setup

 Install MySQL (or use tools like XAMPP/WAMP or MySQL Workbench)

 Connect to MySQL server (CLI + GUI)

3.Basic SQL Commands

 CREATE DATABASE, USE

 CREATE TABLE, DESCRIBE
 INSERT, SELECT, UPDATE, DELETE
 Data types in MySQL (INT, VARCHAR, DATE, etc.)

4.Basic Queries

 SELECT * FROM table

 Filtering with WHERE
 Sorting with ORDER BY
 Limiting results with LIMIT

Phase 2: Intermediate SQL Concepts

1.Functions & Operators

 Aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX()

 String functions: CONCAT(), UPPER(), LOWER()
  Date functions: NOW(), DATE(), DATEDIFF()

2.Advanced Querying

 GROUP BY, HAVING

 DISTINCT
 Nested queries / Subqueries

3.Joins

 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

 Join conditions and aliases

4.Constraints

 PRIMARY KEY, FOREIGN KEY

 NOT NULL, UNIQUE, DEFAULT, AUTO_INCREMENT

Phase 3: Advanced SQL & MySQL Features

1.Views & Indexes

 Creating and using VIEWS

 Creating INDEXes for performance

2.Transactions

 ACID properties
 START TRANSACTION, COMMIT, ROLLBACK

3.Stored Procedures & Functions

 Creating and using stored procedures

 User-defined functions (UDFs)
 Variables and control flow (IF, CASE, WHILE)
4.Triggers & Events

 CREATE TRIGGER
 Scheduled events in MySQL

Phase 4: Real-World Use & Optimization

1.Performance Tuning

 Query optimization techniques

 Analyzing queries with EXPLAIN

2.Backup and Recovery

 mysqldump, mysqlimport
 Basic disaster recovery planning

3.Security

 User management: CREATE USER, GRANT, REVOKE

 Permissions and roles

Phase 5: Practice and Projects

1.Practice Ideas

 Library/book management system

 commerce database (Products, Users, Orders, etc.)
 Employee management system

2.Integrate with Other Tools

 Connect MySQL with Python using mysql-connector or SQLAlchemy

 Use with Power BI/Tableau for dashboard building
Phase 1: Basics of MySQL

1. INSERT – Adding Data to a Table

🔸 Syntax:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example:

Let's say we have a students table:

CREATE TABLE students (id INT, name VARCHAR (100), age INT, grade VARCHAR (10));

Now, insert a student:

INSERT INTO students (name, age, grade) VALUES ('Alice', 18, 'A');

2. SELECT – Retrieving Data

🔸 Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example:

SELECT name, age FROM students WHERE grade = 'A';

This retrieves the name and age of students with grade 'A'.

3. UPDATE – Modifying Existing Data

🔸 Syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Example:

Change Alice’s grade to 'B'

UPDATE students SET grade = 'B' WHERE name = 'Alice';

Warning: Always use WHERE in updates unless you're updating every row.

4. DELETE – Removing Data

🔸 Syntax:

DELETE FROM table_name WHERE condition;

Example:

Delete a student named Alice:

DELETE FROM students WHERE name = 'Alice';

Practice Exercises with Solutions

Let’s work with a books table:

CREATE TABLE books (book_id INT, title VARCHAR (100), author VARCHAR (100), price
DECIMAL (6,2), genre VARCHAR (50));

 Exercise 1: Insert Data

Task: Add the following books:

"Python Basics", John Smith, $29.99, "Programming"

"MySQL Mastery", Alice Brown, $39.50, "Databases”

✅ Solution:

INSERT INTO books (title, author, price, genre) VALUES ('Python Basics', 'John Smith',
29.99, 'Programming'), ('MySQL Mastery', 'Alice Brown', 39.50, 'Databases');

 Exercise 2: Select All Books in "Programming"

Task: Get all details of books in the "Programming" genre.

✅ Solution:

SELECT * FROM books WHERE genre = 'Programming';

 Exercise 3: Update a Price

Task: Change "Python Basics" price to 25.99.

✅ Solution:

UPDATE books SET price = 25.99 WHERE title = 'Python Basics';

 Exercise 4: Delete a Book

Task: Delete the book titled "MySQL Mastery".

✅ Solution:

DELETE FROM books WHERE title = 'MySQL Mastery';

 Exercise 5: Insert and Then Fetch High-Price Books

Task: Add "Advanced SQL", Mike Lee, $45.00, "Databases".

List all books with price above $30.

✅ Solution:

INSERT INTO books (title, author, price, genre) VALUES ('Advanced SQL', 'Mike Lee',
45.00, 'Databases');

SELECT * FROM books WHERE price > 30;

1. SELECT * FROM table – Retrieving All Columns

Explanation:

This command fetches all columns and rows from a table. The * is a wildcard that means
“everything.”
🔸 Syntax:

SELECT * FROM table_name;

Example:

If you have a table called employees:

SELECT * FROM employees;

2. WHERE – Filtering Rows Based on Conditions

Explanation:

WHERE lets you filter the rows based on one or more conditions.

🔸 Syntax:

SELECT * FROM table_name WHERE condition;

Common Conditions:

 Comparison: =, !=, <, >, <=, >=

 Logical: AND, OR, NOT
 Pattern: LIKE '%text%', IN (val1, val2), BETWEEN x AND y

Example:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

This will return employees from the "Sales" department who earn more than 50,000.

3. ORDER BY – Sorting the Result Set

Explanation:

ORDER BY sorts the results based on one or more columns.

🔸 Syntax:
SELECT * FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

 ASC = ascending (default)

 DESC = descending

🔸 Example:

SELECT * FROM employees ORDER BY salary DESC;

This will list employees from highest to lowest salary.

You can also sort by multiple columns:

SELECT * FROM employees ORDER BY department ASC, salary DESC;

4. LIMIT – Restricting the Number of Rows Returned

Explanation:

LIMIT restricts the number of rows returned by a query. Useful for pagination or testing.

🔸 Syntax:

SELECT * FROM table_name LIMIT number_of_rows;

Example:

SELECT * FROM employees LIMIT 5;

This returns only the first 5 rows from the employees table.

You can combine it with ORDER BY to get the "top N" results:

SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

This gives you the top 3 highest-paid employees.

🔄 Combined Example
SELECT name, department, salary FROM employees WHERE department = 'IT' AND salary >
40000 ORDER BY salary DESC LIMIT 2;

✅ What This Does:

Gets name, department, salary From employees in the IT department earning more than
40K Sorted from highest to lowest salary. Returns only the top 2 results.

Sample Table: employees

Id name department salary hire_date

1 Alice IT 60000 2021-06-01
2 Bob HR 50000 2022-01-15
3 Charlie IT 45000 2020-03-20
4 David Sales 70000 2019-11-10
5 Eva HR 52000 2021-08-05
6 Frank IT 48000 2023-04-18

1. Select all columns for all employees.

✅ Solution:

SELECT * FROM employees;

2. Select only the names and salaries of employees.

✅ Solution:

SELECT name, salary FROM employees;

3. Find all employees who work in the "HR" department.

✅ Solution:

SELECT * FROM employees WHERE department = 'HR';

4. Find all employees with a salary greater than 50,000.

✅ Solution:

SELECT * FROM employees WHERE salary > 50000;

5. Find employees in the "IT" department with a salary less than or equal to 50,000.

✅ Solution:

SELECT * FROM employees WHERE department = 'IT' AND salary <= 50000;

6. List all employees sorted by salary in descending order.

✅ Solution:

SELECT * FROM employees ORDER BY salary DESC;

7. List names of top 3 highest-paid employees.

✅ Solution:

SELECT name FROM employees ORDER BY salary DESC LIMIT 3;

8. Show the top 2 most recently hired employees.

✅ Solution:

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2;

9. Get the names and departments of employees whose names start with "A" or "E".

✅ Solution:

SELECT name, department FROM employees WHERE name LIKE 'A%' OR name LIKE 'E%';

10. Show employees who are not in the IT department.

✅ Solution:

SELECT * FROM employees WHERE department != 'IT';

What is DDL?
DDL (Data Definition Language) is a subset of SQL commands used to create, modify, and
delete database objects like tables, views, indexes, schemas, etc.

1. CREATE Command
Purpose:

 To create new tables, databases, views, etc.

Example: Create a Table:

create table employees (emp_id int primary key, emp_name varchar (100), department
varchar (50), salary decimal (10,2));

✅ This creates a table named employees with 4 columns.

Example: Create a Database

Create database company;

2. DROP Command
Purpose:

 To delete tables, databases, or other objects permanently.

Example: Drop a Table

drop table employees;

✅ This completely deletes the table employees and all its data.

Example: Drop a Database

drop database company;

✅ This deletes the entire database with all its tables.

⚠️ Warning:

 Once dropped, data cannot be recovered unless you have a backup!

 Always double-check before using DROP.

3. ALTER Command
Purpose:

 To modify an existing table — like adding, dropping, or modifying columns.

Example 1: Add a New Column

ALTER TABLE employees ADD date_of_joining DATE;

Example 2: Drop a Column

ALTER TABLE employees DROP COLUMN salary;

 Removes the salary column from employees.

Example 3: Modify a Column (Change Data Type)

ALTER TABLE employees MODIFY emp_name VARCHAR (150);

 Changes the data type of emp_name to VARCHAR (150).

Example 4: Rename a Column (MySQL 8+)

ALTER TABLE employees RENAME COLUMN department TO dept_name;

Example 5: Rename a Table

ALTER TABLE employees RENAME TO company_employees;

Major Data Type Categories in MySQL:

1. Numeric Types
2. String (Character) Types
3. Date and Time Types
4. [Spatial Types (Advanced)](Not covered here)
5. JSON and Other Types

1. Numeric Types
Used for storing numbers (integers, decimals, etc.).

Data Type Description Example Value Notes

TINYINT Very small integer (1 127 Range: -128 to 127
byte)
SMALLINT Small integer (2 32,000 Range: ±32K
bytes)
MEDIUMINT Medium integer (3 8,000,000 Range: ±8 million
bytes)
INT / INTEGER Standard integer (4 1,000,000 Range: ±2 billion
bytes)
BIGINT Large integer (8 9 quintillion Huge range
bytes)
DECIMAL(x,y) Fixed-point (exact), 12345.67 Use for precision
money, etc. (e.g., money)
FLOAT Approx. decimal 3.14 Slightly imprecise
(single precision)
 DOUBLE Double-precision 3.14159 More accurate than
float FLOAT
BIT Bit-field (binary) 1 or 0 Use for true/false or
flags
BOOLEAN Alias for TINYINT (1) 1 (true) 0 = false, 1 = true

✅ Tip: Use DECIMAL for money, not FLOAT or DOUBLE, due to rounding errors.

Example:

create table products (id int, name varchar (100), price decimal (8,2), quantity tinyint,
is_available boolean);

insert into products values (1,'Aleena',100.11,22,1);

select * from products;

2. String (Character) Types

Used for text, characters, binary data.

Data Type Description Example

CHAR(n) Fixed-length string CHAR (5) = 'abc '
VARCHAR(n) Variable-length string VARCHAR (255)
TEXT Large text block Blog post
TINYTEXT Up to 255 characters Short comment
MEDIUMTEXT Up to 16 million chars Long article
LONGTEXT Up to 4 billion chars Book or doc
ENUM One value from a set list ENUM ('male', 'female')
SET Multiple values from a set SET('A','B','C')
list
BLOB Binary Large Object Image, video, etc.

✅ Tip: Use VARCHAR for most normal text; TEXT for long entries.

Example:

create table customers (id int, name varchar (100), email varchar (100), gender enum
('Male', 'Female', 'other'), bio text);
insert into customers values (1,'Feba', '[email protected]', 'Female', 'This is premium
customer');

select * from customers;

3. Date and Time Types

Used for storing dates, times, and timestamps.

Data Type Description Format Example

DATE Date only YYYY-MM-DD '2023-04-25'
DATETIME Date and time YYYY-MM-DD '2023-04-25
HH:MM:SS 14:30:00'
TIMESTAMP Like DATETIME (with Same as above Auto-stores changes
auto update)
TIME Time only HH:MM:SS '14:30:00'
YEAR Year only YYYY '2025'

✅ Tip: Use TIMESTAMP for tracking changes and DATETIME for manually set times.

Example:

create table orders (order_id int, customer_name varchar (100), order_date datetime,
delivery_date date);

insert into orders values (101,'Feba','2020-01-03 14:30:00','2020-01-04');

select * from orders;

4.JSON and Other Special Types

Data Type Description Example

JSON Stores structured JSON data '{"name": "Alice"}'
GEOMETRY, POINT, etc. Spatial data types Used in GIS or maps

✅ Tip: Use JSON if you need to store nested structures (e.g., settings, preferences).
Example:

create table settings (user_id int, preferences json);

insert into settings values(110,'{"theme":"dark","lang":"en"}');

select * from settings;

Choosing the Right Data Type:

 For text, prefer VARCHAR over TEXT unless the data is very large.
 Use ENUM for fixed choices like 'Yes', 'No', or 'Pending', 'Approved'.
 Use the smallest numeric type that fits your range to save space.
 Always use DECIMAL for currency to avoid rounding issues.
 Use DATE or DATETIME based on whether you need time.

Phase 2: Intermediate SQL Concepts

What Are Constraints?

Constraints restrict the type of data that can go into a table column.

They help in:

 Preventing invalid data entries

 Enforcing business rules
 Maintaining relationships between tables

List of MySQL Constraints

Constraint Description
NOT NULL Column must have a value (cannot be
NULL)
UNIQUE All values in the column must be unique
PRIMARY KEY Combines NOT NULL and UNIQUE, used to
uniquely identify rows
FOREIGN KEY Enforces a link between two tables
CHECK Ensures values meet a specific condition
DEFAULT Assigns a default value if none is provided
AUTO_INCREMENT Automatically increases value for new rows
(usually IDs)
Detailed Explanations with Examples

1. NOT NULL

Prevents a column from having NULL values.

Example:

create table students (id int not null, name varchar (100) not null);

✅ Now id and name must always have a value.

2. UNIQUE

Ensures all values in a column are different.

Example:

create table employee (email varchar (100) unique);

✅ You can't have two employees with the same email.

3. PRIMARY KEY

A combination of NOT NULL + UNIQUE. Each table should have one.

Example:

create table product (product_id int primary key, name varchar (100));

✅ product_id will uniquely identify each row.

4. FOREIGN KEY

Creates a relationship between two tables by referencing the primary key of another table.

Example:

create table departments (id int primary key, name varchar (100));

create table emp (id int primary key, name varchar (100), dept_id int, foreign key (dept_id)
references departments(id));

✅ dept_id must match an existing id in departments.

5. CHECK
Ensures a column's value satisfies a condition.

Example:

Create table `order` (id int, quantity int check (quantity > 0));

✅ Only positive quantity values are allowed.

Note: CHECK is fully supported in MySQL 8.0 and above.

6.DEFAULT

Sets a default value for a column when none is provided.

Example:

create table users (id int, status varchar (20) default 'active');

✅ If you insert a user without specifying status, it defaults to 'active'.

7. AUTO_INCREMENT

Automatically increases the value of a numeric column.

Example:

create table student (id int primary key auto_increment, name varchar (100));

✅ You don’t need to insert id manually — MySQL handles it.

Real-Life Example:

create table customer (id int primary key auto_increment, name varchar (100) not null,
email varchar (100) unique, age int check(age>=18), country varchar (50) default 'India');

✅ Summary Table

Constraint Purpose

NOT NULL Prevent empty values

 UNIQUE Prevent duplicate entries

PRIMARY KEY Uniquely identify each row

FOREIGN KEY Link data between tables

CHECK Allow only values that meet a condition

DEFAULT Provide default values

AUTO_INCREMENT Auto-generate numeric values

Practice Tasks: Constraints in MySQL

🔹 Task 1: Create a Students Table

Objective: Use PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT.

✅ Create a table students with the following columns:

Column Type Constraints

id INT Primary key, auto-increment

name VARCHAR (100) Not null

email VARCHAR (100) Unique

age INT Must be at least 18

status VARCHAR (20) Default: 'active'

create table students (id int primary key auto_increment, name varchar (100) not null, email
varchar (100) unique, age int check (age>=18), status varchar (20) default 'Active');

🔹 Task 2: Insert Sample Data

Objective: Try violating constraints and observe errors.

✅ Insert valid data:

insert into students (name, email, age) values ('Alice', '[email protected]',25);

insert into students (name, email, age) values ('Bob', '[email protected]',27);

select * from students;

❌ Try inserting a student without a name:

insert into students (email, age) values ('[email protected]',19);

⚠️ Should give error: Column 'name' cannot be null

❌ Try inserting someone younger than 18:

insert into students (name, email, age) values ('Tom', '[email protected]',16);

⚠️ Error from CHECK constraint.

🔹 Task 3: Create a Departments Table with Foreign Key

Objective: Use FOREIGN KEY to link students to departments.

✅ Create departments table:

create table departments (dept_id int primary key, dept_name varchar (100));

✅ Alter students table to add foreign key:

What is a Primary Key?

A Primary Key is a column (or set of columns) in a table that:

 Uniquely identifies each row

 Cannot be NULL

 Cannot have duplicate values

Key Characteristics:

Feature Description

Unique Every value must be different

Not Null Cannot be left empty

One per table Each table can have only one primary key

Can be composite It can be made up of multiple columns

Example:
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR (100), position
VARCHAR (100));

 emp_id is the primary key

 It uniquely identifies every employee
 No two rows can have the same emp_id
 emp_id cannot be NULL

✅ Inserting Valid Data

INSERT INTO employees (emp_id, name, position) VALUES (101, 'Alice', 'Manager'), (102,
'Bob', 'Analyst');

AUTO_INCREMENT with Primary Key

For auto-numbering your primary key (useful in most cases):

CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (100), age
INT);

Now you can insert without specifying the id:

INSERT INTO students (name, age) VALUES ('Raj', 22);

Composite Primary Key (Multi-column key)

A composite primary key is a primary key that consists of two or more columns in a database
table. These columns together uniquely identify each row in the table. None of the individual
columns in a composite key can uniquely identify a row on its own; the uniqueness is only
guaranteed when the combination of all key columns is considered.

Use this when no single column uniquely identifies a row.

CREATE TABLE course_enrollments (student_id INT, course_id INT, enrollment_date DATE,

PRIMARY KEY (student_id, course_id));

Here:

 A student can enroll in many courses

 A course can have many students

 But the combination of student_id and course_id must be unique

Exercises

Task 1: Create a books table with a primary key

Requirements:

 book_id (primary key, auto-increment)

 title (not null)

 author

 isbn (unique)

CREATE TABLE books (book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR (200)
NOT NULL, author VARCHAR (100), isbn VARCHAR (20) UNIQUE);

INSERT INTO books (title, author, isbn) VALUES ('The Alchemist', 'Paulo Coelho',
'9788172234980');

Task 2: Create a table with composite primary key

Create a grades table:

 student_id

 subject_code

 score

 Composite primary key = (student_id, subject_code)

CREATE TABLE grades (student_id INT, subject_code VARCHAR (10), score INT, PRIMARY KEY
(student_id, subject_code));

INSERT INTO grades (student_id, subject_code, score) VALUES (1, 'MATH101', 85), (1,
'ENG102', 90);

What is a Foreign Key?

A foreign key is a field (or collection of fields) in one table that refers to the primary key in
another table.

✅ It creates a link between the two tables

Why Use Foreign Keys?

 To enforce valid relationships between tables

  To prevent orphan records (e.g., orders without customers)

 To cascade changes or deletions (optional)

Basic Syntax:

FOREIGN KEY (column_name) REFERENCES parent_table (parent_column)

Example

Step 1: Create the Parent Table

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR (100));

Step 2: Create the Child Table with FOREIGN KEY

CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR (100), dept_id
INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id));

Now you cannot insert an employee with a dept_id that doesn’t exist in departments.

Practice Exercises

✅ Task 1: Create Two Related Tables

categories (parent)

CREATE TABLE categories (id INT PRIMARY KEY, name VARCHAR (100));

products (child)

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR (100), category_id INT,
FOREIGN KEY (category_id) REFERENCES categories(id));

✅ Task 2: Insert Valid Data

INSERT INTO categories VALUES (1, 'Electronics'), (2, 'Books');

INSERT INTO products VALUES (101, 'Laptop', 1), (102, 'Novel', 2);

Table Relationships in MySQL

In databases, table relationships define how data in one table is associated with data in
another. They are crucial for maintaining data integrity and reducing redundancy.
There are 3 major types of relationships between tables:

Relationship Type Description

✅ One-to-One One row in Table A ↔ one row in Table B (rare)

✅ One-to-Many One row in Table A ↔ many rows in Table B

✅ Many-to-Many Many rows in Table A ↔ many rows in Table B

1. One-to-One Relationship (1:1)

Each row in Table A is linked to exactly one row in Table B.

🟩 Example:

 Table User: user_id, name

 Table UserProfile: user_id, bio

Each user has one profile.

CREATE TABLE User (user_id INT PRIMARY KEY, name VARCHAR (100));

CREATE TABLE UserProfile (user_id INT PRIMARY KEY, bio TEXT, FOREIGN KEY (user_id)
REFERENCES User(user_id));

✅ Insert Data:

INSERT INTO User (user_id, name) VALUES (1, 'Alice Johnson'), (2, 'Bob Smith'), (3, 'Charlie
Davis');

INSERT INTO UserProfile (user_id, bio) VALUES (1, 'Software engineer with a passion for AI
and machine learning.'), (2, 'Freelance graphic designer and digital artist.'), (3, 'Student of
data science, loves Python and statistics.');

2. One-to-Many Relationship (1:N)

Example: A department can have many employees, but an employee belongs to only one
department.

Tables:

departments (one side)

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR (100));

employees (many side)

CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR (100), dept_id
INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id));
✅ Insert Data:

INSERT INTO departments VALUES (1, 'HR'), (2, 'IT');

INSERT INTO employees VALUES (101, 'Alice', 1), (102, 'Bob', 1), (103, 'Charlie', 2);

This models a one-to-many relationship (1 department → many employees).

3. Many-to-Many Relationship (M:N)

Example: A student can enroll in many courses, and a course can have many students.

MySQL doesn’t directly support many-to-many relationships — we solve it using a junction

(bridge) table.

Tables:

students

CREATE TABLE students (student_id INT PRIMARY KEY, name VARCHAR(100));

Courses

CREATE TABLE courses (course_id INT PRIMARY KEY, course_name VARCHAR(100));

✅ Junction Table: enrollments

This will link students and courses:

CREATE TABLE enrollments (student_id INT, course_id INT, PRIMARY KEY (student_id,
course_id), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY
(course_id) REFERENCES courses(course_id));

✅ Insert Sample Data:

INSERT INTO students VALUES (1, 'John'), (2, 'Emma');

INSERT INTO courses VALUES (101, 'Math'), (102, 'Science');

-- Many-to-Many: Enroll students in multiple courses

INSERT INTO enrollments VALUES (1, 101), (1, 102), (2, 101);

✔️ This models a many-to-many relationship:

 John is enrolled in Math & Science

 Emma is enrolled in Math

 Math has both John and Emma

Practice Tasks

✅ Task 1: One-to-Many

Create:

 authors (author_id, name)

 books (book_id, title, author_id)

CREATE TABLE authors (author_id INT PRIMARY KEY, name VARCHAR(100));

CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(200), author_id INT,
FOREIGN KEY (author_id) REFERENCES authors(author_id));

Insert:

INSERT INTO authors VALUES (1, 'Paulo Coelho'), (2, 'J.K. Rowling');

INSERT INTO books VALUES (101, 'The Alchemist', 1), (102, 'Harry Potter', 2), (103, 'Brida', 1);

✅ Task 2: Many-to-Many

Create:

 doctors (doctor_id, name)

 patients (patient_id, name)

 appointments (junction table: doctor_id + patient_id)

CREATE TABLE doctors (doctor_id INT PRIMARY KEY, name VARCHAR (100));

CREATE TABLE patients (patient_id INT PRIMARY KEY, name VARCHAR (100));

CREATE TABLE appointments (doctor_id INT, patient_id INT, PRIMARY KEY (doctor_id,
patient_id), FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id), FOREIGN KEY
(patient_id) REFERENCES patients(patient_id));

Insert:

INSERT INTO doctors VALUES (1, 'Dr. Smith'), (2, 'Dr. Lee');

INSERT INTO patients VALUES (101, 'Tom'), (102, 'Jerry');

INSERT INTO appointments VALUES (1, 101), (1, 102), (2, 101);
What Are Aggregate Functions?5
Aggregate functions perform a calculation on a set of values and return a single summary
value.

Common aggregate functions include:

 COUNT() – Count rows

 SUM() – Total of values

 AVG() – Average of values

 MIN() – Minimum value

 MAX() – Maximum value

Sample Table: orders

order_id customer amount

1 John 100

2 Emma 250

3 John 300

4 Alice 150

5 Emma 200

1. COUNT(): Count Rows

SELECT COUNT (*) AS total_orders FROM orders;

Result: 5
Counts all rows in the orders table.

2. SUM(): Total of a Column

SELECT SUM (amount) AS total_sales FROM orders;

Result: 1000
Adds up all values in the amount column.

3. AVG(): Average of a Column

SELECT AVG (amount) AS avg_order_value FROM orders;

Result: 200.00
Calculates the average amount of all orders.

4. MIN(): Smallest Value

SELECT MIN (amount) AS smallest_order FROM orders;

Result: 100
✔️ Returns the minimum value in the amount column.

5. MAX(): Largest Value

SELECT MAX (amount) AS largest_order FROM orders;

Result: 300
✔️ Returns the maximum value.

GROUP BY + Aggregate Functions

You can use aggregate functions with GROUP BY to get summaries per group.

Example: Total amount per customer

SELECT customer, SUM (amount) AS total_per_customer FROM orders GROUP BY customer;

Result:

customer total_per_customer

John 400

Emma 450

Alice 150

Filtering with HAVING

Unlike WHERE (used before grouping), HAVING is used after aggregation.

Example: Customers with total > 300

SELECT customer, SUM(amount) AS total_spent FROM orders GROUP BY customer HAVING

SUM(amount) > 300;

Result:
 customer total_spent

John 400

Emma 450

Practice Tasks

✅ Task 1: Total number of orders

SELECT COUNT(*) FROM orders;

✅ Task 2: Total sales amount

SELECT SUM(amount) FROM orders;

✅ Task 3: Average order amount

SELECT AVG(amount) FROM orders;

✅ Task 4: Highest and lowest order

SELECT MAX(amount) AS highest, MIN(amount) AS lowest FROM orders;

✅ Task 5: Total sales per customer

SELECT customer, SUM(amount) FROM orders GROUP BY customer;

Summary Table:

Function Description

COUNT() Total number of rows

SUM() Adds all values

AVG() Returns average value

MIN() Returns smallest value

MAX() Returns largest value

DISTINCT – Remove Duplicates

Purpose:

DISTINCT is used to return only unique (non-duplicate) values in a column or set of columns.
Example Table: students

id name course

1 Alice Math

2 Bob Science

3 Alice Math

4 Charlie History

Basic Usage:

SELECT DISTINCT name FROM students;

✅ Result:

Alice

Bob

Charlie

✔️ Duplicates are removed — Alice appears only once.

Multiple Columns:

SELECT DISTINCT name, course FROM students;

✅ Keeps rows only if both name and course values are unique.

Subqueries (Nested Queries)

Purpose:

A subquery is a SQL query inside another query. It's often used to:

 Filter using results from another query

 Compare values

 Calculate derived values

Subquery in WHERE Clause

Example Table: orders
 order_id customer amount

1 Alice 200

2 Bob 300

3 Charlie 150

Task: Find customers with the highest order amount

SELECT customer FROM orders WHERE amount = (SELECT MAX(amount) FROM orders);

Result:

Bob

Subquery in FROM Clause (Derived Table)

SELECT AVG(amount) AS avg_order FROM (SELECT amount FROM orders WHERE customer =
'Alice') AS alice_orders;

This gets the average order value for Alice.

Subquery in SELECT Clause

SELECT customer,(SELECT MAX(amount) FROM orders) AS max_order FROM orders;

Adds the max amount as an extra column in every row.

Practice Tasks

✅ Task 1: Find all unique courses from students table

SELECT DISTINCT course FROM students;

✅ Task 2: Find the second-highest order amount from orders

SELECT MAX(amount) AS second_highest FROM orders WHERE amount < (SELECT

MAX(amount) FROM orders);

✅ Task 3: Get names of customers whose order amount is greater than the average

SELECT customer FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);

Summary Table:

Concept Description Example

 DISTINCT Remove duplicate rows SELECT DISTINCT course FROM students;

Subquery Query inside another query WHERE amount = (SELECT MAX(...))

String functions
 CONCAT()
 UPPER()
 LOWER()

These are super helpful for formatting, transforming, and combining text data.

1. CONCAT() – Combine Strings

Purpose:

Joins two or more strings into one.

Syntax:

CONCAT(string1, string2, ..., stringN)

Example:

SELECT CONCAT('Hello', ' ', 'World') AS greeting;

-- Output: Hello World

With Table:

Assume a students table:

id first_name last_name
1 John Doe
2 Emma Smith

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM students;

Result:

full_name
John Doe
Emma Smith
2. UPPER() – Convert to Uppercase

Purpose:

Converts a string to uppercase.

Example:

SELECT UPPER('hello world') AS upper_text;

-- Output: HELLO WORLD

With Table:

SELECT UPPER(first_name) AS first_upper FROM students;

3. LOWER() – Convert to Lowercase

Purpose:

Converts a string to lowercase.

Example:

SELECT LOWER('HELLO WORLD') AS lower_text;

-- Output: hello world

With Table:

SELECT LOWER(last_name) AS last_lower FROM students;

Practice Tasks

✅ Task 1: Combine first and last names

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM students;

✅ Task 2: Make all names uppercase

SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS upper_name FROM students;

✅ Task 3: Convert last names to lowercase

SELECT LOWER(last_name) AS lower_last FROM students;

Summary Table

Function Description Example

 CONCAT() Joins multiple strings CONCAT('A', 'B') → 'AB'

UPPER() Converts text to uppercase UPPER('abc') → 'ABC'

LOWER() Converts text to lowercase LOWER('XYZ') → 'xyz'

Date functions:
 NOW()
 DATE()
 DATEDIFF()

These are very useful when you're dealing with records that include dates like birthdays,
sign-up times, due dates, etc.

1. NOW() – Current Date and Time

Purpose:

Returns the current date and time (timestamp).

Syntax:

SELECT NOW();

✅ Example Output:

2025-05-01 14:30:45

Use Case:

Insert a record with the current timestamp:

INSERT INTO orders (customer_name, order_date) VALUES ('John Doe', NOW());

2. DATE() – Extract Date Only

Purpose:

Extracts just the date part (YYYY-MM-DD) from a full datetime.

Syntax:

SELECT DATE(NOW());

✅ Example Output:

2025-05-01
Use Case:

If you want to ignore time and only compare dates:

SELECT * FROM orders WHERE DATE(order_date) = '2025-05-01';

3. DATEDIFF() – Difference Between Two Dates

Purpose:

Returns the number of days between two dates.

Syntax:

DATEDIFF(date1, date2)

-- Returns: date1 - date2 in days

✅ Example:

SELECT DATEDIFF('2025-05-10', '2025-05-01') AS days_between;

-- Output: 9

Use Case:

Find overdue invoices:

SELECT invoice_id, due_date, DATEDIFF(NOW(), due_date) AS days_late FROM invoices

WHERE NOW() > due_date;

Practice Tasks

Assume a table members:

member_id name join_date

1 Alice 2023-04-15

2 Bob 2024-12-01

✅ Task 1: Show current date and time

SELECT NOW() AS current_timestamp;

✅ Task 2: Extract only the date from a datetime column

SELECT name, DATE(join_date) AS join_only_date FROM members;

✅ Task 3: Calculate days since each member joined

SELECT name, join_date, DATEDIFF(NOW(), join_date) AS days_since_joined FROM
members;

Summary Table

Function Description Example

NOW() Current date and time NOW() → '2025-05-01 14:32:10'

DATE() Extract date part from datetime DATE(NOW()) → '2025-05-01'

DATEDIFF() Difference in days between two DATEDIFF('2025-05-10', '2025-05-01')

dates →9

JOINs in MySQL
Combining Data Across Tables

JOINs are used to combine rows from two or more tables based on a related column. This is
essential for working with relational databases.

Suppose we have two tables:

Customers

customer_id name

1 Alice

2 Bob

3 Charlie

Orders

order_id customer_id product

101 1 Laptop

102 2 Keyboard

103 1 Monitor
1. INNER JOIN – Matching Records Only
Returns only records that match in both tables.

SELECT customers.name, orders.product FROM customers INNER JOIN orders ON

customers.customer_id = orders.customer_id;

✅ Result:

name product

Alice Laptop

Bob Keyboard

Alice Monitor

2. LEFT JOIN – All from Left, Matches from Right

Returns all customers, even if they don’t have orders.

SELECT customers.name, orders.product FROM customers LEFT JOIN orders ON

customers.customer_id = orders.customer_id;

✅ Result:

name product

Alice Laptop

Bob Keyboard

Alice Monitor

Charlie NULL

3. RIGHT JOIN – All from Right, Matches from Left

Returns all orders, even if the customer doesn't exist (rare in practice).

4. FULL JOIN – All from Both (Not in MySQL directly)

MySQL doesn’t support FULL JOIN directly, but you can simulate it with UNION.

Practice Tasks

✅ Task 1: List all orders with customer names (use INNER JOIN)
SELECT customers.name, orders.product FROM customers INNER JOIN orders ON
customers.customer_id = orders.customer_id;

✅ Task 2: List all customers and their orders (include those with no orders)

SELECT customers.name, orders.product FROM customers LEFT JOIN orders ON

customers.customer_id = orders.customer_id;

✅ Task 3: Show customers who did not place any orders

SELECT customers.name FROM customers LEFT JOIN orders ON customers.customer_id =

orders.customer_id WHERE orders.order_id IS NULL;

Employees

emp_id name dept_id

1 Alice 10

2 Bob 20

3 Charlie 10

Departments

dept_id dept_name

10 HR

20 IT

30 Marketing

1. JOIN Condition

A JOIN condition specifies how rows in one table match rows in another.

🔹 INNER JOIN with a condition:

SELECT employees.name, departments.dept_name FROM employees JOIN departments

ON employees.dept_id = departments.dept_id;

This connects each employee to their department based on dept_id.

2. Using Table Aliases

Aliases help shorten table names and make queries more readable, especially with long
table names or self-joins.

SELECT e.name, d.dept_name FROM employees AS e JOIN departments AS d ON e.dept_id =

d.dept_id;

 e is alias for employees

 d is alias for departments

Example with Aliases and Filtering

SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id =

d.dept_id WHERE d.dept_name = 'HR';

Result:

name dept_name

Alice HR

Charlie HR

Practice Tasks

✅ Task 1: Show employee names and their department names (use aliases)

SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id =

d.dept_id;

✅ Task 2: List employees working in the 'IT' department

SELECT e.name FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE

d.dept_name = 'IT';

✅ Task 3: Use aliases to display full info for each employee and department

SELECT e.emp_id, e.name AS employee_name, d.dept_name AS department FROM

employees e JOIN departments d ON e.dept_id = d.dept_id;

Summary

Feature Use

JOIN condition Matches rows between tables

Alias Simplifies and shortens table/column names

AS Used to define an alias (table AS alias)

Phase 3: Advanced SQL & MySQL Features

1. VIEWS in MySQL
✅ What is a VIEW?

A VIEW is a virtual table based on the result of a SQL query.

It doesn't store data itself — it just shows data from one or more tables.

🔹 Why use VIEWS?

 Simplify complex queries

 Reuse queries easily

 Restrict user access to specific columns

 Make reporting easier

🟩 Example Tables

orders

order_id customer_name product quantity price

1 Alice Laptop 1 1000

2 Bob Mouse 2 25

3 Alice Keyboard 1 45

Creating a VIEW

🔸 Create a view of total sales per order:

CREATE VIEW order_summary AS SELECT order_id, customer_name, product, quantity, price,

quantity * price AS total_price FROM orders;

✅ Now you can use it like a table:

SELECT * FROM order_summary;

Modifying a VIEW

CREATE OR REPLACE VIEW order_summary AS SELECT customer_name, SUM (quantity *

price) AS total_spent FROM orders GROUP BY customer_name;
Dropping a VIEW

DROP VIEW IF EXISTS order_summary;

Practice Tasks for VIEWS

✅ Task 1: Create a view customer_totals that shows total spent by each customer

CREATE VIEW customer_totals AS SELECT customer_name, SUM (quantity * price) AS

total_spent FROM orders GROUP BY customer_name;

✅ Task 2: Use the view to get all customers who spent over $100

SELECT * FROM customer_totals WHERE total_spent > 100;

2. INDEXES in MySQL
✅ What is an INDEX?

An index improves the speed of data retrieval from a table, similar to an index in a book.

Why use INDEXES?

 Speed up SELECT queries

 Useful for searching, sorting, joins

 Slows down INSERT, UPDATE, DELETE a little (due to maintenance cost)

🔸 Example Table: users

id username email

1 alice [email protected]

2 bob [email protected]

Creating an INDEX

🔸 Index on a single column:

CREATE INDEX idx_username ON users(username);

Viewing Indexes

SHOW INDEX FROM users;

Dropping an Index

DROP INDEX idx_username ON users;

Practice Tasks for INDEXES

✅ Task 1: Add an index to the customer_name in the orders table

CREATE INDEX idx_customer_name ON orders(customer_name);

✅ Task 2: Run a query to test the performance improvement

SELECT * FROM orders WHERE customer_name = 'Alice';

Summary Table

Feature Purpose Example

VIEW Virtual table from a query CREATE VIEW name AS SELECT ...

INDEX Speed up search/read operations CREATE INDEX idx ON table(column)

What Is a Transaction in MySQL?

A transaction is a group of one or more SQL statements that are executed as a single unit of
work. Either all succeed (COMMIT), or none happen (ROLLBACK).

ACID Properties
These are the four key principles that define a reliable transaction:

Property Meaning

Atomicity All operations succeed or none (no partial transactions)

Consistency Data remains valid before and after the transaction

Isolation Transactions don't affect each other (intermediate states hidden)

Durability Once committed, the result stays even in case of crash or power loss

3. Key Transaction Commands

🔹 START TRANSACTION

Begins a new transaction block.

🔹 COMMIT

Saves all the changes permanently.

🔹 ROLLBACK

Cancels the transaction and undoes all changes made after START TRANSACTION.

Example Table: accounts

account_id name balance

1 Alice 500

2 Bob 300

Scenario: Transfer $100 from Alice to Bob

START TRANSACTION;

UPDATE accounts SET balance = balance – 100 WHERE name = 'Alice';

UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

COMMIT;

✅ Both updates succeed together. If something fails between them, we use:

ROLLBACK;

✔️ This undoes both updates and leaves balances unchanged.

Practice Tasks

✅ Task 1: Simulate a failed money transfer and use ROLLBACK

START TRANSACTION;

UPDATE accounts SET balance = balance – 200 WHERE name = 'Bob';

-- Suppose an error occurs here (e.g., divide by zero or incorrect account)

ROLLBACK;

✔️ This will cancel the entire transaction — Bob’s balance won’t be deducted.

✅ Task 2: Use COMMIT for a successful transaction

START TRANSACTION;

UPDATE accounts SET balance = balance - 50 WHERE name = 'Alice';

UPDATE accounts SET balance = balance + 50 WHERE name = 'Bob';

COMMIT;

Summary

Command Description

START TRANSACTION Begins a transaction

COMMIT Saves changes permanently

ROLLBACK Undoes all changes since start

ACID Guarantees safe and reliable transactions

TRIGGERS in MySQL
What is a Trigger?

A Trigger is a block of SQL code that automatically executes in response to a specific event
on a table — like an INSERT, UPDATE, or DELETE.

🔹 Syntax:

CREATE TRIGGER trigger_name [BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON

table_name FOR EACH ROW BEGIN – statements END;

Example Table: accounts

CREATE TABLE account (account_id INT PRIMARY KEY,name VARCHAR(100),balance INT);

🔹 Create a log table:

CREATE TABLE account_log (log_id INT AUTO_INCREMENT PRIMARY KEY,action

VARCHAR(20),name VARCHAR(100),balance INT,log_time TIMESTAMP DEFAULT
CURRENT_TIMESTAMP);

Insert data to accounts:

Acc_id name Balance

1 Alice 50000

2 Bob 60000

3 Charlie 15000
🔸 Create Trigger to log updates:

DELIMITER $$

CREATE TRIGGER after_acco_up AFTER UPDATE ON account FOR EACH ROW

BEGIN

INSERT INTO account_log (action, name, balance) VALUES ('UPDATE', NEW.name,

NEW.balance);

END$$

DELIMITER ;

✅ This trigger runs after each salary update and logs the change.

Perform an Update

Let’s say we increase Bob’s balance:

UPDATE account SET balance = 70000 WHERE name = 'Bob';

View Trigger Output in account_log:

SELECT * FROM account_log;

log_id action name balance log_time

1 UPDATE Bob 70000 2025-05-02 10:30:00

Update Another Account

UPDATE account SET balance = 30000 WHERE name = 'Charlie';

SELECT * FROM account_log;

log_id action name balance log_time

1 UPDATE Bob 70000 2025-06-07 10:30:00

2 UPDATE Charlie 30000 2025-06-07 10:36:12

Practice Problem:

Create the account2 Table

CREATE TABLE account2 (account_id INT PRIMARY KEY,name VARCHAR(100),balance INT);

Create the Trigger

DELIMITER $$
CREATE TRIGGER before_insert_account2 BEFORE INSERT ON account2 FOR EACH ROW

BEGIN

SET NEW.name = UPPER(NEW.name);

END$$

DELIMITER ;

This trigger converts the name to uppercase before inserting it into the table.

Insert Some Sample Data

INSERT INTO account2 (account_id, name, balance) VALUES(1, 'alice', 1200),(2, 'Bob',
1400),(3, 'ChARLiE', 1600);

Check the Table

SELECT * FROM account2;

account_id name balance

1 ALICE 1200

2 BOB 1400

3 CHARLIE 1600

Scheduled EVENTS in MySQL

✅ What is a Scheduled Event?

An event is a scheduled task that runs automatically at a specific time or interval, like a cron
job in Linux.

🔹 Enabling Events:

SET GLOBAL event_scheduler = ON;

Example: Auto-archive old records

Suppose you have a table logs with timestamps, and you want to delete logs older than 30
days.

CREATE EVENT delete_old_logs ON SCHEDULE EVERY 1 DAY DO DELETE FROM logs WHERE
log_time < NOW () - INTERVAL 30 DAY;

✅ This event runs once per day to delete old logs.

🔸 One-time Event

CREATE EVENT run_once ON SCHEDULE AT '2025-05-10 10:00:00' DO UPDATE tasks SET

status = 'expired' WHERE deadline < NOW ();

Practice Tasks

✅ Task 1: Create an event to update inactive users every week

CREATE EVENT mark_inactive ON SCHEDULE EVERY 1 WEEK DO UPDATE users SET status =
'inactive' WHERE last_login < NOW() - INTERVAL 30 DAY;

✅ Task 2: Create a trigger that logs deleted employee records

CREATE TABLE employee_deletions (id INT, name VARCHAR (100), deleted_at DATETIME);

CREATE TRIGGER log_employee_delete AFTER DELETE ON employees FOR EACH ROW BEGIN

INSERT INTO employee_deletions (id, name, deleted_at) VALUES (OLD.id, OLD.name, NOW ());

END;

Summary Table

Feature Purpose Example

TRIGGER Auto-execute code on data changes AFTER UPDATE ON table

EVENT Auto-execute code on a schedule EVERY 1 DAY, AT 'YYYY-MM-DD HH:MM'

SIGNAL Used inside trigger to raise error SIGNAL SQLSTATE '45000'

Employee Audit & Cleanup System

Objective:

Build a system that:

1. Tracks changes to employee salaries using a trigger

2. Logs deleted employees using a trigger

3. Auto-cleans audit records older than 90 days using a scheduled event

Step-by-Step Implementation

Step 1: Create Main Table — employees

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (100),
position VARCHAR (50), salary DECIMAL (10,2));
📘 Sample Data:

INSERT INTO employees (name, position, salary) VALUES ('Alice', 'Manager', 60000), ('Bob',
'Analyst', 50000), ('Charlie', 'Developer', 55000);

Step 2: Create Salary Audit Table — salary_audit

CREATE TABLE salary_audit (audit_id INT AUTO_INCREMENT PRIMARY KEY, emp_id INT,

old_salary DECIMAL (10,2), new_salary DECIMAL (10,2), changed_at DATETIME);

Step 3: Trigger to Track Salary Changes

CREATE TRIGGER trg_salary_update AFTER UPDATE ON employees FOR EACH ROW BEGIN IF
OLD.salary <> NEW.salary THEN INSERT INTO salary_audit (emp_id, old_salary, new_salary,
changed_at) VALUES (OLD.id, OLD.salary, NEW.salary, NOW ());

END IF;

END;

Step 4: Create Table for Deleted Employees

CREATE TABLE deleted_employees (id INT, name VARCHAR (100), position VARCHAR (50),
salary DECIMAL (10,2), deleted_at DATETIME);

Step 5: Trigger to Log Deletions

CREATE TRIGGER trg_log_deletion AFTER DELETE ON employees FOR EACH ROW BEGIN

INSERT INTO deleted_employees (id, name, position, salary, deleted_at) VALUES (OLD.id,
OLD.name, OLD.position, OLD.salary, NOW ());

END;

Step 6: Scheduled Event to Clean Old Audit Logs

First, enable event scheduler:

SET GLOBAL event_scheduler = ON;

Then, create the cleanup event:

CREATE EVENT cleanup_old_audit_logs ON SCHEDULE EVERY 1 DAY DO

DELETE FROM salary_audit WHERE changed_at < NOW() - INTERVAL 90 DAY;

Update Salary:
UPDATE employees SET salary = 62000 WHERE name = 'Bob';

Check salary_audit for log

Delete an Employee:

DELETE FROM employees WHERE name = 'Charlie';

Check deleted_employees for log

Summary

Feature Table Purpose

Trigger 1 salary_audit Tracks salary changes

Trigger 2 deleted_employees Logs deleted employee records

Scheduled Event salary_audit Deletes records older than 90 days