Complete MySQL Capstone Project, Portfolio Review, and Interview Preparation

Course: MySQL Training

Topic: Complete MySQL Capstone Project, Portfolio Review, and Interview Preparation

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to the MySQL Capstone Project

A MySQL capstone project is a practical assignment that combines the database concepts learned throughout a MySQL course into one complete, real-world application. It demonstrates the ability to design databases, create tables, establish relationships, insert and update records, write SQL queries, enforce constraints, manage transactions, optimize performance, and produce meaningful business reports.

A capstone project is useful for students, freshers, database developers, data analysts, backend developers, and professionals preparing for SQL-related interviews. It provides evidence of practical database skills rather than relying only on theoretical knowledge.

In this module, we will build a sample Retail Sales and Inventory Management System. The project will manage customers, products, categories, orders, order items, payments, and inventory. We will also prepare a portfolio presentation and practice common MySQL interview questions.

1.1 Learning Objectives

• Understand the purpose and structure of a complete MySQL capstone project.
• Translate business requirements into a relational database design.
• Create tables with primary keys, foreign keys, unique constraints, and appropriate data types.
• Insert, retrieve, update, and delete records safely.
• Use joins, aggregate functions, subqueries, and Common Table Expressions (CTEs).
• Generate sales, customer, product, and inventory reports.
• Apply transactions and understand rollback and commit operations.
• Analyze query performance using indexes and EXPLAIN.
• Document and present the project in a professional portfolio.
• Prepare for MySQL technical interviews and practical SQL assessments.

1.2 Skills Demonstrated by the Project

2. Understanding the Capstone Project Requirements

Before creating tables, identify the business problem and determine what the database must store, calculate, and report. A well-defined project begins with clear requirements rather than writing SQL immediately.

2.1 Project Scenario

A retail business sells products to customers through an online or physical store. The business needs a centralized database to track customer information, product details, categories, orders, payments, and available inventory. Management also wants reports showing monthly revenue, best-selling products, repeat customers, and low-stock items.

2.2 Functional Requirements

• Register customers and maintain their contact information.
• Store product names, categories, prices, and stock quantities.
• Record customer orders and the products included in each order.
• Track order status and payment status.
• Calculate order totals using product quantities and prices.
• Identify products with low inventory.
• Generate monthly sales and product performance reports.
• Prevent invalid references between related records.
• Support safe transaction handling for important database operations.

2.3 Non-Functional Requirements

• Accuracy: Store correct customer, order, payment, and inventory information.
• Consistency: Use constraints and transactions to maintain valid data.
• Performance: Design indexes for frequently filtered and joined columns.
• Security: Restrict access to authorized users and avoid exposing sensitive information.
• Maintainability: Use clear names, comments, documentation, and organized SQL scripts.
• Recoverability: Maintain suitable backup and restore procedures.

3. Project Planning and Database Design

3.1 Identify the Main Entities

An entity represents a major object or concept about which the database stores information.

3.2 Understand Database Relationships

• Customers to Orders: One customer can place many orders. Each order belongs to one customer.
• Categories to Products: One category can contain many products.
• Orders to Order Items: One order can contain multiple order items.
• Products to Order Items: A product can appear in many order items.
• Orders to Payments: An order can have multiple payment records if the business supports partial payments or payment attempts.

3.3 Primary Keys and Foreign Keys

A primary key uniquely identifies a row in a table. A foreign key references a primary or unique key in another table and helps maintain referential integrity.

For example, customer_id is the primary key in the Customers table. The same column can appear as a foreign key in the Orders table, ensuring that an order references an existing customer.

3.4 Normalization

Normalization organizes data into related tables to reduce duplication and prevent inconsistent updates.

• First Normal Form (1NF): Store individual values in columns and avoid repeating groups.
• Second Normal Form (2NF): Meet 1NF and ensure non-key attributes depend on the entire candidate key, where composite keys are involved.
• Third Normal Form (3NF): Meet 2NF and remove inappropriate transitive dependencies between non-key attributes.

For example, storing customer details repeatedly in every order row creates unnecessary duplication. A separate Customers table allows each customer to be stored once and referenced by orders.

4. Setting Up the MySQL Environment

4.1 Required Tools

• MySQL Server: Executes SQL statements and manages databases.
• MySQL Workbench: Provides a graphical interface for queries, database design, and administration.
• Command-Line Client: Allows SQL commands to be executed from a terminal.
• Code Editor: Helps organize SQL scripts and project documentation.
• Git: Tracks changes and helps publish a version-controlled portfolio.

4.2 Verify the MySQL Installation

SELECT VERSION();
SELECT CURRENT_DATE();
SELECT DATABASE();

The first query returns the MySQL server version. The second returns the current date. The third displays the currently selected database, or NULL if none is selected.

4.3 Create the Project Database

CREATE DATABASE retail_capstone;
USE retail_capstone;

Use a dedicated database for the project. This keeps its tables separate from unrelated practice databases.

