Complete MySQL Tutorial

MySQL Installation & Setup

Prerequisites

Before installing MySQL, ensure your system meets these requirements:

• Operating System: Windows 10/11, macOS 10.14+, or Linux (Ubuntu 18.04+, CentOS 7+)
• RAM: Minimum 2GB (4GB recommended for development)
• Storage: At least 2GB free space
• Administrator/root access for installation
• Internet connection for downloading packages

Installation Methods by Platform

Choose the installation method that best fits your operating system:

Step-by-Step Installation Guide

1. Windows Installation

Follow these detailed steps for Windows installation:

2. macOS Installation

For macOS users, here's the installation process:

3. Linux Installation (Ubuntu)

For Ubuntu and other Debian-based systems:

Post-Installation Verification

After installation, verify that MySQL is running correctly:

Initial Configuration

Configure MySQL for optimal development use:

Installing MySQL Workbench (GUI Tool)

For a graphical interface, install MySQL Workbench:

Troubleshooting Common Installation Issues

Next Steps

After successful installation, you're ready to:

• Create your first database
• Learn basic SQL commands
• Set up user accounts and permissions
• Begin developing database-driven applications

Creating Databases in MySQL

Understanding Databases

Before creating databases, it's important to understand what they are:

• Container: A database is a container that holds multiple tables
• Organization: Helps organize related data together
• Security: Provides a security boundary - you can control access per database
• Backup Unit: Makes it easy to backup related data together

Prerequisites

Before creating databases, ensure you have:

• MySQL server installed and running
• Access to MySQL command line or GUI tool
• Appropriate privileges (usually as root user initially)
• Basic understanding of SQL syntax

Step 1: Connect to MySQL Server

First, you need to connect to your MySQL server:

Step 2: View Existing Databases

Before creating new databases, let's see what already exists:

Step 3: Create Your First Database

Now let's create a simple database. We'll use a school management system as an example:

Understanding the CREATE DATABASE Syntax

Let's break down the command:

• CREATE DATABASE - The SQL command to create a new database
• school_db - The name we're giving to our database
• ; - Required semicolon to end the SQL statement

Step 4: Verify Database Creation

Let's confirm our database was created successfully:

Step 5: Select the Database

Before working with tables, you need to tell MySQL which database to use:

Advanced Database Creation Options

CREATE DATABASE has several optional parameters for more control:

Understanding Character Sets and Collations

• Character Set: Defines what characters can be stored (utf8mb4 supports all Unicode characters)
• Collation: Defines how characters are sorted and compared
• utf8mb4_unicode_ci is recommended for most applications

More Practical Examples

Let's create databases for different types of applications:

Viewing Database Information

You can get detailed information about a specific database:

Deleting Databases

To remove a database (use with extreme caution!):

Best Practices for Database Creation

Common Mistakes to Avoid

• Forgetting the semicolon (;) at the end of SQL statements
• Using reserved keywords as database names (like SELECT, TABLE, etc.)
• Not selecting a database with USE before creating tables
• Using DROP DATABASE without double-checking the name
• Creating too many databases when one would suffice

Practice Exercise

Try these exercises to practice what you've learned:

1. Create a database named "library_system"
2. Create a database named "hospital_management" with utf8mb4 character set
3. Use SHOW DATABASES to verify both were created
4. Select the "library_system" database
5. Use SELECT DATABASE() to confirm it's selected

Creating Tables in MySQL

Understanding Table Structure

Before creating tables, let's understand the components:

• Table Name: Unique identifier for the table within a database
• Columns/Fields: Define the type of data stored (name, age, email, etc.)
• Rows/Records: Individual entries in the table
• Data Types: Specify what kind of data each column can hold
• Constraints: Rules that enforce data integrity

Prerequisites

Before creating tables, ensure you have:

• A database created (we'll use "school_db" from previous lesson)
• Selected the database using USE command
• Basic understanding of data types
• MySQL command line or GUI tool access

Step 1: Select Your Database

First, make sure you're working in the right database:

Step 2: Basic Table Creation Syntax

The basic CREATE TABLE statement follows this pattern:

Step 3: Create Your First Table - Students

Let's create a simple students table to track basic information:

Understanding Our Students Table Structure

Let's break down each column in a proper table format:

Step 4: Verify Table Creation

Let's confirm our table was created successfully:

More Practical Table Examples

Let's create additional tables for our school database:

Example 1: Courses Table

Example 2: Teachers Table

Understanding Common Data Types

Understanding Common Constraints

Creating Tables with Foreign Keys

Let's create a table that relates students to courses:

Modifying Existing Tables

You can modify tables after creation using ALTER TABLE:

Viewing Table Information

Various ways to inspect your tables:

Deleting Tables

To remove a table (use with caution!):

Best Practices for Table Creation

Common Mistakes to Avoid

• Forgetting to select a database before creating tables
• Using reserved keywords as table or column names
• Not specifying primary keys for tables
• Using wrong data types (VARCHAR for dates, etc.)
• Creating tables without planning relationships
• Forgetting commas between column definitions

Practice Exercise

Create a complete library management system with these tables:

1. books table with: book_id, title, author, isbn, publication_year
2. members table with: member_id, name, email, join_date, membership_type
3. borrowings table with: borrowing_id, book_id, member_id, borrow_date, return_date
4. Add appropriate data types and constraints
5. Create foreign key relationships between borrowings and the other tables

MySQL Data Types

Why Data Types Matter

Selecting appropriate data types provides several benefits:

• Data Integrity: Prevents invalid data from being stored
• Storage Efficiency: Optimizes disk space usage
• Performance: Improves query speed and efficiency
• Validation: Automatically validates data format
• Memory Usage: Reduces memory consumption

Numeric Data Types

Used for storing numbers - integers, decimals, and floating-point values:

String Data Types

Used for storing text and character data:

Date and Time Data Types

Used for storing dates, times, and timestamps:

Binary Data Types

Used for storing binary data like files, images, or encrypted data:

Other Special Data Types

MySQL also provides specialized data types for specific use cases:

Practical Examples with Different Data Types

Let's create tables using various data types:

Data Type Selection Guidelines

Common Data Type Mistakes

• Using VARCHAR for all text: Use CHAR for fixed-length codes
• Using FLOAT for money: Use DECIMAL to avoid rounding errors
• Oversizing VARCHAR: Choose appropriate max length
• Using TEXT for short strings: Use VARCHAR for better performance
• Storing dates as strings: Use DATE/DATETIME for date operations
• Using INT for small numbers: Use TINYINT or SMALLINT when appropriate

Practice Exercise

Create a table for a blog system with appropriate data types:

1. posts table with: post_id, title, content, author_id, created_date, published_status, view_count
2. categories table with: category_id, name, description, created_date
3. tags table with: tag_id, name, color_code
4. Choose the most appropriate data types for each column
5. Consider storage efficiency and performance

MySQL Constraints

Why Constraints Matter

Using constraints provides several important benefits:

• Data Integrity: Prevents invalid or inconsistent data
• Data Accuracy: Ensures data follows business rules
• Relationship Management: Maintains links between related tables
• Application Reliability: Reduces application-level validation code
• Database Consistency: Ensures data follows defined rules

Types of Constraints in MySQL

PRIMARY KEY Constraint

Uniquely identifies each record in a table. A table can have only one primary key.

FOREIGN KEY Constraint

Creates a relationship between two tables and maintains referential integrity.

Foreign Key Actions

UNIQUE Constraint

Ensures all values in a column are different from each other.

NOT NULL Constraint

Ensures a column cannot contain NULL values.

CHECK Constraint

Ensures that values in a column satisfy a specific condition (MySQL 8.0+).

DEFAULT Constraint

Sets a default value for a column when no value is specified.

Complete Example: E-commerce Database

Let's create a complete example using multiple constraints:

Adding Constraints to Existing Tables

You can add constraints to existing tables using ALTER TABLE:

Removing Constraints

Constraints can be removed when needed:

Constraint Best Practices

Common Constraint Mistakes

• Not using primary keys: Every table needs a primary key
• Forgetting foreign keys: Leads to orphaned records
• Overusing CASCADE: Can accidentally delete important data
• Not using NOT NULL: Allows incomplete data
• Ignoring unique constraints: Allows duplicate data where it shouldn't be
• Complex check constraints: Can impact performance

Practice Exercise

Create a library management system with proper constraints:

1. books table with: book_id (PK), isbn (UNIQUE), title (NOT NULL), publication_year (CHECK > 1450)
2. members table with: member_id (PK), email (UNIQUE, NOT NULL), join_date (DEFAULT CURRENT_DATE)
3. borrowings table with: borrowing_id (PK), book_id (FK), member_id (FK), borrow_date (DEFAULT), return_date (CHECK > borrow_date)
4. Add appropriate foreign key relationships with CASCADE actions
5. Add CHECK constraints for business rules

Inserting Data in MySQL

Prerequisites

Before inserting data, ensure you have:

• Tables created with proper structure
• Appropriate permissions to insert data
• Understanding of your table's columns and data types
• Selected the correct database using USE statement

Basic INSERT Syntax

The basic INSERT statement has several forms:

Step 1: Basic Single Row Insertion

Let's start with inserting a single record into our students table:

Step 2: Insert Multiple Rows

You can insert multiple rows with a single INSERT statement:

Step 3: Verify Inserted Data

Let's check our inserted data using SELECT statement:

Different INSERT Syntax Methods

Method 1: Column List (Recommended)

Method 2: Without Column List

Method 3: SET Syntax

Handling Different Data Types

Working with AUTO_INCREMENT Columns

When a column has AUTO_INCREMENT, MySQL automatically generates the value:

Working with DEFAULT Values

Columns with DEFAULT values can be omitted from INSERT statements:

Inserting Data with Foreign Keys

When inserting into tables with foreign keys, reference existing records:

INSERT with SELECT (Copying Data)

You can insert data from one table into another:

Handling Errors and Constraints

INSERT IGNORE and ON DUPLICATE KEY UPDATE

Advanced techniques for handling duplicate entries:

Best Practices for Inserting Data

Common INSERT Mistakes

• Forgetting column lists: Can cause errors if table structure changes
• Wrong data types: Inserting strings without quotes, etc.
• Violating constraints: Not checking foreign key relationships
• Date format errors: Using wrong date formats
• Missing required fields: Forgetting NOT NULL columns
• Single quotes in text: Not escaping special characters

Practice Exercise

Populate the library management system with sample data:

1. Insert 5 books into the books table with different categories
2. Insert 3 library members into the members table
3. Insert borrowing records linking books to members
4. Use multiple row insertion for books and members
5. Verify all inserts worked by selecting the data
6. Try inserting a duplicate ISBN and handle it with INSERT IGNORE

SELECT Queries in MySQL

Why SELECT Queries Matter

SELECT queries are essential for:

• Data Retrieval: Extracting specific information from databases
• Data Analysis: Generating reports and insights
• Application Development: Displaying data in applications
• Decision Making: Supporting business decisions with data
• Data Validation: Checking data quality and integrity

Basic SELECT Syntax

The SELECT statement has several components that can be combined:

Step 1: Basic SELECT Queries

Let's start with simple queries to retrieve data from our students table:

Step 2: Using DISTINCT for Unique Values

Get unique values from a column:

Step 3: Basic Calculations and Expressions

Perform calculations in SELECT statements:

Common SELECT Patterns

Working with Multiple Tables

Query data from multiple tables using JOINs:

JOIN Types Overview

Using WHERE Clause for Filtering

Filter rows based on specific conditions:

Common WHERE Clause Operators

Sorting Results with ORDER BY

Control the order of your results:

Limiting Results with LIMIT

Restrict the number of rows returned:

Aggregate Functions

Perform calculations on multiple rows and return single values:

Common Aggregate Functions

Best Practices for SELECT Queries

Common SELECT Mistakes

• Using SELECT * in production: Can cause performance issues
• Missing JOIN conditions: Results in Cartesian products
• Confusing AND/OR logic: Use parentheses for complex conditions
• Forgetting NULL handling: NULL values behave differently
• Inefficient LIKE patterns: Leading wildcards can't use indexes
• Overusing subqueries: Consider JOINs for better performance
• Ignoring data types: Can cause implicit conversions

Practice Exercise

Practice these SELECT query scenarios:

1. Retrieve all students born after 2000
2. Find students with Gmail email addresses
3. List students in alphabetical order by last name, then first name
4. Count how many students enrolled each year
5. Find the average age of students
6. Get the top 5 most recently enrolled students
7. Join students with their course enrollments and grades
8. Find students who are not enrolled in any courses

WHERE Clause in MySQL

Why WHERE Clause Matters

The WHERE clause is essential for:

• Data Filtering: Extracting specific subsets of data
• Performance: Reducing data transfer by filtering at database level
• Data Analysis: Focusing on relevant data for reports
• Application Logic: Implementing business rules in queries
• Security: Restricting data access based on conditions

Basic WHERE Clause Syntax

The WHERE clause follows the FROM clause in a SELECT statement:

Comparison Operators

Used to compare values in WHERE conditions:

Step 1: Basic Comparison Examples

Let's start with simple comparison operations on our students table:

Logical Operators (AND, OR, NOT)

Combine multiple conditions in WHERE clauses:

Step 2: Combining Conditions with AND/OR

Use logical operators to create complex filters:

BETWEEN Operator

Filter values within a specified range (inclusive):

IN Operator

Check if a value matches any value in a list:

LIKE Operator for Pattern Matching

Search for patterns in text data using wildcards:

NULL Value Handling

Special handling for NULL values in WHERE clauses:

Complex WHERE Clause Examples

Combine multiple operators for sophisticated filtering:

WHERE Clause with Functions

Use functions in WHERE conditions for dynamic filtering:

Performance Considerations

Best Practices for WHERE Clauses

Common WHERE Clause Mistakes

• Using = NULL instead of IS NULL
• Forgetting parentheses in complex AND/OR conditions
• Case sensitivity in string comparisons
• Date format mismatches
• Using functions that prevent index usage
• Overly complex conditions that are hard to read
• Not considering NULL values in logic

Practice Exercise

Practice these WHERE clause scenarios:

1. Find all students born in the 1990s
2. Find students with first names starting with 'A' or 'B'
3. Find students without email addresses
4. Find students enrolled in the last 30 days
5. Find students with specific last names but exclude certain first names
6. Find students with email addresses from educational institutions (.edu)
7. Find students born on weekdays (Monday-Friday)
8. Find students with odd-numbered student IDs

MySQL Operators

Why Operators Matter

Operators are essential for:

• Data Filtering: Creating conditions in WHERE clauses
• Data Calculation: Performing mathematical operations
• Data Comparison: Comparing values and expressions
• Logical Operations: Combining multiple conditions
• Pattern Matching: Searching for text patterns

Operator Categories Overview

Arithmetic Operators

Used for mathematical calculations on numeric values:

Comparison Operators

Used to compare values and return TRUE, FALSE, or NULL:

Logical Operators

Combine boolean expressions and return TRUE, FALSE, or NULL:

Bitwise Operators

Perform operations on individual bits of integer values:

Special SQL Operators

Unique operators for specific SQL operations:

Operator Precedence

Order in which operators are evaluated (highest to lowest):

Best Practices for Using Operators

Common Operator Mistakes

• Using = NULL instead of IS NULL
• Forgetting operator precedence in complex expressions
• Using bitwise operators instead of logical operators
• Mixing data types in comparisons
• Using AND when OR is needed (and vice versa)
• Forgetting that NULL values affect logical operations
• Using arithmetic operators on non-numeric data

Practice Exercise

Practice using different operators with these scenarios:

1. Calculate the total price including 8% tax for products
2. Find students with ages between 20 and 25 using BETWEEN
3. Use IN operator to find students in specific departments
4. Combine AND/OR to find active students with high GPA or honors status
5. Use bitwise operators to check if a number is even or odd
6. Create complex WHERE conditions using proper parentheses
7. Use REGEXP to find email addresses with specific patterns
8. Test operator precedence with mixed arithmetic and logical operations

UPDATE & DELETE Statements in MySQL

Why UPDATE & DELETE Matter

These statements are essential for database maintenance:

• Data Correction: Fix errors in existing records
• Data Maintenance: Keep information current and accurate
• Data Archiving: Remove obsolete or temporary data
• Business Logic: Implement status changes and updates
• Database Cleanup: Remove unwanted or duplicate records

UPDATE Statement Overview

The UPDATE statement modifies existing records:

Basic UPDATE Syntax

Let's start with basic UPDATE operations on our students table:

Advanced UPDATE Operations

More complex UPDATE scenarios with multiple conditions and joins:

UPDATE with JOIN

Update records based on values from related tables:

DELETE Statement Overview

The DELETE statement removes records from a table:

Basic DELETE Syntax

Let's explore DELETE operations with proper safety measures:

DELETE Safety Practices

Safe DELETE Workflow

Always follow this safety procedure before deleting:

DELETE with JOIN

Delete records based on relationships with other tables:

TRUNCATE vs DELETE

Understanding the difference between TRUNCATE and DELETE:

Handling Foreign Key Constraints

Special considerations when updating or deleting related records:

Best Practices for UPDATE & DELETE

Common UPDATE & DELETE Mistakes

• Omitting WHERE clause: Updates/deletes all records
• Incorrect WHERE conditions: Affects wrong records
• Not testing with SELECT first: No verification
• Ignoring foreign key constraints: Causes errors
• Not using transactions: No rollback capability
• Forgetting about triggers: Unexpected side effects
• Not considering performance: Locks large tables

Practice Exercise

Practice these UPDATE and DELETE scenarios safely:

1. Update all product prices by applying a 15% discount for clearance items
2. Change the email domain for all students from @old-school.edu to @new-school.edu
3. Delete temporary log records older than 30 days (use LIMIT for safety)
4. Update student status to 'graduated' for those with graduation_year in the past
5. Delete duplicate student records (keep the one with the latest enrollment_date)
6. Use a transaction to safely update multiple related tables
7. Practice the safe DELETE workflow with SELECT verification
8. Update records using a JOIN with another table

JOIN Operations in MySQL

Why JOIN Operations Matter

JOIN operations are essential for:

• Data Relationships: Combining related data from multiple tables
• Normalized Databases: Working with properly structured databases
• Complex Queries: Answering business questions across multiple entities
• Reporting: Creating comprehensive reports from related data
• Data Analysis: Analyzing relationships between different data entities

Sample Data for JOIN Examples

Let's set up sample tables to demonstrate different JOIN types:

JOIN Types Overview

INNER JOIN

Returns only the rows that have matching values in both tables:

LEFT JOIN

Returns all rows from the left table and matched rows from the right table. Unmatched rows from the right table will contain NULLs:

RIGHT JOIN

Returns all rows from the right table and matched rows from the left table. Unmatched rows from the left table will contain NULLs:

FULL OUTER JOIN (MySQL Workaround)

MySQL doesn't support FULL OUTER JOIN directly, but we can simulate it using UNION:

CROSS JOIN

Returns the Cartesian product of both tables (every row from first table combined with every row from second table):

SELF JOIN

Join a table with itself to find relationships within the same table:

JOIN Conditions and Multiple Tables

JOIN Performance Considerations

Common JOIN Patterns

Real-world scenarios where different JOIN types are useful:

JOIN Best Practices

Common JOIN Mistakes

• Cartesian products: Forgetting JOIN conditions
• Wrong JOIN type: Using INNER JOIN when LEFT JOIN is needed
• Performance issues: Joining too many tables without indexes
• Ambiguous columns: Not qualifying column names with table aliases
• NULL handling: Not considering NULL values in JOIN conditions
• Condition placement: Putting filter conditions in wrong clause (ON vs WHERE)
• Data type mismatches: Joining columns with different data types

Practice Exercise

Practice these JOIN scenarios:

1. Create INNER JOIN to find all students enrolled in 'Computer Science' courses
2. Use LEFT JOIN to find all courses and count how many students are enrolled in each
3. Write a query to find students who are not enrolled in any courses
4. Create a SELF JOIN to find employees and their managers
5. Use multiple JOINs to display student names, course names, and instructor names
6. Write a query using RIGHT JOIN to find all departments and their employees
7. Create a CROSS JOIN to generate all possible product-size combinations
8. Use JOIN with aggregate functions to find the average grade per course

Aggregate Functions in MySQL

Why Aggregate Functions Matter

Aggregate functions are crucial for:

• Data Summarization: Creating summary reports from detailed data
• Statistical Analysis: Calculating averages, totals, and distributions
• Business Intelligence: Supporting decision-making with summarized data
• Performance Monitoring: Tracking metrics and KPIs
• Data Quality: Identifying data patterns and anomalies

Common Aggregate Functions Overview

Sample Data for Examples

Let's use a sample sales data table for our examples:

COUNT() Function

Counts the number of rows or non-NULL values:

SUM() Function

Calculates the sum of numeric values:

AVG() Function

Calculates the average value of numeric columns:

MIN() and MAX() Functions

Find the minimum and maximum values in a column:

GROUP_CONCAT() Function

Concatenates values from multiple rows into a single string:

Statistical Aggregate Functions

Advanced statistical calculations:

Using Aggregate Functions with GROUP BY

Group data and calculate aggregates for each group:

HAVING Clause with Aggregate Functions

Filter groups based on aggregate results:

Aggregate Function Behavior

Advanced Aggregate Techniques

Combining multiple aggregate functions and techniques:

Best Practices for Aggregate Functions

Common Aggregate Function Mistakes

• Mixing aggregated and non-aggregated columns without GROUP BY
• Using WHERE instead of HAVING for aggregate conditions
• Forgetting NULL handling in calculations
• Incorrect GROUP BY columns leading to wrong groupings
• Performance issues with large datasets and no indexes
• Misunderstanding COUNT(*) vs COUNT(column) behavior
• Floating point precision issues with AVG()

Practice Exercise

Practice these aggregate function scenarios:

1. Calculate total revenue, average sale amount, and number of sales
2. Find the top 3 selling products by quantity
3. Calculate monthly sales trends with revenue and average order value
4. Use GROUP_CONCAT to list all customers who purchased each product
5. Find categories with average price above overall average price
6. Calculate running totals of sales using window functions
7. Use conditional aggregates to compare sales across different regions
8. Find products that have never been sold (using COUNT and LEFT JOIN)

GROUP BY & HAVING in MySQL

Why GROUP BY & HAVING Matter

These clauses are essential for:

• Data Summarization: Creating meaningful summaries from detailed data
• Category Analysis: Analyzing data by different categories or segments
• Statistical Reporting: Generating reports with totals, averages, and counts
• Data Segmentation: Breaking down data into meaningful groups
• Business Intelligence: Supporting decision-making with grouped insights

GROUP BY vs HAVING Comparison

Sample Data for Examples

Let's use an enhanced sales dataset for our examples:

Basic GROUP BY Syntax

The fundamental structure of GROUP BY queries:

Single Column GROUP BY

Grouping by a single column with various aggregate functions:

Multiple Column GROUP BY

Grouping by multiple columns for more detailed analysis:

GROUP BY with Expressions

Using expressions and functions in GROUP BY clauses:

HAVING Clause Fundamentals

Using HAVING to filter groups based on aggregate conditions:

Complex HAVING Conditions

Using multiple conditions and different aggregate functions in HAVING:

WHERE vs HAVING Comparison

GROUP BY with JOINs

Combining GROUP BY with table joins for complex analysis:

Advanced GROUP BY Techniques

Rollup, grouping sets, and other advanced grouping features:

GROUP BY Best Practices

Common GROUP BY & HAVING Mistakes

• Omitting non-aggregated columns from GROUP BY clause
• Using HAVING instead of WHERE for non-aggregate conditions
• Confusing column order in multiple column GROUP BY
• Forgetting about NULL groups in results
• Performance issues with unindexed GROUP BY columns
• Incorrect aggregate calculations due to wrong groupings
• Misunderstanding WHERE vs HAVING execution order

Practice Exercise

Practice these GROUP BY and HAVING scenarios:

1. Find the top 5 best-selling products by total quantity sold
2. Calculate monthly revenue trends and identify months with revenue over $10,000
3. Find salespeople who have closed more than 15 deals with average deal size over $75
4. Identify regions where Electronics category sales exceed Clothing sales
5. Calculate the percentage contribution of each category to total revenue
6. Use WITH ROLLUP to create a sales summary with regional and category subtotals
7. Find products that have been sold in all regions
8. Identify seasonal patterns by grouping sales by quarter and year

Subqueries in MySQL

Why Subqueries Matter

Subqueries are essential for:

• Complex Filtering: Creating dynamic conditions based on other query results
• Data Comparison: Comparing values against calculated results
• Multi-step Analysis: Breaking complex problems into manageable steps
• Flexible Queries: Writing queries that adapt to changing data
• Advanced Reporting: Creating sophisticated business intelligence queries

Subquery Types Overview

Sample Data for Examples

Let's use employee and department data for our examples:

Subqueries in WHERE Clause

Using subqueries to create dynamic conditions:

Subqueries with Comparison Operators

EXISTS and NOT EXISTS

Checking for existence of related records:

Correlated Subqueries

Subqueries that reference columns from the outer query:

Subqueries in SELECT Clause

Using subqueries to calculate additional columns:

Subqueries in FROM Clause (Derived Tables)

Using subqueries as temporary tables in the FROM clause:

Subqueries in HAVING Clause

Using subqueries to filter grouped results:

Subquery Performance Considerations

Subquery vs JOIN Comparison

Common Subquery Patterns

Real-world scenarios where subqueries are particularly useful:

Subquery Best Practices

Common Subquery Mistakes

• Returning multiple rows when single value expected
• Using correlated subqueries unnecessarily on large tables
• Forgetting table aliases in correlated subqueries
• Performance issues with nested subqueries
• Incorrect operator usage (e.g., = instead of IN)
• NULL handling issues in comparison subqueries
• Circular references in complex subquery structures

Practice Exercise

Practice these subquery scenarios:

1. Find employees who earn more than the average salary of their department
2. List departments that have no employees using NOT EXISTS
3. Find products that have never been ordered using a subquery
4. Calculate each employee's salary as a percentage of their department's total salary
5. Find the second highest salary in the company using a subquery
6. List customers who have placed orders in the last 30 days using EXISTS
7. Find departments where the average salary is greater than the company average
8. Use a derived table to find monthly sales trends and compare to previous months

Indexes in MySQL

Why Indexes Matter

Indexes are crucial for:

• Query Performance: Dramatically speed up SELECT queries
• Data Retrieval: Enable fast lookups and range scans
• Sorting Efficiency: Improve ORDER BY and GROUP BY performance
• Join Operations: Accelerate table joins on indexed columns
• Constraint Enforcement: Support UNIQUE and PRIMARY KEY constraints

Index Types in MySQL

How Indexes Work

Understanding the underlying mechanism:

Creating Indexes

Different ways to create indexes in MySQL:

When to Create Indexes

Composite Indexes

Indexes on multiple columns and their optimal usage:

Index Cardinality and Selectivity

Viewing Index Information

How to examine existing indexes and their usage:

EXPLAIN Command for Index Analysis

Using EXPLAIN to understand query execution and index usage:

Index Maintenance and Optimization

Keeping indexes efficient and up-to-date:

Dropping and Modifying Indexes

Managing existing indexes:

Index Costs and Trade-offs

Index Best Practices

Common Indexing Mistakes

• Over-indexing: Creating too many indexes on frequently updated tables
• Under-indexing: Missing indexes on foreign keys and search columns
• Wrong column order: Poor ordering in composite indexes
• Indexing low-selectivity columns: Gender, status flags, etc.
• Not monitoring index usage: Keeping unused indexes
• Ignoring index fragmentation: Not maintaining indexes over time
• Wrong index type: Using standard index where unique is needed
• Not testing with real data: Creating indexes without performance testing

Practice Exercise

Practice these indexing scenarios:

1. Create appropriate indexes for a customers table with name, email, and phone searches
2. Design composite indexes for an orders table with customer_id, order_date, and status queries
3. Use EXPLAIN to analyze query performance before and after adding indexes
4. Identify and remove unused indexes from an existing database
5. Create covering indexes for frequently used reporting queries
6. Monitor index usage statistics to identify performance bottlenecks
7. Design indexes for a products table with category, price range, and brand searches
8. Implement fulltext indexes for product descriptions and search functionality

Views in MySQL

Why Views Matter

Views are essential for:

• Data Security: Restrict access to specific columns or rows
• Query Simplification: Hide complex queries behind simple interfaces
• Data Abstraction: Provide consistent interface despite schema changes
• Logical Data Organization: Present data in business-friendly formats
• Code Reusability: Avoid repeating complex query logic

View Types in MySQL

Sample Data for Examples

Let's use employee and department data for our view examples:

Creating Basic Views

Creating simple and complex views:

Using Views

Querying views just like regular tables:

View Options and Features

Updatable Views

Views that allow data modification operations:

View Modification and Management

Altering, replacing, and dropping views:

View Security and Access Control

Performance Considerations

View Limitations and Restrictions

Understanding what views cannot do:

Best Practices for Using Views

Common View Mistakes

• Overusing views: Creating views for simple queries that don't need abstraction
• Performance issues: Not considering the performance impact of complex views
• Security misconfiguration: Incorrect use of SQL SECURITY options
• Nested view complexity: Creating views that depend on other views excessively
• Assuming updatability: Not checking if a view is actually updatable
• Missing documentation: Not documenting view purposes and limitations
• Ignoring base table changes: Not updating views when underlying tables change
• Wrong algorithm choice: Manually specifying algorithm when not needed

Practice Exercise

Practice these view scenarios:

1. Create a view that shows employee names and departments without revealing salaries
2. Design a view for managers that only shows employees in their department
3. Create a reporting view that summarizes project budgets by department
4. Implement an updatable view for HR to modify basic employee information
5. Create a view with WITH CHECK OPTION to ensure data integrity
6. Design a view that combines employee, department, and project information
7. Create a view that shows monthly hiring trends using date functions
8. Implement security-focused views with SQL SECURITY DEFINER option

Stored Procedures in MySQL

Why Stored Procedures Matter

Stored procedures are essential for:

• Code Reusability: Write once, use multiple times
• Performance: Reduced network traffic and pre-compiled execution
• Security: Control data access through procedure permissions
• Maintainability: Centralize business logic in database
• Transaction Management: Complex operations in single transaction

Stored Procedure Advantages

Basic Stored Procedure Syntax

Fundamental structure of stored procedures:

Sample Data for Examples

Let's use a banking system example for our procedures:

Creating Simple Stored Procedures

Basic procedures without parameters: