SQL & MySQL

Module 1 – What Is a Database

• What is a DBMS
• SQL vs MySQL
• Relational Databases
• Thinking Like a Database Designer
• Client-Server Architecture
• Installing MySQL Server
• MySQL Workbench
• phpMyAdmin

Module 2 – Database Modeling

• Entities
• Attributes
• Relationships
• Cardinality
• ER Diagram
• Primary Key
• Foreign Key
• Candidate Key
• Natural vs Surrogate Key
• Junction Tables
• Hierarchical Relationships
• Exercise: Hotel Database Model

Module 3 – Normalization

Register to access this module.

• Why Normalization Exists
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• BCNF
• When to Break Normalization
• Denormalization
• Exercise: Bad Model to Good Model

Module 4 – Creating a Database

Register to access this module.

• CREATE DATABASE
• CREATE TABLE
• Data Types
• AUTO_INCREMENT
• DEFAULT
• NULL
• NOT NULL
• UNIQUE
• CHECK
• ENUM
• COMMENT
• Exercise: Create Full Database

Module 5 – Table Relationships

Register to access this module.

• One to One
• One to Many
• Many to Many
• Foreign Key
• ON DELETE CASCADE
• ON DELETE RESTRICT
• ON DELETE SET NULL
• Exercise: Student ↔ Course

Module 6 – DML – Data Manipulation

Register to access this module.

• INSERT
• UPDATE
• DELETE
• TRUNCATE
• Exercise: Populate Database

Module 7 – SELECT – Basic Queries

Register to access this module.

• SELECT
• WHERE
• ORDER BY
• LIMIT
• DISTINCT
• LIKE
• IN
• BETWEEN
• IS NULL
• Aliases

Module 8 – SQL Functions

Register to access this module.

• COUNT
• SUM
• AVG
• MAX
• MIN
• ROUND
• CONCAT
• UPPER
• LOWER
• SUBSTRING
• LENGTH
• DATE Functions
• NOW
• DATEDIFF

Module 9 – JOIN

Register to access this module.

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN
• SELF JOIN
• Exercise: JOIN Practice

Module 10 – Grouping

Register to access this module.

• GROUP BY
• HAVING
• Aggregate Functions

Module 11 – Subqueries

Register to access this module.

• Subquery
• EXISTS
• NOT EXISTS
• IN (subquery)
• ANY
• ALL

Module 12 – Advanced SQL

Register to access this module.

• UNION
• UNION ALL
• CASE
• IF()
• COALESCE

Module 13 – Views

Register to access this module.

• CREATE VIEW
• Updatable View
• Views and Security

Module 14 – Stored Procedures

Register to access this module.

• Stored Procedures
• Procedure Parameters
• IF in Procedures
• LOOP
• Cursors

Module 15 – Triggers

Register to access this module.

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER DELETE
• Exercise: Audit Log

Module 16 – Transactions

Register to access this module.

• COMMIT
• ROLLBACK
• ACID
• Isolation Levels

Module 17 – Indexes

Register to access this module.

• Primary Index
• Unique Index
• Composite Index
• Fulltext Index
• EXPLAIN
• How Indexes Work
• Exercise: Index Performance

Module 18 – Optimization

Register to access this module.

• Bad Queries
• Good WHERE Clauses
• JOIN Optimization
• LIMIT and Pagination
• Covering Index

Module 19 – Backup

Register to access this module.

• mysqldump
• Restore
• Import
• Export

Module 20 – Security

Register to access this module.

• SQL Injection
• Prepared Statements
• Privileges
• Database Users
• Backup Strategy

Module 21 – Serious Database Design

Register to access this module.

• Design: Webshop
• Design: Hotel (detailed)
• Design: CRM
• Design: School System

Module 22 – Real-World Examples

Register to access this module.

• WeldPilot – Welder Management
• Apartment Booking
• GPS Fleet Tracking
• E-Learning Platform
• CRM (BrainTech)

Module 23 – Mistakes & Final Project

Register to access this module.

• VARCHAR(255) for Everything
• Missing Foreign Keys
• Multiple NULL Pitfalls
• Poor Table Names
• Unnecessary Duplicate Columns
• Too Many JOINs
• SELECT *
• Final Project: Hotel System