Complete MySQL & SQL Course for Beginners: Learn Relational Databases From Scratch | TutorialRays

Tutorial Rays MYSQL Complete MySQL & SQL Course for Beginners: Learn Relational Databases From Scratch | TutorialRays
  • November 9, 2026

Complete MySQL & SQL Course for Beginners: Learn Relational Databases From Scratch | TutorialRays

AI Reading

Quick summary of this article

Preparing the AI summary…

Complete MySQL & SQL Course for Beginners: Learn Relational Databases From Scratch | TutorialRays

Welcome to the complete, step-by-step MySQL and SQL Mastery Course on TutorialRays. Whether you are aspiring to become a backend developer, data analyst, database administrator (DBA), or software engineer, SQL (Structured Query Language) is one of the most critical foundational skills you will ever learn. In this comprehensive, chapter-wise tutorial, we will take you from database installation and client connection all the way to complex multi-table joins, correlated subqueries, aggregate grouping, and 20 real-world solved interview exercises.

AI Reading

Quick summary of what you will master in this course

  • Architecture & Connection: Connect to MySQL / MariaDB locally and remotely over standard port 3306 and secure SSH tunnels (Port 22999).
  • Schema Design & Constraints: Build normalized relational tables using PRIMARY KEY, FOREIGN KEY, and referential integrity constraints.
  • CRUD Operations: Master INSERT, SELECT, UPDATE, and DELETE with strict condition evaluation.
  • Filtering & Operators: Utilize WHERE, comparison operators (=, >, <, <>), BETWEEN, IN, and boolean logic (AND, OR, NOT).
  • Pattern Matching: Perform fuzzy text searches using LIKE with % and _ wildcards.
  • Summaries & Aggregations: Aggregate large datasets using COUNT, SUM, AVG, MIN, MAX, combined with GROUP BY and HAVING.
  • Relational Joins: Connect related entities using INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and hierarchical SELF JOIN.
  • Advanced Logic: Employ CASE statements, subqueries, UNION, and handle NULL values seamlessly.
  • 20 Solved Exercises: Hands-on, production-tested solutions with live MariaDB tabular outputs, step-by-step logic breakdown, and common trap warnings.

📚 Course Curriculum & Table of Contents

  1. Chapter 1: Introduction to MySQL & Database Connection
  2. Chapter 2: Schema Design, Data Types & Table Creation
  3. Chapter 3: Data Ingestion & Data Manipulation (CRUD)
  4. Chapter 4: Filtering Data with WHERE & Comparison Operators
  5. Chapter 5: Pattern Matching, LIKE & Wildcards
  6. Chapter 6: Sorting & Pagination with ORDER BY & LIMIT
  7. Chapter 7: SQL Aggregate Functions & GROUP BY / HAVING
  8. Chapter 8: Mastering SQL Joins (INNER, LEFT, RIGHT, SELF)
  9. Chapter 9: Conditional Logic (CASE), Subqueries & Set Operations
  10. Chapter 10: 20 Solved Real-World Interview Queries with Live Outputs
  11. Chapter 11: Production Best Practices, Security & Cheatsheet

🚀 Chapter 1: Introduction to MySQL & Database Connection

MySQL is the world’s most popular open-source Relational Database Management System (RDBMS). It stores structured data in tables consisting of rows and columns, enforcing strict schemas and referential integrity between related entities.

1.1 Understanding Relational Database Concepts

  • Database (Schema): A container holding collections of tables, views, triggers, and stored procedures.
  • Table: A two-dimensional grid of rows (tuples/records) and columns (attributes/fields).
  • Primary Key (PK): A unique column (or combination of columns) that unconditionally identifies each row in a table. Primary keys cannot contain NULL values.
  • Foreign Key (FK): A column in one table that references the Primary Key of another table, guaranteeing referential consistency across related data.

1.2 Connecting to Your MySQL / MariaDB Server

To connect to MySQL from a client, command-line terminal, or application code (Python, PHP, Node.js, Ruby), you specify the host, port, username, and password.

A. Connecting via Terminal (Standard Port 3306)

mysql -h localhost -P 3306 -u your_username -p

B. Connecting via Secure Remote SSH Tunnel (Custom SSH Port e.g., 22999)

In modern cloud environments, direct public access to port 3306 is often restricted by firewalls for security. You can tunnel securely through your server’s SSH port:

# Establish SSH tunnel forwarding local port 3307 to remote MySQL 3306
ssh -L 3307:127.0.0.1:3306 -p 22999 user@astris.herosite.pro

# In a separate terminal, connect locally through the tunnel
mysql -h 127.0.0.1 -P 3307 -u lsxbtdyo -p

💾 Chapter 2: Schema Design, Data Types & Table Creation

To learn SQL effectively, we will model a classic corporate structure with two core entities: Departments and Employees.

2.1 Entity Relationship Diagram (ERD)