5. Creating the Database Tables

The following SQL creates the core tables for the retail system. It uses InnoDB for transaction support and foreign-key enforcement.

5.1 Create the Customers Table

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20),
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

The AUTO_INCREMENT attribute generates a new identifier for each inserted customer. The email column is unique so that duplicate email values are not accepted.

5.2 Create the Categories Table

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255)
) ENGINE = InnoDB;

5.3 Create the Products Table

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    product_name VARCHAR(150) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    reorder_level INT NOT NULL DEFAULT 5,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT chk_product_price CHECK (unit_price >= 0),
    CONSTRAINT chk_stock_quantity CHECK (stock_quantity >= 0),
    CONSTRAINT chk_reorder_level CHECK (reorder_level >= 0),
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE = InnoDB;

The DECIMAL data type is appropriate for monetary values because it stores fixed-precision decimal numbers. CHECK constraints help reject negative prices and stock quantities on supported MySQL versions.

5.4 Create the Orders Table

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    order_status ENUM(
        'Pending',
        'Processing',
        'Shipped',
        'Delivered',
        'Cancelled'
    ) NOT NULL DEFAULT 'Pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE = InnoDB;

5.5 Create the Order Items Table

CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT chk_order_quantity CHECK (quantity > 0),
    CONSTRAINT chk_order_item_price CHECK (unit_price >= 0),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE = InnoDB;

The unit price is stored in the order item because a product's current price may change after the order is placed. Keeping the price charged at the time of sale preserves historical order information.

5.6 Create the Payments Table

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM(
        'Cash',
        'Card',
        'UPI',
        'Bank Transfer'
    ) NOT NULL,
    payment_status ENUM(
        'Pending',
        'Completed',
        'Failed',
        'Refunded'
    ) NOT NULL DEFAULT 'Pending',
    CONSTRAINT chk_payment_amount CHECK (amount >= 0),
    CONSTRAINT fk_payments_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE = InnoDB;

5.7 Verify the Database Structure

SHOW TABLES;
DESCRIBE customers;
DESCRIBE products;
DESCRIBE orders;
DESCRIBE order_items;
DESCRIBE payments;
SHOW CREATE TABLE products;

Verification is an important project step. It confirms that the database objects were created with the intended columns, data types, indexes, and constraints.

6. Inserting Sample Data

Sample data allows you to test joins, calculations, business reports, and edge cases. Use realistic but fictional data in public portfolio examples.

6.1 Insert Categories

INSERT INTO categories (category_name, description)
VALUES
('Electronics', 'Electronic devices and accessories'),
('Home Appliances', 'Appliances for household use'),
('Accessories', 'Useful everyday accessories');

6.2 Insert Customers

INSERT INTO customers
(first_name, last_name, email, phone, city)
VALUES
('Aarav', 'Sharma', '[email protected]', '9000000001', 'Mumbai'),
('Meera', 'Patel', '[email protected]', '9000000002', 'Pune'),
('Rohan', 'Verma', '[email protected]', '9000000003', 'Delhi'),
('Ananya', 'Singh', '[email protected]', '9000000004', 'Mumbai');

6.3 Insert Products

INSERT INTO products
(category_id, product_name, unit_price, stock_quantity, reorder_level)
VALUES
(1, 'Wireless Mouse', 799.00, 40, 10),
(1, 'Keyboard', 1499.00, 25, 8),
(1, 'USB Hub', 999.00, 6, 10),
(2, 'Electric Kettle', 1799.00, 15, 5),
(3, 'Laptop Sleeve', 599.00, 4, 8);

6.4 Insert Orders and Order Items

INSERT INTO orders (customer_id, order_status)
VALUES
(1, 'Delivered'),
(2, 'Processing'),
(1, 'Pending'),
(3, 'Delivered');

INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 2, 799.00),
(1, 3, 1, 999.00),
(2, 2, 1, 1499.00),
(2, 4, 1, 1799.00),
(3, 5, 2, 599.00),
(4, 1, 1, 799.00);

6.5 Insert Payments

INSERT INTO payments
(order_id, amount, payment_method, payment_status)
VALUES
(1, 2597.00, 'UPI', 'Completed'),
(2, 3298.00, 'Card', 'Completed'),
(3, 1198.00, 'UPI', 'Pending'),
(4, 799.00, 'Bank Transfer', 'Completed');

In a production system, order totals and payment amounts should be calculated and validated by application logic or carefully designed database routines. The sample values above illustrate SQL reporting and should be checked against the order items before being used as financial records.

7. Writing Essential SQL Queries

7.1 Retrieve All Products

SELECT
    product_id,
    product_name,
    unit_price,
    stock_quantity
FROM products
ORDER BY product_name;

7.2 Filter Products by Price

SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 500 AND 1500
ORDER BY unit_price DESC;

The BETWEEN operator includes both boundary values. This query returns products priced from 500 through 1500.

