MySQL

Module 1: Introduction to Databases and MySQL

• What is a Database?

• Types of Databases (Relational vs Non-Relational)

• Introduction to RDBMS concepts

• Overview of MySQL – features, advantages, use cases

• MySQL installation & setup (Workbench / Command-line)

• Understanding Database, Table, Row, and Column structure

• Hands-on: Creating your first database

Module 2: Data Modeling and Normalization

• What is Data Modeling?

• Entities, Attributes, and Relationships

• Primary Keys and Foreign Keys

• Concept of Redundancy

• Normalization:

  • 1NF, 2NF, 3NF (with examples)

  • Denormalization (when and why)

• Hands-on: Designing a normalized database schema (e.g., Student or Employee DB)

Module 3: SQL Basics and Data Definition Language (DDL)

• What is SQL? (Structured Query Language)

• SQL Syntax and Structure

• DDL Commands:

  • CREATE (Database, Table)

  • ALTER (Modify Table Structure)

  • DROP (Delete Table/Database)

  • TRUNCATE (Remove all records)

• Understanding Data Types in MySQL

• Hands-on: Creating and modifying database tables

Module 4: Data Manipulation Language (DML)

• DML Commands:

  • INSERT (Add records)

  • UPDATE (Modify records)

  • DELETE (Remove records)

• Using SELECT for basic data retrieval

• Sorting (ORDER BY) and Filtering (WHERE, LIKE, BETWEEN, IN)

• Hands-on: CRUD operations on real tables

Module 5: SQL Functions and Operators

• Arithmetic and Logical operators

• Built-in functions:

  • String Functions: CONCAT, LENGTH, UPPER, LOWER

  • Numeric Functions: ROUND, ABS, MOD

  • Date Functions: NOW, CURDATE, DATEDIFF

  • Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

• Hands-on: Applying functions in queries

Module 6: Joins and Relationships

• Understanding table relationships (one-to-one, one-to-many, many-to-many)

• Types of Joins:

  • INNER JOIN

  • LEFT JOIN

  • RIGHT JOIN

  • FULL JOIN (conceptual, workaround in MySQL)

  • CROSS JOIN

• Hands-on: Querying related tables using joins (e.g., Employee & Department tables)

Module 7: Subqueries and Nested Queries

• What is a Subquery?

• Subquery in WHERE clause

• Subquery in FROM clause (Derived Tables)

• Subquery with IN, EXISTS, ANY, ALL

• Correlated Subqueries

• Hands-on: Writing nested queries for data analysis

Module 8: Constraints and Keys

• Primary Key, Foreign Key, Unique, Not Null, Default

• Referential Integrity and Cascading actions (ON DELETE, ON UPDATE)

• Hands-on: Applying constraints in table creation