+----------------------+             +-----------------------+
|     DEPARTMENTS      |             |       EMPLOYEES       |
+----------------------+             +-----------------------+
| * department_id (PK) |<----+       | * employee_id (PK)    |
|   department_name    |     |       |   first_name          |
|   location_id        |     |       |   last_name           |
+----------------------+     |       |   email               |
                             |       |   phone_number        |
                             |       |   hire_date           |
                             |       |   job_id              |
                             |       |   salary              |
                             |       |   commission_pct      |
                             |       |   manager_id (Self-FK)|
                             +-------|   department_id (FK)  |
                                     +-----------------------+

2.2 Creating the Parent Table: departments

The parent table must be created first before child tables that reference it.

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(30) NOT NULL,
    location_id INTEGER
);

2.3 Creating the Child Table: employees

The employees table contains attributes for employee details and establishes two crucial relationships: a Foreign Key referencing departments(department_id), and a self-referencing relationship where manager_id refers to another employee_id.

CREATE TABLE employees (
    employee_id INTEGER,
    first_name VARCHAR(20),
    last_name VARCHAR(25) NOT NULL,
    email VARCHAR(25) NOT NULL,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary INTEGER NOT NULL,
    commission_pct INTEGER,
    manager_id INTEGER,
    department_id INTEGER,
    CONSTRAINT pk_emp PRIMARY KEY (employee_id),
    CONSTRAINT fk_deptno FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

📥 Chapter 3: Data Ingestion & Manipulation (CRUD)

Data insertion must respect foreign key constraints. We insert into departments first, followed by employees.

3.1 Populating the departments Table

INSERT INTO departments VALUES (20, 'Marketing', 180);
INSERT INTO departments VALUES (30, 'Purchasing', 1700);
INSERT INTO departments VALUES (40, 'Human Resources', 2400);
INSERT INTO departments VALUES (50, 'Shipping', 1500);
INSERT INTO departments VALUES (60, 'IT', 1400);
INSERT INTO departments VALUES (70, 'Public Relations', 2700);
INSERT INTO departments VALUES (80, 'Sales', 2500);
INSERT INTO departments VALUES (90, 'Executive', 1700);
INSERT INTO departments VALUES (100, 'Finance', 1700);
INSERT INTO departments VALUES (110, 'Accounting', 1700);
INSERT INTO departments VALUES (120, 'Treasury', 1700);
INSERT INTO departments VALUES (130, 'Corporate Tax', 1700);
INSERT INTO departments VALUES (140, 'Control And Credit', 1700);
INSERT INTO departments VALUES (150, 'Shareholder Services', 1700);
INSERT INTO departments VALUES (160, 'Benefits', 1700);
INSERT INTO departments VALUES (170, 'Payroll', 1700);

3.2 Populating the employees Table

INSERT INTO employees VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17', 'AD_PRES', 24000, NULL, NULL, 20);
INSERT INTO employees VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-11-21', 'AD_VP', 17000, NULL, 100, 20);
INSERT INTO employees VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '1993-09-12', 'AD_VP', 17000, NULL, 100, 30);
INSERT INTO employees VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1990-09-30', 'IT_PROG', 9000, NULL, 102, 60);
INSERT INTO employees VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1991-05-21', 'IT_PROG', 6000, NULL, 103, 60);
INSERT INTO employees VALUES (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', '1997-06-25', 'IT_PROG', 4800, NULL, 103, 60);
INSERT INTO employees VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '1998-02-05', 'IT_PROG', 4800, NULL, 103, 40);
INSERT INTO employees VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '1999-02-09', 'IT_PROG', 4200, NULL, 103, 40);
INSERT INTO employees VALUES (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '1994-08-17', 'FI_MGR', 12000, NULL, 101, 100);
INSERT INTO employees VALUES (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '1994-08-12', 'FI_ACCOUNT', 9000, NULL, 108, 170);
INSERT INTO employees VALUES (110, 'John', 'Chen', 'JCHEN', '515.124.4269', '1997-04-09', 'FI_ACCOUNT', 8200, NULL, 108, 170);
INSERT INTO employees VALUES (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '1997-02-01', 'FI_ACCOUNT', 7700, NULL, 108, 160);
INSERT INTO employees VALUES (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', '1998-06-03', 'FI_ACCOUNT', 7800, NULL, 108, 150);
INSERT INTO employees VALUES (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', '1999-12-07', 'FI_ACCOUNT', 6900, NULL, 108, 140);
INSERT INTO employees VALUES (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '1994-11-08', 'PU_MAN', 11000, NULL, 100, 30);
INSERT INTO employees VALUES (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '1995-05-12', 'PU_CLERK', 3100, NULL, 114, 80);
INSERT INTO employees VALUES (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', '1997-12-13', 'PU_CLERK', 2900, NULL, 114, 70);
INSERT INTO employees VALUES (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '1997-09-10', 'PU_CLERK', 2800, NULL, 114, 30);
INSERT INTO employees VALUES (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '1998-01-02', 'PU_CLERK', 2600, NULL, 114, 60);
INSERT INTO employees VALUES (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '1999-04-08', 'PU_CLERK', 2500, NULL, 114, 130);
INSERT INTO employees VALUES (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '1996-07-18', 'ST_MAN', 8000, NULL, 100, 50);
INSERT INTO employees VALUES (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '1997-08-09', 'ST_MAN', 8200, NULL, 100, 50);
INSERT INTO employees VALUES (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '1995-05-01', 'ST_MAN', 7900, NULL, 100, 40);
INSERT INTO employees VALUES (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '1997-10-12', 'ST_MAN', 6500, NULL, 100, 50);
INSERT INTO employees VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '1999-11-12', 'ST_MAN', 5800, NULL, 100, 80);
INSERT INTO employees VALUES (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', '1997-07-02', 'ST_CLERK', 3200, NULL, 120, 50);
INSERT INTO employees VALUES (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', '1998-11-12', 'ST_CLERK', 2700, NULL, 120, 50);
INSERT INTO employees VALUES (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', '1999-01-02', 'ST_CLERK', 2400, NULL, 120, 90);
INSERT INTO employees VALUES (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', '2000-03-04', 'ST_CLERK', 2200, NULL, 120, 50);
INSERT INTO employees VALUES (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', '1997-09-10', 'ST_CLERK', 3300, NULL, 121, 50);
INSERT INTO employees VALUES (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', '1997-10-12', 'ST_CLERK', 2800, NULL, 121, 110);

3.3 Understanding NULL Values

In SQL, NULL represents unknown, missing, or inapplicable information. It is fundamentally different from a zero integer (0) or an empty string ('').

  • Comparing NULL = NULL returns UNKNOWN (falsy in SQL predicate evaluation).
  • To test for NULL, always use IS NULL or IS NOT NULL.
  • Notice that employee 100 (Steven King) has manager_id = NULL because he is the top-level President with no supervisor.

3.4 Updating and Deleting Records Safely

-- Updating a salary with strict WHERE condition
UPDATE employees
SET salary = salary + 500
WHERE job_id = 'IT_PROG';

-- Deleting a specific employee record
DELETE FROM employees
WHERE employee_id = 130;

⚠ Caution: Always include a WHERE clause when executing UPDATE or DELETE. Omitting WHERE modifies or deletes every record in the table!

🔍 Chapter 4: Filtering Data with WHERE & Comparison Operators

The WHERE clause filters rows according to specified conditions before any aggregation or sorting occurs.

4.1 Comparison Operators in SQL

Operator Description Example Query
= Equal to WHERE salary = 12000
> Greater than WHERE salary > 10000
< Less than WHERE salary < 5000
>= Greater than or equal to WHERE hire_date >= '1995-01-01'
<= Less than or equal to WHERE salary <= 8000
<> or != Not equal to WHERE department_id <> 20
BETWEEN ... AND ... Inclusive range WHERE salary BETWEEN 5000 AND 10000
IN (...) Matches any value in list WHERE department_id IN (20, 60, 90)

4.2 Boolean Logic: AND, OR, NOT

-- Select IT Programmers earning more than 5000
SELECT first_name, last_name, salary
FROM employees
WHERE job_id = 'IT_PROG' AND salary > 5000;

-- Select employees in department 20 or 30
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 30;

-- Select employees who are NOT clerks
SELECT first_name, last_name, job_id
FROM employees
WHERE NOT (job_id = 'ST_CLERK' OR job_id = 'PU_CLERK');

🎯 Chapter 5: Pattern Matching, LIKE & Wildcards

The LIKE operator enables pattern matching on strings using two standard wildcard characters:

  • %: Represents zero, one, or multiple characters.
  • _: Represents exactly one single character.

5.1 Master Wildcard Reference Table

Pattern Meaning / Matching Rule Match Example Non-Match Example
WHERE first_name LIKE 'A%' Starts with the letter “A” Alexander, Adam David
WHERE first_name LIKE '%a' Ends with the letter “a” Neena, Diana Steven
WHERE first_name LIKE '%an%' Contains the substring “an” anywhere Diana, Daniel, Shanta Bruce
WHERE first_name LIKE '_r%' Has the character “r” in the 2nd position Bruce, Irene Steven
WHERE first_name LIKE 'a_%_%' Starts with “a” and has at least 3 characters Adam (4 chars) Al (only 2 chars)
WHERE first_name LIKE 'a%o' Starts with “a” and ends with “o” Antonio Adam

🖈 Chapter 6: Sorting & Pagination with ORDER BY & LIMIT

6.1 Sorting with ORDER BY

By default, ORDER BY sorts ascending (ASC). Use DESC for descending order.

-- Sort employees by salary descending, then last name ascending
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name ASC;

6.2 Restricting Result Counts with LIMIT

-- Get top 5 highest earners
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Pagination: Skip 10 rows (OFFSET 10) and take next 5 rows
SELECT first_name, last_name, salary
FROM employees
ORDER BY employee_id ASC
LIMIT 10, 5;

📊 Chapter 7: SQL Aggregate Functions & GROUP BY / HAVING

7.1 Standard Aggregate Functions

SELECT 
    MIN(salary) AS min_sal,
    MAX(salary) AS max_sal,
    ROUND(AVG(salary)) AS avg_sal,
    SUM(salary) AS total_payroll,
    COUNT(*) AS total_employees
FROM employees;

7.2 Grouping Records with GROUP BY

GROUP BY groups rows that share values into summary rows.

-- Employee count per job
SELECT job_id, COUNT(*) AS headcount
FROM employees
GROUP BY job_id
ORDER BY headcount DESC;

7.3 Filtering Aggregates with HAVING

Crucial Rule: The WHERE clause filters individual records before aggregation, whereas HAVING filters grouped rows after aggregation.

-- Show only departments with more than 3 employees
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3
ORDER BY emp_count DESC;

🔗 Chapter 8: Mastering SQL Joins

Relational databases distribute data across multiple tables to eliminate redundancy. Joins bring this data together in meaningful queries.

8.1 Visualizing Join Types

  • INNER JOIN: Returns rows that have matching values in both tables.
  • LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, plus matched rows from the right table (unmatched columns return NULL).
  • RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, plus matched rows from the left table.
  • CROSS JOIN: Computes the Cartesian product (all combinations of rows).
  • SELF JOIN: Joins a table to itself to model hierarchies (e.g., employee to manager).

8.2 Self Join Example (Employee & Manager)

SELECT 
    CONCAT(emp.first_name, ' ', emp.last_name) AS employee,
    emp.salary AS emp_sal,
    CONCAT(mgr.first_name, ' ', mgr.last_name) AS manager,
    mgr.salary AS mgr_sal
FROM employees emp
JOIN employees mgr ON emp.manager_id = mgr.employee_id;

8.3 Set Unions: UNION vs UNION ALL

  • UNION: Combines the results of two queries and removes duplicate records.
  • UNION ALL: Combines results preserving all duplicate records (faster execution).

💡 Chapter 9: Conditional Logic (CASE), Subqueries & Set Operations

9.1 Conditional Evaluation with CASE

The CASE expression evaluates conditional branching, behaving like an if-else statement within SQL statements.

SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    salary,
    CASE
        WHEN salary >= 2000 AND salary < 5000 THEN 'low'
        WHEN salary >= 5000 AND salary < 10000 THEN 'mid'
        ELSE 'high'
    END AS salary_level
FROM employees
ORDER BY salary ASC;

9.2 Correlated vs Uncorrelated Subqueries

An uncorrelated subquery can run independently of the outer query:

-- Find employees earning above the company-wide average
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

A correlated subquery references columns from the outer query for each row processed:

-- Select Nth highest salary using correlated count of distinct higher salaries
SET @input := 3;
SELECT employee_id, first_name, last_name, salary
FROM employees e
WHERE @input = (
    SELECT COUNT(DISTINCT p.salary)
    FROM employees p
    WHERE p.salary >= e.salary
);

📝 Chapter 10: 20 Solved Real-World Interview Queries with Live Outputs

Below are the 20 comprehensive exercises from our dataset, complete with verified queries, step-by-step logic, and exact tabular outputs executed against our live MariaDB database.

Exercise 1: Employees whose first name starts with ‘S’

Goal: Select employee first name, last name, job_id, and salary for all employees whose first name begins with the letter ‘S’.

SELECT first_name, last_name, job_id, salary
FROM employees
WHERE UPPER(first_name) LIKE 'S%';

Live Query Output:

+------------+-----------+----------+--------+
| first_name | last_name | job_id   | salary |
+------------+-----------+----------+--------+
| Steven     | King      | AD_PRES  |  24000 |
| Shelli     | Baida     | PU_CLERK |   2900 |
| Sigal      | Tobias    | PU_CLERK |   2800 |
| Shanta     | Vollman   | ST_MAN   |   6500 |
| Steven     | Markle    | ST_CLERK |   2200 |
+------------+-----------+----------+--------+

Exercise 2: Employee with the highest salary

Goal: Find the employee earning the maximum salary across the entire organization using an uncorrelated subquery.

SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Live Query Output:

+-------------+------------+-----------+---------+--------+
| employee_id | first_name | last_name | job_id  | salary |
+-------------+------------+-----------+---------+--------+
|         100 | Steven     | King      | AD_PRES |  24000 |
+-------------+------------+-----------+---------+--------+

Exercise 3: Employee with the second highest salary

Goal: Select the employee earning the second highest salary using LIMIT.

SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE salary != (SELECT MAX(salary) FROM employees)
ORDER BY salary DESC
LIMIT 1;

Live Query Output:

+-------------+------------+-----------+--------+--------+
| employee_id | first_name | last_name | job_id | salary |
+-------------+------------+-----------+--------+--------+
|         102 | Lex        | De Haan   | AD_VP  |  17000 |
+-------------+------------+-----------+--------+--------+

Exercise 4: Fetch employees with the Nth highest salary

Goal: Provide a generic query to find the 2nd, 3rd, or Nth highest salary using a variable.

-- Set desired rank (e.g. 3 for 3rd highest salary)
SET @input := 3;

SELECT employee_id, first_name, last_name, job_id, salary 
FROM employees e 
WHERE @input = (
    SELECT COUNT(DISTINCT p.salary) 
    FROM employees p 
    WHERE p.salary >= e.salary
);

Live Query Output:

+-------------+------------+-----------+--------+--------+
| employee_id | first_name | last_name | job_id | salary |
+-------------+------------+-----------+--------+--------+
|         108 | Nancy      | Greenberg | FI_MGR |  12000 |
+-------------+------------+-----------+--------+--------+

Pro-Tip (Modern MySQL 8.0+): In production environments, window functions provide superior readability and performance:

WITH RankedSalaries AS (
    SELECT employee_id, first_name, last_name, job_id, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT employee_id, first_name, last_name, job_id, salary
FROM RankedSalaries
WHERE rnk = 3;

Exercise 5: Employees, their managers, and salaries (SELF JOIN)

Goal: Display each employee alongside their direct manager’s name and compensation.

SELECT 
    CONCAT(emp.first_name, ' ', emp.last_name) AS employee,
    emp.salary AS emp_sal,
    CONCAT(mgr.first_name, ' ', mgr.last_name) AS manager,
    mgr.salary AS mgr_sal
FROM employees emp
JOIN employees mgr ON emp.manager_id = mgr.employee_id;

Live Query Output:

+-------------------+---------+------------------+---------+
| employee          | emp_sal | manager          | mgr_sal |
+-------------------+---------+------------------+---------+
| Neena Kochhar     |   17000 | Steven King      |   24000 |
| Lex De Haan       |   17000 | Steven King      |   24000 |
| Alexander Hunold  |    9000 | Lex De Haan      |   17000 |
| Bruce Ernst       |    6000 | Alexander Hunold |    9000 |
| David Austin      |    4800 | Alexander Hunold |    9000 |
| Valli Pataballa   |    4800 | Alexander Hunold |    9000 |
| Diana Lorentz     |    4200 | Alexander Hunold |    9000 |
| Nancy Greenberg   |   12000 | Neena Kochhar    |   17000 |
| Daniel Faviet     |    9000 | Nancy Greenberg  |   12000 |
| John Chen         |    8200 | Nancy Greenberg  |   12000 |
| Ismael Sciarra    |    7700 | Nancy Greenberg  |   12000 |
| Jose Manuel Urman |    7800 | Nancy Greenberg  |   12000 |
| Luis Popp         |    6900 | Nancy Greenberg  |   12000 |
| Den Raphaely      |   11000 | Steven King      |   24000 |
| Alexander Khoo    |    3100 | Den Raphaely     |   11000 |
| Shelli Baida      |    2900 | Den Raphaely     |   11000 |
| Sigal Tobias      |    2800 | Den Raphaely     |   11000 |
| Guy Himuro        |    2600 | Den Raphaely     |   11000 |
| Karen Colmenares  |    2500 | Den Raphaely     |   11000 |
| Matthew Weiss     |    8000 | Steven King      |   24000 |
| Adam Fripp        |    8200 | Steven King      |   24000 |
| Payam Kaufling    |    7900 | Steven King      |   24000 |
| Shanta Vollman    |    6500 | Steven King      |   24000 |
| Kevin Mourgos     |    5800 | Steven King      |   24000 |
| Julia Nayer       |    3200 | Matthew Weiss    |    8000 |
| Irene Mikkilineni |    2700 | Matthew Weiss    |    8000 |
| James Landry      |    2400 | Matthew Weiss    |    8000 |
| Steven Markle     |    2200 | Matthew Weiss    |    8000 |
| Laura Bissot      |    3300 | Adam Fripp       |    8200 |
| Mozhe Atkinson    |    2800 | Adam Fripp       |    8200 |
+-------------------+---------+------------------+---------+

Exercise 6: Count of employees under each manager

Goal: Display the number of direct reportees for each supervisor, sorted from highest to lowest.

SELECT 
    sup.employee_id,
    CONCAT(sup.first_name, ' ', sup.last_name) AS manager_name,
    COUNT(sub.employee_id) AS number_of_reportees
FROM employees sub 
JOIN employees sup ON sub.manager_id = sup.employee_id
GROUP BY sup.employee_id, sup.first_name, sup.last_name
ORDER BY number_of_reportees DESC;

Live Query Output:

+-------------+------------------+---------------------+
| employee_id | manager_name     | number_of_reportees |
+-------------+------------------+---------------------+
|         100 | Steven King      |                   8 |
|         108 | Nancy Greenberg  |                   5 |
|         114 | Den Raphaely     |                   5 |
|         103 | Alexander Hunold |                   4 |
|         120 | Matthew Weiss    |                   4 |
|         121 | Adam Fripp       |                   2 |
|         101 | Neena Kochhar    |                   1 |
|         102 | Lex De Haan      |                   1 |
+-------------+------------------+---------------------+

Syntax Notice: In MySQL/MariaDB, never insert a space between the function name and parenthesis (e.g. COUNT (sub.employee_id)) unless IGNORE_SPACE SQL mode is enabled, or MySQL will raise an unknown function syntax error.

Exercise 7: Employee count per department

Goal: Display each department’s name along with its current employee count.

SELECT 
    dept.department_name,
    COUNT(emp.employee_id) AS emp_count
FROM employees emp
JOIN departments dept ON emp.department_id = dept.department_id
GROUP BY dept.department_name
ORDER BY emp_count DESC;

Live Query Output:

+----------------------+-----------+
| department_name      | emp_count |
+----------------------+-----------+
| Shipping             |         7 |
| IT                   |         4 |
| Purchasing           |         3 |
| Human Resources      |         3 |
| Sales                |         2 |
| Payroll              |         2 |
| Marketing            |         2 |
| Shareholder Services |         1 |
| Executive            |         1 |
| Benefits             |         1 |
| Finance              |         1 |
| Accounting           |         1 |
| Corporate Tax        |         1 |
| Public Relations     |         1 |
| Control And Credit   |         1 |
+----------------------+-----------+

Exercise 8: Annual hiring trends (Hired year-wise)

Goal: Extract the hire year and count how many employees joined in each year.

SELECT 
    YEAR(hire_date) AS hired_year,
    COUNT(*) AS employees_hired_count
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY employees_hired_count DESC;

Live Query Output:

+------------+-----------------------+
| hired_year | employees_hired_count |
+------------+-----------------------+
|       1997 |                    10 |
|       1999 |                     5 |
|       1998 |                     4 |
|       1994 |                     3 |
|       1995 |                     2 |
|       1996 |                     1 |
|       2000 |                     1 |
|       1987 |                     1 |
|       1989 |                     1 |
|       1993 |                     1 |
|       1990 |                     1 |
|       1991 |                     1 |
+------------+-----------------------+

Exercise 9: Overall salary range and average

Goal: Find the minimum, maximum, and rounded average salary across the company.

SELECT 
    MIN(salary) AS min_sal, 
    MAX(salary) AS max_sal,
    ROUND(AVG(salary)) AS avg_sal
FROM employees;

Live Query Output:

+---------+---------+---------+
| min_sal | max_sal | avg_sal |
+---------+---------+---------+
|    2200 |   24000 |    6977 |
+---------+---------+---------+

Exercise 10: Segment employees into three salary tiers (CASE Statement)

Goal: Categorize employees into ‘low’ (< 5000), ‘mid’ (5000–9999), and ‘high’ (>= 10000) tiers.

SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    salary,
    CASE
        WHEN salary >= 2000 AND salary < 5000 THEN 'low' 
        WHEN salary >= 5000 AND salary < 10000 THEN 'mid'
        ELSE 'high'
    END AS salary_level
FROM employees
ORDER BY employee ASC;

Live Query Output (Sample First 10 Records):

+-------------------+--------+--------------+
| employee          | salary | salary_level |
+-------------------+--------+--------------+
| Adam Fripp        |   8200 | mid          |
| Alexander Hunold  |   9000 | mid          |
| Alexander Khoo    |   3100 | low          |
| Bruce Ernst       |   6000 | mid          |
| Daniel Faviet     |   9000 | mid          |
| David Austin      |   4800 | low          |
| Den Raphaely      |  11000 | high         |
| Diana Lorentz     |   4200 | low          |
| Guy Himuro        |   2600 | low          |
| Irene Mikkilineni |   2700 | low          |
+-------------------+--------+--------------+

Exercise 11: Employees whose first name contains “an”

Goal: Case-insensitive search for names containing the substring “an”.

SELECT first_name
FROM employees
WHERE LOWER(first_name) LIKE '%an%';

Live Query Output:

+-------------+
| first_name  |
+-------------+
| Alexander   |
| Diana       |
| Nancy       |
| Daniel      |
| Jose Manuel |
| Alexander   |
| Shanta      |
+-------------+

Exercise 12: Format phone numbers with hyphens

Goal: Replace dots (.) in phone numbers with hyphens (-).

SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    REPLACE(phone_number, '.', '-') AS phone_number
FROM employees;

Live Query Output:

+-------------------+--------------+
| employee          | phone_number |
+-------------------+--------------+
| Steven King       | 515-123-4567 |
| Neena Kochhar     | 515-123-4568 |
| Lex De Haan       | 515-123-4569 |
| Alexander Hunold  | 590-423-4567 |
| Bruce Ernst       | 590-423-4568 |
| David Austin      | 590-423-4569 |
+-------------------+--------------+

Exercise 13: Employees who joined in August 1994

Goal: Filter records by specific year and month using date functions.

SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    hire_date
FROM employees
WHERE YEAR(hire_date) = 1994
  AND MONTH(hire_date) = 8;

Live Query Output:

+-----------------+------------+
| employee        | hire_date  |
+-----------------+------------+
| Nancy Greenberg | 1994-08-17 |
| Daniel Faviet   | 1994-08-12 |
+-----------------+------------+

Exercise 14: Employees who earn more than the company average

Goal: Display employees earning above average along with their department names.

SELECT 
    CONCAT(emp.first_name, ' ', emp.last_name) AS name,
    emp.employee_id, 
    dept.department_name AS department,
    dept.department_id,
    emp.salary
FROM departments dept
JOIN employees emp ON dept.department_id = emp.department_id
WHERE emp.salary > (SELECT AVG(salary) FROM employees)
ORDER BY dept.department_id;

Live Query Output:

+-------------------+-------------+----------------------+---------------+--------+
| name              | employee_id | department           | department_id | salary |
+-------------------+-------------+----------------------+---------------+--------+
| Steven King       |         100 | Marketing            |            20 |  24000 |
| Neena Kochhar     |         101 | Marketing            |            20 |  17000 |
| Lex De Haan       |         102 | Purchasing           |            30 |  17000 |
| Den Raphaely      |         114 | Purchasing           |            30 |  11000 |
| Payam Kaufling    |         122 | Human Resources      |            40 |   7900 |
| Matthew Weiss     |         120 | Shipping             |            50 |   8000 |
| Adam Fripp        |         121 | Shipping             |            50 |   8200 |
| Alexander Hunold  |         103 | IT                   |            60 |   9000 |
| Nancy Greenberg   |         108 | Finance              |           100 |  12000 |
| Jose Manuel Urman |         112 | Shareholder Services |           150 |   7800 |
| Ismael Sciarra    |         111 | Benefits             |           160 |   7700 |
| Daniel Faviet     |         109 | Payroll              |           170 |   9000 |
| John Chen         |         110 | Payroll              |           170 |   8200 |
+-------------------+-------------+----------------------+---------------+--------+

Exercise 15: Maximum salary from each department

Goal: Calculate the highest compensation within each department.

SELECT 
    dept.department_id,
    dept.department_name AS department,
    MAX(emp.salary) AS maximum_salary
FROM departments dept
JOIN employees emp ON dept.department_id = emp.department_id
GROUP BY dept.department_id, dept.department_name
ORDER BY dept.department_id ASC;

Live Query Output:

+---------------+----------------------+----------------+
| department_id | department           | maximum_salary |
+---------------+----------------------+----------------+
|            20 | Marketing            |          24000 |
|            30 | Purchasing           |          17000 |
|            40 | Human Resources      |           7900 |
|            50 | Shipping             |           8200 |
|            60 | IT                   |           9000 |
|            70 | Public Relations     |           2900 |
|            80 | Sales                |           5800 |
|            90 | Executive            |           2400 |
|           100 | Finance              |          12000 |
|           110 | Accounting           |           2800 |
|           130 | Corporate Tax        |           2500 |
|           140 | Control And Credit   |           6900 |
|           150 | Shareholder Services |           7800 |
|           160 | Benefits             |           7700 |
|           170 | Payroll              |           9000 |
+---------------+----------------------+----------------+

Exercise 16: The 5 least earning employees

Goal: Find the 5 employees with the lowest compensation.

SELECT first_name, last_name, employee_id, salary
FROM employees
ORDER BY salary ASC
LIMIT 5;

Live Query Output:

+------------+-------------+-------------+--------+
| first_name | last_name   | employee_id | salary |
+------------+-------------+-------------+--------+
| Steven     | Markle      |         128 |   2200 |
| James      | Landry      |         127 |   2400 |
| Karen      | Colmenares  |         119 |   2500 |
| Guy        | Himuro      |         118 |   2600 |
| Irene      | Mikkilineni |         126 |   2700 |
+------------+-------------+-------------+--------+

Exercise 17: Employees hired in the 1980s

Goal: Retrieve employees who were hired between 1980 and 1989.

SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS employee,
    hire_date
FROM employees
WHERE YEAR(hire_date) BETWEEN 1980 AND 1989;

Live Query Output:

+-------------+---------------+------------+
| employee_id | employee      | hire_date  |
+-------------+---------------+------------+
|         100 | Steven King   | 1987-06-17 |
|         101 | Neena Kochhar | 1989-11-21 |
+-------------+---------------+------------+

Exercise 18: First name and reversed name

Goal: Print first names alongside their reverse spelling.

SELECT 
    LOWER(first_name) AS name, 
    LOWER(REVERSE(first_name)) AS name_in_reverse
FROM employees
LIMIT 8;

Live Query Output:

+-----------+-----------------+
| name      | name_in_reverse |
+-----------+-----------------+
| steven    | nevets          |
| neena     | aneen           |
| lex       | xel             |
| alexander | rednaxela       |
| bruce     | ecurb           |
| david     | divad           |
| valli     | illav           |
| diana     | anaid           |
+-----------+-----------------+

Exercise 19: Employees hired after the 15th day of any month

Goal: Select employees whose hire date day-of-month is greater than 15.

SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS employee,
    hire_date
FROM employees
WHERE DAY(hire_date) > 15;

Live Query Output:

+-------------+------------------+------------+
| employee_id | employee         | hire_date  |
+-------------+------------------+------------+
|         100 | Steven King      | 1987-06-17 |
|         101 | Neena Kochhar    | 1989-11-21 |
|         103 | Alexander Hunold | 1990-09-30 |
|         104 | Bruce Ernst      | 1991-05-21 |
|         105 | David Austin     | 1997-06-25 |
|         108 | Nancy Greenberg  | 1994-08-17 |
|         120 | Matthew Weiss    | 1996-07-18 |
+-------------+------------------+------------+

Exercise 20: Managers and reportees working in different departments

Goal: Identify cross-department reporting lines where an employee’s department differs from their direct manager’s department.

SELECT 
    CONCAT(mgr.first_name, ' ', mgr.last_name) AS manager,
    CONCAT(emp.first_name, ' ', emp.last_name) AS employee,
    mgr.department_id AS mgr_dept,
    emp.department_id AS emp_dept
FROM employees emp
JOIN employees mgr ON emp.manager_id = mgr.employee_id
WHERE emp.department_id <> mgr.department_id
ORDER BY manager ASC;

Live Query Output:

+------------------+-------------------+----------+----------+
| manager          | employee          | mgr_dept | emp_dept |
+------------------+-------------------+----------+----------+
| Adam Fripp       | Mozhe Atkinson    |       50 |      110 |
| Alexander Hunold | Valli Pataballa   |       60 |       40 |
| Alexander Hunold | Diana Lorentz     |       60 |       40 |
| Den Raphaely     | Alexander Khoo    |       30 |       80 |
| Den Raphaely     | Karen Colmenares  |       30 |      130 |
| Den Raphaely     | Shelli Baida      |       30 |       70 |
| Den Raphaely     | Guy Himuro        |       30 |       60 |
| Lex De Haan      | Alexander Hunold  |       30 |       60 |
| Matthew Weiss    | James Landry      |       50 |       90 |
| Nancy Greenberg  | Luis Popp         |      100 |      140 |
| Nancy Greenberg  | Ismael Sciarra    |      100 |      160 |
| Nancy Greenberg  | Daniel Faviet     |      100 |      170 |
| Nancy Greenberg  | Jose Manuel Urman |      100 |      150 |
| Nancy Greenberg  | John Chen         |      100 |      170 |
| Neena Kochhar    | Nancy Greenberg   |       20 |      100 |
| Steven King      | Adam Fripp        |       20 |       50 |
| Steven King      | Kevin Mourgos     |       20 |       80 |
| Steven King      | Payam Kaufling    |       20 |       40 |
| Steven King      | Lex De Haan       |       20 |       30 |
| Steven King      | Den Raphaely      |       20 |       30 |
| Steven King      | Matthew Weiss     |       20 |       50 |
| Steven King      | Shanta Vollman    |       20 |       50 |
+------------------+-------------------+----------+----------+

🛡️ Chapter 11: Production Best Practices, Security & Cheatsheet

11.1 Indexing for Maximum Performance

In high-throughput environments, full table scans degrade application responsiveness. Always create indexes on foreign keys, join columns, and frequently filtered fields:

-- Indexing foreign keys to accelerate joins
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_emp_mgr ON employees(manager_id);

-- Composite index for search by name
CREATE INDEX idx_emp_name ON employees(last_name, first_name);

11.2 Avoiding Common Production Pitfalls

  • Never run SELECT * in production: Always specify the exact columns needed. SELECT * increases network transfer, consumes CPU cycles, and prevents covering index optimizations.
  • Prevent SQL Injection: Never concatenate user input directly into SQL strings. Always use prepared statements / parameterized queries via your application framework (PDO, mysqli, ActiveRecord, SQLAlchemy).
  • Always Test in Transactions: When testing write operations (UPDATE, DELETE), wrap them inside a transaction with START TRANSACTION; and rollback if results look unexpected: 
    START TRANSACTION;
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 60;
-- Verify results:
SELECT employee_id, salary FROM employees WHERE department_id = 60;
-- If correct: COMMIT; otherwise: ROLLBACK;
ROLLBACK;

🎓 Practice Challenges & Next Steps

  1. Find Departments without Employees: Write a LEFT JOIN query starting from departments to find departments that currently have 0 employees assigned.
  2. Department Budget Report: Write a query showing the total payroll cost (SUM(salary)) and employee count per department, ordering from highest cost to lowest.
  3. Salary Rank within Department: Use modern MySQL window functions (DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)) to find the highest-paid employee in each individual department.

Leave a Reply

Your email address will not be published. Required fields are marked *