7.3 Search by Product Name

SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%Mouse%';

The percent sign is a wildcard that matches zero or more characters. A leading wildcard can prevent efficient use of a normal B-tree index for this search pattern.

7.4 Sort and Limit Results

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 3;

This query displays the three most expensive products. If several products have the same price, add a second sort column for predictable ordering.

7.5 Update a Product Price

UPDATE products
SET unit_price = 849.00
WHERE product_id = 1;

Always use a suitable WHERE clause when updating records. Before executing a large update, run a SELECT statement with the same filter to verify the affected rows.

7.6 Delete a Test Record Safely

SELECT *
FROM customers
WHERE customer_id = 4;

After confirming that the record is appropriate to remove, use a controlled DELETE statement. Related records and foreign-key restrictions must be considered before deletion.

DELETE FROM customers
WHERE customer_id = 4;

In a real retail application, deleting a customer with order history may be inappropriate. A deactivation or anonymization process may be more suitable, depending on business and legal requirements.

8. Advanced SQL for the Capstone Project

8.1 INNER JOIN: Display Orders with Customer Details

SELECT
    o.order_id,
    o.order_date,
    o.order_status,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

An INNER JOIN returns rows with matching records in both tables. This query connects each order to its customer.

8.2 JOIN Multiple Tables to Display Order Details

SELECT
    o.order_id,
    c.first_name,
    c.last_name,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY o.order_id, p.product_name;

This query combines information from four tables. It calculates each line total by multiplying the ordered quantity by the price stored on the order item.

8.3 LEFT JOIN: Find Customers with No Orders

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

A LEFT JOIN retains all rows from the left table, even when there is no matching row on the right. The NULL filter identifies customers without orders.

8.4 GROUP BY: Calculate Revenue per Product

SELECT
    p.product_id,
    p.product_name,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS gross_sales
FROM products AS p
INNER JOIN order_items AS oi
    ON p.product_id = oi.product_id
INNER JOIN orders AS o
    ON oi.order_id = o.order_id
WHERE o.order_status <> 'Cancelled'
GROUP BY p.product_id, p.product_name
ORDER BY gross_sales DESC;

GROUP BY creates one result per product. SUM calculates total units and gross sales for the selected orders. Decide explicitly whether pending, refunded, or cancelled orders should be included in each business metric.

8.5 HAVING: Find High-Value Products

SELECT
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS gross_sales
FROM products AS p
INNER JOIN order_items AS oi
    ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
HAVING SUM(oi.quantity * oi.unit_price) > 2000
ORDER BY gross_sales DESC;

WHERE filters individual rows before grouping, whereas HAVING filters groups after aggregate calculations.

8.6 Subquery: Find Products Above the Average Price

SELECT product_name, unit_price
FROM products
WHERE unit_price > (
    SELECT AVG(unit_price)
    FROM products
)
ORDER BY unit_price DESC;

The inner query calculates the average product price. The outer query returns products whose prices exceed that average.

8.7 CTE: Calculate Order Totals

WITH order_totals AS (
    SELECT
        order_id,
        SUM(quantity * unit_price) AS order_total
    FROM order_items
    GROUP BY order_id
)
SELECT
    o.order_id,
    o.order_date,
    o.order_status,
    COALESCE(ot.order_total, 0) AS order_total
FROM orders AS o
LEFT JOIN order_totals AS ot
    ON o.order_id = ot.order_id
ORDER BY o.order_date DESC;

A Common Table Expression, or CTE, defines a named result set that can be referenced by the main query. This makes complex queries easier to read and maintain. CTEs are available in MySQL 8.0 and later.

8.8 Window Function: Rank Best-Selling Products

WITH product_sales AS (
    SELECT
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.unit_price) AS gross_sales
    FROM products AS p
    INNER JOIN order_items AS oi
        ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.product_name
)
SELECT
    product_name,
    gross_sales,
    DENSE_RANK() OVER (
        ORDER BY gross_sales DESC
    ) AS sales_rank
FROM product_sales
ORDER BY sales_rank, product_name;

DENSE_RANK assigns the same rank to tied values without leaving gaps in the next rank. Window functions help build analytical reports without collapsing the result to a single aggregate row.

9. Building Business Reports

9.1 Monthly Sales Report

SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS sales_month,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS gross_sales
FROM orders AS o
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_status IN ('Shipped', 'Delivered')
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY sales_month;

This report summarizes order counts, units sold, and gross sales by month. The selected statuses are an example policy; the business must define whether sales are recognized at shipment, delivery, payment, or another event.

9.2 Top Five Customers by Order Value

SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(oi.quantity * oi.unit_price) AS total_order_value
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_status <> 'Cancelled'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_order_value DESC
LIMIT 5;

This query identifies customers with the highest recorded order-item value. If refunds, discounts, shipping, taxes, or partial cancellations exist, include those business rules before describing this metric as net revenue.

9.3 Low-Stock Inventory Report

