MySQL Practical Projects

Learning SQL commands individually is important, but practical projects show how those commands work together.

• Practice database design.
• Create related tables.
• Insert real-looking data.
• Use SELECT and WHERE.
• Practice UPDATE and DELETE.
• Use JOINs.
• Use GROUP BY and aggregate functions.
• Practice subqueries and views.
• Use indexes and transactions.
• Understand real-world database structures.

The first project is a simple Student Management System.

It can store:

• Student information
• Course information
• Student enrollment
• Fees
• Student status

Example database:

CREATE DATABASE student_management;

USE student_management;

Create a table for students:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    phone VARCHAR(20),
    city VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active',
    admission_date DATE
);

This table stores the basic student information.

INSERT INTO students
(name, email, phone, city, status, admission_date)
VALUES
('Amit Kumar', 'amit@example.com', '9876543210',
 'Patna', 'Active', '2026-01-10'),

('Priya Singh', 'priya@example.com', '9876543211',
 'Gaya', 'Active', '2026-01-15'),

('Rahul Sharma', 'rahul@example.com', '9876543212',
 'Patna', 'Inactive', '2026-02-05'),

('Neha Kumari', 'neha@example.com', '9876543213',
 'Delhi', 'Active', '2026-02-20');

Display all students:

SELECT *
FROM students;

Find students from Patna:

SELECT *
FROM students
WHERE city = 'Patna';

Find active students:

SELECT *
FROM students
WHERE status = 'Active';

Find students whose names start with A:

SELECT *
FROM students
WHERE name LIKE 'A%';

Find students whose names contain Kumar:

SELECT *
FROM students
WHERE name LIKE '%Kumar%';

LIKE is useful for searching text patterns.

Create a courses table:

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    duration_months INT,
    fee DECIMAL(10,2)
);

Insert courses:

INSERT INTO courses
(course_name, duration_months, fee)
VALUES
('Python Full Stack', 12, 35000),
('Web Development', 6, 15000),
('ADCA', 6, 10000),
('Tally Prime', 3, 4000);

A student can enroll in a course. Create an enrollment table:

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

This creates relationships between students and courses.

INSERT INTO enrollments
(student_id, course_id, enrollment_date)
VALUES
(1, 1, '2026-01-12'),
(2, 2, '2026-01-18'),
(3, 3, '2026-02-10'),
(4, 1, '2026-02-22');

Each enrollment connects one student with one course.

Display students and their courses:

SELECT
    s.name,
    c.course_name,
    c.fee
FROM students s
INNER JOIN enrollments e
    ON s.student_id = e.student_id
INNER JOIN courses c
    ON e.course_id = c.course_id;

This demonstrates how multiple related tables can be combined.

A fee management system can store student payments.

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(30),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id)
);

This table connects each payment with a student.

INSERT INTO payments
(student_id, amount, payment_date, payment_method)
VALUES
(1, 5000, '2026-02-01', 'Cash'),
(1, 3000, '2026-03-01', 'UPI'),
(2, 5000, '2026-02-05', 'UPI'),
(4, 7000, '2026-03-10', 'Cash');

Calculate the total amount collected:

SELECT SUM(amount) AS total_collection
FROM payments;

Calculate the number of payments:

SELECT COUNT(*) AS total_payments
FROM payments;

SELECT
    s.student_id,
    s.name,
    SUM(p.amount) AS total_paid
FROM students s
INNER JOIN payments p
    ON s.student_id = p.student_id
GROUP BY
    s.student_id,
    s.name;

This produces the total payment amount for each student.

Find total payments by payment method:

SELECT
    payment_method,
    SUM(amount) AS total_amount
FROM payments
GROUP BY payment_method;

This demonstrates the practical use of GROUP BY and SUM.

Find students who have paid more than ₹5,000 in total:

SELECT
    s.student_id,
    s.name,
    SUM(p.amount) AS total_paid
FROM students s
INNER JOIN payments p
    ON s.student_id = p.student_id
GROUP BY
    s.student_id,
    s.name
HAVING SUM(p.amount) > 5000;

HAVING filters grouped results.

A library system can contain books, members, and borrowing records.

CREATE DATABASE library_management;

USE library_management;

Basic tables can include:

• books
• members
• borrowings

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(150),
    category VARCHAR(100),
    price DECIMAL(10,2),
    available_copies INT DEFAULT 1
);

Insert sample books:

INSERT INTO books
(title, author, category, price, available_copies)
VALUES
('Learn SQL', 'John Smith', 'Database', 500, 5),
('Python Basics', 'David Brown', 'Programming', 600, 3),
('HTML & CSS', 'Robert Lee', 'Web', 450, 4);

