1.
Introduction to MySQL (30 minutes)
Overview of MySQL: What is MySQL, its uses, and the difference between MySQL and
other database management systems (DBMS).
Setting up MySQL: Installing MySQL and MySQL Workbench (if needed).
Basic MySQL Commands:
o Connecting to MySQL using the command line or GUI tools.
o Basic commands: SHOW DATABASES;, USE database_name;, SHOW
TABLES;, etc.
2. Database and Table Design (45 minutes)
Creating Databases and Tables:
o Basic syntax for creating databases: CREATE DATABASE database_name;
o Creating tables with appropriate data types (e.g., INT, VARCHAR, DATE,
FLOAT, etc.).
o Primary keys and foreign keys.
o Constraints: NOT NULL, UNIQUE, CHECK, etc.
Hands-on Exercise:
o Create a database and tables for a small application (e.g., an employee
management system, library database).
3. CRUD Operations (45 minutes)
Insert Data:
o INSERT INTO table_name (column1, column2, ...) VALUES (value1,
value2, ...);
Select Data:
o SELECT * FROM table_name;
o SELECT column_name FROM table_name WHERE condition;
o Sorting and filtering data using ORDER BY, LIMIT, and WHERE.
Update Data:
o UPDATE table_name SET column_name = value WHERE condition;
Delete Data:
o DELETE FROM table_name WHERE condition;
Hands-on Exercise:
o Perform CRUD operations on the previously created tables.
o Modify data based on specific requirements (e.g., update employee salaries, delete
outdated records).
4. Joins and Relationships (45 minutes)
Types of Joins:
o Inner Join: Combining rows from two tables where conditions are met.
o Left Join: Getting all rows from the left table and matched rows from the right
table.
o Right Join: Getting all rows from the right table and matched rows from the left
table.
o Full Outer Join: Combining rows from both tables, even if no match exists.
Using Aliases: Naming tables and columns for easier readability.
Understanding Relationships: One-to-many, many-to-many, and one-to-one
relationships.
Hands-on Exercise:
o Write queries involving multiple tables using joins.
o Combine data from employees, departments, and salaries tables to retrieve
meaningful insights (e.g., employee details with department names).
5. Aggregate Functions and Grouping (30 minutes)
Aggregate Functions:
o COUNT(), SUM(), AVG(), MAX(), MIN().
GROUP BY Clause: Grouping data based on a column and applying aggregate
functions.
HAVING Clause: Filtering aggregated results.
Hands-on Exercise:
o Calculate total salary by department, find average age of employees, or determine
the highest-paid employee in each department.
6. Subqueries and Nested Queries (30 minutes)
What are Subqueries: A query inside another query.
Types of Subqueries:
o Scalar Subqueries: Returning a single value.
o Column Subqueries: Returning multiple columns.
o Row Subqueries: Returning a set of rows.
o Correlated Subqueries: Subqueries that reference columns from the outer query.
Hands-on Exercise:
o Use subqueries to retrieve data, such as finding employees who earn more than
the average salary in their department.
7. Indexing and Performance (30 minutes)
What are Indexes: How indexes help in speeding up queries.
Creating Indexes:
o CREATE INDEX index_name ON table_name(column_name);
Examining Query Performance:
o Use EXPLAIN to analyze query performance.
Best Practices for Indexing:
o Indexing frequently queried columns, avoiding unnecessary indexes.
Hands-on Exercise:
o Create indexes on appropriate columns and analyze query performance using
EXPLAIN.
8. Advanced Topics and Closing (30 minutes)
Transactions:
o What are transactions and how they work (START TRANSACTION, COMMIT,
ROLLBACK).
o ACID properties of transactions.
Views:
o What are views, and how to create and use them.
Stored Procedures and Functions:
o Basic introduction to stored procedures and functions.
o How to create, modify, and call stored procedures/functions.