SELECT
    product_id,
    product_name,
    stock_quantity,
    reorder_level
FROM products
WHERE stock_quantity <= reorder_level
  AND is_active = TRUE
ORDER BY stock_quantity ASC, product_name;

The report identifies active products that have reached or fallen below their reorder levels. A real inventory system may also need reserved stock, purchase orders, supplier lead times, and warehouse locations.

9.4 Category-Wise Sales Report

SELECT
    c.category_name,
    COUNT(DISTINCT p.product_id) AS products_sold,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS gross_sales
FROM categories AS c
INNER JOIN products AS p
    ON c.category_id = p.category_id
INNER JOIN order_items AS oi
    ON p.product_id = oi.product_id
INNER JOIN orders AS o
    ON oi.order_id = o.order_id
WHERE o.order_status IN ('Shipped', 'Delivered')
GROUP BY c.category_id, c.category_name
ORDER BY gross_sales DESC;

9.5 Average Order Value

WITH order_totals AS (
    SELECT
        o.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders AS o
    INNER JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.order_status IN ('Shipped', 'Delivered')
    GROUP BY o.order_id
)
SELECT
    COUNT(*) AS completed_order_count,
    AVG(order_total) AS average_order_value
FROM order_totals;

Average order value is the average item value per included order. The calculation should use a consistent definition of completed orders and clarify whether tax, shipping, discounts, and refunds are included.

10. Transactions and Data Consistency

Transactions group related database operations into a unit of work. They are especially important when one business action requires multiple changes that must succeed or fail together.

10.1 ACID Properties

10.2 Transaction Example: Reserve Inventory

START TRANSACTION;

SELECT stock_quantity
FROM products
WHERE product_id = 1
FOR UPDATE;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 1
  AND stock_quantity >= 2;

SELECT ROW_COUNT() AS rows_updated;

COMMIT;

This example demonstrates the basic transaction pattern, but a production application must check that the row exists and that exactly one row was updated. If the required stock is unavailable or any step fails, the application should issue ROLLBACK instead of COMMIT. The transaction should also create the order and order-item records consistently.

10.3 Rollback Example

START TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 2
  AND stock_quantity > 0;

-- Run validation checks here.
-- If a required condition fails:
ROLLBACK;

-- Use COMMIT only when every required step succeeds.

ROLLBACK reverses uncommitted changes in the transaction. It does not undo changes that have already been committed.

10.4 Savepoints

START TRANSACTION;

UPDATE products
SET unit_price = unit_price * 1.05
WHERE category_id = 1;

SAVEPOINT after_price_update;

UPDATE products
SET stock_quantity = -1
WHERE product_id = 1;

ROLLBACK TO SAVEPOINT after_price_update;
COMMIT;

This example illustrates how a savepoint can undo later work while retaining earlier changes. The invalid negative stock update will normally be rejected by the CHECK constraint, and the application should still handle the error correctly.

11. Indexing and Query Optimization

Indexes help MySQL locate matching records without scanning every row in a table. They can improve read performance, but they also require storage and add overhead to INSERT, UPDATE, and DELETE operations.

11.1 Create Useful Indexes

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

CREATE INDEX idx_order_items_product
ON order_items (product_id);

CREATE INDEX idx_products_category
ON products (category_id);

Primary keys and unique constraints normally create indexes automatically. InnoDB also requires suitable indexes for foreign-key checks, so inspect existing indexes before adding duplicates.

11.2 Understand Composite Indexes

A composite index contains multiple columns. An index on (customer_id, order_date) is particularly useful for queries filtering by customer and, optionally, by date. Its usefulness for date-only filtering is generally more limited because of the leftmost-prefix principle.

11.3 Use EXPLAIN

EXPLAIN
SELECT
    order_id,
    order_date,
    order_status
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC;

EXPLAIN shows the optimizer's intended query plan, including possible indexes, selected access paths, and estimated row counts. Compare plans before and after changes instead of assuming that every index improves performance.

11.4 Query Optimization Checklist

• Select only the columns required by the report.
• Filter rows as early as appropriate.
• Use indexes that match frequent filter and join patterns.
• Ensure join columns have compatible data types and suitable indexes.
• Avoid wrapping indexed columns in functions in filters when a range condition can be used instead.
• Use LIMIT for exploratory queries that do not require every row.
• Check EXPLAIN output and measure query execution on representative data.
• Avoid adding redundant indexes without a measurable benefit.
• Review slow queries and use appropriate monitoring tools.

12. Data Validation and Testing

Testing confirms that the database behaves correctly under normal conditions, invalid input, and edge cases. A strong capstone includes more than a few successful SELECT statements.

12.1 Functional Tests