CREATE TABLE members (
    member_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(150) UNIQUE,
    join_date DATE
);

Insert sample members:

INSERT INTO members
(name, phone, email, join_date)
VALUES
('Amit Kumar', '9876543210',
 'amit@example.com', '2026-01-05'),

('Priya Singh', '9876543211',
 'priya@example.com', '2026-01-10');

CREATE TABLE borrowings (
    borrowing_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    member_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    return_date DATE,
    FOREIGN KEY (book_id)
        REFERENCES books(book_id),
    FOREIGN KEY (member_id)
        REFERENCES members(member_id)
);

This table connects books with members.

Display borrowing details:

SELECT
    m.name AS member_name,
    b.title AS book_title,
    br.borrow_date,
    br.return_date
FROM borrowings br
INNER JOIN members m
    ON br.member_id = m.member_id
INNER JOIN books b
    ON br.book_id = b.book_id;

This is a practical example of using multiple INNER JOIN operations.

An employee database can store employee and department information.

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(12,2),
    department_id INT,
    joining_date DATE,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Find the average salary by department:

SELECT
    d.department_name,
    AVG(e.salary) AS average_salary
FROM departments d
INNER JOIN employees e
    ON d.department_id = e.department_id
GROUP BY
    d.department_id,
    d.department_name;

Find the highest salary:

SELECT MAX(salary) AS highest_salary
FROM employees;

An online store can contain customers, products, orders, and order items.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150) UNIQUE
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(150),
    price DECIMAL(10,2),
    stock INT
);

These tables form the basic foundation of an online store database.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

Order items can be stored separately:

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,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)
        REFERENCES orders(order_id),
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);

Calculate the total value of each order:

SELECT
    order_id,
    SUM(quantity * price) AS order_total
FROM order_items
GROUP BY order_id;

Calculate total sales:

SELECT
    SUM(quantity * price) AS total_sales
FROM order_items;

A view can simplify frequently used reports.

CREATE VIEW student_course_report AS
SELECT
    s.student_id,
    s.name,
    c.course_name,
    c.fee
FROM students s
INNER JOIN enrollments e
    ON s.student_id = e.student_id
INNER JOIN courses c
    ON e.course_id = c.course_id;

Use the view:

SELECT *
FROM student_course_report;

Use a transaction when multiple related operations should be handled together.

START TRANSACTION;

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

INSERT INTO orders
(customer_id, order_date)
VALUES
(1, CURRENT_DATE);

COMMIT;

If the application detects an error before COMMIT:

ROLLBACK;

Create an index for frequently searched student cities:

CREATE INDEX idx_student_city
ON students(city);

Create an audit table:

CREATE TABLE student_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    action VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create a trigger:

DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_audit
    (student_id, action)
    VALUES
    (NEW.student_id, 'INSERT');
END //

DELIMITER ;

The following example combines several MySQL concepts into one mini project.

CREATE DATABASE school_project;

USE school_project;

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    city VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active'
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2)
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    amount DECIMAL(10,2),
    payment_date DATE,
    FOREIGN KEY (student_id)
        REFERENCES students(student_id)
);

INSERT INTO students
(name, email, city)
VALUES
('Amit Kumar', 'amit@example.com', 'Patna'),
('Priya Singh', 'priya@example.com', 'Gaya');

INSERT INTO courses
(course_name, fee)
VALUES
('Python Full Stack', 35000),
('Web Development', 15000);

INSERT INTO enrollments
(student_id, course_id, enrollment_date)
VALUES
(1, 1, CURRENT_DATE),
(2, 2, CURRENT_DATE);

INSERT INTO payments
(student_id, amount, payment_date)
VALUES
(1, 5000, CURRENT_DATE),
(2, 3000, CURRENT_DATE);

CREATE INDEX idx_students_city
ON students(city);

SELECT
    s.student_id,
    s.name,
    c.course_name,
    c.fee,
    COALESCE(SUM(p.amount), 0) AS total_paid
FROM students s
INNER JOIN enrollments e
    ON s.student_id = e.student_id
INNER JOIN courses c
    ON e.course_id = c.course_id
LEFT JOIN payments p
    ON s.student_id = p.student_id
GROUP BY
    s.student_id,
    s.name,
    c.course_name,
    c.fee
ORDER BY s.name;

This mini project combines databases, tables, primary keys, foreign keys, INSERT, SELECT, JOIN, GROUP BY, aggregate functions, COALESCE, ORDER BY, and indexes.