• Verify that a customer can be inserted with valid information.
• Verify that duplicate customer email addresses are rejected.
• Verify that a product cannot reference a nonexistent category.
• Verify that a product cannot have a negative stock quantity.
• Verify that an order cannot reference a nonexistent customer.
• Verify that an order item cannot reference a nonexistent product.
• Verify that order totals match the sum of their order-item values.
• Verify that low-stock reports return the expected products.
• Verify that cancelled orders are treated according to the documented reporting rules.
• Verify that a failed multi-step transaction does not leave unintended partial changes.

12.2 Example: Find Orders Without Order Items

SELECT
    o.order_id,
    o.order_date,
    o.order_status
FROM orders AS o
LEFT JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE oi.order_item_id IS NULL;

This query identifies orders that have no order-item records. Whether such orders are invalid depends on the business workflow; a draft order may temporarily have no items, whereas a finalized order normally should not.

12.3 Example: Find Potential Duplicate Customers

SELECT
    LOWER(TRIM(email)) AS normalized_email,
    COUNT(*) AS record_count
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

This query helps detect emails that differ only in case or surrounding spaces. If such normalization is a business requirement, enforce it consistently during data entry and consider an appropriate unique normalized key design.

12.4 Test Edge Cases

• Empty result sets.
• NULL values in optional fields.
• Orders with multiple items.
• Customers with no orders.
• Products with zero stock.
• Orders with multiple payment records.
• Products with identical prices or tied sales totals.
• Very large result sets and date ranges.
• Concurrent attempts to purchase the same limited inventory.

13. Security and Backup Practices

13.1 Database Security

• Use separate database accounts for applications, developers, and administrators.
• Grant only the permissions required for each role.
• Use parameterized queries in application code to reduce SQL injection risk.
• Never store database passwords or secrets in a public repository.
• Avoid using real customer data in portfolio demonstrations.
• Do not expose payment card details or other sensitive personal information in reports.
• Review access permissions and rotate credentials according to organizational policy.

13.2 Backup and Restore

A backup is useful only if it can be restored successfully. For a local practice database, MySQL utilities such as mysqldump can create logical backups.

mysqldump -u your_username -p retail_capstone > retail_capstone.sql

To restore the SQL dump, create or select an appropriate target database and use the MySQL command-line client:

mysql -u your_username -p retail_capstone < retail_capstone.sql

Replace the example username with your actual database account. Test restoration in a separate environment before relying on a backup. Production systems may require additional controls for consistency, encryption, retention, and point-in-time recovery.

14. Preparing the Project Portfolio

A portfolio presents your work in a way that allows a recruiter, interviewer, or client to understand the problem, the database design, the SQL techniques used, and the results. A well-organized project should be reproducible by another person.

14.1 Recommended Project Folder Structure

mysql-retail-capstone/
├── README.md
├── schema/
│   ├── 01_create_database.sql
│   └── 02_create_tables.sql
├── data/
│   └── 03_insert_sample_data.sql
├── queries/
│   ├── 04_basic_queries.sql
│   ├── 05_joins_and_subqueries.sql
│   ├── 06_advanced_sql.sql
│   └── 07_business_reports.sql
├── optimization/
│   └── 08_indexes_and_explain.sql
├── tests/
│   └── 09_validation_tests.sql
├── documentation/
│   ├── er_diagram.png
│   └── data_dictionary.md
└── screenshots/
    ├── database_tables.png
    ├── query_results.png
    └── explain_plan.png

This is a suggested structure. Adapt it to the project size and the tools used. Avoid including passwords, confidential information, or unnecessary copies of generated files.

14.2 What to Include in README.md

1. Project Title: Retail Sales and Inventory Management System Using MySQL.
2. Project Overview: Explain the business problem the database solves.
3. Objectives: List the main database and reporting goals.
4. Tools and Technologies: State the MySQL version and any additional tools used.
5. Database Schema: Describe the tables, primary keys, foreign keys, and relationships.
6. Setup Instructions: Explain how to create the database and import the sample data.
7. Key SQL Queries: Describe joins, subqueries, CTEs, window functions, and reports.
8. Optimization: Document indexes and the reasoning behind query improvements.
9. Testing: Include validation checks and expected outcomes.
10. Results: Show sample report outputs and explain their business meaning.
11. Limitations and Future Improvements: Describe what could be extended.

14.3 Sample Portfolio Project Description

Retail Sales and Inventory Management System Using MySQL

Designed a relational database to manage customers, product categories, inventory, orders, order items, and payments. Implemented primary and foreign keys, validation constraints, and transaction-based workflows to improve data integrity. Developed SQL reports for monthly sales, product performance, customer order value, and low-stock inventory. Used joins, aggregate functions, CTEs, window functions, and EXPLAIN to analyze data and evaluate query performance.

Customize this description to reflect the work you actually completed. Do not claim tools, performance gains, or features that you did not implement or test.

14.4 Screenshots and Demonstration Evidence

• Database and table structure.
• Entity-relationship diagram.
• Sample data from each major table.
• Join query results showing order details.
• Monthly sales and top-product reports.
• Low-stock inventory report.
• EXPLAIN output for an important query.
• Successful validation tests and examples of rejected invalid data.

Use readable screenshots with meaningful filenames. Remove personal data, credentials, connection details, and any sensitive information before publishing.

15. Creating an Entity-Relationship Diagram

An entity-relationship diagram (ERD) visually represents tables and their relationships. It helps reviewers understand the database design quickly.

In MySQL Workbench, you can create a model, add tables, define primary and foreign keys, and arrange the relationships in an EER diagram. Include the exported diagram in your project documentation.

16. Portfolio Review Checklist

Review the project as if you were the technical interviewer or a hiring manager. The aim is to make the database understandable, reliable, and easy to run.

16.1 Common Portfolio Mistakes

• Uploading SQL files without explaining the business purpose.
• Using tables without primary keys or suitable relationships.
• Writing queries that return incorrect totals because of join duplication.
• Creating indexes without explaining why they are needed.
• Using SELECT * in every query when only a few columns are required.
• Publishing database passwords or real customer information.
• Claiming that a query is optimized without checking its execution plan.
• Providing no setup instructions or sample data.
• Failing to explain limitations and assumptions.

17. MySQL Interview Preparation Strategy

Interview preparation should combine conceptual knowledge, hands-on query practice, debugging, and the ability to explain design choices. Memorizing definitions alone is not enough for many technical roles.

17.1 Important Topics to Revise

• Relational databases and RDBMS concepts.
• DDL, DML, DQL, DCL, and transaction control statements.
• Primary keys, foreign keys, unique constraints, and CHECK constraints.
• Normalization and database relationships.
• INNER JOIN, LEFT JOIN, self joins, and cross joins.
• GROUP BY, HAVING, aggregate functions, and NULL handling.
• Subqueries, correlated subqueries, and CTEs.
• Window functions, ranking, and running totals.
• Transactions, ACID, isolation levels, locks, and deadlocks.
• Indexes, composite indexes, EXPLAIN, and query optimization.
• Views, stored procedures, triggers, and events.
• Backup, recovery, permissions, and SQL injection prevention.

17.2 Answering Technical Questions Effectively

1. Start with a direct definition.
2. Explain the purpose of the concept.
3. Give a small SQL example when useful.
4. Describe one practical use case.
5. Mention relevant limitations or trade-offs.

For example, when asked about indexes, explain that they help locate rows efficiently, give an example of an index on a frequently filtered column, and mention that indexes use storage and increase the cost of data modifications.

18. MySQL Interview Questions and Answers

Q1. What is MySQL?

Answer: MySQL is a relational database management system that stores data in tables and supports SQL for defining, retrieving, and modifying data. It is commonly used in websites, business applications, reporting systems, and backend services.

Q2. What is the difference between SQL and MySQL?

Answer: SQL is a language used to work with relational databases. MySQL is a database management system that implements SQL along with its own features and syntax.

Q3. What is a primary key?

Answer: A primary key uniquely identifies each row in a table. It cannot contain NULL values, and the primary-key values must be unique.

Q4. What is a foreign key?

Answer: A foreign key references a key in another table, or in the same table, and helps maintain referential integrity by preventing invalid references.

Q5. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before grouping and aggregation. HAVING filters grouped results, often using aggregate expressions such as COUNT or SUM.

Q6. What is the difference between DELETE, TRUNCATE, and DROP?

Answer: DELETE removes selected rows and can use a WHERE clause. TRUNCATE removes all rows from a table and resets its data storage behavior according to MySQL rules. DROP removes the table object itself. Their transactional and foreign-key behavior differs, so they should not be treated as interchangeable.

Q7. What is normalization?

Answer: Normalization organizes data into related tables to reduce unnecessary duplication and prevent insert, update, and delete anomalies.

Q8. What is denormalization?

Answer: Denormalization intentionally introduces some redundancy or precomputed information to support specific performance or reporting needs. It should be based on measured requirements and accompanied by controls to maintain consistency.

Q9. What is an index?

Answer: An index is a data structure that helps the database find rows efficiently. Indexes can improve SELECT queries but require storage and add overhead to INSERT, UPDATE, and DELETE operations.

Q10. What is a composite index?

Answer: A composite index contains more than one column. The column order matters because MySQL can efficiently use the leading columns of the index for matching query patterns.

Q11. What is a JOIN?

Answer: A JOIN combines related rows from two or more tables. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN.

Q12. What is the difference between INNER JOIN and LEFT JOIN?

Answer: INNER JOIN returns only matching rows. LEFT JOIN returns every row from the left table and includes matching right-table data where available; unmatched right-side columns contain NULL.

Q13. What is a subquery?

Answer: A subquery is a query nested inside another SQL statement. It can provide a value, a set of values, or a result set used by the outer query.

Q14. What is a CTE?

Answer: A Common Table Expression is a named temporary result set defined with WITH and used within a single SQL statement. It can improve readability and can support recursive queries in MySQL 8.0 and later.

Q15. What is a window function?

Answer: A window function calculates a value across a related set of rows while preserving the individual rows in the result. Examples include ROW_NUMBER, RANK, DENSE_RANK, and running totals using SUM with an OVER clause.

Q16. What are ACID properties?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe important guarantees of reliable transactions.

Q17. What is the difference between COMMIT and ROLLBACK?

Answer: COMMIT makes the current transaction's changes permanent. ROLLBACK reverses uncommitted changes in the current transaction.

Q18. What is a deadlock?

Answer: A deadlock occurs when transactions wait on resources held by one another in a way that prevents progress. MySQL may detect a deadlock and roll back one transaction. Applications should handle the error and retry suitable transactions safely.

Q19. What is EXPLAIN used for?

Answer: EXPLAIN displays the query execution plan selected or estimated by the optimizer. It helps identify access methods, possible indexes, join order, and estimated rows.

Q20. What is a view?

Answer: A view is a named query that can be used like a virtual table. It can simplify repeated queries and help limit which columns or rows are exposed, depending on how permissions are configured.

Q21. What is a stored procedure?

Answer: A stored procedure is a named collection of SQL statements stored in the database and executed using CALL. It can accept parameters and encapsulate reusable database logic.

Q22. What is a trigger?

Answer: A trigger is a database program that executes automatically in response to a defined table event, such as INSERT, UPDATE, or DELETE. Triggers should be documented because their effects may not be obvious to someone reading the original query.

Q23. What is the difference between CHAR and VARCHAR?

Answer: CHAR is fixed-length, whereas VARCHAR stores variable-length character data. The appropriate choice depends on the data and workload.

Q24. What is the difference between DECIMAL and FLOAT?

Answer: DECIMAL stores fixed-precision decimal values and is commonly used for monetary amounts. FLOAT stores approximate numeric values and is better suited to calculations where approximate precision is acceptable.

Q25. What is NULL in MySQL?

Answer: NULL represents a missing or unknown value. It should be checked using IS NULL or IS NOT NULL rather than equality comparisons such as = NULL.

Q26. What is the difference between COUNT(*) and COUNT(column_name)?

Answer: COUNT(*) counts rows, while COUNT(column_name) counts rows where that column is not NULL.

Q27. How can you find duplicate values?

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Answer: Group the values and use HAVING COUNT(*) greater than one to identify repeated values. A unique constraint can prevent duplicates when the business rules require uniqueness.

Q28. How do you find the second-highest distinct product price?

SELECT MAX(unit_price) AS second_highest_price
FROM products
WHERE unit_price < (
    SELECT MAX(unit_price)
    FROM products
);

Answer: The subquery finds the highest price. The outer query finds the maximum price below it. If fewer than two distinct prices exist, the result is NULL.

Q29. How can you identify customers who placed more than three orders?

SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

Answer: Group orders by customer and filter the groups using HAVING.

Q30. How would you improve a slow SQL query?

Answer: First reproduce and measure the issue. Review EXPLAIN output, check indexes and join conditions, reduce unnecessary rows and columns, avoid costly expressions when alternatives exist, and measure again. Also investigate data volume, server resources, locking, and application behavior rather than assuming the query alone is responsible.

19. Practical SQL Interview Exercises

Exercise 1: Find the Most Expensive Product

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 1;

Concepts Tested: ORDER BY and LIMIT.

Exercise 2: Find Customers from Mumbai

SELECT customer_id, first_name, last_name, email
FROM customers
WHERE city = 'Mumbai';

Concepts Tested: Filtering and SELECT.

Exercise 3: Count Orders by Status

SELECT order_status, COUNT(*) AS total_orders
FROM orders
GROUP BY order_status
ORDER BY total_orders DESC;

Concepts Tested: Aggregate functions and GROUP BY.

Exercise 4: Find Products Never Ordered

SELECT
    p.product_id,
    p.product_name
FROM products AS p
LEFT JOIN order_items AS oi
    ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

Concepts Tested: LEFT JOIN and NULL filtering.

Exercise 5: Calculate Total Units Sold per Product

SELECT
    p.product_name,
    COALESCE(SUM(oi.quantity), 0) AS total_units_sold
FROM products AS p
LEFT JOIN order_items AS oi
    ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_units_sold DESC;

Concepts Tested: LEFT JOIN, SUM, GROUP BY, and COALESCE.

Exercise 6: Find Orders with a Total Above 2,000

SELECT
    order_id,
    SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 2000;

Concepts Tested: Aggregation and HAVING.

Exercise 7: Rank Customers by Spending

WITH customer_spending AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spending
    FROM orders AS o
    INNER JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.order_status <> 'Cancelled'
    GROUP BY o.customer_id
)
SELECT
    customer_id,
    total_spending,
    DENSE_RANK() OVER (
        ORDER BY total_spending DESC
    ) AS spending_rank
FROM customer_spending;

Concepts Tested: CTEs, joins, aggregate functions, and window functions.

20. Mock Interview and Project Presentation

20.1 Suggested Presentation Structure

1. Introduction: Explain the business problem and the purpose of the project.
2. Requirements: Describe the entities and reports the business needs.
3. Schema Design: Present the ERD and explain the relationships.
4. Implementation: Demonstrate tables, keys, constraints, and sample data.
5. SQL Demonstration: Run important joins, aggregates, and advanced queries.
6. Reporting: Show monthly sales, best-selling products, and low-stock reports.
7. Optimization: Explain selected indexes and show an EXPLAIN plan.
8. Testing: Describe validation checks and edge cases.
9. Challenges: Explain one or two problems and how you solved them.
10. Conclusion: Summarize what you learned and identify possible improvements.

20.2 Sample Project Introduction for an Interview

"I developed a Retail Sales and Inventory Management System using MySQL. The project organizes customer, category, product, order, order-item, and payment data into related tables. I implemented primary keys, foreign keys, validation constraints, and SQL queries for operational and business reporting. I also used joins, CTEs, aggregate functions, window functions, and EXPLAIN to analyze sales and evaluate query performance. The project helped me practice database design, data integrity, reporting, and SQL troubleshooting."

Adjust this introduction to match your actual implementation. Be ready to open the relevant SQL script and explain how each feature works.

20.3 Questions to Prepare About Your Own Project

• Why did you select this business problem?
• Why did you create separate tables for orders and order items?
• Why is the order-item price stored separately from the current product price?
• Which columns are primary keys and foreign keys?
• What data duplication did normalization help you avoid?
• How did you calculate order totals?
• How did you handle cancelled orders in your reports?
• Which indexes did you add, and which queries benefit from them?
• How did you test the correctness of the reports?
• What would you change if the database contained millions of orders?
• How would you handle two customers trying to purchase the last unit of a product?
• What security and backup practices would you apply in production?

21. Common Project Problems and Troubleshooting

22. Capstone Project Extensions

After completing the core system, extend it to demonstrate deeper database skills. Choose extensions that solve a clearly defined business problem rather than adding features without a purpose.

22.1 Supplier Management

Create Suppliers and Product Suppliers tables to track supplier details, purchase costs, lead times, and the relationship between suppliers and products.

22.2 Inventory Movement History

Create an inventory_movements table that records stock receipts, sales, returns, adjustments, timestamps, and reference documents. A movement history provides an audit trail beyond storing only the current stock quantity.

22.3 Discounts and Promotions

Create a discount or promotion model with start dates, end dates, eligibility conditions, and validation rules. Document how discounts affect order totals and reporting.

22.4 Returns and Refunds

Introduce order returns and refund records. Distinguish gross sales, refunded amounts, and net sales so that reports reflect actual business definitions.

22.5 Views for Reusable Reports

CREATE VIEW product_inventory_status AS
SELECT
    product_id,
    product_name,
    stock_quantity,
    reorder_level,
    CASE
        WHEN stock_quantity <= reorder_level
            THEN 'Reorder Required'
        ELSE 'Stock Available'
    END AS inventory_status
FROM products
WHERE is_active = TRUE;

This view provides a reusable inventory status query. In production, review access privileges and consider whether the view should expose every underlying column.

22.6 Stored Procedures

Create a stored procedure for a well-defined recurring operation, such as retrieving sales for a specified date range. Validate parameters and document error handling before using procedures for important business operations.

23. Final Capstone Submission Checklist

• Database created successfully.
• All required tables created with suitable data types.
• Primary keys and foreign keys defined correctly.
• Constraints added for important business rules.
• Sample data inserted and checked.
• Basic CRUD operations tested.
• Joins, subqueries, CTEs, and window functions demonstrated where supported.
• Business reports produce verified results.
• Transaction behavior tested for success and failure cases.
• Indexes justified and query plans reviewed.
• Data validation and edge-case tests documented.
• ERD and data dictionary included.
• README contains reproducible setup instructions.
• SQL scripts organized into meaningful files.
• Screenshots contain no credentials or sensitive data.
• Project description accurately reflects the work completed.
• Interview questions and a short project presentation prepared.

24. Quick Revision Notes

25. Conclusion

The Complete MySQL Capstone Project, Portfolio Review, and Interview Preparation module brings together database design, SQL querying, data integrity, transactions, indexing, reporting, testing, documentation, and professional presentation. By building the Retail Sales and Inventory Management System, you practice how individual SQL concepts work together in a realistic business scenario.

To complete the module successfully, run every SQL script in your own MySQL environment, validate the results, document your design choices, and prepare to explain the reasoning behind your queries. A strong portfolio is not simply a collection of SQL commands; it is a clear demonstration of how you solve a business problem with reliable, maintainable database design.

Continue Learning: Explore the MySQL Training Course, register for the JustAcademy Course Demo, or visit the JustAcademy website.